pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g., from 8.4.7 to the current major release of PostgreSQL. It is not required for minor version upgrades, e.g., from 9.0.1 to 9.0.4.
Major PostgreSQL releases regularly add new features that often change the layout of the system tables, but the internal data storage format rarely changes. pg_upgrade uses this fact to perform rapid upgrades by creating new system tables and simply reusing the old user data files. If a future major release ever changes the data storage format in a way that makes the old data format unreadable, pg_upgrade will not be usable for such upgrades. (The community will attempt to avoid such situations).
pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g., by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade.
pg_upgrade supports upgrades from 8.4.X and later to the current major release of PostgreSQL, including snapshot and alpha releases.
In this demo we will give an example upgrading from postgresql 9.5 to 13, before we proceed, just make sure there are two postgresql version running on your machine, the old version and the new version. For example, if we using Ubuntu it will look like this :
postgres@DESKTOP-JF5OGU9:~$ ls /usr/lib/postgresql/
12 13 9.5This mean we have three version of postgresql, but this time we only use 9.5 and 13 version.
Other things that need to be concerned is about authentication (pg_hba.conf), because pg_upgrade will connect to the old and new servers several times, so you might want to set authentication to peer in pg_hba.conf. but setting this authentication only when you want upgrade postgresql on different server or machine, if we do this on local we can skip this step.
For testing purpose we are going to do this on local, so we don’t need to set anything on pg_hba.
This demo will try to upgrade postgresql database version 9.5 with 1,7GB data on it to postgresql 13. Bellow here are the list of database and it size on 9.5:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+---------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 1736 MB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 6857 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 6976 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)While on postgresql 13 there is no data
- Stop both server
Before we proceed, make sure both database cluster is stoped, we can check using this command:
postgres@DESKTOP-JF5OGU9:~$ ps aux | grep postgres
postgres 978 0.0 0.2 215516 19076 ? S 09:30 0:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postgresql/9.5/main/postgresql.conf
postgres 980 0.0 0.0 215516 1188 ? Ss 09:30 0:00 postgres: 9.5/main: checkpointer process
postgres 981 0.0 0.0 215516 2112 ? Ss 09:30 0:00 postgres: 9.5/main: writer process
postgres 982 0.0 0.0 215516 1124 ? Ss 09:30 0:00 postgres: 9.5/main: wal writer process
postgres 983 0.0 0.0 215964 2092 ? Ss 09:30 0:00 postgres: 9.5/main: autovacuum launcher process
postgres 984 0.0 0.0 70644 1088 ? Ss 09:30 0:00 postgres: 9.5/main: stats collector process
root 1345 0.0 0.0 16524 2036 tty1 S 10:00 0:00 su - postgres
postgres 1346 0.4 0.0 16788 3356 tty1 S 10:00 0:00 -su
postgres 1359 1.8 0.2 222212 20304 ? Ss 10:00 0:00
/usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
postgres 1361 0.0 0.0 222212 1148 ? Ss 10:00 0:00 postgres: 13/main: checkpointer
postgres 1362 0.0 0.0 222212 1272 ? Ss 10:00 0:00 postgres: 13/main: background writer
postgres 1363 0.0 0.0 222212 1172 ? Ss 10:00 0:00 postgres: 13/main: walwriter
postgres 1364 0.1 0.0 222760 2148 ? Ss 10:00 0:00 postgres: 13/main: autovacuum launcher
postgres 1365 0.1 0.0 76704 1156 ? Ss 10:00 0:00 postgres: 13/main: stats collector
postgres 1366 0.0 0.0 222764 1872 ? Ss 10:00 0:00 postgres: 13/main: logical replication launcher
postgres 1378 0.0 0.0 17392 1904 tty1 R 10:00 0:00 ps aux
postgres 1379 0.0 0.0 14812 1180 tty1 S 10:00 0:00 grep postgresAs you can see there are two running server, to stop these, we can use this command on Ubuntu:
postgres@DESKTOP-JF5OGU9:~$ pg_ctlcluster 9.5 main stop
postgres@DESKTOP-JF5OGU9:~$ pg_ctlcluster 13 main stop- Run pg_upgrade with check option
pg_upgrade will verify the two clusters are compatible and then do the upgrade. You can use pg_upgrade –check to perform only the checks, even if the old server is still running. pg_upgrade –check will also outline any manual adjustments you will need to make after the upgrade.
Here we are on ubuntu, the command will look like this:
postgres@DESKTOP-JF5OGU9:~$ /usr/lib/postgresql/13/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.5/main \
--new-datadir=/var/lib/postgresql/13/main \
--old-bindir=/usr/lib/postgresql/9.5/bin \
--new-bindir=/usr/lib/postgresql/13/bin \
--old-options '-c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf' \
--checkThe output will look like this:
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for invalid "unknown" user columns ok
Checking for hash indexes ok
Checking for roles starting with "pg_" ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
*Clusters are compatible*If everthings fine, Now we can proceed to the next step
- Run pg_upgrade
Now for the real one, we will using link mode to upgrade this database. The benefit for this method is much faster (no file copying) and use less disk space, but you will not be able to access your old cluster once you start the new cluster after the upgrade. Link mode also requires that the old and new cluster data directories be in the same file system. The command for this upgrade are follow:
/usr/lib/postgresql/13/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.5/main \
--new-datadir=/var/lib/postgresql/13/main \
--old-bindir=/usr/lib/postgresql/9.5/bin \
--new-bindir=/usr/lib/postgresql/13/bin \
--old-options '-c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/13/main/postgresql.conf' \
--linkthe output should be like this :
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for invalid "unknown" user columns ok
Checking for roles starting with "pg_" ok
Creating dump of global objects ok
Creating dump of database schemas ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_clog to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster ok
Adding ".old" suffix to old global/pg_control ok
If you want to start the old cluster, you will need to remove
the ".old" suffix from /var/lib/postgresql/9.5/main/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking user relation files ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok
Checking for hash indexes ok
Checking for extension updates ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh- Start the new server
Before we start database server (postgresql-13), we need to run delete_old_cluster script first, it is contain one line to remove the old database directory. After that we can safely start the new cluster using this command:
postgres@DESKTOP-JF5OGU9:~$ pg_ctlcluster 13 main startThen connect to the new server
postgres@DESKTOP-JF5OGU9:~$ /usr/lib/postgresql/13/bin/psql -p 5434
psql (13.4 (Ubuntu 13.4-1.pgdg18.04+1))
Type "help" for help.
postgres=#Check the database size
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+---------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 1737 MB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 7925 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | postgres=CTc/postgres+| 7909 kB | pg_default | default template for new databases
| | | | | =c/postgres | | |
(3 rows)
(END)And we’re all done here. We have check the size of old and the new are the same. the database has been upgraded from PostgreSQL 9.5 to 13
Leave a Reply