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 p_w_picpath 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 p_w_picpath 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$