Postgresで年代毎の人数を集計する方法

Postgresで今日現在の年齢で年代別に集計する方法です。
Userテーブルにbirthdayというtimestamp型のカラムがあると想定します。

SELECT
    CASE
        when date_part('year', age(birthday)) between 1 and 9  then 0
        when date_part('year', age(birthday)) between 10 and 19 then 10
        when date_part('year', age(birthday)) between 20 and 29 then 20
        when date_part('year', age(birthday)) between 30 and 39 then 30
        when date_part('year', age(birthday)) between 40 and 49 then 40
        when date_part('year', age(birthday)) between 50 and 59 then 50
        when date_part('year', age(birthday)) between 60 and 69 then 60
        when date_part('year', age(birthday)) between 70 and 79 then 70
        when date_part('year', age(birthday)) between 80 and 89 then 80
        when date_part('year', age(birthday)) between 90 and 99 then 90
        when date_part('year', age(birthday)) >= 100 then 100
    end AS age_group,

    count(
        case 
        when date_part('year', age(birthday)) between 1 and 9  then 0
        when date_part('year', age(birthday)) between 10 and 19 then 10
        when date_part('year', age(birthday)) between 20 and 29 then 20
        when date_part('year', age(birthday)) between 30 and 39 then 30
        when date_part('year', age(birthday)) between 40 and 49 then 40
        when date_part('year', age(birthday)) between 50 and 59 then 50
        when date_part('year', age(birthday)) between 60 and 69 then 60
        when date_part('year', age(birthday)) between 70 and 79 then 70
        when date_part('year', age(birthday)) between 80 and 89 then 80
        when date_part('year', age(birthday)) between 90 and 99 then 90
        when date_part('year', age(birthday)) >= 100 then 100
        end
    ) AS age_count
FROM user   
GROUP BY age_group
ORDER BY age_group

結果は、以下のようになります。値は適当です。

age_group	age_count
0	335
10	515
20	27632
30	65276
40	12572
50	819
60	161
70	31
80	3
90	2
100	1

年代の算出に以下を使っています。

age(timestamp)

これは、現在から引数のタイムスタンプを減算します。
それを年に変換してグループ化しています。
例えば、2014-01-01時点の場合は、age(timestamp, timestamp)を使えば、算出可能です。

age('2014-01-01', birthday)

AWS EC2のPostgresを9.2から9.3にバージョンアップする方法

EC2とRDSの環境でRailsのdb:migrateしたら、おこられました。

pg_dump:
server version: 9.3.3; pg_dump version: 9.2.9
pg_dump: aborting because of server version mismatch
rake aborted!

ほったらかしにしておいた環境なので、気がついたら、RDSだけPostgresのバージョンが上がっていました。
そのため、EC2側のPostgresを9.3にバージョンアップすることにしました。

インストールされているPostgresの確認

    rpm -qa | grep postgres

をすると、9.2系が入ってることが確認できます。

postgresql92-libs-9.2.9-1.46.amzn1.x86_64
postgresql92-9.2.9-1.46.amzn1.x86_64
postgresql92-devel-9.2.9-1.46.amzn1.x86_64

Postgres9.2のアンインストール

9.2を削除します。
削除方法は下記。

rpm -e postgresql92-libs-9.2.9-1.46.amzn1.x86_64 postgresql92-9.2.9-1.46.amzn1.x86_64 postgresql92-devel-9.2.9-1.46.amzn1.x86_64

Postgres9.3のインストール

yum list すると、Postgresの9.3系は、以下が入っていることが確認できます。

postgresql93.x86_64            
postgresql93-contrib.x86_64    
postgresql93-devel.x86_64      
postgresql93-docs.x86_64       
postgresql93-libs.i686         
postgresql93-libs.x86_64       
postgresql93-plperl.x86_64     
postgresql93-plpython.x86_64   
postgresql93-pltcl.x86_64      
postgresql93-server.x86_64
postgresql93-test.x86_64

この中から、必要なものをインストールしていきます。
それぞれの、パッケージは以下の通りです。

