5

I am trying to restore data in to select tables of a database (9.3.5). This is a data refresh from production to test, while maintaining some table data in the test database (like application users table). I first truncate the tables that I plan to refresh and then I can successfully restore 1 table using this command: pg_restore -a -d ACMETEST -n public -v -U acme -t hardware /var/acme/backups/restore/ACMEDatabase.bak , which gives the following response followed immediately by a command prompt: !pg_restore: connecting to database for restore pg_restore: processing data for table "hardware" pg_restore: setting owner and privileges for TABLE DATA hardware

Queries show that the data successfully loaded.

The man page doesn't indicate that you can restore data in to multiple tables at once by using an array as an input to -t but I assumed it was possible so tried this: pg_restore -a -d ACMETEST -n public -v -U acme -t hardware,location /var/acme/backups/restore/ACMEDatabase.bak The result of the second command is one line that says: connecting to database for restore and then it dumps me right back to the command prompt as if the command is complete but none of the data is loaded.

I did look at the -L option to use a listfile but I'm trying to create a repeatable process that can be used by many people on databases across the country so I don't see the process of commenting out tons of lines from a listfile for each restore as having realistic chances of success. Do I have to execute the first command for every individual table I want to restore or is there some way I can feed an array of tables to this command?

Improve this question

1 Answer 1

Reset to default
9

From pg_restore's documentation you can see:

-t table --table=table

Restore definition and/or data of named table only.
Multiple tables may be specified with multiple -t switches.
This can be combined with the -n option to specify a schema.

Highlighting "Multiple tables may be specified with multiple -t switches", which means that you can use -t switch multiple times, and your example would work as:

pg_restore -a -d ACMETEST -n public -v -U acme -t hardware -t location /var/acme/backups/restore/ACMEDatabase.bak

As seen, I just used -t hardware -t location instead of -t hardware,location.

Improve this answer
3
  • Thanks MatheusOI! I Googled it and got the 9.1 version, which didn't have that detailed. Should have just done "man pg_restore" instead.
    – GrnMtnBuckeye
    Commented Sep 17, 2014 at 20:26
  • Just to clarify, this feature has been only added to pg_restore (and some other commands like vacuumdb) in PostgreSQL 9.3. In earlier version, you can only restore the full schema or one table at a time.
    – Cito
    Commented Dec 7, 2015 at 16:49
  • Related: you can also use multiple -n switches to specify multiple schemas to include for coarser filtering - we have found this very useful as well.
    – Matt
    Commented Sep 20, 2018 at 19:53

Your Answer

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

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