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: 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?