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