前回のデータベースのダンプ＆リストアではなく、pg_upgradeを使用してpostgresqlをアップグレードします。
PostgreSQL 18からinitdbでチェックサムがデフォルト有効になりましたので、既存データベースのチェックサムが無効なら18以降も無効にします。
前提
- FreeBSD 13.5
- 旧: PostgreSQL 16
- 新: PostgreSQL 17 or 18
1. シェルスクリプト
upgrade-postgresql.sh
#!/bin/sh
export LANG=C
WORKDIR="/var/tmp/pg-upgrade"
OLD_VER="16"
NEW_VER="17"
OLD_PKG="`pkg info | awk '/^postgresql'${OLD_VER}'-server/ {print $1}'`"
cd /var/tmp
mkdir -p $WORKDIR
pkg create $OLD_PKG
tar xf ${OLD_PKG}.pkg -C $WORKDIR
service postgresql stop
pkg unlock -y postgresql${OLD_VER}-client
pkg unlock -y postgresql${OLD_VER}-server
pkg remove -y postgresql${OLD_VER}-client
pkg install -y postgresql${NEW_VER}-server
if [ ${NEW_VER} -le 17 -a ${OLD_CHECKSUM_VER} -ge 1 ]; then
sysrc postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C --data-checksums"
elif [ ${NEW_VER} -le 17 -a ${OLD_CHECKSUM_VER} -lt 1 ]; then
sysrc postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
elif [ ${NEW_VER} -ge 18 -a ${OLD_CHECKSUM_VER} -ge 1 ]; then
sysrc postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
elif [ ${NEW_VER} -ge 18 -a ${OLD_CHECKSUM_VER} -lt 1 ]; then
sysrc postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C --no-data-checksums"
fi
service postgresql initdb
su -l postgres -c "/usr/local/bin/pg_upgrade -b $WORKDIR/usr/local/bin/ -d /var/db/postgres/data${OLD_VER}/ -B /usr/local/bin/ -D /var/db/postgres/data${NEW_VER}/ -U postgres"
diff -u /var/db/postgres/data${NEW_VER}/pg_hba.conf /var/db/postgres/data${OLD_VER}/pg_hba.conf > $WORKDIR/pg_hba.conf.diff
cd /var/db/postgres/data${NEW_VER}
patch < $WORKDIR/pg_hba.conf.diff
chown postgres:postgres pg_hba.conf
cd /var/tmp
service postgresql start
pkg lock -y postgresql${NEW_VER}-client
pkg lock -y postgresql${NEW_VER}-server
rm -f ${OLD_PKG}.pkg
rm -rf $WORKDIR
#if [ ${NEW_VER} -ge 18 ]; then
# /usr/local/bin/vacuumdb -U postgres --all --analyze-in-stages --missing-stats-only
# /usr/local/bin/vacuumdb -U postgres --all --analyze-only
#else
# /usr/local/bin/vacuumdb -U postgres --all --analyze-in-stages
#fi
2. Ansible playbook
2回目以降の実行ではアップグレードマーカーをチェックし、冪等性を保証します。
force_checksums: trueで強制的にチェックサムを有効にします。
update_freebsd_postgresql.yaml
---
- name: PostgreSQL major upgrade using pg_upgrade (FreeBSD)
hosts: postgresql.example.com
gather_facts: false
become: true
vars:
workdir: /var/tmp/pg-upgrade
old_ver: "16"
new_ver: "17"
force_checksums: false
pg_user: postgres
upgrade_marker: "/var/db/postgres/.upgraded_to_{{ new_ver }}"
tasks:
- name: Check upgrade marker
stat:
path: "{{ upgrade_marker }}"
register: upgrade_marker_stat
- name: End play if already upgraded
meta: end_play
when: upgrade_marker_stat.stat.exists
- name: Get installed old PostgreSQL server package name
shell: |
pkg info | awk '/^postgresql{{ old_ver }}-server/ {print $1}'
register: old_pkg
changed_when: false
failed_when: old_pkg.stdout == ""
- name: Create work directory
file:
path: "{{ workdir }}"
state: directory
mode: '0755'
- name: Create package archive of old PostgreSQL server
command: pkg create {{ old_pkg.stdout }}
args:
chdir: /var/tmp
creates: "{{ old_pkg.stdout }}.pkg"
- name: Extract old PostgreSQL binaries
command: tar xf /var/tmp/{{ old_pkg.stdout }}.pkg -C {{ workdir }}
args:
creates: "{{ workdir }}/usr/local/bin/postgres"
- name: Stop PostgreSQL service
service:
name: postgresql
state: stopped
- name: Unlock old PostgreSQL client package
command: pkg unlock -y postgresql{{ old_ver }}-client
failed_when: false
- name: Unlock old PostgreSQL server package
command: pkg unlock -y postgresql{{ old_ver }}-server
failed_when: false
- name: Remove old PostgreSQL client
community.general.pkgng:
name: "postgresql{{ old_ver }}-client"
state: absent
- name: Install new PostgreSQL server
community.general.pkgng:
name: "postgresql{{ new_ver }}-server"
state: present
- name: Get data page checksum version
shell: |
{{ workdir }}/usr/local/bin/pg_controldata /var/db/postgres/data{{ old_ver }} |
awk -F: '/Data page checksum version/ {print $2}'
environment:
LANG: C
LC_ALL: C
register: old_checksum_version
changed_when: false
- name: Enable checksums on old cluster if requested
shell: >
{{ workdir }}/usr/local/bin/pg_checksums --enable /var/db/postgres/data{{ old_ver }}
environment:
LANG: C
LC_ALL: C
when:
- force_checksums
- old_checksum_version.stdout | int < 1
- name: Set initdb flags for PostgreSQL <=17 with checksums
community.general.sysrc:
name: postgresql_initdb_flags
value: '--encoding=utf-8 --lc-collate=C --data-checksums'
when:
- new_ver | int < 18
- old_checksum_version.stdout | int >= 1 or force_checksums
changed_when: false
- name: Set initdb flags for PostgreSQL <=17 without checksums
community.general.sysrc:
name: postgresql_initdb_flags
value: '--encoding=utf-8 --lc-collate=C'
when:
- new_ver | int < 18
- old_checksum_version.stdout | int < 1
- not force_checksums
changed_when: false
- name: Set initdb flags for PostgreSQL 18+ with checksums (default)
community.general.sysrc:
name: postgresql_initdb_flags
value: '--encoding=utf-8 --lc-collate=C'
when:
- new_ver | int >= 18
- old_checksum_version.stdout | int >= 1 or force_checksums
changed_when: false
- name: Set initdb flags for PostgreSQL 18+ without checksums
community.general.sysrc:
name: postgresql_initdb_flags
value: '--encoding=utf-8 --lc-collate=C --no-data-checksums'
when:
- new_ver | int >= 18
- old_checksum_version.stdout | int < 1
- not force_checksums
changed_when: false
- name: Initialize new PostgreSQL data directory
command: service postgresql initdb
- name: Run pg_upgrade
shell: |
su - {{ pg_user }} -c "/usr/local/bin/pg_upgrade -b {{ workdir }}/usr/local/bin -d /var/db/postgres/data{{ old_ver }} -B /usr/local/bin -D /var/db/postgres/data{{ new_ver }} -U {{ pg_user }}"
args:
executable: /bin/sh
- name: Create pg_hba.conf diff between old and new
command: >
diff -u
/var/db/postgres/data{{ new_ver }}/pg_hba.conf
/var/db/postgres/data{{ old_ver }}/pg_hba.conf
register: pg_hba_diff
changed_when: pg_hba_diff.rc == 1
failed_when: false
- name: Save pg_hba.conf diff file
copy:
content: "{{ pg_hba_diff.stdout }}"
dest: "{{ workdir }}/pg_hba.conf.diff"
owner: "{{ pg_user }}"
group: "{{ pg_user }}"
mode: '0644'
when: pg_hba_diff.rc == 1
- name: Apply pg_hba.conf patch to new data directory
shell: patch < {{ workdir }}/pg_hba.conf.diff
args:
chdir: /var/db/postgres/data{{ new_ver }}
when: pg_hba_diff.rc == 1
- name: Fix ownership of pg_hba.conf after patch
file:
path: /var/db/postgres/data{{ new_ver }}/pg_hba.conf
owner: "{{ pg_user }}"
group: "{{ pg_user }}"
when: pg_hba_diff.rc == 1
- name: Start PostgreSQL service
service:
name: postgresql
state: started
- name: Lock PostgreSQL client package
command: pkg lock -y postgresql{{ new_ver }}-client
failed_when: false
- name: Lock PostgreSQL server package
command: pkg lock -y postgresql{{ new_ver }}-server
failed_when: false
- name: Create upgrade marker
file:
path: "{{ upgrade_marker }}"
state: touch
owner: "{{ pg_user }}"
group: "{{ pg_user }}"
mode: '0644'
- name: Remove package archive of old PostgreSQL server
file:
path: "/var/tmp/{{ old_pkg.stdout }}.pkg"
state: absent
- name: Remove work directory
file:
path: "{{ workdir }}"
state: absent
おまけ
ダウングレードする必要がある場合
データベースは破棄します。
downgrade-postgresql.yaml
#!/bin/sh
OLD_VER="16"
NEW_VER="17"
PGDATADIR="/var/db/postgres"
service postgresql stop
if [ -d $PGDATADIR/data${NEW_VER} ]; then
rm -rf $PGDATADIR/data${NEW_VER}
fi
if [ -f $PGDATADIR/.upgraded_to_${NEW_VER} ]; then
rm -f $PGDATADIR/.upgraded_to_${NEW_VER}
fi
pkg unlock -y postgresql${NEW_VER}-client
pkg unlock -y postgresql${NEW_VER}-server
pkg remove -y postgresql${NEW_VER}-client
pkg install -y postgresql${OLD_VER}-server
service postgresql start
pkg lock -y postgresql${OLD_VER}-client
pkg lock -y postgresql${OLD_VER}-server