postgresql repmgr setup

前端之家收集整理的这篇文章主要介绍了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$

原文链接:https://www.f2er.com/postgresql/193649.html

猜你在找的Postgre SQL相关文章