[Pkg-postgresql-public] Bug#708712: Upgrading cluster fails because of quoting failure
marek at xivilization.net
Fri May 17 22:02:12 UTC 2013
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,
Moving configuration file /var/lib/postgresql/9.1/main/postgresql.conf
Moving configuration file /var/lib/postgresql/9.1/main/pg_hba.conf
Moving configuration file /var/lib/postgresql/9.1/main/pg_ident.conf
Configuring postgresql.conf to use port 5433...
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs... Fixing hardcoded library paths for
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:
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.
More information about the Pkg-postgresql-public