Обсуждение: restore a pg_dumpall only breaks

Поиск
Список
Период
Сортировка

restore a pg_dumpall only breaks

От
Marc Fromm
Дата:

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

 

Re: restore a pg_dumpall only breaks

От
Tom Lane
Дата:
Marc Fromm <Marc.Fromm@wwu.edu> writes:
> If I use the command "pg_dumpall -c -U postgres | gzip > pg_dbs.gz" to create a backup of all databases from
"postgresql9.2" and then restore it to "postgresql 8.1" on a different server only about the first half of the
databasesare restored with their tables and records. The second half, only the databases themselves are created but
withno tables and no records. 

A dump from 9.2 is no sure thing to restore into an 8.1 database; there
may be SQL syntax in it that 8.1 doesn't understand.  Have you checked
for errors during the restore?

            regards, tom lane


Re: restore a pg_dumpall only breaks

От
Marc Fromm
Дата:
> A dump from 9.2 is no sure thing to restore into an 8.1 database; there may be SQL syntax in it that 8.1 doesn't
understand. Have you checked for errors during the restore? 

After the last successful database restore from the pg_dumpall file this is displayed on the ssh session.

psql:pg_dbs.bkp:1029100: \connect: invalid connection option "-reuse-previous"


In the pg_dumpall file the \connect statement is different than the databases the fully restore

\connect -reuse-previous=on "dbname='sfr2002-2003'"

Successful restores only have \connect name_of_database. Not sure why the above one has the dbname in quotes.

Where else can I check for errors?

Thanks
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, November 23, 2016 6:05 PM
To: Marc Fromm <Marc.Fromm@wwu.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restore a pg_dumpall only breaks

Marc Fromm <Marc.Fromm@wwu.edu> writes:
> If I use the command "pg_dumpall -c -U postgres | gzip > pg_dbs.gz" to create a backup of all databases from
"postgresql9.2" and then restore it to "postgresql 8.1" on a different server only about the first half of the
databasesare restored with their tables and records. The second half, only the databases themselves are created but
withno tables and no records. 

A dump from 9.2 is no sure thing to restore into an 8.1 database; there may be SQL syntax in it that 8.1 doesn't
understand. Have you checked for errors during the restore? 

            regards, tom lane


Re: restore a pg_dumpall only breaks

От
Marc Fromm
Дата:
I figure the best thing to do is to upgrade postgres 8.4 (not 8.1 as previously mentioned) to 9.2 on this centOS6.4
server,so it's version matches the other server. I searched for some time on how to do this and every tutorial does it
alittle different and each has different steps that the others do not have. Is there a clear step by step procedure to
dothis upgrade? 

Thanks

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, November 23, 2016 6:05 PM
To: Marc Fromm <Marc.Fromm@wwu.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restore a pg_dumpall only breaks

Marc Fromm <Marc.Fromm@wwu.edu> writes:
> If I use the command "pg_dumpall -c -U postgres | gzip > pg_dbs.gz" to create a backup of all databases from
"postgresql9.2" and then restore it to "postgresql 8.1" on a different server only about the first half of the
databasesare restored with their tables and records. The second half, only the databases themselves are created but
withno tables and no records. 

A dump from 9.2 is no sure thing to restore into an 8.1 database; there may be SQL syntax in it that 8.1 doesn't
understand. Have you checked for errors during the restore? 

            regards, tom lane


Re: restore a pg_dumpall only breaks

От
Tom Lane
Дата:
Marc Fromm <Marc.Fromm@wwu.edu> writes:
>> A dump from 9.2 is no sure thing to restore into an 8.1 database; there may be SQL syntax in it that 8.1 doesn't
understand. Have you checked for errors during the restore? 

> After the last successful database restore from the pg_dumpall file this is displayed on the ssh session.
> psql:pg_dbs.bkp:1029100: \connect: invalid connection option "-reuse-previous"

Ah.  That option was introduced quite recently as part of a security fix.
It's no surprise 8.x doesn't recognize it.

You could probably work around this by using pg_dumpall with -g to just
dump roles and tablespaces, and then pg_dump'ing individual databases
separately.  A mite tedious and error-prone.  Or maybe use "sed" to strip
out the -reuse-previous options from pg_dumpall's output.  But really this
is the best answer, because 8.x is long out of support:

> I figure the best thing to do is to upgrade postgres 8.4 (not 8.1 as previously mentioned) to 9.2 on this centOS6.4
server,so it's version matches the other server. I searched for some time on how to do this and every tutorial does it
alittle different and each has different steps that the others do not have. Is there a clear step by step procedure to
dothis upgrade? 

If you're working with locally-built copies of Postgres, I'd say just
follow the recipe given in the pg_upgrade man page.  If you are working
with someone's packaged version of Postgres, the packager may have
provided a script or something for upgrades, in which case follow their
recommendation.  Either way it's a good idea to make a backup beforehand
in case of disaster.

            regards, tom lane


