Postgresql: pg_dump and pg_restore

I had a problem with a database and I wanted to copy a table from a backup and install it in the current version. There are some subtleties. Here are my notes.

pg_dump

Use the “-t <table name>” argument to specify the table to dump. More multiple tables, use it multiple times.

Use the “–format custom” to make the dump compatible with pg_restore. If you do not set the format, the output will be a text file of SQL commands (useful in some cases) and you will need to use psql to restore the table.

Use –oids to maintain id numbers. This is especially important if the id is a foreign key else where.

Here is the command I used:

pg_dump roihubdiazlaw2db -f django_migrations.dump -t django_migrations -U my_username --format custom --oids

pg_restore

The trick here is not NOT use the -t flag! If you use this flag, pg_restore will NOT restore ids as primary keys. Instead they will become ordinary integers. The info needed to restore the table is already in the dump file. Restore with:

pg_restore django_migrations.dump -d roihubdiazlaw2db -U my_username --format custom

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s