Software updates
================

Major software updates frequently require the database to be reloaded.
Having considered this carefully, I think it too risky to let
dpkg handle this automatically with the preinst and postinst scripts. Apart
from the dangers of loss of data, a large database could delay the
installation of other packages for a long time.

If the upgrade requires a reload, the new package will conflict
with all older versions of postgresql and all versions of postgres95.
In the case of postgres95, this is an actual package Conflict.  In the
case of successive releases of postgresql, the conflict is handled
by the preinst and postinst scripts.


************************************************************************
*                    Which upgrade method to use?                      *
************************************************************************

If you already had a Debian package installed, look below for the
appropriate section.  The major distribution releases are highlighted
in asterisk boxes.

Distribution releases were:

??     rex       ?
??     buzz      ?
1.?    bo        6?
2.0    hamm      6.3.2-5
2.1    slink     6.3.2-15
2.2    potato    6.5.3-19

However, since you may have upgraded the package independently of the
main distribution, this may not be helpful.  You can find the database
version by logging in as `postgres' and doing `cat ${PGDATA}/PG_VERSION'.


Manual upgrade procedure
========================

WARNING: You will have problems if you upgrade in large steps (for
instance from 6.1 to 6.4).  This is because at each release the upstream
developers assume that you are upgrading from the immediately preceding
release.  If you upgrade at longer intervals, incompatibilities may
arise between the pg_dump of the previous version and the database
infrastructure of the new version.  In such circumstances, you will 
probably have to edit your dump file before you can restore your data.
This will make you considerably more expert in the use of regular
expressions...

Follow this procedure to dump your old database:


1) Upgrading from 6.2.1 and above to later versions of PostgreSQL
=================================================================

The preinst scripts of libpgsql and postgresql should capture some of
the executables and shared libraries of an earlier package release.  These
are stored in /usr/lib/postgresql/dumpall/<release>.  These executables
are saved so that they can be used to dump the old database.  Once your
database is up-to-date, there is no need to retain these executables and the
directory that they are saved in can be deleted.

Provided that these executables have been successfully captured, the
old database can be dumped, deleted and reloaded by the command
postgresql-dump.  This must be run by the PostgreSQL administrator,
postgres.  See the postgresql-dump manpage for full details of how to
use it.

a) pg_upgrade method 
--------------------
** This method is not usable for going from Debian 2.1 to Debian 2.2 **

For upgrades to version 6.4 and later, except across the boundary between
6.4.2 and 6.5, the best way to run postgresql-dump is:

	# su - postgres
	$ postgresql-dump -t db.out -ucil

which uses the pg_upgrade command introduced at PostgreSQL 6.4.  This
method requires only the database structure to be dumped; the data
itself is read from the old database.  This method does require
two copies of data on the disk at once, so will need to have enough
disk space.  This method cannot be used to upgrade from 6.4 or below
to 6.5 or above.  (See section (b) below.)


************************************************
*  Debian release 2.1 "slink" to 2.2 "potato"  *
************************************************

b) *** Moving from 6.4.2 or earlier to 6.5 or later ***
-------------------------------------------------------

The internal structure of tables changed at release 6.5, so it is not
possible to use pg_upgrade for a move to (or through) 6.5. You have to do
a complete dump and reload.

At 6.5, the use of the keyword `current' in CREATE RULE to refer to the
table being updated was changed to `old'.  Dump scripts produced by 6.4.2
and earlier will therefore not load correctly on 6.5, because all 
occurrences of `current.' have to be changed to `old.'.  postgresql-dump 
will do this for you if you give it the option -f.  This option passes
the dump file through "sed -e '/^CREATE RULE /s/current\./old./g'". If
this is wrong for your data but your data needs conversion, you should
dump the data, edit it and then reload it.  A close study of the manual
page for postgresql-dump is recommended.

The recommended upgrade command for <=6.4.2 to >=6.5 is

   # su - postgres
   $ postgresql-dump -t db.out -cilfdp $PGDATA/../data.save

which is nearly the same as the command for versions before 6.4.

