PostgreSQL Upgrade – How to Upgrade Postgres 9.3 to 9.4 After Ubuntu Upgrade

postgresqlpostgresql-9.3postgresql-9.4upgrade

I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this:

sudo pg_dropcluster 9.4 main --stop
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

However, after running the second command I get the following error:

Error: could not get cluster locales.

How do I fix this?
(PS Sorry if this is a stupid question, and please let me know what additional information is required)

PS: locale (lc_collate) returns en_US.UTF-8

Best Answer

I had the same error, I figured that the error was happening during shutdown of old cluster, it actually caused the cluster not to shut down fully.

What worked for me was stopping the old cluster manually and only then performing an upgrade.

sudo service postgresql stop
sudo pg_upgradecluster 9.3 main

Related Solutions

Postgresql – Trying to set up new data_directory on Postgres 9.3

Ubuntu and Debian use pg_wrapper to manage PostgreSQL. It's in the postgresql-common package.

(As a result, this answer doesn't apply to RHEL/CentOS/Fedora).

If there's no data of value, you can just use pg_dropcluster (part of pg_wrapper) to drop (delete) your old datadir. This will destroy all data in it. Then use pg_createcluster to make a new one in /data. That's simplest, but of course it's destructive.

If you want to retain the data, you must:

Ensure that the file system on the new storage is compatible with PostgreSQL. Do not attempt to put PostgreSQL on FAT32 / vfat. I wouldn't attempt to use NTFS on Linux either.
Create every directory up to the location you want for the new datadir.
Ensure that the postgres user has at least x rights on all directories from the data dir down to the / directory, especially including the mount point. You may want to chmod a+x each directory along the path, or chgrp postgres and chmod g+x it.
stop PostgreSQL
mv the data directory, as shown by pg_lsclusters, to the desired new location. If you copy it instead, use cp -aRv to make sure permissions are preserved correctly. Don't just move all contents, move the directory its self.
Edit /etc/postgresql/mypgversion/postgresql.conf and change data_directory to point to the new data directory location.
Start PostgreSQL

I think the mistake you made was, in bold above, that you failed to stop PostgreSQL before moving the datadir. It won't stop the server because it isn't sure it'd be killing the right process, so it refuses to act. You must:

sudo pkill -u postgres

then make sure that all PostgreSQL processes have exited (very important) by making sure that this returns no results:

ps -u postgres

and only then attempt to start the server again.

Never delete postmaster.pid.

Fixing Connection Issues After Installing PostgreSQL 9.4 and Uninstalling 9.3

I resolve the problem changin the port from 5433 to 5432

Best Answer

Related Solutions

Postgresql – Trying to set up new data_directory on Postgres 9.3

Fixing Connection Issues After Installing PostgreSQL 9.4 and Uninstalling 9.3

Related Question