postgresql repmgr setup

一.soft requires(Master/Slave)

OS: CentOS Linux release 7.3.1611 (Core) X64

1.disabled SELINUX

# more /etc/selinux/config

# This file controls the state of SELinux on the system.

# SELINUX= can take one of these three values:

# enforcing - SELinux security policy is enforced.

# permissive - SELinux prints warnings instead of enforcing.

# disabled - No SELinux policy is loaded.

SELINUX=disabled

# SELINUXTYPE= can take one of three two values:

# targeted - Targeted processes are protected,

# minimum - Modification of targeted policy. Only selected processes are protected.

# mls - Multi Level Security protection.

SELINUXTYPE=targeted

2.disabled firewall

systemctl disable firewalld.service

3.install pg repository rpm

pgdg-centos10-10-1.noarch.rpm

https://yum.postgresql.org/repopackages.php

4.install repmgr repository rpm

yum install http://packages.2ndquadrant.com/repmgr/yum-repo-rpms/repmgr-rhel-1.0-1.noarch.rpm

5.install repmgr

# yum install -y repmgr10

# chown postgres.postgres -R /etc/repmgr/10/

# systemctl enable repmgr10.service

6.set postgres user pwd and pg path

### pgdata ###

# mkdir /pgdata10

# chown postgres.postgres /pgdata10/

### pg password ###

# passwd postgres

### pg PATH ###

# su - postgres

-bash-4.2$ vi .bash_profile

[ -f /etc/profile ] && source /etc/profile

PATH=/usr/pgsql-10/bin:$PATH

export PATH

PGDATA=/var/lib/pgsql/10/data

export PGDATA

# If you want to customize your settings,

# Use the file below. This is not overridden

# by the RPMS.

[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

~

### set pg boot ###

# systemctl enable postgresql-10

# vi /usr/lib/systemd/system/postgresql-10.service

# Location of database directory

Environment=PGDATA=/pgdata10/

二、hostname and ssh

1.set hostname

master

hostnamectl set-hostname 'pgdb1'

[root@pgdb1 ~]# more /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

172.16.3.226 pgdb1

172.16.3.228 pgdb2

slave

hostnamectl set-hostname 'pgdb2'

[root@pgdb2 ~]# more /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

172.16.3.226 pgdb1

172.16.3.228 pgdb2

[root@pgdb2 ~]#

2.ssh

master

[root@pgdb1 ~]# su - postgres

Last login: Mon Sep 4 10:32:25 CST 2017 on pts/0

-bash-4.2$

-bash-4.2$

-bash-4.2$ ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):

Created directory '/var/lib/pgsql/.ssh'.

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.

Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.

The key fingerprint is:

22:84:86:c4:2a:1e:d2:3b:9e:86:b6:1c:72:d5:d2:32 postgres@pgdb1

The key's randomart image is:

+--[ RSA 2048]----+

|.. |

|.o . |

|o.o . |

|+o.. o |

|+ ..E + S |

| .o. = . |

|.+.o |

|+o= |

|.+. |

+-----------------+

-bash-4.2$

-bash-4.2$ ssh-copy-id postgres@pgdb2

The authenticity of host 'pgdb2 (172.16.3.228)' can't be established.

ECDSA key fingerprint is c1:b4:f8:21:7e:3f:81:e4:e9:e8:93:43:d5:8e:0f:0e.

Are you sure you want to continue connecting (yes/no)? yes

/bin/ssh-copy-id: INFO: attempting to log in with the new key(s),to filter out any that are already installed

/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

postgres@pgdb2's password:

Number of key(s) added: 1

Now try logging into the machine,with: "ssh 'postgres@pgdb2'"

and check to make sure that only the key(s) you wanted were added.

-bash-4.2$

-bash-4.2$

-bash-4.2$ ssh pgdb2 date

Mon Sep 4 10:55:40 CST 2017

-bash-4.2$

Slave

[root@pgdb2 ~]# su - postgres

Last login: Mon Sep 4 10:32:25 CST 2017 on pts/0

-bash-4.2$ ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.

Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.

The key fingerprint is:

38:9b:0b:38:9a:ef:ae:75:e5:74:a9:3d:cc:3c:53:05 postgres@pgdb2

The key's randomart image is:

+--[ RSA 2048]----+

| E |

| . |

| . |

| . . . |

| = S . |

| . + @ . |

| + o = O |

| + o . . + |

|+=+ . |

+-----------------+

-bash-4.2$

-bash-4.2$

-bash-4.2$ ssh-copy-id postgres@pgdb1

The authenticity of host 'pgdb1 (172.16.3.226)' can't be established.

ECDSA key fingerprint is c1:b4:f8:21:7e:3f:81:e4:e9:e8:93:43:d5:8e:0f:0e.

Are you sure you want to continue connecting (yes/no)? yes

/bin/ssh-copy-id: INFO: attempting to log in with the new key(s),to filter out any that are already installed

/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

postgres@pgdb1's password:

Number of key(s) added: 1

Now try logging into the machine,with: "ssh 'postgres@pgdb1'"

and check to make sure that only the key(s) you wanted were added.

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$ ssh pgdb1 date

Mon Sep 4 10:56:33 CST 2017

-bash-4.2$

三、setting repmgr

1.Master

1).init pgdata