パッケージ名	主に格納しているもの
postgresql-libs	libpqなどのPostgreSQLのライブラリ群。PostgreSQL以外のパッケージが必要とすることもあります。
postgresql	psqlやpg_dumpなどのPostgreSQLのクライアントユーティリティ
postgresql-server	initdbやpg_ctlなどのPostgreSQLのサーバユーティリティ。DBMSサーバとして必要な機能はこれに含まれています。
postgresql-contrib	pgbenchやpg_statstatementなどのPostgreSQLのcontribモジュール。
postgresql-devel	PostgreSQLのヘッダやpg_configなどの開発用モジュール。
postgresql-doc	PostgreSQLのドキュメント。
postgresql-test	PostgreSQLのリグレッションテスト用モジュール。

インストール

今回、以下のリポジトリをインストールしました。

yum install postgresql93.x86_64
yum install postgresql93-devel.x86_64
yum install postgresql93-server.x86_64

インストールの確認

rpm -qa | grep postgres

postgresql93-9.3.5-1.52.amzn1.x86_64
postgresql93-devel-9.3.5-1.52.amzn1.x86_64
postgresql93-libs-9.3.5-1.52.amzn1.x86_64

参考サイト

http://lets.postgresql.jp/documents/tutorial/yum/yum
http://d.hatena.ne.jp/kasahi/20070819/1187545636

Postgresで読み取り専用のロール(ユーザー)の作成方法

Postgresで読み取り専用のロール（ユーザー）を作成します。
全のテーブルに対して、読み取り権限のみを設定します。

ロールの作成

CREATE ROLE read_only_user LOGIN REPLICATION PASSWORD 'password';

＊ ‘password’は任意に設定してください。

ロールの一覧確認

psqlを使っていれば

¥du

とすれば、下記のように出力されます。

Role name	Attributes	Member of
postgres	Superuser, Create role, Create DB, Replication	{}

権限の付与

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

上記は、read_only_userに schemaがpublicの全テーブルに Select権限を付与します。
というような感じです。

権限の付与の確認

psqlを使っていれば

¥z

とすれば、下記の様に出力されます。
read_only_user=r/postgres となっていることを確認。

Schema	Name	Type	Access privileges	Column access privileges
public	user	table	postgres=arwdDxt/postgres+
			read_only_user=r/postgres
public	book	table	postgres=arwdDxt/postgres+
			read_only_user=r/postgres

権限の一覧は以下

r -- SELECT（読み取り（read））
w -- UPDATE（書き込み（write））
a -- INSERT （追加（append））
d -- DELETE
R -- RULE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
T -- TEMPORARY

その他

schemaの確認

psqlを使っていれば

¥d

とすれば、下記のように出力されます。

Schema	Name	Type	Owner
public	user	table	postgres
public	book	table	postgres

multi_dbを使ってレプリケーションのDBに接続を振り分ける方法

最初、レプリケーションされた、DB環境でのマスターDBトスレーブDBの接続の振り分けにOctopusを使おうか考えて色々と調べてみましたが、
スレーブでトラブルが起きた時にも、スレーブDBを参照しにいこうとしてしまうようなので、
それは、いかがなものかと思い、こちらの、Git Hub - multi_dbに切り替えました。
このmulti_dbは、スレーブが停止しても、マスターを見るように切り替わってくれるので、その点安心です。
それでは、設定方法を説明していきます。

database.ymlにスレーブの設定追加

スレーブ用のDBの接続情報をdatabase.ymlに追加します。
追加する、スレーブのdatabase.ymlでの名前の命名ルールは以下のようにします。

[environment]_slave_database[_optional_name]

例えば、production環境の1番スレーブDBの場合は、以下のようにします。

production_slave_database_1

[environment]は、下記のいずれか

• development
• staging
• production

[_optional_name] は、数字がわかりやすいとおもいます。また、スレーブ一台構成の時などは、省略も可能です。

• _1
• _2

実際の、database.ymlは以下のようになります。

development:
  adapter: postgresql
  encoding: utf8
  database: development_master
  pool: 5
  username: postgres
  password: <%= ENV['DB_PASSWORD'] %>
  port: 5432
  host: <%= ENV['DB_HOST'] %>
  timeout: 5000

production_slave_database: # that would be a slave
  adapter: postgresql
  encoding: utf8
  database: development_slave
  pool: 5
  username: postgres
  password: <%= ENV['DB_PASSWORD'] %>
  port: 5432
  host: <%= ENV['DB_HOST'] %>
  timeout: 5000

初期化処理の追加

passenger未使用の場合

config/environments/[environment].rbのconfig.after_initializeブロックの中にMultiDb::ConnectionProxy.setup! を追加。

config.after_initialize do
  MultiDb::ConnectionProxy.setup!
end

[environment]は、下記のいずれか

• development
• staging
• production

passenger使用の場合

config/initializers/connection_proxy.rbに以下を追加

if defined?(PhusionPassenger)
  PhusionPassenger.on_event(:starting_worker_process) do |forked|
    if forked
      # ... set MultiDb configuration options, if any ...
      MultiDb::ConnectionProxy.setup!
    end
  end
else # not using passenger (e.g. development/testing)
  # ... set MultiDb configuration options, if any ...
  MultiDb::ConnectionProxy.setup!
end

上記を追加した場合、config/environments/[environment].rbの設定は不要になります。

マスターのみを参照するモデルの指定

UserモデルとBookモデルはマスターしか参照しないという設定にする場合、下記のようにmaster_modelsにモデル名の配列を指定する。

MultiDb::ConnectionProxy.master_models = ['User', 'Book']
MultiDb::ConnectionProxy.setup!

動作確認

これまでの設定で、rails c で

 Blog.find(1)

とすると、下記のような結果が帰ってきます。

[MULTIDB] hijacking connection for Blog
Blog Load (6.6ms)  SELECT "blogs".* FROM "blogs" WHERE "blogs"."id" = $1 LIMIT 1  [["id", 1]]

スレーブの重み付け

複数スレーブ環境の場合、各スレーブに対して重み付けもできるようです。

production_slave_database_1:
  <<: *postgres
  host: my.slavedb_1
  weight: 1

production_slave_database_2:
  <<: *postgres
  host: my.slavedb_2
  weight: 10

production_slave_database_3:
  <<: *postgres
  host: my.slavedb_3
  weight: 5

上記のようにそれぞれに、weightを設定した場合、
リクエスト数が以下のようになる様です。

production_slave_database_1　6302クエリ
production_slave_database_2　62764クエリ
production_slave_database_3　30934クエリ

※上記実際に試していないので、公式ドキュメンとそのままです。
AWSでインスタンスのスペックの違いがあるときなんかは、こちらの設定が有効活用できるような気がします。

その他

テスト環境

テスト環境は、Octopasの時も、書きましたが、読み取り専用のユーザーを作成して簡易的にテストしても良いかと思います。
詳しくは、Octopusを使ってレプリケーションのDBに接続を振り分ける方法を参照してください。
本来であれば、ちゃんとレプリケーション環境で試すのが望ましいとおもいますが、、、、

Octopusを使ってレプリケーションのDBに接続を振り分ける方法

DBがレプリケーションの構成になっている場合、更新系のクエリーをマスターDBへ、参照系のクエリーをスレーブDBへクエリを投げるようして、DBの負荷分散をさせる方法です。
RailsのGemでそれを、やってくれるGem octopus (Git Hub - tchandy/octopus) があるので、これを使った実装は説明します。

インストール

gemのインストールは、Gemfileに以下を追加して、bundle install
Gemfile

# DB master slave振り分け
# https://github.com/tchandy/octopus
gem 'ar-octopus'

DBの接続先の設定

接続先の設定は、config/shards.ymlに行って行きます。
公式のConfigファイルの設定に関するWikiはこちら。

全クエリの振り分け

更新のクエリは、マスターDB、参照のクエリはスレーブDBと全てのクエリに対して設定を行う場合は、下記。

octopus:
  replicated: true
  environments:
    - development
    - staging
    - production
  development:
   development_slave:
      database: development_slave_db
      adapter: postgresql
      encoding: utf8
      pool: 5
      username: postgres
      password: password
      port: 5432
      host: localhost
      timeout: 5000

  staging:
    staging_slave1:
      database: staging_slave_db
      adapter: postgresql
      encoding: utf8
      pool: 5
      username: postgres
      password: password
      port: 5432
      host: localhost
      timeout: 5000

  production:
    production_slave1:
      database: production_slave1
      adapter: postgresql
      encoding: utf8
      pool: 5
      username: postgres
      password: password
      port: 5432
      host: localhost
      timeout: 5000
    production_slave2:
      database: production_slave2
      adapter: postgresql
      encoding: utf8
      pool: 5
      username: postgres
      password: password
      port: 5432
      host: localhost
      timeout: 5000

