2

I have to restore a DB table every day from a postgresql 8.4 to 8.1. For the backup, I use this code (windows server):

D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t my_table myDB > D:\backup\myFile.sql

I send "myFile.sql" through SCP to the second server, I restore it with the below command via a console (centos) :

su - postgres

psql myDB

drop my_table;

\q

psql -U postgres -p 5432 -d myDB -t my_table myDB -f /share/temp/myFile.sql

exit

Because of the version problem, I can't use pg_dump and pg_restore. I want to put this code in a shell script and execute as cron task, but I don't know how to do. I have tried with psql:

su - postgres -c "psql -U postgres -p 5432 -d myDB -t my_table myDB -f /share/temp/myFile.sql"

But many errors occurred like:

 duplicate key violates unique constraint
 ERROR:  relation "site_idx" already exists
...

It would be better to drop the table, create a new and restore with psql but I don't know how to do it. Any ideas?

1 Answer 1

3

You can drop the table (and dependent DB objects) with the pg_dump parameter --clean, from the pg_dump manual:

-c, --clean Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Restore might generate some harmless errors.)

So in your case this should be

D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 --clean -t my_table myDB > D:\backup\myFile.sql

you should then be able to restore the dump file with your current restore command in the cron job.

Hope that helps.

1
  • With the --clean parameter, it works ! thank you !
    – canu johann
    Feb 23, 2012 at 9:36

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.