c) before 6.4
-------------
If you have plenty of disk space you can do an automatic full dump and
restore like this (this is the method that had to be used until release
6.4 became available):

	# su - postgres
	$ postgresql-dump -t db.out -dcilp $PGDATA/../data.save

This will dump the database to db.out, in the postgres home directory,
list the dump on screen for the user to inspect and approve, destroy the
old database, create the new database with initdb and finally reload the
data from db.out into the new database.   The old database will be saved
in $PGDATA/../data.save in case anything goes wrong.


If you do not have enough space for multiple copies of your data, you
can use single options of postgresql-dump to do the dump and restore
one bit at a time.  For example:

    postgresql-dump -t /dev/st0               # dump to tape
    postgresql-dump -c -t /dev/st0            # check tape dump
    postgresql-dump -d -t /dev/st0 -i -l      # destroy the old database
                                              # create a new one and
                                              # load the dump

It is highly recommended, of course, that you should have a backup of
${PGDATA} before destroying the database!


2) Upgrading from postgres95 or any non-Debianised version of Postgres
======================================================================

If the preinstallation script finds a data/base directory where it
expects to put its own, or if it finds an executable called postgres
in /usr/bin, it will assume there is an older version installed
and will attempt to capture the old executables as described above.
If it succeeds, it will put them in /usr/lib/postgresql/dumpall/unknown.

Otherwise the installation should fail, and you will have to do a manual
dump and restore before you can continue, like this:

Check in the FAQ, in this documentation, whether and how to dump your data.
Some older versions require you to load intermediate versions in 
order to preserve data integrity through to the current version.
For example, you cannot go directly from Postgres95 1.08 to PostgreSQL 6;
you must dump and reload into Postgres95 1.09 first.

Older versions of the dump_all command were liable to lose data about
SQL permissions and users.  You may have to live with this.

Make sure you have an up-to-date backup; be wary of using normal Linux backup
utilities while the database is being vacuumed, or you may find on recovery
that your database is corrupt.  

$ su - postgres               # become the Postgres superuser
$ pg_dumpall >target_file     # target_file may be a tape or on disk

When this procedure is complete, read through the resulting archive
to ensure that it is correct and can be read to the end.  The dump
format is ASCII text. As at release 6.2.1, pg_dumpall loses table
ownerships and permissions.  At 6.5 it has fairly few problems.

When you are completely satisfied that you have a readable backup of
your database:

(PGDATA should be set; if it isn't, set it thus:

$ export PGDATA=/path_to_database/data
$ export PGLIB=/usr/lib/postgresql
)
$ $PGLIB/bin/cleardbdir
(if, for some reason, that didn't work:
  $ rm -rf $PGDATA/*
)

This will destroy the old Postgres95 or PostgreSQL database, so don't
do it until you are absolutely certain about your data!

Finish this orgy of destruction by removing the old package:

# dpkg --remove postgres95 libpq1 postgres95-dev postgres95-doc

or find the various files and delete them if Postgres wasn't Debianised
before.

When the database has been destroyed, create a new one with initdb.

Start the postmaster (as root):

# /etc/init.d/postgresql start

Finally, reload your database:

# su - postgres
$ psql -e <target_file

(If you are unlucky, you may have to do more or less extensive editing
of target_file before you can reload it.)


Co-existing with local installations
====================================

If someone has created a local copy of PostgreSQL, in /usr/local/pgsql,
for example, the two copies will clash, because both will be listening
on port 5432.  One of the postmasters will be unable to start, because
it will be blocked by the exisiting socket /tmp/.s.PGSQL.5432, which the
other will have opened.

If you do not wish to delete the local copy, you must make sure that the
two copies use different databases ($PGDATA) and listen on different
ports.  You can either reconfigure and recompile the local version, or
make sure it and its front-ends alway runs with PGPORT set to the
desired port, or you can set the value of PGPORT in
/etc/postgresql/postmaster.init.  Do not use 5341, because that is
used by the installation scripts for special purposes.  Do not use any
port that is used by any TCP/IP service.