基础环境
CentOS 7.6
PostgreSQL 9.6
PostgreSQL 9.6安装
1. 安装依赖包
[root]# yum install -y perl-ExtUtils-Embed python-devel bison flex readline-devel zlib-devel gcc gcc-c++ wget2. 源码安装uuid
[root]# wget ftp://ftp.ossp.org/pkg/lib/uuid/uuid-1.6.2.tar.gz
[root]# tar -zxvf uuid-1.6.2.tar.gz
[root]# cd uuid-1.6.2
[root]# ./configure --with-uuid=ossp
[root]# make && make install3. 源码安装postgres
[root]# https://ftp.postgresql.org/pub/source/v9.6.0/postgresql-9.6.0.tar.gz
[root]# tar -zxvf postgresql-9.6.0.tar.gz
[root]# cd postgresql-9.6.0
[root]# ./configure --prefix=/usr/local/postgresql-9.6.0 --with-perl --with-python --enable-thread-safety --with-uuid=ossp --with-wal-segsize=64
[root]# make && make install4. 安装contrib的工具
[root]# cd postgresql-9.6.0/contrib
[root]# make && make install5. 更新安装目录用户及属组并做软链接
[root]# chown -R postgres:postgres /usr/local/postgresql-9.6.0
[root]# ln -s /usr/local/postgresql-9.6.0 /usr/local/postgresql6. 软链接libuuid.so.16
[root]# ln -s /usr/local/lib/libuuid.so.16 /usr/local/postgresql-9.6.0/lib/7. 创建PGDATA目录
[root]# mkdir /data/postgres
[root]# chown postgres:postgres /data/postgres
[root]# chmod 0700 /data/postgres8. 创建postgres用户及环境变量
[root]# useradd postgres
[root]# echo "Your_passwd" | passwd postgres --stdin
[postgres]# vi /home/postgres/.bashrc
[postgres]# export PGDATA=/data/postgres
[postgres]# export PATH=/usr/local/postgresql/bin:$PATH
[postgres]# export LD_LIBRARY_PATH=/usr/local/postgresql/lib9. 初始化postgres
[postgres]# initdb -D $PGDATA -k
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are enabled.
fixing permissions on existing directory /data/postgres ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /data/postgres -l logfile start10.启动postgres
启动postgres
[postgres]# pg_ctl start
waiting for server to start....2022-09-22 08:52:37.658 CST [33036] LOG: starting PostgreSQL 9.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-09-22 08:52:37.659 CST [33036] LOG: listening on IPv6 address "::1", port 5432
2022-09-22 08:52:37.659 CST [33036] LOG: listening on IPv4 address "127.0.0.1", port 5432
2022-09-22 08:52:37.660 CST [33036] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-09-22 08:52:37.662 CST [33037] LOG: database system was shut down at 2022-09-22 08:52:16 CST
2022-09-22 08:52:37.665 CST [33036] LOG: database system is ready to accept connections
done
server started查看postgres版本
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit开启归档
创建归档目录
[postgres]# mkdir $PGDATA/arch_log配置归档脚本
[postgres]# vi $PGDATA/archive.sh
cp --preserve=timestamps $1 $PGDATA/arch_log/$2 ; find $PGDATA/arch_log -type f -mtime +30 | xargs rm -fr;修改归档相关参数
[postgres]# vi $PGDATA/postgresql.conf
archive_mode = on
archive_command = '/bin/bash archive.sh %p %f'
archive_timeout = 1800重启postgres使归档参数生效
[postgres]# pg_ctl restart查看参数是否已经生效
postgres=# select name,setting from pg_settings where name in ('archive_mode','archive_command','archive_timeout');
name | setting
-----------------+----------------------------
archive_command | /bin/bash archive.sh %p %f
archive_mode | on
archive_timeout | 1800手动归档
postgres=# select pg_switch_xlog();
pg_switch_wal
---------------
0/4000160查看归档目录是否已存在归档文件
[postgres]# ls $PGDATA/arch_log
000000010000000000000004pg_stat_statements模块
pg_stat_statements模块提供追踪服务器所执行的所有SQL语句的执行统计信息。
配置参数，重启用于加载pg_stat_statements模块
[postgres]# vi $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_stat_statements'重启postgres
[postgres]# pg_ctl restart创建pg_stat_statements模块
postgres=# create extension pg_stat_statements;
CREATE EXTENSIONpg_stat_statements相关配置参数
postgres=# select name,setting from pg_settings where name like '%pg_stat%';
name | setting
-----------------------------------+---------
pg_stat_statements.max | 10000
pg_stat_statements.save | on
pg_stat_statements.track | all
pg_stat_statements.track_planning | off
pg_stat_statements.track_utility | on
调用次数较多的SQL
select * from pg_stat_statements stat order by calls desc limit 10;总执行时间较长的SQL
select * from pg_stat_statements order by total_time desc limit 10;平均执行时间较长的SQL
select * from pg_stat_statements order by mean_time desc limit 10;在读/写块上总执行时间最多的SQL
select * from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 10;在读/写块上平均执行时间最多的SQL
select * from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 10;查看时间抖动严重的SQL
select * from pg_stat_statements order by stddev_time desc limit 10;消耗共享内存较多的SQL
select * from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;使用临时块较多的SQL
select * from pg_stat_statements order by temp_blks_written desc limit 10;缓冲池命中率较低的SQL
SELECT *,cast(100.0*shared_blks_hit/nullif(shared_blks_hit + shared_blks_read,0) as decimal(10,2)) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
关于pg_stat_statments模块的详细使用请参考：pg_stat_statements
