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:<\/p>\n
You will need to re-configure your postgresql.conf and pg_hba.conf files. Because the files will be reset after upgrade.<\/p>\n
We are installing\u00a0Postgresql 11 with the following commands.<\/p>\n
[root@postgres eng]# yum install https:\/\/download.postgresql.org\/pub\/repos\/yum\/11\/redhat\/rhel-7-x86_64\/pgdg-centos11-11-2.noarch.rpm\n\n[root@postgres eng]# yum install postgresql11\n\n[root@postgres eng]# yum install postgresql11-server<\/pre>\ninitdb<\/h2>\n
We are performing the initdb operation in the Postgresql 11 database.<\/p>\n
[root@postgres eng]# \/usr\/pgsql-11\/bin\/postgresql-11-setup initdb\nInitializing database \u2026 OK<\/pre>\nChecking Whether the Upgrade is Applicable<\/h2>\n
We check the applicability of the upgrade with the command below. (with postgresql user)<\/p>\n
-bash-4.2$ \/usr\/pgsql-1\/bin\/pg_upgrade \u2013old-bindir=\/usr\/pgsql-10\/bin\/ \u2013new-bindir=\/usr\/pgsql-11\/bin\/ \u2013old-datadir=\/var\/lib\/pgsql\/10\/data \u2013new-datadir=\/var\/lib\/pgsql\/11\/data \u2013check\npgsql-10\/ pgsql-11\/\u00a0\n-bash-4.2$ \/usr\/pgsql-11\/bin\/pg_upgrade \u2013old-bindir=\/usr\/pgsql-10\/bin\/ \u2013new-bindir=\/usr\/pgsql-11\/bin\/ \u2013old-datadir=\/var\/lib\/pgsql\/10\/data \u2013new-datadir=\/var\/lib\/pgsql\/11\/data \u2013check\nPerforming Consistency Checks on Old Live Server\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\nChecking cluster versions ok\nChecking database user is the install user ok\nChecking database connection settings ok\nChecking for prepared transactions ok\nChecking for reg* data types in user tables ok\nChecking for contrib\/isn with bigint-passing mismatch ok\nChecking for presence of required libraries ok\nChecking database user is the install user ok\nChecking for prepared transactions ok\n\n*Clusters are compatible*<\/pre>\nStop PostgreSQL 10<\/h2>\n
If we perform the upgrade without stopping Postgresql 10, we get the following error.<\/p>\n
-bash-4.2$ \/usr\/pgsql-11\/bin\/pg_upgrade \u2013old-bindir=\/usr\/pgsql-10\/bin\/ \u2013new-bindir=\/usr\/pgsql-11\/bin\/ \u2013old-datadir=\/var\/lib\/pgsql\/10\/data \u2013new-datadir=\/var\/lib\/pgsql\/11\/data\n\nThere seems to be a postmaster servicing the old cluster.\nPlease shutdown that postmaster and try again.\nFailure, exiting\n-bash-4.2$ logout\n[root@postgres eng]# systemctl stop postgresql-10.service<\/pre>\nWe are stopping Postgresql 10 (with root user)<\/p>\n
[root@postgres eng]# systemctl stop postgresql-10.service<\/pre>\nUpgrade<\/h2>\n
Then we switch to postgres user and run the upgrade command.(with Postgres user)<\/p>\n
-bash-4.2$ \/usr\/pgsql-11\/bin\/pg_upgrade \u2013old-bindir=\/usr\/pgsql-10\/bin\/ \u2013new-bindir=\/usr\/pgsql-11\/bin\/ \u2013old-datadir=\/var\/lib\/pgsql\/10\/data \u2013new-datadir=\/var\/lib\/pgsql\/11\/data\u00a0\nPerforming Consistency Checks\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013\nChecking cluster versions ok\nChecking database user is the install user ok\nChecking database connection settings ok\nChecking for prepared transactions ok\nChecking for reg* data types in user tables ok\nChecking for contrib\/isn with bigint-passing mismatch ok\nCreating dump of global objects ok\nCreating dump of database schemas\nok\nChecking for presence of required libraries ok\nChecking database user is the install user ok\nChecking for prepared transactions ok\n\nIf pg_upgrade fails after this point, you must re-initdb the\nnew cluster before continuing.\n\nPerforming Upgrade\n\u2014\u2014\u2014\u2014\u2014\u2014\nAnalyzing all rows in the new cluster ok\nFreezing all rows in the new cluster ok\nDeleting files from new pg_xact ok\nCopying old pg_xact to new server ok\nSetting next transaction ID and epoch for new cluster ok\nDeleting files from new pg_multixact\/offsets ok\nCopying old pg_multixact\/offsets to new server ok\nDeleting files from new pg_multixact\/members ok\nCopying old pg_multixact\/members to new server ok\nSetting next multixact ID and offset for new cluster ok\nResetting WAL archives ok\nSetting frozenxid and minmxid counters in new cluster ok\nRestoring global objects in the new cluster ok\nRestoring database schemas in the new cluster\nok\nCopying user relation files\nok\nSetting next OID for new cluster ok\nSync data directory to disk ok\nCreating script to analyze new cluster ok\nCreating script to delete old cluster ok\n\nUpgrade Complete\n\u2014\u2014\u2014\u2014\u2014-\nOptimizer statistics are not transferred by pg_upgrade so,\nonce you start the new server, consider running:\n.\/analyze_new_cluster.sh\n\nRunning this script will delete the old cluster\u2019s data files:\n.\/delete_old_cluster.sh\n-bash-4.2$<\/pre>\nWe are running the following commands before executing the commands requested from us.<\/p>\n
[root@postgres eng]# systemctl enable postgresql-11.service\u00a0\n\nCreated symlink from \/etc\/systemd\/system\/multi-user.target.wants\/postgresql-11.service to \/usr\/lib\/systemd\/system\/postgresql-11.service.\n\n[root@postgres eng]# systemctl start postgresql-11.service<\/pre>\nThen we run the commands requested from us.<\/p>\n
-bash-4.2$ .\/analyze_new_cluster.sh\nThis script will generate minimal optimizer statistics rapidly\nso your system is usable, and then gather statistics twice more\nwith increasing accuracy. When it is done, your system will\nhave the default level of optimizer statistics.\n\nIf you have used ALTER TABLE to modify the statistics target for\nany tables, you might want to remove them and restore them after\nrunning this script because they will delay fast statistics generation.\n\nIf you would like default statistics as quickly as possible, cancel\nthis script and run:\n\u201c\/usr\/pgsql-11\/bin\/vacuumdb\u201d \u2013all \u2013analyze-only\n\nvacuumdb: processing database \u201cpostgres\u201d: Generating minimal optimizer statistics (1 target)\nvacuumdb: processing database \u201ctemplate1\u201d: Generating minimal optimizer statistics (1 target)\nvacuumdb: processing database \u201cpostgres\u201d: Generating medium optimizer statistics (10 targets)\nvacuumdb: processing database \u201ctemplate1\u201d: Generating medium optimizer statistics (10 targets)\nvacuumdb: processing database \u201cpostgres\u201d: Generating default (full) optimizer statistics\nvacuumdb: processing database \u201ctemplate1\u201d: Generating default (full) optimizer statistics\n\nDone\n<\/pre>\nDelete Old Cluster<\/h2>\n
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.<\/p>\n
-bash-4.2$ .\/delete_old_cluster.sh<\/pre>\nWe remove all packages related to Postgresql10. (with root user)<\/p>\n
[root@postgres eng]# rpm -qa | grep postgresql\n\nyum remove\u00a0postgresql10-10.5-1PGDG.rhel7.x86_64\n\nyum remove\u00a0\u00a0postgresql10-libs-10.5-1PGDG.rhel7.x86_64<\/pre>\nUpgrade has completed. You can connect to the database and see version 11.<\/p>\n
-bash-4.2$ psql\n\npsql (11.0)\n\nType \u201chelp\u201d for help.\n\npostgres=#<\/pre>\n\n<\/div>\n