Re: Need Help Recovering from Botched Upgrade Attempt

Klint Gore wrote:
> Rich Shepard wrote:
>> Despite trying to be careful, I managed to mess up the upgrade from
>> -8.1.4
>> to -8.3.3 on my Slackware-11.0 server/workstation. I expect that someone
>> here will see my error and point me in the right direction to recover a
>> working dbms.
>>
>> Here's what I did:
>>
>> 1.) As a user, I ran pg_dumpall on version 8.1.4 and had that
>> written to
>> /usr4/postgres-backups/.
>>
>> 2.) Created /usr4/pgsql_old/, and copied all of /var/lib/pgsql/
>> there ('cp
>> -a /var/lib/pgsql/* .')

I hope you mean cp -aR , because you need those subdirectories if you're
ever going to try to use the _old copy. Even if you actually did a
recursive copy, if you really copied the data directories with the DB
server running and without executing:

select pg_start_backup('migrate');

or similar before starting the copy then you're going to have problems
using that data. You can copy a working postgresql instance's data
directories, but only if you've enabled WAL logging and you tell Pg
about it so it can write appropriate markers for recovery.

It would probably have been better to:

- pg_dumpall

- STOP THE 8.1 DATABASE SERVER and make sure it's stopped (no postmaster
or postgres processes hanging around).

- Make the old DB server binaries non-executable with chmod and/or
remove them from the PATH

- mv /var/lib/pgsql to /usr4/pgsql_old

- /path/to/8.3/bin/initdb -D /var/lib/pgsql/data

- Adjust postgresql.conf and pg_hba.conf as required

- Start the 8.3 server

- pg_restore all databases from dumps

Right now, you probably need to make REALLY sure you've put a copy of
those dumps somewhere safe, because I suspect your _old copy will be
useless. Then use 8.3's initdb on a new, empty directory, verify that
the config files are correct, and start the 8.3 server.

--
Craig Ringer