Thread: Move databases from Pg 7.2.1 to 8.3.x
I can't avoid it any longer and have to move an old RT2 installation to a new machine. (I'll upgrade to RT3 on that machine.) The plan calls for a pg_dump using my workstation using CREATE the database and INSERTS (-C -D) . Then use psql to run the script created above to build the database on the new system. I have the time to fiddle about but would appreciate any suggestions to make it run smooth(er). \\||/ Rod --
On Tue, 2009-04-14 at 09:58 -0700, Roderick A. Anderson wrote: > I can't avoid it any longer and have to move an old RT2 installation to > a new machine. (I'll upgrade to RT3 on that machine.) > > The plan calls for a pg_dump using my workstation using CREATE the > database and INSERTS (-C -D) . Then use psql to run the script created > above to build the database on the new system. > > I have the time to fiddle about but would appreciate any suggestions to > make it run smooth(er). O.k. :) Don't do it that way. You want to install the new version of PostgreSQL and use the version of pg_dump that comes with that machine. Secondly there is no reasons to do -D (which is actually -d btw). Just do a stock pg_dumpall. Lastly on the new machine you will likely need to create your cluster with --no-locale otherwise I can pretty much guarantee the restore won't work from that far back. Alternatively you can try to cleanse the data with something like iconv. Joshua D. Drake > > > \\||/ > Rod > -- > -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
"Joshua D. Drake" <jd@commandprompt.com> writes:
> On Tue, 2009-04-14 at 09:58 -0700, Roderick A. Anderson wrote:
>> The plan calls for a pg_dump using my workstation using CREATE the
>> database and INSERTS (-C -D) . Then use psql to run the script created
>> above to build the database on the new system.
> You want to install the new version of PostgreSQL and use the version of
> pg_dump that comes with that machine.
Check. In theory the other way should work, but usually the newer
version of pg_dump is a better bet (particularly for such an old version
--- seven more years of bug fixes, eh?)
> Secondly there is no reasons to do -D (which is actually -d btw). Just
> do a stock pg_dumpall.
It might actually be a good idea to use --column-inserts for this.
I don't recall all the details right at the moment, but ISTR there were
some funnies with respect to corner-case COPY data syntax back in the
dim past, and it might be that that includes 7.2. This was a
server-side issue and so just using a newer pg_dump wouldn't fix it.
In any case it'd be a really good idea to see if you can manage a dry
run before doing it live. You can expect some application compatibility
issues across such a large version jump, even after you get past any
difficulties in moving the data. So it'd be smart to do an import into
a test server and see how your apps work against it before you do it
for real.
regards, tom lane
Roderick A. Anderson wrote:
> I can't avoid it any longer and have to move an old RT2 installation
> to a new machine. (I'll upgrade to RT3 on that machine.)
>
> The plan calls for a pg_dump using my workstation using CREATE the
> database and INSERTS (-C -D) . Then use psql to run the script
> created above to build the database on the new system.
>
> I have the time to fiddle about but would appreciate any suggestions
> to make it run smooth(er).
make sure pg_hba.conf on the old machine allows you to connect from the
new machine's IP address as the user postgres (this may require setting
a database password for the postgres user on the old machine if the
'host' authentication method so specifies), then from the new machine,
after doing an initdb and starting the new version of postgres, and
while logged on as the unix postgres user...
pg_dumpall -h oldmachine -U postgres | psql
John R Pierce wrote: > Roderick A. Anderson wrote: <snip /> > make sure pg_hba.conf on the old machine allows you to connect from the > new machine's IP address as the user postgres (this may require setting > a database password for the postgres user on the old machine if the > 'host' authentication method so specifies), then from the new machine, > after doing an initdb and starting the new version of postgres, and > while logged on as the unix postgres user... Been there done that and own may tee-shirts. :-) The old system has been running for so long without any significant issues I've forgotten if there ever was a password. I'm hoping adding/changing the postgres password doesn't break anything else. No screams so far! > pg_dumpall -h oldmachine -U postgres | psql Well, no pg_dumpall going to happen -- only pg_dump. The whole cluster isn't going to the new machine. But my command line from my workstation goes like this. pg_dump -U postgres -h db.domain.tld -D -C -f Desktop/rt2.sql rt2 \\||/ Rod --
Joshua D. Drake wrote: > On Tue, 2009-04-14 at 09:58 -0700, Roderick A. Anderson wrote: >> I can't avoid it any longer and have to move an old RT2 installation to >> a new machine. (I'll upgrade to RT3 on that machine.) >> >> The plan calls for a pg_dump using my workstation using CREATE the >> database and INSERTS (-C -D) . Then use psql to run the script created >> above to build the database on the new system. >> >> I have the time to fiddle about but would appreciate any suggestions to >> make it run smooth(er). > > O.k. :) Don't do it that way. > > You want to install the new version of PostgreSQL and use the version of > pg_dump that comes with that machine. Thanks Josh. See my later postings. Quickly I'm using pg_dump from Pg 8.3.4 and I'm not doing the whole cluster, just selected databases. > Secondly there is no reasons to do -D (which is actually -d btw). Just > do a stock pg_dumpall. > > Lastly on the new machine you will likely need to create your cluster > with --no-locale otherwise I can pretty much guarantee the restore won't > work from that far back. Alternatively you can try to cleanse the data > with something like iconv. I'll know pretty soon. :-) \\||/ Rod --
Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> On Tue, 2009-04-14 at 09:58 -0700, Roderick A. Anderson wrote: >>> The plan calls for a pg_dump using my workstation using CREATE the >>> database and INSERTS (-C -D) . Then use psql to run the script created >>> above to build the database on the new system. > >> You want to install the new version of PostgreSQL and use the version of >> pg_dump that comes with that machine. Right. I left that out. I have a more recent copy of Pg (or maybe just the utilities) on my workstation -- actually 8.3.4. Pretty recent. > Check. In theory the other way should work, but usually the newer > version of pg_dump is a better bet (particularly for such an old version > --- seven more years of bug fixes, eh?) > >> Secondly there is no reasons to do -D (which is actually -d btw). Just >> do a stock pg_dumpall. Well there are other databases in the cluster for other applications that I will not be moving so I'm doing those databases I need one at a time. > It might actually be a good idea to use --column-inserts for this. > I don't recall all the details right at the moment, but ISTR there were > some funnies with respect to corner-case COPY data syntax back in the > dim past, and it might be that that includes 7.2. This was a > server-side issue and so just using a newer pg_dump wouldn't fix it. Right. Therefore the "-D". > In any case it'd be a really good idea to see if you can manage a dry > run before doing it live. You can expect some application compatibility > issues across such a large version jump, even after you get past any > difficulties in moving the data. So it'd be smart to do an import into > a test server and see how your apps work against it before you do it > for real. Oh yeah! That is the plan. \\||/ Rod --