PostgreSQL体系结构
本地内存
本地内存是服务器进程独占的内存结构，每个postgresql子进程都会分配一小块相应内存空间，随着连接会话的增加而增加，它不属于实例的一部分
work_mem：用于排序的内存
maintenance_work_mem：用于内部运维工作的内存，如VACUUM垃圾回收、创建和重建索引等等
temp_buffers：用于存储临时表的数据
共享内存
Shared Buffer：
-用于缓存表和索引的数据块 -数据的读写都是直接对BUFFER操作的，若所需的块不再缓存中，则需要从磁盘中读取 -在buffer中被修改过的，但又没有写到磁盘文件中的块被称之为脏块 -由shared_buffers参数控制尺寸
WAL(Write Ahead Log) Buffer：
-预写日志缓存用于缓存增删改等写操作产生的事务日志 -由wal_buffers参数控制尺寸
Clog Buffer：
-Commit Log Buffer是记录事务状态的日志缓存
辅助进程
Backgroup writer：
-工作任务是将shared buffer中的脏数据页写到磁盘文件中 -使用LRU算法进行清理脏页 -平时多在休眠，被激活时工作
Autovacuum launcher/workers：
-自动清理垃圾回收进程 -当参数autovacuum设为on的时候启用自动清理功能 -Launcher为清理的守护进程，每次启动的时候会调用一个或多个worker -Worker是负责真正清理工作的进程，由autovacuum_max_workers参数设定其数量
WAL writer：
-将预写日志写入磁盘文件 -触发时机：WAL BUFFER满了 事务commit时； WAL writer进程到达间歇时间时； -checkpoint发生时；
Checkpoint：
-用于保证数据库的一致性 -它会触发bgwriter和wal writer动作 -拥有多个参数控制其启动的间隔 作用： 一般checkpoint会将某个时间点之前的脏数据全部刷新到磁盘，以实现数据的一致性与完整性。其主要目的是为了缩短崩溃恢复时间 Checkpoint 具体工作:
Statistics Collector：
统计信息的收集进程。收集表和索引的空间信息和元组信息等，甚至是表的访问信息。收集到的信息除了能被优化器使用以外，还有autovaccum也能利用，甚至给数据库管理员作为数据库管理的参考信息.
wal_level= replica
archive_mode=on
PostgreSQL 单机部署
01.安装pg所需要的操作系统依赖包
yum install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib zlib-devel perl python36 tcl openssl ncurses-devel openldap pam
02.创建用户
groupadd -g 60000 pgsql
useradd -u 60000 -g pgsql pgsql
echo "pgsql" |passwd --stdin pgsql
03.创建数据库相关目录
mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg12,soft}
chown -R pgsql:pgsql /postgresql
chmod -R 775 /postgresql04.源码安装postgresql
su - pgsql
cd /postgresql/soft
tar zxvf postgresql-12.2.tar.gz
cd postgresql-12.2
./configure --prefix=/postgresql/pg12 --without-readline
make
make install
05.配置数据库环境变量
su - pgsql
vi ~/.bash_profile
export LANG=en_US.UTF8
export PS1="[`whoami`@`hostname`:"'$PWD]$'
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pg12
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export DATE=`date +"%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
06.初始化数据库
su - pgsql
/postgresql/pg12/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres
07.配置参数文件（类my.cnf）
vi /postgresql/pgdata/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 1000
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
shared_buffers = 1024MB
/postgresql/pgdata/pg_hba.conf (网络安全) --repuser 复制用户
host replication repuser 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5
pg_ctl reload
08.启动数据库
su - pgsql
pg_ctl start
pg_ctl stop
● postgresql.service - PostgreSQL database server
Loaded: loaded (/etc/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: activating (start) since Tue 2023-02-28 10:35:56 CST; 2min 5s ago
Docs: man:postgres(1)
Main PID: 26476 (postgres)
CGroup: /system.slice/postgresql.service
├─26476 /postgresql/pg12/bin/postgres -D /postgresql/pgdata
├─26477 postgres: logger
├─26479 postgres: checkpointer
├─26480 postgres: background writer
├─26481 postgres: walwriter
├─26482 postgres: autovacuum launcher
├─26483 postgres: stats collector
└─26484 postgres: logical replication launcher
Feb 28 10:35:56 pg1 systemd[1]: Starting PostgreSQL database server...
Feb 28 10:35:56 pg1 postgres[26476]: 2023-02-28 10:35:56.341 CST [26476] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu... 64-bit
Feb 28 10:35:56 pg1 postgres[26476]: 2023-02-28 10:35:56.342 CST [26476] LOG: listening on IPv4 address "0.0.0.0", port 5432
Feb 28 10:35:56 pg1 postgres[26476]: 2023-02-28 10:35:56.342 CST [26476] LOG: listening on IPv6 address "::", port 5432
Feb 28 10:35:56 pg1 postgres[26476]: 2023-02-28 10:35:56.345 CST [26476] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Feb 28 10:35:56 pg1 postgres[26476]: 2023-02-28 10:35:56.628 CST [26476] LOG: redirecting log output to logging collector process
Feb 28 10:35:56 pg1 postgres[26476]: 2023-02-28 10:35:56.628 CST [26476] HINT: Future log output will appear in directory "pg_log".
PostgreSQL 主从搭建
01.启动归档日志
vi /postgresql/pgdata/postgresql.conf
wal_level= replica
archive_mode=on
#archive_command='/postgresql/archive'
archive_command='test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f'
max_wal_senders=10
wal_keep_segments=256
wal_sender_timeout=60s
重启PG生效配置
pg_ctl stop
pg_ctl start
查看配置是否生效
psql -h 127.0.0.1 -p 5432
show wal_level;
postgres=# show wal_level;
wal_level
-----------
replica
(1 row)
show archive_mode;
postgres=# show archive_mode;
archive_mode
--------------
on
(1 row)
show archive_command;
postgres=# show archive_command;
archive_command
------------------------------------------------------------------
test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f
(1 row)
select pg_switch_wal();
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/1650880
(1 row)
02.创建流复制角色
create role repuser login encrypted password 'repuser123' replication;
查看用户权限
[root@pg3 ~]# su - pgsql
Last login: Tue Feb 28 10:43:29 CST 2023 on pts/0
[pgsql@pg3:/home/pgsql]$psql -h 127.0.0.1 -p 5432
psql (12.2)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
itpux | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repuser | Replication | {}
03.使用流复制用户创建从库1：
su - pgsql
rm -rf /postgresql/pgdata
pg_basebackup -D /postgresql/pgdata -F p -P -R -h 10.100.2.250 -p 5432 -U repuser -l backup20230228
pg_basebackup: error: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host "10.100.2.57", user "repuser"
此报错为网络权限设置有问题
vi /postgresql/pgdata/postgresql.conf
primary_conninfo='host=10.100.2.250 port=5432 user=repuser passowrd=repuser123'
再启动备库：
pg_ctl start
主库查询：
select * from pg_stat_replication;标签：10,postgresql,postgres,28,35,26476,PostgreSQL,主从,搭建 From： https://blog.51cto.com/u_13482808/6093518
\x
postgres=# select * from pg_stat_replication;
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 8379
usesysid | 24581
usename | repuser
application_name | walreceiver
client_addr | 10.100.2.57
client_hostname |
client_port | 36479
backend_start | 2023-02-28 13:34:34.340028+08
backend_xmin |
state | streaming
sent_lsn | 0/6000148
write_lsn | 0/6000148
flush_lsn | 0/6000148
replay_lsn | 0/6000148
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2023-02-28 13:34:55.157283+08