PostgreSQL Major Upgrade via Streaming Replication

Ein PostgresSQL Major Upgrade von Version 14.6 auf 16.9 via Streaming Replication und pg_upgrade mit minimaler Downtime und Fallback Möglichkeit. Bitte die Release Notes PostgreSQL 16.9 beachten.

Ziel

Migration einer PostgresSQL DB in der Version 14.6 von einem Debian 12.11 auf ein Oracle Linux 8.10. Anschließend ein Upgrade auf dem Zielserver von 14.6 auf Version 16.9. Die Migration erfolgt über Streaming Replication.

Voraussetzungen und Umgebung

	Quell-System	Ziel-System
OS	Debian	Oracle Linux 8.10
Hostname	app-srv1	app-srv2
IP	192.168.177.40/24	192.168.177.41/24
PostgreSQL DB	14.6 (ist)	16.9 (soll)

Streaming Replication konfigurieren

Auf dem Ziel-System app-srv2 werden beide benötigten PostgreSQL Versionen 14.6 und 16.9 installiert. Anschließend wird eine Replikation eingerichtet. Wenn die Replikation steht, wird das Quell-System gesperrt, die Replikation aufgetrennt, das Upgrade auf dem Ziel-System durchgeführt und abschließend das Ziel-System verfügbar gemacht.

Installtion auf dem Ziel-System

Benutzer anlegen:

useradd -m -s /bin/bash -g dba postgres
passwd postgres

Benötigte Pakete installieren.

yum groupinstall "Development Tools" 	# Falls notwendig
yum install libicu libicu-devel
yum install readline-devel

PostgreSQL Versionen installieren

Installtion aus dem Quellcode.

mkdir -p /opt/src/postgres
cd /opt/src/postgres
wget -v https://ftp.postgresql.org/pub/source/v14.6/postgresql-14.6.tar.gz
wget -v https://ftp.postgresql.org/pub/source/v16.9/postgresql-16.9.tar.gz

tar -xvzf postgresql-14.6.tar.gz
tar -xvzf postgresql-16.9.tar.gz

cd /opt/src/postgres/postgresql-14.6
./configure --prefix /opt/pgsql/14_6
make
make install

cd /opt/src/postgres/postgresql-16.9
./configure --prefix /opt/pgsql/16_9
make
make install

Replikation aufbauen – Quell-System

Auf dem Quell-System:

# Einträge in der postgresql.conf
    wal_level = replica
    max_wal_senders = 3
    wal_keep_size = 1024                 # Nicht zwingend notwendig bei slot-Verfahren
    max_slot_wal_keep_size = 1024
		
# Einträge in der pg_hba.conf
    host    replication     rep_user        192.168.177.41/32       md5

# Tip: postgresql.conf und pg_hba.conf mal ohne Schrott betrachten:
    cat postgresql.conf | grep -v -P '^\s*#' | grep -v '^$'
    cat pg_hba.conf | grep -v -P '^\s*#' | grep -v '^$'

# Replication User anlegen:
    psql -p 5432
    CREATE ROLE rep_user WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'rep_user';

# Replicationslot anlegen:
    psql -p 5432
    SELECT pg_create_physical_replication_slot('replication_slot');

# Cluster durchstarten
    systemctl stop my_postgres_cluster_1.service
    systemctl start my_postgres_cluster_1.service
    systemctl status my_postgres_cluster_1.servic
    # oder z.B. so
    /usr/local/pgsql1406/bin/pg_ctl -D /opt/app/pgsql/pgsql1406/data -l /opt/app/pgsql/pgsql1406/log/pgsql1406.log restart

–> Achtung: Damit die Änderungen wirksam werden, muss der Cluster durchgestartet werden!

Replikation aufbauen – Ziel-System

# as root:
    mkdir /opt/pgsql/data
    chown postgres:dba /opt/pgsql/data
    chmod 700 /opt/pgsql/data

Basebackup vom Quell-System erstellen.

