PostgreSQL クエリログ変更履歴を残す

DDL+, DROP, ALTER, INSERT, UPDATE, DELETEといった変更履歴を残す設定

# vi /var/lib/pgsql/data/postgresql.conf

logging_collector=on
log_statement = 'mod'
log_line_prefix='[%t]%u %d %p[%l]'

Amazon Linux2に設定する場合

PostgreSQLサーバのインストール

# yum install -y postgresql postgresql-server postgresql-libs php-pgsql

su - postgres

基本DBの作成

initdb --encoding=UTF8 --no-locale

サービスの開始

pg_ctl start

ファイルが作成される

# ls -laht /var/lib/pgsql/data/

total 52K
drwx------  4 postgres postgres   75 May 22 06:19 ..
drwx------  2 postgres postgres   25 May 22 06:18 pg_stat_tmp
drwx------ 15 postgres postgres 4.0K May 22 06:18 .
drwx------  2 postgres postgres 4.0K May 22 06:18 global
drwx------  2 postgres postgres   32 May 22 06:18 pg_log
drwx------  2 postgres postgres   18 May 22 06:18 pg_notify
-rw-------  1 postgres postgres   18 May 22 06:18 postmaster.opts
-rw-------  1 postgres postgres   91 May 22 06:18 postmaster.pid
drwx------  5 postgres postgres   41 May 22 06:18 base
drwx------  2 postgres postgres   18 May 22 06:18 pg_clog
drwx------  2 postgres postgres   18 May 22 06:18 pg_subtrans
drwx------  3 postgres postgres   60 May 22 06:18 pg_xlog
-rw-------  1 postgres postgres 4.4K May 22 06:18 pg_hba.conf
-rw-------  1 postgres postgres 1.6K May 22 06:18 pg_ident.conf
-rw-------  1 postgres postgres  20K May 22 06:18 postgresql.conf
drwx------  4 postgres postgres   36 May 22 06:18 pg_multixact
drwx------  2 postgres postgres    6 May 22 06:18 pg_tblspc
-rw-------  1 postgres postgres    4 May 22 06:18 PG_VERSION
drwx------  2 postgres postgres    6 May 22 06:18 pg_serial
drwx------  2 postgres postgres    6 May 22 06:18 pg_snapshots
drwx------  2 postgres postgres    6 May 22 06:18 pg_twophase

# vi /var/lib/pgsql/data/postgresql.conf


# This is used when logging to stderr:
logging_collector = on                  # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)


・・・
・・・


#log_lock_waits = off                   # log lock waits >= deadlock_timeout
#log_statement = 'none'                 # none, ddl, mod, all
#log_temp_files = -1                    # log temporary files equal or larger
                                        # than the specified size in kilobytes;
                                        # -1 disables, 0 logs all temp files
log_timezone = 'UTC'

※下記を追加

log_statement = 'mod'
log_line_prefix='[%t]%u %d %p[%l]'

# su - postgres

設定の反映

-bash-4.2$ pg_ctl restart

waiting for server to shut down.... done
server stopped
server starting

-bash-4.2$ psql -U postgres

psql (9.2.24)
Type "help" for help.

postgres=# create database testdb;

CREATE DATABASE

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     |
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     |
(4 rows)

postgres=# 

CREATE TABLE city ( 
    id              int,
    city            varchar(80)
);

postgres=# INSERT INTO city VALUES(1,'Kayabacho');
INSERT 0 1

postgres=# DELETE FROM city WHERE id = 1;
DELETE 1

postgres-# \q

-bash-4.2$ exit
logout

ログの確認

# ls -lhat /var/lib/pgsql/data/pg_log/

total 8.0K
-rw-------  1 postgres postgres 2.2K May 22 06:27 postgresql-Wed.log
drwx------ 15 postgres postgres 4.0K May 22 06:21 ..
drwx------  2 postgres postgres   32 May 22 06:18 .

# view /var/lib/pgsql/data/pg_log/postgresql-Wed.log


[2019-05-22 06:21:49 UTC]  3824[1]LOG:  database system was shut down at 2019-05-22 06:21:48 UTC
[2019-05-22 06:21:49 UTC]  3822[1]LOG:  database system is ready to accept connections
[2019-05-22 06:21:49 UTC]  3828[1]LOG:  autovacuum launcher started
[2019-05-22 06:23:21 UTC]postgres postgres 3870[1]LOG:  statement: create database testdb;
[2019-05-22 06:25:27 UTC]postgres postgres 3870[4]LOG:  statement: CREATE TABLE city (
            id              int,
            city            varchar(80)
        );

[2019-05-22 06:26:29 UTC]postgres postgres 3870[13]STATEMENT:  INSERT INTO city VALUES(1,"Kayabacho");
[2019-05-22 06:27:06 UTC]postgres postgres 3870[14]LOG:  statement: INSERT INTO city VALUES(1,'Kayabacho');
[2019-05-22 06:27:33 UTC]postgres postgres 3870[15]LOG:  statement: DELETE FROM city WHERE id = 1;

きちんと出力されていますね。