-- 1 ddl
CREATE USER u1 PASSWORD 'u1.password';
CREATE TABLE t1(c1 INT, c2 TEXT);
CREATE TABLE t2 (c1 INT, c2 TEXT);
CREATE INDEX i1 ON t1(c2);

-- 2 dml
INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
INSERT INTO t2 VALUES (1, 'aaa'), (5, 'bbb');
UPDATE t1 SET c2 = 'ddd' WHERE c1 < 3;
DELETE FROM t1 WHERE c1 = 2;

-- 3 select
SELECT * FROM t1;

-- 4 function
SELECT c2 || '_oper' FROM t1;
SELECT concat(c2, '_func') FROM t1;
INSERT INTO t1 VALUES (5, concat('aaa', '_func')), (6, concat('aaa', '_func'));

-- 5 multi
INSERT INTO t1 SELECT * FROM t2;
SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1;
CREATE TABLE t3 AS SELECT * FROM t2;
DROP TABLE t1,t2,t3;

# 1 配置环境变量
echo 'export BUILD_ROOT=`pwd`' > pgenv
echo 'export PG_HOME=$BUILD_ROOT/install' >> pgenv
echo 'export PATH="$PG_HOME/bin:$PATH"' >> pgenv
source pgenv

# 2 下载源码
wget https://ftp.postgresql.org/pub/source/v14.18/postgresql-14.18.tar.gz
tar -zxvf postgresql-14.18.tar.gz
cd postgresql-14.18.tar.gz

# 3 编译源码 (不开启debug模式，提高性能)
#./configure --prefix=$PG_HOME --enable-debug=yes
./configure --prefix=$PG_HOME --enable-debug=no --enable-cassert=no
make -sj
make install -sj

# ==========================================
cd $BUILD_ROOT/pg14/contrib/pgaudit
make install USE_PGXS=1 PG_CONFIG=$PG_HOME/bin/pg_config
cd -
# ==========================================

# 4 初始化集群
initdb -D $PG_HOME/data

# 4 配置集群
# echo "port = 54000" >> $PG_HOME/data/postgresql.conf
# echo "max_connections = 1000" >> $PG_HOME/data/postgresql.conf

# ==========================================
echo "logging_collector = on" >> $PG_HOME/data/postgresql.conf
echo "shared_preload_libraries = 'pgaudit'" >> $PG_HOME/data/postgresql.conf
echo "pgaudit.log = 'ALL'" >> $PG_HOME/data/postgresql.conf

echo "pgaudit.log_catalog = true" >> $PG_HOME/data/postgresql.conf
echo "pgaudit.log_client = true" >> $PG_HOME/data/postgresql.conf
echo "pgaudit.log_parameter = true" >> $PG_HOME/data/postgresql.conf
echo "pgaudit.log_relation = true" >> $PG_HOME/data/postgresql.conf
echo "pgaudit.log_rows = true" >> $PG_HOME/data/postgresql.conf
echo "pgaudit.log_statement = true" >> $PG_HOME/data/postgresql.conf
# ==========================================

# 5 启动集群
pg_ctl start -D $PG_HOME/data

# ==========================================
DROP EXTENSION pgaudit;
CREATE EXTENSION pgaudit;
CREATE UNLOGGED TABLE adt(c1 TEXT);

CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (1);

\! ls $PG_HOME/data/log
\! tail $PG_HOME/data/log/
# ==========================================

echo "
shared_buffers = 200GB
work_mem = 1GB
maintenance_work_mem = 4GB
effective_cache_size = 500GB
wal_buffers = 1GB
checkpoint_timeout = 55min
" >> $PG_HOME/data/postgresql.conf

show shared_buffers;
show work_mem;
show maintenance_work_mem;
show effective_cache_size;
show wal_buffers;
show checkpoint_timeout;

# 1 下载源码
wget https://codeload.github.com/pgaudit/pgaudit/tar.gz/refs/tags/1.6.2 -O pgaudit-1.6.2.tar.gz
tar -zxvf pgaudit-1.6.2.tar.gz

# 2 编译
cd pgaudit-1.6.2
make install USE_PGXS=1 PG_CONFIG=$PG_HOME/bin/pg_config

# 3 验证编译成功
ll $PG_HOME/share/extension
cat $PG_HOME/data/postgresql.conf | grep shared_preload_libraries

# 4 开启日志功能
echo "logging_collector = on" >> $PG_HOME/data/postgresql.conf
echo "log_statement = all" >> $PG_HOME/data/postgresql.conf

# 5 开启 pgaudit
echo "shared_preload_libraries = 'pgaudit'" >> $PG_HOME/data/postgresql.conf
echo " pgaudit.log = 'ALL'" >> $PG_HOME/data/postgresql.conf

-- 1 安装pgaudit
CREATE EXTENSION pgaudit;

-- 2 查看配置参数
SHOW pgaudit.log;
    -- ALL
    -- READ: SELECT
    -- WRITE: INSERT, UPDATE, DELETE
    -- ROLE
    -- DDL
    -- FUNCTION
    -- MISC
    -- MISC_SET
SHOW pgaudit.role;
SHOW pgaudit.log_level;

-- 查看审计文件
SHOW log_directory;
SHOW log_filename;