Rocky Linux 9でPostgreSQLを13→17にバージョンアップ

え? うちのPostgreSQLって13だったの?

うちのWebサイトの掲示板とかブログとかアクセスカウンタとかは、RDBMSとしてPostgreSQLを使っています。

2023年にVPSを借りてサイト移行した際に、何も考えずにyumで入れたら、バージョンが13になってました。自宅のWindowsに開発用に入れているやつは17で、そこで作ったプログラムのSQLが動かなくてあれっとなって発覚。まあ13でも今のところサポート切れではないようですが、今年の11月で切れるとなるともうすぐですし、バージョン17で作ったプログラムも動かしたいしで今回バージョンアップしました。

環境はRocky Linux 9.6です。

$ cat /etc/redhat-release
Rocky Linux release 9.6 (Blue Onyx)

やったこと

バックアップ

PostgreSQLではメジャーバージョンアップではDBの内部格納形式が変わることが多いとのことなので、バックアップを取って、新たに17のPostgreSQLをインストールして、そっちにリストアする、という手順になります(pg_upgradeという手順もあるようですが、13から17まで4つも上がると難しいかな、と思った)。

バックアップには、pg_dumpとpg_dumpallコマンドを使います。これはpsqlのコマンドではないのでOSから直接実行します。

個々のDBのバックアップは、pg_dumpで取得します。たとえばうちのアクセスカウンタのDBのDB名はaccesscounterdbなので、以下のようにすればバックアップが取得できます。

$ pg_dump -h localhost -Upostgres -c accesscounterdb > accesscounterdb.dump

これで取得できるのは、「そのままpsqlに食わせることができるコマンドのかたまり」です。冒頭部分を抜粋します

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.20
-- Dumped by pg_dump version 13.20

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

ALTER TABLE ONLY accesscounter.accesscounter DROP CONSTRAINT accesscounter_pkey;
DROP TABLE accesscounter.accesscounter;
DROP SCHEMA accesscounter;
--
-- Name: accesscounter; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA accesscounter;
(後略)

pg_dumpで取得するファイルにはデータベースやテーブルの定義(CREATE DATABSEやCREATE TABLE)が含まれません。そっちはpg_dumpallで取得します。実のところpg_dumpallは「全データベースの定義と中身を取得する」ものなので、こっちだけ使えばpg_dumpはやらなくてもよいのですが、何もかも一気にやるのは怖いという理由で中身を取得しない-sオプションをつけてスキーマだけ取得します(もうひとつの-cオプションは、データベースやテーブルを作る前にDROPするコマンドを追加するオプション)。

$ pg_dumpall -h localhost -Upostgres -c -s > schemas.dump
Passsword:
Passsword:
Passsword:
Passsword:
Passsword:
Passsword:
Passsword:
Passsword:

pg_dumpもpg_dumpallもパスワードを聞いてきますが、pg_dumpallは「中にあるDBの数だけ」パスワードを聞いてきます。すべてに答えてください。

参考にしたページ:

PostgreSQL 17のインストール

Rocky Linuxで何も考えずにPostgreSQLをインストールすると13が入ってしまう、という状況は今でも変わらないようで、17を入れるためにPostgreSQL公式のリポジトリをyumに追加します。

$ sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

17を入れるからには13はアンインストールしたほうがよいのかな、と思いましたが、このへんのページを見ても古いのを消してはいないようなので、そのまま導入。バックアップは(人間が読める形で)取ったとはいえ、やっぱり大事なデータが入ったものを移行完了前に消すのは怖い。

$ sudo yum install postgresql17-server

何度か「y」と答える必要があります。

root権限で初期化を実施、サービスを起動します(13は止めた)。

$ sudo su -
# postgresql-17-setup initdb
# systemctl stop postgresql
# systemctl start postgresql-17

インストール時に作られたposgtresユーザにsuしてpsqlを起動。今回、サーバだけ17に上げてクライアントのほうはまだ13なので、警告が出ます。

