Thread: PostgreSQL migrating from 9.2 to 9.5 with data dir on different volume
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!