Re: 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(dot)terry(dot)jones(at)gmail(dot)com>

> 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(dot)rembialkowski(at)gmail(dot)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(at)gunduz(dot)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(at)gunduz(dot)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(at)gunduz(dot)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(at)gunduz(dot)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.