Обсуждение: Upgrade db format without older version of PostgreSQL
I recently upgraded PostgreSQL, and now this happens: # /etc/init.d/postgresql start An old version of the database format was found. You need to upgrade the data format before using PostgreSQL. See /usr/share/doc/postgresql-8.3.8/README.rpm-dist for more information. Unfortunately, I upgraded due to a hardware failure, and can't dump/undump, since I don't have the old PostgreSQL server anymore. Can I upgrade the data format w/o installing Postgres 7.2 (my "data/base/1/PG_VERSION" file says "7.2")? If not, can Fedora 11's "yum" install Postgres 7.2 (so I can at least do a clean install/uninstall and then re-install 8.3.8)? -- We're just a Bunch Of Regular Guys, a collective group that's trying to understand and assimilate technology. We feel that resistance to new ideas and technology is unwise and ultimately futile.
2009/9/29 Kelly Jones <kelly.terry.jones@gmail.com>
apparently your upgrade procedure was far from perfect.
no.
Don't know, but I'd rather try this:
compile 7.2 from source, bring up a temporary postgres 7.2 instance, dump it (using pg_dumpall from 8.3), and load the dump into 8.3.
I recently upgraded PostgreSQL, and now this happens:
# /etc/init.d/postgresql start
An old version of the database format was found.
You need to upgrade the data format before using PostgreSQL.
See /usr/share/doc/postgresql-8.3.8/README.rpm-dist for more information.
apparently your upgrade procedure was far from perfect.
Unfortunately, I upgraded due to a hardware failure, and can't
dump/undump, since I don't have the old PostgreSQL server anymore.
Can I upgrade the data format w/o installing Postgres 7.2 (my
"data/base/1/PG_VERSION" file says "7.2")?
no.
If not, can Fedora 11's "yum" install Postgres 7.2 (so I can at least
do a clean install/uninstall and then re-install 8.3.8)?
Don't know, but I'd rather try this:
compile 7.2 from source, bring up a temporary postgres 7.2 instance, dump it (using pg_dumpall from 8.3), and load the dump into 8.3.
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
On Tue, 2009-09-29 at 10:18 -0600, Kelly Jones wrote: > An old version of the database format was found. > You need to upgrade the data format before using PostgreSQL. > See /usr/share/doc/postgresql-8.3.8/README.rpm-dist for more > information. > > Unfortunately, I upgraded due to a hardware failure, and can't > dump/undump, since I don't have the old PostgreSQL server anymore. > > Can I upgrade the data format w/o installing Postgres 7.2 (my > "data/base/1/PG_VERSION" file says "7.2")? You need to install 7.2 from sources, and start the server, and take the dump using 8.3's pg_dump (and I'm not sure 8.3's pg_dump supports dumping 7.2 server)... Also, however, I bet 7.2 cannot be compiled on Fedora 11. The oldest PG version that I could build on Fedora 11 was 8.0, IIRC. Apart from these, dump/restore from 7.2 to 8.3 will probably not work. So you should first install the old OS version, at least to take a dump. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Вложения
Kelly Jones wrote: > I recently upgraded PostgreSQL, and now this happens: > > # /etc/init.d/postgresql start > > An old version of the database format was found. > You need to upgrade the data format before using PostgreSQL. > See /usr/share/doc/postgresql-8.3.8/README.rpm-dist for more information. > > Unfortunately, I upgraded due to a hardware failure, and can't > dump/undump, since I don't have the old PostgreSQL server anymore. > > Can I upgrade the data format w/o installing Postgres 7.2 (my > "data/base/1/PG_VERSION" file says "7.2")? > > If not, can Fedora 11's "yum" install Postgres 7.2 (so I can at least > do a clean install/uninstall and then re-install 8.3.8)? > thats going to be pretty painful, actually. first, make sure you have a backup of the postgres 7.2 data directory and everything in it. 2nd, find and build 7.2, configuring it to run in a directory like /home/pg72/ and restore a copy of your old data directory to /home/pg72/data. make sure all these files are owned by hte postgres user. edit the postgresql.conf file in that data dir to use a different port, like 5433 rather than the default 5432. manually start this 7.2 server like... # su postgres -c "/home/pg72/bin/pg_ctl start -D /home/pg72/data" now, clear the postgres 8.3.8 data directory where you tried to restore your 7.2 $PGDATA, and create a new 8.3 cluster wiht initdb, and start 8.3... # rm -rf /var/log/pgsql/data/* # service postgresql initdb # service postgresql start now, try this... # su - postgres $ pg_dumpall -p 5433 | psql and this should dump your 7.2 database using the 8.3.8 pg_dumpall tool, and restore it to the 8.3.8 database.
Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim@gunduz.org> writes: > On Tue, 2009-09-29 at 10:18 -0600, Kelly Jones wrote: >> Can I upgrade the data format w/o installing Postgres 7.2 (my >> "data/base/1/PG_VERSION" file says "7.2")? > You need to install 7.2 from sources, and start the server, and take the > dump using 8.3's pg_dump (and I'm not sure 8.3's pg_dump supports > dumping 7.2 server)... Yes, it does. I routinely test pg_dump back to 7.0. There are some issues around embedded newlines in the data with very old servers, but that's a server-side problem and using an older pg_dump won't make it better. (If you have newlines in your text data, try using pg_dump --inserts.) > Also, however, I bet 7.2 cannot be compiled on Fedora 11. The oldest PG > version that I could build on Fedora 11 was 8.0, IIRC. I think the problems you had were probably with contrib stuff. Building the core server without any extras will probably work (perhaps with a lot of warnings, but ignore those). regards, tom lane
On Tue, 2009-09-29 at 12:50 -0400, Tom Lane wrote: > > > Also, however, I bet 7.2 cannot be compiled on Fedora 11. The oldest > > PG version that I could build on Fedora 11 was 8.0, IIRC. > > I think the problems you had were probably with contrib stuff. > Building the core server without any extras will probably work > (perhaps with a lot of warnings, but ignore those). Could be -- but this is what I got with 7.2.8: make[3]: Entering directory `/home/devrim/rpm/BUILD/postgresql-7.2.8/src/backend/libpq' gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o hba.o hba.c hba.c: In function ‘ident_unix’: hba.c:885: error: storage size of ‘peercred’ isn’t known hba.c:885: warning: unused variable ‘peercred’ make[3]: *** [hba.o] Error 1 make[3]: Leaving directory `/home/devrim/rpm/BUILD/postgresql-7.2.8/src/backend/libpq' make[2]: *** [libpq-recursive] Error 2 make[2]: Leaving directory `/home/devrim/rpm/BUILD/postgresql-7.2.8/src/backend' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/devrim/rpm/BUILD/postgresql-7.2.8/src' make: *** [all] Error 2 Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Вложения
Devrim GÜNDÜZ wrote: > Also, however, I bet 7.2 cannot be compiled on Fedora 11. The oldest PG > version that I could build on Fedora 11 was 8.0, IIRC. Apart from these, > dump/restore from 7.2 to 8.3 will probably not work. > oh, thats going to be fun. fyi, the 7.2 sources are here... ftp://ftp-archives.postgresql.org/pub/source/v7.2.8/postgresql-7.2.8.tar.bz2 I'm giving a try building on centos 5 (rhel5) just for the fun of it. this uses gcc 4.1.2 by default. it also has a gcc 3.4.6 -compat version I could try if this fails. lotsa compile warnings so far. this actually seems to have worked... as user 'pierce'... $ ./configure --prefix=/home/pierce/pg72 --with-pgport=5433 $ make $ make install $ /home/pierce/pg72/bin/initdb -D /home/pierce/pg72/data $ vi /home/pierce/pg72/data/postgresql.conf ## change port=5433 $ /home/pierce/pg72/bin/pg_ctl -D /home/pierce/pg72/data -l logfile start $ /home/pierce/pg72/bin/psql -U pierce -p 5433 template1 Welcome to psql, the PostgreSQL interactive terminal. template1=# \l List of databases Name | Owner -----------+-------- template0 | pierce template1 | pierce (2 rows) template1=# select version(); version ---------------------------------------------------------------------------------------------------- PostgreSQL 7.2.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-44) (1 row) template1=# \q like, holy karap. > Apart from these, dump/restore from 7.2 to 8.3 will probably not work. thats going to be a bigger problem. dunno how you'd get around that, or how many intermediate versions youd have to go through. wild guess says, 7.2 -> 7.4 -> 8.3 may work.
Devrim GÜNDÜZ wrote: > Could be -- but this is what I got with 7.2.8: > > make[3]: Entering directory `/home/devrim/rpm/BUILD/postgresql-7.2.8/src/backend/libpq' > gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o hba.o hba.c > hba.c: In function ‘ident_unix’: > hba.c:885: error: storage size of ‘peercred’ isn’t known > hba.c:885: warning: unused variable ‘peercred’ > ... what version gcc is that?
On Tue, 2009-09-29 at 10:03 -0700, John R Pierce wrote: > what version gcc is that? 4.4.1 . It also fails at the same place with GCC 3.4.6 (compat-gcc-34) Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Вложения
Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim@gunduz.org> writes: > On Tue, 2009-09-29 at 12:50 -0400, Tom Lane wrote: >> I think the problems you had were probably with contrib stuff. > Could be -- but this is what I got with 7.2.8: [ pokes around ... ] Wow, I had forgotten exactly how frickin' ancient 7.2 is :-(. You might be able to make it go by adding #define _GNU_SOURCE 1 in src/include/port/linux.h, and adding -fno-strict-aliasing -fwrapv to CFLAGS, but yeah, trying to build it on a modern Linux system looks a bit shaky. If you do go this route, I'd recommend making sure it passes its regression tests before you risk letting it touch your real data. regards, tom lane
Hi, On Tue, 2009-09-29 at 13:36 -0400, Tom Lane wrote: > You might be able to make it go by adding #define _GNU_SOURCE 1 > in src/include/port/linux.h, and adding -fno-strict-aliasing -fwrapv > to CFLAGS, Yeah, these worked. I could build 7.2.8 on my F-11 box. > but yeah, trying to build it on a modern Linux system looks > a bit shaky. If you do go this route, I'd recommend making sure it > passes its regression tests before you risk letting it touch your real > data. geometry and horology tests are failing here. regression.diffs is attached for reference. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Вложения
Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim@gunduz.org> writes: > On Tue, 2009-09-29 at 10:03 -0700, John R Pierce wrote: >> what version gcc is that? > 4.4.1 . It also fails at the same place with GCC 3.4.6 (compat-gcc-34) It's not the compiler version that's at issue. It's that the system headers in F-11 don't define struct ucred unless you defined _GNU_SOURCE someplace. RHEL-5 is a lot closer to the build environment 7.2 is expecting. 7.3.x does still build on F-11, so it probably wouldn't be that hard to make 7.2.x go if someone with familiarity with the sources were motivated to do it. But for a novice it'd be a mighty un-fun task. I'm personally kind of wondering what the OP's system was before. Red Hat never shipped PG 7.2 at all, so it probably wasn't RHEL or CentOS or Fedora. regards, tom lane
Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= <devrim@gunduz.org> writes: > On Tue, 2009-09-29 at 13:36 -0400, Tom Lane wrote: >> You might be able to make it go by adding #define _GNU_SOURCE 1 >> in src/include/port/linux.h, and adding -fno-strict-aliasing -fwrapv >> to CFLAGS, > Yeah, these worked. I could build 7.2.8 on my F-11 box. >> but yeah, trying to build it on a modern Linux system looks >> a bit shaky. If you do go this route, I'd recommend making sure it >> passes its regression tests before you risk letting it touch your real >> data. > geometry and horology tests are failing here. regression.diffs is > attached for reference. Those are harmless/unsurprising. regards, tom lane
Devrim GÜNDÜZ wrote >> but yeah, trying to build it on a modern Linux system looks >> a bit shaky. If you do go this route, I'd recommend making sure it >> passes its regression tests before you risk letting it touch your real >> data. >> > > geometry and horology tests are failing here. regression.diffs is > attached for reference. > same here on CentOS 5.x, except the geometry test had fewer failures logged, only the "six | box" one, and the differences were .000000000000001 rounding kinda things, like -0.535533905932738 came out -0.535533905932737. the horology tests seem to be all about PST vs PDT and may well be because Mar 15 is now PDT when it didn't used to be? I'm guessing this is good enough to dump his data from.