文章目录
- 文档用途
- 详细信息
文档用途
Highgo Database数据库清理流复制节点详细配置过程
详细信息
- 关闭数据库并备份
主库：
[highgo@localhost 4.1]$ cp -rp data data.bak_180122
[highgo@localhost 4.1]$ du -sh *
221G data
221G data.bak_180122
备库：
[highgo@localhost 4.1]$ cp -rp data data.bak_180122
[highgo@localhost 4.1]$ du -sh *
91G data
91G data.bak_180122
- 修改参数
243\236\237\238
cd $PGDATA
sed -i ‘$ s/on/off/g’ postgresql.auto.conf
cat postgresql.auto.conf | grep hot_standby_feedback
25.21\22\23\24
cd $PGDATA
sed -i "$ a hot_standby_feedback = ‘off’ " postgresql.auto.conf
cat postgresql.auto.conf | grep hot_standby_feedback
- 关闭流复制
wal_level = archive
max_wal_senders = ‘0’
hot_standby = ‘off’
- 清理数据库
[highgo@localhost base]$ du -sh * 查看每个数据库实际的大小
7.3M 1
7.3M 13345
7.5M 13350
39M 17042
11G 17652
78G 18657
8.0M 27487
12K pgsql_tmp
连接到主备数据库进行清理
\c highgo_alter_log
vacuum FULL;
清理过程中，可以查询当前锁住的事物
select locktype,
relation::regclass as re1,
virtualxid as vxid,
transactionid as xid,
virtualtransaction as vxid2,
pid,
mode,
granted
from pg_locks;
清理成功
[highgo@localhost base]$ du -sh *
7.5M 1
7.3M 13345
6.6M 13350
39M 17042
11G 17652
78G 18657
8.0M 27487
12K pgsql_tmp
数据库对应的OID：
[highgo@localhost base]$ oid2name
All databases:
Oid Database Name Tablespace -------------------------------------- 18657 highgo pg_default 27487 highgo_alter_log pg_default 17652 highgo_presure pg_default 17042 highgo_test pg_default 13350 highgo pg_default 13345 template0 pg_default template1 pg_default- 清理已经废弃的节点信息
alter system set synchronous_standby_names = ‘sync243,sync236,sync237,sync238, sync21, sync22, sync23, sync24’
select pg_reload_conf();
删除复制槽：
pg_drop_replication_slot(‘10.243.25.241’);
注意：如果不删除复制槽的话已经产生的“错误”WAL日志不会释放
highgo=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
pid | state | client_addr | sync_priority | sync_state
-------±----------±--------------±--------------±-----------
8340 | streaming | 10.243.25.23 | 7 | potential
8341 | streaming | 10.254.23.243 | 1 | sync
8342 | streaming | 10.243.25.24 | 8 | potential
26779 | streaming | 10.254.23.237 | 3 | potential
8344 | streaming | 10.243.25.21 | 5 | potential
8345 | streaming | 10.254.23.236 | 2 | potential
8346 | streaming | 10.243.25.22 | 6 | potential
8347 | streaming | 10.254.23.238 | 4 | potential
- 查看清理后的状态
[highgo@localhost pg_xlog]$ du -sh
514M .
[highgo@localhost pg_xlog]$ pwd
/data/highgo/4.1/data/pg_xlog
[highgo@localhost pg_xlog]$ ls -l|grep “^-”| wc -l
33
[highgo@localhost pg_xlog]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 50G 2.8G 48G 6% /
devtmpfs 16G 0 16G 0% /dev
tmpfs 16G 4.0K 16G 1% /dev/shm
tmpfs 16G 97M 16G 1% /run
tmpfs 16G 0 16G 0% /sys/fs/cgroup
/dev/sda1 497M 109M 389M 22% /boot
/dev/sdb1 2.0T 94G 1.8T 6% /data
/dev/mapper/centos-home 42G 35M 42G 1% /home
tmpfs 3.2G 0 3.2G 0% /run/user/5866
