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?