PostgreSQL repmgr Setup

I. 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 the 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 the values:

# targeted-targeted processes is protected,

# Minimum-modification of targeted policy. Only selected processes is 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 isn't 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 the database directory

environment=pgdata=/pgdata10/

Ii. hostname and SSH

1.set hostname

Master

Hostnamectl set-hostname ' pgdb1 '

[Email protected] ~]# 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 '

[Email protected] ~]# 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

[Email protected] ~]#

2.ssh

Master

[Email protected] ~]# Su-postgres

Last Login:mon Sep 4 10:32:25 CST 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 [email protected]

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 [email protected]

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.

Is you sure 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 is already installed

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

[email protected] ' s password:

Number of key (s) added:1

Now try logging to the machine with: "SSH" [email protected] ' "

And check to make sure that is only the key (s) 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

[Email protected] ~]# Su-postgres

Last Login:mon Sep 4 10:32:25 CST 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 [email protected]

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 [email protected]

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.

Is you sure 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 is already installed

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

[email protected] ' s password:

Number of key (s) added:1

Now try logging to the machine with: "SSH" [email protected] ' "

And check to make sure that is only the key (s) 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$

Third, setting repmgr

1.Master

1). Init pgdata

[Email protected] ~]# Su-postgres

Last Login:mon Sep 4 11:02:33 CST on pts/0

-bash-4.2$

-bash-4.2$ initdb-d/pgdata10/

The files belonging to this database system is owned by user "Postgres".

This user must also own the server process.

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

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

The default text search configuration is set to "中文版".

Data page checksums is 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 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 sockets connections only

Local all All trust

# IPV4 Local connections:

Host All 127.0.0.1/32 Trust

Host Repmgr Repmgr 172.16.3.0/16 Trust

Host All 0/0 MD5

# IPV6 Local connections:

Host 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-register this standby with "Repmgr Standby register"

-bash-4.2$

Start server

[Email protected] ~]# systemctl start Postgresql-10.service

Register standby

[Email protected] pgdata10]# Su-postgres

Last Login:mon Sep 4 11:51:11 CST 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$

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1962489

PostgreSQL repmgr Setup