Software updates ================ Major software updates frequently require the database to be reloaded. Having considered this carefully, I used to consider 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. With release 7.0 and the availability of the pg_upgrade procedure, I have changed my view on this and the package will now attempt the upgrade, if the person running the install permits. 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-26 The next release will be: 2.3 woody 7.1 (once 7.1 gets into testing) 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'. ************************************************************* * Debian release 2.2 "potato" to ?2.3? "woody" (automatic) * ************************************************************* Release 6.5.3-17, 7.0beta4 and later - automatic upgrade ======================================================== From 6.5.3-17 and 7.0beta4-1, there is provision for automatic upgrade using postgresql-dump as described below. The procedure works like this: 1. The prerm script for the old postgresql is run first of all. It dumps the database schema to /usr/lib/postgresql/dumpall/db.out and writes the default database encoding to /usr/lib/postgresql/dumpall/default.encoding. In order to make sure we don't attempt to upgrade the wrong version it also writes the current database version to /usr/lib/postgresql/dumpall/db.out.version The prerm also copies the binaries needed to dump this version to /usr/lib/postgresql/dumpall//. This should overcome recent problems where the ordering of package installations and deletions led to the new package's binaries being put there instead. This copying is done by the first prerm to run of postgresql, libpgsql2.1 and postgresql-client; it is also attempted by the new preinst scripts of the same packages. Whichever is first of all these to run, copies the binaries; the later running scripts do not overwrite existing files. 2. postgresql's postinst script detects the dumped schema left by the old package's prerm. It attempts to run pg_upgrade to create the new database, create the schema and copy the old database rows into the newly created schema. If this procedure is successful, the postmaster will be restarted and the data in your database will be available immediately. As a mark of its success, postgresql-dump deletes /usr/lib/postgresql/dumpall/default.encoding, to prevent any future attempts to do the same upgrade. If automatic update fails, you must do the upgrade manually. If the postgresql-dump partly ran, it may have got as far as creating the new database (in $PGDATA) and will have renamed the old one to $PGDATA/../data.old. You should see what the situation is by examining the file $PGDATA/PG_VERSION. The version required by the current software can be found by running `/usr/lib/postgresql/bin/pg_version /tmp', which will report the required version number. 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/. 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. 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. ************************************************ * 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 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