8

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 :)

Improve this question

1 Answer 1

Reset to default
19

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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 ;)
@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.
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 :)
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.
Can I do this kind of concatenation if I'm taking dump in compressed format ( using -Fc)?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.