Interrupted pg_dump / pg_restore Upgrade
I just became involved in a scenario wherein a migration between
releases (8.1.x -> 8.2) using pg_dumpall piped to psql (per section
23.5 of the 8.2 docs) was interrupted based on duration of the
procedure. The interruption was green lit because it was determined
that the data had been migrated and that indexes and constraints were
still to come (indexes were actually mid-way). A decision was made to
go ahead and move forward with the 8.2 database with the intention of
rebuilding indexes and other constraints manually.
My big question is: Is there anything that happens late in the game
in a pg_dumpall that affects system catalogs or other non-data
internals in any critical ways that would make an interrupted
pg_dumpall | psql sequence unstable?
There are a number of irregularities turning up with the upgraded
database, and I'm trying to rule out as many root causes as possible.
The new database is 8.2 (as were all the client utilities used in the
migration), built from source, running on Solaris:
SunOS x41-xl-01.int 5.10 Generic_118855-19 i86pc i386 i86pc
--
Thomas F. O'Connell
optimizing modern web applications
: for search engines, for usability, and for performance :
http://o.ptimized.com/
615-260-0005
"Thomas F. O'Connell" <tf@o.ptimized.com> writes:
My big question is: Is there anything that happens late in the game
in a pg_dumpall that affects system catalogs or other non-data
internals in any critical ways that would make an interrupted
pg_dumpall | psql sequence unstable?
There's quite a lot of stuff that happens after the data load, yes.
One thought that comes to mind is that permissions aren't
granted/revoked until somewhere near the end. But why don't you
look at the output of "pg_dumpall -s" and find out for yourself
what got lost?
regards, tom lane
On Jan 4, 2007, at 7:03 PM, Tom Lane wrote:
"Thomas F. O'Connell" <tf@o.ptimized.com> writes:
My big question is: Is there anything that happens late in the game
in a pg_dumpall that affects system catalogs or other non-data
internals in any critical ways that would make an interrupted
pg_dumpall | psql sequence unstable?There's quite a lot of stuff that happens after the data load, yes.
One thought that comes to mind is that permissions aren't
granted/revoked until somewhere near the end. But why don't you
look at the output of "pg_dumpall -s" and find out for yourself
what got lost?
Yeah, now that I think about it, though, everything that pg_dumpall
produces is SQL or DDL, so unless it does anything involving
preservation of system catalogs that is critical, I'm somewhat less
concerned about this particular issue.
I still intend to review the schema diff, but I think there are some
other issues that need investigation as well.
Thanks for the tip.
--
Thomas F. O'Connell
optimizing modern web applications
: for search engines, for usability, and for performance :
http://o.ptimized.com/
615-260-0005