How to realize Master-Slave Construction of POSTGRESQL10.3 RPM package 01/18 Update SLTechnology News&Howtos

How to realize Master-Slave Construction of POSTGRESQL10.3 RPM package

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly introduces the POSTGRESQL10.3 RPM package how to achieve master-slave building, the article introduces in great detail, has a certain reference value, interested friends must read it!

1. POSTGRESQL master-slave replication building

1. Install POSTGRESQL

PGSQL10.3

Download address:

Https://yum.postgresql.org/testing/10/redhat/rhel-6-x86_64/repoview/postgresqldbserver10.group.html

download

Postgresql10-PostgreSQL client programs and libraries

Postgresql10-contrib-Contributed source and binaries distributed with PostgreSQL

Postgresql10-libs-The shared libraries required for any PostgreSQL clients

Postgresql10-server-The programs needed to create and run a PostgreSQL server

Install using rpm-ivh in turn (both nodes are installed)

Installation sequence:

Rpm-ivh postgresql10-libs-10.3-1PGDG.rhel6.x86_64.rpm

Rpm-ivh postgresql10-10.3-1PGDG.rhel6.x86_64.rpm

Rpm-ivh postgresql10-server-10.3-1PGDG.rhel6.x86_64.rpm

Rpm-ivh postgresql10-contrib-10.3-1PGDG.rhel6.x86_64.rpm

2. Start the POSTGRESQL service

(1) start the first step

Service postgresql-10 initdb

(2) start the second step

Service postgresql-10 start

3. Modify monitoring

(1) modify the listening address

Vi / var/lib/pgsql/10/data/postgresql.conf

#-Connection Settings-

# here was originally # listen_addresses = 'localhost' changed to the following yellow font

Listen_addresses = '192.168.159.128'

(2) View and monitor

Just display the yellow characteristic font.

Netstat-anlp | grep post

Tcp 0 192.168.159.128 tcp 5432 0.0.0.0 * LISTEN 4784/postmaster

Udp 0 0:: 1:57424:: 1:57424 ESTABLISHED 4784/postmaster

Unix 2 [ACC] STREAM LISTENING 30353 4784/postmaster / var/run/postgresql/.s.PGSQL.5432

Unix 2 [ACC] STREAM LISTENING 30356 4784/postmaster / tmp/.s.PGSQL.5432

4. Master-slave building

(1) main database environment

Su-postgres

Psql

Create a user replica copied by the user

CREATE ROLE replica login replication encrypted password 'replica'

Modify the pg_hba.conf file to specify the replica login network (last add)

Vi / var/lib/pgsql/10/data/pg_hba.conf

Host replication replica 192.168.159.0/24 md5

Host all replica 192.168.159.0/24 trust

The main library configuration file modifies the following items, other items remain unchanged

Vi / var/lib/pgsql/10/data/postgresql.conf

Listen_addresses ='*'

Wal_level = hot_standby # hot standby mode

Max_wal_senders= 6 # can set up a maximum of several stream copy links, as many as there are from.

Wal_keep_segments = 10240 # important configuration

Wal_send_timeout = 60s

Max_connections = 512 # the max_connections of the slave library is larger than the master library

Archive_mode = on # allow archiving

Archive_command ='cp% p / url/path%f' # set according to the actual situation

(2) from the database environment

Empty the data folder directory of the library.

