Postgresql 编译安装

编译安装

#!/bin/bash
# 进入源码目录
cd /var/lib/postgresql-12.7/
./configure --prefix=/usr/local/postgresql --without-readline
make && make install
cd contrib
make && make install
# 添加wal 日志解析工具
cd walminer
make && make install
groupadd postgres
useradd -g postgres postgres
mkdir -p /opt/postgresql/data
chown postgres:postgres /opt/postgresql/ -R
chown postgres:postgres /usr/local/postgresql -R
su - postgres
echo "export PGHOME=/usr/local/postgresql" >> ~/.bash_profile
echo "export PGDATA=/opt/postgresql/data" >> ~/.bash_profile
echo "export PATH=$PGHOME/bin:$PATH" >> ~/.bash_profile
echo "export MANPATH=$PGHOME/share/man:$MANPATH" >> ~/.bash_profile
echo "export LANG=en_US.utf8" >> ~/.bash_profile
echo "export DATE=`date +"%Y-%m-%d %H:%M:%S"`" >> ~/.bash_profile
echo "export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH" >> ~/.bash_profile
echo "alias rm='rm  -i'" >> ~/.bash_profile
echo "alias ll='ls -lh'" >> ~/.bash_profile
source ~/.bash_profile
initdb -D /opt/postgresql/data
pg_ctl -D /opt/postgresql/data -l /opt/postgresql/logfile start

创建用户

ALTER USER postgres WITH PASSWORD '123456';
# ALTER ROLE
CREATE USER sonar with password 'sonar';
#CREATE ROLE
create database sonar OWNER sonar;
#CREATE DATABASE
GRANT ALL PRIVILEGES ON DATABASE sonar TO sonar
\q

日志解析工具使用

movead/WalMiner - 码云 - 开源中国 (gitee.com)

选择需要查询的数据库，执行下列命令：

-- create extension walminer;
-- 
-- select walminer_wal_add('/opt/postgresql/data/pg_wal');
-- create extension walminer;
-- select walminer_all();
select * from walminer_contents;
-- DROP EXTENSION IF EXISTS walminer;
-- CREATE EXTENSION IF NOT EXISTS walminer;
-- select walminer_wal_add('/opt/postgresql/data/pg_wal');
-- 
select walminer_all();
-- select COUNT(*) from walminer_contents;
-- select walminer_wal_list();
-- select wal2sql();

-- SELECT now() AS start_time \gset
-- SELECT now() AS time3 ;

-- SELECT walminer_by_time('2021-06-12 00:25:16.691117+08', '2021-06-12 00:45:16.691117+08','true');