CODEWITHSUNDEEP
postgresqlpostgresql-9.1psqlpg-dump
emraldinho
emraldinho

Reputation: 497

Postgres backup and overwrite one table

I have a postgres database, I am trying to backup a table with :

pg_dump --data-only --table=<table> <db> > dump.sql

Then days later I am trying to overwrite it (basically want to erase all data and add the data from my dump) by:

psql -d <db> -c --table=<table> < dump.sql

But It doesn't overwrite, it adds on it without deleting the existing data.

Any advice would be awesome, thanks!

Upvotes: 6

Views: 3125

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

You have basically two options, depending on your data and fkey constraints.

If there are no fkeys to the table, then the best thing to do is to truncate the table before loading it. Note that truncate behaves a little odd in transactions so the best thing to do is (in a transaction block):

  1. Lock the table
  2. Truncate
  3. Load

This will avoid other transactions seeing an empty table.

If you have fkeys then you may want to load into a temporary table and then do an upsert. In this case you may still want to lock the table to avoid a race condition if it is possible other transactions may want to write to the table (also in a transaction block):

  1. Load data into a temporary table
  2. Lock the destination table (optional, see above)
  3. use a writeable cte to "upsert" in the table.
  4. Use a separate delete statement to delete data from the table.

Stage 3 is a little tricky. You might need to ask a separate question about it, but basically you will have two stages (and write this in consultation with the docs):

  1. Update existing records
  2. Insert non-existing records

Hope this helps.

Upvotes: 4

Related Questions