After dropping and restoring a very large database a few times just to refresh the data in a single table, I thought that there must be an easier way to do this. I was right, you can restore a single table.

If you are starting with a live database, you can simply use pg_dump to backup only one table:

pg_dump --data-only --table=tablename sourcedb >

which you can then restore in the other database:

psql destdb <

But even if all you've got is a full dump of the source database, you can still restore that single table by simply extracting it out of the large dump first:

pg_restore --data-only --table=tablename >

before restoring it as shown above, using psql.

Generally, it's better to do your dumping via

pg_dump -Fc

which lets you then use the full power of pg_restore.

Comment by David Fetter
do you know of any issues if you are restoring the partition of a table and the parent table structure has changed after partition was backed up? will postgres handle this case correctly?
Comment by Anonymous