SQL • 無限

2018年7月29日星期日

在 PostgreSQL 10 上安裝與設定 repmgr

目標設定：

在 PostgreSQL 10 設定 repmgr ！
在各節點上設定 repmgrd 服務！

快速操作流程

Primary Server 上的設定！

安裝 repmgr 10 軟體：
```
#yum install repmgr10
```

修改設定檔 postgresql.conf：

#su - postgres
$vim /var/lib/pgsql/10/data/postgresql.conf
max_wal_senders = 10
wal_level = 'replica'
hot_standby = on
archive_mode = on
archive_command = '/bin/true'

新增 repmgr 系統使用者：

$ createuser -s repmgr
$ createdb repmgr -O repmgr
$ psql
postgres=# ALTER USER repmgr SET search_path TO repmgr, "$user", public;
postgres=# \q

修改 pg_hba.conf 設定檔：

$ cd /var/lib/pgsql/10/data
$ vim pg_hba.conf
local   replication   repmgr                              trust
host    replication   repmgr      127.0.0.1/32            trust
host    replication   repmgr      192.168.1.0/24          trust

local   repmgr        repmgr                              trust
host    repmgr        repmgr      127.0.0.1/32            trust
host    repmgr        repmgr      192.168.1.0/24          trust

修改 /etc/hosts 設定：

# vim /etc/hosts
192.168.1.1 node1
192.168.1.2 node2

測試 repmgr 是否可連到 node1 ：(在 standby 上測試)

# su - postgres
$psql 'host=node1 user=repmgr dbname=repmgr connect_timeout=2'

修改 repmgr.conf 設定檔：

#vim /etc/repmgr/10/repmgr.conf
node_id=1
node_name='node1'
conninfo='host=node1 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/pgsql/10/data'
pg_bindir='/usr/pgsql-10/bin/'
ssh_options='-q -o ConnectTimeout=10'

註冊 node1 成為主要節點：

# su - postgres
$/usr/pgsql-10/bin/repmgr -f /etc/repmgr/10/repmgr.conf primary register

查詢註冊狀況：

$/usr/pgsql-10/bin/repmgr -f /etc/repmgr/10/repmgr.conf cluster show

Standby Server 上的設定！

安裝 repmgr 10 軟體：
```
#yum install repmgr10
```

修改 repmgr.conf 設定檔：

#vim /etc/repmgr/10/repmgr.conf
node_id=2
node_name='node2'
conninfo='host=node2 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/pgsql/10/data'
pg_bindir='/usr/pgsql-10/bin/'
ssh_options='-q -o ConnectTimeout=10'

測試 node2 是否可複製主機內容：

# su - postgres
$/usr/pgsql-10/bin/repmgr -h node1 -U repmgr -d repmgr -f /etc/repmgr/10/repmgr.conf standby clone --dry-run

測試後如果没問題，可直接複製 Primary 主機內容：

$/usr/pgsql-10/bin/repmgr -h node1 -U repmgr -d repmgr -f /etc/repmgr/10/repmgr.conf standby clone

修改 postgresql.conf 設定檔：

$vim /var/lib/pgsql/10/data/postgresql.conf
listen_addresses = '*'

啟動 PostgreSQL Server：

# systemctl restart postgresql-10.service

連進 Primary Server 進行狀況查詢：

# su - postgres
$psql -u repmgr -h node1 -d repmgr
repmgr=# SELECT * FROM pg_stat_replication;

註冊 node2 成為 Standby 節點：

# su - postgres
$/usr/pgsql-10/bin/repmgr -f /etc/repmgr/10/repmgr.conf primary register
$ /usr/pgsql-10/bin/repmgr -f /etc/repmgr/10/repmgr.conf cluster show

設定 repmgrd 機制

修改在各節點的設定檔 postgresql.conf 內容：

# su - postgres
$ vim /var/lib/pgsql/10/data/postgresql.conf
shared_preload_libraries = 'repmgr'

重新啟動 PostgreSQL 服務：

# systemctl restart postgresql-10.service

修改設定檔 regmpr.conf，啟動失效自動接管功能：

# vim /etc/repmgr/10/repmgr.conf
failover=automatic
promote_command='/usr/pgsql-10/bin/repmgr standby promote -f /etc/repmgr/10/repmgr.conf --log-to-file'
follow_command='/usr/pgsql-10/bin/repmgr standby follow -f /etc/repmgr/10/repmgr.conf --log-to-file --upstream-node-id=%n'
monitoring_history=yes
monitor_interval_secs=2

啟動 repmgr 服務：

# systemctl enable repmgr10.service
# systemctl start repmgr10.service

參考文獻：

https://repmgr.org/docs/4.0/quickstart-postgresql-configuration.html

2018年6月7日星期四

建立 PostgreSQL 10 的 replication 機制

目標設定：

在 PostgreSQL 10 架設 Replication 機制！

快速操作流程

Master Server 上的設定！

建立 Replication 專用帳號：

#su - postgres
$createuser --replication -P replica 
Enter password for new role: （請輸入兩次相同密碼！） 
Enter it again:

修改 PostgreSQL Server 相關設定：

$vim /var/lib/pgsql/10/data/postgresql.conf
(只修改下列設定：)
listen_addresses = '*'
wal_level = replica
synchronous_commit = on
archive_command = 'cp %p /var/lib/pgsql/10/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = '*'

設定 pg_hba.conf 檔：

$vim /var/lib/pgsql/10/data/pg_hba.conf
### 在檔尾追加下列設定 ###
# host replication [replication user] [allowed IP addresses] password
host    replication     replica          127.0.0.1/32            password
host    replication     replica          192.168.5.244/32            password
host    replication     replica          192.168.5.243/32            password

重新啟動 PostgreSQL Server：
```
$pg_ctl restart 
```

Slave Server 上的設定！

切換成 postgres 使用者：
```
#su - postgres
```

由 Master 備份資料到 Slave (需要 replica 的密碼)：

$rm -rf /var/lib/pgsql/10/data
$pg_basebackup -h 192.168.5.244 -U replica -D /var/lib/pgsql/10/data -P 
Password:

修改 PostgreSQL 相關設定：

$vim /var/lib/pgsql/10/data/postgresql.conf
(只修改下列設定，其餘設定保留)
hot_standby = on

從範例檔複製設定檔：

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

編修設定檔 /var/lib/pgsql/10/data/recovery.conf：

$vim /var/lib/pgsql/10/data/recovery.conf 
(只修改下列設定，其餘設定保留)
restore_command = 'scp 192.168.5.244:/var/lib/pgsql/10/archive/%f %p'
standby_mode = on
### 設定 Master Server 相關設定 ###
primary_conninfo = 'host=192.168.5.244 port=5432 user=replica password=a123456 application_name=slave01'

重新啟動 PostgreSQL Server：
```
$pg_ctl restart 
```

參考文獻：

https://tecadmin.net/install-postgresql-server-centos/

2018年6月6日星期三

在 CentOS 7 上安裝 PostgreSQL 10

目標設定：

將 PostgreSQL 10 安裝在 CentOS 7 作業系統平台上！

快速操作流程

建立 PostgreSQL 專用帳號與目錄：

# mkdir /var/lib/pgsql
# groupadd -g 26 postgres
# useradd -g postgres -u 26 postgres
# passwd postgres
# chown -R postgres.postgres /var/lib/pgsql

安裝 PostgreSQL 10 repository 安裝檔：

#rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

安裝 PostgreSQL 資料庫：

# yum install postgresql10-server postgresql10

初始化 PostgreSQL 10 資料庫：

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

啟動資料庫方式：

# systemctl start postgresql-10.service
# systemctl enable postgresql-10.service

登入資料庫並修改密碼：

# su - postgres -c "psql"
psql (10.4)
Type "help" for help.

postgres=#\password postgres
postgres=# \q

參考文獻：

https://tecadmin.net/install-postgresql-server-centos/

2017年2月6日星期一

PostgreSQL 表空間的使用

目標設定：

