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プロビジョンドどっちを使えばいいか

• スタンダードの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 [e-mail]

EC2サーバーの環境設定

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

表示言語の設定

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

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を以下のように変更する

/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:[e-mail]/dbname

Postgresqlのチューニング

自分がm3.xlargeの時にやったチューニング情報を載せておくので参照して下さい。
/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したい場合は、

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を以下のように変更

/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のバックアップを取る

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

2. DBのdumpをS3に送る

コマンドラインからs3にアクセスできるようにs3cmdをインストールする。

sudo apt-get install s3cmd

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

s3cmd --configure

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

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

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を設定。

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