{"id":2736,"date":"2026-01-08T21:00:04","date_gmt":"2026-01-08T12:00:04","guid":{"rendered":"https:\/\/www.next-hop.net\/blog\/hiraga\/?p=2736"},"modified":"2026-01-11T04:19:39","modified_gmt":"2026-01-10T19:19:39","slug":"upgrade-postgresql-2","status":"publish","type":"post","link":"https:\/\/www.next-hop.net\/blog\/hiraga\/freebsd\/upgrade-postgresql-2\/","title":{"rendered":"PostgreSQL\u3092\u30a2\u30c3\u30d7\u30b0\u30ec\u30fc\u30c9\u3059\u308b2"},"content":{"rendered":"<p>\u524d\u56de\u306e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u30c0\u30f3\u30d7\uff06\u30ea\u30b9\u30c8\u30a2\u3067\u306f\u306a\u304f\u3001pg_upgrade\u3092\u4f7f\u7528\u3057\u3066postgresql\u3092\u30a2\u30c3\u30d7\u30b0\u30ec\u30fc\u30c9\u3057\u307e\u3059\u3002<br \/>\nPostgreSQL 18\u304b\u3089initdb\u3067\u30c1\u30a7\u30c3\u30af\u30b5\u30e0\u304c\u30c7\u30d5\u30a9\u30eb\u30c8\u6709\u52b9\u306b\u306a\u308a\u307e\u3057\u305f\u306e\u3067\u3001\u65e2\u5b58\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u30c1\u30a7\u30c3\u30af\u30b5\u30e0\u304c\u7121\u52b9\u306a\u308918\u4ee5\u964d\u3082\u7121\u52b9\u306b\u3057\u307e\u3059\u3002<\/p>\n<h2>\u524d\u63d0<\/h2>\n<ul>\n<li>FreeBSD 13.5<\/li>\n<li>\u65e7: PostgreSQL 16<\/li>\n<li>\u65b0: PostgreSQL 17 or 18<\/li>\n<\/ul>\n<h3>1. \u30b7\u30a7\u30eb\u30b9\u30af\u30ea\u30d7\u30c8<\/h3>\n<h4>upgrade-postgresql.sh<\/h4>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n#!\/bin\/sh\r\n\r\nexport LANG=C\r\n\r\nWORKDIR=&quot;\/var\/tmp\/pg-upgrade&quot;\r\nOLD_VER=&quot;16&quot;\r\nNEW_VER=&quot;17&quot;\r\nOLD_PKG=&quot;`pkg info | awk &#039;\/^postgresql&#039;${OLD_VER}&#039;-server\/ {print $1}&#039;`&quot;\r\n\r\ncd \/var\/tmp\r\nmkdir -p $WORKDIR\r\npkg create $OLD_PKG\r\ntar xf ${OLD_PKG}.pkg -C $WORKDIR\r\nservice postgresql stop\r\npkg unlock -y postgresql${OLD_VER}-client\r\npkg unlock -y postgresql${OLD_VER}-server\r\npkg remove -y postgresql${OLD_VER}-client\r\npkg install -y postgresql${NEW_VER}-server\r\nif &#x5B; ${NEW_VER} -le 17 -a ${OLD_CHECKSUM_VER} -ge 1 ]; then\r\n    sysrc postgresql_initdb_flags=&quot;--encoding=utf-8 --lc-collate=C --data-checksums&quot;\r\nelif &#x5B; ${NEW_VER} -le 17 -a ${OLD_CHECKSUM_VER} -lt 1 ]; then\r\n    sysrc postgresql_initdb_flags=&quot;--encoding=utf-8 --lc-collate=C&quot;\r\nelif &#x5B; ${NEW_VER} -ge 18 -a ${OLD_CHECKSUM_VER} -ge 1 ]; then\r\n    sysrc postgresql_initdb_flags=&quot;--encoding=utf-8 --lc-collate=C&quot;\r\nelif &#x5B; ${NEW_VER} -ge 18 -a ${OLD_CHECKSUM_VER} -lt 1 ]; then\r\n    sysrc postgresql_initdb_flags=&quot;--encoding=utf-8 --lc-collate=C --no-data-checksums&quot;\r\nfi\r\nservice postgresql initdb\r\nsu -l postgres -c &quot;\/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&quot;\r\ndiff -u \/var\/db\/postgres\/data${NEW_VER}\/pg_hba.conf \/var\/db\/postgres\/data${OLD_VER}\/pg_hba.conf &gt; $WORKDIR\/pg_hba.conf.diff\r\ncd \/var\/db\/postgres\/data${NEW_VER}\r\npatch &lt; $WORKDIR\/pg_hba.conf.diff\r\nchown postgres:postgres pg_hba.conf\r\ncd \/var\/tmp\r\nservice postgresql start\r\npkg lock -y postgresql${NEW_VER}-client\r\npkg lock -y postgresql${NEW_VER}-server\r\nrm -f ${OLD_PKG}.pkg\r\nrm -rf $WORKDIR\r\n#if &#x5B; ${NEW_VER} -ge 18 ]; then\r\n#    \/usr\/local\/bin\/vacuumdb -U postgres --all --analyze-in-stages --missing-stats-only\r\n#    \/usr\/local\/bin\/vacuumdb -U postgres --all --analyze-only\r\n#else\r\n#    \/usr\/local\/bin\/vacuumdb -U postgres --all --analyze-in-stages\r\n#fi\r\n<\/pre>\n<h3>2. Ansible playbook<\/h3>\n<p>2\u56de\u76ee\u4ee5\u964d\u306e\u5b9f\u884c\u3067\u306f\u30a2\u30c3\u30d7\u30b0\u30ec\u30fc\u30c9\u30de\u30fc\u30ab\u30fc\u3092\u30c1\u30a7\u30c3\u30af\u3057\u3001\u51aa\u7b49\u6027\u3092\u4fdd\u8a3c\u3057\u307e\u3059\u3002<br \/>\nforce_checksums: true\u3067\u5f37\u5236\u7684\u306b\u30c1\u30a7\u30c3\u30af\u30b5\u30e0\u3092\u6709\u52b9\u306b\u3057\u307e\u3059\u3002<\/p>\n<h4>update_freebsd_postgresql.yaml<\/h4>\n<pre class=\"brush: yaml; title: ; notranslate\" title=\"\">\r\n---\r\n- name: PostgreSQL major upgrade using pg_upgrade (FreeBSD)\r\n  hosts: postgresql.example.com\r\n  gather_facts: false\r\n  become: true\r\n  vars:\r\n    workdir: \/var\/tmp\/pg-upgrade\r\n    old_ver: &quot;16&quot;\r\n    new_ver: &quot;17&quot;\r\n    force_checksums: false\r\n    pg_user: postgres\r\n    upgrade_marker: &quot;\/var\/db\/postgres\/.upgraded_to_{{ new_ver }}&quot;\r\n  tasks:\r\n    - name: Check upgrade marker\r\n      stat:\r\n        path: &quot;{{ upgrade_marker }}&quot;\r\n      register: upgrade_marker_stat\r\n\r\n    - name: End play if already upgraded\r\n      meta: end_play\r\n      when: upgrade_marker_stat.stat.exists\r\n\r\n    - name: Get installed old PostgreSQL server package name\r\n      shell: |\r\n        pkg info | awk &#039;\/^postgresql{{ old_ver }}-server\/ {print $1}&#039;\r\n      register: old_pkg\r\n      changed_when: false\r\n      failed_when: old_pkg.stdout == &quot;&quot;\r\n\r\n    - name: Create work directory\r\n      file:\r\n        path: &quot;{{ workdir }}&quot;\r\n        state: directory\r\n        mode: &#039;0755&#039;\r\n\r\n    - name: Create package archive of old PostgreSQL server\r\n      command: pkg create {{ old_pkg.stdout }}\r\n      args:\r\n        chdir: \/var\/tmp\r\n        creates: &quot;{{ old_pkg.stdout }}.pkg&quot;\r\n\r\n    - name: Extract old PostgreSQL binaries\r\n      command: tar xf \/var\/tmp\/{{ old_pkg.stdout }}.pkg -C {{ workdir }}\r\n      args:\r\n        creates: &quot;{{ workdir }}\/usr\/local\/bin\/postgres&quot;\r\n\r\n    - name: Stop PostgreSQL service\r\n      service:\r\n        name: postgresql\r\n        state: stopped\r\n\r\n    - name: Unlock old PostgreSQL client package\r\n      command: pkg unlock -y postgresql{{ old_ver }}-client\r\n      failed_when: false\r\n\r\n    - name: Unlock old PostgreSQL server package\r\n      command: pkg unlock -y postgresql{{ old_ver }}-server\r\n      failed_when: false\r\n\r\n    - name: Remove old PostgreSQL client\r\n      community.general.pkgng:\r\n        name: &quot;postgresql{{ old_ver }}-client&quot;\r\n        state: absent\r\n\r\n    - name: Install new PostgreSQL server\r\n      community.general.pkgng:\r\n        name: &quot;postgresql{{ new_ver }}-server&quot;\r\n        state: present\r\n\r\n    - name: Get data page checksum version\r\n      shell: |\r\n        {{ workdir }}\/usr\/local\/bin\/pg_controldata \/var\/db\/postgres\/data{{ old_ver }} |\r\n        awk -F: &#039;\/Data page checksum version\/ {print $2}&#039;\r\n      environment:\r\n        LANG: C\r\n        LC_ALL: C\r\n      register: old_checksum_version\r\n      changed_when: false\r\n\r\n    - name: Enable checksums on old cluster if requested\r\n      shell: &gt;\r\n        {{ workdir }}\/usr\/local\/bin\/pg_checksums --enable \/var\/db\/postgres\/data{{ old_ver }}\r\n      environment:\r\n        LANG: C\r\n        LC_ALL: C\r\n      when:\r\n        - force_checksums\r\n        - old_checksum_version.stdout | int &lt; 1\r\n\r\n    - name: Set initdb flags for PostgreSQL &lt;=17 with checksums\r\n      community.general.sysrc:\r\n        name: postgresql_initdb_flags\r\n        value: &#039;--encoding=utf-8 --lc-collate=C --data-checksums&#039;\r\n      when:\r\n        - new_ver | int &lt; 18\r\n        - old_checksum_version.stdout | int &gt;= 1 or force_checksums\r\n      changed_when: false\r\n\r\n    - name: Set initdb flags for PostgreSQL &lt;=17 without checksums\r\n      community.general.sysrc:\r\n        name: postgresql_initdb_flags\r\n        value: &#039;--encoding=utf-8 --lc-collate=C&#039;\r\n      when:\r\n        - new_ver | int &lt; 18\r\n        - old_checksum_version.stdout | int &lt; 1\r\n        - not force_checksums\r\n      changed_when: false\r\n\r\n    - name: Set initdb flags for PostgreSQL 18+ with checksums (default)\r\n      community.general.sysrc:\r\n        name: postgresql_initdb_flags\r\n        value: &#039;--encoding=utf-8 --lc-collate=C&#039;\r\n      when:\r\n        - new_ver | int &gt;= 18\r\n        - old_checksum_version.stdout | int &gt;= 1 or force_checksums\r\n      changed_when: false\r\n\r\n    - name: Set initdb flags for PostgreSQL 18+ without checksums\r\n      community.general.sysrc:\r\n        name: postgresql_initdb_flags\r\n        value: &#039;--encoding=utf-8 --lc-collate=C --no-data-checksums&#039;\r\n      when:\r\n        - new_ver | int &gt;= 18\r\n        - old_checksum_version.stdout | int &lt; 1\r\n        - not force_checksums\r\n      changed_when: false\r\n\r\n    - name: Initialize new PostgreSQL data directory\r\n      command: service postgresql initdb\r\n\r\n    - name: Run pg_upgrade\r\n      shell: |\r\n        su - {{ pg_user }} -c &quot;\/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 }}&quot;\r\n      args:\r\n        executable: \/bin\/sh\r\n\r\n    - name: Create pg_hba.conf diff between old and new\r\n      command: &gt;\r\n        diff -u\r\n        \/var\/db\/postgres\/data{{ new_ver }}\/pg_hba.conf\r\n        \/var\/db\/postgres\/data{{ old_ver }}\/pg_hba.conf\r\n      register: pg_hba_diff\r\n      changed_when: pg_hba_diff.rc == 1\r\n      failed_when: false\r\n\r\n    - name: Save pg_hba.conf diff file\r\n      copy:\r\n        content: &quot;{{ pg_hba_diff.stdout }}&quot;\r\n        dest: &quot;{{ workdir }}\/pg_hba.conf.diff&quot;\r\n        owner: &quot;{{ pg_user }}&quot;\r\n        group: &quot;{{ pg_user }}&quot;\r\n        mode: &#039;0644&#039;\r\n      when: pg_hba_diff.rc == 1\r\n\r\n    - name: Apply pg_hba.conf patch to new data directory\r\n      shell: patch &lt; {{ workdir }}\/pg_hba.conf.diff\r\n      args:\r\n        chdir: \/var\/db\/postgres\/data{{ new_ver }}\r\n      when: pg_hba_diff.rc == 1\r\n\r\n    - name: Fix ownership of pg_hba.conf after patch\r\n      file:\r\n        path: \/var\/db\/postgres\/data{{ new_ver }}\/pg_hba.conf\r\n        owner: &quot;{{ pg_user }}&quot;\r\n        group: &quot;{{ pg_user }}&quot;\r\n      when: pg_hba_diff.rc == 1\r\n\r\n    - name: Start PostgreSQL service\r\n      service:\r\n        name: postgresql\r\n        state: started\r\n\r\n    - name: Lock PostgreSQL client package\r\n      command: pkg lock -y postgresql{{ new_ver }}-client\r\n      failed_when: false\r\n\r\n    - name: Lock PostgreSQL server package\r\n      command: pkg lock -y postgresql{{ new_ver }}-server\r\n      failed_when: false\r\n\r\n    - name: Create upgrade marker\r\n      file:\r\n        path: &quot;{{ upgrade_marker }}&quot;\r\n        state: touch\r\n        owner: &quot;{{ pg_user }}&quot;\r\n        group: &quot;{{ pg_user }}&quot;\r\n        mode: &#039;0644&#039;\r\n\r\n    - name: Remove package archive of old PostgreSQL server\r\n      file:\r\n        path: &quot;\/var\/tmp\/{{ old_pkg.stdout }}.pkg&quot;\r\n        state: absent\r\n\r\n    - name: Remove work directory\r\n      file:\r\n        path: &quot;{{ workdir }}&quot;\r\n        state: absent\r\n<\/pre>\n<h2>\u304a\u307e\u3051<\/h2>\n<h3>\u30c0\u30a6\u30f3\u30b0\u30ec\u30fc\u30c9\u3059\u308b\u5fc5\u8981\u304c\u3042\u308b\u5834\u5408<\/h3>\n<p>\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306f\u7834\u68c4\u3057\u307e\u3059\u3002<\/p>\n<h4>downgrade-postgresql.yaml<\/h4>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n#!\/bin\/sh\r\n\r\nOLD_VER=&quot;16&quot;\r\nNEW_VER=&quot;17&quot;\r\nPGDATADIR=&quot;\/var\/db\/postgres&quot;\r\n\r\nservice postgresql stop\r\nif &#x5B; -d $PGDATADIR\/data${NEW_VER} ]; then\r\n    rm -rf $PGDATADIR\/data${NEW_VER}\r\nfi\r\nif &#x5B; -f $PGDATADIR\/.upgraded_to_${NEW_VER} ]; then\r\n    rm -f $PGDATADIR\/.upgraded_to_${NEW_VER}\r\nfi\r\npkg unlock -y postgresql${NEW_VER}-client\r\npkg unlock -y postgresql${NEW_VER}-server\r\npkg remove -y postgresql${NEW_VER}-client\r\npkg install -y postgresql${OLD_VER}-server\r\nservice postgresql start\r\npkg lock -y postgresql${OLD_VER}-client\r\npkg lock -y postgresql${OLD_VER}-server\r\n<\/pre>\n<h2>\u53c2\u8003<\/h2>\n<ul>\n<li><a href=\"https:\/\/www.next-hop.net\/blog\/hiraga\/freebsd\/upgrade-postgresql\/\">PostgreSQL\u3092\u30a2\u30c3\u30d7\u30b0\u30ec\u30fc\u30c9\u3059\u308b<\/a><\/li>\n<li><a href=\"https:\/\/www.sraoss.co.jp\/tech-blog\/pgsql\/18-0\/\">PostgreSQL 18 \u306b\u95a2\u3059\u308b\u6280\u8853\u60c5\u5831<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u524d\u56de\u306e\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u30c0\u30f3\u30d7\uff06\u30ea\u30b9\u30c8\u30a2\u3067\u306f\u306a\u304f\u3001pg_upgrade\u3092\u4f7f\u7528\u3057\u3066postgresql\u3092\u30a2\u30c3\u30d7\u30b0\u30ec\u30fc\u30c9\u3057\u307e\u3059\u3002 PostgreSQL 18\u304b\u3089initdb\u3067\u30c1\u30a7\u30c3\u30af\u30b5\u30e0\u304c\u30c7\u30d5\u30a9\u30eb\u30c8\u6709\u52b9\u306b\u306a\u308a\u307e\u3057\u305f\u306e\u3067\u3001\u65e2\u5b58\u30c7\u30fc\u2026 <span class=\"read-more\"><a href=\"https:\/\/www.next-hop.net\/blog\/hiraga\/freebsd\/upgrade-postgresql-2\/\">\u7d9a\u304d\u3092\u8aad\u3080 &raquo;<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[],"class_list":["post-2736","post","type-post","status-publish","format-standard","hentry","category-freebsd"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.next-hop.net\/blog\/hiraga\/wp-json\/wp\/v2\/posts\/2736"}],"collection":[{"href":"https:\/\/www.next-hop.net\/blog\/hiraga\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.next-hop.net\/blog\/hiraga\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.next-hop.net\/blog\/hiraga\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.next-hop.net\/blog\/hiraga\/wp-json\/wp\/v2\/comments?post=2736"}],"version-history":[{"count":54,"href":"https:\/\/www.next-hop.net\/blog\/hiraga\/wp-json\/wp\/v2\/posts\/2736\/revisions"}],"predecessor-version":[{"id":2848,"href":"https:\/\/www.next-hop.net\/blog\/hiraga\/wp-json\/wp\/v2\/posts\/2736\/revisions\/2848"}],"wp:attachment":[{"href":"https:\/\/www.next-hop.net\/blog\/hiraga\/wp-json\/wp\/v2\/media?parent=2736"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.next-hop.net\/blog\/hiraga\/wp-json\/wp\/v2\/categories?post=2736"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.next-hop.net\/blog\/hiraga\/wp-json\/wp\/v2\/tags?post=2736"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}