PostgreSQL

2017-07-02T07:01:29Z

Pakutoma: php.infoをphp.iniに訂正(php.iniが正しいはずですし、English版でもphp.iniだったので)

[[Category:データベース管理システム]]
[[Category:ウェブサーバー]]
[[en:PostgreSQL]]
[[it:PostgreSQL]]
[[ru:PostgreSQL]]
[[zh-hans:PostgreSQL]]
{{Related articles start}}
{{Related|PhpPgAdmin}}
{{Related articles end}}
[http://www.postgresql.org/ PostgreSQL] はオープンソースの、コミュニティドリブンな、標準準拠のオブジェクト関係データベースシステムです。

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

== はじめに ==

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

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

sudo を使う場合、以下のコマンドを使用します:
$ sudo -i -u postgres

''postgres'' [[ユーザーとグループ|ユーザー]]は PostgreSQL をインストールしたときに自動で作成されます。ユーザーが作成されたら ''postgres'' ユーザーのパスワードを設定してください。

== PostgreSQL のインストール ==

[[公式リポジトリ]]から {{Pkg|postgresql}} を[[インストール]]してください。

PostgreSQL を正しく機能させるには、''postgres'' ユーザーでデータベースクラスタを初期化する必要があります。''postgres'' ユーザーになって次のコマンドを実行してください:
[postgres]$ initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'

上記コマンドの説明:
* ''--locale'' は {{ic|/etc/locale.conf}} ファイルに定義されているロケールを指定します。
* ''-E'' はデータベースのデフォルトエンコーディングです。
* ''-D'' はデータベースクラスタが保存されるデフォルトのディレクトリです。

画面に大量の文字が表示され、最後に ''... ok'' が表示されます:
{{bc|
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
[...]}}

上記のように表示されれば、初期化が成功しています。{{ic|exit}} で通常ユーザーに戻ってください。

{{ic|postgresql.service}} を root で[[systemd#ユニットを使う|起動・有効化]]してください。

{{Tip|ルートディレクトリを {{ic|/var/lib/postgres}} 以外のディレクトリに変更する場合、サービスファイルを編集する必要があります。ルートディレクトリを {{ic|home}} に配置する場合は {{ic|ProtectHome}} を false に設定してください。}}

{{Warning|データベースを [[Btrfs]] ファイルシステム上に配置する場合は、データベースを作成する前に [[Btrfs#コピーオンライト_.28CoW.29|Copy-on-Write]] を無効化するべきです。[[ZFS]] ファイルシステム上に配置する場合は、[[ZFS#データベース]] を読んでください。}}

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

{{Tip|Linux のユーザー名と PostgreSQL ユーザーの名前を同じにした場合、ログインするユーザーを指定しなくても PostgreSQL のデータベースシェルにアクセスすることができます (非常に便利です)。}}

''postgres'' ユーザーになってください。[http://www.postgresql.org/docs/current/static/app-createuser.html createuser] コマンドを使って新しいデータベースユーザーを追加します:
[postgres]$ createuser --interactive

[http://www.postgresql.org/docs/current/static/app-createdb.html createdb] コマンドを使って読み書き権限がある前記のユーザーに新しいデータベースを作成します (データベースユーザーの名前が Linux ユーザーと同じ場合はログインシェルからコマンドを実行してください、もしくはコマンドに {{ic|-U ''database-username''}} を加えて下さい):
$ createdb myDatabaseName

== PostgreSQL の習熟 ==

=== データベースシェルにアクセス ===

postgres ユーザーになってください。基本のデータベースシェル [http://www.postgresql.org/docs/current/static/app-psql.html psql] を起動します。psql ではデータベースやテーブルの作成・削除・パーミッションの設定・生の SQL コマンドの実行など全てが行えます。{{ic|-d}} オプションを使って作成済みのデータベースに接続します (データベースを指定しなかった場合、{{ic|psql}} はユーザー名と同じ名前のデータベースにアクセスします)
[postgres]$ psql -d myDatabaseName

便利なコマンド:

ヘルプを表示:
=> \help
特定のデータベースに接続:
=> \c <database>
全てのユーザーとパーミッションレベルを表示:
=> \du
現在のデータベースにある全てのテーブルのサマリー情報を表示:
=> \dt
{{ic|psql}} シェルを終了:
=> \q or CTRL+d

多数のメタコマンドが存在しますが、最初は以上のコマンドで十分でしょう。全てのメタコマンドを確認するには、次を実行:
=> \?

== 任意の設定 ==

=== リモートホストから PostgreSQL にアクセスできるように設定 ===

PostgreSQL データベースサーバーの設定ファイルは {{ic|postgresql.conf}} です。このファイルはサーバーのデータディレクトリ (通常は {{ic|/var/lib/postgres/data}}) に配置されています。このフォルダには {{ic|pg_hba.conf}} など、その他のメインの設定ファイルも保存されます。

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

{{ic|/var/lib/postgres/data/postgresql.conf}} ファイルを編集します。接続と認証のセクションに {{ic|listen_addresses}} という行を追加してください:
listen_addresses = 'localhost,''my_local_ip_address'''
'*' に設定することで全てのローカルアドレスから使えるようにすることもできます。他の行を注意して見て下さい。

ホストによる認証は {{ic|/var/lib/postgres/data/pg_hba.conf}} で設定します。このファイルでは接続を許可するホストをコントロールします。デフォルトでは、データベースのスーパーユーザーを含め、あらゆるデータベースユーザーとして全てのローカルユーザーが接続できるようになっているので注意してください。以下のように行を追加します:
# IPv4 local connections:
host all all ''my_remote_client_ip_address''/32 md5
{{ic|my_remote_client_ip_address}} はクライアントの IP アドレスに置き換えて下さい。

[http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html pg_hba.conf] のドキュメントも参照。

設定をした後は、{{ic|postgresql}} デーモンを[[systemd#ユニットを使う|再起動]]して変更を適用してください。

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

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

=== PAM で PostgreSQL を認証する設定 ===

PostgreSQL には様々な認証方法があります。システムパスワードでユーザーの認証を行いたい場合、設定が必要です。まずは [[PAM]] を有効にしてください。

例えば、上と同じように設定する場合:

# IPv4 local connections:
host all all ''my_remote_client_ip_address''/32 pam

ただし PostgreSQL サーバーは root 権限を使わずに動作するため {{ic|/etc/shadow}} を読み込むことができません。postgres グループからファイルにアクセスできるように許可することで問題を解決できます:

# setfacl -m g:postgres:r /etc/shadow

=== デフォルトのデータディレクトリを変更 ===

新しく作成したデータベースが保存されるデフォルトのディレクトリは {{ic|/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

{{ic|/home}} ディレクトリをテーブル領域のデフォルトディレクトリとして使いたい場合、以下の行も追加してください:

ProtectHome=false

=== 新しいデータベースのデフォルトエンコーディングを UTF-8 に変更 ===
{{Note|{{ic|initdb}} に {{ic|-E UTF8}} を付けて実行した場合、このステップは必要ありません。}}
({{ic|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';

{{Note|this last step can create problems when upgrading via {{ic|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 |

== 管理ツール ==

* {{App|[[phpPgAdmin]]|PostgreSQL のウェブベースの管理ツール。|http://phppgadmin.sourceforge.net|{{Pkg|phppgadmin}}}}
* {{App|pgAdmin|PostgreSQL の GUI ベースの管理ツール。|http://www.pgadmin.org/|{{Pkg|pgadmin3}}}}

== PostgreSQL のアップグレード ==

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

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

=== クイックガイド ===

{{ic|pg_hba.conf}} や {{ic|postgresql.conf}} などの設定ファイルに変更を加えている場合、新しい設定ファイルに変更を移してください。{{ic|data_directory}}, {{ic|hba_file}}, {{ic|ident_file}} などのオプションを {{ic|postgresql.conf}} で設定している場合は移行を行う前に一時的に設定を解除してください。

{{hc
|upgrade_pg.sh
|<nowiki>
## Set the old version that we want to upgrade from.
export FROM_VERSION=9.5

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"
</nowiki>}}

==== トラブルシューティング ====

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

; cannot write to log file pg_upgrade_internal.log
; Failure, exiting

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

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

; LC_COLLATE error that says that old and new values are different

: 古いロケールが何なのか確認して ({{ic|C}} や {{ic|en_US.UTF-8}} など)、{{ic|initdb}} を実行する際に指定してください:
: {{bc|<nowiki>sudo -u postgres LC_ALL=C initdb -D /var/lib/postgres/data</nowiki>}}

; There seems to be a postmaster servicing the old cluster.
; Please shutdown that postmaster and try again.

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

: 古い pg データに存在する古い pid を検索:
: {{bc|<nowiki>
$ 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
</nowiki>}}

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

: {{bc|<nowiki>
$ sudo -u postgres mv /var/lib/postgres/data-9.X/postmaster.pid /tmp
</nowiki>}}

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

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

; <nowiki>Your installation references loadable libraries that are missing from the new installation.</nowiki>
; <nowiki>could not load library "$libdir/postgis-2.2":</nowiki>
; <nowiki>ERROR: incompatible library "/usr/lib/postgresql/postgis-2.2.so": version mismatch</nowiki>
; <nowiki>DETAIL: Server is version 9.6, library is version 9.5.</nowiki>

: 新しいバージョンの PostgreSQL に対して前のバージョンの PostGIS を手動でコンパイルして、アップグレードしてから、新しいバージョンをインストールしてください。

: 以下の例では '''PostgreSQL 9.5 + PostGIS 2.2''' から '''PostgreSQL 9.6 + PostGIS 2.3''' にアップグレードしています:

: {{bc|<nowiki>
# Uninstall PostGIS 2.3.
sudo pacman -R postgis

# Download and compile PostGIS 2.2 against PostgreSQL 5.6. The latest sources can be found at http://postgis.net/source/
wget http://download.osgeo.org/postgis/source/postgis-2.2.3.tar.gz
tar -xvf postgis-2.2.3.tar.gz
cd postgis-2.2.3
./configure
make

# Copy just the files we need to upgrade the database.
sudo cp postgis/postgis-2.2.so /usr/lib/postgresql
sudo cp raster/rt_pg/rtpostgis-2.2.so /usr/lib/postgresql
sudo cp topology/postgis_topology-2.2.so /usr/lib/postgresql

# Run pg_upgrade as described above. It should now finish successfully.
[...]

# Remove PostGIS 2.2.
sudo rm /usr/lib/postgresql/postgis-2.2.so
sudo rm /usr/lib/postgresql/rtpostgis-2.2.so
sudo rm /usr/lib/postgresql/postgis_topology-2.2.so

# Install PostGIS 2.3 back again.
sudo pacman -S postgis
</nowiki>}}

; 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"
; <nowiki>ERROR: could not access file "$libdir/pg_upgrade_support": No such file or directory</nowiki>

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

=== 詳細な手順 ===

{{Note|PostgreSQL の公式の [http://www.postgresql.org/docs/current/static/upgrading.html アップグレードドキュメント] に従って下さい。}}

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

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

IgnorePkg = postgresql postgresql-libs

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

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

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

準備ができたら、次のパッケージをアップグレードしてください: {{Pkg|postgresql}}, {{Pkg|postgresql-libs}}, {{Pkg|postgresql-old-upgrade}}。データディレクトリはバージョンによって変わることがないため、{{ic|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'

アップグレードは以下のコマンドのように実行します。何も準備をしないままコマンドを実行するのは止めてください。詳しくは [http://www.postgresql.org/docs/current/static/pgupgrade.html 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/'

==== 手動のダンプとリロード ====

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

{{Note|以下は PostgreSQL 9.4 でのコマンドです。PostgreSQL 9.2 の場合、{{ic|/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 を使っていて動作が遅い場合、設定で [http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT synchronous_commit をオフ] にしてみてください。ただし、[http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT 注意事項] があります。

{{hc|1=/var/lib/postgres/data/postgresql.conf|2=
synchronous_commit = off
}}
=== アイドル状態の際にディスクの書き込みを止めさせる ===

PostgreSQL は定期的に内部の "statistics" ファイルを更新しています。デフォルトでは、このファイルはディスク上に保存されるため、ノートパソコンのディスクのスピンダウンの妨げになったり、ハードドライブのシーク音が発生したりします。以下の設定オプションを使うことでこのファイルをメモリ上のファイルシステムに再配置することができます:

{{hc|1=/var/lib/postgres/data/postgresql.conf|2=
stats_temp_directory = '/run/postgresql'
}}

=== pg_connect() でデータベースに接続できない ===

{{Pkg|php-pgsql}} をインストールして {{ic|php.ini}} ファイルを編集して {{ic|extension<nowiki>=</nowiki>pdo_pgsql.so}} や {{ic|extension<nowiki>=</nowiki>pgsql.so}} という行をアンコメントして httpd を再起動してください。

ArchWiki - 利用者の投稿記録 [ja]

PostgreSQL