Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We will upgrade POSTGRESQL 10 To 11 on CENTOS in this article. Before I start the upgrade process, I want to make the following critical warning:
You will need to re-configure your postgresql.conf and pg_hba.conf files. Because the files will be reset after upgrade.
Install PostgreSQL 11
We are installing Postgresql 11 with the following commands.
1 2 3 4 5 | [root@postgres eng]# yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm [root@postgres eng]# yum install postgresql11 [root@postgres eng]# yum install postgresql11-server |
initdb
We are performing the initdb operation in the Postgresql 11 database.
1 2 | [root@postgres eng]# /usr/pgsql-11/bin/postgresql-11-setup initdb Initializing database … OK |
Checking Whether the Upgrade is Applicable
We check the applicability of the upgrade with the command below. (with postgresql user)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -bash-4.2$ /usr/pgsql-1/bin/pg_upgrade –old-bindir=/usr/pgsql-10/bin/ –new-bindir=/usr/pgsql-11/bin/ –old-datadir=/var/lib/pgsql/10/data –new-datadir=/var/lib/pgsql/11/data –check pgsql-10/ pgsql-11/ -bash-4.2$ /usr/pgsql-11/bin/pg_upgrade –old-bindir=/usr/pgsql-10/bin/ –new-bindir=/usr/pgsql-11/bin/ –old-datadir=/var/lib/pgsql/10/data –new-datadir=/var/lib/pgsql/11/data –check Performing Consistency Checks on Old Live Server ———————————————— Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok *Clusters are compatible* |
Stop PostgreSQL 10
If we perform the upgrade without stopping Postgresql 10, we get the following error.
1 2 3 4 5 6 7 | -bash-4.2$ /usr/pgsql-11/bin/pg_upgrade –old-bindir=/usr/pgsql-10/bin/ –new-bindir=/usr/pgsql-11/bin/ –old-datadir=/var/lib/pgsql/10/data –new-datadir=/var/lib/pgsql/11/data There seems to be a postmaster servicing the old cluster. Please shutdown that postmaster and try again. Failure, exiting -bash-4.2$ logout [root@postgres eng]# systemctl stop postgresql-10.service |
We are stopping Postgresql 10 (with root user)
1 | [root@postgres eng]# systemctl stop postgresql-10.service |
Upgrade
Then we switch to postgres user and run the upgrade command.(with Postgres user)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | -bash-4.2$ /usr/pgsql-11/bin/pg_upgrade –old-bindir=/usr/pgsql-10/bin/ –new-bindir=/usr/pgsql-11/bin/ –old-datadir=/var/lib/pgsql/10/data –new-datadir=/var/lib/pgsql/11/data Performing Consistency Checks —————————– Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade —————— Analyzing all rows in the new cluster ok Freezing all rows in the new cluster ok Deleting files from new pg_xact ok Copying old pg_xact to new server ok Setting next transaction ID and epoch for new cluster ok Deleting files from new pg_multixact/offsets ok Copying old pg_multixact/offsets to new server ok Deleting files from new pg_multixact/members ok Copying old pg_multixact/members to new server ok Setting next multixact ID and offset for new cluster ok Resetting WAL archives ok Setting frozenxid and minmxid counters in new cluster ok Restoring global objects in the new cluster ok Restoring database schemas in the new cluster ok Copying user relation files ok Setting next OID for new cluster ok Sync data directory to disk ok Creating script to analyze new cluster ok Creating script to delete old cluster ok Upgrade Complete —————- Optimizer statistics are not transferred by pg_upgrade so, once you start the new server, consider running: ./analyze_new_cluster.sh Running this script will delete the old cluster’s data files: ./delete_old_cluster.sh -bash-4.2$ |
We are running the following commands before executing the commands requested from us.
1 2 3 4 5 | [root@postgres eng]# systemctl enable postgresql-11.service Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service. [root@postgres eng]# systemctl start postgresql-11.service |
Then we run the commands requested from us.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | -bash-4.2$ ./analyze_new_cluster.sh This script will generate minimal optimizer statistics rapidly so your system is usable, and then gather statistics twice more with increasing accuracy. When it is done, your system will have the default level of optimizer statistics. If you have used ALTER TABLE to modify the statistics target for any tables, you might want to remove them and restore them after running this script because they will delay fast statistics generation. If you would like default statistics as quickly as possible, cancel this script and run: “/usr/pgsql-11/bin/vacuumdb” –all –analyze-only vacuumdb: processing database “postgres”: Generating minimal optimizer statistics (1 target) vacuumdb: processing database “template1”: Generating minimal optimizer statistics (1 target) vacuumdb: processing database “postgres”: Generating medium optimizer statistics (10 targets) vacuumdb: processing database “template1”: Generating medium optimizer statistics (10 targets) vacuumdb: processing database “postgres”: Generating default (full) optimizer statistics vacuumdb: processing database “template1”: Generating default (full) optimizer statistics Done |
Delete Old Cluster
Then run the below command. But, be aware that this command delete old cluster. Thats why, be sure that postgresql 11 is working before delete the old cluster.
1 | -bash-4.2$ ./delete_old_cluster.sh |
We remove all packages related to Postgresql10. (with root user)
1 2 3 4 5 | [root@postgres eng]# rpm -qa | grep postgresql yum remove postgresql10-10.5-1PGDG.rhel7.x86_64 yum remove postgresql10-libs-10.5-1PGDG.rhel7.x86_64 |
Upgrade has completed. You can connect to the database and see version 11.
1 2 3 4 5 6 7 | -bash-4.2$ psql psql (11.0) Type “help” for help. postgres=# |
There’s some typos, it should have double dash “–”
/usr/pgsql-10/bin/pg_upgrade –old-bindir=/usr/pgsql-10/bin/ –new-bindir=/usr/pgsql-11/bin/ –old-datadir=/var/lib/pgsql/10/data –new-datadir=/var/lib/pgsql/11/data –check
also I’m getting this error after executing that:
could not write to log file “pg_upgrade_internal.log”
Failure, exiting
Any ideas on how to workaround that?
Hi Jim,
Could you please write operating system name and version?
Also, which user did you try upgrade check command? postgres or other?
Best Regards,
you should run the below command on tmp. That is, it should be as follows.
su – postgres
cd /tmp
then execute command:
/usr/pgsql-10/bin/pg_upgrade –old-bindir=/usr/pgsql-10/bin/ –new-bindir=/usr/pgsql-11/bin/ –old-datadir=/var/lib/pgsql/10/data –new-datadir=/var/lib/pgsql/11/data –check
This is a very detail information. Good Job