PostgreSQL Upgrade From 9.6 To 11

Melek Durdu May 20, 2022 PostgreSQL

In this article, I will talk about how to PostgreSQL Upgrade From 9.6 To 11 using pg_dumpall on Linux 7 operating system.

We are creating a new database called “test” for testing purposes.

[root@test ~]# su - postgres
-bash-4.2$ psql
postgres=# create database test;
CREATE DATABASE

[root@test ~]# su - postgres

-bash-4.2$ psql

postgres=# create database test;

CREATE DATABASE

We create a table in the database we have created and add records to it.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table test(id int);
CREATE TABLE
test=# insert into test values(1);
INSERT 0 1
test=# insert into test values(2);
INSERT 0 1

postgres=# \c test

You are now connected to database "test" as user "postgres".

test=# create table test(id int);

CREATE TABLE

test=# insert into test values(1);

INSERT 0 1

test=# insert into test values(2);

INSERT 0 1

We can see the table we created and the records we added as follows.

test=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | postgres
(1 row)
test=# table test;
 id 
----
  1
  2
(2 rows)

test=# \dt

List of relations

Schema | Name | Type | Owner

--------+------+-------+----------

public | test | table | postgres

(1 row)

test=# table test;

----

(2 rows)

After these operations, we take a backup of our database.

-bash-4.2$ pg_dumpall >/backups/yedek_testdb

1	-bash-4.2$ pg_dumpall >/backups/yedek_testdb

After completing the backup process, we stop the PostgreSQL 9.6 service and delete all the packages related to PostgreSQL.

[root@test]# systemctl stop postgresql-9.6.service
[root@test]# yum remove postgresql*

1 2	[root@test]# systemctl stop postgresql-9.6.service [root@test]# yum remove postgresql*

We are backing up the data directory.

[root@test]# mv /var/lib/pgsql/9.6/data /var/lib/pgsql/9.6/data.old

1	[root@test]# mv /var/lib/pgsql/9.6/data /var/lib/pgsql/9.6/data.old

We install PostgreSQL 11 with the commands below.

[root@orcl ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[root@orcl ~]# yum install -y postgresql11-server

1 2	[root@orcl ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm [root@orcl ~]# yum install -y postgresql11-server

We start the database, then start the service.

[root@orcl ~]# /usr/pgsql-11/bin/postgresql-11-setup initdb
[root@orcl ~]# systemctl enable postgresql-11
[root@orcl ~]# systemctl start postgresql-11

[root@orcl ~]# /usr/pgsql-11/bin/postgresql-11-setup initdb

[root@orcl ~]# systemctl enable postgresql-11

[root@orcl ~]# systemctl start postgresql-11

We are restoring the backup we took earlier.

-bash-4.2$ psql -d postgres -f /backups/yedek_testdb

1	-bash-4.2$ psql -d postgres -f /backups/yedek_testdb

We reboot the server.

[root@orcl ~]# reboot

1	[root@orcl ~]# reboot

We connect to the database and check whether the test database we created before exists.

-bash-4.2$ psql
psql (11.12)
Type "help" for help.
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
           |          |          |             |             | =c/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

-bash-4.2$ psql

psql (11.12)

Type "help" for help.

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(4 rows)

As we can see above, we have a test database. Let’s check the table we created.

postgres=# \c test
You are now connected to database "test" as user "postgres".

test=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | postgres
(1 row)
test=# table test;
 id 
----
  1
  2
(2 rows)