/opt/pgsql/14_6/bin/pg_basebackup -h 192.168.177.40 -D /opt/pgsql/data -U rep_user -v -P --wal-method=stream --slot=replication_slot

Die postgresql.conf editieren: Eintrag für hot_standby sowie IP Adresse des Ziel-Systems eintragen!

hot_standby = on
listen_addresses = '192.168.177.41,127.0.0.1'

Standby-File erzeugen.

touch /opt/pgsql/data/standby.signal

Log Directory anlegen.

mkdir /opt/pgsql/data/log

Die postgresql.auto.conf configurieren:

primary_conninfo = 'host=192.168.177.40 port=5432 user=rep_user password=rep_user'
primary_slot_name = 'replication_slot'

Den Cluster auf dem Ziel-System starten.

/opt/pgsql/14_6/bin/pg_ctl -D /opt/pgsql/data -l /opt/pgsql/data/log/postgres_146.log start

Status der Replikation auf dem Ziel-System prüfen.

/opt/pgsql/14_6/bin/psql -U postgres -c "SELECT pg_is_in_recovery();"
	pg_is_in_recovery
	-------------------
	t
	(1 row)

Status der Replikation auf dem Quell-System prüfen.

	psql -p 5432
	SELECT client_addr, state FROM pg_stat_replication;
		client_addr   |   state
		----------------+-----------
		192.168.177.41 | streaming
		(1 row)

Replikation testen

Eine Tabelle auf dem Quell-System anlegen und mit Roboterdaten füttern:

/usr/local/pgsql1406/bin/psql -p 5432
\c my_db

CREATE TABLE public.robots (
    name character varying(200),
    fullname character varying(200),
    nickname character varying(200)
);

INSERT INTO robots (name, fullname, nickname)
	VALUES ('R2-D2', 'R2-D2 Astromech Droid', 'Artoo');

Auf dem Ziel-System prüfen, ob die Tabelle robots da ist und der Roboter Artoo drin steht.

/opt/pgsql/16_9/bin/psql -p 5432
\c my_db

select * from robots;
 name  |       fullname        | nickname
-------+-----------------------+----------
 R2-D2 | R2-D2 Astromech Droid | Artoo
(1 row)

Ein Update der Tabelle auf dem Ziel-Sytem sollte selbstverständlich nicht funktionieren!

UPDATE robots
    SET nickname = 'bottomat'
    WHERE name = 'R2-D2';
    ERROR:  cannot execute UPDATE in a read-only transaction

Replikation prüfen

Auf dem Quell-System

SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS bytes_lag,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag
FROM pg_stat_replication;



   # client_addr: IP des Standbys

   # state: Status der Replikation (z.B. streaming)

   # sent_lsn: Letzte vom Primary gesendete WAL-Position

   # write_lsn: Letzte vom Standby geschriebene WAL-Position

   # flush_lsn: Letzte vom Standby auf Disk geflushte WAL-Position

   # replay_lsn: Letzte vom Standby angewandte WAL-Position

   # bytes_lag: Wie viele Bytes der Standby hinterherhängt

   # lag: Lesbare Darstellung des Lags (z.B. 0 bytes wenn aktuell)

# Wenn replay_lsn gleich sent_lsn ist (oder sehr nah dran), ist der Standby aktuell.

Auf dem Ziel-System:

SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
       pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS bytes_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) AS lag;


   # pg_last_wal_receive_lsn: Letzte empfangene WAL-Position

   # pg_last_wal_replay_lsn: Letzte angewandte WAL-Position

   # bytes_lag: Wie viele Bytes noch nicht angewandt wurden

   # lag: Lesbare Darstellung des Lags

# Wenn der Lag 0 ist (oder sehr klein), ist der Standby aktuell.

Replikation auftrennen

User Zugang auf Quell-System sperren, ggf. Sessions killen

pg_hba.conf (Beispiel lokales Netzt)
    host    all             all             192.168.177.0/24        reject