モデル毎の振り分け

モデル毎に、マスター、スレーブを使うかを決める場合は、こちら。
fully_replicated: false を指定する。

octopus:
  replicated: true
  fully_replicated: false
  environments:
    - development
    - staging
    - production
  development:
   development_slave1:
      database: development_slave_db
      adapter: postgresql
      encoding: utf8
      pool: 5
      username: postgres
      password: password
      port: 5432
      host: localhost
      timeout: 5000

  staging:
    staging_slave1:
      database: staging_slave_db
      adapter: postgresql
      encoding: utf8
      pool: 5
      username: postgres
      password: password
      port: 5432
      host: localhost
      timeout: 5000

  production:
    production_slave1:
      database: production_slave1
      adapter: postgresql
      encoding: utf8
      pool: 5
      username: postgres
      password: password
      port: 5432
      host: localhost
      timeout: 5000
    production_slave2:
      database: production_slave2
      adapter: postgresql
      encoding: utf8
      pool: 5
      username: postgres
      password: password
      port: 5432
      host: localhost
      timeout: 5000

config/shards.ymlの他に、modeleから、replicated_model() メソッドを呼び出します。

#This class is replicated, writes to master and reads to slave.
class Cat < ActiveRecord::Base
  replicated_model()
end

接続の確認

上記まで、設定が完了していれば、rails c で

Cat.find(1)

とすれば、 下記のような、ログがとれるはずです。

[Shard: development_slave1]  Cat Load (0.4ms)  SELECT "cats".* FROM "cats" WHERE "cats"."id" = $1 LIMIT 1  [["id", 1]]

直接DBを選択する場合

usingメソッドを呼び出します。

マスターに投げる場合

Cat.using(:master).find(1)

スレーブに投げる場合

Cat.using(:development_slave1).find(1)

または、ブロックを使って。

Octopus.using(:development_slave1) do 
　Cat.count
end

テスト環境の構築

テスト環境にもマスターDB、スレーブDBを作るのが環境的にはベストかもしれませんが、状況によって作れないこともあると思うので、
そんな時は、読み取り専用のユーザーを作るの楽かと思います。

読み取り専用ユーザーの作成

postgresでの読み取り専用ロールの作り方は以下

CREATE ROLE read_only_user LOGIN REPLICATION PASSWORD 'password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

shards.ymlの設定変更

下記のように、databaseをマスターDBに向けて、usernameをread_only_userに変更すれば、OKです。

octopus:
  replicated: true
  fully_replicated: false
  environments:
    - development
    - staging
    - production
  development:
   development_slave:
      database: development_master_db
      adapter: postgresql
      encoding: utf8
      pool: 5
      username: read_only_user
      password: password
      port: 5432
      host: localhost
      timeout: 5000

障害発生時の対応

もし、マスターDBに以上が発生した場合などに、取り合えず、振り分け処理を停止したい時は - production をコメントアウトするのが有効かと思います。

octopus:
  replicated: true
  fully_replicated: false
  environments:
#    - development
#    - staging
#    - production
  development:
   development_slave:

最低限、productionのみコメントにすれば、OKかと思います。
また、開発環境やステージング環境で、レプリケーション不要なんて時もコメントアウトしておけば、振り分け処理はおこなわれません。

Postgresでストリーミング・レプリケーションの環境を構築する方法

ストリーミング・レプリケーションの構築方法を下記します。
postgresのサーバーはそれぞれ以下と仮定します。

master 10.0.4.10
slave  10.0.4.11

マスターの設定

replicatino用のuser 生成

userの作成は任意ですが、今回 repl_userを作成しました。

CREATE ROLE repl_user LOGIN REPLICATION PASSWORD 'repl_user_password';  

一般ユーザーに対して、REPLICATION権限を与えています。
※ repl_user_passwordは適宜設定してください。

pg_hba.confの設定

以下のレプリケーション用の設定を追加する。
slaveからmasterにアクセスできるように接続設定をします。

host　replication　repl_user　10.0.4.11/32　 md5

シンタックス

host  database  user  address  auth-method

host : TCP/IPを使用した接続
database : 仮想データベースと考えてreplicationを指定
user:アクセスするuser
address : slaveのアドレス
auth-method : md5 認証時にMD5暗号化パスワードを要求
pg_hba.confファイルについてはこちら

