[DB] postgreSQL-12 Streaming_Replication

PGpool

• https://www.pgpool.net/docs/42/en/html/example-kubernetes.html
• https://www.pgpool.net/docs/latest/en/html/configuring-pgpool.html

postgreSQL Streaming_Replication

• https://www.digitalocean.com/community/tutorials/how-to-set-up-physical-streaming-replication-with-postgresql-12-on-ubuntu-20-04

Master1

[root@worker01 ~]# cat /var/lib/pgsql/12/data/postgresql.conf |grep listen
listen_addresses = '10.97.90.20'        # what IP address(es) to listen on;

## 아래와 같이 하면 향후 좀 더 편함
listen_addresses = '*'        # what IP address(es) to listen on;

-

[root@worker01 ~]# su - postgres -c 'psql'
psql (12.5)
Type "help" for help.

postgres=# CREATE ROLE test WITH REPLICATION PASSWORD 'testpassword' LOGIN;
CREATE ROLE
postgres=# \q

[root@worker01 ~]# cat /var/lib/pgsql/12/data/pg_hba.conf |grep 21
host    replication     test    10.97.90.21/32   md5

## 아래와 같이 하면 모든 DB를 모든 계정이 md5 암호로 접근할 수 있음
host    all     all     0.0.0.0/0       md5

replicas

[root@worker02 data]# su - postgres -c 'psql'
psql (12.5)
Type "help" for help.

postgres=# SHOW data_directory;
     data_directory     
------------------------
 /var/lib/pgsql/12/data
(1 row)

[root@worker02 data]# rm -rf /var/lib/pgsql/12/data/*

[root@worker02 data]# ls -ld /var/lib/pgsql/12/data
drwx------. 2 postgres postgres 6  1월 25 03:05 /var/lib/pgsql/12/data
[root@worker02 data]# ls -l /var/lib/pgsql/12/data
합계 0

[root@worker02 pgsql]# sudo -u postgres pg_basebackup -h 10.97.90.20 -p 5432 -U test -D /var/lib/pgsql/12/data/ -Fp -Xs -R
암호: 
[root@worker02 pgsql]# ls -l /var/lib/pgsql/12/data/
합계 60
-rw-------. 1 postgres postgres     3  1월 25 03:09 PG_VERSION
-rw-------. 1 postgres postgres   224  1월 25 03:09 backup_label
drwx------. 5 postgres postgres    41  1월 25 03:09 base
-rw-------. 1 postgres postgres    30  1월 25 03:09 current_logfiles
drwx------. 2 postgres postgres  4096  1월 25 03:09 global
drwx------. 2 postgres postgres    32  1월 25 03:09 log
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_commit_ts
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_dynshmem
-rw-------. 1 postgres postgres  4569  1월 25 03:09 pg_hba.conf
-rw-------. 1 postgres postgres  1636  1월 25 03:09 pg_ident.conf
drwx------. 4 postgres postgres    68  1월 25 03:09 pg_logical
drwx------. 4 postgres postgres    36  1월 25 03:09 pg_multixact
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_notify
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_replslot
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_serial
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_snapshots
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_stat
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_stat_tmp
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_subtrans
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_tblspc
drwx------. 2 postgres postgres     6  1월 25 03:09 pg_twophase
drwx------. 3 postgres postgres    60  1월 25 03:09 pg_wal
drwx------. 2 postgres postgres    18  1월 25 03:09 pg_xact
-rw-------. 1 postgres postgres   263  1월 25 03:09 postgresql.auto.conf
-rw-------. 1 postgres postgres 26613  1월 25 03:09 postgresql.conf
-rw-------. 1 postgres postgres     0  1월 25 03:09 standby.signal

[root@worker02 12]# grep "listen" /var/lib/pgsql/12/data/postgresql.conf 
listen_addresses = '10.97.90.21'        # what IP address(es) to listen on;

[root@worker02 12]# systemctl restart postgresql-12

master

[root@worker01 ~]# su - postgres -c 'psql'
psql (12.5)
Type "help" for help.

# Before restart replica db server service
postgres=# SELECT client_addr, state FROM pg_stat_replication;
 client_addr | state 
-------------+-------
(0 rows)

# After restart replica db server service
postgres=# SELECT client_addr, state FROM pg_stat_replication;
 client_addr |   state   
-------------+-----------
 10.97.90.21 | streaming
(1 row)

postgres=# create database testdb;
CREATE DATABASE

replicas

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
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)