/usr/local/pgsql1406/bin/psql -p 5432    
    SELECT pg_reload_conf();
# oder
pg_ctl reload -D /opt/app/pgsql/pgsql1406/data

Quell-System Herunterfahren

systemctl stop my_postgres_cluster_1.service
systemctl status my_postgres_cluster_1.service

Ziel-System von einer Standby zu einer Primary umwandeln.

/opt/pgsql/14_6/bin/pg_ctl -D /opt/pgsql/data promote

Prüfen, ob Ziel-System noch Recovery macht.

/opt/pgsql/16_9/bin/psql -p 5432
    SELECT pg_is_in_recovery();
        pg_is_in_recovery
        -------------------
        f
        (1 row)

Prüfen, ob das Standby-File wirklich weg ist.

ls -l /opt/pgsql/data/standby.signal
ls: cannot access '/opt/pgsql/data/standby.signal': No such file or directory

Ziel-System Cluster herunterfahren:

/opt/pgsql/14_6/bin/pg_ctl -D /opt/pgsql/data stop

Ziel-System in postgresql.conf hot_standby deaktivieren:

# hot_standby = on

Ziel-System in postgresql.auto.conf primary_conninfo und primary_slot_name deaktivieren:

# primary_conninfo = 'host=192.168.177.40 port=5432 user=rep_user password=rep_user'
# primary_slot_name = 'replication_slot'

Upgrade auf dem Ziel-System

14.6er Cluster stoppen

/opt/pgsql/14_6/bin/pg_ctl -D /opt/pgsql/data stop

Data Directories bearbeiten

Altes Datenverzeichnis umbenennen und neues Datenverzeichnis für 16.9 erstellen.

as root:
    mv /opt/pgsql/data /opt/pgsql/data_old
    mkdir /opt/pgsql/data
    chmod 700 /opt/pgsql/data
    chown postgres:dba /opt/pgsql/data

16.9er PostgreSQL-Datenbankcluster initialisieren

/opt/pgsql/16_9/bin/initdb -D /opt/pgsql/data

–> Achtung: Den Cluster danach nicht starten!!!

Upgrade auf 16.9 durchführen

Bei großen Datenbanken kann man hier auch die Option –link benutzen um Platz zu sparen. Für das Upgrade am besten in das /opt/pgsql/data wechseln, da sonst möglicherweise eine Fehlermeldung „You must have read and write access in the current directory.“ kommen kann.

cd /opt/pgsql/data
/opt/pgsql/16_9/bin/pg_upgrade \
    --old-datadir=/opt/pgsql/data_old \
    --new-datadir=/opt/pgsql/data \
    --old-bindir=/opt/pgsql/14_6/bin \
    --new-bindir=/opt/pgsql/16_9/bin

16.9er Cluster starten

mkdir /opt/pgsql/data/log
/opt/pgsql/16_9/bin/pg_ctl -D /opt/pgsql/data -l /opt/pgsql/data/log/postgres_169.log start

Vacuum & Statistiken

/opt/pgsql/16_9/bin/vacuumdb --all --analyze-in-stages

Extensions upgraden

SELECT extname, extversion FROM pg_extension;
SELECT name, default_version, installed_version FROM pg_available_extensions;

/opt/pgsql/16_9/bin/psql -d <db> -c "ALTER EXTENSION <extension> UPDATE;"

Nacharbeiten auf der neuen Primary

rep_user droppen

/opt/pgsql/16_9/bin/psql -p 5432
DROP ROLE rep_user;

postgresql.conf und pg_hba.conf

Konfiguration der postgresql.conf und der pg_hba.conf editieren und ggf. anpassen

Nacharbeiten auf der alten Primary – falls erforderlich

Replication Slot und rep_user droppen

Wird die alte Primary am Ende doch noch weiterhin benötigt, kann man abschließend dort den Replication Slot und den rep_user wieder droppen.

psql -p 5432
SELECT pg_drop_replication_slot('replication_slot');
DROP ROLE rep_user;