Migrating PostgreSQL 14 to 18 Using pg_dumpall
A major PostgreSQL version upgrade is not only a matter of installing the new server. The migration also requires a clear understanding of the existing environment, a verified backup, careful restoration, and post-upgrade validation. In this article, I walk through a PostgreSQL 14 to PostgreSQL 18 migration using pg_dumpall, based on a real environment.
1. Review the Current PostgreSQL 14 Environment
Before starting the upgrade, I record the current PostgreSQL version, data directory, databases, database sizes, tables, indexes, and installed extensions. These checks give me a reference point for validating the PostgreSQL 18 environment later.
--Current version:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 14.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)
--data directory:
postgres=# show data_directory;
data_directory
------------------------
/var/lib/pgsql/14/data
(1 row)--list of databases:
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 | =c/postgres +
| | | | | postgres=CTc/postgres
usef | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
vahid | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)The total database size is useful for estimating the amount of data involved in the migration and for checking whether the restored environment is in the expected range.
At first, I want to verify the total size of all databases:
postgres=# SELECT
ROUND(SUM(pg_database_size(datname)) / 1024.0 / 1024.0, 2) AS total_size_mb
FROM pg_database;
total_size_mb
---------------
3494.09
(1 row)It’s approximately 3.5GB, in addition, the following query shows exact size of each database:
postgres=# SELECT
datname AS database_name,
ROUND(pg_database_size(datname) / 1024.0 / 1024.0, 2) AS size_mb
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
database_name | size_mb
---------------+---------
vahid | 3460.68
usef | 8.49
postgres | 8.38
template1 | 8.23
template0 | 8.23
(5 rows)I then drill into the largest database and record the size of its tables and indexes. This also helps identify the objects that should be checked most carefully after the restore.
The biggest database is VAHID and we check the tables which are in this database:
postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------------------+----------+----------+-------------+---------------+------------+-------------
public | tb_emsal | table | postgres | permanent | heap | 2233 MB |
public | tbl_fragmentation | table | postgres | permanent | heap | 1116 MB |
public | tbl_fragmentation_id_seq | sequence | postgres | permanent | | 8192 bytes |
(3 rows)
vahid=# \di+
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+------------------------+-------+----------+-------------------+-------------+---------------+-------+-------------
public | indname | index | postgres | tbl_fragmentation | permanent | btree | 60 MB |
public | tbl_fragmentation_pkey | index | postgres | tbl_fragmentation | permanent | btree | 43 MB |
(2 rows)Moreover, we can examine tables and indexes which are in USEF database:
vahid=# \c usef
You are now connected to database "usef" as user "postgres".
usef=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+-------+-------------
public | tbl | table | postgres | permanent | heap | 16 kB |
(1 row)
usef=# \di+
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+----------+-------+----------+-------+-------------+---------------+-------+-------------
public | indname | index | postgres | tbl | permanent | btree | 16 kB |
public | tbl_pkey | index | postgres | tbl | permanent | btree | 16 kB |
(2 rows)
usef=# select * from tbl;
id | fname | lname
----+-------+-------------
1 | Vahid | Yousefzadeh
(1 row)Finally, I check the POSTGRES database:
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+---------+-------+----------+-------------+---------------+-------+-------------
public | person | table | postgres | permanent | heap | 16 kB |
public | tb_1405 | table | postgres | permanent | heap | 16 kB |
(2 rows)
postgres=# \di+
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+-------------+-------+----------+--------+-------------+---------------+-------+-------------
public | person_pkey | index | postgres | person | permanent | btree | 16 kB |
(1 row)
postgres=# select * from person;
id | name
----+-------------------
1 | Vahid Yousefzadeh
(1 row)2.Check Installed Extensions
Extensions deserve a separate check because they are installed per database. A successful database restore does not remove the need to verify that the required extension objects are available in the target PostgreSQL installation.
[postgres@OEL98-PG ~]$ psql
psql (14.19)
Type "help" for help.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
vahid=# \c usef
You are now connected to database "usef" as user "postgres".
usef=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)3. Take the Logical Backup
Once the baseline has been recorded, I stop application activity and take the final logical backup. For this migration, pg_dumpall is used to capture all databases and cluster-level objects supported by the logical dump.
pg_dumpall --verbose >>/backup/ALL_Databases_14.sqlDuring the backup process, pg_dumpall displays the objects and data being processed. The following is a sample of the output:
pg_dump: creating DATABASE "vahid"
pg_dump: connecting to new database "vahid"
pg_dump: creating TABLE "public.tb_emsal"
pg_dump: creating TABLE "public.tbl_fragmentation"
pg_dump: creating SEQUENCE "public.tbl_fragmentation_id_seq"
pg_dump: creating SEQUENCE OWNED BY "public.tbl_fragmentation_id_seq"
pg_dump: creating DEFAULT "public.tbl_fragmentation id"
pg_dump: processing data for table "public.tb_emsal"
pg_dump: dumping contents of table "public.tb_emsal"
pg_dump: processing data for table "public.tbl_fragmentation"
pg_dump: dumping contents of table "public.tbl_fragmentation"The backup was successfully created, and its size is approximately 3.2 GB:
[postgres@OEL98-PG ~]$ ls -lh /backup/ALL_Databases_14.sql
-rw-r--r--. 1 postgres postgres 3.2G Sep 28 18:57 /backup/ALL_Databases_14.sql4.Stop PostgreSQL 14
After the backup is complete, PostgreSQL 14 is stopped. Keeping the old data directory untouched at this stage is important because it provides a rollback point while PostgreSQL 18 is being validated.
There are two ways to stop the PostgreSQL 14 instance. The first is to use pg_ctl directly with the PostgreSQL 14 data directory:
[postgres@OEL98-PG ~]$ pg_ctl -D /var/lib/pgsql/14/data -l logfile stop
waiting for server to shut down.... done
server stoppedAlternatively, if PostgreSQL 14 is managed by systemd, we can stop the service using:
[root@OEL98-PG ~]# systemctl stop postgresql-14
[root@OEL98-PG ~]# systemctl status postgresql-14
○ postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; disabled; preset: disabled)
Active: inactive (dead)
Docs: https://www.postgresql.org/docs/14/static/
Sep 28 19:01:11 OEL98-PG systemd[1]: Starting PostgreSQL 14 database server...
Sep 28 19:01:11 OEL98-PG postmaster[2163]: 2026-09-28 19:01:11.296 +03 [2163] LOG: redirecting log output to logging collector process
Sep 28 19:01:11 OEL98-PG postmaster[2163]: 2026-09-28 19:01:11.296 +03 [2163] HINT: Future log output will appear in directory "log".
Sep 28 19:01:11 OEL98-PG systemd[1]: Started PostgreSQL 14 database server.
Sep 28 19:01:15 OEL98-PG systemd[1]: Stopping PostgreSQL 14 database server...
Sep 28 19:01:15 OEL98-PG systemd[1]: postgresql-14.service: Killing process 2164 (postmaster) with signal SIGKILL.
Sep 28 19:01:15 OEL98-PG systemd[1]: postgresql-14.service: Deactivated successfully.
Sep 28 19:01:15 OEL98-PG systemd[1]: Stopped PostgreSQL 14 database server.5.Install and Initialize PostgreSQL 18
PostgreSQL 18 was installed and initialized beforehand. At this stage, I switch to the PostgreSQL 18 environment and verify the configuration, including the PostgreSQL version, data directory, and listening port.
This confirms that the new PostgreSQL 18 instance is ready for the migration and subsequent restoration of the backup.
[postgres@OEL98-PG ~]$ export PG_VERSION=18
[postgres@OEL98-PG ~]$ export PGHOME=/usr/pgsql-18
[postgres@OEL98-PG ~]$ export PGDATA=/var/lib/pgsql/18/data
[postgres@OEL98-PG ~]$ export PGPORT=5418
[postgres@OEL98-PG ~]$ export PATH=$PGHOME/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
[postgres@OEL98-PG ~]$ psql
psql (18.6)
Type "help" for help.
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)
postgres=# show data_directory;
data_directory
------------------------
/var/lib/pgsql/18/data
(1 row)
postgres=# show port;
port
------
5418
(1 row)6.Restore the PostgreSQL 14 Backup
The backup is now restored into PostgreSQL 18. Because this is a logical migration, PostgreSQL 18 recreates the databases, tables, sequences, and indexes from the dump rather than reusing the PostgreSQL 14 data directory.
Note: Server configuration files such as postgresql.conf and pg_hba.conf are separate from the logical dump. They should therefore be reviewed and migrated deliberately rather than assumed to be included in pg_dumpall.
The backup is restored using the PostgreSQL 18 psql executable:
/usr/pgsql-18/bin/psql -f /backup/ALL_Databases_14.sqlDuring the restore, psql executes the SQL statements generated by pg_dumpall. The following is a sample of the output:
You are now connected to database "vahid" as user "postgres".
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 4000000
COPY 2000000
setval
---------
2000000
(1 row)
ALTER TABLE
CREATE INDEX7. Validate PostgreSQL 18
After restoring the backup, I verify the PostgreSQL 18 environment against the information collected from PostgreSQL 14. This includes checking the PostgreSQL version, confirming that the expected databases exist, and comparing their sizes.
[postgres@OEL98-PG ~]$ psql
psql (18.6)
Type "help" for help.
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
usef | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
vahid | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
(5 rows)The expected databases are present in PostgreSQL 18. I then check their sizes:
postgres=# SELECT
datname AS database_name,
ROUND(pg_database_size(datname) / 1024.0 / 1024.0, 2) AS size_mb
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
database_name | size_mb
---------------+---------
vahid | 3460.72
postgres | 7.59
usef | 7.59
template1 | 7.56
template0 | 7.49
(5 rows)Finally, I verify the actual objects and sample data. For a production migration, this validation should also include representative row counts, application connectivity, critical queries, permissions, and scheduled jobs.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+----------
public | person | table | postgres
public | person_id_seq | sequence | postgres
public | tb_1405 | table | postgres
(3 rows)
postgres=# select * from person;
id | name
----+-------------------
1 | Vahid Yousefzadeh
(1 row)
postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------------+-------+----------+-------------+---------------+---------+-------------
public | tb_emsal | table | postgres | permanent | heap | 2233 MB |
public | tbl_fragmentation | table | postgres | permanent | heap | 1117 MB |
(2 rows)
vahid=# \di+
List of indexes
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+------------------------+-------+----------+-------------------+-------------+---------------+-------+-------------
public | indname | index | postgres | tbl_fragmentation | permanent | btree | 60 MB |
public | tbl_fragmentation_pkey | index | postgres | tbl_fragmentation | permanent | btree | 43 MB |
(2 rows)vahid=# \c usef
You are now connected to database "usef" as user "postgres".
usef=# \dt+
List of tables
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+------+-------+----------+-------------+---------------+-------+-------------
public | tbl | table | postgres | permanent | heap | 16 kB |
(1 row)
usef=# \di+
List of indexes
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+----------+-------+----------+-------+-------------+---------------+-------+-------------
public | indname | index | postgres | tbl | permanent | btree | 16 kB |
public | tbl_pkey | index | postgres | tbl | permanent | btree | 16 kB |
(2 rows)
usef=# select * from tbl;
id | fname | lname
----+-------+-------------
1 | Vahid | Yousefzadeh
(1 row)
Comments
Post a Comment