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 defaults to 9.2
1. Install software and 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 the database, start the database service and set it to boot
# initdb-D / data/pg_data
Note:
-D is the specified data storage directory, which is stored in the / var/lib/pgsql/data directory by default, but production environments usually have 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 synchronous users
# su postgres
Bash-4.2$ psql
Postgres=# create role repuser login replication encrypted password 'password123'
Postgres=#\ Q
Note:
The user created here is repuser, and the password is password123, which you can configure as needed.
4. Modify configuration files pg_hba.conf and postgresql.conf
Bash-4.2$ vi / data/pg_data/pg_hba.conf
Host replication repuser 192.168.11.0/8 md5
Host all all 192.168.11.0/8 trust
Bash-4.2$ vi / data/pg_data/postgresql.conf
Add the following configuration, and the configuration file has the following configuration to be deleted (including those with the alarm number'#'in front)
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'
Bash-4.2$ mkdir / data/pg_data/pg_archive
Note:
The configuration archive_command here needs to be changed according to the actual configuration.
Reload to make the configuration effective
Bash-4.2 $pg_ctl-D / data/pg_data reload
From the server:
5. Synchronize data
Bash-4.2 $pg_basebackup-h 192.168.11.131-U repuser-D / data/pg_data-X stream-P
Password:
36413336413 kB, 1 tablespace
6. Modify configuration files recovery.conf and 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, and the configuration file has the following configuration to be deleted (including those with the alarm number'#'in front)
Standby_mode = on
Primary_conninfo = 'host=192.168.11.131 port=5432 user=repuser password=password123 keepalives_idle=60'
Recovery_target_timeline = 'latest
Note:
The user here is the user who synchronized the data created earlier, and the password is also the password of the response.
Bash-4.2$ vi / data/pg_data/postgresql.conf
Add the following configuration, and the configuration file has the following configuration to be deleted (including those with the alarm number'#'in front)
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 boot up
# chmod 700 / data/pg_data
# vi / etc/rc.d/rc.local
Su postgres-c'/ 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 database pgdata to pgone
GRANT
10. To set up the local login database, you need to enter a password, and to set up the client tool, you need to enter a password to login to the database.
10.1. If there is a need, you can set the local login database to enter a password.
Primary server
Modify the account login database password
# su postgres
Bash-4.2$ psql
Postgres=# alter user postgres with password 'password321'
ALTER ROLE
Modify the configuration file pg_hba.conf
Postgres=# vi / data/pg_data/pg_hba.conf
Local all all trust
Host all all 127.0.0.1/32 trust
> >
Local all all md5
Host all all 127.0.0.1/32 md5
Reload the configuration file
Bash-4.2 $pg_ctl-D / data/pg_data reload
From the server, you only need to modify the configuration file pg_hba.conf and make it effective.
10.2. To set up the remote login database, you need to enter a password for client tool connection.
Primary server
Modify the configuration file pg_hba.conf
Postgres=# vi / data/pg_data/pg_hba.con
Host all all 192.168.11.0/24 md5
You need to enter a password to log in to the server login database for the 192.168.11.0 network segment.
From the server, you only need to modify the configuration file pg_hba.conf and make it effective.
Reference:
Https://www.cnblogs.com/sunshine-long/p/9059695.html
