PostgreSQL Upgrade from version 9.5 to version 12

In this article, we are going to learn how to upgrade PostgreSQL from version 9.6 to version 12.

Enhancements to administrative functionality in version 12, including:

• REINDEX CONCURRENTLY can rebuild an index without blocking writes to its table
• pg_checksums can enable/disable page checksums (used for detecting data corruption) in an offline cluster
• Progress reporting statistics for CREATE INDEX, REINDEX, CLUSTER, VACUUM FULL, and pg_checksums
• Support for the SQL/JSON path language
• Stored generated columns
• Nondeterministic ICU collations, enabling case-insensitive and accent-insensitive grouping and ordering
• New authentication features, including: Encryption of TCP/IP connections when using GSSAPI authentication
• Discovery of LDAP servers using DNS SRV records
• Multi-factor authentication, using the clientcert=verify-full option combined with an additional authentication method in pg_hba.conf
• Notable improvements to query performance particularly over larger data sets, and overall space utilization
• This release also introduces the pluggable table storage interface, which allows developers to create their own methods for storing data
• Queries on partitioned tables have also seen demonstrable improvements, particularly for tables with thousands of partitions that only need to retrieve data from a limited subset.

PostgreSQL 12 Encryption Options

PostgreSQL offers encryption at several levels and provides flexibility in protecting data from disclosure due to database server theft, unscrupulous administrators, and insecure networks. Encryption might also be required to secure sensitive data such as medical records or financial transactions.

• Password Encryption
• Encryption For Specific Columns
• Data Partition Encryption
• Encrypting Data Across A Network
• GSSAPI-encrypted connections
• SSL Host Authentication
• Client-Side Encryption

Prerequisites

• 1 Server with PostgreSQL 9.5
• Ubuntu 18.04 LTS
• Shell access with sudo privileges
• Backed up current databases for precaution if process breaks or fail

Step 1: Install PostgreSQL

$  wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

$ echo "deb http://apt.postgresql.org/pub/repos/apt/ lsb_release -cs-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list-

$ sudo apt update

$ sudo apt install postgresql-12 postgresql-client-12 -y

Screenshots below

Use the below command to check which versions of postgresql are installed

$ dpkg -l | grep postgresql

$ pg_lsclusters

There already is a cluster “main” for 12 (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 crashes when you try to upgrade 9.5/main when 12/main also exists. The recommended procedure is to remove the version 12 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Step 2: Upgrade PostgreSQL

$ service postgresql stop

$ sudo pg_dropcluster 12 main --stop

$ sudo pg_upgradecluster 9.5 main

Step 3: Start the upgraded version of postgreSQL

$   service postgresql start

List the Cluster to check which version is online

$   pg_lsclusters

Step 4: Drop postgresql 9.5 cluster

$ pg_dropcluster 9.5 main

In this process of upgrade, we did not face any issue, if you find any problem do let us know or contact us for the assistance at support@ddevops.com