Upgrade PostgreSQL on Ubuntu
Install the new version
root@srv01:~# apt install postgresql-11List psql clusters
root@srv01:~# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
10 main 5432 down postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11 main 5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.logBackup psql
root@srv01:~# su - postgres
postgres@srv01:~$ pg_dumpall > psql_dump.sqlStop the old cluster
root@srv01:~# pg_ctlcluster 10 main stopDrop the new created cluster
root@srv01:~# pg_dropcluster --stop 11 mainUpgrade the old Cluster
root@srv01:~# pg_upgradecluster # pg_upgradecluster -m upgrade 10 m upgrade 10 main
Disabling connections to the old cluster during Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted Restarting old cluster with restricted connections...
Stopping old Stopping old cluster...
Creating new PostgreSQL cluster Creating new PostgreSQL cluster 11/main main ...
/usr/lib/postgresql/11/bin/initdb initdb -D D /var/lib/postgresql/11/main main --auth-local peer --auth-host md5 --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8
The files belonging to this database system will be owned by user The files belonging to this database system will be owned by user "postgres".
This user must also own the server This user must also own the server process.
The database cluster will be initialized with locale The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to The default text search configuration will be set to "english".
Data page checksums are Data page checksums are disabled.
fixing permissions on existing directory fixing permissions on existing directory /var/lib/postgresql/11/main main ... ok
creating subdirectories creating subdirectories ... ok
selecting default max_connections selecting default max_connections ... 100
selecting default shared_buffers selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation selecting dynamic shared memory implementation ... posix
creating configuration files creating configuration files ... ok
running bootstrap script running bootstrap script ... ok
performing performing post-bootstrap initialization bootstrap initialization ... ok
syncing data to disk syncing data to disk ... ok
Success. You can now start the database server You can now start the database server using:
/usr/lib/postgresql/11/bin/pg_ctl pg_ctl -D D /var/lib/postgresql/11/main main -l logfile l logfile start
Ver Cluster Port Status Owner Data directory Log Ver Cluster Port Status Owner Data directory Log file
11 main 5433 down postgres 11 main 5433 down postgres /var/lib/postgresql/11/main main /var/log/postgresql/postgresql-11-main.log
/usr/lib/postgresql/11/bin/pg_upgrade pg_upgrade -b b /usr/lib/postgresql/10/bin bin -B B /usr/lib/postgresql/11/bin bin -p 5432 p 5432 -P 5433 P 5433 -d d /etc/postgresql/10/main main -D D /etc/postgresql/11/main
Finding the real data directory for the source cluster Finding the real data directory for the source cluster ok
Finding the real data directory for the target cluster Finding the real data directory for the target cluster ok
Performing Consistency Performing Consistency Checks
-----------------------------
Checking cluster versions Checking cluster versions ok
Checking database user is the install user Checking database user is the install user ok
Checking database connection settings Checking database connection settings ok
Checking for prepared transactions Checking for prepared transactions ok
Checking for Checking for reg* data types in user tables data types in user tables ok
Checking for Checking for contrib/isn with isn with bigint-passing mismatch passing mismatch ok
Creating dump of global objects Creating dump of global objects ok
Creating dump of database Creating dump of database schemas
ok
Checking for presence of required libraries Checking for presence of required libraries ok
Checking database user is the install user Checking database user is the install user ok
Checking for prepared transactions Checking for prepared transactions ok
If pg_upgrade fails after this If pg_upgrade fails after this point, you must you must re-initdb initdb the
new cluster before new cluster before continuing.
Performing Performing Upgrade
------------------
Analyzing all rows in the new cluster Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster Freezing all rows in the new cluster ok
Deleting files from new pg_xact Deleting files from new pg_xact ok
Copying old pg_xact to new server Copying old pg_xact to new server ok
Setting next transaction ID and epoch for new cluster Setting next transaction ID and epoch for new cluster ok
Deleting files from new Deleting files from new pg_multixact/offsets offsets ok
Copying old Copying old pg_multixact/offsets to new server offsets to new server ok
Deleting files from new Deleting files from new pg_multixact/members members ok
Copying old Copying old pg_multixact/members to new server members to new server ok
Setting next multixact ID and offset for new cluster Setting next multixact ID and offset for new cluster ok
Resetting WAL archives Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster Restoring global objects in the new cluster ok
Restoring database schemas in the new Restoring database schemas in the new cluster
ok
Copying user relation Copying user relation files
ok
Setting next OID for new cluster Setting next OID for new cluster ok
Sync data directory to disk Sync data directory to disk ok
Creating script to analyze new cluster Creating script to analyze new cluster ok
Creating script to delete old cluster Creating script to delete old cluster ok
Upgrade Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade Optimizer statistics are not transferred by pg_upgrade so,
once you start the new once you start the new server, consider consider running:
./analyze_new_cluster.sh
Running this script will delete the old Running this script will delete the old cluster's data s data files:
./delete_old_cluster.sh
pg_upgrade output scripts are in pg_upgrade output scripts are in /var/log/postgresql/pg_upgradecluster-10-11-main.5n35
Re-enabling connections to the old enabling connections to the old cluster...
Copying old configuration Copying old configuration files...
Copying old Copying old start.conf...
Copying old Copying old pg_ctl.conf...
Disabling automatic startup of old Disabling automatic startup of old cluster...
Configuring old cluster to use a different port Configuring old cluster to use a different port (5433)...
Success. Please check that the upgraded cluster Please check that the upgraded cluster works. If it If it does,
you can remove the old cluster you can remove the old cluster with
pg_dropcluster 10 pg_dropcluster 10 main
Ver Cluster Port Status Owner Data directory Log Ver Cluster Port Status Owner Data directory Log file
10 main 5433 down postgres 10 main 5433 down postgres /var/lib/postgresql/10/main main /var/log/postgresql/postgresql-10-main.log
Ver Cluster Port Status Owner Data directory Log Ver Cluster Port Status Owner Data directory Log file
11 main 5432 down postgres 11 main 5432 down postgres /var/lib/postgresql/11/main main /var/log/postgresql/postgresql-11-main.log
Start new cluster
root@srv01:~# pg_ctlcluster 11 main startPost Checks
Check is the new cluster is working properly, when ready delete the old cluster
root@srv01:~# apt-get autoremove --purge postgresql-10
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
postgresql-10*
0 upgraded, 0 newly installed, 1 to remove and 0 not upgraded.
After this operation, 23.5 MB disk space will be freed.
Do you want to continue? [Y/n]
(Reading database ... 39243 files and directories currently installed.)
Removing postgresql-10 (10.6-1.pgdg16.04+1) ...
Purging configuration files for postgresql-10 (10.6-1.pgdg16.04+1) ...
Dropping cluster main...
Processing triggers for postgresql-common (197.pgdg16.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files: