To use the most modern version of Postgres software we need to add postgresql repository. Edit /etc/apt/sources.list
or create /etc/apt/sources.list.d/pgdg.list
and add there a line: deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main
Then import the repository signing key, and update the package lists:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
Install a new version of PostgreSQL server.
Once the Debian upgrade finished, I used dpkg-query -l postgresql*
to check which versions of postgres I have installed.
dpkg-query -l postgresql*
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name Version Architecture Description
+++-===========================-==================-==================-============================================================
ii postgresql 9.6+177.pgdg80+1 all object-relational SQL database (supported version)
un postgresql-7.4 <none> <none> (no description available)
un postgresql-8.0 <none> <none> (no description available)
un postgresql-9.1 <none> <none> (no description available)
ii postgresql-9.4 9.4.9-0+deb8u1 amd64 object-relational SQL database, version 9.4 server
ii postgresql-9.6 9.6.1-1.pgdg80+1 amd64 object-relational SQL database, version 9.6 server
un postgresql-client <none> <none> (no description available)
ii postgresql-client-9.4 9.4.10-1.pgdg80+1 amd64 front-end programs for PostgreSQL 9.4
ii postgresql-client-9.6 9.6.1-1.pgdg80+1 amd64 front-end programs for PostgreSQL 9.6
ii postgresql-client-common 177.pgdg80+1 all manager for multiple PostgreSQL client versions
ii postgresql-common 177.pgdg80+1 all PostgreSQL database-cluster manager
ii postgresql-contrib-9.6 9.6.1-1.pgdg80+1 amd64 additional facilities for PostgreSQL
un postgresql-doc <none> <none> (no description available)
un postgresql-doc-9.4 <none> <none> (no description available)
un postgresql-doc-9.6 <none> <none> (no description available)
Looks like the Debian upgrade included PostgreSQL 9.6, but I still need to upgrade from 9.4 to 9.6.
Run pg_lsclusters
, your 9.4 and 9.6 main clusters should be "online".
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.4 main 5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.6 main 5433 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
There already is a cluster "main" for 9.6 (since this is created by default on package installation).
This is done so that a fresh installation works out of the box without the need to create a cluster first,
but of course it clashes when you try to upgrade 9.4/main when 9.6/main also exists.
The recommended procedure is to remove the 9.6 cluster with pg_dropcluster
and then upgrade with pg_upgradecluster
.
Stop the 9.6 cluster and drop it.
sudo pg_dropcluster 9.6 main --stop
Upgrade the 9.4 cluster to the latest version.
sudo pg_upgradecluster 9.4 main
Your 9.4 cluster should now be "down".
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.4 main 5433 down postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
Check that the upgraded cluster works, then remove the 9.4 cluster.
sudo pg_dropcluster 9.4 main
After all you may totally remove version 9.4 from the server:
sudo apt-get --purge remove postgresql-client-9.4 postgresql-9.4
dpkg-query -l postgresql*
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name Version Architecture Description
+++-===========================-==================-==================-============================================================
ii postgresql 9.6+177.pgdg80+1 all object-relational SQL database (supported version)
un postgresql-7.4 <none> <none> (no description available)
un postgresql-8.0 <none> <none> (no description available)
un postgresql-9.1 <none> <none> (no description available)
ii postgresql-9.6 9.6.1-1.pgdg80+1 amd64 object-relational SQL database, version 9.6 server
un postgresql-client <none> <none> (no description available)
ii postgresql-client-9.6 9.6.1-1.pgdg80+1 amd64 front-end programs for PostgreSQL 9.6
ii postgresql-client-common 177.pgdg80+1 all manager for multiple PostgreSQL client versions
ii postgresql-common 177.pgdg80+1 all PostgreSQL database-cluster manager
ii postgresql-contrib-9.6 9.6.1-1.pgdg80+1 amd64 additional facilities for PostgreSQL
un postgresql-doc <none> <none> (no description available)
un postgresql-doc-9.6 <none> <none> (no description available)
One suggested addition. If you have any extensions that you compiled and installed from source, AND they are referenced in you usage, you MUST recompile, and do the install steps for the binaries first before doing this. You only need for the upgrade to be able to locate the binaries, you DO NOT need to do the create extension from within the new DB server.