====== PostgreSQL ======

===== インストール =====
Fedoraでは、以下のようにパッケージをインストールする。(Fedora 10, 18, 29 にて確認)
<code>
$ sudo dnf install postgresql postgresql-server postgresql-contrib pgadmin3
</code>

===== クラスタの初期化 =====
==== Fedora の場合 ====
<code>
$ sudo postgresql-setup --initdb --unit=postgresql
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
</code>
※ --unit=postgresql はデフォルト値で省略できる。\\
==== CentOS 6 の場合 ====
サービスを起動する前にデータベースクラスタ(インスタンスで管理されるデータベース集合)を初期化する必要があります。
<code>
$ 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
</code>

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

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

==== CentOS 7 の場合 ====
<code>
$ sudo postgresql-setup initdb
Initializing database ... OK
</code>

===== サービスの自動起動設定 =====

==== Fedora の場合 ====
<code>
$ 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
</code>
※自動起動は Loaded: 行の最後の enabled を確認。

==== CentOS の場合 ====
<code>
$ sudo chkconfig postgresql on
$ sudo chkconfig --list postgresql
postgresql      0:off   1:off   2:on    3:on    4:on    5:on    6:off
</code>

===== サービスの起動 =====

==== Fedora の場合 ====
<code>
$ sudo systemctl start postgresql.service
</code>

==== CentOS の場合 ====
<code>
$ sudo service postgresql start
</code>

===== 認証方法の設定 =====

==== DB に格納された暗号化パスワードで認証 ====
postgres のパスワードを設定する。\\
<code>
$ sudo passwd postgres
ユーザー postgres のパスワードを変更。
新しいパスワード:
新しいパスワードを再入力してください:
passwd: すべての認証トークンが正しく更新できました。
</code>

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

postgres=# ALTER USER postgres ENCRYPTED PASSWORD 'password';
ALTER ROLE
postgres=# \q
$ exit
</code>
/var/lib/pgsql/data/pg_hba.confを修正する。
<code>
$ sudo vi /var/lib/pgsql/data/pg_hba.conf
</code>
<code autoconf>
# "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
</code>

==== ローカル接続のみパスワードなしでログイン ====
/var/lib/pgsql/data/pg_hba.confを修正する。
<code>
$ sudo vi /var/lib/pgsql/data/pg_hba.conf
</code>
<code autoconf>
# "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
</code>

サービスを再起動する。
=== Fedora の場合 ===
<code>
$ sudo systemctl restart postgresql.service
</code>
=== CentOS の場合 ===
<code>
$ sudo service postgresql restart
postgresql サービスを停止中:                               [  OK  ]
postgresql サービスを開始中:                               [  OK  ]
</code>
===== データベース操作 =====

==== データベース作成 ====
<code>
$ createdb -E EUC_JP [-U username] databasename   # EUC_JP encoding
$ createdb -E UNICODE [-U username] databasename  # UTF-8 encoding
</code>
※-U を指定しない場合はログイン中のユーザーになる。\\
※ログイン中のユーザーに権限がない場合は -U に権限のあるユーザー名を指定する必要がある。
例:
<code>
$ createdb -E UNICODE -U postgres hogehoge_db
</code>

==== データベース削除 ====
<code>
$ dropdb [-U username] databasename
</code>

==== SQL実行 ====
<code>
$ 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
</code>

===== ユーザー操作 =====

==== ユーザー作成 ====
<code>
$ createuser -a -d -U postgres -P username
新しいロールのためのパスワード:
もう一度入力してください:
</code>

==== ユーザー削除 ====
<code>
$ dropuser -U postgres username
</code>

==== パスワード変更 ====
<code>
$ psql -U postgres
psql (8.4.11)
"help" でヘルプを表示します.

postgres=#ALTER USER postgres ENCRYPTED PASSWORD 'new password';
postgres=#\q
</code>

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

バックアップ(プレーンテキストの場合)
<code>
$ pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v > dumpfile.sql
</code>

バックアップ(カスタムアーカイブ形式の場合)
<code>
$ pg_dump databasename -h xxx.xxx.xxx.xxx -U username -v -b -Fc > dumpfile.dmp
</code>

リストア(プレーンテキストの場合)
<code>
$ psql -d databasename -U username -f dumpfile.sql
</code>