postgresql.confの設定

postgresql.confを以下のように編集する。

wal_level = hot_standby
max_wal_sender = 2 # standby dbの数+1
# アーカイブ使用時
archive_mode = on
archive_command = 'cp "%p" /pgdata/archive/"%f"'

WALを利用して、/pgdata/archive/配下に配置することを想定しています。
ただし、このままでは、WALアーカイブがたまる一方になってしまうため、置き場のディスク容量不足を避けるため、十分に古いWALアーカイブを定期的に削除する必要があります。
WAL置き場のディレクトリも事前に作成しておきます。

mkdir /pgdata/archive
chown -R postgres:postgres /pgdata/archive

アクセスするユーザーグループはそれぞれ、postgresとしています。

postgres 再起動

上記までの設定が終わったらpostgresを再起動
/etc/init.d/postgresql restart

その他

ダンプ

念のため、masterのbumpを取っておく

pg_dump -U postgres -Fc db_name > /pgdata/db_name_$date.dump

リストア

と、いざという時に、そのリストアの方法

dropdb db_name
pg_restore -C -d db_name /pgdata/db_name_$date.dump

スレーブの設定

DBの停止

もし、DBが動いていたら、DBを一旦停止します。

/etc/init.d/postgresql stop

既存dataファイルの削除

もし、postgresのデータが存在する場合、一度、全て削除しておく。

cd /pgdata
rm -rf *

base backupの実行

以下を実行する場合、master側の/pgdata配下がpostgres権限である必要があります。

pg_basebackup -h 10.0.4.10 -p 5432 -U repl_user -D /pgdata --xlog --checkpoint=fast --progress
chown postgres:postgres -R /pgdata/
chmod 700 -R /pgdata/

-h : マスターのIPを指定
-D : postgres用のデータの配置場所を指定

postgresql.confの設定

postgresql.confを以下のように編集する。

wal_level = hot_standby
standby_mode = on

recovery.confの設定

# standbyを有効化
standby_mode = 'on'

# masterを指定
primary_conninfo = 'host=10.0.4.10 port=5432 user=repl_user password=repl_user_password'

restore_command = 'cp /pgdata/archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /pgdata/archive %r'

postgresのデータ置き場の権限を変更

chown postgres:postgres -R /pgdata/
chmod 700 -R /pgdata/

postgres 再起動

上記までの設定が終わったらpostgresを再起動

/etc/init.d/postgresql restart

起動確認

master側

SELECT * FROM pg_stat_replication;

上記を実行して、stateが streamingになっていればOK
その他の stateは以下です。

• startup : 接続の確立中
• backup : pg_basebackup によるバックアップの実施中
• catchup : 過去の更新を反映中
• streaming : 更新をリアルタイムに反映中

slave側

SELECT pg_last_xact_replay_timestamp();

上記を実行すると、処理が行われた時刻を取得することができます。

フェールオーバの方法

もし、masterに障害が発生して、フェールオーバーしたい時は、
pg_ctl promoteを実行することで、recovery.confを変更して、レプリケーションを切り離してくれます。

su - postgres -c "/usr/bin/pg_ctl promote -D /pgdata

後は、アプリケーションのmasterに向いている接続をslaveに変更すれば再稼働可能です。

Postgresで結果をCSV出力する方法

postgresでSQLの結果をCSV出力する。

実行するSQLをコマンドで直接渡す方法とSQLファイルを実行するパターンがありますが、よく使うのは、SQLファイルを実行する方法です。

SQLファイルを実行する方法

書き方は

psql [DB名] -U [ユーザー名] -f [SQLファイル.sql] -A -F, >> [出力CSVファイル.csv]

-A 桁揃えしない
-F 区切り文字指定
例

psql dbname -U postgres -f test.sql -A -F, >> test-result.csv

直接SQLを指定する方法

少し、SQLが複雑になってしまうと、めんどになってしまうのであまり使いませんが、コマンドはこんな感じ。
シンプルなSQLには、こちらの方が便利ですね。
psql [DB名] -U [ユーザー名] -c [SQL] -A -F, [出力CSVファイル.csv]
例

psql dbname -U postgres -f "select * from user;" -A -F, >> test-result.csv