restore a pg_dumpall only breaks

From: Marc Fromm <Marc(dot)Fromm(at)wwu(dot)edu>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: restore a pg_dumpall only breaks
Date: 2016-11-24 02:00:03
Message-ID: CY1PR0201MB14360D78FE30F98FFAB1E00B9DB60@CY1PR0201MB1436.namprd02.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2016-11-24 02:04:50 Re: restore a pg_dumpall only breaks
Previous Message Yves Lavoie, GaYLi 2016-11-22 18:19:16 Filter logging by table