postgres pg_upgrade patching upgrade - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/pgupgrade.html
• https://www.crunchydata.com/blog/examining-postgres-upgrades-with-pg_upgrade

1. Backup

pg_basebackup -D /backup/base_bkp_b4_minor_upg -F tar fetch

Polecany backup fizyczny calego klastra.

Backup logiczny jest wolniejszy i tylko pojedyncze bazy ewentualnie pg_dump_al;

mkdir upgrade_backup
cd upgrade_backup
pwd
pg_dump -U postgres -d <debname> -F -c -f <dbname>_buckup.dump 

2. Stop server

select version();

pg_ctl stop

or

sudo systemctl status postgres
sudo systemctl stop postgres
sudo systemctl status postgres

3. Install binaries

yum list postgresql15-server*
sudo yum install postgresql15-15.7-1PDGD.rhe17*

or

sudo apt-get update
sudo apt-get install postgresql-15

4. Start server

pg_ctl starty

sudo systemctl start postgres
sudo systemctl status postgres

5. Verification

psql --version

select version();

psql -U postgres -c "select version();"

Post upgrade task to update statistics

ANALYZE VERBOSE;

For the pg_upgrade utility to work effectively in upgrading the major version to another, the following needs to be done.

• Prepare the environment by stopping the old server (current version server), installing the new server, and making sure that the old cluster is compatible with the new one.
• pg_upgrade utility copies the old cluster’s data to the new cluster’s data directory. It also copies the old bin directory to the new cluster. Once the checks and upgrade are completed successfully, we will need to analyze and optimize the new cluster by running the vacuumdb command provided at the end of the upgrade process. We also need to perform necessary testing of the application to ensure everything with the new version works as expected.

1. Nie ma prepared tranactions

Upgrade method

• Link (Use hard link to references data files ${PGDATA}) --link - is risky!!!
• Copy ( Copies data ${PGDATA} from old cluster to new cluster)

0. Check current verion
1. Create backup
2. Install new binaries

sudo apt-get update
sudo apt-get install postgresql-16 postgresql-server-dev-16 -y

3. Set different port in postgressq.conf

port = 5433

3. stop db

sudo systemctl status postgresql.service
sudo systemctl stop postgresql.service
sudo systemctl status postgresql.service

4. pg upgrade check

/usr/lib/postgresql/16/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/13/main \
--new-datadir=/var/lib/postgresql/16/main \
--old-bindir=/usr/lib/postgresql/13/bin \
--new-bindir=/usr/lib/postgresql/16/bin \
--old-options '-c config_file=/etc/postgresql/13/main/
postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/16/main/
postgresql.conf' \
--check

• /usr/lib/postgresql/16/bin/pg_upgrade : This is the path to the pg_upgrade executable from PostgreSQL 16. The tool is responsible for performing the upgrade from one major version of PostgreSQL to another.
• --old-datadir=/var/lib/postgresql/13/main : Shows the data directory of the old PostgreSQL version (13). This directory contains the database files for PostgreSQL 13.
• --new-datadir=/var/lib/postgresql/16/main : Shows the data directory where the new PostgreSQL version (16) will store its database files.
• --old-bindir=/usr/lib/postgresql/13/bin : Shows the binary directory of the old PostgreSQL version (13). This is where the executables (such as postgres , pg_ctl , and so on) for PostgreSQL13 are located.
• --new-bindir=/usr/lib/postgresql/16/bin : Specifies the binary directory of the new PostgreSQL version (16). This directory contains the executables for PostgreSQL 16.
• --old-options '-c config_file=/etc/postgresql/13/main/postgresql.conf ' : This shows the configuration file to be used by the old server during the upgrade process.
• --new-options '-c config_file=/etc/postgresql/16/main/postgresql.conf ' : This shows the configuration file for the new server during the upgrade process.
• --check : This flag tells pg_upgrade to perform a " dry run " of the upgrade. It checks for potential issues that might prevent the upgrade from being successful but does not actually perform the upgrade. This is an important step to identify any incompatibilities or problems before proceeding with the actual upgrade.

All checks must be marked as " OK " for the upgrade to be successful. This is also indicated at the end as " Clusters are compatible ".

5. pg_upgrade To perform the upgrade from versions 13 to 16, we will take off the --check or -c from the command block and then execute it to upgrade our Postgresql database.

/usr/lib/postgresql/16/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/13/main \
--new-datadir=/var/lib/postgresql/16/main \
--old-bindir=/usr/lib/postgresql/13/bin \
--new-bindir=/usr/lib/postgresql/16/bin \
--old-options '-c config_file=/etc/postgresql/13/main/
postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/16/main/
postgresql.conf'

