Re: Migrating Data Across Major Versions - Mailing list pgsql-general

On Fri, Apr 15, 2011 at 9:32 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On Friday, April 15, 2011 5:25:51 am Shawn Gennaria wrote:
>> Hi all,
>>
>> I'm in the process of migrating our data from 8.4 to 9.0.3 on a
>> different server, both running Ubuntu.  My initial attempt involved
>> running pg_dumpall (from the 9.0.3 version) on the entire 8.4 cluster,
>> putting all of the schemas, data and everything into one giant file.
>> Jumping ahead, I attempted to restore that file to the 9.0.3 installed
>> on the new server and it's still running now, but I noticed early on
>> that it spat out error messages about schemas not existing.  Does the
>> pg_dumpall know to create the schemas first, before it creates all the
>> stored functions and tables?  Being that this is the recommended
>> method from the docs, I'm confused why it would yield such errors.
>
> What where the error mesages?
> Did you do the restore as a Postgres superuser?
> Do the schemas in fact exist in the 9.0.3 database?

Yes, it's running as a postgres superuser.  Unfortunately I didn't
dump the output to a file so I could read it after it exceeds the
terminal's buffer.

It just dawned on me that I may have misinterpreted the messages.  I
just took a look through the early parts of the file I'm using to
restore everything and noticed that many of my stored functions
contain references to other stored functions.  Naturally, one wouldn't
be able to create a function unless all dependent referenced functions
were created first.  It would be rather time-consuming to sort that
out manually and re-add each function in the correct order.  Does
anyone have experience working that out with some kind of tool?  Does
postgres have anything built-in to help with this?

>
>>
>> up with two files.  The reason I'm concerned now is because my
>> original file containing everything was 208GB, but now my schema-only
>> file is 2.9MB and my data-only file is 167GB.  Is this evidence that
>> my data may have been corrupted due to the incomplete mv operation
>> from before, or could there be some other explanation as to why the
>> sizes are so different?
>
> Have you done any normal work on the 8.4 database that could have caused the
> amount of data to decrease i.e DELETE from ?
> I guess some of this might be further explained by the clarification of the
> issues you had restarting the 8.4 server.

No modifications were made to the 8.4 cluster after I put the pgdata
folder back in place.  All I did was quickly login with PgAdmin to
verify that it wasn't completely empty and then I logged out.  I
wasn't thorough about it and didn't even run a query.  I do have logs
in my pgdata/pg_log folder from each day.  Would those help track down
issues with getting the server to start, or are they only used once
the server's already up?  I took a look through the recent ones, and
the only thing that looks odd from that time period are repeating
messages:

FATAL: unrecognized configuration parameter "application_name"

In either case, the issue seems to have worked itself out after the
server was rebooted.

>
>>
>> Thanks!
>> sg
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>