リストア(カスタムアーカイブ形式の場合)
<code>
$ pg_restore -d databasename -U username dumpfile.dmp
</code>

===== データベース再構築 =====
<code>
# 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を復元する
</code>
<del>
# su postgres\\
# createlang plpgsql databasename\\
# exit\\
</del>

===== データベース修復/最適化 =====
VACUUMによって破損したデータベースを修復できる。
<code>
VACUUM FULL FREEZE VERBOSE ANALYZE
</code>
<code>
# /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    <- サービス開始
</code>

===== パッケージ更新後のデータベースアップグレード =====
<WRAP color_term>
<WRAP color_command><html><pre>
<span style="color:#FF8700"><b>$</b></span> <span style="color:#26A269"><u style="text-decoration-style:single">sudo</u></span> <span style="color:#26A269">postgresql-setup</span> <span style="color:#A347BA">--upgrade</span>
</pre></html></WRAP>
<WRAP color_result><html><pre>
 * Upgrading database.
 * Upgraded OK.
WARNING: The configuration files were replaced by default configuration.
WARNING: The previous configuration and data are stored in folder
WARNING: /var/lib/pgsql/data-old.
WARNING: If you&apos;ve just upgraded your database from a previous major version of
Fedora or RHEL, please run reindexdb against your databases.  Core library
collation data may have changed and this will invalidate database indexes.  For
example, in Fedora 28 and RHEL 8 there have been extensive changes in glibc
collations to support ISO 14651:2016 (Unicode 9.0.0 data) and your indexes may
be affected: https://sourceware.org/ml/libc-announce/2018/msg00002.html
 * See /var/lib/pgsql/upgrade_postgresql.log for details.
</pre></html></WRAP>
</WRAP>

===== RedHatにおけるpostgresql.confの場所 =====
<code>
/var/lib/pgsql/data/postgresql.conf
</code>

===== コンフィギュレーション チューニング =====
postgresql.confの内容を変更してサービス再起動。

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

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

===== データベース解析 チューニング =====
データベースの不要領域の回収。データベースの解析オプションを持つ。
<code>
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
</code>

FULL\\
　領域の回収だけでなく、より時間のかかる、テーブルの排他的ロックを行なう、 "完全な"vacuum を選択します。\\
\\
FREEZE\\
　積極的にタプルの"凍結"を選択します。\\
\\
VERBOSE\\
　各テーブルについてvacuumの詳細な活動報告を出力します。\\
\\
ANALYZE\\
　オプティマイザが使用する統計情報を更新し、問い合わせを実行する最も効率的な方法を決定できるようにします。\\
\\
table\\
　掃除の対象とするテーブルの名前です。デフォルトは全テーブルです。\\
\\
column\\
　解析の対象とする列名です。デフォルトは全列です。\\
\\

破損したインデックスの再構築。
<code>
REINDEX { TABLE | DATABASE | INDEX } name [ FORCE ]
</code>

TABLE\\
　指定したテーブルの全インデックスを再作成します。\\
\\
DATABASE\\
　指定したデータベースの全てのシステムインデックスを再作成します。(ユーザテーブルのインデックスは含まれません。)\\
\\
INDEX\\
　指定したインデックスを再作成します。\\
\\
name\\
　インデックスの再作成を行なうテーブル/データベース/インデックスの名前を指定します。\\
\\
FORCE\\
　強制的にシステムインデックスの再構築を行ないます。このキーワードがなければ、**REINDEX** は無効とされていないシステムインデックスを処理しません。FORCE を **REINDEX INDEX** や ユーザインデックスに対する再作成に付与することは意味がありません。\\
\\

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

===== 参考文献 =====
[[http://www.fiberbit.net/user/kunyami/postgresql/|PostgreSQL リファレンス]]\\
[[http://www.postgresql.jp/document/pg721doc/reference/index.html|PostgreSQL 7.2.3 リファレンスマニュアル]]\\
[[http://blog.asial.co.jp/418|PostgreSQLの手軽なSQLチューニング]]\\
[[http://d.hatena.ne.jp/factory-g/20111212/1323673911|PostgreSQL9.1　インスコできない　やっぱお前か　編 - 日常茶飯事]]\\
[[http://www.postgresql.jp/document/8.4/html/reference-client.html|PostgreSQLクライアントアプリケーション]]\\