免费资源部落

这篇文章主要讲解了“postgresql高可用集群的安装步骤”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“postgresql高可用集群的安装步骤”吧！

一、hosts and topology structure of pg cluster

1.host infos

cluster01_node01 192.168.0.108cluster01_node02 192.168.0.109cluster02_node03 192.168.0.110

2.topology structure

syncasync

primary(cls01_node01) -------》 standby01(cls01_node02) -------》standby02(cls01_node03)

二、安装配置

1.安装、初始化PG (cls01_node01,cls01_node02, cls01_node03)1).install and init

--- install pg packagesyum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpmyum install postgresql10-serveryum install -y postgresql10-contrib

--- init and auto boot/usr/pgsql-10/bin/postgresql-10-setup initdbsystemctl enable postgresql-10systemctl start postgresql-10

PGDATA:/var/lib/pgsql/10/data/

2).mkdir pg archives

mkdir /pg_archivechown postgres.postgres /pg_archive/chmod 700 /pg_archive/

2.primary创建复制用户与数据库配置(cls01_node01)

1).create replication user

user/password: repuser/repuser

[root@pg_master ~]# su - postgresLast login: Sun Apr 22 17:25:06 CST 2018 on pts/0-bash-4.2$ createuser -U postgres repuser -P -c 5 --replicationEnter password for new role: Enter it again: -bash-4.2$

设置超级用户密码-bash-4.2$ psql -h 127.0.0.1psql (10.3)Type "help" for help.postgres=# postgres=# alter user postgres with password '123456';

2).configuration file

a. postgresql.conf

#--------------------------------------------------------------------------------# CONNECTIONS AND AUTHENTICATION#--------------------------------------------------------------------------------# - Connection Settings -listen_addresses = '*'max_connections = 2000

--# - TCP Keepalives -tcp_keepalives_idle = 60tcp_keepalives_interval = 10tcp_keepalives_count = 6

#--------------------------------------------------------------------------------# RESOURCE USAGE (except WAL)#--------------------------------------------------------------------------------# - Memory -shared_buffers = 256MBmaintenance_work_mem = 64MB

--# - Kernel Resource Usage -shared_preload_libraries = 'pg_stat_statements'

#--------------------------------------------------------------------------------# WRITE AHEAD LOG#--------------------------------------------------------------------------------# - Settings -wal_level = logicalwal_log_hints = on

--# - Checkpoints -max_wal_size = 10GBcheckpoint_completion_target = 0.9

--# - Archiving -archive_mode = onarchive_command = 'test ! -f /pg_archive/%f && cp %p /pg_archive/%f'

#--------------------------------------------------------------------------------# REPLICATION#--------------------------------------------------------------------------------# - Sending Server(s) -wal_keep_segments = 5000

--# - Master Server -synchronous_standby_names = '*'

--# - Standby Servers -hot_standby_feedback = on

#--------------------------------------------------------------------------------# ERROR REPORTING AND LOGGING#--------------------------------------------------------------------------------# - When to Log -log_min_duration_statement = 1000

--# - What to Log -log_checkpoints = onlog_connections = onlog_disconnections = log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h 'log_lock_waits = onlog_temp_files = 0

#------------------------------------------------------------------------------AUTOVACUUM PARAMETERS#------------------------------------------------------------------------------log_autovacuum_min_duration = 0

b. pg_hba.conf

--# TYPE DATABASE USER ADDRESS METHOD--# IPv4 local connections:host all all 127.0.0.1/32 md5host all all 0.0.0.0/0 md5--# IPv6 local connections:host all all ::1/128 md5--# Allow replication connectionshost replication repuser 192.168.0.108/32 md5host replication repuser 192.168.0.109/32 md5host replication repuser 192.168.0.110/32 md5

3).restart database

--# systemctl restart postgresql-10

3.sync slave配置1).停止数据库[root@cls01_node02 ~]# systemctl stop postgresql-10

2).clear old pgdata dir[root@cls01_node02 ~]# su - postgresLast login: Sun Apr 22 18:39:24 CST 2018 on pts/0 -bash-4.2$ cd 10/data/-bash-4.2$ rm -rf *-bash-4.2$ lltotal 0-bash-4.2$ 3). make sync standby

注意：从primary获取数据库数据

-bash-4.2$ cd-bash-4.2$ pg_basebackup -h 192.168.0.108 -U repuser -p 5432 -D /var/lib/pgsql/10/data --wal-method=stream --checkpoint=fast --progress --verbose --write-recovery-conf > makeslave$(date +%Y%m%d).log 2>&1Password: -bash-4.2$ more make_slave_2018_04_22.log pg_basebackup: initiating base backup, waiting for checkpoint to completepg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 0/4000060 on timeline 1pg_basebackup: starting background WAL receiver24448/24448 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 0/4000130pg_basebackup: waiting for background process to finish streaming ...pg_basebackup: base backup completed-bash-4.2$

4).add sync flag(application_name) and trigger file (trigger_file)-bash-4.2$ cd /var/lib/pgsql/10/data/-bash-4.2$ vi recovery.conf standby_mode = 'on'primary_conninfo = 'application_name=sync_slave user=repuser password=repuser host=192.168.0.108 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'trigger_file = '/tmp/trigger_failover'-bash-4.2$

5).start sync slave-bash-4.2$ exitlogout[root@cls01_node02 ~]# systemctl start postgresql-10[root@cls01_node02 ~]#

4.async slave 配置1).停止数据库[root@cls01_node03 ~]# systemctl stop postgresql-10

2).清空pgdata目录[root@cls01_node03 ~]# su - postgres -bash-4.2$ cd /var/lib/pgsql/10/data/-bash-4.2$ rm -rf *-bash-4.2$ lltotal 0-bash-4.2$

3).make slave

注意：从sync standby获取数据库数据

-bash-4.2$ cd-bash-4.2$ pg_basebackup -h 192.168.0.109 -U repuser -p 5432 -D /var/lib/pgsql/10/data --wal-method=stream --checkpoint=fast --progress --verbose --write-recovery-conf > makeslave$(date +%Y%m%d).log 2>&1Password: -bash-4.2$ lltotal 4drwx------ 4 postgres postgres 51 Apr 22 17:17 10-rw-r--r-- 1 postgres postgres 690 Apr 22 19:23 make_slave_2018_04_22.log-bash-4.2$ more make_slave_2018_04_22.log pg_basebackup: initiating base backup, waiting for checkpoint to completepg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 0/6000028 on timeline 1pg_basebackup: starting background WAL receiver32173/32173 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 0/7000060pg_basebackup: waiting for background process to finish streaming ...pg_basebackup: base backup completed-bash-4.2$

4).add wal switch flag(recovery_target_timeline='latest')

-bash-4.2$ cd /var/lib/pgsql/10/data/-bash-4.2$ vi recovery.conf standby_mode = 'on'primary_conninfo = 'user=repuser password=repuser host=192.168.0.109 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'recovery_target_timeline='latest'-bash-4.2$

5).start async standby

-bash-4.2$ exitlogout[root@cls01_node03 ~]# [root@cls01_node03 ~]# systemctl start postgresql-10[root@cls01_node03 ~]#

5.检查replication状态

1).primary

[root@cls01_node01 ~]# su - postgresLast login: Sun Apr 22 19:16:19 CST 2018 on pts/0

-bash-4.2$ -bash-4.2$ psql -h 127.0.0.1Password: psql (10.3)Type "help" for help.

postgres=# \xExpanded display is on.postgres=# select * from pg_stat_replication ;-[ RECORD 1 ]----+------------------------------pid | 9341usesysid | 16384usename | repuserapplication_name | sync_slaveclient_addr | 192.168.0.109client_hostname | client_port | 34152backend_start | 2018-04-22 19:15:51.242261+08backend_xmin | 558state | streamingsent_lsn | 0/7000140write_lsn | 0/7000140flush_lsn | 0/7000140replay_lsn | 0/7000140write_lag | flush_lag | replay_lag | sync_priority | 1sync_state | sync

postgres=# create database tdb01;CREATE DATABASEpostgres=# \c tdb01You are now connected to database "tdb01" as user "postgres".tdb01=# create table t1(id serial,user_name varchar(20));CREATE TABLEtdb01=# insert into t1(user_name) values('mia');INSERT 0 1tdb01=# tdb01=# tdb01=# select * from t1;id | user_name ----+-----------1 | mia(1 row)

tdb01=# \q-bash-4.2$

2).sync standby

[root@cls01_node02 ~]# su - postgresLast login: Sun Apr 22 18:41:55 CST 2018 on pts/0-bash-4.2$ -bash-4.2$ psql -h 127.0.0.1Password: psql (10.3)Type "help" for help.

postgres=# \xExpanded display is on.postgres=# select * from pg_stat_replication ;-[ RECORD 1 ]----+------------------------------pid | 9086usesysid | 16384usename | repuserapplication_name | walreceiverclient_addr | 192.168.0.110client_hostname | client_port | 51408backend_start | 2018-04-22 19:29:17.659393+08backend_xmin | 563state | streamingsent_lsn | 0/7039290write_lsn | 0/7039290flush_lsn | 0/7039290replay_lsn | 0/7039290write_lag | flush_lag | replay_lag | sync_priority | 0sync_state | async

tdb01=# \xExpanded display is off.tdb01=# tdb01=# \lList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+-----------------------postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | tdb01 | 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/postgrestemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| | | | | postgres=CTc/postgres(4 rows)

tdb01=# select * from t1;id | user_name ----+-----------1 | mia(1 row)

tdb01=#

3).async standby

[root@cls01_node03 ~]# su - postgresLast login: Sun Apr 22 19:18:55 CST 2018 on pts/0-bash-4.2$ -bash-4.2$ psql -h 127.0.0.1Password: psql (10.3)Type "help" for help.

postgres=# \xExpanded display is on.postgres=# select * from pg_stat_replication ;(0 rows)

postgres=# \xExpanded display is off.postgres=# \lList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+-----------------------postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | tdb01 | 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/postgrestemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| | | | | postgres=CTc/postgres(4 rows)

postgres=# \c tdb01 You are now connected to database "tdb01" as user "postgres".tdb01=# select * from t1;id | user_name ----+-----------1 | mia(1 row)

tdb01=# \q-bash-4.2$

感谢各位的阅读，以上就是“postgresql高可用集群的安装步骤”的内容了，经过本文的学习后，相信大家对postgresql高可用集群的安装步骤这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！