Postgres 配置 1主多備 同步部屬

Postgres 配置 1 主 2 備庫設定, 在主庫新增資料會向兩台備庫資料庫同步資料

Postgres Version : 9.5

相關設定如下:

主 IP: 10.10.17.21

備1 IP: 10.10.17.22

備2 IP: 10.10.17.24

1.先在主庫新增同步帳號

postgres# CREATE ROLE replica login replication encrypted password 'replica';

2.新增同步資料夾

mkdir /data/pg_archive

3.在 10.10.17.21的 pg_hba.conf 新增 replica 用戶連線權限

host     all             all          10.10.17.22/32          trust  
host   replication      replica       10.10.17.22/32          md5   

host     all             all          10.10.17.24/32          trust   
host   replication      replica       10.10.17.24/32          md5   

4.修改 10.10.17.21 的 postgresql.conf

listen_addresses ='*'＃監聽所有IP
archive_mode = on ＃允許歸檔
archive_command ='cp %p /data/pg_archive/%f' ＃用該命令來歸檔日誌文件段
wal_level = hot_standby
max_wal_senders = 32 
wal_sender_timeout = 60s ＃設置流複製主機發送數據的超時時間
max_connections = 100

重啟 Postgres db

pg_ctl stop

pg_ctl start

5. 在備庫 10.10.17.22 連連看 正庫

psql -h 10.10.17.21 -U postgres

要是不行得先查看 防火牆及DB Port 有無開啟

6.配置 postgresql.conf

wal_level = hot_standby
max_connections = 1000 ＃ 這邊設置的連接數要比正庫的還要大
hot_standby = on ＃ 說明備庫資料庫也用於資料查詢
max_standby_streaming_delay = 30s # 數據備份的最大時間
wal_receiver_status_interval = 10s # 最常多久向正庫報告一次備庫的狀態
hot_standby_feedback = on # 
wal_keep_segments = 100 #主庫保留多少個 WAL 給備庫

7.配置資料夾及資料

su - postgres
rm -rf /data/pgsql/*   #先将data目录下的数据都清空
pg_basebackup -h 10.10.17.21 -U replica -D /data/pgsql/ -X stream -P  # 從正庫Copy資料到備庫
mkdir /data/pgsql/pg_archive

8.備庫資料庫 10.10.17.22 新增 recovery.conf 檔案

cp /usr/pgsql-9.5/share/recovery.conf.sample /data/pgsql/recovery.conf

並且修改recovery.conf資料

standby_mode = on    # 說明該節點是備庫
primary_conninfo = 'host=10.10.17.21 port=5432 user=replica password=replica'  # 正庫的IP及用戶
recovery_target_timeline = 'latest'

配置完重啟DB

pg_ctl stop

pg_ctl start

9.查詢正庫

select client_addr,sync_state from pg_stat_replication;

會出現以下畫面

postgres=# select client_addr,sync_state from pg_stat_replication;
 client_addr | sync_state 
-------------+------------
 10.10.17.22 | async

(1 筆資料列)

10. 接下來另外一台備庫 10.10.17.24 的資料庫設定 跟 22一樣 (從Step 6做到8)

設定完後查詢正庫就會出現 兩台備庫IP已跟正庫連線的訊息 

postgres=# select client_addr,sync_state from pg_stat_replication;

 client_addr | sync_state 

-------------+------------

 10.10.17.24 | async

 10.10.17.22 | async

(2 筆資料列)

測試一下,正庫insert資料到備庫兩台, 同步是否正常! 

CentOS 上安裝 Postgres 9.5

在 CentOS 上安裝 Postgres 9.5

1.新增RPM

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

這個是官方發布的 RPM LIST 

https://yum.postgresql.org/repopackages.php

2.安裝PostgreSQL 9.5

yum install postgresql95-server postgresql95-contrib

這時候會自動建立 Postgres 用戶

3.配置 Postgres 相關設定

sudo mkdir -p /data/pgsql
sudo chown postgres:postgres /data/pgsql
sudo su - postgres
cp /etc/skel/.bash* /var/lib/pgsql

設定PGDATE環境參數，把資料庫的相關檔案建立在我們所設定的資料夾中，编辑~/.basrc，加入下面内容：

export PGDATA=/data/pgsql
export PATH=/usr/pgsql-9.5/bin:$PATH

生效參數
source ~/.bashrc


4.初始化資料庫

[postgres@DEV bin]$ /usr/pgsql-9.5/bin/initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/pgsql ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /data/pgsql/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    ./pg_ctl -D /data/pgsql -l logfile start




5.啟動資料庫

[postgres@DEV bin]$ pg_ctl start
server starting
[postgres@DEV bin]$< 2017-05-16 18:53:04.745 +08 >LOG:  redirecting log output to logging collector process
< 2017-05-16 18:53:04.745 +08 >HINT:  Future log output will appear in directory "pg_log".




6.修改相關參數 postgresql.conf 

vi /data/pgsql/postgresql.conf 
修改下面參數

listen_addresses = '*'          

port = 5432                     

設定完後 上下一次DB

pg_ctl stop

pg_ctl start

7.變更資料庫最高使用者Postgres 密碼

[postgres@MNL_DEV pgsql]$ psql

psql (9.5.7)

Type "help" for help.




postgres=# 

postgres=#  ALTER USER postgres WITH PASSWORD 'qwe123';

ALTER ROLE



postgres=# 




安裝完成