Overview
PostgreSQL's latest major version is 12. That being said, it is fairly easy to migrate between versions even major ones. There are couple ways to upgrade, depending on how much you want to risk to compromise with. Practically there are 2 ways of Upgrade in any database:
- In-Place: You upgrade on the same host
- Out-Of-Place: You upgrade using 2nd host, most often via Replication or data migration directly to the new release.
We will discuss both methods in this section. So let's get started:
In-Place
Important to note here is that even the In-place upgrade isn't so risky as with other databases (Oracle / SQL / DB2 and so on). The steps to perform In-Place upgrade in PostgreSQL between major version are pretty straight-forward. So let's get going: In this case, we will also, upgrade from 9.6 ā 10.
Install the new Release
You can install the new version by either compiling the source or using package manager (in my case yum).
[root@localhost 9.6]# yum install postgresql10-server.x86_64 Loaded plugins: fastestmirror, langpacks Dependencies Resolved ============================================================================================================================================================================================================================================================================= Package Arch Version Repository Size ============================================================================================================================================================================================================================================================================= Installing: postgresql10-server x86_64 10.11-2PGDG.rhel7 pgdg10 4.5 M Installing for dependencies: libicu x86_64 50.2-3.el7 base 6.9 M postgresql10 x86_64 10.11-2PGDG.rhel7 pgdg10 1.6 M postgresql10-libs x86_64 10.11-2PGDG.rhel7 pgdg10 356 k Transaction Summary ============================================================================================================================================================================================================================================================================= Installed: postgresql10-server.x86_64 0:10.11-2PGDG.rhel7 Dependency Installed: libicu.x86_64 0:50.2-3.el7 postgresql10.x86_64 0:10.11-2PGDG.rhel7 postgresql10-libs.x86_64 0:10.11-2PGDG.rhel7 Complete!
The new version, will come in it's own binary and data locations:
/var/lib/pgsql/10/data /usr/pgsql-10/bin
Create Database base
After that, we have to create a database base:
-bash-4.2$ /usr/pgsql-10/bin/initdb -D /var/lib/pgsql/10/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/pgsql/10/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... America/New_York
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data -l logfile start
-bash-4.2$
It is very good idea to check for possible compatability problems between the two versions in order to play it safe:
-bash-4.2$ /usr/pgsql-10/bin/pg_upgrade -b /usr/pgsql-9.6/bin/ -B /usr/pgsql-10/bin -d /var/lib/pgsql/9.6/data -D /var/lib/pgsql/10/data -c Performing Consistency Checks on Old Live Server ------------------------------------------------ Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for invalid "unknown" user columns ok Checking for hash indexes ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok *Clusters are compatible* <- This line is VERY IMPORTANT :) -bash-4.2$
From this point, we have two ways we can go.
- Ask pg_upgrade to copy the datafiles from old ā new location. The downtime depends the size of the DB
- Ask pg_upgrade to LINK the datafiles (hard links). The downtime is minimal.
Since we want to have the least downtime, we will use 2nd option
Stop the databases
Since we didn't start the new version. Stop the old database version:
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data/ stop waiting for server to shut down.... done server stopped
Once stoppped, let's upgrade:
Link the new Version
-bash-4.2$ /usr/pgsql-10/bin/pg_upgrade -b /usr/pgsql-9.6/bin/ -B /usr/pgsql-10/bin -d /var/lib/pgsql/9.6/data -D /var/lib/pgsql/10/data -k
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 reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "unknown" user columns 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
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 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/pgsql/9.6/data/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
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
-bash-4.2$
Start the new Cluster
After that, we can delete the old cluster and start the one. Please don't be afraid that the upgrade is via links, in that case links mean that both files point to the same piece of chunk in the hard disk. Meaning as long as we have one pointer (the new version) we can delete the old one (9.6). These AREN'T SYMBOLIC links, these are hard links to the hard disk.
-bash-4.2$ cat delete_old_cluster.sh #!/bin/sh rm -rf '/var/lib/pgsql/9.6/data' -bash-4.2$ rm -rf '/var/lib/pgsql/9.6/data' -bash-4.2$ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data start waiting for server to start....2020-01-20 10:58:18.330 EST [4917] LOG: listening on IPv6 address "::1", port 5432 2020-01-20 10:58:18.330 EST [4917] LOG: listening on IPv4 address "127.0.0.1", port 5432 2020-01-20 10:58:18.332 EST [4917] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2020-01-20 10:58:18.363 EST [4917] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2020-01-20 10:58:18.370 EST [4917] LOG: redirecting log output to logging collector process 2020-01-20 10:58:18.370 EST [4917] HINT: Future log output will appear in directory "log". done server started -bash-4.2$ psql psql (10.11) <- Finally, upgraded to 10.11 Type "help" for help. postgres=#
Out-Of-Place
Out of place of place upgrade usually is done using one of the following:
- Complete installation of the new PostgreSQL on 2nd host and using pg_dumpall, pg_dump and psql to restore the data
- Using logical replication to minimize the downtime to almost 0.
We have two environments:
PostgreSQL 9.1 on Linux 5, which should be migrated to: PostgreSQL 10 on Linux 7
To migrate from such an old version there are two options
- Upgrade the current version to 10 and simply migrate the data using backup/restore
- Backup the current version 9.1 using (pg_dumpall and pg_dump) and restore it (pg_restore) to the new version 10
Since the first path will take waaay longer and since we are lazy AF, we will be using the second path
Backup
In order to backup the data in universal format we have to:
- Dump the globals: Roles & Tablespaces using pg_dumpall
- Dump each database (we will use the custom format ā-Fcā since it is more flexible) using pg_dump -Fc
So let's get going:
Dump Globals