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.
|
1 2 3 4 |
[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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
test=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | test | table | postgres (1 row) test=# table test; id ---- 1 2 (2 rows) |
After these operations, we take a backup of our database.
|
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.
|
1 2 |
[root@test]# systemctl stop postgresql-9.6.service [root@test]# yum remove postgresql* |
We are backing up the data directory.
|
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.
|
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.
|
1 2 3 |
[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.
|
1 |
-bash-4.2$ psql -d postgres -f /backups/yedek_testdb |
We reboot the server.
|
1 |
[root@orcl ~]# reboot |
We connect to the database and check whether the test database we created before exists.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-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) |
As we can see above, we have a test database. Let’s check the table we created.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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) |
We are deleting the data directory of PostgreSQL 9.6 as the upgrade process has been completed successfully.
|
1 |
[root@test]# rm -rf /var/lib/pgsql/9.6/data.old |
I hope it will be useful for you.
