Cài đặt

các bài viết liên quan tới cài đặt

Toàn tập về pg_hint_plan

1. Pg_hint_plan là gì?

Pg_hint_plan là một công cụ dùng để quản lý , điều khiển các kế hoạch thực thi câu lệnh SQL bằng cách sử dụng định dạng '/*+' và '*/' này đặt trước câu lệnh SQL. Nhờ đó mà ta có thể quyết định câu lệnh SQL được thực thi với kế hoạch tối ưu nhằm cải thiện tốc độ xử lý của SQL.

2. Qúa trình thực hiện một câu lệnh SQL:

3. Cài đặt pg_hint_plan:

Các version pg_hint_plan bạn có thể tải tại đây https://github.com/ossc-db/pg_hint_plan/releases

tar xfz pg_hint_plan-REL12_1_3_7.tar.gz
 cd pg_hint_plan-REL12_1_3_7
make
make install

 
Cấu hình pg_hint_plan vào file config postgresql
$ vi $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_hint_plan'
Ngoài ra bạn có thêm các thuộc tính sau vào file config postgresql:
pg_hint_plan.enable_hint = on : bật hay tắt pg_hint_plan
pg_hint_plan.enable_hint_table = on : bật hay tắt pg_hint_pl``an cho bảng
pg_hint_plan.debug_print = on : bật hay tắt chế độ debug
`pg_hint_plan.message_level = log` bật hay tắt chế độ lưu log
 
`pg_ctl restart -D $PGDATA`
`test2=# create extension pg_hint_plan;`
`CREATE EXTENSION`

4. Testing và sử dụng pg_hint_plan

create table t1 (c1 int, c2 int, c3 int, dummy char(100));
create index t1_idx1 on t1 (c1, c2, c3);
create index t1_idx2 on t1 (c2, c3);
create index t1_idx3 on t1 (c3);
create index t1_idx4 on t1 (c1);

create table t2 (c1 int, c2 int, c3 int, dummy char(100));
create index t2_idx1 on t2 (c1, c2, c3);
create index t2_idx2 on t2 (c2, c3);
create index t2_idx3 on t2 (c3);

create table t3 (c1 int, c2 int, c3 int, dummy char(100));
create index t3_idx1 on t3 (c1, c2, c3);
create index t3_idx2 on t3 (c2, c3);
create index t3_idx3 on t3 (c3);
 
insert into t1 select 1, mod(c1,100), mod(c1,1000), 'dummy' from generate_series(1,100000) c1;
insert into t2 select 1, mod(c1,100), mod(c1,1000), 'dummy' from generate_series(1,10000) c1;
insert into t3 select 1, mod(c1,100), mod(c1,1000), 'dummy' from generate_series(1,100) c1;

/*+ IndexScan(t1 t1_idx1) */ explain analyze select * from t1 where c1=1 and c2=10 and c3=100

 
/*+ IndexScan(t1 t1_idx4) */ explain analyze select * from t1 where c1=1 and c2=10 and c3=100

 
/*+ Leading(c b a) NestLoop(c b) HashJoin(c b a) */ explain analyze select a.*, b.*, c.* from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c2=b.c2 and a.c3=b.c3 and b.c1=c.c1 and b.c2=c.c2 and b.c3=c.c3

 
/*+ Leading(a b c) HashJoin(a b) NestLoop(a b c) */ explain analyze select a.*, b.*, c.* from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c2=b.c2 and a.c3=b.c3 and b.c1=c.c1 and b.c2=c.c2 and b.c3=c.c3

 
INSERT INTO hint_plan.hints(norm_query_string, application_name, hints) VALUES ( 'explain analyze select * from t1 t where c1=?;', '', 'SeqScan(t)' );
select * from hint_plan.hints;
explain analyze select * from t1 t where c1=10;

Hoặc có thể đặt pg_hint_plan vào trong câu lệnh SQL

5. Tìm hiểu thêm về các phương pháp truy xuất dự liệu

