うちの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の数だけ」パスワードを聞いてきます。すべてに答えてください。
参考にしたページ：
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
間違い等ありましたら、掲示板にご連絡願います。
ひとつ前 | ひとつ上のページへ戻る | トップページへ戻る