If any of the checks do not return an ok status, then you must reinstall the new server.

All items upgraded are marked with a status of " ok ", and at the end, it is also shown the successful statement of " upgrade complete ". You are also prompted to optimize the statistics by running the vacuumdb command provided at the end.

5. Zmiana portow nowy klaster 5432 a stary 5433 w postgresql.conf
6. start db

sudo systemctl status postgresql.service
sudo systemctl start postgresql.service
sudo systemctl status postgresql.service

7. post upgrade steps Check verion

select version();

Run the recommended vacuumdb command that was provided at the end of the upgrade results to gather statistics.

/usr/lib/postgresql/16/bin/vaccumdb --all --analyze-in-stages

8. delete old binaries

apt list --installed| grep postgresql
sudo apt-get remove postgresql-13 postgresql-server-dev-13

Next, remove the old configuration, and then switch to user Postgres and delete the old cluster using the last command " ./delete_old_cluster.sh " provided at the end of the upgraded result.

sudo rm  -rf /etc/postgresql/13/
sudo su - postgres
./delete_old_cluster.sh

• The -j (short for " jobs ") option in pg_upgrade is a feature that enables parallel processing during the upgrade. This means that multiple database objects (such as tables and indexes) can be processed simultaneously, instead of one at a time.
• Parallel processing uses multiple threads to carry out the upgrade tasks in parallel, which can drastically reduce the overall time needed, particularly for databases with a large number of objects or high data volume.
• When you run pg_upgrade with the -j option, you specify the number of threads that should be used.The syntax is as shown as follows:

pg_upgrade -b /path/to/old/bin -B /path/to/new/bin -d /
path/to/old/data -D /path/to/new/data -j 4

• The -j 4 option tells pg_upgrade to use 4 threads to process the upgrade tasks in parallel.
• Each thread will independently work on upgrading different database objects. This is especially helpful for systems with multiple CPU cores , as it takes advantage of the hardware’s ability to process multiple tasks concurrently. We did not use the -j option as our database was not big.

When you use the --link option during pg_upgrade , it creates hard links for the database files instead of duplicating (copying) them. A hard link essentially creates another directory entry that points to the same underlying data on disk. This means:

1. There is no physical duplication of data, saving disk space.
2. The process is faster because files aren’t physically copied. The syntax is as shown as follows:

pg_upgrade -b /path/to/old/bin -B /path/to/new/bin -d /path/to/old/data -D /path/to/new/data -j 4 --link
/usr/lib/postgresql/16/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/13/main \
--new-datadir=/var/lib/postgresql/16/main \
--old-bindir=/usr/lib/postgresql/13/bin \
--new-bindir=/usr/lib/postgresql/16/bin \
--old-options '-c config_file=/etc/postgresql/13/main/
postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/16/main/
postgresql.conf' \
-j 4 --link

If you use the --link option, once the upgrade completes, any changes to the files in the new database cluster will also affect the old cluster (since both use the same hard-linked files). This means you cannot safely roll back to the old cluster unless you back it up beforehand. Always backup your database before using --link , even though it saves disk space. If anything goes wrong during or after the upgrade, you’ll have a recovery path. Like with any upgrade process, test the --link upgrade in a staging environment before applying it to production.

Using --link is highly efficient but best suited for cases where you are confident in the upgrade process and the new cluster’s stability. If you are unsure or need to preserve the old cluster independently, it may be better to avoid --link and rely on the traditional copy-based approach.

pg_upgrade --clone

pg_upgrade --link  # -k use hardlink to migration

instead of copying files to the new cluster NOTE: remove this option if you need to keep a local copy of the old cluster's data files. This will increase the time it takes the upgrade to run as well as the size of the cluster on disk. You can NOT go back after using -k.

Reflinks are like a snapshot of a file. The old cluster will be preseved and the new cluster have only a point to old data plus the changed data incrementaly. If you need to start old cluster again,no problem. To use, you need a filesystem that supports reflinks and use pg_upgrade –clone instead –link.

• pg_upgrade with –link option, that use hardlinks.
• pg_upgrade with –clone option uses reflinks, that are a different approach but requires that filesystem have reflinks enabled (mkfs.xfs -m reflink=1)

time /usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-11/bin --new-bindir /usr/pgsql-12/bin --old-datadir /var/lib/pgsql/11/data --new-datadir /var/lib/pgsql/12/data --link --check

You can use pg_upgrade --check to perform only the checks, even if the old server is still running.

Upgrade:

time /usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-11/bin --new-bindir /usr/pgsql-12/bin --old-datadir /var/lib/pgsql/11/data --new-datadir /var/lib/pgsql/12/data --link