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 > onetable.pg
which you can then restore in the other database:
psql destdb < onetable.pg
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 fulldump.pg > onetable.pg
before restoring it as shown above, using psql
.
Generally, it's better to do your dumping via
which lets you then use the full power of pg_restore.
Following this STACKOVERFLOW's question, if the dump format is text
works fine.
For these type of operations the dump file should be in the postgresql custom format created with pg_dump:
Then you can restore a single table with pg_restore: