Kohei Nozaki's blog 

dump data

hogedb=# copy (select * from hogetable where basedate between '20070101' and '20070131') TO '/tmp/1month.sql';
COPY 561368
hogedb=# 

dump schema

pg_dump --schema-only -t hogetable hogedb > /tmp/1month.ddl

restore schema

hogedb=# \i /tmp/1month.ddl 
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
hogedb=# 

restore data

hogedb=# copy hogetable from '/tmp/1month.sql';
COPY 561368
hogedb=#

References

1. COPY

環境

• PostgreSQL 9.2.4
• OS X 10.9.1

何をするか

• OS XのsyslogをPostgreSQLからのログ出力を受け入れられるように設定する
• PostgreSQLのログをsyslogへ出力するようにする
• 実行されるSQLのログを取ってみる

OS Xのsyslogの設定

/etc/syslog.confを編集する

一番下の行を追加する。こんな感じ。flat file logsは/etc/asl.confに設定されているとか書かれているのでそっちに書いた方がいいのかもしれないけど面倒なのでここで

# Note that flat file logs are now configured in /etc/asl.conf

install.*                                               @127.0.0.1:32376
local0.*                                                /var/log/postgresql

設定内容を反映させる

sudo launchctl unload /System/Library/LaunchDaemons/com.apple.syslogd.plist
sudo launchctl load /System/Library/LaunchDaemons/com.apple.syslogd.plist

こんなんでもよい

ps -Af | awk '/\/usr\/sbin\/syslogd$/{print "sudo kill -HUP " $2}' | sh

ログを出力してみる

kyle-no-MacBook:etc kyle$ logger -p local0.notice hogehoge
kyle-no-MacBook:etc kyle$ ls -l /var/log/postgresql 
-rw-r--r--@ 1 root  wheel  58  2  2 10:55 /var/log/postgresql
kyle-no-MacBook:etc kyle$ cat /var/log/postgresql 
Feb  2 10:55:06 kyle-no-MacBook.local kyle[880]: hogehoge
kyle-no-MacBook:etc kyle$ 

ちゃんと出ていますね。「コンソール」からも内容を見られるのは便利かも

PostgreSQLの設定

$PG_HOME/data/postgresql.confを編集

sudo su postgres -c 'vi /Library/PostgreSQL/9.2/data/postgresql.conf'

以下内容にする

log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
log_statement = 'all' 

一番下がSQLをログに出す設定。大量に出るので要注意

再起動

sudo su postgres -c 'pg_ctl -D /Library/PostgreSQL/9.2/data restart'

ログを見てみる

kyle-no-MacBook:~ kyle$ psql
Password: 
psql (9.2.4)
Type "help" for help.

kyle=# select now();
             now              
------------------------------
 2014-02-02 11:32:20.67332+09
(1 row)

kyle=# \q
kyle-no-MacBook:~ kyle$ cat /var/log/postgresql.log 
Feb  2 11:20:21 kyle-no-MacBook.local kyle[1002]: hogehogehoge
Feb  2 11:31:24 kyle-no-MacBook.local postgres[1073]: [1-1] 2014-02-02 11:31:24 JST LOG:  database system was shut down at 2014-02-02 11:31:23 JST
Feb  2 11:31:24 kyle-no-MacBook.local postgres[1077]: [1-1] 2014-02-02 11:31:24 JST LOG:  autovacuum launcher started
Feb  2 11:31:24 kyle-no-MacBook.local postgres[1071]: [1-1] 2014-02-02 11:31:24 JST LOG:  database system is ready to accept connections
Feb  2 11:31:30 kyle-no-MacBook.local postgres[1083]: [2-1] 2014-02-02 11:31:30 JST LOG:  incomplete startup packet
Feb  2 11:32:20 kyle-no-MacBook.local postgres[1098]: [2-1] 2014-02-02 11:32:20 JST LOG:  statement: select now();
kyle-no-MacBook:~ kyle$ 

出てますね

その他

できればSQLのログはシビリティをdebugとかにしたいので若干調べた[6]が、よくわからん。現状無理ということだろうか

参考文献

1. Linux管理者への道（3）：システム管理の基礎 syslogdの設定をマスターしよう (2/3) - ＠IT
2. ログ関連の設定 — Let's Postgres
3. 第10回Mac OS X Server勉強会 Mac OSXシステム管理 読書会「ログ」 - サイト更新停滞ちうっ
4. 新世代syslogデーモン徹底活用（1）：syslogdの限界と次世代シスログデーモン (1/3) - ＠IT
5. PostgreSQL/PostgreSQLでSQLステートメントをログファイルに出力する方法 - 調べる.db
6. [GENERAL] log_statement and syslog severity - Google グループ

何をするの？

手元の端末で作ったDBを別のマシン上のPostgreSQLにリストアしてみます

環境

ダンプ環境

PostgreSQL 9.2.4 @ OS X 10.9.1

リストア環境

PostgreSQL 9.2.6 @ CentOS 6.5

テスト用のDBを作る

作成用SQL

一応日本語が化けるのとか心配なので日本語も入れてみます

create database testdb001 owner kyle encoding 'UTF8' TEMPLATE template0;
\c testdb001
create table hogetable (id bigint, hogecol varchar(255));
insert into hogetable values (1, 'hoge111');
insert into hogetable values (2, 'ほげ222');

