Master-Slave Server IP:
192.168.11.131 PostgreSQL Master
192.168.11.132 PostgreSQL Slave
Server System version:
# Cat/etc/redhat-release
Red Hat Enterprise Linux Server Release 7.2 (MAIPO)
PG Version:
System default 9.2
1. Install software, configure environment variables
Master-Slave Server:
# yum Install Postgresql-y
# yum Install postgresql-server-y
# Mkdir/data/pg_data
# chown Postgres:postgres/data/pg_data
# Vi/etc/profile
Export Pgdata=/data/pg_data
# Source/etc/profile
Primary server:
2. Initialize database, start database service and set to boot
# initdb-d/data/pg_data
Attention:
-D is the specified data storage directory, which is stored in the/var/lib/pgsql/data directory by default, but the production environment usually has separate data storage partitions.
# su Postgres
bash-4.2$ pg_ctl-d/data/pg_data Start
bash-4.2$ exit
# vi/etc/rc.d/rc.local
/usr/bin/postgres-d/data/pg_data
3. Create a Sync user
# su Postgres
bash-4.2$ Psql
postgres=# Create role Repuser login replication encrypted password ' password123 ';
postgres=# \q
Attention:
The user created here is Repuser, the password is password123 and you can configure it as needed.
4, modify the configuration file pg_hba.conf, postgresql.conf
bash-4.2$ vi/data/pg_data/pg_hba.conf
Host replication Repuser 192.168.11.0/8 MD5
Host All 192.168.11.0/8 Trust
bash-4.2$ vi/data/pg_data/postgresql.conf
Add the following configuration, the configuration file has the following configuration to delete (including the front siren ' # ')
listen_addresses = ' 192.168.11.131 '
Wal_level = Hot_standby
Max_wal_senders= 6
Wal_keep_segments = 10240
Max_connections = 512
Archive_mode = On
Archive_command = ' cp%p/data/pg_data/pg_archive/%f '
Attention:
The configuration Archive_command here needs to be changed based on the actual configuration.
Reboot makes configuration effective
bash-4.2$ pg_ctl-d/data/pg_data Restart
From the server:
5. Synchronizing Data
bash-4.2$ pg_basebackup-h 192.168.11.131-u repuser-d/data/pg_data-x stream-p
Password:
36413/36413 KB (100%), 1/1 tablespace
6, modify the configuration file recovery.conf, postgresql.conf
bash-4.2$ cp/usr/share/pgsql/recovery.conf.sample/data/pg_data/recovery.conf
bash-4.2$ vi/data/pg_data/recovery.conf
Add the following configuration, the configuration file has the following configuration to delete (including the front siren ' # ')
Standby_mode = On
Primary_conninfo = ' host=192.168.11.131 port=5432 user=repuser password=password123 keepalives_idle=60 '
Recovery_target_timeline = ' Latest
Attention:
The user here is the one that was previously created to synchronize the data, and the password is the password for the response.
bash-4.2$ vi/data/pg_data/postgresql.conf
Add the following configuration, the configuration file has the following configuration to delete (including the front siren ' # ')
listen_addresses = ' 192.168.11.132 '
Wal_level = Hot_standby
max_connections = 1000
Hot_standby = On
Max_standby_streaming_delay = 30s
Wal_receiver_status_interval = 10s
Hot_standby_feedback = On
bash-4.2$ exit
7. Start the service and set up boot
# chmod 700/data/pg_data
# vi/etc/rc.d/rc.local
/usr/bin/postgres-d/data/pg_data
# su Postgres
bash-4.2$ pg_ctl-d/data/pg_data Start
8. Verification
Primary server:
bash-4.2$ Psql
postgres=# select Client_addr,sync_state from Pg_stat_replication;
client_addr | Sync_state
--------------+------------
192.168.11.132 | Async
postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access Privileges
-----------+----------+----------+-------------+-------------+-----------------------
Postgres | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |
Template0 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 | =c/postgres +
| | | | | Postgres=ctc/postgres
template1 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 | =c/postgres +
| | | | | Postgres=ctc/postgres
Test | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |
(4 rows)
From the server:
# su Postgres
bash-4.2$ Psql
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access Privileges
-----------+----------+----------+-------------+-------------+-----------------------
Postgres | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |
Template0 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 | =c/postgres +
| | | | | Postgres=ctc/postgres
template1 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 | =c/postgres +
| | | | | Postgres=ctc/postgres
Test | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |
(4 rows)
9. Create a database, access users, and empower the database
Primary server
postgres=# Create user Pgone with password ' password321 ';
CREATE ROLE
postgres=# CREATE DATABASE pgdata owner Pgone;
CREATE DATABASE
postgres=# Grant all privileges on the database pgdata to Pgone;
GRANT
Reference:
Https://www.cnblogs.com/sunshine-long/p/9059695.html
Production environment PostgreSQL Master-Slave environment configuration