PostgreSQL migrating from 9.2 to 9.5 with data dir on different volume

From: Joey Ayap <joey(at)cybranding(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: PostgreSQL migrating from 9.2 to 9.5 with data dir on different volume
Date: 2016-04-24 20:35:09
Message-ID: CADvCsYE71QOZohzB=Lf6VFYZVq7Gq-K7NPOMTEcjAy6_P7EBpw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)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(at)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!

Browse pgsql-admin by date

  From Date Subject
Next Message 周正中 (德歌) 2016-04-25 01:44:17 回复:[ADMIN] pgadmin select slow than psql command much
Previous Message Gary Stainburn 2016-04-22 10:52:37 Re: Server deleted then restored - now postgresql will not start