Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The official PostgreSQL documentation states that pg_dump can use the --schema=SCHEMA option to selectively dump a given schema.

However according to the same documentation (read Note on --schema=SCHEMA) foreign-keys to objects outside the selected schema will be included in the dump, but not the tables which they refer to.

With that said, I would like to know how to dump a given schema in a way that it includes all the needed tables regardless of the schema they are in and that this is done in a way that is safe/easy to restore the dump.

Additionally, are there any tools available that do it already?

Thanks

share|improve this question
    
@dezso clarified the question and added documentation link. What I meant is that tables to which the foreign-keys refer to are not in the dump. The actual foreign-key is. –  Unode Oct 30 '12 at 15:27
1  
Now this is much clearer. However, I wouldn't expect that a partial dump can be restored in a fresh database. If you want to accomplish that, you have to query the information schema for FK dependencies, and dump the tables which your schema objects depend on alongside with your schema. –  dezso Oct 30 '12 at 15:33
    
@dezso you wouldn't happen to know any tool that does that automatically? –  Unode Oct 30 '12 at 15:37
    
Unfortunately not. Could be useful somtimes. –  dezso Oct 30 '12 at 15:46
add comment

migrated from serverfault.com Oct 30 '12 at 15:04

This question came from our site for professional system and network administrators.

1 Answer

I am not aware of any tools to do exactly what you are asking right now. I want to suggest a few options you might have overlooked however that may meet your needs.

Database Cloning

If your database is not being used 24/7 you can clone it for test cases. This would be done using during times when the db is not in use:

createdb -U postgres new_dbname -T old_dbname

This does a file-level copy of the database, and so it preserves all data and is much faster than a dump and reload.

I very frequently use this technique for cases where I want to run test cases on one db where I can't guarantee that everything will roll back.

Base Backup and Restore

A second thing you can do if you want to bring all data onto another server is to set it up for replication as a master with at least one wal_sender, and use pg_base_backup to clone your db cluster over the network. Note this is a file-based clone, and so it will be a very network-intensive process (more than a dump) but restore will be a lot faster.

Writing your own Tool

One of the big difficulties in having a generally available tool is that cross-schema dependencies could be quite complex and handling all possible complexity is a bit of a daunting task. However for specific purposes this isn't too hard. I would recommend three steps, using pg_dump to get you most of the way there, and then having a script written in one language or another to add what we can hope are modest missing pieces. I think you could even do it with an sql script run through psql.

  1. Dump schemas only of tables you need. Create new file. Something like: pg_dump -t mytable1 -t mytable2 -t mytable3 > my_partial_dump.sql

  2. Export selected data to csv, and wrap using COPY statements. This could be done using psql along with echo and COPY TO STDOUT. This is appended to the partial dump.

  3. Dump the schema as you are, and append this to the partial dump.

The advantage of a purpose-built tool if this is what you need is that you can manually manage the complex part of it and thus have a smaller, simpler tool than you might otherwise require.

share|improve this answer
    
We currently use the approach described on your second point to have a Hot replacement ready when needed. We are still lacking that tool though, but at the moment we workaround the limitation by doing a full dump every now and then. The first point is indeed a neat trick for a quick copy for testing. –  Unode Feb 15 '13 at 11:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.