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

This article mainly explains the "installation steps of postgresql high availability cluster". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the installation steps of postgresql high availability cluster".

1. Hosts and topology structure of pg cluster

1.host infos

Cluster01_node01 192.168.0.108

Cluster01_node02 192.168.0.109

Cluster02_node03 192.168.0.110

2.topology structure

Sync async

Primary (cls01_node01)-"standby01 (cls01_node02) -" standby02 (cls01_node03)

II. Installation and configuration

1. Install and initialize PG (cls01_node01,cls01_node02, cls01_node03)

1) .install and init

-install pg packages

Yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

Yum install postgresql10-server

Yum install-y postgresql10-contrib

-init and auto boot

/ usr/pgsql-10/bin/postgresql-10-setup initdb

Systemctl enable postgresql-10

Systemctl start postgresql-10

PGDATA:

/ var/lib/pgsql/10/data/

2). Mkdir pg archives

Mkdir / pg_archive

Chown postgres.postgres / pg_archive/

Chmod 700 / pg_archive/

2.primary create replication user and database configuration (cls01_node01)

1) .create replication user

User/password: repuser/repuser

[root@pg_master ~] # su-postgres

Last login: Sun Apr 22 17:25:06 CST 2018 on pts/0

-bash-4.2$ createuser-U postgres repuser-P-c 5-- replication

Enter password for new role:

Enter it again:

-bash-4.2 $

Set the superuser password

-bash-4.2$ psql-h 127.0.0.1

Psql (10.3)

Type "help" for help.

Postgres=#

Postgres=# alter user postgres with password '123456'

2). Configuration file

A. Postgresql.conf

#-

-- # CONNECTIONS AND AUTHENTICATION

#-

-#-Connection Settings-

Listen_addresses ='*'

Max_connections = 2000

-#-TCP Keepalives-

Tcp_keepalives_idle = 60

Tcp_keepalives_interval = 10

Tcp_keepalives_count = 6

#-

-# RESOURCE USAGE (except WAL)

#-

-#-Memory-

Shared_buffers = 256MB

Maintenance_work_mem = 64MB

-#-Kernel Resource Usage-

Shared_preload_libraries = 'pg_stat_statements'

#-

-- # WRITE AHEAD LOG

#-

-#-Settings-

Wal_level = logical

Wal_log_hints = on

-#-Checkpoints-

Max_wal_size = 10GB

Checkpoint_completion_target = 0.9

-#-Archiving-

Archive_mode = on

Archive_command = 'test!-f / pg_archive/%f & & cp% p / pg_archive/%f'

#-

-- # REPLICATION

#-

-- #-Sending Server (s)-

Wal_keep_segments = 5000

-#-Master Server-

Synchronous_standby_names ='*'

-#-Standby Servers-

Hot_standby_feedback = on

#-

-- # ERROR REPORTING AND LOGGING

#-

-#-When to Log-

Log_min_duration_statement = 1000

-#-What to Log-

Log_checkpoints = on

Log_connections = on

Log_disconnections =

Log_line_prefix ='% t [% p]: [% lmer1] user=%u,db=%d,app=%a,client=%h'

Log_lock_waits = on

Log_temp_files = 0

#-

AUTOVACUUM PARAMETERS

#-

Log_autovacuum_min_duration = 0

B. Pg_hba.conf

-- # TYPE DATABASE USER ADDRESS METHOD

-- # IPv4 local connections:

Host all all 127.0.0.1/32 md5

Host all all 0.0.0.0/0 md5

-- # IPv6 local connections:

Host all all:: 1/128 md5

-- # Allow replication connections

Host replication repuser 192.168.0.108/32 md5

Host replication repuser 192.168.0.109/32 md5

Host replication repuser 192.168.0.110/32 md5

3) .restart database

-- # systemctl restart postgresql-10

3.sync slave configuration

1)。 Stop the database

[root@cls01_node02 ~] # systemctl stop postgresql-10

2). Clear old pgdata dir

[root@cls01_node02 ~] # su-postgres

Last login: Sun Apr 22 18:39:24 CST 2018 on pts/0

-bash-4.2$ cd 10/data/

-bash-4.2$ rm-rf *

-bash-4.2$ ll

Total 0

-bash-4.2 $

3)。 Make sync standby

Note: get database data from primary

-bash-4.2$ cd

-bash-4.2 $pg_basebackup-h 192.168.0.108-U repuser-p 5432-D / var/lib/pgsql/10/data-- wal-method=stream-checkpoint=fast-- progress-- verbose-- write-recovery-conf > makeslave$ (date +% Y%m%d). Log 2 > & 1

Password:

-bash-4.2$ more make_slave_2018_04_22.log

Pg_basebackup: initiating basebackup, waiting for checkpoint to complete

Pg_basebackup: checkpoint completed

Pg_basebackup: write-ahead log start point: 0/4000060 on timeline 1

Pg_basebackup: starting background WAL receiver

24448 kB (100%), 1 tablespace

Pg_basebackup: write-ahead log end point: 0/4000130

Pg_basebackup: waiting for background process to finish streaming...

Pg_basebackup: basebackup completed

-bash-4.2 $

4). Add sync flag (application_name) and trigger file (trigger_file)

-bash-4.2$ cd / var/lib/pgsql/10/data/

-bash-4.2$ vi recovery.conf

Standby_mode = 'on'

Primary_conninfo = 'application_name=sync_slave user=repuser password=repuser host=192.168.0.108 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'

Trigger_file ='/ tmp/trigger_failover'

-bash-4.2 $

5). Start sync slave

-bash-4.2$ exit

Logout

[root@cls01_node02 ~] # systemctl start postgresql-10

[root@cls01_node02 ~] #

4.async slave configuration

