PostgreSQL
関連記事
PostgreSQL はオープンソースの、コミュニティドリブンな、標準準拠のオブジェクト関係データベースシステムです。
目次
- 1 はじめに
- 2 PostgreSQL のインストール
- 3 最初のデータベース/ユーザーの作成
- 4 PostgreSQL の習熟
- 5 任意の設定
- 6 管理ツール
- 7 PostgreSQL のアップグレード
- 8 トラブルシューティング
- 8.1 小規模なトランザクションのパフォーマンスを向上させる
- 8.2 アイドル状態の際にディスクの書き込みを止めさせる
- 8.3 pg_connect() でデータベースに接続できない
- 8.4 PostgreSQL database unable to start after package update when using extensions
- 8.5 Failing to start a PostgreSQL server with the older version of the database while upgrading to the newer version with extensions
はじめに
"postgres ユーザーになってください" という記述がセクションのところどころで出てくることがあります。この記事では postgres ユーザーで実行するべきコマンドには [postgres]$ と記しています。
postgres ユーザーのシェルになるには root で次のコマンドを実行します:
# su -l postgres
sudo を使う場合、以下のコマンドを使用します:
$ sudo -u postgres -i
postgres ユーザーは PostgreSQL をインストールしたときに自動で作成されます。ユーザーが作成されたら postgres ユーザーのパスワードを設定してください。
PostgreSQL のインストール
postgresql パッケージをインストールしてください。
PostgreSQL を正しく機能させるには、postgres ユーザーでデータベースクラスタを初期化する必要があります。postgres ユーザーになって次のコマンドを実行してください:
[postgres]$ initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'
上記コマンドの説明:
--localeは/etc/locale.confファイルに定義されているロケールを指定します。-Eはデータベースのデフォルトエンコーディングです。-Dはデータベースクラスタが保存されるデフォルトのディレクトリです。
画面に大量の文字が表示され、最後に ... ok が表示されます:
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 "en_GB.UTF-8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/postgres/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok creating template1 database in /var/lib/postgres/data/base/1 ... ok initializing pg_authid ... ok [...]
上記のように表示されれば、初期化が成功しています。exit で通常ユーザーに戻ってください。
postgresql.service を root で起動・有効化してください。新しいバージョンの PostgreSQL パッケージをインストールする前に必要な手順については #PostgreSQL のアップグレードを参照してください。
最初のデータベース/ユーザーの作成
postgres ユーザーになってください。createuser コマンドを使って新しいデータベースユーザーを追加します:
[postgres]$ createuser --interactive
createdb コマンドを使って読み書き権限がある前記のユーザーに新しいデータベースを作成します (データベースユーザーの名前が Linux ユーザーと同じ場合はログインシェルからコマンドを実行してください、もしくはコマンドに -U database-username を加えて下さい):
$ createdb myDatabaseName
PostgreSQL の習熟
データベースシェルにアクセス
postgres ユーザーになってください。基本のデータベースシェル psql を起動します。psql ではデータベースやテーブルの作成・削除・パーミッションの設定・生の SQL コマンドの実行など全てが行えます。-d オプションを使って作成済みのデータベースに接続します (データベースを指定しなかった場合、psql はユーザー名と同じ名前のデータベースにアクセスします)
[postgres]$ psql -d myDatabaseName
便利なコマンド:
ヘルプを表示:
=> \help
特定のデータベースに接続:
=> \c <database>
全てのユーザーとパーミッションレベルを表示:
=> \du
現在のデータベースにある全てのテーブルのサマリー情報を表示:
=> \dt
psql シェルを終了:
=> \q or CTRL+d
多数のメタコマンドが存在しますが、最初は以上のコマンドで十分でしょう。全てのメタコマンドを確認するには、次を実行:
=> \?
任意の設定
リモートホストから PostgreSQL にアクセスできるように設定
PostgreSQL データベースサーバーの設定ファイルは postgresql.conf です。このファイルはサーバーのデータディレクトリ (通常は /var/lib/postgres/data) に配置されています。このフォルダには pg_hba.conf など、その他のメインの設定ファイルも保存されます。
/var/lib/postgres/data/postgresql.conf ファイルを編集します。接続と認証のセクションに listen_addresses という行を追加してください:
listen_addresses = 'localhost,my_local_ip_address'
'*' に設定することで全てのローカルアドレスから使えるようにすることもできます。他の行を注意して見て下さい。
ホストによる認証は /var/lib/postgres/data/pg_hba.conf で設定します。このファイルでは接続を許可するホストをコントロールします。デフォルトでは、データベースのスーパーユーザーを含め、あらゆるデータベースユーザーとして全てのローカルユーザーが接続できるようになっているので注意してください。以下のように行を追加します:
# IPv4 local connections: host all all my_remote_client_ip_address/32 md5
my_remote_client_ip_address はクライアントの IP アドレスに置き換えて下さい。
pg_hba.conf のドキュメントも参照。
設定をした後は、postgresql デーモンを再起動して変更を適用してください。
トラブルシューティングの際はサーバーのログファイルを見て下さい:
$ journalctl -u postgresql
PAM で PostgreSQL を認証する設定
PostgreSQL には様々な認証方法があります。システムパスワードでユーザーの認証を行いたい場合、設定が必要です。まずは PAM を有効にしてください。
例えば、上と同じように設定する場合:
# IPv4 local connections: host all all my_remote_client_ip_address/32 pam
ただし PostgreSQL サーバーは root 権限を使わずに動作するため /etc/shadow を読み込むことができません。postgres グループからファイルにアクセスできるように許可することで問題を解決できます:
# setfacl -m g:postgres:r /etc/shadow
デフォルトのデータディレクトリを変更
新しく作成したデータベースが保存されるデフォルトのディレクトリは /var/lib/postgres/data です。これを変更するには、以下の手順を踏んで下さい:
新しいディレクトリを作成して postgres ユーザーをディレクトリの所有者にする:
# mkdir -p /pathto/pgroot/data # chown -R postgres:postgres /pathto/pgroot
postgres ユーザーになって、新しいクラスタを初期化:
[postgres]$ initdb -D /pathto/pgroot/data
systemd#ユニットファイルの編集に書いてあるようにして設定で変数を上書きする必要があります。まず、以下のコマンドを実行:
# systemctl edit postgresql.service
Systemctl はドロップインの設定ファイルをエディタで開きます。以下のように書き込んでください:
[Service] Environment=PGROOT=/pathto/pgroot/ PIDFile=/pathto/pgroot/data/postmaster.pid
/home ディレクトリをテーブル領域のデフォルトディレクトリとして使いたい場合、以下の行も追加してください:
ProtectHome=false
新しいデータベースのデフォルトエンコーディングを UTF-8 に変更
(createdb blog などで) 新しいデータベースを作成するとき、PostgreSQL は実際にはテンプレートデータベースをコピーしています。2つの定義済みテンプレートが存在します: template0 が標準のテンプレートである一方、オンサイトの template1 は管理者によって変更を加えることができるテンプレートでデフォルトで使用されます。新しいデータベースのエンコーディングを変更するには、オンサイトの template1 を変更します。PostgresSQL シェル (psql) にログインして以下を実行してください:
まず、template1 を削除する必要があります。テンプレートは削除できないので、最初に template1 を通常のデータベースにします:
UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
そして template1 を削除:
DROP DATABASE template1;
次に、新しいデフォルトエンコーディングを使って、template0 から新しいデータベースを作成します:
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
そして template1 をテンプレートに戻します:
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
(任意) このテンプレートに接続できないようにしたい場合、datallowconn を FALSE に設定してください:
UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';
これで新しいデータベースを作成することができるようになりました:
[postgres]$ createdb blog
psql にまたログインしてデータベースを確認すれば、新しいデータベースに適切なエンコーディングがあるのを見れるはずです:
\l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+-----------+-----------+-------+----------------------
blog | postgres | UTF8 | C | C |
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | C | C |
管理ツール
- phpPgAdmin — PostgreSQL のウェブベースの管理ツール。
- pgAdmin — PostgreSQL の GUI ベースの管理ツール。
PostgreSQL のアップグレード
PosgreSQL のメジャーバージョンのアップグレードにはメンテナンスが必要です。
現在使用されているデータベースバージョンの取得
# cat /var/lib/postgres/data/PG_VERSION
データベースを誤って互換性のないバージョンにアップグレードしないようにするために、PostgreSQL パッケージの 更新をスキップすることをお勧めします。
マイナーバージョンのアップグレードは実行しても問題ありません。しかしながら、メジャーバージョンのアップグレードをした場合、データにアクセスできなくなる可能性があります。毎回 PostgreSQL のホームページ を確認してアップグレード時に必要な手順を確認してください。詳しくは バージョン管理ポリシー を参照。
PostgreSQL データベースをアップグレードする方法は大きく2つあります。詳しくは公式のドキュメントを読んで下さい。
pg_upgrade
pg_upgrade を使いたい場合、postgresql-old-upgrade パッケージが存在します。常に最新の PostgreSQL パッケージの1つ前のメジャーバージョンを実行します。新しいバージョンの PostgreSQL と一緒にインストールすることが可能です。
準備ができたら、次のパッケージをアップグレードしてください: postgresql, postgresql-libs, postgresql-old-upgrade。データディレクトリはバージョンによって変わることがないため、pg_upgrade を実行する前に、既存のデータディレクトリの名前を変更して、新しいディレクトリに移動する必要があります。新しいデータベースは、このページの一番上で書いているように初期化する必要があります:
# systemctl stop postgresql.service # mv /var/lib/postgres/data /var/lib/postgres/olddata # mkdir /var/lib/postgres/data /var/lib/postgres/tmp # chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp [postgres]$ initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'
アップグレードは以下のコマンドのように実行します。何も準備をしないままコマンドを実行するのは止めてください。詳しくは 上流の pg_upgrade のドキュメント を参照。
[postgres]$ cd /var/lib/postgres/tmp [postgres]$ pg_upgrade -b /opt/pgsql-9.6/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data
pg_upgrade はアップグレードを実行すると /var/lib/postgres/tmp にスクリプトを作成します。画面上の指示に従ってください。アップグレードが完了したら /var/lib/postgres/tmp ディレクトリは削除してもかまいません。
手動ダンプとリロード
以下のように行います (postgresql-old-upgrade のアップグレードとインストール後に実行):
# systemctl stop postgresql.service # mv /var/lib/postgres/data /var/lib/postgres/olddata # mkdir /var/lib/postgres/data # chown postgres:postgres /var/lib/postgres/data [postgres]$ initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data' [postgres]$ /opt/pgsql-9.6/bin/pg_ctl -D /var/lib/postgres/olddata/ start [postgres]$ pg_dumpall -f /tmp/old_backup.sql [postgres]$ /opt/pgsql-9.6/bin/pg_ctl -D /var/lib/postgres/olddata/ stop # systemctl start postgresql.service [postgres]$ psql -f /tmp/old_backup.sql postgres
トラブルシューティング
小規模なトランザクションのパフォーマンスを向上させる
開発用のローカルマシンで PostgresSQL を使っていて動作が遅い場合、設定で synchronous_commit をオフ にしてみてください。ただし、注意事項 があります。
/var/lib/postgres/data/postgresql.conf
synchronous_commit = off
アイドル状態の際にディスクの書き込みを止めさせる
PostgreSQL は定期的に内部の "statistics" ファイルを更新しています。デフォルトでは、このファイルはディスク上に保存されるため、ノートパソコンのディスクのスピンダウンの妨げになったり、ハードドライブのシーク音が発生したりします。以下の設定オプションを使うことでこのファイルをメモリ上のファイルシステムに再配置することができます:
/var/lib/postgres/data/postgresql.conf
stats_temp_directory = '/run/postgresql'
pg_connect() でデータベースに接続できない
php-pgsql をインストールして php.ini ファイルを編集して extension=pdo_pgsql.so や extension=pgsql.so という行をアンコメントして httpd を再起動してください。
PostgreSQL database unable to start after package update when using extensions
The cause in this case is mostly the existing package is not compiled for the newer version (and it may be up-to-date), the solution is rebuilding the package either manually or waiting for an update to the extension package.
Failing to start a PostgreSQL server with the older version of the database while upgrading to the newer version with extensions
This is caused because the old version of postgres from the package postgresql-old-upgrade does not have the required extensions (.so files) in it's lib directory, the current solution is dirty, and might cause a lot of problems so keep a backup of the database just in case, basically copy the required extension .so files from /usr/lib/postgresql/ to /opt/pgsql-XX/lib/ (remember to replace XX with the major version of postgresql-old-upgrade).
For example, for timescaledb
# cp /usr/lib/postgresql/timescaledb*.so /opt/pgsql-13/lib/
to know the exact files to copy, check the contant of the package of the extension using :
$ pacman -Ql package_name