[Pkg-postgresql-public] Bug#768098: Upgrade Instructions - POSSIBLE DATA LOSS

Michael Evans michael.evans at nor-consult.com
Tue Nov 4 23:11:48 UTC 2014

Package: postgresql

Version: 9.4~beta3-3

Severity: critical


The provided upgrade instructions, if run exactly, lead to data loss


When following the provided instructions, the current working directory is
not changed.

Files are saved to the current working directory.

It is /extremely/ likely that the postgres user does not have write access
to said current working directory (often it is /root ).


The instructions should be modified to store and read files in ~ postgres/
OR include a cd ~postgres (to ensure that the correct directory is


Alternatively, a multi-instruction pre/post commands with && separating
operations should be used, to force an error to occur the first time
revealing the need to modify the provided commands to administrator taste.


The provided example is particularly dangerous because it /looks/ at a
glance like it will work and that status messages might be ignorable (during
the work of copy/pasting or retyping lines).





Current, wrong, upgrade instructions.


The on-disk format of the PostgreSQL 9.4 data files has changed between

beta2 and beta3 (and as a consequence, the catalog version number). For that

reason, existing PostgreSQL 9.4 clusters need to be dumped using the old

package version, and reloaded after upgrading the packages.


The postgresql-9.4 package will now refuse to upgrade because version 9.4

clusters exist on the system.


Per default, a "main" cluster is created. Run "pg_lsclusters" to check if

other clusters exists, and repeat the steps below appropriately.


To resolve the situation, before upgrading, execute:

# su - postgres

$ pg_lsclusters

$ pg_ctlcluster 9.4 main start

$ pg_dumpall --cluster 9.4/main | gzip > 9.4-main.dump.gz

$ cp -a /etc/postgresql/9.4/main 9.4-main.config

$ pg_dropcluster 9.4 main --stop


Then after the upgrade, execute:

# su - postgres

$ pg_createcluster 9.4 main

$ cp 9.4-main.config/* /etc/postgresql/9.4/main

$ pg_ctlcluster 9.4 main start

$ zcat 9.4-main.dump.gz | psql -q

$ rm -rf 9.4-main.config 9.4-main.dump.gz


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/attachments/20141104/d244599c/attachment.html>

More information about the Pkg-postgresql-public mailing list