メンテナンス・トラブルシュート - データベース - postgreSQL 9.6 → 12


 　クラウディア
1.　概要
2.　調査
3.　バックアップ・サーバ停止
4.　postgreSQL 12 インストール
5.　サーバ起動
6.　リストア
7.　設定
8.　後日 
1.　概要


　これを書いてて気づきましたが、「postgreSQL 9.4」を「9.6」へアップグレードする際の記事がないのは、すんなりいったからでしょうか。

　「openssl」をアップグレード時に、表示されるメッセージで気づきました。



===>  Cleaning for openssl-1.1.1n,1
--->  Cleaning out obsolete shared libraries
--->  Skipping 'www/apache24' (apache24-2.4.52) because a requisite package 'apr-1.7.0.1.6.1_2' (devel/apr1) failed (specify -k to force)
** Listing the failed packages (-:ignored / *:skipped / !:failed)
        - devel/apr1 (marked as IGNORE)
        - security/php80-openssl (port deleted)
        - databases/postgresql96-client (port deleted)
        - devel/apr1 (marked as IGNORE)
        - databases/p5-DBD-Pg (marked as IGNORE)
        * www/apache24 (apache24-2.4.52)



　「postgresql96-client」「postgresql96-client」の「port deleted」ってなんだ？
　2022年3月17日のことです。


2.　調査


　「ports」の状態を見てみると



$ ls -d /usr/ports/databases/postgresql*
/usr/ports/databases/postgresql-cstore_fdw  /usr/ports/databases/postgresql11-plperl
/usr/ports/databases/postgresql-jdbc        /usr/ports/databases/postgresql11-plpython
/usr/ports/databases/postgresql-libpgeasy   /usr/ports/databases/postgresql11-pltcl
/usr/ports/databases/postgresql-libpqxx     /usr/ports/databases/postgresql11-server
/usr/ports/databases/postgresql-mysql_fdw   /usr/ports/databases/postgresql12-client
/usr/ports/databases/postgresql-odbc        /usr/ports/databases/postgresql12-contrib
/usr/ports/databases/postgresql-ogr_fdw     /usr/ports/databases/postgresql12-docs
/usr/ports/databases/postgresql-orafce      /usr/ports/databases/postgresql12-pgtcl
/usr/ports/databases/postgresql-plproxy     /usr/ports/databases/postgresql12-plperl
/usr/ports/databases/postgresql-plv8js      /usr/ports/databases/postgresql12-plpython
/usr/ports/databases/postgresql-prefix      /usr/ports/databases/postgresql12-pltcl
/usr/ports/databases/postgresql-relay       /usr/ports/databases/postgresql12-server
/usr/ports/databases/postgresql-repmgr      /usr/ports/databases/postgresql13-client
/usr/ports/databases/postgresql-rum         /usr/ports/databases/postgresql13-contrib
/usr/ports/databases/postgresql-semver      /usr/ports/databases/postgresql13-docs
/usr/ports/databases/postgresql-tds_fdw     /usr/ports/databases/postgresql13-pgtcl
/usr/ports/databases/postgresql-zhparser    /usr/ports/databases/postgresql13-plperl
/usr/ports/databases/postgresql10-client    /usr/ports/databases/postgresql13-plpython
/usr/ports/databases/postgresql10-contrib   /usr/ports/databases/postgresql13-pltcl
/usr/ports/databases/postgresql10-docs      /usr/ports/databases/postgresql13-server
/usr/ports/databases/postgresql10-pgtcl     /usr/ports/databases/postgresql14-client
/usr/ports/databases/postgresql10-plperl    /usr/ports/databases/postgresql14-contrib
/usr/ports/databases/postgresql10-plpython  /usr/ports/databases/postgresql14-docs
/usr/ports/databases/postgresql10-pltcl     /usr/ports/databases/postgresql14-pgtcl
/usr/ports/databases/postgresql10-server    /usr/ports/databases/postgresql14-plperl
/usr/ports/databases/postgresql11-client    /usr/ports/databases/postgresql14-plpython
/usr/ports/databases/postgresql11-contrib   /usr/ports/databases/postgresql14-pltcl
/usr/ports/databases/postgresql11-docs      /usr/ports/databases/postgresql14-server
/usr/ports/databases/postgresql11-pgtcl



　わはは、「port deleted」だわ。
　では、どのバージョンへアップグレードするか考えましたが、「PostgreSQL日本語ドキュメント」を見ると。
「PostgreSQL日本語ドキュメント」

　2022年3月17日時点で、まだ「postgreSQL 13」は充実していないし、「postgreSQL 14」は存在すらしていない。
　「postgreSQL 12」あたりが、適当かと思った次第です。



/usr/ports/databases/postgresql12-server/Makefile



　を見てみると



# Created by: Marc G. Fournier <scrappy@FreeBSD.org>

DISTVERSION?=   12.10
# PORTREVISION must be ?= otherwise, all slave ports get this PORTREVISION and
# not their own.  Probably best to keep it at ?=0 when reset here too.
PORTREVISION?=  0



　2022年3月17日時点で、バージョン 12.10 だそうであります。


3.　バックアップ・サーバ停止


　以降の、全項目、「root」ユーザ権限で。
　現状のデータをバックアップ（そこまで大きくないので、テキストファイルに保存可能なのだ）。



sudo su postgres -c "pg_dumpall --column-inserts > pg_dumpall.sql"



　サーバ停止。



service postgresql stop



　データベース全体をバックアップ。



cd /var/db/postgres
cp -R data96 data96-20220321-backup



　バックアップのファイル名は、ここではその日の名前にしました。


4.　postgreSQL 12 インストール


　以前の失敗を繰り返さぬように・・・。
　バージョンの変更を行います。



$ pkg set -o databases/postgresql96-client:databases/postgresql12-client
Change origin from databases/postgresql96-client to databases/postgresql12-client for postgresql96-client-9.6.24? [y/N]:



　yEnterを入力します。



$ pkg set -o databases/postgresql96-server:databases/postgresql12-server
Change origin from databases/postgresql96-server to databases/postgresql12-server for postgresql96-server-9.6.24? [y/N]:



　yEnterを入力します。



$ pkg version -vl "?"
postgresql96-client-9.6.24         ?   orphaned: databases/postgresql12-client
postgresql96-server-9.6.24         ?   orphaned: databases/postgresql12-server



　ん？これでええんかいな・・・。



portupgrade -Rr databases/postgresql12-client



　通常は、「server」を更新すれば、「client」も同時に更新されますが、こういう場合は、「client」を先にしないと、怒られるようです。
　でまぁ、「client」を先にしたのですが



===>  postgresql12-client-12.10 conflicts with installed package(s):
      postgresql96-server-9.6.24

      They install files into the same place.
      You may want to stop build with Ctrl + C.
===>  License PostgreSQL accepted by the user
===>   postgresql12-client-12.10 depends on file: /usr/local/sbin/pkg - found
=> postgresql-12.10.tar.bz2 doesn't seem to exist in /usr/ports/distfiles/postgresql.
=> Attempting to fetch https://ftp.postgresql.org/pub/source/v12.10/postgresql-12.10.tar.bz2
postgresql-12.10.tar.bz2                                20 MB 1573 kBps    13s
===> Fetching all distfiles required by postgresql12-client-12.10 for building

===>  postgresql12-client-12.10 conflicts with installed package(s):
      postgresql96-server-9.6.24

      They will not build together.
      Please remove them first with pkg delete.
*** Error code 1

Stop.
make[1]: stopped in /usr/ports/databases/postgresql12-client
*** Error code 1

Stop.
make: stopped in /usr/ports/databases/postgresql12-client
** Command failed [exit code 1]: /usr/bin/script -qa /tmp/portupgrade20220317-3399-nw59kd env UPGRADE_TOOL=portupgrade UPGRADE_PORT=postgresql96-client-9.6.24 UPGRADE_PORT_VER=9.6.24 make
** Fix the problem and try again.
** Listing the failed packages (-:ignored / *:skipped / !:failed)
        - databases/php80-pgsql (marked as IGNORE)
        - databases/postgresql12-server (marked as IGNORE)
        ! databases/postgresql12-client (postgresql96-client-9.6.24)    (unknown build error)



　となりまして・・・。
　「conflicts」が発生するので「pkg delete」しろと・・・。



pkg delete postgresql96-server



　これで、「postgresql12-server」は、アップグレードではなくなったわな・・・。
　再度。



portupgrade -Rr databases/postgresql12-client



　これも、最後の方で



postgresql-contrib
  Lots of contributed utilities, postgresql functions and
  datatypes. There you find pg_standby, pgcrypto and many other cool
  things.

etc...

===>  Cleaning for postgresql12-client-12.10
--->  Cleaning out obsolete shared libraries
** Listing the failed packages (-:ignored / *:skipped / !:failed)
        - databases/php80-pgsql (marked as IGNORE)



　と「databases/php80-pgsql」の「Cleaning」でエラーになったようですが、これは、また後でやりましょうか・・・。



$ pkg version -v | grep postgres
postgresql12-client-12.10          =   up-to-date with index



　にはなっております。



portupgrade -Rr databases/postgresql12-server



　は、もう効かないので・・・。



cd /usr/ports/databases/postgresql12-server
make config



　下記が、デフォルトの状態ですが。
「/usr/ports/databases/postgresql12-server」「make config」

　「XML」は、加えてチェックしておきましょうか。



cd /usr/ports/databases/postgresql12-server
make
make install



　「postgresql12-server」のインストールには、「llvm10」が必要なようです。
　「llm10」のインストールには、相当な時間がかかりますので、「postgreSQL」サーバを停止する時間を短くしたい場合は、サーバを停止する前に「llvm10」を先にインストールしておいた方がいいでしょう。

　実は「postgreSQL」がアップグレードできなかったことが原因で、他にもアップグレードできなかったものがあります。



$ pkg version -vl "<"
apache24-2.4.52                    <   needs updating (index has 2.4.53)
mod_php80-8.0.14                   <   needs updating (index has 8.0.16)
php80-extensions-1.0               <   needs updating (index has 1.1)
php80-pgsql-8.0.14                 <   needs updating (index has 8.0.16)



　これらも、アップグレードできるようになっていますので、アップグレードしておきます。


5.　サーバ起動


　データベースを初期化します。



service postgresql initdb



　下記のメッセージが、表示されました。



The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data12 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Japan
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data12 -l logfile start



　予想はしておりましたが、データベースは



/var/db/postgres/data12/



　に作成されます。
　サーバの起動



service postgresql start



　なんか、メッセージがずいぶん変わりましたわ。



2022-03-18 09:44:13.137 JST [11435] LOG:  starting PostgreSQL 12.10 on amd64-portbld-freebsd12.2, compiled by FreeBSD clang version 10.0.1 (git@github.com:llvm/llvm-project.git llvmorg-10.0.1-0-gef32c611aa2), 64-bit
2022-03-18 09:44:13.166 JST [11435] LOG:  listening on IPv6 address "::1", port 5432
2022-03-18 09:44:13.166 JST [11435] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-03-18 09:44:13.169 JST [11435] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-03-18 09:44:13.198 JST [11435] LOG:  ending log output to stderr
2022-03-18 09:44:13.198 JST [11435] HINT:  Future log output will go to log destination "syslog".



　データベースのディレクトリが変わったので、コンフィグレーションファイルもいじらなければならない・・・。
　まぁ、とりあえずそれは、後回しで、次項のリストアを先にいたします。


6. リストア


　バックアップをとっておいた、ディレクトリへ移動して・・・。



sudo su postgres -c "psql --quiet --username=postgres --file pg_dumpall.sql"



　データをいくつか確認して、とりあえずバージョンアップ作業は一段落しました。


7.　設定


　ディレクトリが変わりましたので、「9.6」のときに設定していた、コンフィグレーションファイルは、ご破算になっています。
　あらためて、下記のファイルの設定をします（内容は、わたしの環境にあわせているものなのでご注意）。



/var/db/postgres/data12/pg_hba.conf
/var/db/postgres/data12/postgresql.conf



　ユーザ権限が、「postgres」にありますので



su postgres -c "vim -c ':set number' -c ':set mouse-=a' /var/db/postgres/data12/pg_hba.conf"



　下記を環境に合わせて変更します。



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust



　データベースをどこの誰からでも参照可能にするには、1カラム目のコメント用「#」をはずすだけですが、これでは危険極まりないので、許可するアドレスを下記の要領で設定します。
TYPE localは UNIX のドメインソケット使用の接続を示し host は TCP/IP を使用した接続を示します。
DATABASEデータベース名を記述します
all を指定すると全データベースを指定することになります
USERロール名を指定します
CIDR-ADDRESS 標準のドット区切り10進表記でのIPアドレスとCIDRマスクの長さを指定します
METHODユーザ認証を行う方法を指定します
identident(RFC1413)による認証を行う
trust認証処理を行わず、スルーでパスさせる
reject接続を拒否する
password [password_file] パスワードファイルによる認証を行う
crypt PostgreSQL のシステムテーブル pg_shadow による認証を行う
kbr4/kbr5Kerberos V4/V5 による認証を行う
 

　「pg_hba.conf」は、上から順に見ていき、条件にマッチした行が見つかると、それが適用されます。
　次に



su postgres -c "vim -c ':set number' -c ':set mouse-=a' /var/db/postgres/data12/postgresql.conf"



　バージョン「9.6」までは、60、64 行を変更していましたが、起動時のメッセージを読むと、少なくともポート番号は、デフォルトで「5432」が設定されているので、編集しなくてもいいかもしれません。



#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)



　ログを



/var/log/postgresql/postgresql.log



　というファイルに出力させ、1日ずつローテーションさせることとしますと



log_destination = 'syslog'
#log_destination = 'stderr'



　を



#log_destination = 'syslog'
log_destination = 'stderr'



　に変えます。以下、矢印で・・・。



#logging_collector = off                # Enable capturing of stderr and csvlog


　　　↓


logging_collector = on                  # Enable capturing of stderr and csvlog






#log_directory = 'log'                  # directory where log files are written,


　　　↓


log_directory = '/var/log/postgresql'   # directory where log files are written,






#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,


　　　↓


log_filename = 'postgresql.log'



　これは、ローテーションの具合をみて書き換えるかも・・・。



#log_rotation_age = 1d                  # Automatic rotation of logfiles will


　　　↓


log_rotation_age = 1d                   # Automatic rotation of logfiles will



　1日ごとにローテーションさせます。
　ローテーションは、「newsyslog.conf」に書いてそちらで制御させようかとも思ったのですが、ファイルの所有権の問題がややこしそうなので、当面こちらで・・・。



#log_line_prefix = ''                   # special values:


　　　↓


log_line_prefix = '%t '                 # special values:



　先頭に出力時刻をつけるようにします。
　後ろにセパレータの空白か何かをいれないと後続のメッセージとくっついて出力されちゃいます。

　初回出力用にフォルダとファイルを作成します。



mkdir -pv /var/log/postgresql
touch /var/log/postgresql/postgresql.log
chown -R postgres:postgres /var/log/postgresql



　「PortgreSQL」を再起動して、ログを確認します。



service postgresql restart



　下記のメッセージが出力されます。



2022-03-18 12:48:47 JST LOG:  starting PostgreSQL 12.10 on amd64-portbld-freebsd12.2, compiled by FreeBSD clang version 10.0.1 (git@github.com:llvm/llvm-project.git llvmorg-10.0.1-0-gef32c611aa2), 64-bit
2022-03-18 12:48:47 JST LOG:  listening on IPv6 address "::1", port 5432
2022-03-18 12:48:47 JST LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-03-18 12:48:47 JST LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-03-18 12:48:47 JST LOG:  redirecting log output to logging collector process
2022-03-18 12:48:47 JST HINT:  Future log output will appear in directory "/var/log/postgresql".



　ログの中身を確認してみますと



2022-03-18 12:48:47 JST LOG:  database system was shut down at 2022-03-18 12:48:47 JST
2022-03-18 12:48:47 JST LOG:  database system is ready to accept connections



　意図通りになりました。


8.　後日


　後日（2022年4月5日）、「FreeBSD 12.2 RELEASE」を「FreeBSD 12.3 RELEASE」へアップグレードした際、「postgreSQL」のサーバが起動できなくなってしまいました。
　依存する「libxml2」がないとかどうとか・・・。
　そういえば、別のページに書いていますが、「libxml2」がらみでトラブルがありました。
　あれこれやったあげく、「libxml2」「postgresql12-server」を再インストールすることになりました。

　すなわち。



cd /usr/ports/textproc/libxml2/
make
make reinstall





cd /usr/ports/databases/postgresql12-server/
make clean
make
make reinstall



　これで、正常に起動できるようになりました。
ハイスピードプラン神戸養蜂場損保との違いそれがだいじWi-Fiマイニングベース健康サポート特集