![](\"https:\/\/miro.medium.com\/max\/1344\/1*ZrW6Cq1YKdtpznsF-nqSog.png\")
<\/p>\nIn this article, I will talk about how to PostgreSQL Upgrade From 9.6 To 11 using pg_dumpall on Linux 7 operating system.<\/p>\n
<\/p>\n
We are creating a new database called “test” for testing purposes.<\/p>\n
[root@test ~]# su - postgres\r\n-bash-4.2$ psql\r\npostgres=# create database test;\r\nCREATE DATABASE<\/pre>\nWe create a table in the database we have created and add records to it.<\/p>\n
postgres=# \\c test\r\nYou are now connected to database \"test\" as user \"postgres\".\r\ntest=# create table test(id int);\r\nCREATE TABLE\r\ntest=# insert into test values(1);\r\nINSERT 0 1\r\ntest=# insert into test values(2);\r\nINSERT 0 1<\/pre>\nWe can see the table we created and the records we added as follows.<\/p>\n
test=# \\dt\r\n List of relations\r\n Schema | Name | Type | Owner \r\n--------+------+-------+----------\r\n public | test | table | postgres\r\n(1 row)\r\ntest=# table test;\r\n id \r\n----\r\n 1\r\n 2\r\n(2 rows)<\/pre>\nAfter these operations, we take a backup of our database.<\/p>\n
-bash-4.2$ pg_dumpall >\/backups\/yedek_testdb\r\n<\/pre>\nAfter completing the backup process, we stop the PostgreSQL 9.6 service and delete all the packages related to PostgreSQL.<\/p>\n
[root@test]# systemctl stop postgresql-9.6.service\r\n[root@test]# yum remove postgresql*<\/pre>\nWe are backing up the data directory.<\/p>\n
[root@test]# mv \/var\/lib\/pgsql\/9.6\/data \/var\/lib\/pgsql\/9.6\/data.old\r\n<\/pre>\nWe install PostgreSQL 11 with the commands below.<\/p>\n
[root@orcl ~]# yum install -y https:\/\/download.postgresql.org\/pub\/repos\/yum\/reporpms\/EL-7-x86_64\/pgdg-redhat-repo-latest.noarch.rpm\r\n[root@orcl ~]# yum install -y postgresql11-server<\/pre>\nWe start the database, then start the service.<\/p>\n
[root@orcl ~]# \/usr\/pgsql-11\/bin\/postgresql-11-setup initdb\r\n[root@orcl ~]# systemctl enable postgresql-11\r\n[root@orcl ~]# systemctl start postgresql-11<\/pre>\nWe are restoring the backup we took earlier.<\/p>\n
-bash-4.2$ psql -d postgres -f \/backups\/yedek_testdb\r\n<\/pre>\nWe reboot the server.<\/p>\n
[root@orcl ~]# reboot\r\n<\/pre>\nWe connect to the database and check whether the test database we created before exists.<\/p>\n
-bash-4.2$ psql\r\npsql (11.12)\r\nType \"help\" for help.\r\npostgres=# \\l\r\n List of databases\r\n Name | Owner | Encoding | Collate | Ctype | Access privileges \r\n-----------+----------+----------+-------------+-------------+-----------------------\r\n postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | \r\n template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres +\r\n | | | | | postgres=CTc\/postgres\r\n template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc\/postgres+\r\n | | | | | =c\/postgres\r\n test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | \r\n(4 rows)<\/pre>\nAs we can see above, we have a test database. Let’s check the table we created.<\/p>\n
postgres=# \\c test\r\nYou are now connected to database \"test\" as user \"postgres\".\r\n\r\ntest=# \\dt\r\n List of relations\r\n Schema | Name | Type | Owner \r\n--------+------+-------+----------\r\n public | test | table | postgres\r\n(1 row)\r\ntest=# table test;\r\n id \r\n----\r\n 1\r\n 2\r\n(2 rows)\r\n<\/pre>\nWe are deleting the data directory of PostgreSQL 9.6 as the upgrade process has been completed successfully.<\/p>\n
[root@test]# rm -rf \/var\/lib\/pgsql\/9.6\/data.old\r\n<\/pre>\nI hope it will be useful for you.<\/p>\n
<\/p>\n
<\/div>\n