Debian PostgreSQL - migration between database formats
======================================================

When the database format changes, as frequently happens when the minor
release number changes, the database must be dumped and reinitialised.

The procedure is explained in the INSTALL instructions from upstream
and may briefly be described as follows:

1. Use pg_dumpall to dump the database.

2. Destroy the old database

3. Use initdb to create a new database structure.

4. Use psql to read in the dump from the old database.


Automatic upgrade by the postinst script
========================================

The postinst asks for permission to attempt the upgrade.  If you say
yes, it runs a script called postgresql-dump which attempts to
automate all the procedure described above.  If this is successful,
no further action is necessary.

If automatic upgrading fails, you must do a manual upgrade.


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:

upgrading from <= 6.4.2
	# su - postgres
	$ postgresql-dump -t db.out -dcifvlp $PGDATA/../data.save

upgrading from >= 6.5
	# su - postgres
	$ postgresql-dump -t db.out -dcivlp $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!


Problems
========

In order to be able to do the upgrade, we need to use binaries that
match the old database.  This used to be done by the preinst script,
which saved the binaries before overwriting them.

With the advent of apt, however, this proved inadequate, since apt
will often remove a package rather than overwrite it, in order to cope
with interlinked dependencies.  If this happens, the previous binaries
are no longer available to be preserved.  As from 6.5.3-16 and 7.0.2-xx,
the prerm script is used instead to preserve old binaries.

In one way or another, therefore, the installation should have preserved
the executables and shared libraries of an earlier package release.  These
are stored in /usr/lib/postgresql/dumpall/<database_version>.  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.

If the executables are not preserved, you will have to reinstall a
package of the appropriate release level to read your database.  You
should run pg_dumpall to an output file of your choice before reinstalling
the latest version of the package.


pg_upgrade
==========

There used to be a program called pg_upgrade, which could upgrade data
from one version to the next without the need for a full data dump.
This program does not work at version 7.1 and has been disabled by
the upstream developers.   The -u option (which invoked pg_upgrade)
has been removed from postgresql-dump at 7.1.

If you need to use pg_dumpall to dump your database, be sure not to use
the -s option (to dump the schema only).  You must dump all the data
as well.

(A new, experimental version of pg_upgrade is included in 7.2.  The Debian
upgrade procedure will not attempt to use it until it has been thoroughly
tested.)


Wrong program version saved
===========================

People who follow unstable may have installed beta versions of
PostgreSQL.  These sometimes suffer database format changes which
are not reflected by a version number change.  As a result, the
prerm scripts may see software already present in
/usr/lib/postgresql/dumpall/<database_version> and therefore fail to
update it with the correct version.  It is well therefore to delete
this directory as soon as your database is up-to-date with the
software, to avoid problems in the future.

If you hit this problem, you need to go back and reinstall the
previous package version corresponding to your database, so that you
can do a dump.


Rule syntax
===========

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 and later, 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.


UNICODE databases
=================

There is a problem in 7.0 (at least) with dumping some UNICODE characters.
Due to bad interaction between components, it is possible for 7.0 pg_dump
(which has to be used to dump the 7.0 format database) to truncate data
lines at some characters -- for example the Norwegian 0370 (o with a line
through it).  Such a database needs to be dumped with the -i option to
pg_dumpall, so as to create the data by INSERT statements rather than by COPY.
Since this is much slower, it cannot be used as the default.

If this problem affects you, you should do a manual dump with pg_dumpall -i
before upgrading your postgresql packages.


Bugs in old versions of pg_dump
===============================

There are bugs in old versions of pg_dump that cause the output not
to be sufficient to recreate the database exactly.  In successive
releases these bugs have been very substantially reduced, but some still
may exist.  In the process of migration, you will suffer the bugs of the
version from which you are upgrading, and this new release cannot do
anything about them.

Do not delete the saved copy of your database until you are sure that all 
your data has been correctly transferred.  If you find errors in
reloading your database, you will probably be able to cure them by
editing the ASCII dump to correct the SQL commands it contains.


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.

If it fails, 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.  (If you actually
need to do this, I can no longer help you; these software versions are
not just old - they are antediluvian!)

Older versions of the pg_dumpall 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 had fairly few problems; at 7.1
it is greatly improved.

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 existing socket /var/run/.s.PGSQL.5432, which the
other will have opened.  Alternatively, it may be using an unaltered
upstream version with the socket in /tmp/.s.PGSQL.5432.  In this case there
is even more scope for confusion - which database you get depends on which
version of the libpq shared library is loaded by the front-end program.

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 always run with PGPORT set to the
desired port, or you can set the value of PORT in
/etc/postgresql/postgresql.conf.  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 other TCP/IP service.