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.

@VaoTsun hence why I called it psuedo-code. I know I can't, but I'd like to do the equivalent — m0meni, Apr 21 '17 at 15:18
either use transaction and let's say `COPY` from to (or fdw, or dblink), or do not in transaction — Vao Tsun, Apr 21 '17 at 15:20
@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`. — m0meni, Apr 21 '17 at 15:21
if you want to use `pg_restore` - loosing transaction is inavoidable — Vao Tsun, Apr 21 '17 at 15:23

score 9 · Accepted Answer · edited Jun 20 '20 at 09:12

9

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 21 '17 at 15:23

DeepButi

402
4
19

2

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 ! – DeepButi Apr 21 '17 at 15:34

score 2 · Answer 2 · answered Apr 21 '17 at 15:22

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;

answered Apr 21 '17 at 15:22

Vao Tsun

47,234
13
100
132

Unfortunately, this isn't helpful to me because I was looking to use **only** `pg_restore`, and not write my own SQL. – m0meni Apr 21 '17 at 15:28
that's right - it is either transaction or `pg_restore` – Vao Tsun Apr 21 '17 at 15:29
1

You can run pg_restore with the `--single-transaction` option. – m0meni Apr 21 '17 at 15:31
yes multiple `-t` `-c` and `--single-transaction` must do the trick – Vao Tsun Apr 21 '17 at 15:32
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? – Ben Wilson Oct 21 '21 at 20:30

Using pg_restore to create or overwrite tables

2 Answers2