1)。 Stop the database

[root@cls01_node03 ~] # systemctl stop postgresql-10

2)。 Clear the pgdata directory

[root@cls01_node03 ~] # su-postgres

-bash-4.2$ cd / var/lib/pgsql/10/data/

-bash-4.2$ rm-rf *

-bash-4.2$ ll

Total 0

-bash-4.2 $

3) .make slave

Note: get database data from sync standby

-bash-4.2$ cd

-bash-4.2 $pg_basebackup-h 192.168.0.109-U repuser-p 5432-D / var/lib/pgsql/10/data-- wal-method=stream-- checkpoint=fast-- progress-- verbose-- write-recovery-conf > makeslave$ (date +% Y%m%d). Log 2 > & 1

Password:

-bash-4.2$ ll

Total 4

Drwx- 4 postgres postgres 51 Apr 22 17:17 10

-rw-r--r-- 1 postgres postgres 690 Apr 22 19:23 make_slave_2018_04_22.log

-bash-4.2$ more make_slave_2018_04_22.log

Pg_basebackup: initiating basebackup, waiting for checkpoint to complete

Pg_basebackup: checkpoint completed

Pg_basebackup: write-ahead log start point: 0/6000028 on timeline 1

Pg_basebackup: starting background WAL receiver

32173 kB, 1 tablespace

Pg_basebackup: write-ahead log end point: 0/7000060

Pg_basebackup: waiting for background process to finish streaming...

Pg_basebackup: basebackup completed

-bash-4.2 $

4). Add wal switch flag (recovery_target_timeline='latest')

-bash-4.2$ cd / var/lib/pgsql/10/data/

-bash-4.2$ vi recovery.conf

Standby_mode = 'on'

Primary_conninfo = 'user=repuser password=repuser host=192.168.0.109 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'

Recovery_target_timeline='latest'

-bash-4.2 $

5). Start async standby

-bash-4.2$ exit

Logout

[root@cls01_node03 ~] #

[root@cls01_node03 ~] # systemctl start postgresql-10

[root@cls01_node03 ~] #

5. Check replication status

1) .primary

[root@cls01_node01 ~] # su-postgres

Last login: Sun Apr 22 19:16:19 CST 2018 on pts/0

-bash-4.2 $

-bash-4.2$ psql-h 127.0.0.1

Password:

Psql (10.3)

Type "help" for help.

Postgres=#\ x

Expanded display is on.

Postgres=# select * from pg_stat_replication

-[RECORD 1]-+--

Pid | 9341

Usesysid | 16384

Usename | repuser

Application_name | sync_slave

Client_addr | 192.168.0.109

Client_hostname |

Client_port | 34152

Backend_start | 2018-04-22 1914 1514 51.2422611408

Backend_xmin | 558

State | streaming

Sent_lsn | 0x7000140

Write_lsn | 0x7000140

Flush_lsn | 0x7000140

Replay_lsn | 0x7000140

Write_lag |

Flush_lag |

Replay_lag |

Sync_priority | 1

Sync_state | sync

Postgres=# create database tdb01

CREATE DATABASE

Postgres=#\ c tdb01

You are now connected to database "tdb01" as user "postgres".

Tdb01=# create table T1 (id serial,user_name varchar (20))

CREATE TABLE

Tdb01=# insert into T1 (user_name) values ('mia')

INSERT 0 1

Tdb01=#

Tdb01=#

Tdb01=# select * from T1

Id | user_name

-- +-

1 | mia

(1 row)

Tdb01=#\ Q

-bash-4.2 $

2). Sync standby

[root@cls01_node02 ~] # su-postgres

Last login: Sun Apr 22 18:41:55 CST 2018 on pts/0

-bash-4.2 $

-bash-4.2$ psql-h 127.0.0.1

Password:

Psql (10.3)

Type "help" for help.

Postgres=#\ x

Expanded display is on.

Postgres=# select * from pg_stat_replication

-[RECORD 1]-+--

Pid | 9086

Usesysid | 16384

Usename | repuser

Application_name | walreceiver

Client_addr | 192.168.0.110

Client_hostname |

Client_port | 51408

Backend_start | 2018-04-22 19ore29VAND 17.6593931408

| backend_xmin | 563 |

State | streaming

Sent_lsn | 0thumb 7039290

Write_lsn | 0thumb 7039290

Flush_lsn | 0thumb 7039290

Replay_lsn | 0thumb 7039290

Write_lag |

Flush_lag |

Replay_lag |

Sync_priority | 0

Sync_state | async

Tdb01=#\ x

Expanded display is off.

Tdb01=#

Tdb01=#\ l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-+-

Postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

Tdb01 | 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 |

(4 rows)

Tdb01=# select * from T1

Id | user_name

-- +-

1 | mia

(1 row)

Tdb01=#

3) .async standby

[root@cls01_node03 ~] # su-postgres

Last login: Sun Apr 22 19:18:55 CST 2018 on pts/0

-bash-4.2 $

-bash-4.2$ psql-h 127.0.0.1

Password:

Psql (10.3)

Type "help" for help.

Postgres=#\ x

Expanded display is on.

Postgres=# select * from pg_stat_replication

(0 rows)

Postgres=#\ x

Expanded display is off.

Postgres=#\ l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-+-

Postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

Tdb01 | 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 |

(4 rows)

Postgres=#\ c tdb01

You are now connected to database "tdb01" as user "postgres".

Tdb01=# select * from T1

Id | user_name

-- +-

1 | mia

(1 row)

Tdb01=#\ Q

-bash-4.2 $

Thank you for reading, the above is the content of "installation steps of postgresql High availability Cluster". After the study of this article, I believe you have a deeper understanding of the installation steps of postgresql High availability Cluster, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.