How can I do a dump of only the table structure in PostgreSQL?

Question

In a similar vein to this question, how would I do a schema-only dump in PostgreSQL?

score 62 · Accepted Answer · answered Aug 31 '09 at 04:19

62

pg_dump --schema-only

answered Aug 31 '09 at 04:19

jldugger

14,122
19
73
129

easier than I realized it would be :) – warren Aug 31 '09 at 04:22
3

Frankly, I haven't much clue about postgres administration, but I've been watching videos from pgcon 09 and someone mentioned pg_dump. – jldugger Aug 31 '09 at 04:27
either way - it's what I needed :) – warren Sep 07 '09 at 13:41
7

@warren: it's even easier: `pg_dump -s`. – hans0l0 Nov 20 '13 at 14:58
This command dumps everything and not only *table* structure: --schema-only Dump only the object definitions (schema), not data [docs](https://www.postgresql.org/docs/current/app-pgdump.html) – arod Aug 11 '22 at 18:30

arod · Answer 2 · 2017-03-04T22:46:02.350

16

If you only want the CREATE TABLE statements, then you can do pg_dump -s databasename | awk 'RS="";/CREATE TABLE[^;]*;/'

edited Mar 04 '17 at 22:46

answered May 28 '15 at 18:22

arod

542
6
19

5

Not sure why the downvotes as this command works perfectly. So +1 from me. – Dave Mar 04 '17 at 19:17
4

That was the rigth answer to me, because i couldn't create file with functions, views and etc. – Natan Medeiros Jun 18 '18 at 12:27
1

@Dave because adding an additional option not at all linked to the initial question 6 years later is not that useful. – Arne Nov 21 '18 at 13:28
1

@Arne. Disagree. The question title, which specifies dumping "only the table structure", gives this page a high search rank. And plenty of people, like Natan who remarked prior to you, want to avoid moving functions and/or views from old extensions in the event that they are starting with a fresh db with updated extensions. So it absolutely is "useful", as opposed to "not useful", which are the voting criteria. Absolutely getting my +1. – elrobis Aug 09 '19 at 19:23
1

@Dave. I think that this answer is right. I opened because the title. I'm searching table-only struct dump. This is better than the other or the title is not acurated. – Emilio Platzer Oct 09 '19 at 14:20
This awk command didn't work for me - it returned everything. Perhaps there has been some syntax change since the original post? – nonethewiser Aug 05 '22 at 14:38
@nonethewiser syntax has not changed AFAIK – arod Aug 06 '22 at 17:43
@arod I am not familiar with awk but see there are a few different implementations (mawk, gawk). I am on macOS. I ended up going a different direction but believe either it has to do with that, or I am having a totally different issue. Thank you for the reply. – nonethewiser Aug 07 '22 at 18:05
@nonethewiser I'm not too familiar with awk either (or the other ones), but it seems like your SQL statements don't end with ";" each one? That could make this approach fail... – arod Aug 11 '22 at 18:26

How can I do a dump of only the table structure in PostgreSQL?

2 Answers2