8.4 Data Not Compatible with 9.0.1 Upgrade?

In response to Carlos Mennens <carlos.mennens@gmail.com>:

I did an upgrade on my database server this past weekend and the
database fails to start. I checked /var/log/postgresql and found the
reason:

[root@slave ~]# ps aux | grep postgres
root 5189 0.0 0.0 8128 956 pts/0 S+ 12:28 0:00 grep postgres

[root@slave ~]# /etc/rc.d/postgresql start
:: Starting PostgreSQL

[BUSY] server starting

[DONE]
[root@slave ~]# ps aux | grep postgres
root 5205 0.0 0.0 8128 960 pts/0 R+ 12:28 0:00 grep postgres

[root@slave ~]# tail -n 50 /var/log/postgresql.log
FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.
FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.
FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.

Does anyone know if this is a issue with PostgreSQL or with the way
Arch Linux packages the upgrade?

I can't speak for Arch Linux' upgrade setup, but going from 8.4 -> 9.0
requires that the data directory either be dumped/recreated, or ran
through the new upgrade process (which (as yet) I have no experience
with).

If the Arch Linux stuff doesn't do that automatically, then you'll have
to do it manually.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Mon, Nov 1, 2010 at 10:36 AM, Carlos Mennens
<carlos.mennens@gmail.com> wrote:

I did an upgrade on my database server this past weekend and the
database fails to start. I checked /var/log/postgresql and found the
reason:

[root@slave ~]# ps aux | grep postgres
root      5189  0.0  0.0   8128   956 pts/0    S+   12:28   0:00 grep postgres

[root@slave ~]# /etc/rc.d/postgresql start
:: Starting PostgreSQL

                 [BUSY] server starting

                 [DONE]
[root@slave ~]# ps aux | grep postgres
root      5205  0.0  0.0   8128   960 pts/0    R+   12:28   0:00 grep postgres

[root@slave ~]# tail -n 50 /var/log/postgresql.log
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.

Does anyone know if this is a issue with PostgreSQL or with the way
Arch Linux packages the upgrade?

It's always been like that. There should be a pg_migrator script or
something like that to convert the 8.4 db to 9.0

On Mon, Nov 1, 2010 at 12:45 PM, Bill Moran <wmoran@potentialtech.com> wrote:

I can't speak for Arch Linux' upgrade setup, but going from 8.4 -> 9.0
requires that the data directory either be dumped/recreated, or ran
through the new upgrade process (which (as yet) I have no experience
with).

If the Arch Linux stuff doesn't do that automatically, then you'll have
to do it manually.

I just read:

http://www.postgresql.org/docs/9.0/static/release-9-0

Sadly I blindly upgraded my database w/o doing a dump / restore so can
anyone tell me if I am dead in the water or is there a way I can
recover from this error on my part?

In response to Carlos Mennens <carlos.mennens@gmail.com>:

On Mon, Nov 1, 2010 at 12:45 PM, Bill Moran <wmoran@potentialtech.com> wrote:

I can't speak for Arch Linux' upgrade setup, but going from 8.4 -> 9.0
requires that the data directory either be dumped/recreated, or ran
through the new upgrade process (which (as yet) I have no experience
with).

If the Arch Linux stuff doesn't do that automatically, then you'll have
to do it manually.

I just read:

http://www.postgresql.org/docs/9.0/static/release-9-0

Sadly I blindly upgraded my database w/o doing a dump / restore so can
anyone tell me if I am dead in the water or is there a way I can
recover from this error on my part?

It's unlikely that anything is wrong with the data. If you downgrade
that machine back to 8.4, you should be OK. Or you could copy the
data directory to another machine that has 8.4 on it (as long as the
hardware architecture is the same)

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Mon, Nov 1, 2010 at 9:47 AM, Carlos Mennens <carlos.mennens@gmail.com> wrote:

Sadly I blindly upgraded my database w/o doing a dump / restore so can
anyone tell me if I am dead in the water or is there a way I can
recover from this error on my part?

No, but you'll want to read this document to proceed.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Mon, Nov 1, 2010 at 9:52 AM, Richard Broersma
<richard.broersma@gmail.com> wrote:

