Re: Correct pg_dumpall Syntax

On Wed, 2008-06-18 at 08:55 -0700, Rich Shepard wrote:
> On Wed, 18 Jun 2008, Andrew Sullivan wrote:
>
> > Can't do it. Start the old postmaster with -D /usr4/pgsql_old/data, and
> > then use pg_dumpall against that backend.
>
> Andrew,
>
> When I try, I see:
>
> postgres(at)salmo:/var/lib/pgsql$ postgres -D /usr4/pgsql_old/data
> FATAL: database files are incompatible with server
> DETAIL: The data directory was initialized by PostgreSQL version 8.1, which
> is not compatible with this version 8.3.3.
>
> Am I correct that the 8.1.4 executables have been replaced with the 8.3.3
> ones during the upgrade?
>
> Perhaps I should restore /var/lib/pgsql/data from the backup tape, as well
> as /usr/share/postgresql/, /usr/include/postgresql/, and /usr/bin/postgres?

Your upgrade process that I saw originally has put you in a bad state.
This is what I suggest:

download 8.1.13:

http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fsource%
2Fv8.1.13%2Fpostgresql-8.1.13.tar.gz

unpack; then:

./configure --prefix=/tmp/pg813; make install
cd /tmp/pg813
bin/pg_ctl -D /usr4/pgsql_old/data start
cd /
/usr/bin/pg_dumpall -U <user> > mydatabase.sql

At this point you will have an 8.3 dump of your 8.1 data.

Then you can reinitialize a new cluster with initdb here:

/var/lib/pgsql/data (you will have to remove the old one)

Then restore as normal using psql -U postgres < mydatabase.sql

Joshua D. Drake