pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrestore a pg_dumpall only breaks
7+ messages / 2 participants
[nested] [flat]
* restore a pg_dumpall only breaks
@ 2016-11-24 02:00 Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 02:04 ` Re: restore a pg_dumpall only breaks Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Marc Fromm @ 2016-11-24 02:00 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: restore a pg_dumpall only breaks
2016-11-24 02:00 restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
@ 2016-11-24 02:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-11-24 02:19 ` Re: restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 06:07 ` Re: restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2016-11-24 02:04 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
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 "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.
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: restore a pg_dumpall only breaks
2016-11-24 02:00 restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 02:04 ` Re: restore a pg_dumpall only breaks Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-11-24 02:19 ` Marc Fromm <Marc.Fromm@wwu.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Marc Fromm @ 2016-11-24 02:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
> 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 "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.
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: restore a pg_dumpall only breaks
2016-11-24 02:00 restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 02:04 ` Re: restore a pg_dumpall only breaks Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-11-24 06:07 ` Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 18:22 ` Re: restore a pg_dumpall only breaks Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Marc Fromm @ 2016-11-24 06:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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 a little different and each has different steps that the others do not have. Is there a clear step by step procedure to do this 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 "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.
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: restore a pg_dumpall only breaks
2016-11-24 02:00 restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 02:04 ` Re: restore a pg_dumpall only breaks Tom Lane <tgl@sss.pgh.pa.us>
2016-11-24 06:07 ` Re: restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
@ 2016-11-24 18:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-11-24 23:33 ` Re: restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-25 05:36 ` Re: restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2016-11-24 18:22 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
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 a little different and each has different steps that the others do not have. Is there a clear step by step procedure to do this 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: restore a pg_dumpall only breaks
2016-11-24 02:00 restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 02:04 ` Re: restore a pg_dumpall only breaks Tom Lane <tgl@sss.pgh.pa.us>
2016-11-24 06:07 ` Re: restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 18:22 ` Re: restore a pg_dumpall only breaks Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-11-24 23:33 ` Marc Fromm <Marc.Fromm@wwu.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Marc Fromm @ 2016-11-24 23:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
> 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.
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 copy the entire pgsql directory and thing blow up can I just replace the copied directory with its contents and be restored to 8.4?
I downloaded pgdg-centos92-9.2-8.noarch.rpm for CentOS6.4 from https://yum.postgresql.org/repopackages.php#pg92. Is there more 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'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 a little different and each has different steps that the others do not have. Is there a clear step by step procedure to do this 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: restore a pg_dumpall only breaks
2016-11-24 02:00 restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 02:04 ` Re: restore a pg_dumpall only breaks Tom Lane <tgl@sss.pgh.pa.us>
2016-11-24 06:07 ` Re: restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 18:22 ` Re: restore a pg_dumpall only breaks Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-11-25 05:36 ` Marc Fromm <Marc.Fromm@wwu.edu>
1 sibling, 0 replies; 7+ messages in thread
From: Marc Fromm @ 2016-11-25 05:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
>> 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'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 a little different and each has different steps that the others do not have. Is there a clear step by step procedure to do this 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2016-11-25 05:36 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-11-24 02:00 restore a pg_dumpall only breaks Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 02:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-11-24 02:19 ` Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 06:07 ` Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-24 18:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-11-24 23:33 ` Marc Fromm <Marc.Fromm@wwu.edu>
2016-11-25 05:36 ` Marc Fromm <Marc.Fromm@wwu.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox