If I use the command “pg_dumpall -c -U postgres | gzip > pg_dbs.gz” to create a backup of all databases from “postgresql 9.2” and then restore it to “postgresql 8.1” on a different server only about the first half of the databases are restored with their tables and records. The second half, only the databases themselves are created but with no tables and no records.
My restore commands are:
gunzip pg_dbs.gz
psql -U postgres -f pg_dbs postgres
If I backup one of the databases that was not restored by itself and then restore it. It is restored with its tables and records.
Backup from postgresql 9.2: pg_dump -U postgres -O -c cswe2 > cswe2.bkp
RESTORE to postgresql 8.1: psql -U postgres cswe2 < /tmp/cswe2.bkp
The first database in the pg_dumpall file that did not restore with its tables and records, I was able to restore from a separate individual pg_dump backup of it.
Thus I cannot figure why the pg_dumpall stops restoring at a certain point. It’s always as the same point where it stops restoring the tables and records, but as stated above I can restore that database if from a pg_dump backup.
What can be causing the restore to stop populating the tables and data for the remainder of the databases from the pg_dumpall?
Thanks
Marc