PostgreSQL migrating from 9.2 to 9.5 with data dir on different volume - Mailing list pgsql-admin

We have a problem in upgrading postgresql 9.2 cluster to 9.5 using pg_upgradecluster, because the data directory is on a different volume from the root one.

At the beginning, we just tried:

# pg_upgradecluster 9.2 main --method=upgrade --link

But it failed because our data directory for 9.2 is on a different volume mounted on /data:

Could not create hard link between old and new data directories: Invalid cross-device link

In link mode the old and new data directories must be on the same file system volume.

Later we found out that we could tell the upgrader to put the new version on the same volume like this:

# pg_upgradecluster 9.2 main /data/postgresql/9.5/main --method=upgrade --link

This worked on a test installation with the same volumes and directory structure, but it failed when we tried it again in production. This was the output :

root@myserver ~ # pg_upgradecluster 9.2 main /data/postgresql/9.5/main --method=upgrade --link

Disabling connections to the old cluster during upgrade...

Restarting old cluster with restricted connections...

Stopping old cluster...

Creating new cluster 9.5/main ...

 config /etc/postgresql/9.5/main

 data   /data/postgresql/9.5/main

 locale en_US.UTF-8

 port   5433

Error: could not symlink configuration file

Error during cluster dumping, removing new cluster

Searching around it looks like maybe the first attempt at upgrading created these symlinks:

lrwxrwxrwx  1 postgres postgres     36 Apr 17 08:32 pg_hba.conf -> /etc/postgresql/9.2/main/pg_hba.conf

lrwxrwxrwx  1 postgres postgres     38 Apr 17 08:32 pg_ident.conf -> /etc/postgresql/9.2/main/pg_ident.conf

lrwxrwxrwx  1 postgres postgres     40 Apr 17 08:32 postgresql.conf -> /etc/postgresql/9.2/main/postgresql.conf

We also found a reference (http://postgresql.nabble.com/Problem-pg-upgradecluster-from-9-1-to-9-3-tp5829359p5829495.html) that suggests it could be an issue with an older version of postgresql-common we have installed - this is the full list of our packages:

root@myserver ~ # dpkg -l | grep postgres

ii  pgdg-keyring                         2014.1                          all          keyring for apt.postgresql.org

rc  postgres-xc                          1.1-2ubuntu2                    amd64        write-scalable, synchronous multi-master, transparent PostgreSQL cluster

ii  postgresql-9.2                       9.2.9-1.pgdg14.04+1             amd64        object-relational SQL database, version 9.2 server

ii  postgresql-9.5                       9.5.2-1.pgdg14.04+1             amd64        object-relational SQL database, version 9.5 server

ii  postgresql-client-9.2                9.2.9-1.pgdg14.04+1             amd64        front-end programs for PostgreSQL 9.2

ii  postgresql-client-9.5                9.5.2-1.pgdg14.04+1             amd64        front-end programs for PostgreSQL 9.5

ii  postgresql-client-common             154.pgdg14.04+1                 all          manager for multiple PostgreSQL client versions

ii  postgresql-common                    154.pgdg14.04+1                 all          PostgreSQL database-cluster manager

ii  postgresql-contrib-9.5               9.5.2-1.pgdg14.04+1             amd64        additional facilities for PostgreSQL

ii  postgresql-server-dev-9.2            9.2.14-1.pgdg14.04+1            amd64        development files for PostgreSQL 9.2 server-side programming

ii  postgresql-server-dev-9.5            9.5.2-1.pgdg14.04+1             amd64        development files for PostgreSQL 9.5 server-side programming

So the question is:

- Should we upgrade postgresql-common using

# sudo apt-get install postgresql-common=173.pgdg15.10+1

? Is it safe even if we still have 9.2 running?

- Should we also manually delete the symlinks

pg_hba.conf -> /etc/postgresql/9.2/main/pg_hba.conf

pg_ident.conf -> /etc/postgresql/9.2/main/pg_ident.conf

postgresql.conf -> /etc/postgresql/9.2/main/postgresql.conf

Or the latest version of postgresql-common should be able to proceed without that?

Thanks!