Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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;


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


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?

share|improve this question

migrated from Feb 23 '12 at 17:12

This question came from our site for professional and enthusiast programmers.

up vote 3 down vote accepted

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.

share|improve this answer
With the --clean parameter, it works ! thank you ! – canu johann Feb 23 '12 at 9:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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