Обсуждение: 8.4 Data Not Compatible with 9.0.1 Upgrade?

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?

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 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 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 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}

On Tuesday 2. November 2010 22.37.33 Carlos Mennens wrote:
> 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 should be at 9.0. The important thing is that you must start the new
installation initdb with an empty  data directory, or you'll inevitably get
the complaints about incompatibility with the old files. It's quite logical,
really, if you think about it.

> > 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

That's only the first part of the upgrade process. Then you must prepare an
empty directory to acommodate your data, do an initdb, and then populate the
new directory from your dump file.

I think that the Gentoo method works great: In the /var/lib/postgres
directory, we just create a new subirectory named /8.3, /8.4, /9.0, and so on,
and then a new /data directory under each one. That way, you can safely remove
the old data when you've confirmed that the new version actually works.

regards,
Leif B. Kristensen

Carlos Mennens, 02.11.2010 22:37:
> Before I move or rename '/var/lib/postgres/data', what version of
> PostgreSQL should I be at? 8.4 or 9.0?

Actually both, because pg_upgrade needs the binaries of the old *and* new version.

On 11/02/2010 02:54 PM, Thomas Kellerer wrote:
> Carlos Mennens, 02.11.2010 22:37:
>> Before I move or rename '/var/lib/postgres/data', what version of
>> PostgreSQL should I be at? 8.4 or 9.0?
>
> Actually both, because pg_upgrade needs the binaries of the old *and*
> new version.
>
>
>

Part of the confusion Carlos is experiencing is that he is caught
between two upgrade suggestions. At this point he is most of the way to
doing it the traditional way, dump/restore. While pg_upgrade could be of
use, it does not solve the immediate problem, which how to restore the
dump file :)

--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver, 02.11.2010 23:23:
>>> Before I move or rename '/var/lib/postgres/data', what version of
>>> PostgreSQL should I be at? 8.4 or 9.0?
>>
>> Actually both, because pg_upgrade needs the binaries of the old
>> *and* new version.
>>
> Part of the confusion Carlos is experiencing is that he is caught
> between two upgrade suggestions. At this point he is most of the way
> to doing it the traditional way, dump/restore. While pg_upgrade
> could be of use, it does not solve the immediate problem, which how
> to restore the dump file :)

Ah, right.

But on the other hand, he only dumped a single database which will not
include e.g. users and roles.

So if he needs to restore users and privileges from the original 8.4
installation there is no way around re-installing the 8.4 binaries.

Regards
Thomas

On 02/11/2010 21:37, Carlos Mennens wrote:

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

That's the daemon start/stop script. You need something like this:

   /path/to/pg/binaries/initdb /path/to/data/dir

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie