[Pkg-postgresql-public] Bug#708712: Upgrading cluster fails because of quoting failure

[Marek Kubica]

Package: postgresql-common
Version: 134wheezy3

Trying to update my PostgreSQL database from 8.4 (Debian 6.0) to 9.0
(Debian 7.0) causes a failure when using pg_upgradecluster:

$ pg_upgradecluster 8.4 main
Stopping old cluster...
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster (configuration: /etc/postgresql/9.1/main,
data: /var/lib/postgresql/9.1/main)...
Moving configuration file /var/lib/postgresql/9.1/main/postgresql.conf
to /etc/postgresql/9.1/main...
Moving configuration file /var/lib/postgresql/9.1/main/pg_hba.conf
to /etc/postgresql/9.1/main...
Moving configuration file /var/lib/postgresql/9.1/main/pg_ident.conf
to /etc/postgresql/9.1/main...
Configuring postgresql.conf to use port 5433...
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs... Fixing hardcoded library paths for
stored procedures...
Upgrading database foobar...
pg_dump: SQL command failed pg_dump: Error message from server: ERROR:
syntax error at or near "new" LINE 1: COPY public.homepage_series (id,
name, vendor_id, new, anima... ^ pg_dump: The command was: COPY
public.homepage_series (id, name, vendor_id, new, animation_id,
"position") TO stdout; pg_restore: [custom archiver] unexpected end of
file Re-enabling connections to the old cluster... Re-enabling
connections to the new cluster... Error during cluster dumping,
removing new cluster

Searching for this error yields this:

<http://www.postgresql.org/message-id/9673.1321412697@sss.pgh.pa.us>

Basically it says that new used to be a keyword but is not anymore, so
the 9.x pg_dump does not quote it. But when interfacing with 8.4
databases, it fails.

Solution: quote everything.

Now pg_dump and pg_dumpall both have the --quote-all-identifiers option
and when modifying pg_upgradecluster to use this option, this
particular error goes away.

So, please add this option to the pg_upgradecluster source.

regards,
Marek