Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.