PostgreSQL > 3.コマンドによるDBA

PostgreSQLデータベースの管理などに関する、俯瞰。
現在8.3を運用で使っているため、8.3ベースで記載しているものの、上位バージョンでも基本的には使用可能と思われる。

TOPへ編集

---

プログラム

バージョンの確認

以下のコマンドにて、インストールされているバージョンを確認する。

postgres --version

(出力例)

postgres (PostgreSQL) 8.3.11

TOPへ編集

---

データベース

データベースの確認

psqlコマンドを起動した後、メタコマンド\lにて確認を行う。
(実行例)

\l

(出力例)

        List of databases
   Name    |  Owner   | Encoding
-----------+----------+----------
 postgres  | postgres | UTF8
 template0 | postgres | UTF8
 template1 | postgres | UTF8
 testdb    | postgres | UTF8
(4 rows)

接続の確認および変更

psqlコマンドを起動した後、メタコマンド\cで接続の確認、および変更を行う。
(実行例：接続の確認)

\c

(出力例)

You are now connected to database "postgres".

(実行例：接続の変更)

\c testdb

(出力例)

You are now connected to database "testdb".

データベースの作成

以下のSQLを実行して、データベースを作成します。

CREATE DATABASE データベース名 WITH options...;

(出力例)

CREATE DATABASE

options...

OWNER	所有者ロール	通常はpostgres
TEMPLATE	テンプレート	通常はDEFAULT（template1）
ENCODING	文字コード	通常はDEFAULT（UTF-8）※多分OSの環境変数LANG
TABLESPACE	テーブルスペース	通常はDEFAULT
CONNECTION LIMIT	同時接続数	デフォルトは-1（無制限）

あるいは、シェルから以下のコマンドを実行します。

createdb データベース名

データベースの作成詳細は 参考:http://www.postgresql.jp/document/8.3/html/manage-ag-createdb.htmlを参照すること。

データベースの変更

以下のSQLを実行して、データベースを変更します。

ALTER DATABASE データベース名 WITH options...;

(出力例)

ALTER DATABASE

データベースの変更詳細は、参考:http://www.postgresql.jp/document/8.3/html/sql-alterdatabase.htmlを参照してください。

データベースの削除

以下のSQLを実行して、データベースを削除します。

DROP DATABASE データベース名;

(出力例)

DROP DATABASE

あるいは、シェルから以下のコマンドを実行します。

dropdb データベース名

データベースの削除詳細は、参考:http://www.postgresql.jp/document/8.3/html/manage-ag-dropdb.htmlを参照すること。

データベースのバックアップ（ダンプ）

単純には、以下のコマンドで、バックアップを取得する。

pg_dump データベース名 -f データベースダンプ名

単純なこの方法では、多少制限もあるため、
もう少し調べた内容については、別途PostgreSQL/A-1.バックアップとリストアを参照すること。

データベースのリストア

概要的には、一度データベースを再作成して、バックアップ（ダンプ）しておいた内容を復元する。

dropdb データベース名;
createdb データベース名;
psql testdb -f データベースバックアップ（ダンプ）ファイル名

もう少し調べた内容については、別途PostgreSQL/A-1.バックアップとリストアを参照すること。

この方法によるリストアの制限について、参考:http://www.postgresql.jp/document/8.3/html/backup-dump.htmlを参照すること。

主に、ロールやテーブル空間などはダンプされない（データベースクラスタ全体で保持している情報のため）。

TOPへ編集

---

ユーザ(ロール)

PostgreSQLではロールとも呼ばれる。
データベースオブジェクトの所有者になったりする。

データベース接続への認証にもユーザ（ロール）は使われるが、PostgreSQLではクライアント認証に、いくつかの段階を経て認証を行うため、ユーザ（ロール）を追加しただけでは、設定は完結しない。

詳細は参考:http://www.postgresql.jp/document/8.3/html/auth-pg-hba-conf.htmlを参照すること。

ユーザ(ロール)の確認

psqlコマンドを起動した後、メタコマンド\duにて確認を行う。
(実行例)

\du

                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
 postgres  | yes       | yes         | yes       | no limit    | {}
 test      | no        | no          | no        | no limit    | {}
(2 rows)

あるいは以下のSQL

SELECT rolname FROM pg_roles;

(実行例)

 rolname
----------
 postgres
 test
(2 rows)

ユーザ(ロール)の作成

以下のSQLにてユーザ（ロール）を作成します。

CREATE ROLE ユーザ名;

(例)パスワード認証が必要な、ログイン可能なユーザ（ロール）の作成

CREATE ROLE test WITH NOSUPERUSER LOGIN PASSWORD 'password';

作成操作の詳細は 参考:http://www.postgresql.jp/document/8.3/html/sql-createrole.html を参照すること。

ユーザ(ロール)の変更

以下のSQLにてユーザ（ロール）を変更します。

ALTER ROLE ユーザ名 WITH option...

(例)パスワードの変更

ALTER ROLE test WITH PASSWORD 'NewPassword';

変更操作の詳細は 参考:http://www.postgresql.jp/document/8.3/html/sql-alterrole.html を参照すること。

ユーザ(ロール)の削除

以下のSQLにてユーザ（ロール）を削除します。

DROP ROLE ユーザ名;

TOPへ編集

---

スキーマ

スキーマを使うことで、データベース内に同じテーブル名を使うことが可能になる。
厳密には、スキーマによって別のテーブルとして扱われる。

論理的な構造としては、
データベース > スキーマ > テーブル
というような感じ。

参考:http://www.postgresql.jp/document/8.3/html/ddl-schemas.html

スキーマの確認

psqlコマンドを起動した後、\dnメタコマンドにて確認を行う。
(実行例)

\dn
        List of schemas
        Name        |  Owner
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | postgres
 test1              | postgres
(6 rows)

スキーマの作成

以下のSQLで定義する。

CREATE SCHEMA スキーマ名;

スキーマの削除

以下のSQLで削除する。

DROP SCHEMA スキーマ名;

オブジェクトを含む場合、以下の構文で削除する。

DROP SCHEMA スキーマ名 CASCADE;

その他、スキーマの使われ方等

public スキーマ

スキーマ名を指定しないで作成したテーブルは、publicスキーマとして作成されています。

スキーマ検索パス

スキーマ名を記載しない場合に、検索する順序がある。
以下のコマンドで確認。

SHOW search_path;

(出力例)

postgres=# SHOW search_path;
  search_path
----------------
 "$user",public
(1 row)

スキーマ検索パスの設定

以下のコマンドで設定

SET search_path TO test1,public;

TOPへ編集

---

最終更新日 : [2011-04-12]