On Mon, Nov 1, 2010 at 9:47 AM, Carlos Mennens <carlos.mennens@gmail.com> wrote:

Sadly I blindly upgraded my database w/o doing a dump / restore so can
anyone tell me if I am dead in the water or is there a way I can
recover from this error on my part?

No, but you'll want to read this document to proceed.

oops: http://www.postgresql.org/docs/9.0/interactive/pgupgrade.html

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Mon, Nov 1, 2010 at 12:52 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:

oops: http://www.postgresql.org/docs/9.0/interactive/pgupgrade.html

Thanks for the URL. I will try this but I am confused how to proceed?
Can I attempt this with PostgreSQL 9.0.1-2 server installed and the
data is still 8.4 or do I need to find a way to uninstall 9.0.1-2 and
reinstall the 8.4 server?

On Mon, Nov 1, 2010 at 11:06 AM, Carlos Mennens
<carlos.mennens@gmail.com> wrote:

On Mon, Nov 1, 2010 at 12:52 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:

oops: http://www.postgresql.org/docs/9.0/interactive/pgupgrade.html

Thanks for the URL. I will try this but I am confused how to proceed?
Can I attempt this with PostgreSQL 9.0.1-2 server installed and the
data is still 8.4 or do I need to find a way to uninstall 9.0.1-2 and
reinstall the 8.4 server?

you would do it with 9.0.x installed, and there should be a program in
one of the 9.0 packages that has pg_upgrade in it.

On Mon, Nov 1, 2010 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

you would do it with 9.0.x installed, and there should be a program in
one of the 9.0 packages that has pg_upgrade in it.

So I have my 8.4.4-6 databases backed up. I don't know if I needed the
default 'postgres' database dumped but I did that one too just in
case. I then upgraded the server to 9.0.1-2 and my question is how do
you create a new database in PostgeSQL 9.0 coming from 8.4.4-6 when
the server refuses to start. I can't connect to PostgreSQL simply
because the logs tell me the data is not compatible. Am I missing
something?

In response to Carlos Mennens <carlos.mennens@gmail.com>:

On Mon, Nov 1, 2010 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

you would do it with 9.0.x installed, and there should be a program in
one of the 9.0 packages that has pg_upgrade in it.

So I have my 8.4.4-6 databases backed up. I don't know if I needed the
default 'postgres' database dumped but I did that one too just in
case. I then upgraded the server to 9.0.1-2 and my question is how do
you create a new database in PostgeSQL 9.0 coming from 8.4.4-6 when
the server refuses to start. I can't connect to PostgreSQL simply
because the logs tell me the data is not compatible. Am I missing
something?

To clarify my earlier comments, if you're going to use pg_upgrade, you
probably won't need to downgrade to 8.4. My comments about putting
8.4 back on would have be necessary if you were going to go the old
dump/restore route.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Bill Moran <wmoran@potentialtech.com> writes:

To clarify my earlier comments, if you're going to use pg_upgrade, you
probably won't need to downgrade to 8.4. My comments about putting
8.4 back on would have be necessary if you were going to go the old
dump/restore route.

Note that pg_upgrade depends on having a copy of the old postmaster
executable available (so that it can read the old catalogs). If you're
using a pre-packaged version then hopefully the packager took care of
that for you, but if you're trying to build from source then it's
something you'll have to deal with.

regards, tom lane

On Mon, Nov 1, 2010 at 2:00 PM, Bill Moran <wmoran@potentialtech.com> wrote:

To clarify my earlier comments, if you're going to use pg_upgrade, you
probably won't need to downgrade to 8.4.  My comments about putting
8.4 back on would have be necessary if you were going to go the old
dump/restore route.

I've already downgraded / dumped the databases and upgraded to the
latest version. I was then going to create the new databases however I
can't connect because of the invalid data error:

I guess I'm just missing something here...I didn't choose to go with
the 'pg_upgrade' script simply because I wasn't aware of it's location
and was worried it would dork up my data so I reverted back to 8.4.4-6
and got a clean backup of my databases. So is there nothing I can do
from my position now?

On Mon, Nov 1, 2010 at 2:27 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:

I'm guessing you are missing an initdb. Move your old data directory
somewhere else for now and do a new initdb so you can start up version 9.

