Postgresql - Dump database with x tables - schema only but data from one table

Question

wondering about a little convenience problem of mine atm.

Say I got a database with a variable amount of tables and I want a dump of that database BUT only the table structure and the data of one specific table.

It is, of course, basically possible to do that, but the command would be rather long and I'd have to know all the tables.

But I'd need a command without knowing the names or how many other tables there are, only the one table whose data I want should be relevant, the others are basically just cattle, and in the end I would like to have it all in one file.

Looking forward to reading some suggestions or maybe some pointers on how to solve my problem. Really curious :)

Leges

Answer 1

The default pg_dump output format is a psql script, so you can just concatenate them:

pg_dump -d my_db --schema-only > dump.sql
pg_dump -d my_db -t my_table --data-only >> dump.sql

Hibernia

True, that's the rather obvious solution. Thanks anyways, but mainly I am looking to gain some experience with postgresql through more elaborate solutions. But of course I am also looking for, preferably, short and simple solutions to my problem. The concatenation is good and works, but I'd prefer a one-liner ;) – Leges

@Doeyo: Well, you could cram it onto one line with {pg_dump...; pg_dump...} > dump.sql... Postgres doesn't provide any way of exporting the database structure other than pg_dump, and the --schema-only and --data-only options are mutually exclusive, so I think this is about as simple as it's going to get. – Hibernia

But if you want something that's "elaborate" for its own sake, here are a couple of horrific kludges... You could build a temporary database which matches your desired output, or suppress dumping of other tables via row security policies. Please don't run either of these on any system you actually care about :) – Hibernia

Ya, I already figured the mutual exclusiveness, thats what actually got me to ask the question. Thanks for the link, I'm not gonna run those but they look kinda interesting, so I'll spare some time and check them out. The solution I am going with now is the concatenation, since it seems to be the most reasonable one and it's just a temporary workaround to get a set of testdata anyways. But it has been an interesting learning experience, so thanks for that. – Leges

Can I do this kind of concatenation if I'm taking dump in compressed format ( using -Fc)? – Jodoin

@prabhang: No, each binary dump contains its own "table of contents", so you can't concatenate the files directly. If you really need a single -Fc dump, I think you'll need to restore the --schema-only and --data-only dumps into a temporary database, and take a full dump of that instead. – Hibernia