5

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.

Improve this question
5
  • you cant put bash command in postgres transaction Commented Apr 21, 2017 at 15:17
  • 1
    @VaoTsun hence why I called it psuedo-code. I know I can't, but I'd like to do the equivalent Commented Apr 21, 2017 at 15:18
  • either use transaction and let's say COPY from to (or fdw, or dblink), or do not in transaction Commented Apr 21, 2017 at 15:20
  • 1
    @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. Commented Apr 21, 2017 at 15:21
  • if you want to use pg_restore - loosing transaction is inavoidable Commented Apr 21, 2017 at 15:23

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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 !
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;
Improve this answer

5 Comments

Unfortunately, this isn't helpful to me because I was looking to use only pg_restore, and not write my own SQL.
that's right - it is either transaction or pg_restore
You can run pg_restore with the --single-transaction option.
yes multiple -t -c and --single-transaction must do the trick
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?

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.