[TriLUG] Help with postgresql upgrade

[Chander Ganesan]

Andrew Perrin wrote:
> Greetings all-
>
> Running postgresql under debian, my standard apt-get upgrade upgraded me
> from 8.3 to 8.4. As a result, I no longer have access to the databases
> that were created under 8.3. Typically I would use pg_upgradecluster to
> fix this problem; however, the upgrade also removed 8.3, and so I have no
> access to these databases! Reinstalling 8.3 does not restore the
> access, although it does appear that the raw files are still there.
>
> I do have regular tape backups of the databases, but am having trouble
> getting them to read. So I would love advice on one (or both) of the
> following questions:
>
> 1.) Is there a way to "reconnect" the 8.3 server, after reinstalling it,
> to the raw files, so that the databases are there? I will then do a
> pg_upgradecluster and be happy; or, alternatively, 
Sure.  The following command should do it (make sure you stop 8.4 before 
trying to start 8.3):

[path to 8.3 pg_ctl version]/pg_ctl -D [directory of old cluster files] 
start

Incidentally, I'm not familiar with pg_upgradecluster (it's likely a 
debian supplied tool?), but generally, the easiest way to upgrade is to 
do install the pg_dump version that comes with 8.4, dump the old (8.3 in 
your case) database with it, and then install 8.4 and re-load the database.

Alternately, you can use pg_migrator, which will do an in-place upgrade 
from 8.3 to 8.4 for you.
> 2.) Can someone help with the restore process from tape? The command my
> script uses to write the backups to tape is:
>         pg_dump -F t <databasename> | bzip2 -c | dd of=/dev/nst0 
> obs=512 conv=sync
You are aware that you aren't backing up lots of important things (such 
as user account information, tablespace information, etc), with that 
command, right?

-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Expert PostgreSQL, PostGIS, and other Open Source software delivered worldwide.