PostgreSQL

関連記事

• PhpPgAdmin

PostgreSQL はオープンソースの、コミュニティドリブンな、標準準拠のオブジェクト関係データベースシステムです。

この文章では PostgreSQL をセットアップする方法を説明してします。リモートクライアントから PostgreSQL にアクセスできるようにする設定方法も紹介します。データベース以外のウェブスタックの設定については、LAMP ページが役立つでしょう。MySQL に関連するセクション以外のところを見て下さい。

はじめに

"postgres ユーザーになってください" という記述がセクションのところどころで出てくることがあります。この記事では postgres ユーザーで実行するべきコマンドには [postgres]$ と記しています。

postgres ユーザーのシェルになるには root で次のコマンドを実行します:

# su - postgres

sudo を使う場合、以下のコマンドを使用します:

$ sudo -i -u postgres

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 サービスを root で起動・有効化してください。

警告: データベースを Btrfs ファイルシステム上に配置する場合は、データベースを作成する前に Copy-on-Write を無効化するべきです。ZFS ファイルシステム上に配置する場合は、ZFS#データベース を読んでください。

最初のデータベース/ユーザーの作成

ヒント: Linux のユーザー名と 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 など、その他のメインの設定ファイルも保存されます。

ノート: デフォルトでは、通常ユーザーを使ってこのフォルダを見ることは不可能になっているため、find や locate で conf ファイルを探そうとしても見つかりません。

/var/lib/postgres/data/postgresql.conf ファイルを編集します。接続と認証のセクションに listen_addresses という行を追加してください:

listen_addresses = 'localhost,my_remote_server_ip_address'

他の行を注意して見て下さい。

ホストによる認証は /var/lib/postgres/data/pg_hba.conf で設定します。このファイルでは接続を許可するホストをコントロールします。デフォルトでは、データベースのスーパーユーザーを含め、あらゆるデータベースユーザーとして全てのローカルユーザーが接続できるようになっているので注意してください。以下のように行を追加します:

# IPv4 local connections:
host  all  all  my_remote_client_ip_address/32  md5

your_desired_ip_address はクライアントの IP アドレスに置き換えて下さい。

pg_hba.conf のドキュメントも参照。

設定をした後は、postgresql デーモンを再起動して変更を適用してください。

ノート: PostgreSQL はデフォルトでリモート接続にポート 5432 を使います。このポートが開いていること、接続が受信できることを確認してください。

トラブルシューティングの際はサーバーのログファイルを見て下さい:

$ 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

postgresql.service が有効になっていたら、無効にする。/usr/lib/systemd/system/postgresql.service を /etc/systemd/system/postgresql.service にコピーして、ファイルを編集してデフォルトの PGROOT と PIDFile のパスを変更する。

Environment=PGROOT=/pathto/pgroot/
...
PIDFile=/pathto/pgroot/data/postmaster.pid

新しいデータベースのデフォルトエンコーディングを UTF-8 に変更

ノート: initdb に -E UTF8 を付けて実行した場合、このステップは必要ありません。

