Kohei Nozaki's blog 

Entries tagged [postgres]

Configuring Apache James to use PostgreSQL as the backend


Posted on Sunday Apr 19, 2015 at 09:37PM in Technology


UPDATE: I’m running into a strange problem of using PostgreSQL as backend with Thunderbird. I recommend to use an other database as the backend at the moment. http://www.mail-archive.com/server-user%40james.apache.org/msg14715.html

UPDATE2: I created a patch to solve the issue and it seems fine. for detail and download the patch see: https://issues.apache.org/jira/browse/MAILBOX-228

I have been used Apache James with Embedded Derby, but its CPU/IO consumption is increasing day by day. I’m not sure where is the bottleneck but I decided to move to PostgreSQL as its backend anyway.

  1. Put JDBC driver (I used postgresql-9.3-1100.jdbc41.jar) into $JAMES_HOME/conf/lib

  2. Create james-database.properties from template, and put it into $JAMES_HOME/conf as follows

    database.driverClassName=org.postgresql.Driver
    database.url=jdbc:postgresql://127.0.0.1:5432/james
    database.username=james
    database.password=***
    
    vendorAdapter.database=POSTGRESQL
    
    openjpa.streaming=true
  3. Restart James instance

  4. Create domains, addresses and aliases:

    ./james-cli.sh -h localhost -p 9999 adddomain example.org
    ./james-cli.sh -h localhost -p 9999 adduser kyle@example.org ***
    ./james-cli.sh -p 9999 -h localhost addaddressmapping postmaster example.org kyle@example.org

Note

This guide claims that setting standard_conforming_strings=off is needed, but I guess this issue may address this problem, so I don’t use this setting at the moment. my James installation is shipped with openjpa-2.2.1.jar and the issue was fixed in 2.2.0.


Registering PostgreSQL JDBC driver & datasource on WildFly


Posted on Monday Jan 26, 2015 at 07:35AM in Technology


Registering JDBC driver as a module:

module add \
 --name=org.postgresql \
 --resources=/tmp/postgresql-9.3-1102.jdbc41.jar \
 --resource-delimiter=, \
 --dependencies=javax.api,javax.transaction.api

Registering JDBC driver which is referencing a module:

/subsystem=datasources/jdbc-driver=postgresql:add(driver-name=postgresql, \
 driver-module-name=org.postgresql, \
 driver-class-name=org.postgresql.Driver, \
 driver-datasource-class-name=org.postgresql.ds.PGSimpleDataSource, \
 driver-xa-datasource-class-name=org.postgresql.xa.PGXADataSource)

Registering datasource

data-source add \
 --name=MyDS \
 --driver-name=postgresql \
 --jndi-name=java:jboss/jdbc/MyDS \
 --user-name=wildfly \
 --password=**** \
 --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker \
 --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter \
 --connection-url=jdbc:postgresql://localhost:5432/wildfly

Registering XA datasource

xa-data-source add \
 --name=MyDS \
 --driver-name=postgresql \
 --jndi-name=java:jboss/jdbc/MyDS \
 --user-name=wildfly \
 --password=****\
 --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker \
 --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter \
 --xa-datasource-properties={ \
  "ServerName" => "localhost", \
  "PortNumber" => "5432", \
  "DatabaseName" => "wildfly"}

Testing connection:

/subsystem=datasources/xa-data-source=MyDS:test-connection-in-pool

References


Partial data dump/restore


Posted on Tuesday Mar 11, 2014 at 03:45PM in Technology


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


ログ出力の設定をしてみる


Posted on Sunday Feb 02, 2014 at 10:43AM in Technology


環境

  • 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 グループ


ダンプしてリストアしてみる


Posted on Tuesday Jan 21, 2014 at 02:32PM in Technology


何をするの?

手元の端末で作った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=> 

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

参考文献