PostgreSQL

Fedoraでは、以下のようにパッケージをインストールする。(Fedora 10, 18, 29 にて確認)

$ sudo dnf install postgresql postgresql-server postgresql-contrib pgadmin3

$ sudo postgresql-setup --initdb --unit=postgresql
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

※ –unit=postgresql はデフォルト値で省略できる。

サービスを起動する前にデータベースクラスタ(インスタンスで管理されるデータベース集合)を初期化する必要があります。

$ sudo su - postgres
パスワード:
$ initdb
データベースシステム内のファイルの所有者は"postgres"ユーザでした。
このユーザがサーバプロセスを所有しなければなりません。

データベースクラスタはロケール"ja_JP.utf8"で初期化されます。
したがってデフォルトのデータベース符号化方式はUTF8に設定されました。
initdb: ロケール"ja_JP.utf8"用の適切なテキスト検索設定が見つかりません
デフォルトのテキスト検索設定はsimpleに設定されました。

ディレクトリ/var/lib/pgsql/dataを作成しています ... ok
サブディレクトリを作成しています ... ok
デフォルトのmax_connectionsを選択しています ... 100
デフォルトの shared_buffers を選択しています ... 32MB
設定ファイルを作成しています ... ok
/var/lib/pgsql/data/base/1にtemplate1データベースを作成しています ... ok
pg_authidを初期化しています ... ok
依存関係を初期化しています ... ok
システムビューを作成しています ... ok
システムオブジェクトの定義をロードしています ... ok
照合順序を作成しています ... ok
変換を作成しています ... ok
ディレクトリを作成しています ... ok
組み込みオブジェクトに権限を設定しています ... ok
情報スキーマを作成しています ... ok
PL/pgSQL サーバサイド言語をロードしています ... ok
template1データベースをバキュームしています ... ok
template1からtemplate0へコピーしています ... ok
template1からpostgresへコピーしています ... ok

警告: ローカル接続向けに"trust"認証が有効です。
pg_hba.confを編集する、もしくは、次回initdbを実行する時に-Aオプショ
ン、または、--auth-localおよび--auth-hostを使用することで変更するこ
とができます。

成功しました。以下を使用してデータベースサーバを起動することができます。

    postmaster -D /var/lib/pgsql/data
または
    pg_ctl -D /var/lib/pgsql/data -l logfile start

初期化せずにサービスを開始しようとすると、「“service postgresql initdb”を使用して、最初にクラスタを初期化してください。」と言われる。

$ sudo service postgresql start

/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
                                                           [失敗]

$ sudo postgresql-setup initdb
Initializing database ... OK

$ sudo systemctl enable postgresql.service
ln -s '/usr/lib/systemd/system/postgresql.service' '/etc/systemd/system/multi-user.target.wants/postgresql.service'
$ systemctl status postgresql.service
postgresql.service - PostgreSQL database server
	  Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
	  Active: inactive (dead) since 火 2013-03-26 02:42:49 JST; 19s ago
	 Process: 18564 ExecStop=/usr/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
	Main PID: 17775 (code=exited, status=0/SUCCESS)
	  CGroup: name=systemd:/system/postgresql.service

※自動起動は Loaded: 行の最後の enabled を確認。

$ sudo chkconfig postgresql on
$ sudo chkconfig --list postgresql
postgresql      0:off   1:off   2:on    3:on    4:on    5:on    6:off

$ sudo systemctl start postgresql.service

$ sudo service postgresql start

postgres のパスワードを設定する。

$ sudo passwd postgres
ユーザー postgres のパスワードを変更。
新しいパスワード:
新しいパスワードを再入力してください:
passwd: すべての認証トークンが正しく更新できました。

$ su - postgres
パスワード:
$ psql -U postgres
psql (10.6)
"help" でヘルプを表示します。

postgres=# ALTER USER postgres ENCRYPTED PASSWORD 'password';
ALTER ROLE
postgres=# \q
$ exit

/var/lib/pgsql/data/pg_hba.confを修正する。

$ sudo vi /var/lib/pgsql/data/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

/var/lib/pgsql/data/pg_hba.confを修正する。

$ sudo vi /var/lib/pgsql/data/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

サービスを再起動する。

$ sudo systemctl restart postgresql.service

$ sudo service postgresql restart
postgresql サービスを停止中:                               [  OK  ]
postgresql サービスを開始中:                               [  OK  ]

$ createdb -E EUC_JP [-U username] databasename   # EUC_JP encoding
$ createdb -E UNICODE [-U username] databasename  # UTF-8 encoding

※-U を指定しない場合はログイン中のユーザーになる。
※ログイン中のユーザーに権限がない場合は -U に権限のあるユーザー名を指定する必要がある。 例:

$ createdb -E UNICODE -U postgres hogehoge_db

$ dropdb [-U username] databasename

$ psql -d database_name -U postgres
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

database_name=# select * from pg_tables;
...
database_name=#\q