Re: restore a pg_dumpall only breaks

От
Marc Fromm
Дата:
> If you're working with locally-built copies of Postgres, I'd say just follow the recipe given in the pg_upgrade man
page. If you are working with someone's packaged version of Postgres, the packager may have provided a script or
somethingfor upgrades, in which case follow their recommendation.  Either way it's a good idea to make a backup
beforehandin case of disaster. 

Postgresql was installed during the install of centOS 6.4. Postgresql was an install option. Thus I figure I am working
with"someone's packaged version.  

No need for database backup since the backups are from a different server (9.2), which has prompted all of this. If I
copythe entire pgsql directory and thing blow up can I just replace the copied directory with its contents and be
restoredto 8.4?  

I downloaded pgdg-centos92-9.2-8.noarch.rpm for CentOS6.4 from https://yum.postgresql.org/repopackages.php#pg92. Is
theremore involved then using yum to uninstall 8.4 and then use yum to install 9.2? 

Thanks

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, November 24, 2016 10:22 AM
To: Marc Fromm <Marc.Fromm@wwu.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restore a pg_dumpall only breaks

Marc Fromm <Marc.Fromm@wwu.edu> writes:
>> A dump from 9.2 is no sure thing to restore into an 8.1 database; there may be SQL syntax in it that 8.1 doesn't
understand. Have you checked for errors during the restore? 

> After the last successful database restore from the pg_dumpall file this is displayed on the ssh session.
> psql:pg_dbs.bkp:1029100: \connect: invalid connection option "-reuse-previous"

Ah.  That option was introduced quite recently as part of a security fix.
It's no surprise 8.x doesn't recognize it.

You could probably work around this by using pg_dumpall with -g to just dump roles and tablespaces, and then
pg_dump'ingindividual databases separately.  A mite tedious and error-prone.  Or maybe use "sed" to strip out the
-reuse-previousoptions from pg_dumpall's output.  But really this is the best answer, because 8.x is long out of
support:

> I figure the best thing to do is to upgrade postgres 8.4 (not 8.1 as previously mentioned) to 9.2 on this centOS6.4
server,so it's version matches the other server. I searched for some time on how to do this and every tutorial does it
alittle different and each has different steps that the others do not have. Is there a clear step by step procedure to
dothis upgrade? 

If you're working with locally-built copies of Postgres, I'd say just follow the recipe given in the pg_upgrade man
page. If you are working with someone's packaged version of Postgres, the packager may have provided a script or
somethingfor upgrades, in which case follow their recommendation.  Either way it's a good idea to make a backup
beforehandin case of disaster. 

            regards, tom lane


Re: restore a pg_dumpall only breaks

От
Marc Fromm
Дата:
>> Or maybe use "sed" to strip out the -reuse-previous options from pg_dumpall's output

Removing " -reuse-previous=on" from the pg_dumpall file worked. I still would like to upgrade to 9.2. As with my prior
email,if there is a recipe of sorts for upgrading pg8.4 to pg9.2 that would be great. 

Thanks

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, November 24, 2016 10:22 AM
To: Marc Fromm <Marc.Fromm@wwu.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] restore a pg_dumpall only breaks

Marc Fromm <Marc.Fromm@wwu.edu> writes:
>> A dump from 9.2 is no sure thing to restore into an 8.1 database; there may be SQL syntax in it that 8.1 doesn't
understand. Have you checked for errors during the restore? 

> After the last successful database restore from the pg_dumpall file this is displayed on the ssh session.
> psql:pg_dbs.bkp:1029100: \connect: invalid connection option "-reuse-previous"

Ah.  That option was introduced quite recently as part of a security fix.
It's no surprise 8.x doesn't recognize it.

You could probably work around this by using pg_dumpall with -g to just dump roles and tablespaces, and then
pg_dump'ingindividual databases separately.  A mite tedious and error-prone.  Or maybe use "sed" to strip out the
-reuse-previousoptions from pg_dumpall's output.  But really this is the best answer, because 8.x is long out of
support:

> I figure the best thing to do is to upgrade postgres 8.4 (not 8.1 as previously mentioned) to 9.2 on this centOS6.4
server,so it's version matches the other server. I searched for some time on how to do this and every tutorial does it
alittle different and each has different steps that the others do not have. Is there a clear step by step procedure to
dothis upgrade? 

If you're working with locally-built copies of Postgres, I'd say just follow the recipe given in the pg_upgrade man
page. If you are working with someone's packaged version of Postgres, the packager may have provided a script or
somethingfor upgrades, in which case follow their recommendation.  Either way it's a good idea to make a backup
beforehandin case of disaster. 

            regards, tom lane