[root@pgdb1 ~]# su - postgres

Last login: Mon Sep 4 11:02:33 CST 2017 on pts/0

-bash-4.2$

-bash-4.2$ initdb -D /pgdata10/

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /pgdata10 ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting dynamic shared memory implementation ... posix

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A,or

--auth-local and --auth-host,the next time you run initdb.

Success. You can now start the database server using:

pg_ctl -D /pgdata10/ -l logfile start

-bash-4.2$

2.Configure The Postgresql.Conf

-bash-4.2$ cd /pgdata10/

-bash-4.2$ vi postgresql.conf

#max_wal_senders = 10

wal_keep_segments = 5000

wal_level = logical

#full_page_writes = on

#max_replication_slots = 10

#hot_standby = on

# - Archiving -

archive_mode = on # enables archiving; off,on,or always

# (change requires restart)

archive_command = 'cd .' # command to use to archive a logfile segment

shared_preload_libraries = 'repmgr_funcs'

log_min_duration_statement = 1000

log_checkpoints = on

log_connections = on

log_disconnections = on

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,client=%h '

log_lock_waits = on # log lock waits >= deadlock_timeout

#log_statement = 'none' # none,ddl,mod,all

#log_replication_commands = off

log_temp_files = 0

log_autovacuum_min_duration = 0

3.pg_hba.conf

vi pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all trust

# IPv4 local connections:

host all all 127.0.0.1/32 trust

host repmgr repmgr 172.16.3.0/16 trust

host all all 0/0 md5

# IPv6 local connections:

host all all ::1/128 trust

# Allow replication connections from localhost,by a user with the

# replication privilege.

local replication all trust

local replication repmgr trust

host replication all 127.0.0.1/32 trust

host replication all ::1/128 trust

"pg_hba.conf" 97L,4791C written

### start pg boot ###

# systemctl start postgresql-10

4. Repmgr.Conf

master

# su - postgres

$ vi /etc/repmgr/10/repmgr.conf

cluster=pgcluster

node=1 # a unique integer

node_name=node1

conninfo='host=pgdb1 dbname=repmgr user=repmgr'

#use_replication_slots=0

#loglevel=NOTICE

#logfacility=STDERR

logfile='/var/log/repmgr/repmgr-10.log'

master_response_timeout=30

#reconnect_attempts=6

#reconnect_interval=10

#failover=manual

#priority=100

promote_command='/usr/pgsql-10/bin/repmgr standby promote -f /etc/repmgr/10/repmgr.conf'

follow_command='/usr/pgsql-10/bin/repmgr standby follow -f /etc/repmgr/10/repmgr.conf'

slave

cluster=pgcluster

node=2 # a unique integer

node_name=node2

conninfo='host=pgdb2 dbname=repmgr user=repmgr'

#use_replication_slots=0

#loglevel=NOTICE

#logfacility=STDERR

logfile='/var/log/repmgr/repmgr-10.log'

master_response_timeout=30

#reconnect_attempts=6

#reconnect_interval=10

#failover=manual

#priority=100

promote_command='/usr/pgsql-10/bin/repmgr standby promote -f /etc/repmgr/10/repmgr.conf'

follow_command='/usr/pgsql-10/bin/repmgr standby follow -f /etc/repmgr/10/repmgr.conf'

5.master

-bash-4.2$ createuser -s repmgr

-bash-4.2$ createdb repmgr -O repmgr

register master

-bash-4.2$ repmgr -f /etc/repmgr/10/repmgr.conf master register

NOTICE: master node correctly registered for cluster 'pgcluster' with id 1 (conninfo: host=pgdb1 dbname=repmgr user=repmgr)

-bash-4.2$

slave

-bash-4.2$ repmgr -f /etc/repmgr/10/repmgr.conf -h pgdb1 -d repmgr -U repmgr -D /pgdata10/ --force standby clone

NOTICE: destination directory '/pgdata10/' provided

NOTICE: starting backup (using pg_basebackup)...

HINT: this may take some time; consider using the -c/--fast-checkpoint option

NOTICE: standby clone (using pg_basebackup) complete

NOTICE: you can now start your PostgreSQL server

HINT: for example : pg_ctl -D /pgdata10/ start

HINT: After starting the server,you need to register this standby with "repmgr standby register"

-bash-4.2$

start server

[root@pgdb2 ~]# systemctl start postgresql-10.service

register standby

[root@pgdb2 pgdata10]# su - postgres

Last login: Mon Sep 4 11:51:11 CST 2017 on pts/1

-bash-4.2$

-bash-4.2$

-bash-4.2$ repmgr -f /etc/repmgr/10/repmgr.conf standby register

NOTICE: standby node correctly registered for cluster pgcluster with id 2 (conninfo: host=pgdb2 dbname=repmgr user=repmgr)

-bash-4.2$

master/slave

start repmgr10.service

# systemctl start repmgr10.service

master

su - postgres

-bash-4.2$ repmgr -f /etc/repmgr/10/repmgr.conf cluster show

Role | Name | Upstream | Connection String

----------+-------|----------|-------------------------------------

* master | node1 | | host=pgdb1 dbname=repmgr user=repmgr

standby | node2 | node1 | host=pgdb2 dbname=repmgr user=repmgr

-bash-4.2$

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!