利用 PostgreSQL 表空間的功能，分散資料庫上的表格至不同目錄內！

快速操作流程

先轉成 postgres 使用者：
```
#su - postgres
```

利用 psql 指令，連入資料庫：

$psql
postgres=# \c btrecord
btrecord=#

建立表格空間，並且指定路徑：

btrecord=# create tablespace cool location '/homebackup';

將表格移至新建的表空間內：

btrecord=# alter table score_2015 set tablespace cool;

2017年2月5日星期日

PostgreSQL 表格分割法

目標設定：

利用 PostgreSQL 繼承功能，分割使用中的表格

快速操作流程

先轉成 postgres 使用者：
```
#su - postgres
```

利用 psql 指令，連入資料庫：

$psql
postgres=# \c btrecord
btrecord=#

複刻一個與原來表格相同的主表格：

btrecord=# CREATE TABLE Trecords (like Orecords including all);

查看已複刻的表格：
```
btrecord=#\d Trecords
```

建立一個繼承主表格的子表格：

btrecord=# CREATE TABLE score_2017 (like Trecords including all) INHERITS (Trecords);

設定子表格的約束，只同意某一年份的資料寫入：

btrecord=# ALTER TABLE score_2017 add constraint partition_check CHECK ("score_time" >= '2017-01-01'::date AND "score_time" < '2018-01-01'::date);

使用觸發程序來分類資料，寫入子表格內：

btrecord=# CREATE OR REPLACE FUNCTION score_record_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.'score_time'>= '2017-01-01'::date AND NEW.'score_time' < '2018-01-01'::date) THEN
INSERT INTO score_2017 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the score_record_trigger() function';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER score_record_trigger
BEFORE INSERT ON Trecords
FOR EACH ROW EXECUTE PROCEDURE score_record_trigger();

將原來的資料表內容，寫入主表格：

btrecord=#INSERT INTO Trecords (select * from Orecords);

若要刪除子表格，可直接刪除，不影響主表格：
```
btrecord=#DROP TABLE Trecords; 
```
若要刪除主表格，不可直接刪除！但追加 CASCADE 則可將所有表格刪除：
```
btrecord=#DROP TABLE Trecords CASCADE; 
```

參考文獻：

https://www.depesz.com/2015/06/07/partitioning-what-why-how/

2016年7月22日星期五

PostgreSQL 監控程式資源

使用套件

POWA

使用原生程式

最佳化工具

PostgreSQL Wiki

部落客文章

https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/
http://www.butleranalytics.com/5-free-postgresql-dashboard-software/

官網記錄

https://www.postgresql.org/download/products/5-reporting-tools/

2016年7月18日星期一

PostgreSQL 備份與復原

目標設定：

PostgreSQL 的備份資料庫指令：pg_dump

pg_dump [資料庫名稱] -U [使用者帳號] -f [備份檔名稱.備份日期.sql]

PostgreSQL 的回復資料庫指令：psql

psql -f [備份檔名稱.備份日期.sql] [資料庫] [使用者名稱]

操作練習流程

先轉成 postgres 使用者：
```
# su - postgres
```

備份 student 資料庫：

$ pg_dump student -U student -f student_20160718.tar.gz

回復 student 資料庫：

$psql -f student_20160718.tar.gz student student

查看 student 資料庫：

$psql -U student
student=# \c student
student=# \d

訂閱：文章 (Atom)

2018年7月29日 星期日

在 PostgreSQL 10 上安裝與設定 repmgr

2018年6月7日 星期四

建立 PostgreSQL 10 的 replication 機制

2018年6月6日 星期三

在 CentOS 7 上安裝 PostgreSQL 10

2017年2月6日 星期一

PostgreSQL 表空間的使用

2017年2月5日 星期日

PostgreSQL 表格分割法

2016年7月22日 星期五

PostgreSQL 監控程式資源

2016年7月18日 星期一

PostgreSQL 備份與復原

2018年7月29日星期日

2018年6月7日星期四

2018年6月6日星期三

2017年2月6日星期一

2017年2月5日星期日

2016年7月22日星期五

2016年7月18日星期一