Обсуждение: 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

> 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

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

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

> 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

>> 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