AWSでPostgreSQLサーバーを作る


最新情報：2013年11月よりAmazonRDSがPostgreSQLに対応しました。
http://aws.typepad.com/aws_japan/2013/11/amazon-rds-for-postgresql-now-available.html
以下の情報はそれでも自分で構築して、勉強したい、一円でも安くしたいなどという人のみに有用な情報になります。


AWS EC2を利用してPostgreSQLサーバーを立てる。RDS含め、MySQL情報はあるけどPostgreSQL情報はなかなか見つからなかったので作成。規模感としてはm2.xlarge,m3.xlargeあたりで使用する人を想定。

# EC2をPostgreSQLサーバーとして動かす

## EC2, EBSのセットアップ

AWSのClassic Wizardで作成。各項目について以下の様に設定。

```
サーバー：Ubuntuの64ビット (CentOSが好きならAmazonLinuxを選ぶ)
Availability Zone：好きなのを選ぶ (最悪AMIを通して移動できる)
EBS: データ用、WALログ用、LOG用をスタンダードボリュームで３つ作っておく 
    (サイズの拡大は頑張ればできるが、料金は10Gで1ヶ月1$ほどなので、すぐには満杯にならない程度の大きさで作る)
Tag：後から付けられるので無視。区別する名前が必要なら付ける感じ
KeyPair：必要なら新しく作る
SecurityGroup：新しくDB用のを作る（後から付け替えられない）
```

EBSは、確保しているディスク量と、IOの量で課金されるので、書き込み先が違う部分は分ければ分けるほどお得。なのでDBの書き込み先に応じて3つEBSを作っている。
データ用は現状のDBサイズの数倍、その他は5Gもあれば十分な印象。

作り終わったら、

- 間違ってTerminateしないように、Termination ProtectionをOnにしておくと良い。
- ElasticIPでIPを指定しておくと、インスタンスを立ち上げ直した時に同じIPでssh等することが出来て良い。無料。(http://aws.amazon.com/jp/ec2/#elastic-ip)
- CloudWatchも、とりあえずディスクがいっぱいになりそうな部分の指定だけしておくと良い。基本指標の5分毎のチェックなら無料。(http://aws.amazon.com/jp/cloudwatch/)


### 補足：EBSのスタンダードとIOPSプロビジョンドどっちを使えばいいか

http://aws.amazon.com/jp/ebs/

- スタンダードのIOPSは平均100
- ボリューム課金：Standardは10Gで1$、IOPSプロビジョンドは8Gで1$
- IO課金：Standardは月間1000万IOにつき1$、IOPSプロビジョンドは100IOPSで1月使って10$

同じ100IOPSの場合standardだと月間1億回IO(書き込み)が起きて初めてIOPSより高くなる。相当秒間アクセスが多くないサービスの場合スタンダードで十分。


## 作成したEC2にsshでログインしてみる

ユーザー名`ubuntu`でKeyPairのとこで指定したpemを使ってログイン

```
ssh -i ~/.ssh/hogehoge.pem ubuntu@ec2-1-234-56-78.compute-1.amazonaws.com
```

## EC2サーバーの環境設定

色々好みがあると思うので、好きにやればいいと思うが以下の項目はとりあえずやっておけばいいのではないだろうか。

### 表示言語の設定

とりあえず日本語化してみる。英語でもいいのだけどその場合でも、それぞれに対して`en_US.UTF-8`と直接指定しておいたほうが無難。

```/etc/environment
LANGUAGE="ja_JP:ja"
LC_ALL="ja_JP.UTF-8"
LC_CTYPE="ja_JP.UTF-8"
LANG="ja_JP.UTF-8"
```

環境によってはこの設定をする前に

```
sudo apt-get install --reinstall locales
sudo apt-get install language-pack-ja-base
```

が必要かもしれない。

### Timezoneの変更

```
$ date
2013年 2月 20日 水曜日 11:31:36 UTC
$ sudo mv /etc/localtime /etc/localtime.default
$ sudo ln -s /usr/share/zoneinfo/Asia/Tokyo /etc/localtime
$  date
2013年 2月 20日 水曜日 20:31:51 JST
```

### 個人用userを作る
```
sudo adduser hoge
sudo passwd hoge
sudo visudo  # hogeユーザーでsudo可能に
su - hoge
```

sshでいきなりログイン出来るようにするために、id_rsa.pubをauthorize_keyとして入れておくといい。詳しくはhttp://d.hatena.ne.jp/torazuka/20110420/ssh 参照


### apt-getで必要なものをインストール

まずはaptを最新に。

```
sudo apt-get upgrade
sudo apt-get update
```

後は好きなものを入れればいいと思うが、自分はとりあえずこれだけ入れるようにしている。

```
sudo apt-get install build-essential emacs git screen zsh
```

サーバーで動かすにはscreenかtmuxは必須だと思う。

## postgresqlのインストール

```
sudo apt-get install postgresql
sudo apt-get install libpq-dev  # rubyやpython等から使うときに必要になるだろうから入れておく
sudo apt-get install sysv-rc-conf  # chkconfigの代わり
sudo sysv-rc-conf postgresql on  # 起動時にpostgresqlサーバーが起動するようにする
```

## Postgresqlにつないでみる

postgresqlをinstallすると、postgresユーザーが勝手に作られる。このユーザーでログインするには以下のようにする

```
sudo su - postgres
```

postgresユーザーになったついでにtest用DBも作っておこう

```
createdb testdb
```

これで`psql`でDBを

```
psql testdb
```

後はcreate tableするなり、pg_shadowやpg_userを見るなり、ご自由に。

### おまけ：別ユーザーからpostgresとしてpsqlアクセス

`ph_hba.conf`を以下のように変更する

```diff:/etc/postgresql/9.1/main/pg_hba.conf
- local     all     postgres     peer
+ local     all     postgres     trust
```

http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
http://www.hizlab.net/app/pgsec.html

こうすることで、自分のアカウントから

```
psql -U postgres testdb
```

のようにすることが可能になる。
ちなみにここの内容はやらないほうが安全なので、localhostが安全＆めんどくさがり屋さんのみすること。ただ、`pg_hba.conf`で色々なアクセス法を設定できることを知っておくことは大事。

## Postgresqlに外部からアクセス出来るように設定する

```/etc/postgresql/9.1/main/pg_hba.conf
host all all 0.0.0.0/0 md5
```

```/etc/postgresql/9.1/main/postgresql.conf
listen_addresses = '*'
```

して、

```/etc/postgresql/9.1/main/postgresql.conf
sudo /etc/init.d/postgresql restart
```

さらにセキュリティーグループの設定でFireWallを設定する必要がある。
AWSのコンソールでポート番号5432(Postgres用のデフォルトポート番号)を`0.0.0.0/0`で設定追加する。

もちろん、よりセキュアにする場合は、以下のことをするべき。

- `0.0.0.0/0`を事前にわかっているアクセス元のIPアドレスに変更する。
- ポート番号をデフォルトの5432から変える。

### おまけ：HerokuのDBとして使ってみる

HerokuのDATABASE_URLは

> postgres://ユーザ名:パスワード@DBサーバURL/データベース名

として構成されている。なのでデータベースを作成して、この通りに設定してあげれば普通に使うことができる。

```
heroku config:set DATABASE_URL=postgres://user:pass@hogehoge-xxx.amazonaws.com/dbname
```

## Postgresqlのチューニング

自分が[m3.xlargeの時にやったチューニング情報](http://qiita.com/items/54503f309216c840765e)を載せておくので参照して下さい。
`/etc/sysctl.conf`の設定は必須だと思う。


# データの書き込み先を役割に応じて分割

ここまでで、既にAWSをPostgresサーバーとして使うことは出来ているはず。ここからさきは、DBの書き込み先を役割に応じて分割して、速度や、障害耐性をあげる。

## 最終構成

最終的には以下のように設定する。

| マウント先   | FileSystem  | 用途               |
|:-----------|:----------- |:-------------------|
| /          | /dev/xvda1  | EC2のデフォルト      |
| /ebs       | /dev/xvdb   | PostgresのData用    |
| /ebs2      | /dev/xvdc   | PostgresのWALログ用(pg_xlog) |
| /ebs3      | /dev/xvdd   | Postgresのsyslog用(pg_log)

また、それぞれのボリュームで`posgres`ユーザーで以下のようにディレクトリを作成しておく。ディレクトリ名に特に意味はないので好きに変更してOK。

```
sudo su - postgres
umask u=rwx,g=,o=
mkdir -p /ebs/postgresql/data
mkdir -p /ebs2/postgresql/wal/pg_xlog
mkdir -p /ebs3/postgresql/log/pg_log
```

## EBSのフォーマットとマウント

まずEBSをディスクとして使うためには、ちゃんとフォーマットしてマウントしてあげなければならない。

まずは、EBSのディスクが認識されているか確認。ちゃんとEBSと接続できていたら`/dev/xvdb`などが存在するはず。

```
sudo fdisk -l
```

パーティションを設定。エンターを押し続けて最後にwを押して設定を書き込めばいいはず。

```
sudo fdisk /dev/xvdb
```

ext4でファイルフォーマット。ext3やxfsも調べたが結局ext4が良さそう。EC2にもともと付いているEBSもext4でフォーマットされている。

```
$ sudo mkfs.ext4 /dev/xvdb
mke2fs 1.42.5 (29-Jul-2012)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
655360 inodes, 2621440 blocks
131072 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2684354560
80 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Allocating group tables: done                            
Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done 
```

マウントする。ここでは`/ebs`以下にマウントすることにする。

```
sudo mkdir /ebs
sudo chmod 777 /ebs
sudo mount /dev/xvdb /ebs
mount (マウント状況を確認)
```

ちなみにこれだけだと、再起動時にMountされなくなる。
起動時に自動的にMountしたい場合は、

```etc/fstab
LABEL=cloudimg-rootfs / ext4 defaults 0 0
/dev/xvda3 none swap sw,comment=cloudconfig 0 0
/dev/xvdb /ebs ext4 defaults 0 0
```

での`/dev/xvdb/`の欄を書いて、設定を反映。

```
sudo mount -a
```

他の2つのEBSボリュームについても同様にすること。

## Postgresのデータディレクトリを移す

DBを止める

```
sudo /etc/init.d/postgresql stop
```

元のDataの内容を`/ebs`以下の新しい場所にコピー

```
sudo su - postgres
cp -r /var/lib/postgresql/9.1/main /ebs/postgresql/data
```

`postgresql.conf`を以下のように変更

```diff:/etc/postgresql/9.1/main/postgresql.conf
- data_directory = '/var/lib/postgresql/9.1/main' # use data in another directory
+ data_directory = '/ebs/postgresql/data' # use data in another directory
```

DBを起動

```
sudo /etc/init.d/postgresql start
```

## WALログ(pg_xlog)、システムログ(pg_log)の書き込み先を変える

ログ系の書き込み先は、configで変えられたりしないので、シンボリックリンクで対応する。

```
sudo /etc/init.d/postgresql stop
```

```
mv /ebs/postgresql/data/pg_xlog /ebs2/postgresql/wal/
ln -s /ebs2/postgresql/wal/pg_xlog /ebs/postgresql/data/pg_xlog

mv /ebs/postgresql/data/pg_log /ebs3/postgresql/log/
ln -s /ebs3/postgresql/log/pg_log /ebs/postgresql/data/pg_log
```

```
sudo /etc/init.d/postgresql start
```

これだけ。

システムログは正直どうでもいいが、WALログの書き込み先を変更するのはやるべき、WALログ(+アーカイブログ)さえ残っていれば、Data用ディレクトリが壊れても、ログが残っている限りの最新状態や、指定したポイントや時間に復旧することが出来る。
http://www.postgresql.jp/document/9.1/html/recovery-config.html

# バックアップ環境を整える

DBが壊れた時の復元方法として以下の2つが考えられる。

1. PostgreSQLのリカバリ機能を利用して、復元ポイント、アーカイブログ、WALログから復元
2. DBのdumpから復元

前者は、手順が煩雑だが、壊れた直前に近い状態で再現することが可能。後者は、単純だが、バックアップを取った時間にしか復元出来ない(DB負荷の少ない深夜に1日に1回とるのが普通なので最大1日分のデータロスがある)といった違いがある。

以下、1.,2.を可能にする方法をそれぞれ書いておく。
バックアップデータの保存先に関してはAmazon S3を想定する。


## 1. 復元ポイント、アーカイブログをS3上に作成

Herokuの作ったWAL-Eを使うのが簡単。長くなるので、別エントリにまとめてある。
[Herokuが開発したWAL-Eを使って、AWSでPostgreSQLのバックアップを取る](http://qiita.com/items/0f36c051a1df8f0b6c6a)

ちなみに、アーカイブログというのはS3上にバックアップが取られたWALログのことで、pg_xlog上からは削除してOKなもののことをいう。

## 2. DBのdumpをS3に送る

コマンドラインからs3にアクセスできるように[s3cmd](http://s3tools.org/s3cmd)をインストールする。

```
sudo apt-get install s3cmd
```

アクセスキー、シークレットキーなどを設定。

```
s3cmd --configure
```

`.s3cfg`がホームディレクトリに作成されていればOK。

次に`s3cmd`を使ったバックアップ用スクリプトを作成。
dumps/ディレクトリ以下に、過去のdumpすべてを保存、dumb/db.dumpに最新のdumpを保存するようにしている。

```bash:usr/local/bin/s3backup.sh
DUMPTIME=`date +%Y%m%d_%H%M%S`
pg_dump -Fc --no-acl --no-owner your-db-name > /data/db.dump
s3cmd put /data/db.dump s3://your-s3-bucket/dumps/db_${DUMPTIME}.dump
s3cmd cp s3://your-s3-bucket/dumps/db_${DUMPTIME}.dump s3://your-s3-bucket/dump/db.dump
```

`postgres`ユーザーで`crontab -e`を打ってcron jobを設定。

```crontab
30 3 * * * /usr/local/bin/s3pgbackup.sh > /dev/null
```