PostgreSQL dump/restore from 8.4 to 8.1 in a shell script 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 &gt; 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?
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 &gt; 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.