1. 安装依赖包

[root]# yum install -y perl-ExtUtils-Embed python-devel bison flex readline-devel zlib-devel gcc gcc-c++ wget

2. 源码安装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 install

3. 源码安装postgres

[root]# wget https://ftp.postgresql.org/pub/source/v14.5/postgresql-14.5.tar.gz
[root]# tar -zxvf postgresql-14.5.tar.gz
[root]# cd postgresql-14.5
[root]# ./configure --prefix=/usr/local/postgresql-14.5 --with-perl --with-python --enable-thread-safety --with-uuid=ossp --with-segsize=64
[root]# make && make install

4. 安装contrib的工具

[root]# cd postgresql-14.5/contrib
[root]# make && make install

5. 更新安装目录用户及属组并做软链接

[root]# chown -R postgres:postgres /usr/local/postgresql-14.5
[root]# ln -s /usr/local/postgresql-14.5 /usr/local/postgresql

6. 软链接libuuid.so.16

[root]# ln -s /usr/local/lib/libuuid.so.16 /usr/local/postgresql-14.5/lib/

7. 创建PGDATA目录

[root]# mkdir /data/postgres
[root]# chown postgres:postgres /data/postgres
[root]# chmod 0700 /data/postgres

8. 创建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/lib

9. 初始化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 start

10.启动postgres

启动postgres

[postgres]# pg_ctl start
waiting for server to start....2022-09-22 08:52:37.658 CST [33036] LOG:  starting PostgreSQL 14.5 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]# psql
postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 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_wal();
 pg_switch_wal 
---------------
 0/4000160

查看归档目录是否已存在归档文件

[postgres]# ls $PGDATA/arch_log
000000010000000000000004

pg_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 EXTENSION

pg_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_exec_time desc limit 10;

平均执行时间较长的SQL

select * from pg_stat_statements order by mean_exec_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_exec_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_exec_time DESC LIMIT 10;
参考https://www.cnblogs.com/haha029/p/16718252.html