Rm-rf / var/lib/pgsql/10/data/*

Run on the repository

Pg_basebackup-F p-- progress-D / var/lib/pgsql/10/data/-h 192.168.159.128-p 5432-U replica-- password

Enter password replica

!!! Note that after the replication is completed, all the files in the data directory must be re-authorized in the standby library.

Chown-R postgres.postgres / var/lib/pgsql/10/data/

Create a recovery.cnf file

Cp / usr/pgsql-10/share/recovery.conf.sample / var/lib/pgsql/10/data/recovery.conf

Vi / var/lib/pgsql/10/data/recovery.conf

Standby_mode = on

Primary_conninfo = 'host=192.168.159.128 port=5432 user=replica password=replica'

Recovery_target_timeline = 'latest'

Trigger_file ='/ var/lib/pgsql/10/data/trigger.kenyon'

Vi / var/lib/pgsql/10/data/postgresql.conf

Listen_addresses ='*'

Wal_level = hot_standby

Max_connections = 1000 # generally, the maximum slave link is larger than the master link.

Hot_standby = on # indicates that this machine is not only used for data archiving, but also for query

Max_standby_streaming_delay = 30s

Wal_receiver_status_interval = 10s # how often do you report the slave status to the master?

Hot_standby_feedback = on # if there is incorrect data replication, whether to make an example to the master

Start the standby library

Service postgresql-10 start

If it cannot be started, do the following:

Scp / var/lib/pgsql/10/data/postmaster.opts 192.168.159.129:/var/lib/pgsql/10/data/

Chown-R postgres.postgres / var/lib/pgsql/10/data/

Chmod 700 data/

5. Verify the master-slave function

Main database query

Su-postgres

Psql

Postgres=# select client_addr,sync_state from pg_stat_replication

Client_addr | sync_state

-+-

192.168.159.129 | async

(1 row)

Create a table in the main library

Create table test2 (name varchar (3))

Querying the table from the library

\ d

The main library queries yellow fonts

[root@ha1 data] # ps aux | grep postgres

Postgres 4686 0.0 0.8 356584 15592? S 15:22 0:00 / usr/pgsql-10/bin/postmaster-D / var/lib/pgsql/10/data

Postgres 4688 0.0 0.0 209416 1572? Ss 15:22 0:00 postgres: logger process

Postgres 4690 0.0 0.1 356700 3744? Ss 15:22 0:00 postgres: checkpointer process

Postgres 4691 0.0 0.1 356584 3044? Ss 15:22 0:00 postgres: writer process

Postgres 4692 0.0 0.3 356584 5980? Ss 15:22 0:00 postgres: wal writer process

Postgres 4693 0.0 0.1 356992 2616? Ss 15:22 0:00 postgres: autovacuum launcher process

Postgres 4694 0.0 0.0 211516 1540? Ss 15:22 0:00 postgres: archiver process

Postgres 4695 0.0 0.0 211648 1784? Ss 15:22 0:02 postgres: stats collector process

Postgres 4696 0.0 0.1 356876 2272? Ss 15:22 0:00 postgres: bgworker: logical replication launcher

Postgres 4711 0.7 0.4 358576 8536? Ss 15:24 0:35 postgres: dbuser exampledb 192.168.159.1 (63172) idle

Postgres 4845 0.0 0.1 357384 3424? Ss 15:39 0:00 postgres: wal sender process replica 192.168.159.129 (46554) streaming 0/401B0A0

Root 5049 0.0 103256 840 pts/3 S + 16:42 0:00 grep postgres

Database query for yellow fonts

[root@ha2 data] # ps aux | grep postgres

Postgres 4827 0.0 2.2 398228 42844? S 15:39 0:00 / usr/pgsql-10/bin/postmaster-D / var/lib/pgsql/10/data

Postgres 4829 0.0 0.0 209416 1536? Ss 15:39 0:00 postgres: logger process

Postgres 4830 0.0 0.2 398324 3948? Ss 15:39 0:00 postgres: startup process recovering 000000010000000000000004

Postgres 4831 0.0 0.1 398228 3496? Ss 15:39 0:00 postgres: checkpointer process

Postgres 4832 0.0 0.1 398228 2772? Ss 15:39 0:00 postgres: writer process

Postgres 4833 0.0 0.0 211516 1604? Ss 15:39 0:00 postgres: stats collector process

Postgres 4834 0.1 0.1 405444 3688? Ss 15:39 0:06 postgres: wal receiver process streaming 0/401B0A0

Root 4912 0.0 103252 828 pts/3 S+ 16:42 0:00 grep postgres

6. POSTGRESQL related

(1) create a LINUX user

Useradd dbuser

Passwd dbuser

(2) create a database user

CREATE USER dbuser WITH PASSWORD 'password'

(3) create a database

CREATE DATABASE exampledb OWNER dbuser

(4) Grant authority

GRANT ALL PRIVILEGES ON DATABASE exampledb TO dbuser

(5) Connect to database exampledb as dbuser

Su-dbuser

Psql-d exampledb

(6) the mode of network login

Vi / var/lib/pgsql/10/data/pg_hba.conf (add the following at IPV4)

Host exampledb dbuser 192.168.159.0/24 trust

Toggle dbuser user login

Psql-h 192.168.159.128-U dbuser-p password-d exampledb-p 5432

(7) restart and stop commands

Service postgresql-10 restart

Service postgresql-10 stop

(8) important profile

Pg_hba.conf

Postgresql.conf

(9) installation directory and command directory of PG

To see if the process is started, you can find

Command directory / usr/pgsql-10/bin/

Installation directory / var/lib/pgsql/10/data

[root@ha1 ~] # ps-ef | grep post

Root 1732 1 0 02:25? 00:00:00 / usr/libexec/postfix/master

Postfix 1742 1732 0 02:25? 00:00:00 pickup-l-t fifo-u

Postfix 1743 1732 0 02:25? 00:00:00 qmgr-l-t fifo-u

Postgres 2322 1 1 02:38? 00:00:00 / usr/pgsql-10/bin/postmaster-D / var/lib/pgsql/10/data

Postgres 2324 2322 0 02:38? 00:00:00 postgres: logger process

Postgres 2329 2322 0 02:38? 00:00:00 postgres: checkpointer process

Postgres 2330 2322 0 02:38? 00:00:00 postgres: writer process

Postgres 2331 2322 0 02:38? 00:00:00 postgres: wal writer process

Postgres 2332 2322 0 02:38? 00:00:00 postgres: autovacuum launcher process

Postgres 2333 2322 0 02:38? 00:00:00 postgres: stats collector process

Postgres 2334 2322 0 02:38? 00:00:00 postgres: bgworker: logical replication launcher

Root 2336 2232 0 02:39 pts/0 00:00:00 grep post

7. Manual switching between active and standby

4. Manual active / standby switching

(1) create a slave recovery.conf file (operate 192.168.159.152 on the slave database)

Cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf

Configure the following parameters

Standby_mode = 'on'-- Mark PG as STANDBY SERVER

Primary_conninfo = 'host=192.168.159.151 port=1921 user=repuser'-- identifies the main library information

Trigger_file ='/ data/pg/data/trigger.kenyon'-- identifies the trigger file

(2) close the main library (operate 192.168.159.151 on the main library)

/ opt/postgresql-10.3/bin/pg_ctl-D / data/pg/data/-l logfile stop

(3) activate slave database to master database status (operate 192.168.159.152 on slave database)

To activate the slave library, you only need to create a file, according to the parameter trigger_file value of the slave recovery.conf configuration file.

Just create this trigger file. For example "touch / data/pg/data/trigger.kenyon"

Touch / data/pg/data/trigger.kenyon

After a while, it is found that the recovery.conf file has become recovery.done, indicating that the repository has been activated.

Check the logfile log and activate when the following message appears

2018-06-04 21 11V 01.137 PDT [12818] Log: trigger file found: / data/pg/data/trigger.kenyon

2018-06-04 21 0/C02A390 11V 01.148 PDT [12818] Log: redo is completed in 0/C02A390

2018-06-04 21 11V 01.172 PDT [12818] Log: selected new timeline ID:2

2018-06-04 21 1115 05.442 PDT [12818] Log: archive recovery completed

2018-06-04 21 11V 05.568 PDT [12817] Log: database system is ready to accept connections

(4) modify the configuration file of the original main library

Vi / data/pg/data/postgresql.conf

Max_connections = 1500

(5) activate the original master library and turn it into a slave library (execute 192.168.159.151 on the original master library)

Create a $PGDATA/recovery.conf file and configure the following parameters

Vi / data/pg/data/recovery.conf

Recovery_target_timeline = 'latest'

Standby_mode = 'on'-- Mark PG as STANDBY SERVER

Primary_conninfo = 'host=192.168.159.152 port=1921 user=repuser'-- identifies the main library information

Trigger_file ='/ data/pg/data/trigger.kenyon'-- identifies the trigger file

-- modify pg_hba.conf (now add 192.168.159.152 to the main library) to add the following configuration

Vi / data/pg/data/pg_hba.conf

Host replication repuser 192.168.159.151/32 md5

Start the original master library, which is now the slave library (192.168.159.151)

/ opt/postgresql-10.3/bin/pg_ctl-D / data/pg/data/-l logfile start

Check the current logfile log from the library to find the error message.

2018-06-05 00 PDT 08V 00.326 PDT [9729] details: at the end of WAL, arrive at timeline 1 and address 0/C02A400.

2018-06-05 00 PDT 08V 00.327 log: before the current recovery point 0/C02A630, the new point in time 2 broke away from the current database system point in time 1

2018-06-05 00 WAL 08V 05.322 PDT [9729] Log: restart WAL stream operation on timeline 1 at 0/C000000

2018-06-05 00 PDT 08V 05.327 Log: replication is terminated by the active server

2018-06-05 00 PDT 08V 05.327 PDT [9729] details: at the end of WAL, arrive at timeline 1 and address 0/C02A400.

2018-06-05 00 PDT 08V 05.329 Log: before the current recovery point 0/C02A630, the new time point 2 broke away from the current database system time point 1

2018-06-05 00 WAL 08 PDT [9729] Log: restart the WAL stream operation on timeline 1 at 0/C000000

2018-06-05 00 0815 PDT [9729] Log: replication is terminated by the active server

2018-06-05 00 PDT 08 PDT [9729] details: at the end of the WAL, arrive at timeline 1 and address 0/C02A400.

2018-06-05 00 PDT 08 PDT [9725] Log: before the current recovery point 0/C02A630, the new point in time 2 separated from the current database system point in time 1

In the current main library operation:

Scp / data/pg/data/pg_wal/00000002.history 192.168.159.151:/data/pg/data/pg_wal/4, manual master / slave handover