[ PostgreSQL Streaming Replication ]
■ summary
- postgresql log streaming replication
Security is enhanced and replication delay is reduced rather than the 8.2 version.
(The replication delay is the delay between the transfer of the log records to be replicated from the master node to the standby node,
The apply delay seems to be about the time it takes to replay the received WAL record and write it to disk.)
Two or more standby configurations are possible, but replication between standby nodes is not possible.
■ Configuration environments
1) Version : postgresql 9.x.x
2) Node : master 192.168.16.128 / standby 192.168.16.129
■ Procedures
1) Master / standby installation
2) initdb on the master node
3) Set postgresql.conf on the master node
4) Set pg_hba.conf on the master node
5) Create user 'repluser' the master node
6) Synchronize $PGDATA with master base-backup and standby nodes (except for pg_xlog)
7) Create recovery.conf on the slave node
8) replication test / failover test / replication monitoring
■ Step-by-step details
1. (master/standby) PostgreSQL install
1) Downloading and installing using yum
# yum -y install postgresql-server --downloadonly --downloaddir=./
Loaded plugins: product-id, search-disabled-repos, security, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql-server.x86_64 0:8.4.20-7.el6 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 8.4.20-7.el6 for package: postgresql-server-8.4.20-7.el6.x86_64
--> Processing Dependency: postgresql(x86-64) = 8.4.20-7.el6 for package: postgresql-server-8.4.20-7.el6.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-server-8.4.20-7.el6.x86_64
--> Running transaction check
---> Package postgresql.x86_64 0:8.4.20-7.el6 will be installed
---> Package postgresql-libs.x86_64 0:8.4.20-7.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================
Package Arch Version Repository Size
====================================================================================================
Installing:
postgresql-server x86_64 8.4.20-7.el6 base 3.4 M
Installing for dependencies:
postgresql x86_64 8.4.20-7.el6 base 2.6 M
postgresql-libs x86_64 8.4.20-7.el6 base 202 k
Transaction Summary
====================================================================================================
Install 3 Package(s)
Total download size: 6.2 M
Installed size: 29 M
Downloading Packages:
(1/3): postgresql-8.4.20-7.el6.x86_64.rpm | 2.6 MB 00:00
(2/3): postgresql-libs-8.4.20-7.el6.x86_64.rpm | 202 kB 00:00
(3/3): postgresql-server-8.4.20-7.el6.x86_64.rpm | 3.4 MB 00:00
------------------------------------------------------------------------------------------------------
Total 7.1 MB/s | 6.2 MB 00:00
exiting because --downloadonly specified
2) Account verification
When installed postgrepsql rpm, creates a "postgres" account.
# cat /etc/passwd | grep postgres
(*) Afterwards all work will be done with the "postgres" account.
3) Specify database path
# vi ~postgres/.bash_profile
PGDATA=/var/lib/pgsql/data
export PGDATA
2. (master) initdb run
The command to create the default database. (master only)
# initdb
3. (master) postgresql start
# pg_ctl start
# ps -ef | grep postgres
4. (master) Create custom database
# psql -> create database mydb; select * from pg_database;
5. (master) Create Replication User
# psql -> CREATE ROLE repluser WITH REPLICATION PASSWORD 'password' LOGIN
6. (master) postgresql.conf
# vi PGDATA/postgresql.conf
==>
listen_addresses = '*'
wal_level = 'hot_standby'
max_wal_senders = 3
wal_keep_segments = 64
hot_standby = on
7. (master) pg_hba.conf : To grant access to replicate DB users so that they can access from the standby node to the master node.
# vi $PGDATA/pg_hba.conf
==>
# repluser not allow to connect to database
local all repluser reject
host all repluser 0.0.0.0/0 reject
# localhost replication test
local replication repluser trust
# Master IP
host replication repluser 192.168.16.128/32 trust
# Standby IP
host replication repluser 192.168.16.129/32 trust
8. (master) postgresql restart
# pg_ctl restart
# ps -ef | grep postgres
9. (standby)
2-1) It is to back up the master node and import the necessary files to the standby node. The easiest way is to use the pg_basebackup command.
(The directory /var/lib/pgsql/data should be empty)
# (예) pg_basebackup -h MASTER -D /var/lib/pgsql/9.x/data -U repluser -v -P --xlog-method=stream
# pg_basebackup -h 192.168.16.128 -U repluser -D /var/lib/pgsql/data
10. (standby) recovery.conf
# vi $PGDATA/recovery.conf
=>
standby_mode = 'on'
primary_conninfo = 'host=192.168.16.128 user=repluser password=password'
trigger_file = '/tmp/postgresql.trigger.5432' ## failover trigger
recovery_target_timeline = 'latest'
- Place this recovery.conf file on one of the master node side and change the master node address in the primary_conninfo setting.
(It is a good idea to preview this file later when you want to use it immediately, without rebuilding the production server as a standby server.)
11. (standby) postgresql start
# pg_ctl start
# ps -ef | grep postgres
12. (master/standby) monitoring
# ps -ef | grep sender (master)
# ps -ef | grep receiver (standby)
13. replication test / failover test / replication monitoring
# psql
select * from pg_stat_replication;
1) (standby) failover test (recovery.conf -> recovery.done changed)
# touch /tmp/postgresql.trigger.5432
# ls -l recovery.*
-rw-r--r-- 1 postgres dba 136 27 Aug 02:33 recovery.done
No comments:
Post a Comment