トップ > 技術メモ (システム管理) > Linuxサーバ管理 >
PostgreSQLの管理
(2007.2.11) 古い文書に加筆、修正のうえ公開。
(2007.9.19 更新。)
PostgreSQL Global Development Groupによって開発されているフリーなデータベースPostgreSQLの保守、運用、日常管理について。
インストールについては、PostgreSQLのインストール を参照してください。
また、データベースを利用したプログラミングについては、基礎から学ぶデータベースプログラミング を参照。
目次:
- 初期設定
- PostgreSQLのメジャーバージョンアップ
- データベースサーバの起動、停止と再起動
- データベースユーザ、パスワード
- データベースユーザの登録
- ユーザの一覧
- パスワードの設定
- パスワードの変更
- ユーザの削除
- データベースの作成と削除
- バックアップと復元
- ●・・・
@ 初期設定
PostgreSQLでは、データベースを操作するユーザと、OSのユーザとは別物になっている。
PostgreSQLのデータベースサーバ (postmasterプログラム) は複数のデータベースを束ねて管理できる。これを「データベースクラスタ」といい、一つのディレクトリにデータが保存される。
PostgreSQLパッケージを使う場合
LinuxのPostgreSQLパッケージをそのまま使う場合、データベースユーザはあらかじめ登録されている。
データベースクラスタの保存先は /var/lib/pgsql/data になっている。また、サーバはポート5432で接続を待ち受ける。
PostgreSQLのセキュリティの設定を変更すれば、すぐに使用できる。
ソースからインストールした場合
PostgreSQLがインストールできたら、まず、データベースユーザを登録する。最初のデータベースユーザは、伝統的にpostgresにする。
データベースクラスタが保存されるディレクトリを作成する。また、このディレクトリをpostgresユーザの所有にする。
# mkdir /var/lib/postgres # mkdir /var/lib/postgres/data # useradd postgres -d /var/lib/postgres # chown -R postgres /var/lib/postgres
initdbコマンドを実行して、データベースクラスタを初期化する。initdbコマンドは、環境変数PGDATAで指定されたディレクトリをデータベースクラスタとして初期化する。initdbコマンドは、内部で、initdbコマンドを実行したユーザ (postgres) をデータベーススーパーユーザとして登録する。
initdbコマンドを実行すると、すべてのデータベースやユーザが失われるので、気軽に実行するものではない。実行するのは、PostgreSQLの非互換なバージョンアップのときなどだけ。
データベースの文字コードは、日本語EUC（EUC-JP）にしておきます。localeサポートは害が大きいので、切っておきます。 -- (2007.2) 今ならUTF-8にすべき。
# su - postgres $ export PGDATA=/var/lib/postgres/data $ /usr/local/pgsql/bin/initdb --encoding=EUC-JP --no-locale
データベースクラスタの初期化に成功すると、次のようなメッセージが表示されます。
Success. You can now start the database server using:
/usr/local/pgsql/bin/postmaster -D /var/lib/postgres/data
or
/usr/local/pgsql/bin/pg_ctl -D /var/lib/postgres/data -l logfile start
@ PostgreSQLのメジャーバージョンアップ
(2008.5.23追記。)
Linuxディストリビューションを更新した場合、PostgreSQLのメジャーバージョンが上がることがあります。このときは、データベースのバックアップ、復元（リストア）をおこなわなければなりません。
Fedora Linux では、データベースのデータは、デフォルトで、/var/lib/pgsql ディレクトリ以下に保存されます。
バージョンアップ前に、後述するpg_dumpをしておきます。
バージョンアップ後、まず、initdb します。
# su postgres $ mv /var/lib/pgsql/data /var/lib/pgsql/data.old $ initdb /var/lib/pgsql/data
後述する pg_restore, psql などでデータを復元します。
@ データベースサーバの起動、停止と再起動
rpmパッケージでインストールしたときは、/etc/rc.d/init.d/postgresql コマンドが用意されている。引数として start / stop などを与えて実行すればいい。
設定の変更（クラスタディレクトリの変更など）は、/etc/sysconfig/pgsql ディレクトリに設定ファイルを置く。/etc/rc.d/init.d/postgresqlファイルから参照される。
サーバの設定は、/var/lib/pgsql/data/postgresql.conf ファイルを編集する。例えば、bindするIPアドレスを変更する場合は、listen_addresses を変更する。'*'など。
ソースからインストールした場合
データベースサーバ (postmasterコマンド) を起動します。postmasterコマンドには、-Dオプションか環境変数PGDATAでデータベースクラスタのディレクトリを与えます。postgres UNIXユーザのみがpostmasterコマンドを実行できます。
postmasterコマンドを直接起動してもいいのですが、起動・終了を安全に行うためのpg_ctlというコマンドがあります。こちらを使って起動します。
$ /usr/local/pgsql/bin/pg_ctl -o "-i" start
「PgAccess」というPostgreSQLをGUIで操作するプログラムを使うときは、-o "-i"オプションを付加します。
なお、OSの起動時にpostmasterを起動するには、/etc/rc.d/init.d/ ディレクトリにpg_ctlコマンドへのシンボリックリンクを作成し、必要に応じて /etc/rc.d/rc3.d/ などのディレクトリに先のシンボリックリンクへのシンボリックリンクを作成します。
データベースサーバの停止
データベースサーバを停止するには、pg_ctlコマンドの引数としてstopを渡します。
$ /usr/local/pgsql/bin/pg_ctl stop
@ データベースユーザ、パスワード
データベースユーザの登録
PostgreSQLでは、OSのユーザとデータベースユーザとは独立しているので、データベースを扱うには、PostgreSQLにデータベースユーザを登録しなければなりません。
コマンドラインから登録するには createuser コマンド、GUIだと pgAdmin III などを用います。
ユーザの作成は、postmasterが動いている状態でないとできません。また、postgresデータベースユーザ（またはユーザ生成権限のあるユーザ）で接続しなければいけません。
createuser コマンドは、追加するデータベースユーザ名、データベース作成許可、ユーザ追加許可を聞いてくるので、順次、答えていきます。
$ /usr/local/pgsql/bin/createuser -P Enter name of user to add: hori Enter password for user "hori": パスワード Enter it again: 上で入力したパスワード Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) n CREATE USER
データベースユーザ horiを追加し、このユーザが新しいデータベースを作成できるようにしました。
ユーザの一覧
データベースユーザの一覧は、psqlでコンソールに入って、
=> \du
List of database users
User name | User ID | Attributes
-----------+---------+----------------------------
hori | 100 | create database
olap | 101 | create database
postgres | 1 | superuser, create database
(3 rows)
パスワードの設定
初期状態では、postgresユーザにパスワードが設定されていません。いったん、サーバーを起動したら、後述の方法で、postgresユーザにパスワードを設定します。それから、これも後述の方法でサーバーを停止し、設定ファイルを修正して、パスワードを使うようにします。 TODO: pg_ctl reloadか、kill -HUPで、再読み込みされる。
PostgreSQLの認証は、$(PGDATA)/pg_hba.confファイルで管理されます。pg_hba.confファイルは、initdbコマンドがインストールします。 $(PGDATA)/pg_hba.confファイルに認証方法が書かれています。これを修正します。
 | Note.
Fedora Core 5 (PostgreSQL 8.1.4) では、/var/lib/pgsql/data ディレクトリにある。 |
Fedora Core 6のPostgreSQLでは、次のようになっている。
# TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: host all all 127.0.0.1/32 ident sameuser # IPv6 local connections: host all all ::1/128 ident sameuser「trust」は、パスワードチェックなしにサーバーへの接続を許可します。 これを、次のように修正します。trustをmd5に変更します。
local all all md5 host all all 127.0.0.1 255.255.255.255 md5この状態でサーバーを開始すると、パスワードが要求されるようになります。 しかし、これだけでは、サーバーへ接続したユーザーは、すべてのデータベースにアクセスできます。これでは不味いので、さらに次のようにします。 認証方式がidentになっていると、パスワード認証されない。パスワードは、password, md5, cryptのときのみ有効。 identのときは、UNIXユーザーがそのままPostgreSQLユーザーとして扱われる。したがって、次のような挙動になる。 $ createuser -U postgres -W postgres' password => これは失敗。-U, -Wは無視されている。 postgresユーザーになって、 $ createuser hori => これは成功。 デフォルトでは、所有者（およびスーパーユーザー）のみがそのオブジェクトに対して変更を加えることができる。 でも、他人がオーナーのデータベースに接続して、そこで表を作ったりはできる。ダメじゃん。 Chapter 6. クライアント認証 http://www.postgresql.jp/document/pg734doc/admin/client-authentication.html データベース単位でアクセス制限を設けるには、次のようにするのでは？ 未確認 local horidb hori md5 → 違う。他のユーザーでもこのDBに表を作れる。allの行がなければいいのかな。 allの行を消せばOK # psql -U olap hori psql: FATAL: no pg_hba.conf entry for host "[local]", user "olap", database "hori", SSL off
パスワードの変更
パスワードを変更するには、次のようにします。
# psql -U postgres template1 ... template1=# ALTER USER ユーザ名 WITH PASSWORD '新しいパスワード'; ALTER ROLE
ユーザの削除
データベースユーザを削除するには、dropuserコマンドでよい。
@ データベースの作成と削除
(2007.9.19 更新)
データベースの作成
データベースを作成するには、createdbコマンドを使います。当然、postmasterが動いていなければ動作しません。
$ createdb -h localhost -U postgres --encoding=UTF8 --owner=rails booksample CREATE DATABASE
-h (--host)、-U (--username) オプションでデータベースのホスト名、接続するユーザを指定し、作成するデータベースについて、所有者 (-O, --owner), 文字コード (-E, --encoding)、データベース名を指定します。
データベースの一覧
データベースを一覧するには、psqlコマンドに-lオプションを渡します。自分が所有者でないデータベースも一覧表示されます。
$ ./psql -l
List of databases
Name | Owner | Encoding
-------------+----------+-----------
booksample2 | hori | EUC_JP
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)
データベースの削除
データベースを削除するには、dropdbコマンドを使います。データベースユーザー名を明示的に指定するには、createdbコマンドと同様に、-Uオプションを渡します。$ ./dropdb booksample DROP DATABASE
データベースのバックアップと復元
(2007.7.20更新。)
バックアップ
PostgreSQLのデータベースをバックアップする場合、特定のデータベースのみバックアップする方法と，すべてのデータベースをバックアップする方法があります。
PostgreSQLは、バージョン番号の二つ目の数字が変わると，内部的なデータ保存形式が変更されます。したがって，例えばv7.2.xからv7.3へのアップデートでは，いったんデータベースをすべてバックアップし，新しいバージョンのPostgreSQLをインストールしてから復元する必要があります。
すべてのデータベースをバックアップするには，pg_dumpallコマンドを実行します。
$ pg_dumpall > 出力ファイル
ラージオブジェクト (BLOB, CLOB) もデフォルトで保存されます (Fedora 7 LinuxのPostgreSQLバージョン8.2.4)。以前はラージオブジェクトは保存されなかったように思うのですが、気のせいかもしれません。
一つのデータベースのみをバックアップするときは、pg_dumpコマンドを使います。次のようにします。
$ pg_dump mydb > mydb.sql
あるいはpg_restoreへの入力に適した形式（カスタム形式; 圧縮される）で出力します。
$ pg_dump -Fc mydb > mydb.dump
復元 (リストア)
バックアップデータのリストアの仕方は、バックアップデータのデータ形式によって異なります。
pg_dumpall (オプションなし) で生成したファイルは、
$ psql -f db.out postgres
pg_dump (オプションなし) で生成したファイルは、データベースを指定して、
$ psql -d newdb -f db.sql
カスタム形式で生成したファイルは、pg_restore で復元します。
$ pg_restore -d newdb db.dump
クライアント
PgAccess
PgAccessは、Tcl/Tkで書かれた、PostgreSQL用のGUIソフトウェアで、Microsoft Accessに似たインターフェイスを提供する。 -- (2007.2) 開発が停滞している？
インストール$ tar xzvf pgaccess-0.98.8.20030117.tar.gz $ cd pgaccess-0.98.8.20030117 $ su # make clean # make allOSにインストールされているTcl/Tkのバージョンによっては、日本語の入力ができません。 Tcl Developer Site http://www.tcl.tk/
Tcl/Tkバージョン8.4.xであれば問題なく日本語の入出力ができます。バージョン8.4.xはバージョン8.1以降と高い互換性があるので、そのまま上書きインストールしても大丈夫です。次の3つのパッケージをインストールします。 Tcl Tk tcllib
$ ./configure --prefix=/usr && make $ su # make install環境変数を設定。 PGACCESS_HOME: /usr/lib/pgaccess PGLIB: /usr/local/pgsql/lib
pgAdmin III
PostgreSQL用のメジャーなGUIツール。Windows, Linuxの両方でバイナリが提供されている。(wxWidgetsを利用。)
Fedora Core 6では、パッケージが用意されている。pgadmin3-1.4.3
Rekall
使ったことはないが、KDE用のMicrosoft AccessライクなGUIツール、のようだ。サイトはここかな？
Fedora Core 6にはパッケージが用意されている。
トップ > 技術メモ (システム管理) > Linuxサーバ管理 > PostgreSQLの管理
Netsphere Laboratories http://www.nslabs.jp/
[PR]
[PR]