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?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.