「PostgreSQL」の版間の差分

提供: ArchWiki
ナビゲーションに移動 検索に移動
 
(3人の利用者による、間の20版が非表示)
1行目: 1行目:
[[Category:データベース管理システム]]
+
[[Category:関係データベース管理システム]]
[[Category:ウェブサーバー]]
 
 
[[en:PostgreSQL]]
 
[[en:PostgreSQL]]
 
[[it:PostgreSQL]]
 
[[it:PostgreSQL]]
 
[[ru:PostgreSQL]]
 
[[ru:PostgreSQL]]
[[zh-CN:PostgreSQL]]
+
[[zh-hans:PostgreSQL]]
 
{{Related articles start}}
 
{{Related articles start}}
 
{{Related|PhpPgAdmin}}
 
{{Related|PhpPgAdmin}}
 
{{Related articles end}}
 
{{Related articles end}}
[http://www.postgresql.org/ PostgreSQL] はオープンソースの、コミュニティドリブンな、標準準拠のオブジェクト関係データベースシステムです。
+
[https://www.postgresql.org/ PostgreSQL] はオープンソースの、コミュニティドリブンな、標準準拠のオブジェクト関係データベースシステムです。
 
この文章では PostgreSQL をセットアップする方法を説明してします。リモートクライアントから PostgreSQL にアクセスできるようにする設定方法も紹介します。データベース以外のウェブスタックの設定については、[[LAMP]] ページが役立つでしょう。[[MySQL]] に関連するセクション以外のところを見て下さい。
 
   
 
== はじめに ==
 
== はじめに ==
17行目: 14行目:
   
 
''postgres'' ユーザーのシェルになるには root で次のコマンドを実行します:
 
''postgres'' ユーザーのシェルになるには root で次のコマンドを実行します:
# su - postgres
+
# su -l postgres
   
 
sudo を使う場合、以下のコマンドを使用します:
 
sudo を使う場合、以下のコマンドを使用します:
$ sudo -i -u postgres
+
$ sudo -u postgres -i
   
''postgres'' [[Users and Groups|ユーザー]]は PostgreSQL をインストールしたときに自動で作成されます。ユーザーが作成されたら ''postgres'' ユーザーのパスワードを設定してください。
+
''postgres'' [[ユーザーとグループ|ユーザー]]は PostgreSQL をインストールしたときに自動で作成されます。ユーザーが作成されたら ''postgres'' ユーザーのパスワードを設定してください。
   
 
== PostgreSQL のインストール ==
 
== PostgreSQL のインストール ==
   
[[公式リポジトリ]]から {{Pkg|postgresql}} を[[インストール]]してください。
+
{{Pkg|postgresql}} パッケージを[[インストール]]してください。
   
 
PostgreSQL を正しく機能させるには、''postgres'' ユーザーでデータベースクラスタを初期化する必要があります。''postgres'' ユーザーになって次のコマンドを実行してください:
 
PostgreSQL を正しく機能させるには、''postgres'' ユーザーでデータベースクラスタを初期化する必要があります。''postgres'' ユーザーになって次のコマンドを実行してください:
[postgres]$ initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'
+
[postgres]$ initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'
   
  +
上記コマンドの説明:
{{ic|postgresql}} サービスを[[systemd#ユニットを使う|起動・有効化]]してください。
 
  +
* {{ic|--locale}} は {{ic|/etc/locale.conf}} ファイルに定義されているロケールを指定します。
  +
* {{ic|-E}} はデータベースのデフォルトエンコーディングです。
  +
* {{ic|-D}} はデータベースクラスタが保存されるデフォルトのディレクトリです。
   
  +
画面に大量の文字が表示され、最後に {{ic|... ok}} が表示されます:
{{Warning|
 
  +
{{bc|
* データベースを [[Btrfs|Btrfs]] ファイルシステム上に配置する場合は、データベースを作成する前に [[Btrfs#コピーオンライト_.28CoW.29|Copy-on-Write]] を無効化するべきです。
 
  +
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#ユニットを使う|起動・有効化]]してください。新しいバージョンの PostgreSQL パッケージをインストールする前に必要な手順については [[#PostgreSQL のアップグレード]]を参照してください。
  +
  +
{{Tip|ルートディレクトリを {{ic|/var/lib/postgres}} 以外のディレクトリに変更する場合、サービスファイルを編集する必要があります。ルートディレクトリを {{ic|home}} に配置する場合は {{ic|ProtectHome}} を false に設定してください。}}
  +
  +
{{Warning|データベースを [[Btrfs]] ファイルシステム上に配置する場合は、データベースを作成する前に [[Btrfs#コピーオンライト_.28CoW.29|Copy-on-Write]] を無効化するべきです。[[ZFS]] ファイルシステム上に配置する場合は、[[ZFS#データベース]]を読んでください。}}
   
 
== 最初のデータベース/ユーザーの作成 ==
 
== 最初のデータベース/ユーザーの作成 ==
   
  +
{{Tip|Linux のユーザー名と PostgreSQL ユーザーの名前を同じにした場合、ログインするユーザーを指定しなくても PostgreSQL のデータベースシェルにアクセスすることができます (非常に便利です)。}}
{{Tip|If you create a PostgreSQL user with the same name as your Linux username, it allows you to access the PostgreSQL database shell without having to specify a user to login (which makes it quite convenient).}}
 
   
''postgres'' ユーザーになってください。[http://www.postgresql.org/docs/9.0/static/app-createuser.html createuser] コマンドを使って新しいデータベースユーザーを追加します:
+
''postgres'' ユーザーになってください。[https://www.postgresql.jp/document/9.6/html/app-createuser.html createuser] コマンドを使って新しいデータベースユーザーを追加します:
 
[postgres]$ createuser --interactive
 
[postgres]$ createuser --interactive
   
[http://www.postgresql.org/docs/9.0/static/app-createdb.html createdb] コマンドを使って読み書き権限がある前記のユーザーに新しいデータベースを作成します (データベースユーザーの名前が Linux ユーザーと同じ場合はログインシェルからコマンドを実行してください、もしくはコマンドに {{ic|-U ''database-username''}} を加えて下さい):
+
[https://www.postgresql.jp/document/9.6/html/app-createdb.html createdb] コマンドを使って読み書き権限がある前記のユーザーに新しいデータベースを作成します (データベースユーザーの名前が Linux ユーザーと同じ場合はログインシェルからコマンドを実行してください、もしくはコマンドに {{ic|-U ''database-username''}} を加えて下さい):
 
$ createdb myDatabaseName
 
$ createdb myDatabaseName
   
51行目: 75行目:
 
=== データベースシェルにアクセス ===
 
=== データベースシェルにアクセス ===
   
postgres ユーザーになってください。基本のデータベースシェル [http://www.postgresql.org/docs/8.3/static/app-psql.html psql] を起動します。psql ではデータベースやテーブルの作成・削除・パーミッションの設定・生の SQL コマンドの実行など全てが行えます。"-d" オプションを使って作成済みのデータベースに接続します (データベースを指定しなかった場合、psql はユーザー名と同じ名前のデータベースにアクセスします)
+
postgres ユーザーになってください。基本のデータベースシェル [https://www.postgresql.jp/document/9.6/html/app-psql.html psql] を起動します。psql ではデータベースやテーブルの作成・削除・パーミッションの設定・生の SQL コマンドの実行など全てが行えます。{{ic|-d}} オプションを使って作成済みのデータベースに接続します (データベースを指定しなかった場合、{{ic|psql}} はユーザー名と同じ名前のデータベースにアクセスします)
 
[postgres]$ psql -d myDatabaseName
 
[postgres]$ psql -d myDatabaseName
   
 
便利なコマンド:
 
便利なコマンド:
   
ヘルプを表示
+
ヘルプを表示:
 
=> \help
 
=> \help
特定のデータベースに接続
+
特定のデータベースに接続:
 
=> \c <database>
 
=> \c <database>
全てのユーザーとパーミッションレベルを表示
+
全てのユーザーとパーミッションレベルを表示:
 
=> \du
 
=> \du
現在のデータベースにある全てのテーブルのサマリー情報を表示
+
現在のデータベースにある全てのテーブルのサマリー情報を表示:
 
=> \dt
 
=> \dt
psql シェルを終了
+
{{ic|psql}} シェルを終了:
 
=> \q or CTRL+d
 
=> \q or CTRL+d
   
多数のメタコマンドが存在しますが、最初は以上のコマンドで十分でしょう。
+
多数のメタコマンドが存在しますが、最初は以上のコマンドで十分でしょう。全てのメタコマンドを確認するには、次を実行:
  +
=> \?
   
 
== 任意の設定 ==
 
== 任意の設定 ==
78行目: 103行目:
   
 
{{ic|/var/lib/postgres/data/postgresql.conf}} ファイルを編集します。接続と認証のセクションに {{ic|listen_addresses}} という行を追加してください:
 
{{ic|/var/lib/postgres/data/postgresql.conf}} ファイルを編集します。接続と認証のセクションに {{ic|listen_addresses}} という行を追加してください:
listen_addresses = 'localhost,my_remote_ip_address'
+
listen_addresses = 'localhost,''my_local_ip_address'''
他の行を注意して見て下さい。
+
'*' に設定することで全てのローカルアドレスから使えるようにすることもできます。他の行を注意して見て下さい。
   
 
ホストによる認証は {{ic|/var/lib/postgres/data/pg_hba.conf}} で設定します。このファイルでは接続を許可するホストをコントロールします。デフォルトでは、データベースのスーパーユーザーを含め、あらゆるデータベースユーザーとして全てのローカルユーザーが接続できるようになっているので注意してください。以下のように行を追加します:
 
ホストによる認証は {{ic|/var/lib/postgres/data/pg_hba.conf}} で設定します。このファイルでは接続を許可するホストをコントロールします。デフォルトでは、データベースのスーパーユーザーを含め、あらゆるデータベースユーザーとして全てのローカルユーザーが接続できるようになっているので注意してください。以下のように行を追加します:
 
# IPv4 local connections:
 
# IPv4 local connections:
host all all ''my_remote_ip_address''/32 md5
+
host all all ''my_remote_client_ip_address''/32 md5
{{ic|your_desired_ip_address}} はクライアントの IP アドレスに置き換えて下さい。
+
{{ic|my_remote_client_ip_address}} はクライアントの IP アドレスに置き換えて下さい。
   
[http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html pg_hba.conf] のドキュメントも参照。
+
[https://www.postgresql.jp/document/9.6/html/auth-pg-hba-conf.html pg_hba.conf] のドキュメントも参照。
  +
  +
{{Note|SSL で接続が保護されていない場合、平文パスワードや md5 ハッシュ (上記の例で使用しています) をインターネット経由で送信しないほうがセキュアです。PostgreSQL で SSL を使うように設定する方法は [https://www.postgresql.org/docs/current/static/ssl-tcp.html Secure TCP/IP Connections with SSL] を参照。}}
   
 
設定をした後は、{{ic|postgresql}} デーモンを[[systemd#ユニットを使う|再起動]]して変更を適用してください。
 
設定をした後は、{{ic|postgresql}} デーモンを[[systemd#ユニットを使う|再起動]]して変更を適用してください。
   
  +
{{Note|PostgreSQL はデフォルトでリモート接続にポート {{ic|5432}} を使います。このポートが開いていること、接続が受信できることを確認してください。}}
{{Note|Postgresql uses port 5432 by default for remote connections. So make sure this port is open and able to receive incoming connections.}}
 
   
 
トラブルシューティングの際はサーバーのログファイルを見て下さい:
 
トラブルシューティングの際はサーバーのログファイルを見て下さい:
 
$ journalctl -u postgresql
 
$ 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
   
 
=== デフォルトのデータディレクトリを変更 ===
 
=== デフォルトのデータディレクトリを変更 ===
104行目: 144行目:
 
''postgres'' ユーザーになって、新しいクラスタを初期化:
 
''postgres'' ユーザーになって、新しいクラスタを初期化:
 
[postgres]$ initdb -D /pathto/pgroot/data
 
[postgres]$ initdb -D /pathto/pgroot/data
  +
{{ic|postgres.service}} が有効になっていたら、無効にする。{{ic|/usr/lib/systemd/system/postgresql.service}} を {{ic|/etc/systemd/system/postgresql.service}} にコピーして、ファイルを編集してデフォルトの {{ic|PGROOT}} と {{ic|PIDFile}} のパスを変更する。
 
  +
[[systemd#ユニットファイルの編集]]に書いてあるようにして設定で変数を上書きする必要があります。まず、以下のコマンドを実行:
  +
# systemctl edit postgresql.service
  +
  +
Systemctl はドロップインの設定ファイルをエディタで開きます。以下のように書き込んでください:
  +
  +
[Service]
 
Environment=PGROOT=''/pathto/pgroot/''
 
Environment=PGROOT=''/pathto/pgroot/''
...
 
 
PIDFile=''/pathto/pgroot/''data/postmaster.pid
 
PIDFile=''/pathto/pgroot/''data/postmaster.pid
  +
  +
{{ic|/home}} ディレクトリをテーブル領域のデフォルトディレクトリとして使いたい場合、以下の行も追加してください:
  +
  +
ProtectHome=false
   
 
=== 新しいデータベースのデフォルトエンコーディングを UTF-8 に変更 ===
 
=== 新しいデータベースのデフォルトエンコーディングを UTF-8 に変更 ===
{{Note|If you ran {{ic|initdb}} with {{ic|-E UTF8}} these steps are not required}}
+
{{Note|{{ic|initdb}} {{ic|-E UTF8}} を付けて実行した場合、このステップは必要ありません。}}
 
({{ic|createdb blog}} などで) 新しいデータベースを作成するとき、PostgreSQL は実際にはテンプレートデータベースをコピーしています。2つの定義済みテンプレートが存在します: template0 が標準のテンプレートである一方、オンサイトの template1 は管理者によって変更を加えることができるテンプレートでデフォルトで使用されます。新しいデータベースのエンコーディングを変更するには、オンサイトの template1 を変更します。PostgresSQL シェル (psql) にログインして以下を実行してください:
 
({{ic|createdb blog}} などで) 新しいデータベースを作成するとき、PostgreSQL は実際にはテンプレートデータベースをコピーしています。2つの定義済みテンプレートが存在します: template0 が標準のテンプレートである一方、オンサイトの template1 は管理者によって変更を加えることができるテンプレートでデフォルトで使用されます。新しいデータベースのエンコーディングを変更するには、オンサイトの template1 を変更します。PostgresSQL シェル (psql) にログインして以下を実行してください:
   
124行目: 173行目:
 
UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';
 
UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';
   
  +
{{Note|最後のコマンドは {{ic|pg_upgrade}} でアップグレードしたときに問題が発生する可能性があります。}}
{{Note|this last step can create problems when upgrading via {{ic|pg_upgrade}}.}}
 
   
 
これで新しいデータベースを作成することが出来るようになりました:
 
これで新しいデータベースを作成することが出来るようになりました:
144行目: 193行目:
   
 
* {{App|[[phpPgAdmin]]|PostgreSQL のウェブベースの管理ツール。|http://phppgadmin.sourceforge.net|{{Pkg|phppgadmin}}}}
 
* {{App|[[phpPgAdmin]]|PostgreSQL のウェブベースの管理ツール。|http://phppgadmin.sourceforge.net|{{Pkg|phppgadmin}}}}
* {{App|pgAdmin|PostgreSQL の GUI ベースの管理ツール。|http://www.pgadmin.org/|{{Pkg|pgadmin3}}}}
+
* {{App|pgAdmin|PostgreSQL の GUI ベースの管理ツール。|http://www.pgadmin.org/|{{Pkg|pgadmin4}}}}
   
 
== PostgreSQL のアップグレード ==
 
== PostgreSQL のアップグレード ==
   
  +
PosgreSQL のメジャーバージョンのアップグレードにはメンテナンスが必要です。
=== クイックガイド ===
 
   
  +
{{Note|
以下は 9.2 から 9.3 へのアップグレードです。
 
  +
* PostgreSQL の公式の [https://www.postgresql.org/docs/current/static/upgrading.html アップグレードドキュメント] に従ってください。
  +
* バージョン {{ic|10.0}} から PostgreSQL のバージョンの命名規則は変更されています [https://www.postgresql.org/about/news/1786/]。前は {{ic|9.''x''}} から {{ic|9.''y''}} への変更がメジャーアップグレードとされていましたが、現在はバージョン {{ic|10.''x''}} から {{ic|10.''y''}} へのアップグレードはマイナーアップグレードで、バージョン {{ic|10.''x''}} から {{ic|11.''y''}} のアップグレードがメジャーアップグレードになります。}}
   
  +
{{Warning|以下のコマンドを実行するとデータが失われる可能性があります。アップグレードは自己責任で行ってください。}}
pacman -S --needed postgresql-old-upgrade
 
su -
 
su - postgres -c 'mv /var/lib/postgres/data /var/lib/postgres/data-9.2'
 
su - postgres -c 'mkdir /var/lib/postgres/data'
 
su - postgres -c 'initdb --locale en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data'
 
   
  +
現在使用されているデータベースバージョンの取得
pg_hba.conf や postgresql.conf などの設定ファイルをカスタマイズしている場合、新しい設定ファイルにマージしてください。そして:
 
   
  +
# cat /var/lib/postgres/data/PG_VERSION
su - postgres -c 'pg_upgrade -b /opt/pgsql-9.2/bin/ -B /usr/bin/ -d /var/lib/postgres/data-9.2 -D /var/lib/postgres/data'
 
   
  +
データベースを誤って互換性のないバージョンにアップグレードしないようにするために、PostgreSQL パッケージの [[pacman#アップグレードさせないパッケージを設定|更新をスキップ]]することをお勧めします。
"pg_upgrade" が失敗する場合:
 
* ''cannot write to log file pg_upgrade_internal.log<br /> Failure, exiting'' <br />"postgres" ユーザーに、ログファイルをディレクトリに書き出す権限がちゃんとあるか確認してください (例えば {{ic|/tmp}})。もしくは "sudo -u postgres" のかわりに "su - postgres" を使って下さい。
 
* ''LC_COLLATE error that says that old and new values are different''<br />Figure out what the old locale was, C or en_US.UTF-8 for example, and force it when calling initdb.
 
sudo -u postgres LC_ALL=C initdb -D /var/lib/postgres/data
 
   
  +
マイナーバージョンのアップグレードは実行しても問題ありません。しかしながら、メジャーバージョンのアップグレードをした場合、データにアクセスできなくなる可能性があります。毎回 [https://www.postgresql.org/ PostgreSQL のホームページ] を確認してアップグレード時に必要な手順を確認してください。詳しくは [https://www.postgresql.org/support/versioning バージョン管理ポリシー] を参照。
* ''There seems to be a postmaster servicing the old cluster.<br/>Please shutdown that postmaster and try again.''<br/>Make sure postgres isn't running. If you still get the error then chances are these an old PID file you need to clear out.
 
> sudo -u postgres ls -l /var/lib/postgres/data-9.2
 
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
 
 
> sudo -u postgres mv /var/lib/postgres/data-9.2/postmaster.pid /tmp
 
* ''ERROR: could not access file "$libdir/postgis-2.0": No such file or directory'' <br> Retrieve postgis-2.0.so from postgis package for version postgresql 9.2 () and copy it to /opt/pgsql-9.2/lib (make sure the privileges are right)
 
   
  +
{{Note|拡張機能を使用する場合は、[[#PostgreSQL database unable to start after package update when using extensions]] および [[#Failing to start a PostgreSQL server with the older version of the database while upgrading to the newer version with extensions]] をチェックしてください。}}
=== 詳細な手順 ===
 
   
{{Note|PostgreSQL の公式の [http://www.postgresql.org/docs/current/static/upgrading.html アップグレードドキュメント] に従って下さい。}}
+
PostgreSQL データベースをアップグレードする方法は大きく2つあります。詳しくは公式のドキュメントを読んで下さい。
   
  +
=== pg_upgrade ===
以下の手順はデータを喪失する可能性があります。自己責任でお願いします。
 
   
  +
{{ic|pg_upgrade}} を使いたい場合、{{Pkg|postgresql-old-upgrade}} パッケージが存在します。常に最新の PostgreSQL パッケージの1つ前のメジャーバージョンを実行します。新しいバージョンの PostgreSQL と一緒にインストールすることが可能です。
It is recommended to add the following to your {{ic|/etc/pacman.conf}} file:
 
IgnorePkg = postgresql postgresql-libs
 
This will ensure you do not accidentally upgrade the database to an incompatible version. When an upgrade is available, pacman will notify you that it is skipping the upgrade because of the entry in pacman.conf. Minor version upgrades (e.g., 9.0.3 to 9.0.4) are safe to perform. However, if you do an accidental upgrade to a different major version (e.g., 9.0.X to 9.1.X), you might not be able to access any of your data. Always check the PostgreSQL home page (http://www.postgresql.org/) to be sure of what steps are required for each upgrade. For a bit about why this is the case see the [http://www.postgresql.org/support/versioning versioning policy].
 
   
  +
準備ができたら、次のパッケージをアップグレードしてください: {{Pkg|postgresql}}, {{Pkg|postgresql-libs}}, {{Pkg|postgresql-old-upgrade}}。データディレクトリはバージョンによって変わることがないため、{{ic|pg_upgrade}} を実行する前に、既存のデータディレクトリの名前を変更して、新しいディレクトリに移動する必要があります。新しいデータベースは、このページの一番上で書いているように初期化する必要があります:
PostgreSQL データベースをアップグレードする方法は大きく2つあります。詳しくは公式のドキュメントを読んで下さい。
 
   
  +
# systemctl stop postgresql.service
For those wishing to use {{ic|pg_upgrade}}, a {{Pkg|postgresql-old-upgrade}} package is available in the repositories that will always run one major version behind the real PostgreSQL package. This can be installed side by side with the new version of PostgreSQL. When you are ready to perform the upgrade, you can do
 
  +
# mv /var/lib/postgres/data /var/lib/postgres/olddata
pacman -Syu postgresql postgresql-libs postgresql-old-upgrade
 
  +
# mkdir /var/lib/postgres/data /var/lib/postgres/tmp
Note also that the data directory does not change from version to version, so before running pg_upgrade it is necessary to rename your existing data directory and migrate into a new directory. The new database must be initialized, as described near the top of this page.
 
  +
# chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp
  +
[postgres]$ initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'
   
  +
アップグレードは以下のコマンドのように実行します。何も準備をしないままコマンドを実行するのは止めてください。詳しくは [https://www.postgresql.jp/document/9.6/html/pgupgrade.html 上流の pg_upgrade のドキュメント] を参照。
# systemctl stop postgresql
 
# su - postgres -c 'mv /var/lib/postgres/data /var/lib/postgres/olddata'
 
# su - postgres -c 'initdb --locale en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data'
 
   
  +
[postgres]$ cd /var/lib/postgres/tmp
詳しくは上流の [http://www.postgresql.org/docs/current/static/pgupgrade.html pg_upgrade のドキュメント] を参照。
 
  +
[postgres]$ pg_upgrade -b /opt/pgsql-9.6/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data
   
  +
pg_upgrade はアップグレードを実行すると {{ic|/var/lib/postgres/tmp}} にスクリプトを作成します。画面上の指示に従ってください。アップグレードが完了したら {{ic|/var/lib/postgres/tmp}} ディレクトリは削除してもかまいません。
The upgrade invocation will likely look something like the following (run as the postgres user). '''Do not run this command blindly without understanding what it does!'''
 
   
  +
=== 手動ダンプとリロード ===
# su - postgres -c 'pg_upgrade -d /var/lib/postgres/olddata/ -D /var/lib/postgres/data/ -b /opt/pgsql-8.4/bin/ -B /usr/bin/'
 
   
  +
以下のように行います ({{Pkg|postgresql-old-upgrade}} のアップグレードとインストール後に実行):
==== 手動のダンプとリロード ====
 
   
  +
{{Note|
You could also do something like this (after the upgrade and install of postgresql-old-upgrade) (NB: below is command for postgres8.4 update, you can find similar command in /opt/ for postgres 9.2 update. )
 
  +
* 以下は PostgreSQL 9.6 でのコマンドです。PostgreSQL 9.2 の場合、{{ic|/opt/}} に同じようなコマンドがあるはずです。
  +
* {{ic|pg_hba.conf}} ファイルをカスタマイズしている場合、ローカル環境から古いデータベースクラスタへの完全なアクセスを許可するように一時的に修正が必要です。アップグレードが完了したら新しいデータベースクラスタを同じようにカスタマイズして {{ic|postgresql.service}} を[[再起動]]してください。}}
   
# systemctl stop postgresql
+
# systemctl stop postgresql.service
# /opt/pgsql-8.4/bin/pg_ctl -D /var/lib/postgres/olddata/ start
+
# mv /var/lib/postgres/data /var/lib/postgres/olddata
  +
# mkdir /var/lib/postgres/data
# /opt/pgsql-8.4/bin/pg_dumpall >> old_backup.sql
 
# /opt/pgsql-8.4/bin/pg_ctl -D /var/lib/postgres/olddata/ stop
+
# chown postgres:postgres /var/lib/postgres/data
  +
[postgres]$ initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'
# systemctl start postgresql
 
  +
[postgres]$ /opt/pgsql-9.6/bin/pg_ctl -D /var/lib/postgres/olddata/ start
# psql -f old_backup.sql postgres
 
  +
[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
   
 
== トラブルシューティング ==
 
== トラブルシューティング ==
232行目: 259行目:
 
=== 小規模なトランザクションのパフォーマンスを向上させる ===
 
=== 小規模なトランザクションのパフォーマンスを向上させる ===
   
If you are using PostgresSQL on a local machine for development and it seems slow, you could try turning [http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT synchronous_commit off] in the configuration. Beware of the [http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT caveats], however.
+
開発用のローカルマシンで PostgresSQL を使っていて動作が遅い場合、設定で [https://www.postgresql.jp/document/9.6/html/runtime-config-wal.html#guc-synchronous-commit synchronous_commit をオフ] にしてみてください。ただし、[https://www.postgresql.jp/document/9.6/html/runtime-config-wal.html#guc-synchronous-commit 注意事項] があります。
   
 
{{hc|1=/var/lib/postgres/data/postgresql.conf|2=
 
{{hc|1=/var/lib/postgres/data/postgresql.conf|2=
247行目: 274行目:
 
=== pg_connect() でデータベースに接続できない ===
 
=== pg_connect() でデータベースに接続できない ===
   
{{Pkg|php-pgsql}} をインストールして {{ic|php.info}} ファイルを編集して {{ic|extension<nowiki>=</nowiki>pdo_pgsql.so}} や {{ic|extension<nowiki>=</nowiki>pgsql.so}} という行をアンコメントして httpd を再起動してください。
+
{{Pkg|php-pgsql}} をインストールして {{ic|php.ini}} ファイルを編集して {{ic|extension<nowiki>=</nowiki>pdo_pgsql.so}} や {{ic|extension<nowiki>=</nowiki>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 [[Arch Build System|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 {{Pkg|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 {{ic|/usr/lib/postgresql/}} to {{ic|/opt/pgsql-XX/lib/}} (remember to replace XX with the major version of {{Pkg|postgresql-old-upgrade}}).
  +
  +
For example, for timescaledb
  +
# cp /usr/lib/postgresql/timescaledb*.so /opt/pgsql-13/lib/
  +
  +
{{Warning|while copying the .so files was enough for me, it might be required to copy more files to the correct directories under {{ic|/opt/pgsql-XX/}}}}
  +
  +
to know the exact files to copy, check the contant of the package of the extension using :
  +
$ pacman -Ql ''package_name''
  +
  +
{{Warning|This is a very dirty solution that may break or cause data loss in the database, so keep a backup}}

2022年5月2日 (月) 15:01時点における最新版

関連記事

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

はじめに

"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 のアップグレードを参照してください。

ヒント: ルートディレクトリを /var/lib/postgres 以外のディレクトリに変更する場合、サービスファイルを編集する必要があります。ルートディレクトリを home に配置する場合は ProtectHome を false に設定してください。
警告: データベースを 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 など、その他のメインの設定ファイルも保存されます。

ノート: デフォルトでは、通常ユーザーを使ってこのフォルダを見ることは不可能になっているため、findlocate で 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 のドキュメントも参照。

ノート: SSL で接続が保護されていない場合、平文パスワードや md5 ハッシュ (上記の例で使用しています) をインターネット経由で送信しないほうがセキュアです。PostgreSQL で SSL を使うように設定する方法は Secure TCP/IP Connections with SSL を参照。

設定をした後は、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

systemd#ユニットファイルの編集に書いてあるようにして設定で変数を上書きする必要があります。まず、以下のコマンドを実行:

# systemctl edit postgresql.service

Systemctl はドロップインの設定ファイルをエディタで開きます。以下のように書き込んでください:

[Service]
Environment=PGROOT=/pathto/pgroot/
PIDFile=/pathto/pgroot/data/postmaster.pid

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

ProtectHome=false

新しいデータベースのデフォルトエンコーディングを 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';
ノート: 最後のコマンドは 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/ || pgadmin4

PostgreSQL のアップグレード

PosgreSQL のメジャーバージョンのアップグレードにはメンテナンスが必要です。

ノート:
  • PostgreSQL の公式の アップグレードドキュメント に従ってください。
  • バージョン 10.0 から PostgreSQL のバージョンの命名規則は変更されています [1]。前は 9.x から 9.y への変更がメジャーアップグレードとされていましたが、現在はバージョン 10.x から 10.y へのアップグレードはマイナーアップグレードで、バージョン 10.x から 11.y のアップグレードがメジャーアップグレードになります。
警告: 以下のコマンドを実行するとデータが失われる可能性があります。アップグレードは自己責任で行ってください。

現在使用されているデータベースバージョンの取得

# cat /var/lib/postgres/data/PG_VERSION

データベースを誤って互換性のないバージョンにアップグレードしないようにするために、PostgreSQL パッケージの 更新をスキップすることをお勧めします。

マイナーバージョンのアップグレードは実行しても問題ありません。しかしながら、メジャーバージョンのアップグレードをした場合、データにアクセスできなくなる可能性があります。毎回 PostgreSQL のホームページ を確認してアップグレード時に必要な手順を確認してください。詳しくは バージョン管理ポリシー を参照。

ノート: 拡張機能を使用する場合は、#PostgreSQL database unable to start after package update when using extensions および #Failing to start a PostgreSQL server with the older version of the database while upgrading to the newer version with extensions をチェックしてください。

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 のアップグレードとインストール後に実行):

ノート:
  • 以下は PostgreSQL 9.6 でのコマンドです。PostgreSQL 9.2 の場合、/opt/ に同じようなコマンドがあるはずです。
  • pg_hba.conf ファイルをカスタマイズしている場合、ローカル環境から古いデータベースクラスタへの完全なアクセスを許可するように一時的に修正が必要です。アップグレードが完了したら新しいデータベースクラスタを同じようにカスタマイズして postgresql.service再起動してください。
# 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.soextension=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/
警告: while copying the .so files was enough for me, it might be required to copy more files to the correct directories under /opt/pgsql-XX/

to know the exact files to copy, check the contant of the package of the extension using :

$ pacman -Ql package_name
警告: This is a very dirty solution that may break or cause data loss in the database, so keep a backup