Use pg_basebackup to build a PostgreSQL stream replication Environment

Use pg_basebackup to build a PostgreSQL stream replication Environment

This article introduces the high availability of PostgreSQL. Here we will first conduct a stream replication experiment.

Environment:
OS:

[Ha @ node0 ~] $ Uname-
Linux node0 2.6.32-358. el6.x86 _ 64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
 
Memory: 1 GB
CPU: 1 core
Database:

S = # select version ();
Version

--------------------------------------------------------------------------------
-------------------------------
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120
313 (Red Hat 4.4.7-16), 64-bit
(1 row)
 
Postgres = #
 
. Bash_profile

[Ha @ node0 ~] $ Cat. bash_profile
#. Bash_profile
 
# Get the aliases and functions
If [-f ~ /. Bashrc]; then
.~ /. Bashrc
Fi
 
# User specific environment and startup programs
 
Export PGHOME =/home/ha/pgdb/
Export PGDATA =/home/ha/pgdb/data/
Export LD_LIBRARY_PATH = $ LD_LIBRARY_PATH: $ PGHOME/lib/
Export PATH = $ PGHOME/bin: $ PATH: $ HOME/bin
 

Construction Process:
1. Master Database Configuration
A. postgresql. conf:

Listen_addresses = '*'
Wal_level = hot_standby
Archive_mode = off
Max_wal_senders = 3
Wal_keep_segments = 16
 
The archive_mode parameter is described here. During the experiment, I found that the archive_mode and archive_command parameters can also complete stream replication without being set. Therefore, I have investigated this issue, I found that francs has explained this. Here I reference francs's conclusion:
 
"When building a stream replication environment, you do not have to set the archive_mode parameter to on. Many documents describe how to set this parameter to on when building a stream replication environment, the reason may be that WAL archive is more secure, because when the master database is down and cannot be recovered for a long time, the WAL in the archive directory can still be read from the slave database, so as not to lose data; on the other hand, if the master database is configured with a large wal_keep_segments, you do not need to enable archive_mode because the master database retains enough WAL, this greatly reduces the need to retrieve WAL from the archive because the WAL required by the slave database is overwritten by the master database. Therefore, setting the archive_mode parameter is not directly related to setting up stream replication. Tip: when creating a stream replication slave database for a busy database, we recommend that you set a large wal_keep_segments parameter for the master database. "

B. pg_mirror.conf

Host all 192.168.238.0/24 md5
Host replication rep 192.168.238.0/24 trust
 
It is noted that there are other methods on the Internet to set replication to md5 and create a. pgpass file. This method is also feasible.
C. Create a user rep

Create user rep replication encrypted password 'rep ';

2. Use pg_basebackup for slave Database Backup

[Ha @ localhost pgdb] $ pg_basebackup-D $ PGDATA-F p-X stream-v-P-h 192.168.238.130-U rep
Transaction log start point: 0/2000028 on timeline 1
Pg_basebackup: starting background WAL receiver
20945/20945 kB (100%), 1/1 tablespace
Transaction log end point: 0/20000F0
Pg_basebackup: waiting for background process to finish streaming...
Pg_basebackup: base backup completed
[Ha @ localhost pgdb] $ ll
Total 20
Drwxrwxr-x. 2 ha ha 4096 Dec 26 00:54 bin
Drwx ------. 18 ha ha 4096 Dec 26 data
Drwxrwxr-x. 4 ha ha 4096 Dec 26 00:54 include
Drwxrwxr-x. 4 ha ha 4096 Dec 26 00:54 lib
Drwxrwxr-x. 4 ha ha 4096 Dec 26 00:54 share
[Ha @ localhost pgdb] $ cd data/
[Ha @ localhost data] $ ls
Backup_label pg_mirror.conf pg_replslot pg_subtrans postgresql. auto. conf
Base pg_ident.conf pg_serial pg_tblspc postgresql. conf
Global pg_logical pg_snapshots pg_twophase
Pg_clog pg_multixact pg_stat PG_VERSION
Pg_dynshmem pg_notify pg_stat_tmp pg_xlog
 
Here we need to explain the Directory: The data directory can be automatically created through pg_basebackup. However, for other tablespaces, You need to manually create and grant permissions. Note that the permission is 700.

3. Slave Database Configuration
A. postgresql. conf

Hot_standby = on
 
B. recovery. conf

Standby_mode = on
Primary_conninfo = 'host = 192.168.238.130 port = 5432 user = rep'
Trigger_file = '/home/ha/pgdb/pg. trigger. file'
 
4. Before the slave database starts, the master database Process

[Ha @ localhost pgdb] $ ps-ef | grep post
Root 2124 1 0? 00:00:00/usr/libexec/postfix/master
Postfix 2147 2124 0? 00:00:00 qmgr-l-t fifo-u
Postfix 10385 2124 0? 00:00:00 pickup-l-t fifo-u
Ha 10691 1 0 :33 pts/3 00:00:00/home/ha/pgdb/bin/s_s_ora
Ha 10693 10691 0? 00:00:00 ipvs: checkpointer process
Ha 10694 10691 0? 00:00:00 ipvs: writer process
Ha 10695 10691 0? 00:00:00 ipvs: wal writer process
Ha 10696 10691 0? 00:00:00 postgres: autovacuum launcher process
Ha 10697 10691 0? 00:00:00 postgres: stats collector process
Ha 10717 4087 0 00:00:00 pts/3 grep post
 
5. After the slave database is started
A. master database Process

[Ha @ localhost pgdb] $ ps-ef | grep post
Root 2124 1 0? 00:00:00/usr/libexec/postfix/master
Postfix 2147 2124 0? 00:00:00 qmgr-l-t fifo-u
Postfix 10385 2124 0? 00:00:00 pickup-l-t fifo-u
Ha 10691 1 0 :33 pts/3 00:00:00/home/ha/pgdb/bin/s_s_ora
Ha 10693 10691 0? 00:00:00 ipvs: checkpointer process
Ha 10694 10691 0? 00:00:00 ipvs: writer process
Ha 10695 10691 0? 00:00:00 ipvs: wal writer process
Ha 10696 10691 0? 00:00:00 postgres: autovacuum launcher process
Ha 10697 10691 0? 00:00:00 postgres: stats collector process
Ha 10718 10691 0? 00:00:00 ipvs: wal sender process rep 192.168.238.131 (59195) streaming 0/3000060
Ha 10720 4087 0 00:00:00 pts/3 grep post
 
B. Slave database Process

[Ha @ localhost data] $ ps-ef | grep post
Root 2086 1 0? 00:00:00/usr/libexec/postfix/master
Postfix 2108 2086 0? 00:00:00 qmgr-l-t fifo-u
Postfix 9657 2086 0? 00:00:00 pickup-l-t fifo-u
Ha 9782 1 0 00:00:00 pts/2/home/ha/pgdb/bin/postgres
Ha 9783 9782 0? 00:00:00 postgres: startup process recovering 000000010000000000000003
Ha 9784 9782 0? 00:00:00 ipvs: checkpointer process
Ha 9785 9782 0? 00:00:00 ipvs: writer process
Ha 9786 9782 0? 00:00:00 postgres: stats collector process
Ha 9787 9782 0? 00:00:00 postgres: wal receiver process streaming 0/3000060
Ha 9792 3744 0 00:00:00 pts/2 grep post
 
6. Experiment results
A. master database

[Ha @ localhost pgdb] $ psql postgres
Psql (9.4.5)
Type "help" for help.
 
S = # create table test (id int );
CREATE TABLE
S = # insert into test values (1), (2 );
INSERT 0 2
S = # select * from test;
Id
----
1
2
(2 rows)
 
Postgres = #
 
B. Slave Database

[Ha @ localhost data] $ psql postgres
Psql (9.4.5)
Type "help" for help.
 
S = # select * from test;
Id
----
1
2
(2 rows)

------------------------------------ Lili split line ------------------------------------

Install the PostgreSQL 9.3.5 database in Ubuntu Server 14.04

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: