马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
x
PostgreSQL 是一款功能强大的开源关系型数据库，以高扩展性、SQL 标准兼容性和丰富的高级功能著称。以下从架构、核心功能、应用场景到优化实践，全面剖析 PostgreSQL 的独特上风与利用细节：
一、架构与核心机制
- 多版本并发控制（MVCC）
- 实现原理：每个事件看到的数据快照（Snapshot）独立，通过 xmin 和 xmax 标记数据的可见性，避免读写锁竞争。
- 上风：高并发场景下读不壅闭写，写不壅闭读，适合 OLTP 系统。
- 清理机制：VACUUM 进程回收旧版本数据空间，AUTO_VACUUM 可配置自动清理。
- 存储引擎与表结构
- 堆表（Heap Table）：数据按行存储，通过 TID（行物理地址）快速定位。
- TOAST 机制：自动压缩大字段（如 TEXT、JSONB），超过 2KB 的数据存储到扩展存储区。
- 页面结构：默认 8KB 页巨细，包含页头、行指针（ItemId）和实际数据行。
- 进程模型
- 主进程（Postmaster）：管理连接请求，派生子进程处理查询。
- 子进程（Backend）：每个客户端连接对应一个子进程，独立内存上下文。
- 背景进程：包括 BgWriter（脏页刷盘）、Checkpointer（查抄点）、WalWriter（WAL 日记写入）等
二、流复制的核心机制
- WAL（Write-Ahead Logging）的作用
- 数据持久化基础：全部数据修改（增删改）必须先写入 WAL 文件，再写入数据文件。
- 复制基础：流复制的本质是主库将 WAL 记载及时传输到备库，备库重放这些记载以保持数据同等性。
- 流复制的核心组件
- 主库（Primary）：
- WAL Sender 进程：负责将 WAL 数据发送到备库。
- 备库（Standby）：
- WAL Receiver 进程：接收主库发送的 WAL 数据。
- Startup 进程：剖析并应用接收到的 WAL 记载到当地数据文件。
三、流复制的工作流程
- 主库写入数据
- 事件提交时天生 WAL 记载，写入当地 pg_wal 目录。
- WAL Sender 进程读取 WAL 文件，通过网络发送给备库。
- 备库接收并应用 WAL
- WAL Receiver 进程接收 WAL 数据，暂存到备库的 pg_wal 目录。
- Startup 进程按次序剖析 WAL 记载，重放（Replay）到备库的数据文件中。
- 同步与异步模式
- 异步复制（默认）：
- 主库提交事件后无需期待备库确认，性能高但存在数据丢失风险。
- 同步复制：
- 主库提交事件后需至少一个备库确认 WAL 写入（synchronous_commit = on）。
- 确保数据零丢失，但增长事件延迟。
四、部署
主：192.168.88.123
从：192.168.88.124
版本：9.2.23
master
- [root@master ~]# yum install postgresql-server -y
- [root@master ~]# id postgres
- uid=26(postgres) gid=26(postgres) groups=26(postgres)
- [root@master ~]# postgresql-setup initdb
- Initializing database ... OK
- [root@master ~]# su - postgres
- -bash-4.2$ exit
- logout
- [root@master ~]# systemctl start postgresql
- [root@master ~]# systemctl status postgresql
- ● postgresql.service - PostgreSQL database server
- Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
- Active: active (running) since Thu 2023-08-31 15:09:13 CST; 6s ago
- Process: 15849 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
- Process: 15844 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
- Main PID: 15852 (postgres)
- CGroup: /system.slice/postgresql.service
- ├─15852 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
- ├─15853 postgres: logger process
- ├─15855 postgres: checkpointer process
- ├─15856 postgres: writer process
- ├─15857 postgres: wal writer process
- ├─15858 postgres: autovacuum launcher process
- └─15859 postgres: stats collector process
- Aug 31 15:09:12 master systemd[1]: Starting PostgreSQL database server...
- Aug 31 15:09:12 master pg_ctl[15849]: LOG: could not bind IPv6 socket: Cannot assign requested address
- Aug 31 15:09:12 master pg_ctl[15849]: HINT: Is another postmaster already running on port 5432? If not, wait ...retry.
- Aug 31 15:09:13 master systemd[1]: Started PostgreSQL database server.
- Hint: Some lines were ellipsized, use -l to show in full.
- [root@master ~]# su - postgres
- Last login: Thu Aug 31 15:08:39 CST 2023 on pts/0
- -bash-4.2$ psql
- psql (9.2.24)
- Type "help" for help.
- #设置postgres密码
- postgres=# ALTER USER postgres WITH PASSWORD '123456';
- ALTER ROLE
- #创建用户replica
- postgres=# create role replica login replication encrypted password '123456';
- CREATE ROLE
- postgres=# SELECT usename from pg_user;
- usename
- ----------
- postgres
- replica
- (2 rows)
- #查看权限
- postgres=# SELECT rolname from pg_roles;
- rolname
- ----------
- postgres
- replica
- (2 rows)
- postgres=# \q
- -bash-4.2$ exit
- logout
- #设置 replica 用户白名单
- [root@master ~]# diff -U0 /var/lib/pgsql/data/pg_hba.conf{,.2023-08-31}
- --- /var/lib/pgsql/data/pg_hba.conf 2023-08-31 15:14:04.793067629 +0800
- +++ /var/lib/pgsql/data/pg_hba.conf.2023-08-31 2023-08-31 15:12:20.773067629 +0800
- @@ -90,2 +89,0 @@
- -host all all 192.168.88.124/32 md5
- -host replication replica 192.168.88.124/32 md5
- #更改配置文件
- [root@master ~]# diff -U0 /var/lib/pgsql/data/postgresql.conf{,.2023-08-31}
- --- /var/lib/pgsql/data/postgresql.conf 2023-08-31 15:19:54.652898220 +0800
- +++ /var/lib/pgsql/data/postgresql.conf.2023-08-31 2023-08-31 15:16:49.376908465 +0800
- @@ -59 +59 @@
- -listen_addresses = '*' # what IP address(es) to listen on;
- +#listen_addresses = 'localhost' # what IP address(es) to listen on;
- @@ -165 +165 @@
- -wal_level = hot_standby # minimal, archive, or hot_standby
- +#wal_level = minimal # minimal, archive, or hot_standby
- @@ -168 +168 @@
- -synchronous_commit = on # synchronization level;
- +#synchronous_commit = on # synchronization level;
- @@ -212 +212 @@
- -max_wal_senders = 2 # max number of walsender processes
- +#max_wal_senders = 0 # max number of walsender processes
- #重启主节点进程
- [root@master ~]# systemctl restart postgresql
- [root@master ~]# systemctl status postgresql
- ● postgresql.service - PostgreSQL database server
- Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
- Active: active (running) since Thu 2023-08-31 15:21:59 CST; 17s ago
- Process: 16545 ExecStop=/usr/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
- Process: 16570 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
- Process: 16565 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
- Main PID: 16573 (postgres)
- CGroup: /system.slice/postgresql.service
- ├─16573 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
- ├─16574 postgres: logger process
- ├─16576 postgres: checkpointer process
- ├─16577 postgres: writer process
- ├─16578 postgres: wal writer process
- ├─16579 postgres: autovacuum launcher process
- └─16580 postgres: stats collector process
- Aug 31 15:21:58 master systemd[1]: Starting PostgreSQL database server...
- Aug 31 15:21:59 master systemd[1]: Started PostgreSQL database server.
- [root@master ~]# find / -name recovery.conf.sample
- /usr/share/pgsql/recovery.conf.sample
- [root@master ~]# cp /usr/share/pgsql/recovery.conf.sample /var/lib/pgsql/data/
slave
- [root@slave ~]# yum install postgresql-server -y
- #验证是否可以登录主库
- [root@slave ~]# psql -h 192.168.88.123 -p 5432 postgres replica
- Password for user replica:
- psql: FATAL: password authentication failed for user "replica"
- [root@slave ~]# psql -h 192.168.88.123 -p 5432 postgres replica
- Password for user replica:
- psql (9.2.24)
- Type "help" for help.
- postgres=>
- postgres=>
- postgres=> exit
- postgres-> \q
- #备库上执行对于主库的基础备份
- [root@slave ~]# pg_basebackup -D /var/lib/pgsql/data -h 192.168.88.123 -p 5432 -U replica -X stream -P
- Password:
- 20178/20178 kB (100%), 1/1 tablespace
-
- #确定从节点
- [root@slave data]# diff -U0 recovery.conf{,.sample}
- --- recovery.conf 2023-08-31 16:47:46.554902886 +0800
- +++ recovery.conf.sample 2023-08-31 16:43:09.641911034 +0800
- @@ -88 +88 @@
- -recovery_target_timeline = 'latest'
- +#recovery_target_timeline = 'latest'
- @@ -108 +108 @@
- -standby_mode = on
- +#standby_mode = off
- @@ -115 +115 @@
- -primary_conninfo = 'host=192.168.88.123 port=5432 user=replica password=123456' # e.g. 'host=localhost port=5432'
- +#primary_conninfo = '' # e.g. 'host=localhost port=5432'
- #开启热备
- [root@slave data]# cp pg_hba.conf.2023-08-31 pg_hba.conf
- [root@slave data]# cp postgresql.conf.2023-08-31 postgresql.conf
- [root@slave data]# diff -U0 postgresql.conf{,.2023-08-31}
- --- postgresql.conf 2023-08-31 16:32:36.968655754 +0800
- +++ postgresql.conf.2023-08-31 2023-08-31 15:50:01.314461009 +0800
- @@ -230 +230 @@
- -hot_standby = on # "on" allows queries during recovery
- +#hot_standby = off # "on" allows queries during recovery
- #启动从节点进程
- [root@slave ~]# systemctl start postgresql
查看进程
- #查看主节点sender进程
- [root@master data]# ps -ef |grep wal
- postgres 16578 16573 0 15:21 ? 00:00:00 postgres: wal writer process
- postgres 25332 16573 0 16:50 ? 00:00:00 postgres: wal sender process replica 192.168.88.124(10010) streaming 0/60000B8
- root 25348 20780 0 16:50 pts/1 00:00:00 grep --color=auto wal
- #查看从节点receiver进程
- [root@slave data]# ps -ef |grep wal
- postgres 26541 26535 0 16:50 ? 00:00:00 postgres: wal receiver process streaming 0/60000B8
- root 26552 15622 0 16:50 pts/0 00:00:00 grep --color=auto wal
- #主节点进程信息
- [root@master data]# ps -ef |grep postgres
- postgres 16573 1 0 15:21 ? 00:00:00 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
- postgres 16574 16573 0 15:21 ? 00:00:00 postgres: logger process
- postgres 16576 16573 0 15:21 ? 00:00:00 postgres: checkpointer process
- postgres 16577 16573 0 15:21 ? 00:00:00 postgres: writer process
- postgres 16578 16573 0 15:21 ? 00:00:00 postgres: wal writer process
- postgres 16579 16573 0 15:21 ? 00:00:00 postgres: autovacuum launcher process
- postgres 16580 16573 0 15:21 ? 00:00:00 postgres: stats collector process
- postgres 25332 16573 0 16:50 ? 00:00:00 postgres: wal sender process replica 192.168.88.124(10010) streaming 0/60000B8
- root 25458 20780 0 16:51 pts/1 00:00:00 grep --color=auto postgres
- #备节点进程信息
- [root@slave data]# ps -ef |grep postgres
- postgres 26535 1 0 16:50 ? 00:00:00 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
- postgres 26536 26535 0 16:50 ? 00:00:00 postgres: logger process
- postgres 26537 26535 0 16:50 ? 00:00:00 postgres: startup process recovering 000000010000000000000006
- postgres 26538 26535 0 16:50 ? 00:00:00 postgres: checkpointer process
- postgres 26539 26535 0 16:50 ? 00:00:00 postgres: writer process
- postgres 26540 26535 0 16:50 ? 00:00:00 postgres: stats collector process
- postgres 26541 26535 0 16:50 ? 00:00:00 postgres: wal receiver process streaming 0/60000B8
- root 26578 15622 0 16:51 pts/0 00:00:00 grep --color=auto postgres
- #数据库信息，主库里有从的数据
- postgres=# select * from pg_stat_replication;
- pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent
- _location | write_location | flush_location | replay_location | sync_priority | sync_state
- -------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+-----------+-----
- ----------+----------------+----------------+-----------------+---------------+------------
- 25332 | 16384 | replica | walreceiver | 192.168.88.124 | | 10010 | 2023-08-31 16:50:27.803408+08 | streaming | 0/60
- 000B8 | 0/60000B8 | 0/60000B8 | 0/60000B8 | 0 | async
- (1 row)
测试
- #测试同步，主库增加和删除数据库
- postgres=# create database new;
- CREATE DATABASE
- postgres=# drop database test;
- DROP DATABASE
- postgres=# \l
- List of databases
- Name | Owner | Encoding | Collate | Ctype | Access privileges
- -----------+----------+----------+-------------+-------------+-----------------------
- new | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
- template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
- | | | | | postgres=CTc/postgres
- template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
- | | | | | postgres=CTc/postgres
- (4 rows)
- #从库跟着改变
- postgres-# \l
- List of databases
- Name | Owner | Encoding | Collate | Ctype | Access privileges
- -----------+----------+----------+-------------+-------------+-----------------------
- new | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
- template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
- | | | | | postgres=CTc/postgres
- template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
- | | | | | postgres=CTc/postgres
- (4 rows)
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
|