Phương pháp scan Giải thích
SeqScan(table) Truy xuất theo thứ tự trên bảng
TidScan(table) Truy xuất TID(Tuple ID0 trên bảng
IndexScan(table[ index...]) Truy xuất dựa theo index
IndexOnlyScan(table[ index...]) Chỉ truy xuất dựa theo index mà không truy cập table
BitmapScan(table[ index...]) Chỉ truy xuất dựa theo Bitmap
IndexScanRegexp(table[ POSIX Regexp...]) Truy xuất chỉ mục dựa theo các mẫu Regrex
IndexOnlyScanRegexp(table[ POSIX Regexp...]) Truy xuất chỉ mục dựa theo các mẫu Regrex mà không truy cập bảng
BitmapScanRegexp(table[ POSIX Regexp...]) Truy xuất Bitmap dựa theo các mẫu Regrex chỉ định
NoSeqScan(table) Truy xuất không theo thứ tự
NoTidScan(table) Truy xuất không theo TID
NoIndexScan(table) Truy xuất không theo chỉ mục
NoIndexOnlyScan(table) Truy xuất không theo chỉ mục mà không cần truy cập
NoBitmapScan(table) Truy xuất không theo Bitmap

 

Phương pháp join Giải thích
NestLoop(table table[ table...]) Vòng lặp lồng nhau bắt buộc cho các liên kết bao gồm các bảng được chỉ định.
HashJoin(table table[ table...]) Tạo kết nối dựa theo bảng băm của các bảng được chỉ định.
MergeJoin(table table[ table...]) Hợp nhất các liên kết của các bảng chỉ định
NoNestLoop(table table[ table...]) Ngược lại với Nestloop
NoHashJoin(table table[ table...]) Ngược lại với Hashjoin
NoMergeJoin(table table[ table...]) Ngược lại với MergeJoin

 

Phương pháp khác Giải thích
Leading(table table[ table...]) Tạo liên kết theo tự các bảng chỉ định
Leading() Tạo liên kết theo thứ tự và hướng mặc định
Rows(table table[ table...] correction) Sửa số hàng của liên kết bao gồm các bảng đã xác định
Parallel(table [soft|hard]) Sử dụng hay ngăn chặn việc xử lý nhiều luồng song song
Set(GUC-param value) Thiết lập các giá trị GUC trong lúc thực hiện kế hoạch (planner)



Cảm ơn bạn đã đọc tôi!!!

Hướng dẫn cấu hình postgresql sử dụng như service trong systemd

Tạo file postgresql service trong sysytemd

$ sudo nano /etc/systemd/system/postgresql.service

Cập nhật nội dung dưới vào file postgresql.service file và lưu nó

(Chú ý kiểm tra /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data có đúng đường dẫn mà bạn đã cài đặt chưa nhé)
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network-online.target

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

Tiến hành enable service postgresql cho servers

$ sudo systemctl daemon-reload
$ sudo systemctl enable postgresql

Start postgresql và kiểm tra

$ sudo systemctl start postgresql
$ sudo systemctl status postgresql

Hướng dẫn cài đặt postgres 12 từ Source Code

Cài đặt các package hỗ trợ:

# yum install -y gcc make systemd-devel zlib-devel readline-devel

Tạo postgres user

# useradd -m -s /bin/bash postgres
# usermod -aG wheel postgres
# passwd postgres

Chuyển qua user postgres

# su postgres

Tiến hành Download source code PostgreSQL

wget https://ftp.postgresql.org/pub/source/v12.0/postgresql-12.0.tar.gz
(Hoặc bạn có thể download từ windows PC rồi dùng WinSCP copy lên server linux)

Tiến hành giải nén:

$ gunzip postgresql-12.1.tar.gz
$ tar xf postgresql-12.1.tar
$ cd postgresql-12.1

Thực hiện build

### Thực hiện cấu hình:
./configure --with-icu --with-openssl --with-systemd
Chú ý: --with-systemd : hỗ trợ build với systemd

Make a build

$ make

Kiểm tra trước khi cài đặt

$ make check

Tiến hành cài đặt

$ sudo make install

Cấu hình biến môi trường cho postgres server:

$ DATA_DIR=/usr/local/pgsql/data
$ sudo mkdir $DATA_DIR
$ sudo chown postgres $DATA_DIR
$ echo 'export PATH=$PATH:/usr/local/pgsql/bin' > /etc/profile.d/postgres.sh

Khởi tạo database cluster

$ initdb --encoding=UTF8 --locale=C -D $DATA_DIR


Như vậy bạn đã cài xong postgres 12 cho server centos linux
Bài viết sau chúng tôi sẽ hướng dẫn bạn cấu hình master-slave server cho postgres 12 nhé

Cấu hình PostgreSQL cluster với pacemaker và DRBD

HA (high availability) là một cấu trúc hệ thống không thể thiếu trong vận hành một database system. Bài viết này sẽ giới thiệu cách cấu hình một PostgreSQL cluster sử dụng pacemaker và DRDB.

Pacemaker

Pacemaker là một phần mềm mã nguồn mở dùng để quản lý HA cluster được sử dụng rất phổ biến trong các hệ thống hạ tầng IT.

Đặc điểm của pacemaker

Pacemaker được cấu thành từ 2 bộ phận chính corosync và pacemaker. Corosync thực hiện giám sát, giao tiếp giữa các node trong cluster, trong khi pacemaker quản lý điều khiển các hoạt động của resource agents trong cluster. Resource agents là những agent được viết dưới dạng script (bash, perl, ..) xử lý giám sát các resource (như volume, VIP, filesystem,..) và được điều khiển bởi pacemaker.

Về tổng quan, pacemaker nổi bật với những tính năng bên dưới.

  • Giám sát hệ thống và tự động phục hồi service khi có lỗi
  • Hỗ trợ nhiều servers trong cùng một cluster
  • Tính sử dụng cao (nhờ có thể chỉnh sửa resource agent)

DRBD (Distributed Replicated Block Device)

DRBD là một phần mềm phát triển bởi linbit dùng để đồng bộ dữ liệu giữa 2 node ở mức độ block dữ liệu. drbd được sử dụng phổ biến trong những hệ thống đơn giản và dữ liệu đồng bộ không lớn lắm. Một số đặc điểm về drbd.

  • Thiết lập ban đầu đơn giản
  • Dễ dàng kết hợp với cluster như pacemaker
  • Không cần hiệu chỉnh nhiều trong lúc vận hành

Ngoài những ưu điểm trên, DRBD khó vận hành khi replicate nhiều nodes (ở phiên bản trước 9.0, DRBD chỉ cho phép replicate giữa 2 nodes)

  • drbd chỉ cho phép đồng bộ dữ liệu giữa 2 node.
  • đồng bộ toàn bộ (drbd volume) dữ liệu khi split brain xảy ra.

Ở một số phương pháp đồng bộ khác như rsync, chỉ cần đồng bộ những files có thay đổi. Như vậy sẽ giảm thiểu nhiều thời gian đồng bộ dữ liệu từ primary node. Thời gian phục hồi secondary node ảnh hưởng tới tính HA của hệ thống.

Cấu hình

Bên dưới mình trình bày một hướng dẫn cụ thể cấu hình một PostgreSQL cluster sử dụng pacemaker và DRBD.

Môi trường thực thi

# Resources phiên bản Ghi Chú
1 OS CentOS 8 Bao gồm 2 nodes, node1: 172.17.28.69 và node2: 172.17.28.71
2 PostgreSQL 13 beta 2 sử dụng bản build từ source
3 Pacemaker Sử dụng package từ CentOS yum repo Sử dụng rerource agents: drbd, Filesystem, pgsql, IP
4 DRBD 9.0 sử dụng giao thức (mức độ đồng bộ) B (đồng bộ trên memory hay tiền đồng bộ). Bài viết này mình cấu hình DRBD trên LVM (phần mềm quản lý volume logic được sử dụng rất phổ biến.

Các bước thực hiện

Cài đặt PostgreSQL 13 beta2 Thực hiện trên: 2 nodes

Đã có nhiều bài viết liên quan tới cài đặt nên ở đây mình sẽ giảm thiểu giải thích các bước cài đặt PostgreSQL

# cd /usr/local/src
# wget https://ftp.postgresql.org/pub/source/v13beta2/postgresql-13beta2.tar.gz > /dev/null
# tar xfz postgresql-13beta2.tar.gz
# cd postgresql-13beta2
# ./configure --prefix=/usr/local/pgsql/pg13 > /dev/null
# make -j16 install > /dev/null

Cấu hình LVM và DRBD

  1. Thực hiện trên: 2 nodes Download drbd packages
    Download DRBD packages từ đây
+ drbd90-utils-9.13.1-1.el8.elrepo.x86_64.rpm
+ drbd90-utils-sysvinit-9.13.1-1.el8.elrepo.x86_64.rpm
+ kmod-drbd90-9.0.23-1.el8_2.elrepo.x86_64.rpm
  1. Thực hiện trên: 2 nodes Cài đặt drbd packages
sudo yum localinstall *rpm
  1. Thực hiện trên: 2 nodes Kiểm tra xem firewalld đã tắt chưa
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)

Tắt Firewalld ở đây để thuận lợi khi giao tiếp giữa các nodes. Nếu firewalld chưa tắt, bạn có thể tắt và vô hiệu hóa bằng lệnh systemctl stop firewalld; systemctl disable firewalld.

  1. Thực hiện trên: 2 nodes Tạo ổ đĩa logic LVM

Thông tin thiết lập LVM

# Resources phiên bản
1 Tên ổ đĩa /dev/sdb
2 Tên physical volume /dev/sdb1
3 Tên volume group vg
4 Tên logical volume lv

Ở đây mình chuẩn bị một đĩa cứng với dung lượng 1GB (sdb) và chỉ sử dụng 512MB cho DRBD volume.

# fdisk /dev/sdb 
...snip...
Command (m for help): n
Partition type
   p   primary (0 primary, 0 extended, 4 free)
   e   extended (container for logical partitions)
Select (default p): 

Using default response p.
Partition number (1-4, default 1): 
First sector (2048-2097151, default 2048): 
Last sector, +sectors or +size{K,M,G,T,P} (2048-2097151, default 2097151): 

Created a new partition 1 of type 'Linux' and of size 1023 MiB.

Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Syncing disks.

# lsblk 
NAME        MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda           8:0    0   15G  0 disk 
├─sda1        8:1    0    1G  0 part /boot
└─sda2        8:2    0   14G  0 part 
  ├─cl-root 253:0    0 12.5G  0 lvm  /
  └─cl-swap 253:1    0  1.5G  0 lvm  [SWAP]
sdb           8:16   0    1G  0 disk 
└─sdb1        8:17   0 1023M  0 part 
sr0          11:0    1  7.7G  0 rom  
# pvcreate /dev/sdb1
  Physical volume "/dev/sdb1" successfully created.
# vgcreate vg /dev/sdb1
  Volume group "vg" successfully created
# lvcreate --size 512MB --name lv vg
  Logical volume "lv" created.
#
# lsblk 
NAME        MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda           8:0    0   15G  0 disk 
├─sda1        8:1    0    1G  0 part /boot
└─sda2        8:2    0   14G  0 part 
  ├─cl-root 253:0    0 12.5G  0 lvm  /
  └─cl-swap 253:1    0  1.5G  0 lvm  [SWAP]
sdb           8:16   0    1G  0 disk 
└─sdb1        8:17   0 1023M  0 part 
  └─vg-lv   253:2    0  512M  0 lvm  
sr0          11:0    1  7.7G  0 rom  
[root@node1 src]# 
  1. Tạo ổ đĩa DRBD trên LVM đã tạo

Thông tin thiết lập ổ DRBD

# Nội dung giải thích
1 Tên LVM sử dụng cho DRBD /dev/vg/lv
2 Node1 IP 172.17.28.69
3 Node2 IP 172.17.28.71
4 Port sử dụng cho replicate dữ liệu 7001
5 Protocol B (đồng bộ mức độ bộ nhớ)
  • Thực hiện trên: 2 nodes Tạo file drbd resource
# vi /etc/drbd.d/pgres.res
resource pgres {
    protocol B;
    device minor 1;
    meta-disk internal;
    disk /dev/mapper/vg-lv;
    on node1 {
        address 172.17.28.69:7001;
    }
    on node2 {
        address 172.17.28.71:7001;
    }
}

Bạn có thể thao khảo thêm ở manual của DRBD để có thêm nhiều tinh chỉnh.

  • Thực hiện trên: 2 nodes Tạo meta data cho ổ DRBD
# drbdadm create-md pgres
initializing activity log
initializing bitmap (16 KB) to all zero
Writing meta data...
New drbd meta data block successfully created.
# 
  • Thực hiện trên: 2 nodes Khởi động DRBD
# systemctl start drbd
# systemctl enable drbd
Synchronizing state of drbd.service with SysV service script with /usr/lib/systemd/systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable drbd
Created symlink /etc/systemd/system/multi-user.target.wants/drbd.service → /usr/lib/systemd/system/drbd.service.
# drbdadm status pgres
pgres role:Secondary
  disk:Inconsistent
  node2 role:Secondary
    peer-disk:Inconsistent
  • Thực hiện trên: Node1 Thiết lập node 1 là primary
[root@node1 ~]# drbdadm --force primary pgres
[root@node1 ~]# drbdadm status pgres
pgres role:Primary
  disk:UpToDate
  node2 role:Secondary
    peer-disk:UpToDate

[root@node1 ~]##
  • Thực hiện trên: Node1 Định dạng xfs cho ổ DRBD
[root@node1 src]# mkfs.xfs /dev/drbd1 -L PGDATA -f
meta-data=/dev/drbd1             isize=512    agcount=4, agsize=32765 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=1, sparse=1, rmapbt=0
         =                       reflink=1
data     =                       bsize=4096   blocks=131059, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0, ftype=1
log      =internal log           bsize=4096   blocks=1368, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
[root@node1 src]#

Tới đây mình có thể sử dụng ổ drbd, dữ liệu sẽ được đồng bộ từ node primary sang secondary. Bạn có thể chuyển chuyển vai trò các nodes lẫn nhau nhờ lệnh drbd secondary pgres để chuyển vai trò ổ drbd sang secondary (passive) drbd primary pgres để chuyển vai trò thành primary (active)

Cài đặt Pacemaker

Phần này mình sẽ hướng dẫn cài đặt pacemaker với cấu trúc như bên dưới.

+                       [ VIP: 172.17.28.111 ] (resources: drbd, Filesystem, pgsql, Ip)
+                                  |
++----------------------+          |          +----------------------+
+| [  Cluster Node1  ]  |          |          | [  Cluster Node2  ]  |
+|   IP: 172.17.28.69   +----------+----------+   172.17.28.71       |
+|                      |                     |                      |
++----------------------+                     +----------------------+

Các giá trị thiết lập cho pacemaker resources.

# Resource giá trị Chú thích
1 ocf:linbit:drbd drbd_resource=pgres Sử dụng pgres resource vừa tạo
2 ocf:heartbeat:Filesystem device="/dev/drbd1" directory=/pgdata fstype=xfs Mount ổ DRBD vào thư mục dữ liệu
3 ocf:heartbeat:pgsql

pgctl=/usr/local/pgsql/pg13/bin/pg_ctl

psql=/usr/local/pgsql/pg13/bin/psql 

pgdata=/pgdata/pg13data pgport=5432

PostgreSQL resource. Thực hiện các thao tác khởi động, monitoring, ... PostgreSQL.
4 ocf:heartbeat:IPaddr2 ip=172.17.28.111 VIP resource. Thực hiện các thao tác khởi động mornitoring, ... IP ảo cho cluster.

Các bước thực hiện với pacemaker

  • Thực hiện trên: 2 Nodes thực hiện enable HighAvailability yum repo (mặc định repo này không được enable trên CentOS 8), và cài đặt pacemaker packages.
# dnf config-manager --set-enabled HighAvailability
# yum install -y pacemaker pcs fence-agents-all psmisc > /dev/null
  • Thực hiện trên: 2 Nodes Thiết lập mật khẩu cho hacluster OS user (user này được tạo ra khi cài đặt pacemaker packages).
# echo "firstclt" | sudo passwd hacluster --stdin
Changing password for user hacluster.
passwd: all authentication tokens updated successfully.
  • Thực hiện trên: 2 Nodes Khởi động pcsd deamon (để có thể thực hiện các câu lệnh pcs).
# sudo systemctl start pcsd.service
# systemctl enable pcsd.service
Created symlink /etc/systemd/system/multi-user.target.wants/pcsd.service → /usr/lib/systemd/system/pcsd.service.
  • Thực hiện trên: 2 Nodes Thực hiện chứng thực giữa các nodes cho cluster
# sudo pcs host auth node1 node2 -u hacluster -p firstclt
node2: Authorized
node1: Authorized
  • Thực hiện trên: Node1 Khởi tạo cluster và khởi động
[root@node1 ~]# sudo pcs cluster setup firstcluster node1 node2 --force --start
...snip...
Sending 'corosync authkey', 'pacemaker authkey' to 'node1', 'node2'
node2: successful distribution of the file 'corosync authkey'
node2: successful distribution of the file 'pacemaker authkey'
node1: successful distribution of the file 'corosync authkey'
node1: successful distribution of the file 'pacemaker authkey'
Sending 'corosync.conf' to 'node1', 'node2'
node2: successful distribution of the file 'corosync.conf'
node1: successful distribution of the file 'corosync.conf'
Cluster has been successfully set up.
Starting cluster on hosts: 'node1', 'node2'...
[root@node1 ~]#
  • Xác nhận trạng thái cluster
# crm_mon -Arf1
Cluster Summary:
  * Stack: corosync
  * Current DC: node1 (version 2.0.3-5.el8_2.1-4b1f869f0f) - partition with quorum
  * Last updated: Wed Jul 15 03:29:57 2020
  * Last change:  Wed Jul 15 03:29:48 2020 by hacluster via crmd on node1
  * 2 nodes configured
  * 0 resource instances configured

Node List:
  * Online: [ node1 node2 ]

Full List of Resources:
  * No resources

Migration Summary:
  • Thực hiện trên: Node1 Thiết lập một số thông số mặc định
[root@node1 ~]# pcs cluster enable --all # thiết lập khởi động cluster cùng với OS
[root@node1 ~]# pcs property set stonith-enabled=false  # không thiết lập snonith vì không có device liên quan
[root@node1 ~]# pcs property set cluster-recheck-interval=10m # kiểm tra trạng thái mỗi 10 phút
[root@node1 ~]# pcs property set no-quorum-policy=ignore # thiết lập thao tác khi mất quorum 
  • Thực hiện trên: Node1 Tạo DRBD resource trên cluster
# pcs resource create drbd-fisrtcluster ocf:linbit:drbd drbd_resource=pgres op monitor \
interval=15s op start timeout=60 op stop timeout=60 op promote timeout=90
# pcs resource promotable drbd-fisrtcluster master-max=1 master-node-max=1 clone-max=2 clone-node-max=1 notify=true
# pcs resource cleanup

Kiểm tra trạng thái cluster

# crm_mon -Arf1
...snip...
Full List of Resources:
  * Clone Set: drbd-fisrtcluster-clone [drbd-fisrtcluster] (promotable):
    * Masters: [ node1 ]
    * Slaves: [ node2 ]
...snip...
  • Thực hiện trên: Node1 Tạo Filesystem resource trên cluster
# sudo mkdir /pgdata # tạo data directory để mount filesystem
# sudo pcs resource create pgFS-firstcluster ocf:heartbeat:Filesystem device="/dev/drbd1" \
 directory=/pgdata fstype=xfs options=defaults,noatime,nodiratime,attr2 op start timeout=60 \
op stop timeout=60 --group masterg-firstcluster
  • Kiểm tra trạng thái cluster
# crm_mon -Arf1
...snip...
Full List of Resources:
  * Clone Set: drbd-fisrtcluster-clone [drbd-fisrtcluster] (promotable):
    * Masters: [ node1 ]
    * Slaves: [ node2 ]
  * Resource Group: masterg-firstcluster:
    * pgFS-firstcluster (ocf::heartbeat:Filesystem):    Started node1
...snip...
  • Thực hiện trên: Node1 Khởi tạo postgres DB cluster trên filesystem vừa tạo
[root@node1 ~]# chown postgres /pgdata/ -R
[root@node1 ~]# df -h /pgdata
Filesystem      Size  Used Avail Use% Mounted on
/dev/drbd1      507M   30M  478M   6% /pgdata
[root@node1 ~]# su - postgres
$ /usr/local/pgsql/pg13/bin/initdb -E utf8 --no-locale -D /pgdata/pg13data
  • Thực hiện trên: Node1 Tạo pgsql resource cho postgres
# sudo pcs resource create pgsql-firstcluster ocf:heartbeat:pgsql \
pgctl=/usr/local/pgsql/pg13/bin/pg_ctl psql=/usr/local/pgsql/pg13/bin/psql \
pgdata=/pgdata/pg13data pgport=5432 stop_escalate=1 op start timeout=60s \
interval=0s on-fail=restart op monitor timeout=60s interval=7s on-fail=restart \
op monitor timeout=60s interval=2s on-fail=restart role=Master op promote \
timeout=60s interval=0s on-fail=restart op demote timeout=60s interval=0s \
on-fail=stop op stop timeout=60s interval=0s on-fail=block op notify timeout=60s \
interval=0s --group masterg-firstcluster
  • Thiết lập failover postgres resource nếu số lượng lỗi (không thể kết nối) là một lần.
# pcs resource meta pgsql-firstcluster migration-threshold=1
  • Kiểm tra trạng thái cluster
# crm_mon -Arf1
...snip...
Full List of Resources:
  * Clone Set: drbd-fisrtcluster-clone [drbd-fisrtcluster] (promotable):
    * Masters: [ node1 ]
    * Slaves: [ node2 ]
  * Resource Group: masterg-firstcluster:
    * pgFS-firstcluster (ocf::heartbeat:Filesystem):    Started node1
    * pgsql-firstcluster        (ocf::heartbeat:pgsql): Started node1
...snip...
# ps -ef | grep postgres: | grep -v grep
postgres   11226   11217  0 03:47 ?        00:00:00 postgres: checkpointer
postgres   11227   11217  0 03:47 ?        00:00:00 postgres: background writer
postgres   11228   11217  0 03:47 ?        00:00:00 postgres: walwriter
postgres   11229   11217  0 03:47 ?        00:00:00 postgres: autovacuum launcher
postgres   11230   11217  0 03:47 ?        00:00:00 postgres: stats collector
postgres   11231   11217  0 03:47 ?        00:00:00 postgres: logical replication launcher
#
  • Thực hiện trên: Node1 Tạo VIP cho cluster
# sudo pcs resource create vip-firstcluster ocf:heartbeat:IPaddr2 ip=172.17.28.111 \
nic=ens33 cidr_netmask=28 op start timeout=60s interval=0s on-fail=restart \
op monitor timeout=120s interval=20s on-fail=restart op stop timeout=60s \
interval=0s on-fail=block --group masterg-firstcluster
  • Kiểm tra trạng thái cluster
# crm_mon -Arf1
...snip...
Full List of Resources:
  * Clone Set: drbd-fisrtcluster-clone [drbd-fisrtcluster] (promotable):
    * Masters: [ node1 ]
    * Slaves: [ node2 ]
  * Resource Group: masterg-firstcluster:
    * pgFS-firstcluster (ocf::heartbeat:Filesystem):    Started node1
    * pgsql-firstcluster        (ocf::heartbeat:pgsql): Started node1
    * vip-firstcluster  (ocf::heartbeat:IPaddr2):       Started node1
...snip...
# ip a | grep -w inet
    inet 127.0.0.1/8 scope host lo
    inet 172.17.28.69/28 brd 172.17.28.79 scope global noprefixroute ens33
    inet 172.17.28.111/28 scope global ens33
    inet 192.168.91.131/24 brd 192.168.91.255 scope global dynamic noprefixroute ens37
#
  • Thực hiện trên: Node1 Tạo constraint cho cluster
  • Khởi động group master với DRBD trên cùng một Node
# sudo pcs constraint colocation add started masterg-firstcluster with Master \
drbd-fisrtcluster-clone INFINITY 
  • Khởi động theo thứ tự DRBD xong rồi master group
# sudo pcs constraint order promote drbd-fisrtcluster-clone then start \
masterg-firstcluster symmetrical=false score=INFINITY 
  • Thực hiện trên: Node1 Test failover cluster
    • Kiểm tra trạng thái cluster
# crm_mon -Arf1
...snip...
Full List of Resources:
  * Clone Set: drbd-fisrtcluster-clone [drbd-fisrtcluster] (promotable):
    * Masters: [ node1 ]
    * Slaves: [ node2 ]
  * Resource Group: masterg-firstcluster:
    * pgFS-firstcluster (ocf::heartbeat:Filesystem):    Started node1
    * pgsql-firstcluster        (ocf::heartbeat:pgsql): Started node1
    * vip-firstcluster  (ocf::heartbeat:IPaddr2):       Started node1
...snip...
  • Failover master resource sang node2
# pcs resource move masterg-firstcluster
Warning: Creating location constraint 'cli-ban-masterg-firstcluster-on-node1' with a score of -INFINITY for resource masterg-firstcluster on node1.
        This will prevent masterg-firstcluster from running on node1 until the constraint is removed
        This will be the case even if node1 is the last node in the cluster
  • Kiểm tra trạng thái cluster
# crm_mon -Arf1
...snip...
Full List of Resources:
  * Clone Set: drbd-fisrtcluster-clone [drbd-fisrtcluster] (promotable):
    * Masters: [ node2 ]
    * Slaves: [ node1 ]
  * Resource Group: masterg-firstcluster:
    * pgFS-firstcluster (ocf::heartbeat:Filesystem):    Started node2
    * pgsql-firstcluster        (ocf::heartbeat:pgsql): Started node2
    * vip-firstcluster  (ocf::heartbeat:IPaddr2):       Started node2
...snip...
  • Xóa constraint tạo ra bởi lệnh move resource
# pcs resource clear masterg-firstcluster
Removing constraint: cli-ban-masterg-firstcluster-on-node1

Đến đây bạn đã có một PostgreSQL cluster tự động failover giữa các nodes nếu gặp lỗi.

Lời kết

Bao gồm pacemaker có rất nhiều cấu trúc cluster có thể áp dụng cho hệ thống của bạn. Bạn nên tham khảo vào mục đích sử dụng, yêu cầu từ hệ thống và business để lựa chọn cấu hình phù hợp với hệ thống của mình.

Hướng dẫn cài đặt postgres 13 beta 1

Hướng dẫn cài đặt postgres 13 beta 1

Vào ngày 21-05-2020, bản thử nghiệm Postgresql version 13 đã được công bố. Dự kiến cuối năm nay, chúng ta sẽ có bản chính thức của version 13. Vậy còn chần chừ gì nữa, hãy thử nghiệm version 13 beta và cùng nhau tìm hiểu, đóng góp, chia sẻ những tính năng mới của version này nào

Cài đặt các package hỗ trợ:

# yum install -y gcc make systemd-devel zlib-devel readline-devel

Tạo postgres user

# useradd -m -s /bin/bash postgres

Tiến hành Download source code PostgreSQL

# cd /usr/local/src
# wget https://ftp.postgresql.org/pub/source/v13beta1/postgresql-13beta1.tar.gz
 
(Hoặc bạn có thể download từ windows PC rồi dùng WinSCP copy lên server linux)

Tiến hành giải nén:

# gunzip postgresql-13beta1.tar.gz
# tar xf postgresql-13beta1.tar
# cd postgresql-13beta1
# chown postgres:postgres -R postgresql-13beta1/

Chuyển qua user postgres

# sudo -iu postgres

Thực hiện build

### Thực hiện cấu hình:
$ /usr/local/src/postgresql-13beta1
./configure --with-icu --with-openssl --with-systemd
Chú ý: --with-systemd : hỗ trợ build với systemd

Make a build

$ make

Kiểm tra trước khi cài đặt

$ make check
 

Tiến hành cài đặt

$ sudo make install
 

Cấu hình biến môi trường cho postgres server:

$ DATA_DIR=/usr/local/pgsql/data
$ sudo mkdir $DATA_DIR
$ sudo chown postgres $DATA_DIR
$ echo 'export PATH=$PATH:/usr/local/pgsql/bin' > /etc/profile.d/postgres.sh

Khởi tạo database cluster

$ initdb --encoding=UTF8 --locale=C -D $DATA_DIR
$ pg_ctl -D /usr/local/pgsql/data start


**Như vậy bạn đã cài xong postgres 13 beta để trải nghiệm rồi**
**Have a nice time**

Cấu hình master slave cho postgresql 12

Hướng dẫn cấu hình master-slave cho postgresql version 12

Ví dụ chúng ta có 2 server cần cấu hình mô hình master-slave:
Master node: 10.133.36.95
Slave node: 10.133.36.96

Trước tiên bạn cần cài đặt postgresql 12 cho 2 servers như các hướng dẫn ở phần trước:
Hướng dẫn cài đặt postgres 12 từ Source Code (27/03/2020)
Hướng dẫn cấu hình postgresql service trong systemd(28/03/2020)

Bây giờ chúng ta tiến hành cấu hình mô hình master-slave:

Thực hiện cấu hình cho master node

step 01: Tạo thư mục archive wal

$ sudo mkdir -p /usr/local/pgsql/archive/
$ sudo chmod 700 /usr/local/pgsql/archive/
$ sudo chown -R postgres:postgres /usr/local/pgsql/archive/

step 02: Thực hiện cấu hình trên postgresql.conf

$ cd /usr/local/pgsql/data
$ vim postgresql.conf
 

listen_addresses = '10.133.36.95'
wal_level = hot_standby
synchronous_commit = on
archive_mode = on
archive_command = 'cp %p /usr/local/pgsql/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'slave_db'

step 03: Tạo new user với quyền replication

sudo -u postgres psql
postgres=# createuser --replication -P replica
Enter password for new role:
Enter it again:

step 04: Thực hiện cấu hình trên pg_hba.conf

$ cd /usr/local/pgsql/data/
$ vim pg_hba.conf

# Localhost
host replication replica 127.0.0.1/32 md5

# Master
host replication replica 10.133.36.95/32 md5

# Slave
host replication replica 10.133.36.96/32 md5

step 05: Khởi động lại postgresql database

$ sudo systemctl restart postgresql

 

Thực hiện cấu hình cho slave node

step 01: Dừng chạy postgresql

$ sudo systemctl stop postgresql

step 02: Xóa tất cả file trong thư mục data

rm -rf /usr/local/pgsql/data/*

step 03: Copy tất cả file từ thư mục data của master node sang slave node

$ pg_basebackup -h 10.133.36.95 -U replica -p 5432 -D /usr/local/pgsql/data -Fp -Xs -P -R

step 04: Tạo file standby.signal

(Chú ý: đây là điểm khác biệt của version 12 so với các version trước)
touch /usr/local/pgsql/data/standby.signal

step 05: Thực hiện khai báo cấu hình vào file config postgres

$ cd /usr/local/pgsql/data
$ vim postgresql.conf
listen_addresses = '10.133.36.96'
hot_standby = on
$ vim postgresql.auto.conf
primary_conninfo = 'user=replica password=root host=10.133.36.95 port=5432 sslmode=prefer sslcompression=0 gssencmode=disable target_session_attrs=any application_name=slave_db'

step 06: khởi động postgresql service

sudo systemctl start postgresql
 

Kiểm tra lại kết quả

$ psql -x -c "select * from pg_stat_replication"

Như vậy bạn đã cấu hình xong mô hình master-slave cho postgresql version 12. Và bạn thấy điểm khác biệt là vesion 12 sẽ không còn file recovery.conf như các version trước nữa.Hãy khám phá thêm những điểm khác biệt của version 12 ở các bài viết tiếp nhé

Hướng dẫn cài extension pg_show_plans

1. Định nghĩa

pg_show_plans là một công cụ PostgreSQL miễn phí cho phép bạn giám sát các SQL Execution plan trong thời gian thực.
Từ trước đến nay, chúng ta không có cách nào theo dõi các SQL Execution plan trong khi câu lệnh SQL vẫn đang chạy.
Bây giờ bạn có thể thấy chính xác những gì đang diễn ra trên máy chủ của mình - trực tiếp và trong thời gian thực với pg_show_plans.

2. Cách cài đặt pg_show_plans tool

Bước 1: download gói pg_show_plans

$ sudo git clone https://github.com/cybertec-postgresql/pg_show_plans.git

Bước 2: Cài đặt pg_show_plans

$ cd pg_show_plans
$ sudo make

$ sudo make install

Bước 3 : Thêm cấu hình tại postgresql.conf và khởi động lại DB

vi postgresql.conf
shared_preload_libraries = 'pg_show_plans'

$ pg_ctl -D /ur/local/pgsql/data restart

Bước 4: Tạo pg_show_plans extension trong postgresql

$ psql -c "CREATE EXTENSION pg_show_plans;"

3. Cách sử dụng

SELECT * FROM pg_show_plans;
SELECT p.pid, p.level, p.plan, a.query FROM pg_show_plans p LEFT JOIN pg_stat_activity a ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level;

Cách enable and disable pg_show_plans

pg_show_plans_disable() dùng để disable pg_show_plans trong postgresql
pg_show_plans_enable() dùng để enable pg_show_plans trong postgresql

Cách thay đổi format hiển thị

pgsp_format_json() Hiển thị dạng json
pgsp_format_text() Hiển thị dạng text

PostgreSQL 10~ Declarative Partitioning

Lời mở đầu

Partitioning bảng dữ liệu, là chức năng tách dữ liệu vật lý của một bảng dữ liệu lớn sang các bảng nhỏ hơn.
Nhờ phân chia dữ liệu được qua các bảng con nhỏ hơn chức năng được biết đến với những lợi ích như bên dưới.

  • Tăng performance của hệ thống.
    Thay vì truy cập dữ toàn bộ dữ liệu (hay thực hiện scan toàn index), chức năng này chỉ truy cập dữ liệu ở bảng (index) con cần thiết, ngoài ra có thể thực hiện truy vấn song song trên các bảng con nên performance tăng đáng kể nếu có thiết kế thích hợp.
  • Quản lý dễ dàng.
    Đối với dữ liệu thiết kế kiểu INSERT/DELETE diễn ra định kỳ. Bạn có thể xoá dữ liệu bằng DROP bảng con không cần thiết, thay vì thực hiện DELETE và VACUUM.
    Ngoài ra việc migration dữ liệu, backup/restore, hay các thao tác maintenance dữ liệu như VACUUM/REINDEX cũng diễn ra dễ dàng hơn.

PostgreSQL hỗ trợ chức năng Partitioning bảng dữ liệu.
Trước phiên bản 10, PostgreSQL cung cấp chức năng kế thừa (INHERITS) để thực hiện partitioning bảng dữ liệu. Nhưng việc điều hướng dữ liệu đến các bảng con khi INSERT hay UPDATE không diễn ra tự động mà phải sử dụng TRIGGER để thực hiện.

Bài viết này giới thiệu về chức năng partitioning bảng dữ liệu theo kiến trúc mới, chức năng Declarative Partitioning được đưa vào từ phiên bản PostgreSQL 10.

Scale out cho hệ thống PostgreSQL

Trước khi đi vào giới thiệu cụ thể, xin được tóm tắt các kỹ thuật scale out hệ thống của PostgreSQL hiện tại.
Đối với các hệ thống lớn hay các hệ thống dữ liệu có độ lớn tăng dần theo thời gian, việc thiết kế scale out (chia nhỏ dữ liệu thành nhiều phần để dễ quản lý) là một việc quan trọng nếu không muốn xử lý các vấn đề về dung lượng sau vận hành. PostgeSQL cung cấp các chức năng như bên dưới để hỗ trợ scale out hệ thống.

  • Sử dụng TABLESPACE
    Từ các phiên bản cũ TABLESPACE đã được sử dụng như công cụ để phân tán dữ liệu qua nhiều đĩa cứng giảm disk I/O. Nhưng gần đây gần đây nhiều hệ thống chuyển qua sử dụng RAID, nên chức năng này cũng không được sử dụng nhiều
  • Sử dụng các software liên quan
    Sử dụng một số cluster software như PostgreSQL-XL, pacemaker, pgpool-II sử dụng slave node để tham chiếu giảm tải cho master. Hay dùng chức năng load balancer (của pgpool-II),... cũng có thể được coi là một trong các giải pháp scale out. Giải pháp này hơi khó khăn về mặt bảo trì về cấu trúc hệ thống.
  • Cascade Replication
    Là chức năng đưa vào từ phiên bản 9.2 của PostgreSQL, slave node có thể chuyển tiếp đồng bộ sang các node slave mới.Ta có thể sử dụng chức năng này để scale out cho hệ thống. Hạn chế của cấu trúc này là cascade replication chỉ support phi đồng bộ, thời gian phản ánh WAL (transaction log) lên các slave node có thể trễ, nên dữ liệu trên slave không phải lúc nào cũng là mới nhất.
  • Sử dụng postgres_fwd
    Contrib postgres_fwd cho phép PostgreSQL kết nối tới server bên ngoài. PostgreSQL 9.6 hỗ trợ push down, kết quả được sử lý (sort, join, ...) ở remote server trước khi gửi về local. Việc này làm giảm tải rất nhiều cho local server trong các hệ thống lớn.
    Chức năng này trong tương lai được kỳ vọng là tạo thành nền tảng cho chức năng Sharding giống như các RDBMS như mongodb.
  • Partitioning Table
    Chức năng phân tán dữ liệu từ một bảng sang nhiều bảng con để tăng khả năng scale out cho hệ thống. Từ Application chỉ cần chú ý tới bảng dữ liệu cha. Dữ liệu khi INSERT/UPDATE vào bảng cha sẽ được phân tán tới các bảng con. Khi SELECT dữ liệu bảng cha, nhờ chức năng Partitioning dữ liệu các bảng con được tập hợp lại và gửi lại cho bảng cha. Các hệ thống cũ thường sử dụng chức năng này kết hợp với TABLESPACE để scale out hệ thống.

Chức năng Partitioning của PostgreSQL

Chức năng này cũng là một chức năng được được PostgreSQL đưa vào sớm từ phiên bản 8.1. Mặc dù có một số hạn chế overhead do phải thiết dựa trên trigger, nhưng chức năng này cũng được sử dụng rộng dãi cho tới nay. Từ phiên bản 10 PostgreSQL hỗ trợ phương thức partitioning mới, không dựa vào trigger nên có performance tốt và cách sử dụng đơn giản.

Chức năng Partitioning sử dụng kế thừa

Ở các phiên bản trước của PostgreSQL 10, để sử dụng chức năng partitioning. Ta cần các bước tổng quan như sau.

  1. Tạo bảng master
  2. Tạo các bảng con
  3. Tạo TRIGGER cho bảng master

Tạo bảng master

Ta tạo bảng master với các trường cần thiết.
Ở đây ta sử dụng cột range cho chức năng partitioning.

10000 postgres@postgres=# CREATE TABLE parent(id integer, childname text, range bigint);
CREATE TABLE

Tạo các bảng con

Bạn có thể định nghĩa số lượng bảng con tuỳ ý theo thiết kế hệ thống của bạn. Nhưng community khuyến cáo không nên sử dụng quá 100 bảng con.
Ví dụ bên dưới mình tạo 3 bảng con kế thừa bảng master.
Ở đây mình sử dụng ràng buộc CHECK dữ liệu, để bảng con tương ứng chỉ chứa những giá trị cho phép.
Khi tham chiếu dữ liệu bảng cha, PostgreSQL có thể dựa vào ràng buộc CHECK của bảng con để bỏ qua tham chiếu tới các bảng con không cần thiết.

10000 postgres@postgres=# CREATE TABLE child1(check(range <= 99999)) inherits(parent);
CREATE TABLE
10000 postgres@postgres=# CREATE TABLE child2(check(range > 99999 and range <= 199999)) inherits(parent);
CREATE TABLE
10000 postgres@postgres=# CREATE TABLE child3(check(range > 199999)) inherits(parent);
CREATE TABLE
10000 postgres@postgres=#

Tạo TRIGGER cho bảng master

Mặc định khi tạo table con ta đã có thể sử dụng chức năng partitioning để truy suất dữ liệu các bảng con thông qua bảng master. Nhưng để insert dữ liệu tới các bảng con thông qua bảng master ta phải sử dụng chức năng TRIGGER của PostgreSQL.
Ví dụ bên dưới thực hiện:

  • Tạo hàm TRIGGER insert_to_child cho phép kiểm tra dữ liệu INSERT và, thực hiện INSERT vào các bảng con tương ứng mà không thực hiện INSERT trên bảng master (return NULL;).
  • Sử dụng hàm insert_to_child đã định nghĩa để tạo TRIGGER cho bảng master trước khi INSERT dữ liệu.

Ta có thể sử dụng chức năng RULE của PostgreSQL thay vì tạo TRIGGER. RULE có overhead nhiều hơn TRIGGER khi INSERT 1 hàng, nhưng ngược lại khi INSERT một khối lượng lớn dữ liệu (ví dụ như COPY) thì overhead nhỏ hơn TRIGGER.

10000 postgres@postgres=# CREATE OR REPLACE FUNCTION insert_to_child () RETURNS TRIGGER
AS 
$$ 
BEGIN
    IF (NEW.range <= 99999) THEN
        INSERT INTO child1 VALUES (NEW.*);
    ELSIF (NEW.range > 99999 AND NEW.range <= 199999) THEN
        INSERT INTO child2 VALUES (NEW.*);
    ELSIF (NEW.range > 199999) THEN
        INSERT INTO child3 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'out of range';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE PLPGSQL;
CREATE FUNCTION
10000 postgres@postgres=# CREATE TRIGGER insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child();
CREATE TRIGGER

Sau khi tạo được bảng con và TRIGGER cho bảng master. Bảng master sẽ có cấu trúc như bên dưới.

10000 postgres@postgres=# \d+ parent
                                    Table "public.parent"
  Column   |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
-----------+---------+-----------+----------+---------+----------+--------------+-------------
 id        | integer |           |          |         | plain    |              | 
 childname | text    |           |          |         | extended |              | 
 range     | bigint  |           |          |         | plain    |              | 
Triggers:
    insert_to_child_tg BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_to_child()
Child tables: child1,
              child2,
              child3

Thực hiện truy vấn

INSERT & SELECT dữ liệu

Ví dụ bên dưới INSERT 3 dòng dữ liệu thông qua bảng master và xác nhận dữ liệu đã được ghi vào các bảng con nhờ TRIGGER đã định nghĩa.

10000 postgres@postgres=# INSERT INTO parent VALUES (1,'a',1), (2,'b',199999), (3,'c',200000);
INSERT 0 0
10000 postgres@postgres=# SELECT * FROM parent ;
 id | childname | range  
----+-----------+--------
  1 | a         |      1
  2 | b         | 199999
  3 | c         | 200000
(3 rows)
10000 postgres@postgres=# SELECT * FROM parent ;
 id | childname | range  
----+-----------+--------
  1 | a         |      1
  2 | b         | 199999
  3 | c         | 200000
(3 rows)

10000 postgres@postgres=# SELECT * FROM child1;
 id | childname | range 
----+-----------+-------
  1 | a         |     1
(1 row)

10000 postgres@postgres=# SELECT * FROM child2;
 id | childname | range  
----+-----------+--------
  2 | b         | 199999
(1 row)

10000 postgres@postgres=# SELECT * FROM child3;
 id | childname | range  
----+-----------+--------
  3 | c         | 200000
(1 row)

Như kết quả EXPLAIN của câu lệnh SELECT bảng master bên dưới. Dữ liệu được tìm kiếm ở các bảng con chứa dữ liệu tương ứng và bảng master.

10000 postgres@postgres=# EXPLAIN (ANALYZE,VERBOSE) SELECT * FROM parent WHERE range IN (1,199999);
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..48.37 rows=23 width=44) (actual time=0.028..0.039 rows=2 loops=1)
   ->  Seq Scan on public.parent  (cost=0.00..0.00 rows=1 width=44) (actual time=0.011..0.011 rows=0 loops=1)
         Output: parent.id, parent.childname, parent.range
         Filter: (parent.range = ANY ('{1,199999}'::bigint[]))
   ->  Seq Scan on public.child1  (cost=0.00..24.12 rows=11 width=44) (actual time=0.015..0.016 rows=1 loops=1)
         Output: child1.id, child1.childname, child1.range
         Filter: (child1.range = ANY ('{1,199999}'::bigint[]))
   ->  Seq Scan on public.child2  (cost=0.00..24.12 rows=11 width=44) (actual time=0.008..0.009 rows=1 loops=1)
         Output: child2.id, child2.childname, child2.range
         Filter: (child2.range = ANY ('{1,199999}'::bigint[]))
 Planning Time: 1.433 ms
 Execution Time: 0.082 ms
(12 rows)

10000 postgres@postgres=# show constraint_exclusion ;
 constraint_exclusion 
----------------------
 partition
(1 row)

PostgreSQL bỏ qua tìm kiếm dữ liệu dựa vào ràng buộc CHECK thông qua tham số constraint_exclusion. Nếu tham số này thiết lập là on hoặc partition, PostgreSQL sẽ sử dụng ràng buộc CHECK để bỏ qua tìm kiếm ở bảng con không cần thiết.
Ví dụ bên dưới khi set constraint_exclusion sang off, PostgreSQL scan tất cả các bảng con.

10000 postgres@postgres=# set constraint_exclusion TO off;
SET
10000 postgres@postgres=# EXPLAIN (ANALYZE,VERBOSE) SELECT * FROM parent WHERE range IN (1,199999);
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..72.55 rows=34 width=44) (actual time=0.021..0.053 rows=2 loops=1)
   ->  Seq Scan on public.parent  (cost=0.00..0.00 rows=1 width=44) (actual time=0.006..0.006 rows=0 loops=1)
         Output: parent.id, parent.childname, parent.range
         Filter: (parent.range = ANY ('{1,199999}'::bigint[]))
   ->  Seq Scan on public.child1  (cost=0.00..24.12 rows=11 width=44) (actual time=0.013..0.014 rows=1 loops=1)
         Output: child1.id, child1.childname, child1.range
         Filter: (child1.range = ANY ('{1,199999}'::bigint[]))
   ->  Seq Scan on public.child2  (cost=0.00..24.12 rows=11 width=44) (actual time=0.007..0.007 rows=1 loops=1)
         Output: child2.id, child2.childname, child2.range
         Filter: (child2.range = ANY ('{1,199999}'::bigint[]))
   ->  Seq Scan on public.child3  (cost=0.00..24.12 rows=11 width=44) (actual time=0.022..0.022 rows=0 loops=1)
         Output: child3.id, child3.childname, child3.range
         Filter: (child3.range = ANY ('{1,199999}'::bigint[]))
         Rows Removed by Filter: 1
 Planning Time: 0.299 ms
 Execution Time: 0.128 ms
(16 rows)

UPDATE dữ liệu

Như ví dụ bên dưới vì ta chưa tạo TRIGGER khi UPDATE bảng master. Nên dữ liệu UPDATE không nằm trong khoảng của ràng buộc CHECK sẽ gây ra lỗi. Nếu muốn khắc phục lỗi này ta phải định nghĩa thêm TRIGGER thực hiện điều hướng dữ liệu UPDATE tới các bảng con tương ứng.

10000 postgres@postgres=# UPDATE parent SET range = 200000 WHERE range = 1;
ERROR:  new row for relation "child1" violates check constraint "child1_range_check"
DETAIL:  Failing row contains (1, a, 200000).
10000 postgres@postgres=# UPDATE parent SET range = 2 WHERE range = 1;
UPDATE 1
10000 postgres@postgres=# 

Declarative Partitioning (PostgreSQL 10 ~)

Nếu như ta phải thực hiện nhiều thao tác mới sử dụng được chức năng Partitioning sử dụng kế thừa như trên, thì từ phiên bản 10 ta chỉ đơn giản thực hiện các thao tác bên dưới là có thể sử dụng được.

  1. Tạo bảng master với tuỳ chọn partitioning
  2. Tạo bảng con tương ứng cho bảng master

Tạo bảng master Declarative Partitioning

Chức năng Declarative Partitioning hỗ trợ các phương thức partitioning như sau:

  • range (PostgreSQL 10~) Chỉ định phạm vi giá trị cho cột khoá của mỗi bảng con
  • list (PostgreSQL 10~) Chỉ định danh sách giá trị cho cột khoá của mỗi bảng con
  • hash (PostgreSQL 11~)
    Sử dụng hash để chỉ định giá trị cho cột khoá của mỗi bảng con

Ví dụ bên dưới mình sử dụng phương thức range (tương ứng với ví dụ ở chức năng partitioning sử dụng kế thừa).

10000 postgres@postgres=# CREATE TABLE parent_pg10(id integer, name text, range bigint) PARTITION BY RANGE (range);
CREATE TABLE

Tạo bảng con tương ứng cho bảng master

10000 postgres@postgres=# CREATE TABLE child1_pg10 PARTITION OF parent_pg10 FOR VALUES FROM (0) TO (99999) ;
CREATE TABLE
10000 postgres@postgres=# CREATE TABLE child2_pg10 PARTITION OF parent_pg10 FOR VALUES FROM (99999) TO (200000) ;
CREATE TABLE
10000 postgres@postgres=# CREATE TABLE child3_pg10 PARTITION OF parent_pg10 FOR VALUES FROM (200000) TO (9223372036854775807) ;
CREATE TABLE

Sau khi tạo được bảng con. Bảng master sẽ có cấu trúc như bên dưới.

10000 postgres@postgres=# \d+ parent_pg10
                                Table "public.parent_pg10"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              | 
 name   | text    |           |          |         | extended |              | 
 range  | bigint  |           |          |         | plain    |              | 
Partition key: RANGE (range)
Partitions: child1_pg10 FOR VALUES FROM ('0') TO ('99999'),
            child2_pg10 FOR VALUES FROM ('99999') TO ('200000'),
            child3_pg10 FOR VALUES FROM ('200000') TO ('9223372036854775807')

Thực hiện truy vấn

INSERT & SELECT dữ liệu

Ví dụ bên dưới INSERT 3 dòng dữ liệu thông qua bảng master và xác nhận dữ liệu đã được ghi vào các bảng con nhờ chức năng Declarative Partitioning.

10000 postgres@postgres=# INSERT INTO parent_pg10 VALUES (1,'a',1), (2,'b',199999), (3,'c',200000);
INSERT 0 3
10000 postgres@postgres=# SELECT * FROM parent_pg10 ;
 id | name | range  
----+------+--------
  1 | a    |      1
  2 | b    | 199999
  3 | c    | 200000
(3 rows)

10000 postgres@postgres=# SELECT * FROM child1_pg10 ;
 id | name | range 
----+------+-------
  1 | a    |     1
(1 row)

10000 postgres@postgres=# SELECT * FROM child2_pg10 ;
 id | name | range  
----+------+--------
  2 | b    | 199999
(1 row)

10000 postgres@postgres=# SELECT * FROM child3_pg10 ;
 id | name | range  
----+------+--------
  3 | c    | 200000
(1 row)

Như kết quả EXPLAIN của câu lệnh SELECT bảng master bên dưới. Mặc định dữ liệu được tìm kiếm qua các bảng con chứa dữ liệu tương ứng (bảng master không chứa dữ liệu).

10000 postgres@postgres=# EXPLAIN (ANALYZE,VERBOSE) SELECT * FROM parent_pg10 WHERE range IN (1,199999);
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..48.36 rows=22 width=44) (actual time=0.028..0.041 rows=2 loops=1)
   ->  Seq Scan on public.child1_pg10  (cost=0.00..24.12 rows=11 width=44) (actual time=0.027..0.028 rows=1 loops=1)
         Output: child1_pg10.id, child1_pg10.name, child1_pg10.range
         Filter: (child1_pg10.range = ANY ('{1,199999}'::bigint[]))
   ->  Seq Scan on public.child2_pg10  (cost=0.00..24.12 rows=11 width=44) (actual time=0.010..0.010 rows=1 loops=1)
         Output: child2_pg10.id, child2_pg10.name, child2_pg10.range
         Filter: (child2_pg10.range = ANY ('{1,199999}'::bigint[]))
 Planning Time: 1.352 ms
 Execution Time: 0.099 ms
(9 rows)

Cũng giống với chức năng partitioning sử dụng kết thừa. Sau khi set tham số constraint_exclusion sang off, dữ liệu được tìm kiếm trên tất cả các bảng.

Ở phiên bản PostgreSQL 11, PostgreSQL không sử dụng exclusion constraint cho việc loại trừ tìm kiếm dữ liệu bảng con không cần thiết (prunning), nên tham số constraint_exclusion không có hiệu lực, thay vào đó ta sử dụng tham số enable_partition_pruning.

10000 postgres@postgres=# set constraint_exclusion to off;
SET
10000 postgres@postgres=# set constraint_EXPLAIN (ANALYZE,VERBOSE) SELECT * FROM parent_pg10 WHERE range IN (1,199999);
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..26.15 rows=13 width=44) (actual time=0.017..0.046 rows=2 loops=1)
   ->  Seq Scan on public.child1_pg10  (cost=0.00..1.01 rows=1 width=44) (actual time=0.016..0.017 rows=1 loops=1)
         Output: child1_pg10.id, child1_pg10.name, child1_pg10.range
         Filter: (child1_pg10.range = ANY ('{1,199999}'::bigint[]))
   ->  Seq Scan on public.child2_pg10  (cost=0.00..1.01 rows=1 width=44) (actual time=0.007..0.007 rows=1 loops=1)
         Output: child2_pg10.id, child2_pg10.name, child2_pg10.range
         Filter: (child2_pg10.range = ANY ('{1,199999}'::bigint[]))
   ->  Seq Scan on public.child3_pg10  (cost=0.00..24.12 rows=11 width=44) (actual time=0.020..0.020 rows=0 loops=1)
         Output: child3_pg10.id, child3_pg10.name, child3_pg10.range
         Filter: (child3_pg10.range = ANY ('{1,199999}'::bigint[]))
         Rows Removed by Filter: 1
 Planning time: 0.278 ms
 Execution time: 0.090 ms
(13 rows)

UPDATE dữ liệu

  • Ở phiên bản PostgreSQL 10, cũng giống như với partitioning sử dụng kế thừa, câu lệnh UPDATE thất bại nếu dữ liệu mới cho cột key nằm ở partition khác.
10000 postgres@postgres=# UPDATE parent_pg10 SET range = 200001 WHERE range = 1;
ERROR:  new row for relation "child1_pg10" violates partition constraint
DETAIL:  Failing row contains (1, a, 200001).
  • Ở phiên bản PostgreSQL 11, hạn chế bên trên đã được loại bỏ, dữ liệu tự động điều hướng sang partition tương ứng.
11000 postgres@postgres=# UPDATE parent_pg10 SET range = 200001 WHERE range = 1;
UPDATE 1
11000 postgres@postgres=# SELECT * FROM child3_pg10 ;
 id | name | range  
----+------+--------
  3 | c    | 200000
  1 | a    | 200001
(2 rows)

Ở trường hợp dữ liệu đồng thời được update trên một cột key, có thể xảy ra lỗi, vui lòng xem mục hạn chế ở cuối bài viết này.

Performance

Như ví dụ bên dưới. Do có overhead bởi TRIGGER, Performance của INSERT giảm rõ rệt (~10 lần) khi sử dụng phương thức partitioning sử dụng kế thừa.

  • Kết quả EXPLAIN sử dụng phương thức partitioning sử dụng kế thừa.
10000 postgres@postgres=# EXPLAIN (ANALYZE,VERBOSE) INSERT INTO parent SELECT generate_series(1,999999),'CHILD', random()*10000000;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Insert on public.parent  (cost=0.00..17.52 rows=1000 width=44) (actual time=33340.724..33340.724 rows=0 loops=1)
   ->  Subquery Scan on "*SELECT*"  (cost=0.00..17.52 rows=1000 width=44) (actual time=0.008..1278.986 rows=999999 loops=1)
         Output: "*SELECT*".generate_series, 'CHILD'::text, "*SELECT*"."?column?_1"
         ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=44) (actual time=0.005..692.425 rows=999999 loops=1)
               Output: generate_series(1, 999999), NULL::unknown, (random() * '10000000'::double precision)
               ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
 Planning Time: 0.076 ms
 Trigger insert_to_child_tg: time=31178.106 calls=999999
 Execution Time: 33340.757 ms
(9 rows)
  • Kết quả EXPLAIN sử dụng phương thức Declarative Partitioning.
10000 postgres@postgres=# EXPLAIN (ANALYZE,VERBOSE) INSERT INTO parent_pg10 SELECT generate_series(1,999999),'CHILD', random()*10000000;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Insert on public.parent_pg10  (cost=0.00..17.52 rows=1000 width=44) (actual time=3814.105..3814.105 rows=0 loops=1)
   ->  Subquery Scan on "*SELECT*"  (cost=0.00..17.52 rows=1000 width=44) (actual time=0.011..646.356 rows=999999 loops=1)
         Output: "*SELECT*".generate_series, 'CHILD'::text, "*SELECT*"."?column?_1"
         ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=44) (actual time=0.007..328.986 rows=999999 loops=1)
               Output: generate_series(1, 999999), NULL::unknown, (random() * '10000000'::double precision)
               ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
 Planning Time: 0.100 ms
 Execution Time: 3814.203 ms
(8 rows)

Một số hạn chế và chú ý liên quan tới chức năng Declarative Partitioning

  1. Không hỗ trợ Primary Key cho cột khoá
10000 postgres@postgres=# alter table child1_pg10 add primary key (range);
ERROR:  multiple primary keys for table "child1_pg10" are not allowed
  1. Không hỗ trợ khoá ngoại lai
    Không hỗ hợ Primary Key đồng nghĩa với không hỗ trợ khoá ngoại lai tham chiếu tới cột khoá.
10000 postgres@postgres=# create table grand_child(id integer, name text, range bigint references child1(range));
ERROR:  there is no unique constraint matching given keys for referenced table "child1"
  1. Cập nhật dữ liệu sang partition khác (hạn chế ở phiên bản PostgreSQL 10, đã được fixed ở phiên bản 11)
  • Ở phiên bản PostgreSQL 10, Declarative Partitioning không hỗ trợ lệnh UPDATE chuyển dữ liệu từ partition này qua partition khác.
10000 postgres@postgres=# UPDATE parent_pg10 SET range = 200001 WHERE range = 1;
ERROR:  new row for relation "child1_pg10" violates partition constraint
DETAIL:  Failing row contains (1, a, 200001).
  • Phiên bản PostgreSQL 11 đã loại bỏ được hạn chế bên trên, nhưng nếu dữ liệu đang được chuyển qua partition khác, cùng lúc đó có lệnh UPDATE đối với dữ liệu tương ứng sẽ có thể xảy ra lỗi như bên dưới.
11000 postgres@postgres=#* update parent_pg10 SET range = 1000;
ERROR:  tuple to be updated was already moved to another partition due to concurrent update
  1. Không hỗ trợ ON CONFLICT (hạn chế ở phiên bản PostgreSQL 10, đã được fixed ở phiên bản 11)
  • Ở phiên bản 10 Declarative Partitioning không hỗ trợ cấu trúc ON CONFLICT.
10000 postgres@postgres=# insert into parent_pg10 values(1,'a',1) on conflict do nothing;
ERROR:  ON CONFLICT clause is not supported with partitioned tables
  • Hạn chế này đã được fixed tại phiên bản 11.
11000 postgres@postgres=#* into parent_pg10 values(1,'a',1) on conflict do nothing;
INSERT 0 0
  1. TRIGGER mức độ dòng dữ liệu phải được định nghĩa ở bảng con, vì bảng cha không chứa dữ liệu.

  2. Bảng con của một bảng master không thể có cả bảng cố định và bảng tạm thời (temp table).

11000 postgres@postgres=# CREATE temp TABLE child3_pg11 PARTITION OF parent_pg10 FOR VALUES FROM (200000) TO (9223372036854775807) ;
ERROR:  cannot create a temporary relation as partition of permanent relation "parent_pg10"
  1. Không hỗ trợ full cho postgres_fdw (hạn chế ở phiên bản PostgreSQL 10, đã được fixed ở phiên bản 11).

Ta có thể sử dụng chức năng partitioning table kết hợp với postgres_fdw để scale out hệ thống như bên dưới ở phiên bản PostgreSQL 11.
Ở phiên bản 10 có thể cấu hình được hệ thống bên dưới, nhưng hệ thống chưa hỗ trợ hoàn toàn các câu lệnh SQL cho postgres_fdw.
Hệ thống ví dụ bên dưới, sử dụng chức năng partitioning với 2 bảng con, 2 bảng con này tiếp tục được đồng bộ sang các DB install khác thông qua chức năng postgres_fdw.

===========================================
DB install 1:         Bảng master
                           |
                   +- partitioning -+
                   |                |
               bảng con 1      bảng con 2
                   |                |
===================+==postgres_fdw==+======
DB install 2:      |                |
               bảng con 1           |
                   |                |
===================+==postgres_fdw==+======
DB install 3:                       |
                               bảng con 2
                                    |
===========================================

Những thay đổi ở phiên bản PostgreSQL 11

Ở phiên bản PostgreSQL 11, có 3 cải thiện chính như bên dưới.

Faster partition pruning

Ở phiên bản 10 PostgreSQL sử dụng constraint_exclusion để bỏ qua partition không cần thiết. Việc loại bỏ này thực hiện bởi sử dụng giá trị ở WHERE clause rồi so sánh với các metadata của từng partition. Phiên bản 11 không sử dụng constraint_exclusion nữa mà thực hiện tìm kiếm trực tiếp tới partition cần thiết làm tăng performance.

Partition Pruning at Execution Time

Phiên bản 10 thực hiện bỏ qua partition không cần thiết (prunning) ở giai đoạn planning. Nếu chỉ thực hiện ở giai đoạn này, PostgreSQL sẽ không thể thực hiện prunning được những câu lệnh có truy vấn phụ như ví dụ bên dưới.

  • Ở phiên bản 10, PostgreSQL thực hiện scan trên tất cả các bảng con khi biểu thức tìm kiếm là một truy vấn phụ vì truy vấn chưa được thực thi ở giai đoạn planning.
10000 postgres@postgres=# explain (analyze, verbose) select * from parent_pg10 where range = (select id from temp);
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Append  (cost=35.50..19111.50 rows=3 width=18) (actual time=210.733..210.733 rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on public.temp  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.006..0.007 rows=1 loops=1)
           Output: temp.id
   ->  Seq Scan on public.child1_pg10  (cost=0.00..192.64 rows=1 width=18) (actual time=3.033..3.033 rows=0 loops=1)
         Output: child1_pg10.id, child1_pg10.name, child1_pg10.range
         Filter: (child1_pg10.range = $0)
         Rows Removed by Filter: 10051
   ->  Seq Scan on public.child2_pg10  (cost=0.00..192.30 rows=1 width=18) (actual time=2.420..2.420 rows=0 loops=1)
         Output: child2_pg10.id, child2_pg10.name, child2_pg10.range
         Filter: (child2_pg10.range = $0)
         Rows Removed by Filter: 10024
   ->  Seq Scan on public.child3_pg10  (cost=0.00..18691.06 rows=1 width=18) (actual time=205.277..205.277 rows=0 loops=1)
         Output: child3_pg10.id, child3_pg10.name, child3_pg10.range
         Filter: (child3_pg10.range = $0)
         Rows Removed by Filter: 979925
 Planning time: 0.287 ms
 Execution time: 210.785 ms
(18 rows)
  • PostgreSQL 11 thực hiện prunning khi thực thi truy vấn. Nên có thể đối ứng cả truy vấn phụ.
11000 postgres@postgres=# explain (analyze, verbose) select * from parent_pg10 where range = (select id from temp);
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Append  (cost=35.50..18906.51 rows=3 width=18) (actual time=6.997..6.997 rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on public.temp  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.799..0.801 rows=1 loops=1)
           Output: temp.id
   ->  Seq Scan on public.child1_pg10  (cost=0.00..190.64 rows=1 width=18) (actual time=6.157..6.157 rows=0 loops=1)
         Output: child1_pg10.id, child1_pg10.name, child1_pg10.range
         Filter: (child1_pg10.range = $0)
         Rows Removed by Filter: 10051
   ->  Seq Scan on public.child2_pg10  (cost=0.00..189.30 rows=1 width=18) (never executed)
         Output: child2_pg10.id, child2_pg10.name, child2_pg10.range
         Filter: (child2_pg10.range = $0)
   ->  Seq Scan on public.child3_pg10  (cost=0.00..18491.06 rows=1 width=18) (never executed)
         Output: child3_pg10.id, child3_pg10.name, child3_pg10.range
         Filter: (child3_pg10.range = $0)
 Planning Time: 0.377 ms
 Execution Time: 7.052 ms
(16 rows)

Như đã nói ở trên. Do PostgreSQL 10 không sử dụng exclusion constraint để bỏ qua partition không cần thiết, nên tham số constraint_exclusion không có hiệu lực đối với Declarative Partitioning trên phiên bản 11, thay vào đó là parameter enable_partition_pruning (mặc định là on).

Hỗ trợ hash partitioning

Ngoài kiểu range và list ở PostgreSQL 10, PostgreSQL 11 hỗ trợ thêm partitioning kiểu hash.
Thông qua hàm hash của PostgreSQL, dữ liệu INSERT vào cột khoá (cột partitioning) sẽ được điều hướng tới bảng con tương ứng.

Ví dụ:

  • Tạo bảng master
11000 postgres@postgres=# create table parent_hash(id name, child text, hashval text) partition by hash (hashval);
CREATE TABLE
  • Tạo bảng con
    Modulus có thể hiểu là số lượng partition cho bảng master. Remainder là số dư khi chia hash của giá trị INSERT vào cho số Modulus tương ứng.
11000 postgres@postgres=# create table child1_hash partition of parent_hash for values with (modulus 3, remainder 0);
CREATE TABLE
11000 postgres@postgres=# create table child2_hash partition of parent_hash for values with (modulus 3, remainder 1);
CREATE TABLE
11000 postgres@postgres=# create table child3_hash partition of parent_hash for values with (modulus 3, remainder 2);
CREATE TABLE
  • Thực hiện truy vấn
11000 postgres@postgres=# explain (analyze, verbose) insert into parent_hash select generate_series(1,10000000),'child',random()::text;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Insert on public.parent_hash  (cost=0.00..20.02 rows=1000 width=128) (actual time=66464.827..66464.827 rows=0 loops=1)
   ->  Subquery Scan on "*SELECT*"  (cost=0.00..20.02 rows=1000 width=128) (actual time=0.024..23043.207 rows=10000000 loops=1)
         Output: "*SELECT*".generate_series, 'child'::text, "*SELECT*".random
         ->  ProjectSet  (cost=0.00..5.03 rows=1000 width=68) (actual time=0.018..16336.944 rows=10000000 loops=1)
               Output: generate_series(1, 10000000), NULL::unknown, (random())::text
               ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
 Planning Time: 0.091 ms
 Execution Time: 66466.265 ms
(8 rows)
11000 postgres@postgres=# \d+ parent_hash 
                               Table "public.parent_hash"
 Column  | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
---------+------+-----------+----------+---------+----------+--------------+-------------
 id      | name |           |          |         | plain    |              | 
 child   | text |           |          |         | extended |              | 
 hashval | text |           |          |         | extended |              | 
Partition key: HASH (hashval)
Partitions: child1_hash FOR VALUES WITH (modulus 3, remainder 0),
            child2_hash FOR VALUES WITH (modulus 3, remainder 1),
            child3_hash FOR VALUES WITH (modulus 3, remainder 2)

11000 postgres@postgres=# select count(*) from child1_hash;
  count  
---------
 3332642
(1 row)

11000 postgres@postgres=# select count(*) from child2_hash;
  count  
---------
 3333466
(1 row)

11000 postgres@postgres=# select count(*) from child3_hash;
  count  
---------
 3333892
(1 row)

11000 postgres@postgres=# select count(*) from only parent_hash;
 count 
-------
     0
(1 row)

-- Dữ liệu được tìm kiếm ở bảng tương ứng (public.child1_hash)
11000 postgres@postgres=# explain (analyze, verbose) select * from parent_hash where hashval = '0.413760441355407';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..74228.30 rows=1 width=88) (actual time=1.003..522.970 rows=1 loops=1)
   Output: child1_hash.id, child1_hash.child, child1_hash.hashval
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.00..73228.20 rows=1 width=88) (actual time=342.915..516.902 rows=0 loops=3)
         Worker 0: actual time=514.096..514.096 rows=0 loops=1
         Worker 1: actual time=514.136..514.136 rows=0 loops=1
         ->  Parallel Seq Scan on public.child1_hash  (cost=0.00..73228.19 rows=1 width=88) (actual time=342.908..516.894 rows=0 loops=3)
               Output: child1_hash.id, child1_hash.child, child1_hash.hashval
               Filter: (child1_hash.hashval = '0.413760441355407'::text)
               Rows Removed by Filter: 1110880
               Worker 0: actual time=514.088..514.088 rows=0 loops=1
               Worker 1: actual time=514.127..514.127 rows=0 loops=1
 Planning Time: 1.810 ms
 Execution Time: 526.021 ms
(15 rows)

--chưa hỗ trợ LIKE clause (dữ liệu không được prunning)  
11000 postgres@postgres=# explain (analyze, verbose) select * from parent_hash where hashval like '%41376044135%';
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..220838.95 rows=1002 width=88) (actual time=2.684..1956.412 rows=1 loops=1)
   Output: child2_hash.id, child2_hash.child, child2_hash.hashval
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.00..219738.75 rows=417 width=88) (actual time=1298.137..1949.377 rows=0 loops=3)
         Worker 0: actual time=1945.537..1945.537 rows=0 loops=1
         Worker 1: actual time=1946.631..1946.631 rows=0 loops=1
         ->  Parallel Seq Scan on public.child2_hash  (cost=0.00..73259.59 rows=139 width=88) (actual time=655.334..655.334 rows=0 loops=3)
               Output: child2_hash.id, child2_hash.child, child2_hash.hashval
               Filter: (child2_hash.hashval ~~ '%41376044135%'::text)
               Rows Removed by Filter: 1111155
               Worker 0: actual time=9.694..9.694 rows=0 loops=1
               Worker 1: actual time=1946.623..1946.623 rows=0 loops=1
         ->  Parallel Seq Scan on public.child3_hash  (cost=0.00..73248.88 rows=139 width=88) (actual time=968.696..968.696 rows=0 loops=2)
               Output: child3_hash.id, child3_hash.child, child3_hash.hashval
               Filter: (child3_hash.hashval ~~ '%41376044135%'::text)
               Rows Removed by Filter: 1666946
               Worker 0: actual time=1935.832..1935.832 rows=0 loops=1
         ->  Parallel Seq Scan on public.child1_hash  (cost=0.00..73228.19 rows=139 width=88) (actual time=2.242..1944.713 rows=1 loops=1)
               Output: child1_hash.id, child1_hash.child, child1_hash.hashval
               Filter: (child1_hash.hashval ~~ '%41376044135%'::text)
               Rows Removed by Filter: 3332641
 Planning Time: 0.471 ms
 Execution Time: 1961.897 ms
(24 rows)

Kết luận

Cùng với chức năng logical replication, chức năng Declarative Partitioning được đưa vào từ phiên bản PostgreSQL 10 được coi là một bước tiến đáng kể của PostgreSQL.
Chức năng Declarative Partitioning kết hợp với postgres_fdw có thể là nền tảng cho chức năng sharding của PostgreSQL sau này.
Phiên bản 11 khắc phục khá lớn các hạn chế của Declarative Partitioning trong phiên bản 10. Mặc dù vậy cũng cần chú ý các hạn chế còn tồn đọng để thiết kết xử lý sao cho đúng với hệ thống của bạn.  

Quản lý log trên PostgreSQL

Chào các bạn. Bài viết này mình xin giới thiệu thêm về một chức năng cơ bản mà rất cần thiết khi thiết kế sử dụng một cơ sở dữ liệu PostgreSQL cho người sử dụng. Như tiêu đề đã nêu, đó là quản lý log.
Log là một phần không thể thiếu trong việc giải quyết các vấn đề liên quan tới database như phát hiện lock wait, deadlock, tình trạng checkpoint, autovacuum, phát hiện slow query... Thiết kế sao cho log chứa những thông tin hữu ích để giải quyết những vấn đề xảy ra trong lúc hoạt động, và làm sao để việc xuất log không ảnh hưởng nhiều tới hệ thống là một phần không thể thiếu đối với một quản trị viên cơ sở dữ liệu.

Chức năng log của PostgreSQL

Log là chức năng có sẵn của PostgreSQL, ta có thể sử dụng mà không cần cài đặt thêm module nào cả. Chức năng log của PostgreSQL lưu trữ các thao tác, lỗi khi sử dụng, vận hành, ... dưới dạng text, cho phép người dùng có thể quan sát trực tiếp. PostgreSQL có thể thiết kế để xuất log tới syslog(linux) hay event log(windows), mặc định PostgreSQL xuất log bên dưới pg_log bên dưới thư mục cơ sở dữ liệu (nếu tham số logging_collector thiết lập là on). Để quản lý đơn giản hơn thường dữ liệu log được thiết lập để xuất bên dưới thư mục cơ sở dữ liệu. Bài viết này mình cũng đưa ra những ví dụ log từ pg_log bên dưới thư mục cơ sở dữ liệu.

Chú ý khi thiết kế log

Trạng thái xuất log mặc định

Giá trị khởi tạo của tham số logging_collector là off, tức là log messages không được lưu trữ. Nhưng nếu bạn tạo database cluster theo cách bên dưới đây thì giá trị mặc định đối với tham số logging_collector cho database cluster đó là on.

  • Database được tạo ra lúc cài đặt PostgreSQL bằng (EDB) Installer trên môi trường Windows.
  • Database được tạo ra bởi initdb được cài đặt từ rpm từ community.

Chú ý khác với initdb của rpm, database cluster được tạo ra bằng cách chạy trực tiếp lệnh initdb của EDB installer (ít nhất tại thời điểm hiện tại) lại có mặc định logging_collector = off.

Một số chú ý về log của PostgreSQL khi lưu trữ bên dưới thư mục dữ liệu

  • Mặc định log của PostgreSQL tăng không có giới hạn
    Mặc định các tham số log_filenamelog_truncate_on_rotation làm cho log không có cơ chế loại bỏ được log cũ nên dung lượng log sẽ tăng không ngừng theo thời gian. Ta phải thiết kế để tránh tình trạng trên và sao cho phù hợp với yêu cầu hệ thống.
  • Không thể giới hạn được dung lượng xuất log của PostgreSQL
    Như giải thích về tham số log_rotate_size ở bên dưới. Chúng ta không thể giới hạn được dung lượng xuất log của PostgreSQL. Bởi vậy nên cần tính toán thiết kế sao cho lượng log không xuất ra quá nhiều so với mong đợi.

Các tham số liên quan tới log của PostgreSQL

PostgreSQL có rất nhiều paramters liên quan tới log, nội dung giải thích bên dưới mình chỉ tập chung vào các tham số thường được sử dụng trong vận hành và lúc tuning (tinh chỉnh). Cụ thể các tham số đó vui lòng tham khảo ở Document của PostgreSQL.

Các tham số liên quan tới PostgreSQL được chia ra 3 nhóm chính. Mình sẽ đi vào giải thích các tham số trong từng nhóm.

Trong các tham số liên quan tới log, duy có tham số logging_collector khi thay đổi cần restart server(pg_ctl restart) để có hiệu lực. Các paramter khác có thể reload server(pg_ctl reload), hoặc một vài paramter có thể thiết lập trong từng session(bằng cách sử dụng câu lệnh SET).

Nơi xuất log

tên paramter chức năng chú thích
log_destination Tham số chỉ định nơi xuất log.

PostgreSQL có 3 phương pháp chỉ định nơi xuất log, stderr: bên dưới database cluster (pg_log or log) dưới dạng text, csvlog: như stderr nhưng xuất theo định dạng csv, syslog: xuất log ra log hệ thống(linux OS), eventlog: xuất log ra event log trên môi trường Windows. Mặc định giá trị của tham số này là 'stderr'. 

Ta có thể chỉ định nhiều nơi xuất log qua dấu phân cách ",". Ví dụ log_destination = 'stderr, eventlog' # xuất log dạng text vào $PGDATA/log và eventlog trên Windows.

Thực tế để quản lý log dễ dàng hơn tham số này thường được để mặc định.

logging_collector Tham số chỉ định cho phép lưu trữ log vào file hay không.

Mặc định cho là off khi tạo database cluster bằng lệnh initdb. Khi thay đổi giá trị, cần khởi động lại server để có hiệu lực

Tham số này chỉ có hiệu quả khi log_destination là csvlog hay stderr.

log_directory Tham số chỉ định nơi lưu log khi log_destination là stderr hoặc csvlog

Từ phiên bản PostgreSQL 10 mặc định cho tham số này chuyển từ pg_log sang log. Các thư mục này nằm bên dưới thư mục cơ sở dữ liệu.

log_filename Tham số chỉ định tên của file log

Giá trị chỉ định của tham số này giống như chuẩn Open Group's ftrtime. Bạn có thể chỉ định tên file log theo ngày(postgresql-%d.log: thiết lập này sẽ lưu số lượng log file bằng số ngày của tháng là 31), thứ trong tuần(postgresql-%a.log: thiết lập này sẽ lưu trữ số lượng log file bằng số lượng ngày trong tuần là 7),.. Mặc định giá trị cho tham số này là 'postgresql-%Y-%m-%d_%H%M%S.log' tức là tên log file sẽ chứa thời gian tới đơn vị giây. Đồng nghĩa với việc số lượng log file không kiểm soát được, ta nên chỉnh lại tham số này trước khi đưa vào vận hành.

Ví dụ: với thiết lập log_filename = 'postgresql-%a.log'. Trong vận hành ta sẽ có 7 log files(1 log file cho mỗi ngày trong tuần) như bên dưới.

$ ll $PGDATA/log/
total 16
-rw-------. 1 postgres postgres    0 May  4 00:00 postgresql-Fri.log
-rw-------. 1 postgres postgres    0 May  7 00:00 postgresql-Mon.log
-rw-------. 1 postgres postgres 1039 May  5 16:07 postgresql-Sat.log
-rw-------. 1 postgres postgres    0 May  6 00:00 postgresql-Sun.log
-rw-------. 1 postgres postgres  941 May  3 18:39 postgresql-Thu.log
-rw-------. 1 postgres postgres   74 May 11 23:55 postgresql-Tue.log
-rw-------. 1 postgres postgres  718 May 16 06:31 postgresql-Wed.log
log_file_mode Tham số chỉ định quyền tham chiếu tới log file Mặc định là 0600, nghĩa là chỉ có OS user sở hữu server (user dùng để khởi động PostgreSQL) là có thể tham chiếu tới log file.
log_rotation_age Tham số chỉ định rotate log file(ghi sang log file khác khi tới ngưỡng chỉ định) theo thời gian Mặc định là một ngày, tức là cứ sau 1 ngày log file mới sẽ được tạo ra theo định dạng chỉ định trong tham số log_filename.
log_rotation_size Tham số chỉ định rotate log file(ghi sang log file khác khi tới ngưỡng chỉ định) theo kích thước Mặc định là 10MB. Tức là cứ 10MB, sẽ có log file mới được tạo ra theo định dạng trong tham số log_filename. Lưu ý rằng log file mới tạo ra theo định dạng của log_filename, nên chỉ khi tên log file mới được tạo ra quá trình rotate mới xảy ra. Điều này dẫn đến ta không thể kiểm soát được dung lượng log xuất ra
log_truncate_on_rotation Tham số chỉ định có truncate log cũ khi rotation hay không

Mặc định tham số này là off. Tức là không truncate(xóa) log cũ khi rotation thực thi. Nên dung lượng file log sẽ lớn dần theo thời gian. Nếu không có cơ chế backup log cụ thể nào nên để tham số này là on.

Chú ý rằng thời điểm thực hiện rotation dựa vào log_filename(ví dụ khi log_rotation_age=1d, log_filename='postgresql-%d.log' thì rotation sẽ diễn ra vào lúc 0 giờ hằng ngày). Tại thời điểm 0 giờ này nếu PostgreSQL đang dừng hoạt động thì xử lý truncate sẽ không diễn ra khi khởi động PostgreSQL, khi đó PostgreSQL chỉ chèn thêm log messages vào log file hiện tại.

Thời điểm xuất log

tên paramter chức năng chú thích
client_min_mesages Tham số chỉ định mức độ mà log messages sẽ trả về cho phía client(khi chạy các câu lệnh SQL)

Mặc định cho tham số này là NOTICE. Tức là chỉ những messages có mức độ từ NOTICE trở lên(NOTICE, WARNING, ERROR, FATAL, PANIC) mới trả về cho client.

postgres=# set client_min_messages to log;
SET
postgres=# set log_min_duration_statement to 0;
LOG:  duration: 0.103 ms  statement: set log_min_duration_statement to 0;
SET
postgres=# select pg_sleep(1);
LOG:  duration: 1003.193 ms  statement: select pg_sleep(1);
 pg_sleep 
----------
 
(1 row)
log_min_messages Tham số chỉ định mức độ mà log messages sẽ xuất phía server

Mặc định cho tham số này là WARNING. Tức là chỉ những messages có mức độ từ WARNING trở lên(WARNING, ERROR, LOG, FATAL, PANIC) mới được xuất. Tham số này hữu ích khi điều tra vấn đề gì đó xảy ra trên server của bạn.

$ psql
psql (11devel)
Type "help" for help.

postgres=# set log_min_messages to debug5;
SET
postgres=# select count(*) from pg_class;
 count 
-------
   343
(1 row)

postgres=# \q
$ tail -2 $PGDATA/log/postgresql-Wed.log | grep DEBUG
2018-05-16 07:42:50.299 JST [22540] DEBUG:  shmem_exit(-1): 0 on_shmem_exit callbacks to make
2018-05-16 07:42:50.299 JST [22540] DEBUG:  proc_exit(-1): 0 callbacks to make
$ 
log_min_error_statement Tham số chỉ định khi nào sẽ xuất câu lệnh SQL liên quan tới log messages hiện tại

Mặc định cho tham số này là ERROR. Tức là đối với các messages có mức độ từ ERROR trở lên (ERROR, LOG, FATAL, PANIC) khi xuất ra sẽ kèm với câu lệnh SQL tương ứng

postgres=# show log_min_error_statement;
 log_min_error_statement 
-------------------------
 error
(1 row)

postgres=# select testerror;
ERROR:	column "testerror" does not exist
LINE 1: select testerror;
	       ^
postgres=# \q
$ tail -3 $PGDATA/log/postgresql-Wed.log | grep ERROR -A2
2018-05-16 07:45:51.324 JST [22591] ERROR:  column "testerror" does not exist at character 8
2018-05-16 07:45:51.324 JST [22591] STATEMENT:	select testerror;
$ 
log_min_duration_statement Tham số chỉ định sẽ xuất log cho những SQL có thời gian thực thi lớn hơn giá trị chỉ định(đơn vị là mili giây)

Mặc định cho tham số này là -1 tức là sẽ không xuất các câu lệnh SQL đã chạy. Tham số này rất hữu ích cho việc xác định slow query trong PostgreSQL. Ví dụ: nếu thiết lập giá trị là '10s', những SQL nào chạy quá 10s sẽ được xuất ra log

postgres=# set client_min_messages to log;
SET
postgres=# set log_min_duration_statement to 100;
LOG:  duration: 0.103 ms  statement: set log_min_duration_statement to 100;
SET
postgres=# select pg_sleep(1);
LOG:  duration: 1003.193 ms  statement: select pg_sleep(1);
 pg_sleep 
----------
 
(1 row)

Nội dung log

tên paramter chức năng chú thích
log_connections Tham số chỉ định log thông tin khi kết nối.

Mặc định tham số này là off, tức là ko log khi kết nối. Thực tế khi tham số này on thì mỗi lần kết nối dung lượng log cũng không xuất ra nhiều (2 dòng), ta nên để on để phân tích log dễ dàng hơn. Tham số này chỉ chỉ định được trước khi bắt đầu session.

$ psql -c "show log_connections"
 log_connections 
-----------------
 on
(1 row)

$ tail $PGDATA/log/postgresql-Wed.log | grep -A1 "connection received"
2018-05-16 07:51:12.137 JST [22714] LOG:  connection received: host=[local]
2018-05-16 07:51:12.138 JST [22714] LOG:  connection authorized: user=postgres database=postgres
log_disconnections Tham số chỉ định log thông tin khi dữ liệu ngừng kết nối

Mặc định tham số này là off. Giống như log_connections, tham số này cũng nên thiết lập on để phân tích log dễ dàng hơn.

$ psql -c "show log_disconnections"
 log_disconnections 
--------------------
 on
(1 row)

$ tail -1 $PGDATA/log/postgresql-Wed.log
2018-05-16 07:53:49.485 JST [22754] LOG:  disconnection: session time: 0:00:00.003 user=postgres database=postgres host=[local]
log_error_verbosity Tham số chỉ định lượng log chi tiết xuất ra cho mỗi messages Giá trị có thể thiết lập là, TERSE, DEFAULT, và VERBOSE. Ở thiết lập TERSE, những thông tin DETAIL, HINT, QUERY và CONTEXT không được xuất. VERBOSE ngoài việc xuất những thông tin này ra, vị trí dòng code, tên hàm, tên file source code cũng được hiện thị. Những hệ thống có workload nhỏ có thể thiết lập lên VERBOSE để phân tích log dễ dàng hơn
log_duration Tham số cho phép xuất thời gian đã thực thi của mỗi câu lệnh SQL

Mặc định tham số này là off và chỉ có super user mới thay đổi được

postgres=# set log_duration to on;
SET
postgres=# set client_min_messages to log;
LOG:  duration: 0.075 ms
SET
postgres=# select pg_sleep(1);
LOG:  duration: 1002.341 ms
 pg_sleep 
----------
 
(1 row)

postgres=# 
log_line_prefix Tham số chỉ định tiền tố cho mỗi log messages

Tham số này có thể chỉ định theo ký tự % cho các thông tin khác nhau, cụ thể bạn có thể xem tại đây. Từ phiên bản 10 mặc định cho tham số này là '%m [%p] ', tức là thông tin về thời gian bao gồm cả mili giây(%m) và ID của tiến trình thực hiện log(%p). Thông thường để phân tích log khi có sự cố, ta thường thêm các thông tin như %d: truy cập cơ sở dữ liệu nào, %h: truy cập từ IP address nào, %u: truy cập bằng user nào, %x: trong transaction ID nào.

[postgres@localhost ~]$ psql -c "show log_line_prefix"
		     log_line_prefix			 
---------------------------------------------------------
 [%m host:%h database:%d user:%u PID:%p TransactionID:%x] 
(1 row)

[postgres@localhost ~]$ tail -1 $PGDATA/log/postgresql-Wed.log
[2018-05-16 07:57:33.815 JST host:[local] database:postgres user:postgres PID:22817 TransactionID:0] LOG:  disconnection: session time: 0:00:00.003 user=postgres database=postgres host=[local]
[postgres@localhost ~]$ 
log_statement Tham số cho phép log các lệnh SQL thực thi theo từng chủng loại chỉ định

Giá trị cho phép là off(mặc định), ddl: các câu lệnh SQL liên quan tới định nghĩa (CREATE, ALTER, DROP), mod: các câu lệnh SQL liên quan tới thay đổi dữ liệu(INSERT, UPDATE, DELETE, TRUNCATE,...), all: tất cả các câu lệnh SQL. Lưu ý thiết lập all cho tham số này có ý nghĩa tương tự như thiết lập tham số log_min_duration_statement = 0, nhưng log_min_duration_statement xuất các câu lệnh SQL khi đã thực thi xong còn log_statement log khi câu lệnh vừa được chạy(có thể chưa hoặc kết thúc). Tùy vào yêu cầu về mornitoring hệ thống, ta có thể kết hợp tham số này với log_duration hoặc chỉ sử dụng log_min_duration_statement để theo dõi các câu lệnh SQL thực thi

postgres=# set log_statement to 'ddl';
SET
postgres=# set client_min_messages to log;
SET
postgres=# create table testtbl(id integer);
LOG:  statement: create table testtbl(id integer);
CREATE TABLE
postgres=# insert into testtbl values(1);
INSERT 0 1
postgres=# drop table testtbl ;
LOG:  statement: drop table testtbl ;
DROP TABLE
postgres=#
log_temp_files Cho phép log khi có file tạm xuất ra tùy thuộc vào dung lượng file chỉ định

Đối với xử lý như hashjoin hay sort, PostgreSQL sử dụng bộ nhớ chỉ định trong tham số work_mem(cho mỗi process) để chứa dữ liệu tạm thời. Khi dữ liệu tạm thời vượt quá work_mem, PostgreSQL sẽ thực hiện sort/hashjoin trên đĩa cứng. Khi dung lượng file tạm sử dụng cho sort/hashjoin trên đĩa cứng lớn hơn log_temp_files, logger process sẽ xuất thông tin đó ra log file.

postgres=# set log_temp_files to 0;
SET
postgres=# set work_mem to 64;
SET
postgres=# show work_mem ;
 work_mem 
----------
 64kB
(1 row)

postgres=# explain analyze select * from pg_class order by relname desc;
						  QUERY PLAN					    
	   
----------------------------------------------------------------------------------------------------
-----------
 Sort  (cost=75.31..76.17 rows=342 width=262) (actual time=0.636..0.720 rows=343 loops=1)
   Sort Key: relname DESC
   Sort Method: external merge	Disk: 64kB
   ->  Seq Scan on pg_class  (cost=0.00..15.42 rows=342 width=262) (actual time=0.010..0.067 rows=34
3 loops=1)
 Planning Time: 0.862 ms
 Execution Time: 1.025 ms
(6 rows)

postgres=# \! tail -2 $PGDATA/log/postgresql-Wed.log
2018-05-16 08:10:36.530 JST [23134] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp23134.0", size 65536
2018-05-16 08:10:36.530 JST [23134] STATEMENT:	explain analyze select * from pg_class order by relname desc;
postgres=# 
log_checkpoints Cho phép log thông tin CHECKPOINT khi chức năng này thực thi Mặc định cho tham số này là off. Tham số này cần thiết khi tinh chỉnh hệ thống liên quan tới bộ nhớ đệm và tần xuất lưu lượng mỗi CHECKPOINT. Bạn có thể tham khảm thêm ở bài viết checkpoint
log_lock_waits Tham số chỉ định xuất log liên quan khi lock wait cho session đó vượt quá thời gian chỉ định trong deadlock_timeout Mặc định cho tham số này là off. Nếu on PostgreSQL sẽ xuất log liên quan cho phiên đó khi quá thời gian deadlock_timeout(mặc định 1 giây) mà chưa thực thi được lock. Trong các hệ thống có work load (bạn có thể hiểu là tần xuất thực thi SQL) lớn, tham số này rất hữu ích cho việc tìm hiểu nguyên nhân slow query do lock wait hoặc phát hiện deadlock. Bạn có thể tham khảo thêm ở bài viết về lock

Cách cấu hình setting files trên PostgreSQL

Lời mở đầu

Chào các bạn, chắc hẳn lúc mới đầu sử dụng PostgreSQL bạn đã có lúc tự hỏi phải thiết lập cấu hình từ file cấu hình nào, PostgreSQL có những file cấu hình gì. Làm như thế nào để cấu hình,.. Bài viết này sẽ cố gắng đưa ra cách nhìn tổng quan về setting files, cách thiết lập, và một số point khi thiết lập.

PostgreSQL setting files

Tới phiên bản PostgreSQL 10, PostgreSQL có những file settings như bên dưới.

PostgreSQL 10 có nhiều thay đổi về file name, parameter name,... But may mắn là tên file setting thì không có thay đổi.


DangnoMacBook-Pro:~ bocap$ ll $PGDATA | grep conf
-rw-------   1 bocap  wheel   4469 Nov  1 20:42 pg_hba.conf
-rw-------   1 bocap  wheel   1636 Sep 10 23:19 pg_ident.conf
-rw-------   1 bocap  wheel     88 Sep 10 23:19 postgresql.auto.conf
-rw-------   1 bocap  wheel  22321 Nov  1 20:45 postgresql.conf
DangnoMacBook-Pro:~ bocap$ 

File Setting Mục đích sử dụng Cách phản ảnh
pg_hba.conf File cấu hình xác thực cho PostgreSQL server. Chỉ những host được cấu hình cho phép từ file này mới được phép xác thực tới server. File này cũng cấu hình phương pháp xác thực tới server. Ví dụ: trust(không xác thực password), password(plain text password), md5(xác thực bằng password được hash md5), ident(sử dụng xác thực thông qua OS user), ... reload
pg_ident.conf File cấu hình mapping cho OS user với PostgreSQL trong trong phương pháp xác thực reload
postgresql.auto.conf Được thêm vào PostgreSQL 9.4, file này có nội dung được ghi vào khi thực hiện lệnh ALTER SYSTEM để thay đổi parameters. Khuyến cáo là không nên chỉnh sửa trực tiếp file này. reload, restart
postgresql.conf File cấu hình chính của PostgreSQL Server. reload, restart
recovery.conf sử dụng khi restore dữ liệu từ basebackup hoặc dùng trong cấu hình standby restart

Như trên có 2 cách phản ảnh parameters sau khi đã config(thay đổi giá trị paramter).

  • Reload là cách làm cho các process đọc lại file config mà không làm ảnh hưởng tới Application.

DangnoMacBook-Pro:~ bocap$ pg_ctl reload
server signaled
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "archive_command" removed from configuration file, reset to default
DangnoMacBook-Pro:~ bocap$ 

  • Restart là hình thức phản ảnh parameters bằng cách khởi động lại server. Cách này làm toàn bộ connections bị disconnect, nên phải khởi động lại Application. Nên nếu không cần thiết thì không dùng cách này.

DangnoMacBook-Pro:~ bocap$ pg_ctl restart 
waiting for server to shut down....LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down
 done
server stopped
server starting
DangnoMacBook-Pro:~ bocap$ 


pg_hba.conf

File cấu hình này có phản ảnh sau khi config bằng cách reload mà không cần restart server. Đương nhiên là restart thì các parameters này cũng được phản ảnh.


DangnoMacBook-Pro:~ bocap$ grep "^local " $PGDATA/pg_hba.conf
local   all             all                                     md5
DangnoMacBook-Pro:~ bocap$ pg_ctl reload
server signaled
LOG:  received SIGHUP, reloading configuration files
DangnoMacBook-Pro:~ bocap$ psql -c "select 1"
 ?column? 
----------
        1
(1 row)

DangnoMacBook-Pro:~ bocap$ 
DangnoMacBook-Pro:~ bocap$ grep "^local " $PGDATA/pg_hba.conf
local   all             all                                     reject
DangnoMacBook-Pro:~ bocap$ pg_ctl reload
server signaled
LOG:  received SIGHUP, reloading configuration files
DangnoMacBook-Pro:~ bocap$ psql -c "select 1"
FATAL:  pg_hba.conf rejects connection for host "[local]", user "postgres", database "postgres"
psql: FATAL:  pg_hba.conf rejects connection for host "[local]", user "postgres", database "postgres"
DangnoMacBook-Pro:~ bocap$ 

pg_ident.conf

Cũng như pg_hba.conf, nội dung file này cũng được phản ảnh sau khi reload server.

postgresql.auto.conf

Nội dung file này được phản ảnh sau khi reload server, nhưng giống như postgresql.conf có một số paramters chỉ được phản ảnh sau khi restart server. Như ví dụ bên dưới, mình thay đổi 2 parameters log_disconnections và max_connections, but chỉ có log_disconnections là thay đổi được. Còn parameter max_connections thì cần restart server mới thay đổi được.


DangnoMacBook-Pro:~ bocap$ psql
psql (9.6.5)
Type "help" for help.

postgres=#  alter system reset all;
ALTER SYSTEM
postgres=# \! cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
postgres=# show max_connections ;
 max_connections 
-----------------
 20
(1 row)

postgres=# show log_disconnections ;
 log_disconnections 
--------------------
 off
(1 row)

postgres=# alter system set max_connections to 15;
ALTER SYSTEM
postgres=# alter system set log_disconnections to on;
ALTER SYSTEM
postgres=# show log_disconnections ;
 log_disconnections 
--------------------
 off
(1 row)

postgres=# show max_connections ;
 max_connections 
-----------------
 20
(1 row)

postgres=# \q


DangnoMacBook-Pro:~ bocap$ pg_ctl reload
server signaled
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "max_connections" cannot be changed without restarting the server
LOG:  parameter "log_disconnections" changed to "on"
LOG:  configuration file "/usr/local/pgsql/pg9605/data/postgresql.auto.conf" contains errors; unaffected changes were applied
DangnoMacBook-Pro:~ bocap$ psql
psql (9.6.5)
Type "help" for help.

postgres=# show log_disconnections ;
 log_disconnections 
--------------------
 on
(1 row)

postgres=# show max_connections ;
 max_connections 
-----------------
 20
(1 row)

postgres=# \! cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
max_connections = '15'
log_disconnections = 'on'
postgres=# \q
LOG:  disconnection: session time: 0:00:33.338 user=postgres database=postgres host=[local]
DangnoMacBook-Pro:~ bocap$ 

Parameter max_connections được thay đổi sau khi server restart.


DangnoMacBook-Pro:~ bocap$ pg_ctl restart
waiting for server to shut down....LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down
 done
server stopped
server starting
DangnoMacBook-Pro:~ bocap$ LOG:  database system was shut down at 2017-11-04 22:53:11 JST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

DangnoMacBook-Pro:~ bocap$ psql
psql (9.6.5)
Type "help" for help.

postgres=# show max_connections ;
 max_connections 
-----------------
 15
(1 row)

postgres=# 

postgresql.conf

Giống như postgresql.auto.conf, cách phản của postgresql.conf cũng như vậy.
Ở postgresql.conf và postgresql.auto.conf các parameters dưới đây cần restart server để phản ánh sau khi config.


postgres=# select name,context from pg_settings where context = 'postmaster';
                name                 |  context   
-------------------------------------+------------
 allow_system_table_mods             | postmaster
 archive_mode                        | postmaster
 autovacuum_freeze_max_age           | postmaster
 autovacuum_max_workers              | postmaster
 autovacuum_multixact_freeze_max_age | postmaster
 bonjour                             | postmaster
 bonjour_name                        | postmaster
 cluster_name                        | postmaster
 config_file                         | postmaster
 data_directory                      | postmaster
 dynamic_shared_memory_type          | postmaster
 event_source                        | postmaster
 external_pid_file                   | postmaster
 hba_file                            | postmaster
 hot_standby                         | postmaster
 huge_pages                          | postmaster
 ident_file                          | postmaster
 listen_addresses                    | postmaster
 logging_collector                   | postmaster
 max_connections                     | postmaster
 max_files_per_process               | postmaster
 max_locks_per_transaction           | postmaster
 max_pred_locks_per_transaction      | postmaster
 max_prepared_transactions           | postmaster
 max_replication_slots               | postmaster
 max_wal_senders                     | postmaster
 max_worker_processes                | postmaster
 old_snapshot_threshold              | postmaster
 port                                | postmaster
 shared_buffers                      | postmaster
 shared_preload_libraries            | postmaster
 ssl                                 | postmaster
 ssl_ca_file                         | postmaster
 ssl_cert_file                       | postmaster
 ssl_ciphers                         | postmaster
 ssl_crl_file                        | postmaster
 ssl_ecdh_curve                      | postmaster
 ssl_key_file                        | postmaster
 ssl_prefer_server_ciphers           | postmaster
 superuser_reserved_connections      | postmaster
 track_activity_query_size           | postmaster
 track_commit_timestamp              | postmaster
 unix_socket_directories             | postmaster
 unix_socket_group                   | postmaster
 unix_socket_permissions             | postmaster
 wal_buffers                         | postmaster
 wal_level                           | postmaster
 wal_log_hints                       | postmaster
(48 rows)

recovery.conf

Khi server start nếu thư mục dữ liệu tồn tại file recovery.conf PostgreSQL sẽ đọc file này và thực hiện quá trình recovery như trong file recovery.conf chỉ định.
Ví dụ bên dưới mình tạo một standby và thiết lập recovery.conf để khi standby server khi khởi động trước hết sẽ restore archive log từ "/mnt/archivedir/".


DangnoMacBook-Pro:~ bocap$ pg_basebackup -h localhost -R -x -D $PGDATA.sby
LOG:  disconnection: session time: 0:00:01.258 user=postgres database= host=::1 port=63760
DangnoMacBook-Pro:~ bocap$ echo port=9606 >> $PGDATA.sby/postgresql.conf
DangnoMacBook-Pro:~ bocap$ cat >> $PGDATA.sby/recovery.conf  restore_command = 'cp /mnt/archivedir/%f "%p"'
> EOF
DangnoMacBook-Pro:~ bocap$ pg_ctl start -D $PGDATA.sby
server starting
DangnoMacBook-Pro:~ bocap$ LOG:  database system was interrupted; last known up at 2017-11-04 23:10:05 JST
LOG:  entering standby mode
LOG:  restored log file "00000001000000040000007F" from archive
LOG:  redo starts at 4/7F000060
LOG:  consistent recovery state reached at 4/7F000130
cp: /mnt/archivedir/000000010000000400000080: No such file or directory
LOG:  started streaming WAL from primary at 4/80000000 on timeline 1

DangnoMacBook-Pro:~ bocap$

Trang

Đăng kí nhận RSS - Cài đặt