Thread: migrate data 6.5.3 -> 8.3.1
Hello everybody. We have a dusty old server, FreeBSD 3.3-RELEASE, PostgreSQL 6.5.3. I need to migrate four DBs from old server to new server (FreeBSD 6.2, PostgreSQL 8.3.1). I've tried to do pg_dump on old server, transfer it to new one and do `psql -f dumpfile dbname`. Well, no surprise, then i see a lot of warning and errors. Here they are (only unique error messages, they're duplicated actually): psql:dump:389: WARNING: aggregate attribute "sfunc2" not recognized psql:dump:389: WARNING: aggregate attribute "stype2" not recognized psql:dump:389: WARNING: aggregate attribute "initcond2" not recognized psql:dump:389: ERROR: function int84div(bigint) does not exist psql:dump:390: ERROR: function int4div(integer) does not exist psql:dump:391: ERROR: function int2div(smallint) does not exist psql:dump:392: ERROR: function float4div(real) does not exist psql:dump:393: ERROR: function float8div(double precision) does not exist psql:dump:394: ERROR: function cash_div_flt8(money) does not exist psql:dump:395: ERROR: type "timespan" does not exist psql:dump:396: ERROR: function numeric_div(numeric) does not exist psql:dump:410: ERROR: function int4larger(abstime, abstime) does not exist psql:dump:422: ERROR: function int4smaller(abstime, abstime) does not exist psql:dump:413: ERROR: type "datetime" does not exist psql:dump:429: ERROR: aggregate stype must be specified How can i safely do this migration? Dumps of these four DBs is about 250Megs in sum. -- alexander lunyov
On Thu, Aug 14, 2008 at 9:58 AM, alexander lunyov <lan@zato.ru> wrote: > Hello everybody. > > We have a dusty old server, FreeBSD 3.3-RELEASE, PostgreSQL 6.5.3. > I need to migrate four DBs from old server to new server (FreeBSD 6.2, > PostgreSQL 8.3.1). Just an FYI: I advised Alexander to post here, because I thought some of the devs might have older pg installs/dump tools and might be able to give some advice. David Blewett
When upgrading, you use the pg_dump from the new version to dump the old database. Then it can take care of incidental changes during the process. I think that the mailing list archives have articles on upgrading from v6.5. I do not think that you can go straight from v6.5 to v8.3. You will almost certainly need to use v7.2-7.4 as a intermediate step, maybe v8.0 will work, check the list archives. Good luck. Cheers, Ken On Thu, Aug 14, 2008 at 10:34:11AM -0400, David Blewett wrote: > On Thu, Aug 14, 2008 at 9:58 AM, alexander lunyov <lan@zato.ru> wrote: > > Hello everybody. > > > > We have a dusty old server, FreeBSD 3.3-RELEASE, PostgreSQL 6.5.3. > > I need to migrate four DBs from old server to new server (FreeBSD 6.2, > > PostgreSQL 8.3.1). > > Just an FYI: I advised Alexander to post here, because I thought some > of the devs might have older pg installs/dump tools and might be able > to give some advice. > > David Blewett > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Thanks for the tip. I want to try new pg_dump to connect to old server, but i can't - old postgres doesn't listening to network socket. Why postgres 6.5.3 not binding to network socket? It started with this line: su pgsql -c '/usr/local/pgsql/bin/postmaster -d 5 -p 5432 -S -o "-e -F" -D /usr/local/pgsql/data' >/var/log/postgres.log 2>&1 & There is no errors in logfile about network socket, and postgres not listening to network: # sockstat | grep 5432 # While postgres is up and running # ps ax | grep postgres 47034 ?? Is 0:00.03 /usr/local/pgsql/bin/postmaster -d 5 -p 5432 -S -o -e -F -D /usr/local/pgsql/data (postgres) And here's the logfile: FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0] binding ShmemCreate(key=52e2c1, size=1001472) Kenneth Marshall wrote: > When upgrading, you use the pg_dump from the new version to > dump the old database. Then it can take care of incidental > changes during the process. I think that the mailing list > archives have articles on upgrading from v6.5. I do not think > that you can go straight from v6.5 to v8.3. You will almost > certainly need to use v7.2-7.4 as a intermediate step, maybe > v8.0 will work, check the list archives. Good luck. > > Cheers, > Ken > > On Thu, Aug 14, 2008 at 10:34:11AM -0400, David Blewett wrote: >> On Thu, Aug 14, 2008 at 9:58 AM, alexander lunyov <lan@zato.ru> wrote: >>> Hello everybody. >>> >>> We have a dusty old server, FreeBSD 3.3-RELEASE, PostgreSQL 6.5.3. >>> I need to migrate four DBs from old server to new server (FreeBSD 6.2, >>> PostgreSQL 8.3.1). >> Just an FYI: I advised Alexander to post here, because I thought some >> of the devs might have older pg installs/dump tools and might be able >> to give some advice. >> >> David Blewett >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers >> > -- alexander lunyov
I think that you use to much new pg_dump. I'm not sure but my expectation is
that latest pg_dump version does not support too old PG. The problem there could
be network protocol. Currently only version 3 and 2 is supported. I recommend
you to jump to 7.x (7.2) first and use 7.2 pg_dump and after that to 7.4 and
8.3. IIRC there could be problem also with foreign keys.
Zdenek
alexander lunyov napsal(a):
>
> Thanks for the tip.
> I want to try new pg_dump to connect to old server, but i can't - old
> postgres doesn't listening to network socket. Why postgres 6.5.3 not
> binding to network socket? It started with this line:
>
> su pgsql -c '/usr/local/pgsql/bin/postmaster -d 5 -p 5432 -S -o "-e -F"
> -D /usr/local/pgsql/data' >/var/log/postgres.log 2>&1 &
>
> There is no errors in logfile about network socket, and postgres not
> listening to network:
>
> # sockstat | grep 5432
> #
>
> While postgres is up and running
>
> # ps ax | grep postgres
> 47034 ?? Is 0:00.03 /usr/local/pgsql/bin/postmaster -d 5 -p 5432
> -S -o -e -F -D /usr/local/pgsql/data (postgres)
>
> And here's the logfile:
>
> FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
> binding ShmemCreate(key=52e2c1, size=1001472)
>
>
> Kenneth Marshall wrote:
>> When upgrading, you use the pg_dump from the new version to
>> dump the old database. Then it can take care of incidental
>> changes during the process. I think that the mailing list
>> archives have articles on upgrading from v6.5. I do not think
>> that you can go straight from v6.5 to v8.3. You will almost
>> certainly need to use v7.2-7.4 as a intermediate step, maybe
>> v8.0 will work, check the list archives. Good luck.
>>
>> Cheers,
>> Ken
>>
>> On Thu, Aug 14, 2008 at 10:34:11AM -0400, David Blewett wrote:
>>> On Thu, Aug 14, 2008 at 9:58 AM, alexander lunyov <lan@zato.ru> wrote:
>>>> Hello everybody.
>>>>
>>>> We have a dusty old server, FreeBSD 3.3-RELEASE, PostgreSQL 6.5.3.
>>>> I need to migrate four DBs from old server to new server (FreeBSD 6.2,
>>>> PostgreSQL 8.3.1).
>>> Just an FYI: I advised Alexander to post here, because I thought some
>>> of the devs might have older pg installs/dump tools and might be able
>>> to give some advice.
>>>
>>> David Blewett
>>>
>>> --
>>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>
>>
>
>
--
Zdenek Kotala Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql
On Fri, Aug 15, 2008 at 9:54 AM, alexander lunyov <lan@zato.ru> wrote: > Thanks for the tip. > I want to try new pg_dump to connect to old server, but i can't - old > postgres doesn't listening to network socket. Why postgres 6.5.3 not > binding to network socket? It started with this line: (Forgot the list, sorry for the double email, Alexander) Do you have something like tcpip_socket in your postgresql.conf (I don't know if it was in 6.5)? If so, you have to set it to true to make PostgreSQL listen on the network. As for the upgrade path, I'll go with: - dump with 7.3 pg_dump, insert your dump into a 7.3 db and run adddepends contrib shipped with 7.3 on your db (it should fix the foreign key problem reported by Zdenek); - then dump your 7.3 db with 8.3 pg_dump and insert your dump into your 8.3 server. -- Guillaume
Guillaume Smet wrote: >> I want to try new pg_dump to connect to old server, but i can't - old >> postgres doesn't listening to network socket. Why postgres 6.5.3 not >> binding to network socket? It started with this line: > Do you have something like tcpip_socket in your postgresql.conf (I > don't know if it was in 6.5)? If so, you have to set it to true to > make PostgreSQL listen on the network. i didn't find anything like postgresql.conf on old server. Right now i'm tried to start 6.5.3 on windows (downloaded binary from ftp archive on postgresql.org, installed last cygwin) with the data dir from old server, but there's errors: D:\pgsql\bin>d:\pgsql\bin\postgres -p 5432 -D d:\\pgsql\\data 6 [main] postgres 2820 _cygtls::handle_exceptions: Exception: STATUS_ACCESS_VIOLATION 426 [main] postgres 2820 open_stackdumpfile: Dumping stack trace to postgres.exe.stackdump 27692 [main] postgres 2820 _cygtls::handle_exceptions: Exception: STATUS_ACCESS_VIOLATION 28338 [main] postgres 2820 _cygtls::handle_exceptions: Error while dumping state (probably corrupted stack) dump: Exception: STATUS_ACCESS_VIOLATION at eip=0044EBE3 eax=00000000 ebx=00506A90 ecx=00000000 edx=00000001 esi=00506AD1 edi=004E83FC ebp=02862BBC esp=02862BA8 program=d:\pgsql\bin\postgres.exe, pid 2820, thread main cs=001B ds=0023 es=0023 fs=003B gs=0000 ss=0023 Stack trace: Frame Function Args 02862BBC 0044EBE3 (00000045, 02862BF8, 00000000, 00000000) 02868BF8 004E5A93 (00000001, 004E83C8, 00000000, 0286CC80) 02868C0C 004E8460 (04870150, 0286CCF8, 0044F5A4, 00000000) 0286CC80 004B3518 (00000005, 04870150, 00000005, 04870150) 0286CCB4 0044F698 (00000005, 04870150, 04870090, 60030000) 0286CDA4 610060D8 (00000000, 0286CDDC, 61005450, 0286CDDC) 61005450 61004416 (0000009C, A02404C7, E8611021, FFFFFF48) 27692 [main] postgres 2820 _cygtls::handle_exceptions: Exception: STATUS_ACCESS_VIOLATION 28338 [main] postgres 2820 _cygtls::handle_exceptions: Error while dumping state (probably corrupted stack) Also tried to compile 6.5.3 from source on newer freebsd - configure doesn't do Makefiles, errors are: creating GNUmakefile sed: 35: conftest.s1: unescaped newline inside substitute pattern and for all Makefiles this message, and Makefiles are empty, so i can't compile it. :( > As for the upgrade path, I'll go with: > - dump with 7.3 pg_dump, insert your dump into a 7.3 db and run > adddepends contrib shipped with 7.3 on your db (it should fix the > foreign key problem reported by Zdenek); > - then dump your 7.3 db with 8.3 pg_dump and insert your dump into > your 8.3 server. Ok, that's understood, thanks (thank you too, Zdenek). But how can i connect with newer pg_dump to this old server, if it's doesn't make a socket to connect to? -- С уважением Александр Лунев МП РТК
alexander lunyov wrote: > Guillaume Smet wrote: >>> I want to try new pg_dump to connect to old server, but i can't - old >>> postgres doesn't listening to network socket. Why postgres 6.5.3 not >>> binding to network socket? It started with this line: Maybe you should just dump schema and data separately with your old pg_dump tool, then rework the schema for 8.3 manually. Regards, Andreas
Andreas Pflug wrote: >>>> I want to try new pg_dump to connect to old server, but i can't - old >>>> postgres doesn't listening to network socket. Why postgres 6.5.3 not >>>> binding to network socket? It started with this line: > > Maybe you should just dump schema and data separately with your old > pg_dump tool, then rework the schema for 8.3 manually. I can do this, but i don't know how to rework it. psql:dump:389: WARNING: aggregate attribute "sfunc2" not recognized psql:dump:389: WARNING: aggregate attribute "stype2" not recognized psql:dump:389: WARNING: aggregate attribute "initcond2" not recognized psql:dump:389: ERROR: function int84div(bigint) does not exist psql:dump:390: ERROR: function int4div(integer) does not exist psql:dump:391: ERROR: function int2div(smallint) does not exist psql:dump:392: ERROR: function float4div(real) does not exist psql:dump:393: ERROR: function float8div(double precision) does not exist psql:dump:394: ERROR: function cash_div_flt8(money) does not exist psql:dump:395: ERROR: type "timespan" does not exist psql:dump:396: ERROR: function numeric_div(numeric) does not exist psql:dump:410: ERROR: function int4larger(abstime, abstime) does not exist psql:dump:422: ERROR: function int4smaller(abstime, abstime) does not exist psql:dump:413: ERROR: type "datetime" does not exist psql:dump:429: ERROR: aggregate stype must be specified If i understand it right, much of this errors are about AGGREGATEs (they are a part of schema): CREATE AGGREGATE avg ( BASETYPE = int8, SFUNC1 = int8pl, STYPE1 = int8, INITCOND1 = '', SFUNC2 = int4inc, STYPE2 = int4, INITCOND 2 = '0', FINALFUNC = int84div ); CREATE AGGREGATE avg ( BASETYPE = int4, SFUNC1 = int4pl, STYPE1 = int4, INITCOND1 = '', SFUNC2 = int4inc, STYPE2 = int4, INITCOND 2 = '0', FINALFUNC = int4div ); CREATE AGGREGATE avg ( BASETYPE = int2, SFUNC1 = int2pl, STYPE1 = int2, INITCOND1 = '', SFUNC2 = int2inc, STYPE2 = int2, INITCOND 2 = '0', FINALFUNC = int2div ); CREATE AGGREGATE avg ( BASETYPE = float4, SFUNC1 = float4pl, STYPE1 = float4, INITCOND1 = '', SFUNC2 = float4inc, STYPE2 = float4 , INITCOND2 = '0.0', FINALFUNC = float4div ); CREATE AGGREGATE avg ( BASETYPE = float8, SFUNC1 = float8pl, STYPE1 = float8, INITCOND1 = '', SFUNC2 = float8inc, STYPE2 = float8 , INITCOND2 = '0.0', FINALFUNC = float8div ); CREATE AGGREGATE avg ( BASETYPE = money, SFUNC1 = cash_pl, STYPE1 = money, INITCOND1 = '', SFUNC2 = float8inc, STYPE2 = float8, I NITCOND2 = '0.0', FINALFUNC = cash_div_flt8 ); CREATE AGGREGATE avg ( BASETYPE = timespan, SFUNC1 = timespan_pl, STYPE1 = timespan, INITCOND1 = '', SFUNC2 = float8inc, STYPE2 = float8, INITCOND2 = '0.0', FINALFUNC = timespan_div ); CREATE AGGREGATE avg ( BASETYPE = numeric, SFUNC1 = numeric_add, STYPE1 = numeric, INITCOND1 = '', SFUNC2 = numeric_inc, STYPE2 = numeric, INITCOND2 = '0', FINALFUNC = numeric_div ); So, i have to replace these float4div to some new equivalent? And where can i find those equivalents for all these functions and special words, that "doesn't exists"? -- alexander lunyov
On Fri, Aug 15, 2008 at 11:42 AM, alexander lunyov <lan@zato.ru> wrote: > i didn't find anything like postgresql.conf on old server. Right now i'm > tried to start 6.5.3 on windows (downloaded binary from ftp archive on > postgresql.org, installed last cygwin) with the data dir from old server, > but there's errors: Add the -i option to your startup command line. You should then be able to connect using the network. That said, you should also be able to connect using the unix domain socket using -h /path/to/domain option. -- Guillaume
alexander lunyov wrote: > Andreas Pflug wrote: >>>>> I want to try new pg_dump to connect to old server, but i can't - old >>>>> postgres doesn't listening to network socket. Why postgres 6.5.3 not >>>>> binding to network socket? It started with this line: >> >> Maybe you should just dump schema and data separately with your old >> pg_dump tool, then rework the schema for 8.3 manually. > > I can do this, but i don't know how to rework it. I wonder if you need these self defined aggregates at all, most or all of them are in 8.3 already. Regards, Andreas
Guillaume Smet wrote: > On Fri, Aug 15, 2008 at 11:42 AM, alexander lunyov <lan@zato.ru> wrote: >> i didn't find anything like postgresql.conf on old server. Right now i'm >> tried to start 6.5.3 on windows (downloaded binary from ftp archive on >> postgresql.org, installed last cygwin) with the data dir from old server, >> but there's errors: > > Add the -i option to your startup command line. You should then be > able to connect using the network. Thanks a lot! It did a trick, so now i can connect to it. I followed your advice and installed 7.3 client and tried to dump and get this error: JAIL@incubator@root # pg_dump -U ira -h 192.168.0.3 itt_user pg_dump: [archiver (db)] connection to database "itt_user" failed: ERROR: MultiByte strings (MB) must be enabled to use this function I searched for meaning of this error, and found solution here: http://archives.postgresql.org/pgsql-general/2000-09/msg00489.php "Recompile your 7.0.2 without --enable-multibyte option." but in 7.3 configure no such option. -- alexander lunyov
alexander lunyov <lan@zato.ru> writes:
> I want to try new pg_dump to connect to old server, but i can't - old
> postgres doesn't listening to network socket.
It won't work anyway: modern versions of pg_dump are only designed to
work with servers back to 7.0. I see from the rest of the thread that
you tried to bludgeon it into submission, but I'd very strongly
recommend that you abandon that approach and use 6.5's pg_dump.
A further suggestion is that you use -d or even -D option on the dump.
I think there have been some corner-case changes in COPY data format
since 6.5 days; which might or might not bite you, but why take the
chance ...
regards, tom lane
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> I wonder if you need these self defined aggregates at all, most or all
> of them are in 8.3 already.
They aren't "self defined" in 6.5 either. I think what is happening
is that he's trying to force a 7.x pg_dump to dump from the 6.5 server
(with -i no doubt), and it's just tremendously confused about what's
what and what it should dump.
regards, tom lane
Tom Lane wrote: >> I wonder if you need these self defined aggregates at all, most or all >> of them are in 8.3 already. > > They aren't "self defined" in 6.5 either. So i can't just delete those AGGREGATEs? > I think what is happening > is that he's trying to force a 7.x pg_dump to dump from the 6.5 server > (with -i no doubt), and it's just tremendously confused about what's > what and what it should dump. These errors occurs when i'm trying to restore dump from 6.5 on 8.3. -- alexander lunyov
Tom Lane wrote: > alexander lunyov <lan@zato.ru> writes: >> I want to try new pg_dump to connect to old server, but i can't - old >> postgres doesn't listening to network socket. > > It won't work anyway: modern versions of pg_dump are only designed to > work with servers back to 7.0. I see from the rest of the thread that > you tried to bludgeon it into submission, but I'd very strongly > recommend that you abandon that approach and use 6.5's pg_dump. Ok, i already have the dumps made with 6.5 pg_dump, but what should i do with those errors on AGGREGATEs? Do they really exist in 8.3, so i can just cut them off from dumps and happily restore dumps without them? > A further suggestion is that you use -d or even -D option on the dump. > I think there have been some corner-case changes in COPY data format > since 6.5 days; which might or might not bite you, but why take the > chance ... Thank you, i'll redo dumps with this option just to make sure everything is fine, but despite errors on AGGREGATEs and some types errors all data was restored correctly even without -d/-D option. But i'll redo them anyway. -- alexander lunyov
Tom Lane wrote:
> A further suggestion is that you use -d or even -D option on the dump.
> I think there have been some corner-case changes in COPY data format
> since 6.5 days; which might or might not bite you, but why take the
> chance ...
%/usr/local/pgsql/bin/pg_dump -D itt_user > itt_user.dump
Backend sent D message without prior T
Backend sent D message without prior T
...
... (about 2 screens of same messages)
...
Backend sent D message without prior T
Backend sent D message without prior T
and then it hangs.
Then i've tried it with -d option:
%/usr/local/pgsql/bin/pg_dump -d itt_user > itt_user.dump
Killed
I didn't killed pg_dump, so i think it was killed by system after
pg_dump grows out of some system limit. Size of itt_user.dump is
something about 2Mb (1974272), last strings in that file are:
INSERT INTO "ip_log" VALUES ('1.1.1.1','user1',300000);
INSERT INTO "ip_log" VALUES ('1.1.1.2','user2',500000);
INSERT INTO "ip_log" VALUES
I crossed my fingers for those dumps i did previously to work.
--
alexander lunyov
alexander lunyov <lan@zato.ru> writes:
> Tom Lane wrote:
>>> I wonder if you need these self defined aggregates at all, most or all
>>> of them are in 8.3 already.
>>
>> They aren't "self defined" in 6.5 either.
> So i can't just delete those AGGREGATEs?
>> I think what is happening
>> is that he's trying to force a 7.x pg_dump to dump from the 6.5 server
>> (with -i no doubt), and it's just tremendously confused about what's
>> what and what it should dump.
> These errors occurs when i'm trying to restore dump from 6.5 on 8.3.
I took a trip down memory lane looking at the 6.5 pg_dump code ...
the way it decides whether an object is built-in or user-defined is
by looking at its OID, using the rule that any OID less than template1's
OID must be built in. Which actually ought to mostly work. I speculate
that template1 got dropped and recreated sometime during this
installation's history. That would still not produce the observed
symptoms, except that the "OID" comparisons are all done using signed
integer comparisons. So if the new template1 had an OID past 2 billion,
pretty much everything would look like it was user-defined. Were you
getting bogus duplicate type and operator definitions as well as aggregates?
Can we see the results of "select oid, datname from pg_database" in
the 6.5 installation? There might be some other candidate you could
rename to template1 to get this to work better.
Of course, since you got the data migrated you might not care anymore.
regards, tom lane
Tom Lane wrote: > Of course, since you got the data migrated you might not care anymore. That's what i've done: 1. pg_dump database from 6.5.3 2. iconv from windows-1251 charset to utf-8. 3. cutted all AGGREGATEs 4. succesefully imported all data to 8.3.1 without errors. Seems to me everything now working as it should. Thanks everyone :) -- alexander lunyov