$ createuser -a -d -U postgres -P username
新しいロールのためのパスワード:
もう一度入力してください:

$ dropuser -U postgres username

$ psql -U postgres
psql (8.4.11)
"help" でヘルプを表示します.

postgres=#ALTER USER postgres ENCRYPTED PASSWORD 'new password';
postgres=#\q

$ pg_dump database options > dumpfile.sql

options
-a データのみをダンプ
-b ラージオブジェクトもダンプする。(注: デフォルトではダンプされません)
-d INSERTコマンドにてダンプ(通常はCOPYコマンド)
-D カラム名を含めたINSERTコマンドにてダンプ
-s テーブルのみダンプ
-t 特定のテーブルのみダンプ
-F アウトプットファイルの形式を指定(cがカスタム、tがtar、pがテキスト)
-v 冗長モードを指定(verbose - ダンプファイルに詳細なオブジェクトコメント、開始時刻、終了時刻、進行状況を出力、標準エラーにメッセージ出力)

バックアップ(プレーンテキストの場合)

$ pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v > dumpfile.sql

バックアップ(カスタムアーカイブ形式の場合)

$ pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v -b -Fc > dumpfile.dmp

リストア(プレーンテキストの場合)

$ psql -d databasename -U username -f dumpfile.sql

リストア(カスタムアーカイブ形式の場合)

$ pg_restore -d databasename -U username dumpfile.dmp

# pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v -b -Fc > dumpfile.dmp  <- まずはフルバックアップ
# dropdb -U username databasename                        <- DBをきれいに消す
# createdb -E EUC_JP -U username databasename            <- DBを新規作成
# pg_restore -d databasename -U username dumpfile.dmp    <- DBを復元する

# su postgres
# createlang plpgsql databasename
# exit

VACUUMによって破損したデータベースを修復できる。

VACUUM FULL FREEZE VERBOSE ANALYZE

# /etc/rc.d/init.d/postgresql stop    <- サービス停止
# su postgres                         <- postgresユーザに移行
$ postgres -O -P -D /var/lib/pgsql/data databasename <- PostgreSQLをスタンドアロンで起動
 LOG:  database system was shut down at 2008-11-30 01:03:16 JST
 LOG:  checkpoint record is at 0/529CDFD8
 LOG:  redo record is at 0/529CDFD8; undo record is at 0/0; shutdown TRUE
 LOG:  next transaction id: 2879007; next oid: 4195414
 LOG:  database system is ready
  
 POSTGRES backend interactive interface
 $Revision: 1.307.2.2 $ $Date: 2005/06/02 21:04:30 $
 
backend> REINDEX DATABASE databasename FORCE; <- データベースのインデックス再構築
Ctrl+D                                        <- backend終了
# /etc/rc.d/init.d/postgresql start    <- サービス開始

/var/lib/pgsql/data/postgresql.conf

postgresql.confの内容を変更してサービス再起動。

データベースサーバで使用される共有メモリバッファのページ数を設定する。

shared_buffers = 3072

セッションがソート処理およびハッシュテーブルに使用するメモリの最大量をKByte単位で設定する。

sort_mem = 4096

データベースの不要領域の回収。データベースの解析オプションを持つ。

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

FULL
　領域の回収だけでなく、より時間のかかる、テーブルの排他的ロックを行なう、 “完全な”vacuum を選択します。

FREEZE
　積極的にタプルの“凍結”を選択します。

VERBOSE
　各テーブルについてvacuumの詳細な活動報告を出力します。

ANALYZE
　オプティマイザが使用する統計情報を更新し、問い合わせを実行する最も効率的な方法を決定できるようにします。

table
　掃除の対象とするテーブルの名前です。デフォルトは全テーブルです。

column
　解析の対象とする列名です。デフォルトは全列です。

破損したインデックスの再構築。

REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]

TABLE
　指定したテーブルの全インデックスを再作成します。

DATABASE
　指定したデータベースの全てのシステムインデックスを再作成します。(ユーザテーブルのインデックスは含まれません。)

INDEX
　指定したインデックスを再作成します。

name
　インデックスの再作成を行なうテーブル/データベース/インデックスの名前を指定します。

FORCE
　強制的にシステムインデックスの再構築を行ないます。このキーワードがなければ、REINDEX は無効とされていないシステムインデックスを処理しません。FORCE を REINDEX INDEX や ユーザインデックスに対する再作成に付与することは意味がありません。

実行例

VACUUM FULL
>クエリーは、147104 ミリ秒で結果なしでうまく帰りました。
REINDEX TABLE tablename
>クエリーは、22064 ミリ秒で結果なしでうまく帰りました。

PostgreSQL リファレンス
PostgreSQL 7.2.3 リファレンスマニュアル
PostgreSQLの手軽なSQLチューニング
PostgreSQL9.1　インスコできない　やっぱお前か　編 - 日常茶飯事
PostgreSQLクライアントアプリケーション