Using pg_restore to create or overwrite tables

Question

I know this is a weird request, but for some hacky reasons I can't avoid, I'd like to be able to consistently sync a few tables from one database to another. I know I could write out the functionality myself in a script, but I figure pg_dump and pg_restore will apply a lot of optimizations to the process that I'm not aware of myself.

What I'm wondering is if there's a way to have pg_restore overwrite the existing tables. Basically, in pseudo-code something like:

-- pseudo code
begin;
drop table to_restore;
drop table to_restore2;
drop table to_restore3;

-- etc

restore table to_restore;
restore table to_restore2;
restore table to_restore3;

-- etc
commit;

I'm also open to alternatives ways of doing this if this isn't so great.

Schmo

you cant put bash command in postgres transaction – Deva

@VaoTsun hence why I called it psuedo-code. I know I can't, but I'd like to do the equivalent – Schmo

either use transaction and let's say COPY from to (or fdw, or dblink), or do not in transaction – Deva

@VaoTsun right, but is the equivalent possible with just pg_restore, and no other extra SQL. What I'm wondering is if I can do with without the need to write my own code other than just calling pg_restore. – Schmo

if you want to use pg_restore - loosing transaction is inavoidable – Deva

Answer 1

Seems like you want the -c option specified in the pg_restore documentation

-c

--clean

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)

which you can use with the -1 flag to do everything in one transaction

-1

--single-transaction

Execute the restore as a single transaction (that is, wrap the emitted commands in BEGIN/COMMIT). This ensures that either all the commands complete successfully, or no changes are applied. This option implies --exit-on-error.

Gethsemane

Be carefull when restoring partial data. On one occassion a restore operation failed and we thought everything would be rolled back. It took us some time to understand why we were getting duplicate key value errors when inserting new rows: The sequence table(s) were restored and not rolled back, while no data was restored ! – Gethsemane

Answer 2

This is only example of possible solution:

copy those tables from first db to csv. and use extremely fast copy in transaction:

begin;
truncate table to_restore;
truncate table to_restore2;
truncate table to_restore3;
  set commit_delay to 100000;
  set synchronous_commit to off;
copy to_restore from 'to_restore.csv';
copy to_restore2 from 'to_restore2.csv';
copy to_restore3 from 'to_restore3.csv';
commit;

Deva

Unfortunately, this isn't helpful to me because I was looking to use only pg_restore, and not write my own SQL. – Schmo

that's right - it is either transaction or pg_restore – Deva

You can run pg_restore with the --single-transaction option. – Schmo

yes multiple -t -c and --single-transaction must do the trick – Deva

This works great except for one situation -- when you need to cascade the truncate/drop. Is there any way to accomplish this via pg_restore? – Autochthon