流す

何故かpsqlのプロンプトに日本語をコピペすると文字化けしてしまうのでヒアドキュメント経由で食わせます

kyle-no-MacBook:prepare kyle$ psql <<EOF
> create database testdb001 owner kyle encoding 'UTF8' TEMPLATE template0;
> \c testdb001
> create table hogetable (id bigint, hogecol varchar(255));
> insert into hogetable values (1, 'hoge111');
> insert into hogetable values (2, 'ほげ222');
> EOF
CREATE DATABASE
You are now connected to database "testdb001" as user "kyle".
CREATE TABLE
INSERT 0 1
INSERT 0 1
kyle-no-MacBook:prepare kyle$

ちゃんと入ったか確認してみましょう

kyle-no-MacBook:prepare kyle$ psql testdb001
psql (9.2.4)
Type "help" for help.

testdb001=# select * from hogetable ;
 id | hogecol 
----+---------
  1 | hoge111
  2 | ほげ222
(2 rows)

testdb001=# 

大丈夫そうですね。

ダンプ

もっとも柔軟だそうなのでcustom形式にします。このコマンドで

pg_dump -F c testdb001 > testdb001.dump

実行してみる

kyle-no-MacBook:~ kyle$ pg_dump -F c testdb001 > testdb001.dump
kyle-no-MacBook:~ kyle$ ls -l testdb001.dump 
-rw-r--r--+ 1 kyle  staff  2106  1 21 13:37 testdb001.dump
kyle-no-MacBook:~ kyle$

こいつをscpか何かでリストア環境に持っていきます

リストア

このコマンドでいってみる

pg_restore -v -C -d postgres -j 2 testdb001.dump

-v

進捗状況を詳細表示

-C

リストアの前にデータベースを作成する

-d postgres

接続時のDBにpostgresを使う。-Cを同時に指定している場合，データのリストア先ではない。リストア先のDB名はダンプファイルに含まれるものが使われる．-Cを同時に指定していない場合は，データのリストア先になる．

-j 2

2パラで同時実行。この場合入力はパイプではだめらしい

スーパーユーザで実行するならこう

sudo -u postgres sh -c "pg_restore -v -C -d postgres -j 2 testdb001.dump"

ダンプファイルをpostgresユーザが読めるところに置いて実行

$ cp testdb001.dump /tmp
$ cd /tmp
$ sudo -u postgres sh -c "pg_restore -v -C -d postgres -j 2 testdb001.dump"
pg_restore: リストアのためにデータベースに接続しています
pg_restore: 2183 ENCODING ENCODING を処理しています
pg_restore: 2184 STDSTRINGS STDSTRINGS を処理しています
pg_restore: 2185 DATABASE testdb001 を処理しています
pg_restore: DATABASE testdb001を作成しています
pg_restore: 新しいデータベース"testdb001"に接続しています
pg_restore: データベース"testdb001"にユーザ"postgres"で接続しています
pg_restore: 5 SCHEMA public を処理しています
pg_restore: SCHEMA publicを作成しています
pg_restore: 2186 COMMENT SCHEMA public を処理しています
pg_restore: COMMENT SCHEMA publicを作成しています
pg_restore: 2187 ACL public を処理しています
pg_restore: 169 EXTENSION plpgsql を処理しています
pg_restore: EXTENSION plpgsqlを作成しています
pg_restore: 2188 COMMENT EXTENSION plpgsql を処理しています
pg_restore: COMMENT EXTENSION plpgsqlを作成しています
pg_restore: 168 TABLE hogetable を処理しています
pg_restore: TABLE hogetableを作成しています
pg_restore: メインの並列ループに入ります
pg_restore: 項目 2180 TABLE DATA hogetable に着手します
pg_restore: テーブル"hogetable"のデータをリストアしています
pg_restore: 項目 2180 TABLE DATA hogetable を完了しました
pg_restore: メインの並列ループを終了します
pg_restore: DATABASE testdb001用の所有者と権限を設定しています
pg_restore: SCHEMA public用の所有者と権限を設定しています
pg_restore: COMMENT SCHEMA public用の所有者と権限を設定しています
pg_restore: ACL public用の所有者と権限を設定しています
pg_restore: EXTENSION plpgsql用の所有者と権限を設定しています
pg_restore: COMMENT EXTENSION plpgsql用の所有者と権限を設定しています
pg_restore: TABLE hogetable用の所有者と権限を設定しています
pg_restore: TABLE DATA hogetable用の所有者と権限を設定しています
$ echo $?
0
$ 

確認してみましょう

$ psql testdb001
psql (9.2.6)
"help" でヘルプを表示します.

testdb001=> \d
            リレーションの一覧
 スキーマ |   名前    |    型    | 所有者 
----------+-----------+----------+--------
 public   | hogetable | テーブル | kyle
(1 行)

testdb001=> select * from hogetable ;
 id | hogecol 
----+---------
  1 | hoge111
  2 | ほげ222
(2 行)

testdb001=> 

権限とかスキーマが若干不安ですがデータは移っているようですね。

参考文献

• pg_dump
• pg_restore
• 論理バックアップ（pg_dump と pg_dumpall） — Let's Postgres