postgresql-14流复制部署⼿册
背景
部署⼀主⼀从的PG测试环境。
过程:
服务器两台准备好---->下载软件，上传⾄服务器---->rpm安装，部署（两台）------>配置流复制---->验证。主要步骤
1 服务器两台操作系统：
# more /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
2 下载/上传rpm包。
地址：www.educity/ucenter2/shipin/list.html
4个rpm包
```bash
postgresql-client libraries and client binaries
postgresql-server core database server
postgresql-contrib additional supplied modules
postgresql-devel libraries and headers for C language development
```bash
上传⾄服务器。
并修改⽤户资源限制
vi /etc/f 添加
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
vi /f 添加
fs.file-max = 65536
sysctl -p ⽣效
建⽤户
useradd -d /home/postgres -m -g root postgres
[root@afofa-dev-app ~]# more 12.sh
echo "Post1234%" | passwd postgres --stdin > /dev/null 2>&1
3 部署PG实例
注意，此处需要配置yum源，⽤于解决依赖的包。
使⽤root进⾏部署
rpm -ivh postgresql14-libs-14.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-14.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-contrib-14.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-server-14.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql14-contrib-14.1-1PGDG.rhel7.x86_64.rpm
yum install libpython3.6m.so.1.0
rpm -ivh postgresql14-contrib-14.1-1PGDG.rhel7.x86_64.rpm
部署完毕后，进⾏数据初始化（是不是于MySQL的部署很类似）
[root@afofa-dev-app pgsql-14]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK
将数据路径修改到合适的路径下（默认的可能在/下，这不太安全）
建⽬录/赋权限
[root@afofa-dev-app etc]# mkdir -p /ofa/postgres_data
[root@afofa-dev-app etc]# chown -R postgres:root /ofa/postgres_data
[root@afofa-dev-app etc]# ls -lld /ofa
drwxr-xr-x 3 root root 27 Dec 112:28 /ofa
[root@afofa-dev-app etc]# ls -lld /ofa/postgres_data
drwxr-xr-x 2 postgres root 6 Dec 112:28 /ofa/postgres_data
vi /var/lib/pgsql/14/f
data_directory='/ofa/postgres_data'
同步数据
rsync -av /var/lib/pgsql/14/data/ /ofa/postgres_data/
重启数据库
systemctl restart postgresql-14
优化参数
show shared_buffers；设置为物理内存的25%左右，shared_buffers是最有效的⽤于调优的参数
show wal_buffers; postgreSQL将其WAL（预写⽇志）记录写⼊缓冲区，然后将这些缓冲区刷新到磁盘。如果有⼤量并发连接的话，则设置为⼀个较⾼的值可以提供更好的性能。
show effective_cache_size 提供可⽤于磁盘⾼速缓存的内存量的估计值，更⾼的数值会使得索引扫描更可能被使⽤. 更低的数值会使得顺序扫描更可能被使⽤.默认4G
show work_mem；此配置⽤于复合排序。强烈建议在会话级别修改此参数值
show maintenance_work_mem；是⽤于维护任务的内存设置。默认值为64MB。设置较⼤的值对于VACUUM，RESTORE，CREATE INDEX，ADD FOREIGN KEY和ALTER TABLE等操作的性能提升效果显著。
show synchronous_commit；此参数的作⽤为在向客户端返回成功状态之前，强制提交等待WAL被写⼊磁盘。这是性能和可靠性之间的权衡。如果应⽤程序被设计为性能⽐可靠性更重要，那么关闭synchronous_commit。这意味着成功状态与保证写⼊磁盘之间会存在时间差。在服务器崩溃的情况下，即使客户端在提交时收到成功消息，数据也可能丢失。
show checkpoint_timeout;
show checkpoint_completion_target;
⽤户可以在需要时随时发出CHECKPOINT指令，或者通过PostgreSQL的参数checkpoint_timeout和checkpoint_completion_target来⾃动完成。
max_connections
允许客户端连接的最⼤数⽬
4 配置主从
修改监听地址
psql -c "ALTER SYSTEM SET listen_addresses TO '*';"
mysql下载libs包的网址查看配置⽂件位置
postgres=# select name, setting from pg_settings where category='File Locations' ;
name | setting
-------------------+----------------------------------------
config_file | /var/lib/pgsql/14/f
data_directory | /ofa/postgres_data
external_pid_file |
hba_file | /var/lib/pgsql/14/data/f
ident_file | /var/lib/pgsql/14/data/f
(5 rows)
改配置⽂件
more /var/lib/pgsql/14/f
listen_addresses='*'
创建复制⽤户
createuser --replication -P -e replicator
密码；5chHEGfC
复制⽤户添加到配置⽂件f中
vi /var/lib/pgsql/14/data/f
host replication replicator 10.50.110.1xx/24 md5
重启
重启
systemctl restart postgresql-14
配置从服务器
备份旧数据，删掉，然后备份主数据数据
cp -R /ofa/postgres_data/ /ofa/postgres_data.bak
cd /ofa/postgres_data
rm -rf *
使⽤pg_basebackup⼯具进⾏备份
pg_basebackup -h 10.110.115.108 -D /ofa/postgres_data -U replicator -P -v -R -X stream -C -S pgstandby1
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot "pgstandby1"
26940/26940 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming p to backup_manifest
pg_basebackup: base backup completed
当备份结束后，从数据库的data_directory已经有数据，并且⼀个standby.signal被创建。
[postgres@afofa-dev-app postgres_data]$ ls -lltrh standby.signal
-rw------- 1 postgres root 0 Dec 114:03 standby.signal
这表⽰⼀个从数据库运⾏在hot standby node[配置⽂件中默认参数hot_standby=on]
回到主数据库，通过查询视图进⾏验证
[postgres@afofa-dev-app ~]$ psql -c "SELECT * FROM pg_replication_slots;"
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | sa fe_wal_size | two_phase
------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+----------- pgstandby1 || physical ||| f | f ||||0/2000000 || reserved || f
(1 row)
启动从服务器
验证streaming replication
⼀旦主从复制链接上，就可以在从服务器上看到⼀个WAL receiver进程，⽤如下视图查看
[postgres@afofa-dev-app ~]$ psql -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"
Expanded display is on.
-[ RECORD 1]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid |47155
status | streaming
receive_start_lsn |0/3000000
receive_start_tli |1
written_lsn |0/3000148
flushed_lsn |0/3000148
received_tli |1
last_msg_send_time |2021-12-01 14:19:36.95701+08
last_msg_receipt_time |2021-12-01 14:19:36.957251+08
latest_end_lsn |0/3000148
latest_end_time |2021-12-01 14:18:06.815422+08
slot_name | pgstandby1
sender_host |10.110.115.108
sender_port |5432
conninfo |user=replicator password=******** channel_binding=prefer dbname=replication host=10.110.115.108 port=5432fallback_application_na me=walreceiver sslmode=prefer sslcompression=0sslsni=1ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_ attrs=any
在主库上有⼀个WAL sender进程，state是streaming， sync_state是async，如下：
[postgres@afofa-dev-app ~]$ psql -c "\x" -c "SELECT * FROM pg_stat_replication;"
Expanded display is on.
-[ RECORD 1]----+------------------------------
pid |48001
usesysid |16384
usename | replicator
application_name | walreceiver
client_addr |10.110.115.109
client_hostname |
client_port |54090
backend_start |2021-12-01 14:18:06.799575+08 backend_xmin |
state | streaming
sent_lsn |0/3000148
write_lsn |0/3000148
flush_lsn |0/3000148
replay_lsn |0/3000148
write_lag |
flush_lag |
replay_lag |
sync_priority |0
sync_state | async
reply_time |2021-12-01 14:21:47.241286+08 async表⽰异步复制，后⾯将会将如何调整为同步复制
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论