When you say 'old data' can you be more specific as to the path and
possible files I need to move?

I go to '/var/lib/postgres/data/' directory however I am not sure
where from that folder structure I need to start moving files away
without breaking basic server functionality & connection data.

On 11/01/2010 11:49 AM, Carlos Mennens wrote:

On Mon, Nov 1, 2010 at 2:27 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:

I'm guessing you are missing an initdb. Move your old data directory
somewhere else for now and do a new initdb so you can start up version 9.

When you say 'old data' can you be more specific as to the path and
possible files I need to move?

I go to '/var/lib/postgres/data/' directory however I am not sure
where from that folder structure I need to start moving files away
without breaking basic server functionality& connection data.

I installed from source so my data directory is in /usr/local/pgsql ,
but here is what the layout look likes. Basically everything under
~/data can be moved and the initdb run against ~/data

postgres@ford:/usr/local/pgsql/data$ l
total 112
drwx------ 13 postgres postgres 4096 2010-11-01 11:58 .
drwxr-xr-x 7 root root 4096 2010-06-28 12:18 ..
drwx------ 13 postgres postgres 4096 2010-07-14 15:16 base
drwx------ 2 postgres postgres 4096 2010-11-01 11:59 global
drwx------ 2 postgres postgres 4096 2010-06-28 14:43 pg_clog
-rw------- 1 postgres postgres 3939 2010-06-28 14:43 pg_hba.conf
-rw------- 1 postgres postgres 1636 2010-06-28 14:43 pg_ident.conf
drwx------ 3 postgres postgres 12288 2010-11-01 11:58 pg_log
drwx------ 4 postgres postgres 4096 2010-06-28 14:43 pg_multixact
drwx------ 2 postgres postgres 4096 2010-11-01 11:58 pg_notify
drwx------ 2 postgres postgres 4096 2010-11-01 12:15 pg_stat_tmp
drwx------ 2 postgres postgres 4096 2010-06-28 14:43 pg_subtrans
drwx------ 2 postgres postgres 4096 2010-06-28 14:43 pg_tblspc
drwx------ 2 postgres postgres 4096 2010-06-28 14:43 pg_twophase
-rw------- 1 postgres postgres 4 2010-06-28 14:43 PG_VERSION
drwx------ 3 postgres postgres 4096 2010-06-28 16:17 pg_xlog
-rw------- 1 postgres postgres 17578 2010-10-21 11:28 postgresql.conf
-rw------- 1 postgres postgres 59 2010-11-01 11:58 postmaster.opts
-rw------- 1 postgres postgres 47 2010-11-01 11:58 postmaster.pid
-rw-r--r-- 1 postgres postgres 3302 2010-07-01 14:52 server.crt
-rw------- 1 postgres postgres 887 2010-07-01 14:52 server.key
-rw-r--r-- 1 postgres postgres 2094 2010-07-01 14:51 server.req

--
Adrian Klaver
adrian.klaver@gmail.com

So I am still in the dark about the entire upgrade or step up process
from 8.4.4-6 to 9.0.1-2. I have my 4 databases all backed up which I
did when my server was 8.4.4-6 using the 'pg_dump' utility. That
worked fine. So after I backed up my databases, I then upgraded the
daemon to 9.0.1-2 and from there I don't understand the process. I
obviously can't connect to the PostgreSQL database after I upgrade
because I get the error:

FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.

I have located the '/usr/bin/pg_upgrade' but I don't understand how I
go about this. I am worried because I have 12 unique database users
with unique / individual grants on specific databases. I don't want to
start from scratch so I would assume this is where the 'pg_upgrade'
script comes into play. How do I proceed from this point on? Obviously
I can't access or connect to PostgreSQL once I upgrade to 9.0.1-2 so I
assume at this time I need to execute the 'pg_upgrade' script to move
forward, right?

On Tuesday 02 November 2010 7:02:05 am Carlos Mennens wrote:

So I am still in the dark about the entire upgrade or step up process
from 8.4.4-6 to 9.0.1-2. I have my 4 databases all backed up which I
did when my server was 8.4.4-6 using the 'pg_dump' utility. That
worked fine. So after I backed up my databases, I then upgraded the
daemon to 9.0.1-2 and from there I don't understand the process. I
obviously can't connect to the PostgreSQL database after I upgrade
because I get the error:

FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.

I have located the '/usr/bin/pg_upgrade' but I don't understand how I
go about this. I am worried because I have 12 unique database users
with unique / individual grants on specific databases. I don't want to
start from scratch so I would assume this is where the 'pg_upgrade'
script comes into play. How do I proceed from this point on? Obviously
I can't access or connect to PostgreSQL once I upgrade to 9.0.1-2 so I
assume at this time I need to execute the 'pg_upgrade' script to move
forward, right?

pg_upgrade uses a different process then pg_dump/pg_restore.In order to use it
you have to have both the old cluster and new cluster directories available at
the same time, though not both servers running at the same time. See here for
details:
http://www.postgresql.org/docs/9.0/interactive/pgupgrade.html

Since you already did the pg_dump, you also have the option of using
psql/pg_restore to populate the 9.0 cluster. The choice of restore software
depends on whether you used the plain text option to pg_dump(restore with psql)
or the custom option(restore with pg_restore). Database users are global to
cluster, in order to carry them over you need to do pg_dumpall -g where the -g
specifies dump global objects only. If you have not already done that, it will
be necessary to start up the 8.4 server again.

--
Adrian Klaver
adrian.klaver@gmail.com

On 11/01/2010 02:49 PM, Carlos Mennens wrote:

On Mon, Nov 1, 2010 at 2:27 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:

I'm guessing you are missing an initdb. Move your old data directory
somewhere else for now and do a new initdb so you can start up version 9.

When you say 'old data' can you be more specific as to the path and
possible files I need to move?

I go to '/var/lib/postgres/data/' directory however I am not sure
where from that folder structure I need to start moving files away
without breaking basic server functionality& connection data.

mv /var/lib/postgres/data /var/lib/postgres/data.old

You will then have to do an initdb to create the basic 9.x databases.
You can then use psql or pg_restore depending on how you dumped
your data to restore your databases.

With fedora you use either:
/etc/init.d/postgresql initdb
or
service postgresql initdb
to initialize the 9.x database system.

HTH
--
Stephen Clark
*NetWolves*
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
http://www.netwolves.com

On Mon, Nov 1, 2010 at 1:36 PM, Carlos Mennens <carlos.mennens@gmail.com> wrote:

I did an upgrade on my database server this past weekend and the
database fails to start. I checked /var/log/postgresql and found the
reason:

[root@slave ~]# ps aux | grep postgres
root      5189  0.0  0.0   8128   956 pts/0    S+   12:28   0:00 grep postgres

[root@slave ~]# /etc/rc.d/postgresql start
:: Starting PostgreSQL

                 [BUSY] server starting

                 [DONE]
[root@slave ~]# ps aux | grep postgres
root      5205  0.0  0.0   8128   960 pts/0    R+   12:28   0:00 grep postgres

[root@slave ~]# tail -n 50 /var/log/postgresql.log
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4,
which is not compatible with this version 9.0.1.

Does anyone know if this is a issue with PostgreSQL or with the way
Arch Linux packages the upgrade?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi all,

Just upgraded PostgreSQL yesterday, from 8.4.5 to 9.0.1 on Debian.
This is not to start a flame on linux distributions but instead I just
want to stand out that the hard work done over the years by Martin
Pitt on the excellent PostgreSQL debian packages isn't sufficiently
appreciated.

Regards,

diego

On Tue, Nov 2, 2010 at 10:53 AM, Steve Clark <sclark@netwolves.com> wrote:

mv /var/lib/postgres/data  /var/lib/postgres/data.old

Before I move or rename '/var/lib/postgres/data', what version of
PostgreSQL should I be at? 8.4 or 9.0?

You will then have to do an initdb to create the basic 9.x databases.
You can then use psql or pg_restore depending on how you dumped
your data to restore your databases.

I simply ran the following command:

/usr/bin/pg_dump finance > finance.sql

With fedora you use either:
/etc/init.d/postgresql initdb
or
service postgresql initdb
to initialize the 9.x database system.

Sadly that command didn't pan out for Arch Linux:

# /etc/rc.d/postgresql initdb
usage: /etc/rc.d/postgresql {start|stop|restart}