Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Feb 23 '12 at 17:12

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

add comment

1 Answer 1

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
add comment

Your Answer

 
discard

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.