PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

postgresqlpsql

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?

Best Answer

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.

Related Question