ここで、postgresユーザにパスワードを付与しておきます。

$ sudo su - postgres
$ psql
psql (13.20, server 17.5)
WARNING: psql major version 13, server major version 17.
         Some psql features might not work.
Type "help" for help.

postgres=# \password
Enter new password for user "postgres":
Enter it again:
postgres=# exit

OSを再起動してもPostgreSQL 13は上がってこずに17が起動するようにするため、systemctlの無効化と有効化を行います。

$ sudo systemctl disable postgresql
Removed "/etc/systemd/system/multi-user.target.wants/postgresql.service".
$ sudo systemctl enable postgresql-17
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-17.service → /usr/lib/systemd/system/postgresql-17.service.

ただし、この状態だと、postgres以外の一般ユーザでpsqlでログインしようとすると、こんなエラーが出ます。

psql: error: FATAL:  Peer authentication failed for user "postgres"

これは、PostgreSQLの「ホストベース認証(host-based authentication)」の設定で、ローカルからの接続が「peer」になっており、peerでは「OSのユーザ名とデータベース名のユーザ名が一致する必要がある」ためです。まあクラッカーがサーバまで乗り込んでいたらどうしようもないので、peerをtrust(無条件で許可)に変更します。修正するのは/var/lib/pgsql/17/data/pg_hba.confです。

修正前

# "local" is for Unix domain socket connections only
local   all             all                                     peer

修正後

# "local" is for Unix domain socket connections only
local   all             all                                     trust

参考にしたページ:

リストア

PostgreSQL 17が動いたら、いよいよデータのリストアです。

バックアップは、「そのままpsqlに食わせることができるコマンドのかたまり」で取得されているので、まずpg_dumpallで取得したスキーマ情報を食わせて、その後pg_dumpで取得した各データベースのデータを食わせてやればよさそうです。――が、今回ここではまりました。

pg_dumpallで取得したスクリプトには、今回-cオプションをつけたので、既存のDBをDROP DATABASEしたりユーザをDROP ROLEしたりする処理が含まれています。まだないDBやROLEをDROPしようとすればエラーになるわけで、それは無視すればよいのですが、今回、postgresに対するALTER ROLEが含まれていました(これはおそらく-cとは関係ない)。

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md563bdd5ecb8e7e500a2003be679931429';

リストアはpostgres権限で実行し、最初にパスワードを聞かれます。でもその途中でpostgresのパスワードがALTER TABLEで変更されてしまうので、その後の処理の途中で認証エラーでコケます。だいたいここのALTER TABLEで出てきているパスワード(md5~)って正しいパスワードに見えません。今どきPostgreSQLだってパスワードを平文で持っているはずがないので、md5でハッシュ化したものがここに出てる?

postgresユーザは再作成の必要はないので、pg_dumpallで作ったschema.dumpファイルから以下の行を削除しました。

DROP ROLE postgres;

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md563bdd5ecb8e7e500a2003be679931429';

そのうえで、以下を実行。-fオプションは、psqlにファイルから入力するオプションです。

$ psql -h localhost -Upostgres -f schemas.dump postgres

各DBのデータも以下の要領でリストアします(以下はaccesscounterdbの場合)。

$ psql -h localhost -Upostgres -f accesscounterdb.dump accesscounterdb

上記の通り、各ROLEのパスワードは正しいものが復旧されているように見えません。ここまでやってTomcatを再起動して動かしてみたらやっぱり認証エラーでコケました。

そこで、psqlにpostgresでログインして各ROLEのパスワードを変更したら完了です。

postgres=# ALTER ROLE accesscounteruser WITH PASSWORD '<パスワード>'
ALTER ROLE

残件

クライアントはまだ13のままなので毎回警告が出ます。害はありませんが、入れなおさないとなあ。

公開日: 2025/06/28


間違い等ありましたら、掲示板にご連絡願います。

ひとつ前 | ひとつ上のページへ戻る | トップページへ戻る