So you just upgraded to Debian Jessie, and now how do you upgrade postgres? It’s not too bad, and a nice tool called pg_upgradecluster does the work for you. Check it out!
1. First, install the newer postgresql version, if you have not already.
$ sudo apt-get install postgresql-9.4
2. Drop default created main cluster of the newer postgres 9.4
Installing a newer version of postgres on Debian creates a default main cluster which prevents pg_upgradecluster from working.
VERY CAREFULLY, drop this newly created main cluster as below to have a clean slate for pg_upgradecluster to function.
Be extremely careful not to drop your actual older existing postgres cluster!! 🙂
This is the new, empty, default created main cluster being deleted:
$ sudo pg_dropcluster --stop 9.4 main
3. Next, run the pg_upgradecluster as below
The syntax is:
sudo pg_upgradecluster -v NEWVERSION OLDVERSION main
$ sudo pg_upgradecluster -v 9.4 9.1 main Disabling connections to the old cluster during upgrade... Restarting old cluster with restricted connections... Creating new cluster 9.4/main ... config /etc/postgresql/9.4/main data /var/lib/postgresql/9.4/main locale C port 5433 Disabling connections to the new cluster during upgrade... Roles, databases, schemas, ACLs... Fixing hardcoded library paths for stored procedures... Upgrading database ttrss... pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2822; 0 17040 TABLE DATA ttrss_entries ttrss pg_restore: [archiver (db)] COPY failed for table "ttrss_entries": ERROR: duplicate key value violates unique constraint "ttrss_entries_guid_key" DETAIL: Key (guid)=(SHA1:9617bce31af056ea991bc628563258804f4537aa) already exists. CONTEXT: COPY ttrss_entries, line 82670 WARNING: errors ignored on restore: 1 Analyzing database ttrss... Fixing hardcoded library paths for stored procedures... Upgrading database postgres... Analyzing database postgres... Fixing hardcoded library paths for stored procedures... Upgrading database owncloud... Analyzing database owncloud... Fixing hardcoded library paths for stored procedures... Upgrading database template1... Analyzing database template1... Re-enabling connections to the old cluster... Re-enabling connections to the new cluster... Copying old configuration files... Copying old start.conf... Copying old pg_ctl.conf... Copying old server.crt... Copying old server.key... Stopping target cluster... Stopping old cluster... Disabling automatic startup of old cluster... Configuring old cluster to use a different port (5433)... Starting target cluster on the original port... Success. Please check that the upgraded cluster works. If it does, you can remove the old cluster with pg_dropcluster 9.1 main
4. And as at the bottom of the output, you can optionally delete your old postgres cluster with:
$ sudo pg_dropcluster 9.1 main