Migrating Data Across Major Versions

From: Shawn Gennaria <sgennaria2(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Migrating Data Across Major Versions
Date: 2011-04-15 12:25:51
Message-ID: BANLkTinQdFJsC6a5JFecHnkD+bEw08YGYA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

This raised some concern, so I went back to the old server in an
attempt to run pg_dumpall to make separate files for schema-only and
data-only. At this point, I have to backtrack a bit and explain that
after the initial data dump, I discovered that I didn't have enough
room on the original machine to restore all of the 8.4 data to the
9.0.3 cluster while the old data still remained in place. So I moved
the entire 8.4 pgdata folder to an external harddrive. Unfortunately,
the mv operation was interrupted at some point and when I discovered
this, I was unable to re-run the same mv command to get the rest of
the files into the new location because it was giving me weird errors.
The solution seemed to be to use the cp command instead, so I did
that and didn't get any negative feedback. After all that occurred, I
basically changed my mind about migrating the data to 9.0.3 on the old
machine, which is when I decided to put the old 8.4 data back and
re-run pg_dumpall in an attempt to get separate files for schema and
data. I ran the mv command again to get the 8.4 pgdata folder back in
place and initially I had some issues getting the server to start. I
got sidetracked with other things at that point, but when I came back
to it a few days later, the server issue had apparently fixed itself
and 8.4 was running again. I quickly checked PgAdmin and nothing
looked obviously out of place. So I went ahead and ran the pg_dumpall
(again, from the 9.0.3 version) on the same 8.4 cluster and now ended
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?

Thanks!
sg

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Perry Smith 2011-04-15 12:45:21 Re: Normalize or not?
Previous Message Wim Bertels 2011-04-15 08:07:11 Re: function to filter out tokens (sql syntax lexical)?