(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';

ノート: this last step can create problems when upgrading via pg_upgrade.

これで新しいデータベースを作成することが出来るようになりました:

[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 のウェブベースの管理ツール。

http://phppgadmin.sourceforge.net || phppgadmin

• pgAdmin — PostgreSQL の GUI ベースの管理ツール。

http://www.pgadmin.org/ || pgadmin3

PostgreSQL のアップグレード

PostgreSQL のメジャーバージョン (9.2, 9.3, 9.4, 9.5) のアップグレードにはメンテナンスが必要です。

ヒント: 以前 9.2 から 9.3 にアップグレードしていて、9.3 から 9.4 にアップグレードしたい場合、コマンドを実行する前にバージョンを変えてください。/var/lib/postgres/data-9.2 が存在する場合、全てのコマンドをコピーアンドペーストすると pg_upgrade はデータベースのバージョンが間違っているとエラーを表示します。バージョン 9.3 のデータベースが /var/lib/postgres/data-9.2/data/ に保存されるためです。

クイックガイド

pg_hba.conf や postgresql.conf などの設定ファイルに変更を加えている場合、新しい設定ファイルに変更を移してください。

upgrade_pg.sh

## Set the old version that we want to upgrade from.
export FROM_VERSION=9.4

pacman -S --needed postgresql-old-upgrade
chown postgres:postgres /var/lib/postgres/
su - postgres -c "mv /var/lib/postgres/data /var/lib/postgres/data-${FROM_VERSION}"
su - postgres -c 'mkdir /var/lib/postgres/data'
su - postgres -c "initdb --locale $LANG -E UTF8 -D /var/lib/postgres/data"
su - postgres -c "pg_upgrade -b /opt/pgsql-${FROM_VERSION}/bin/ -B /usr/bin/ -d /var/lib/postgres/data-${FROM_VERSION} -D /var/lib/postgres/data"

トラブルシューティング

pg_upgrade を実行した際に以下のメッセージが表示される場合:

cannot write to log file pg_upgrade_internal.log

Failure, exiting

postgres ユーザーに、ログファイルをディレクトリに書き出す権限があるか確認してください (例えば /tmp)。もしくは sudo -u postgres のかわりに su - postgres を使って下さい。

postgres ユーザーからログファイルを書き出す権限があるはずなのにこのエラーが表示される場合、/var/lib/postgres の所有者が postgres になっていることを確認してください。

LC_COLLATE error that says that old and new values are different

古いロケールが何なのか確認して (C や en_US.UTF-8 など)、initdb を実行する際に指定してください:

sudo -u postgres LC_ALL=C initdb -D /var/lib/postgres/data

There seems to be a postmaster servicing the old cluster.

Please shutdown that postmaster and try again.

postgres が実行していないことを確認してください。エラーが消えない場合、古い PID ファイルを削除する必要があるかもしれません。

古い pg データに存在する古い pid を検索:

  $ sudo -u postgres ls -l /var/lib/postgres/data-9.X
  total 88
  -rw------- 1 postgres postgres     4 Mar 25  2012 PG_VERSION
  drwx------ 8 postgres postgres  4096 Jul 17 00:36 base
  drwx------ 2 postgres postgres  4096 Jul 17 00:38 global
  drwx------ 2 postgres postgres  4096 Mar 25  2012 pg_clog
  -rw------- 1 postgres postgres  4476 Mar 25  2012 pg_hba.conf
  -rw------- 1 postgres postgres  1636 Mar 25  2012 pg_ident.conf
  drwx------ 4 postgres postgres  4096 Mar 25  2012 pg_multixact
  drwx------ 2 postgres postgres  4096 Jul 17 00:05 pg_notify
  drwx------ 2 postgres postgres  4096 Mar 25  2012 pg_serial
  drwx------ 2 postgres postgres  4096 Jul 17 00:53 pg_stat_tmp
  drwx------ 2 postgres postgres  4096 Mar 25  2012 pg_subtrans
  drwx------ 2 postgres postgres  4096 Mar 25  2012 pg_tblspc
  drwx------ 2 postgres postgres  4096 Mar 25  2012 pg_twophase
  drwx------ 3 postgres postgres  4096 Mar 25  2012 pg_xlog
  -rw------- 1 postgres postgres 19169 Mar 25  2012 postgresql.conf
  -rw------- 1 postgres postgres    48 Jul 17 00:05 postmaster.opts
  -rw------- 1 postgres postgres    80 Jul 17 00:05 postmaster.pid   # <-- This is the problem

古い pid を一時ディレクトリに移動:

$ sudo -u postgres mv /var/lib/postgres/data-9.X/postmaster.pid /tmp

ERROR: could not access file "$libdir/postgis-2.0": No such file or directory

postgresql 9.X の postgis から postgis-2.0.so を取得して /opt/pgsql-9.X/lib にコピーし、postgres ユーザーから読み込めるようにパーミッションを設定してください。

Your installation references loadable libraries that are missing from the new installation.

You can add these libraries to the new installation, or remove the functions using them from the old installation.

A list of problem libraries is in the file

loadable_libraries.txt

Could not load library "$libdir/pg_upgrade_support"

ERROR: could not access file "$libdir/pg_upgrade_support": No such file or directory

pg_upgrade の実行に失敗したときのファイルが残っています。古いデータで postgres を起動して、各データベースで次を実行: DROP SCHEMA IF EXISTS binary_upgrade CASCADE;

詳細な手順

ノート: PostgreSQL の公式の アップグレードドキュメント に従って下さい。

警告: 以下の手順はデータを喪失する可能性があります。自己責任でお願いします。

/etc/pacman.conf ファイルに以下を追加することを推奨します:

IgnorePkg = postgresql postgresql-libs

上記のように設定することでデータベースが勝手にアップグレードされることを防げます。アップデートがあった場合、pacman は pacman.conf にエントリがあるためアップグレードをスキップすることを通知します。マイナーバージョンのアップグレード (例: 9.0.3 から 9.0.4) は実行しても問題ありません。しかしながら、メジャーバージョンのアップグレードをした場合 (例: 9.0.x から 9.1.x)、データにアクセスできなくなる可能性があります。毎回 PostgreSQL のホームページ (http://www.postgresql.org/) を確認してアップグレード時に必要な手順を確認してください。詳しくは versioning policy を参照。

PostgreSQL データベースをアップグレードする方法は大きく2つあります。詳しくは公式のドキュメントを読んで下さい。

pg_upgrade を使いたい場合、公式リポジトリに postgresql-old-upgrade パッケージが存在します。常に最新の PostgreSQL パッケージの1つ前のメジャーバージョンを実行します。新しいバージョンの PostgreSQL と一緒にインストールすることが可能です。

準備ができたら、次のパッケージをアップグレードしてください: postgresql, postgresql-libs, postgresql-old-upgrade。データディレクトリはバージョンによって変わることがないため、pg_upgrade を実行する前に、既存のデータディレクトリの名前を変更して、新しいディレクトリに移動する必要があります。新しいデータベースは、このページの一番上で書いているように初期化する必要があります:

# systemctl stop postgresql
# su - postgres -c 'mv /var/lib/postgres/data /var/lib/postgres/olddata'
# su - postgres -c 'initdb --locale $LANG -E UTF8 -D /var/lib/postgres/data'

アップグレードは以下のコマンドのように実行します。何も準備をしないままコマンドを実行するのは止めてください。詳しくは upstream pg_upgrade documentation を参照。

# su - postgres -c 'pg_upgrade -d /var/lib/postgres/olddata/ -D /var/lib/postgres/data/ -b /opt/pgsql-9.4/bin/ -B /usr/bin/'

手動のダンプとリロード

以下のように行います (postgresql-old-upgrade のアップグレードとインストール後に実行):

ノート: 以下は PostgreSQL 9.4 でのコマンドです。PostgreSQL 9.2 の場合、/opt/ に同じようなコマンドがあるはずです。

# systemctl stop postgresql
# /opt/pgsql-9.4/bin/pg_ctl -D /var/lib/postgres/olddata/ start
# /opt/pgsql-9.4/bin/pg_dumpall >> old_backup.sql
# /opt/pgsql-9.4/bin/pg_ctl -D /var/lib/postgres/olddata/ stop
# systemctl start postgresql
# psql -f 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.info ファイルを編集して extension=pdo_pgsql.so や extension=pgsql.so という行をアンコメントして httpd を再起動してください。