Обсуждение: restore a pg_dumpall only breaks
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
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