postgresql repmgr setup

來源:互聯網
上載者:User

標籤:postgresql repmgr

一.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‘


[[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 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 [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.

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

[email protected]‘s password: 


Number of key(s) added: 1


Now try logging into the machine, with:   "ssh ‘[email protected]‘"

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


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

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

[email protected]‘s password: 


Number of key(s) added: 1


Now try logging into the machine, with:   "ssh ‘[email protected]‘"

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


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


[[email protected] ~]# systemctl start postgresql-10.service


register standby 

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


本文出自 “yiyi” 部落格,請務必保留此出處http://heyiyi.blog.51cto.com/205455/1962489

postgresql repmgr setup

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.