Production environment PostgreSQL Master-Slave environment configuration

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