Re: [HACKERS] migrate data 6.5.3 - 8.3.1
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] migrate data 6.5.3 - 8.3.1
Tom Lane wrote: alexander lunyov [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] migrate data 6.5.3 - 8.3.1
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',30);
INSERT INTO ip_log VALUES ('1.1.1.2','user2',50);
INSERT INTO ip_log VALUES
I crossed my fingers for those dumps i did previously to work.
--
alexander lunyov
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] migrate data 6.5.3 - 8.3.1
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] migrate data 6.5.3 - 8.3.1
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 21 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 [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] migrate data 6.5.3 - 8.3.1
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\bind:\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= ebx=00506A90 ecx= edx=0001 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= ss=0023 Stack trace: Frame Function Args 02862BBC 0044EBE3 (0045, 02862BF8, , ) 02868BF8 004E5A93 (0001, 004E83C8, , 0286CC80) 02868C0C 004E8460 (04870150, 0286CCF8, 0044F5A4, ) 0286CC80 004B3518 (0005, 04870150, 0005, 04870150) 0286CCB4 0044F698 (0005, 04870150, 04870090, 6003) 0286CDA4 610060D8 (, 0286CDDC, 61005450, 0286CDDC) 61005450 61004416 (009C, A02404C7, E8611021, FF48) 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? -- С уважением Александр Лунев МП РТК -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] migrate data 6.5.3 - 8.3.1
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] migrate data 6.5.3 - 8.3.1
Guillaume Smet wrote: On Fri, Aug 15, 2008 at 11:42 AM, alexander lunyov [EMAIL PROTECTED] 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: [EMAIL PROTECTED]@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
