Chức năng phía Server

Bài viết liên quan tới chức năng phía server

Quản lý log trên PostgreSQL

bocap gửi vào T5, 04/26/2018 - 01:01

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_filename, log_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ác đối tượng lưu trữ dữ liệu trên PostgreSQL

bocap gửi vào T2, 04/09/2018 - 22:32

Sau khi install PostgreSQL việc đầu tiên bạn phải làm là tạo ra một database cluster để thực hiện tiếp các truy vấn (SQL) khác. Để tạo một database cluster, ta sử dụng command initdb. Sau đó khởi động PostgreSQL là ta có thể bắt đầu sử dụng PostgreSQL. Bạn có băn khoăn tại sao mình lại có thể thực hiện được truy vấn, mình đang access bằng User nào truy cập vào database nào để thực hiện truy vấn, ... Bài viết này sẽ giới thiệu một số Objects chính trên PostgreSQL, cách tạo ra chúng, và cách nhìn tổng quan về các Objects lưu trữ dữ liệu trên PostgreSQL.

Một database cluster được tạo và khởi động (dựa trên thông tin bạn nhập trên wizard của installer) nếu bạn cài đặt PostgreSQL qua installer trên Windows.

Khái quát về các Objects lưu trữ trên PostgreSQL

Giống như các RDBMS khác, bảng dữ liệu là nơi PostgreSQL thực hiện lưu trữ dữ liệu. Bảng dữ liệu được lưu trữ trong đơn vị lớn hơn là database, các database nằm bên dưới đơn vị lớn nhất của PostgreSQL là database cluster. Như hình minh họa bên dưới ta còn thấy bảng dữ liệu nằm dưới schema và tablespace. Schema là đơn vị tập hợp các bảng (và một số objects khác) theo hình thức logic. Và tablespace là đơn vị của một tập hợp các bảng và indexes theo hình thức vật lý. Xin vui lòng chi xem tiết các Objects lưu trữ như bên dưới.

**Tổng quan phân cấp đối tượng trong PostgreSQL.**

**Các đối tương lưu trữ trong PostgreSQL.**

Database cluster

Là đơn vị lưu trữ lớn nhất của một PostgreSQL database server. Database cluster được tạo ra bởi câu lệnh initdb, bao gồm các files config (postgresql.conf, pg_hba.conf, ...), và tất cả các đối tượng lưu trữ đều nằm trong database cluster. Xin vui lòng xem chi tiết trong bài viết Cấu trúc thư mục PostgreSQL.

Có thể cài đặt nhiều PostgreSQL database server trên một hệ điều hành, bằng cách chỉ định các port (thiết lập trong $PGDATA/postgresql.conf) khác nhau cho mỗi server.

Database

Là đơn vị lớn sau Database cluster. Để thực hiện được câu truy vấn, bạn phải truy cập vào một database nào đó. Mặc định sau khi tạo database cluster, PostgreSQL tạo ra 3 database như bên dưới.

 
$ psql
Timing is on.
psql (10.3)
Type "help" for help.

10300 postgres@postgres=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 postgres  | bocap | UTF8     | C       | C     | 
 template0 | bocap | UTF8     | C       | C     | =c/bocap         +
           |       |          |         |       | bocap=CTc/bocap
 template1 | bocap | UTF8     | C       | C     | =c/bocap         +
           |       |          |         |       | bocap=CTc/bocap
(3 rows)

Tên Database	Mục Đích sử dụng
template0	Là template database. Không thể truy nhập và chỉnh sửa các đối tượng trong đó. Người dùng có thể tạo database mới dựa trên template0 này bằng cách chỉ định TEMPLATE trong câu lệnh "CREATE DATABASE"
template1	Là một template database. Người dùng có thể truy nhập và chỉnh sửa các đối tượng trong đó. Khi thực hiện câu lệnh "CREATE DATABASE", PostgreSQL sẽ copy template1 này để tạo database mới.
postgres	database mặc định của PostgreSQL khi tạo database cluster.

PostgreSQL cũng như các RDBMS khác, để truy nhập một database. Ta cần thông tin: tên database, tên user, password(ở chứng thực trust thì bạn không cần), số port (cho từng database cluster), hostname or IP address.

Để tạo một database ta sử dụng câu lệnh SQL "CREATE DATABASE", hoặc sử dụng createdb từ installer.

Khác với Oracle, PostgreSQL mặc định đối tượng câu lệnh SQL là chữ thường. Nếu muốn chữ in hoa vui lòng viết đối tượng trong dấu "".

Tạo database từ câu lệnh SQL

Cú pháp


10300 postgres@postgres=# \h CREATE DATABASE
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

Ví dụ


10300 postgres@postgres=# CREATE DATABASE testdb TEMPLATE template0;
CREATE DATABASE
Time: 4941.826 ms (00:04.942)
10300 postgres@postgres=# \l
                           List of databases
   Name    |  Owner   | Encoding | Collate | Ctype | Access privileges 
-----------+----------+----------+---------+-------+-------------------
 postgres  | bocap    | UTF8     | C       | C     | 
 template0 | bocap    | UTF8     | C       | C     | =c/bocap         +
           |          |          |         |       | bocap=CTc/bocap
 template1 | bocap    | UTF8     | C       | C     | =c/bocap         +
           |          |          |         |       | bocap=CTc/bocap
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

10300 postgres@postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
10300 postgres@testdb=# \d
Did not find any relations.
10300 postgres@testdb=# \c postgres 
You are now connected to database "postgres" as user "postgres".
10300 postgres@postgres=# DROP DATABASE testdb ;
DROP DATABASE

Để hiện thị thông tin của database trên psql mode, ta sử dụng meta command \l hay \l+ (chi tiết). Muốn access vào database nào đó ta sử dụng meta command "\c database".

Tạo database từ câu lệnh createdb

Cú pháp

 
$ createdb --help
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database

By default, a database with the same name as the current user is created.

Ví dụ

 
[ ~]$ createdb --template=template0 testdb
[ ~]$ psql -l
Timing is on.
                           List of databases
   Name    |  Owner   | Encoding | Collate | Ctype | Access privileges 
-----------+----------+----------+---------+-------+-------------------
 postgres  | bocap    | UTF8     | C       | C     | 
 template0 | bocap    | UTF8     | C       | C     | =c/bocap         +
           |          |          |         |       | bocap=CTc/bocap
 template1 | bocap    | UTF8     | C       | C     | =c/bocap         +
           |          |          |         |       | bocap=CTc/bocap
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

[ ~]$ dropdb testdb
[ ~]$

Schema

Là đơn vị lưu trữ bên dưới database, quản lý dữ liệu dưới dạng logic. Mặc định trong mỗi database có một schema cho người sử dụng, đó là schema public. Ta có thể tạo schema bằng câu lệnh "CREATE SCHEMA ". Đặc điểm của 1 schema như bên dưới.

Có thể sử dụng tên trùng với schema ở database khác but không trùng tên trên cùng database.
Ngoài TABLESPACE và user ra, schema có thể chứa hầu hết các đối tượng còn lại (như table, index, sequence, constraint...)
để truy cập schema ta có thể thêm tên schema vào phía trước đối tượng muốn truy cập hoặc sử dụng tham số search_path để thay đổi schema truy cập hiện tại.
Schema có thể sử dụng với các mục đích như tăng cường security, quản lý dữ liệu dễ dàng hơn.

Khác với Oracle, Schema của PostgreSQL không có liên quan tới database User.

Để tạo schema, ta phải truy cập vào một database nào đó rồi thực hiện lệnh "CREATE SCHEMA " để tạo.

Cú pháp


10300 postgres@postgres=# \h CREATE SCHEMA
Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:

    user_name
  | CURRENT_USER
  | SESSION_USER

Ví dụ:


10300 postgres@postgres=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

10300 postgres@postgres=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

10300 postgres@postgres=# create schema testscm;
CREATE SCHEMA
10300 postgres@postgres=# create table testscm.testtbl();
CREATE TABLE
10300 postgres@postgres=# \d
Did not find any relations.
10300 postgres@postgres=# set search_path to testscm ;
SET
10300 postgres@postgres=# \d
          List of relations
 Schema  |  Name   | Type  |  Owner
---------+---------+-------+----------
 testscm | testtbl | table | postgres
(1 row)

10300 postgres@postgres=# \dn
  List of schemas
  Name   |  Owner
---------+----------
 public  | postgres
 testscm | postgres
(2 rows)

10300 postgres@postgres=#

TABLESPACE

Là đơn vị lưu trữ dữ liệu về phương diện vật lý bên dưới database. Thông thường dữ liệu vật lý được lưu trữ tại thư mục dữ liệu (nơi ta chỉ định lúc ta tạo database cluster initdb -D). Nhưng có một phương pháp lưu trữ dữ liệu ngoài phân vùng này, nhờ sử dụng chức năng TABLESPACE. Khi tạo một TABLESPACE tức là ta đã tạo ra một vùng lưu trữ dữ liệu mới độc lập với dữ liệu bên dưới thư mục dữ liệu. Điều này giảm thiểu được disk I/O cho phân vùng thư mục dữ liệu (nếu trong các hệ thống cấu hình RAID, hay hệ thống có 1 đĩa cứng thì không có hiệu quả).

Cú pháp


10300 postgres@postgres=# \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

10300 postgres@postgres=#

Ví dụ


10300 postgres@postgres=# \! mkdir /tmp/testtablespace
10300 postgres@postgres=# create tablespace testtps location '/tmp/testtablespace';
CREATE TABLESPACE
10300 postgres@postgres=# \! ls -l /tmp/testtablespace/*
total 0
10300 postgres@postgres=# set default_tablespace to testtps;
SET
10300 postgres@postgres=# create table testtbl as select generate_series(1,10) as id;
SELECT 10
10300 postgres@postgres=# checkpoint;
CHECKPOINT
drwx------. 2 postgres postgres 18 Apr 15 13:59 13211
10300 postgres@postgres=# \! ls -l /tmp/testtablespace/*/*
total 8
-rw-------. 1 postgres postgres 8192 Apr 15 13:59 16389
10300 postgres@postgres=# select oid,* from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 16388 | testtps    |       10 |        |
(3 rows)

10300 postgres@postgres=# \! ls -l $PGDATA/pg_tblspc/16388
lrwxrwxrwx. 1 postgres postgres 19 Apr 15 13:58 /usr/local/pgsql/pg1000/data/pg_tblspc/16388 -> /tmp/testtablespace
10300 postgres@postgres=#

PostgreSQL tạo một symblic link có tên là oid (Object ID) của tablespace vừa được tạo ở bên dưới pg_tblspc của database cluster. Trỏ tới vùng dữ liệu được chỉ định (LOCATION) trong lúc tạo tablespace.

Để chuyển đổi qua các tablespace để lưu trữ dữ liệu, ta chỉ định thông qua parameter default_tablespace trước khi tạo đối tượng (bảng hoặc index). Ngoài ra đối với bảng tạm thời (temporary table) và index của nó, để chuyển đổi vùng lưu trữ ta thiết lập tablespace cho tham số temp_tablespaces trước khi tạo đối tượng. default_tablespace và temp_tablespaces đều có giá trị mặc định là không thiết lập, tức là sử dụng tablespace mặc định bên dưới database cluster.

ví dụ trên sử dụng meta command ! của psql, meta command này thực hiện các lệnh shell bên ngoài.

Bảng(table)

Bảng là đối tượng lưu trữ dữ liệu từ người dùng. Một bảng bao gồm 0 hoặc nhiều cột (column) tương ứng với từng kiểu dữ liệu khác nhau của PostgreSQL.
Tổng quan có 3 kiểu tables mà PostgreSQL support, đó là

unlogged table: là kiểu table mà các thao tác đối với bảng dữ liệu này không được lưu trữ vào WAL. Tức là không có khả năng phục hồi nếu bị corrupt.
temporary table: là kiểu table chỉ được tạo trong phiên làm việc đó. Khi connection bị ngắt, nó sẽ tự động mất đi.
table thông thường: Khác với 2 kiểu table trên, là loại table thông thường để lưu trữ dữ liệu. Có khả năng phục hồi khi bị corrupt và tồn tại vĩnh viễn nếu không có thao tác xóa bỏ nào.

PostgreSQL support nhiều kiểu dữ liệu, bao gồm cả kiểu dữ liệu do người dùng định nghĩa. Để biết thêm chi tiết vui lòng thao khảo ở đây

Cú pháp tạo


postgres=# \h create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ] FOR VALUES partition_bound_spec
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and like_option is:

{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES | STORAGE | COMMENTS | ALL }

and partition_bound_spec is:

IN ( { numeric_literal | string_literal | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )
  TO ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

Ví dụ:


postgres=#
postgres=# create unlogged table test_unlog_tbl as select generate_series(1,10) as id;
SELECT 10
postgres=# create temporary table test_temp_tbl as select generate_series(1,10) as id;
SELECT 10
postgres=# create table test_nomarl_tbl as select generate_series(1,10) as id;
SELECT 10
postgres=# \d
               List of relations
  Schema   |      Name       | Type  |  Owner
-----------+-----------------+-------+----------
 pg_temp_3 | test_temp_tbl   | table | postgres
 public    | test_nomarl_tbl | table | postgres
 public    | test_unlog_tbl  | table | postgres
(3 rows)

postgres=# \q
[postgres@localhost ~]$ psql
psql (10.3)
Type "help" for help.

postgres=# \d
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | test_nomarl_tbl | table | postgres
 public | test_unlog_tbl  | table | postgres
(2 rows)

postgres=#

index

Index là đối tượng chỉ mục của một cột nào đó trong bảng, đối tượng này đã được sắp xếp theo một trình tự nào đó khi được sử dụng có thể làm tăng khả năng tìm kiếm.
PostgreSQL hiện tại(tới phiên bản 10) support 6 loại index btree, hash, gist, spgist, gin, and brin. Thường thì btree và hash là 2 loại index được sử dụng rộng rãi. Các index còn lại được sử dụng cho từng mục đích khác nhau, ví dụ gist và spgist cho kiểu dữ liệu liên quan tới vị trí địa lý, gin cho kiểu dữ liệu text tìm kiếm với dữ liệu lớn, brin được thiết kế để xử lý các bảng lớn, trong đó các cột nhất định có một số tương quan tự nhiên với vị trí thực của chúng trong bảng.

Cú pháp tạo


postgres=# \h create index
Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

postgres=#

Ví dụ


postgres=# create index test_nomarl_tbl_idx on test_nomarl_tbl using btree (id);
CREATE INDEX
postgres=# create index test_nomarl_tbl_hash_idx on test_nomarl_tbl using hash (id);
CREATE INDEX
postgres=# \d test_nomarl_tbl
          Table "public.test_nomarl_tbl"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
Indexes:
    "test_nomarl_tbl_hash_idx" hash (id)
    "test_nomarl_tbl_idx" btree (id)
Tablespace: "testtps"

postgres=#

Chi tiết về các loại index xin tìm hiểu ở đây: https://www.postgresql.org/docs/current/static/internals.html

PostgreSQL 10 - Logical replication

bocap gửi vào T2, 11/27/2017 - 11:50

PostgreSQL 10 mới được release tháng 10 năm 2017 vừa qua, nổi bật với 2 tính năng mới được kỳ vọng là Logical Replication và Declarative Partitioning (ngoài ra cũng có nhiều cải thiện và thay đổi), bài viết này mình muốn giới thiệu qua về chức năng Logical Replication và cách sử dụng trong thực tế.

PostgreSQL Replication

Chức năng Replication của PostgreSQL bắt đầu được giới thiệu ở phiên bản PostgreSQL 9.0 dựa trên kỹ thuật chuyển WAL(Transaction log) qua standby. Chức năng này dần được cải thiện qua các phiên bản và gần đây nhất là chức năng Logical Replication trên phiên bản 10. Các phiên bản của chức năng Replication của PostgreSQL bạn có thể tham khảo ở bài viết chức năng replication của PostgreSQL.

Logical Replication

Logical Replication được thêm vào nhờ một số nhà phát triển PostgreSQL từ công ty 2ndQuadrant dựa trên logic của module pglogical (cũng được phát triển bởi công ty này).
Trước phiên bản logical replication, PostgreSQL cũng đã thực hiện replication qua hình thức chuyển thông tin thay đổi thông qua gửi WAL tới standby. Nhưng ở những phiên bản trước, WAL chuyển qua ở mức độ chi tiết thấp, standby thực hiện phản ảnh lên DB gần như ở mức độ vật lý (đồng bộ các file dữ liệu).
Logical Replication sử dụng WAL ở mức độ chi tiết hơn, có thể decode được thành các câu lệnh SQL (phải sử dụng module ngoài để decode). Phía master (wal sender process) decode và thực hiện ghi lại WAL trước khi gửi tới standby node, phía standby sau đó sử dụng WAL nhận được và phản ảnh vào dữ liệu phía mình ở trạng thái logic thông qua logical worker process.
Logical Replication sử dụng 2 khái niệm mới, PUBLICATION và SUBSCRIPTION. PUBLICATION hiểu đơn giản là nơi phát dữ liệu cập nhật, và SUBSCRIPTION là nơi nhận cập nhật và phản ảnh vào database phía standby.

Thiết lập paramters

Để sử dụng chức năng này. Ta cần chỉnh wal_level (mức độ chi tiết của WAL) lên mức có thể decode được "replica", mỗi một cặp Publication & Subscription cần 1 wal sender processes và một slot chứa wal cho standby ta phải tăng parameters max_wal_senders, max_replication_slots lên giá trị cần thiết.

Parameter	Giá trị thiết lập
wal_level	replica
max_wal_senders	số lượng SUBSCRIPTION + α (cho các kết nối replication khác)
max_replication_slots	số lượng SUBSCRIPTION + α (cho các kết nối replication khác)

Ngoài ra, để SUBSCRIPTION có thể connect tới PUBLICATION, ta phải thiết lập authentication cho kết nối replication. Chú ý, không giống với streaming replication, những thiết lập bên trên chỉ cần cho phía PUBLICATION.

Sử dụng Logical Replication

Để sử dụng chức năng Logical Replication, ta cần 2 database cluster riêng biệt cho PUBLICATION và SUBSCRIPTION. Cả 2 database cluster này đều phải hỗ trợ ghi dữ liệu (không giống streaming replication, dữ liệu chỉ có thể ghi ở phía primary).

Thiết lập parameters
Phía PUBLICATION Server thiết lập các tham số yêu cầu như ở trên.

DangnoMacBook-Pro:postgres bocap$ grep -e max_replication_slots -e max_wal_senders -e wal_level $PGDATA/postgresql.conf
wal_level = logical     # minimal, replica, or logical
#max_wal_senders = 10   # max number of walsender processes
#max_replication_slots = 10 # max number of replication slots
DangnoMacBook-Pro:postgres bocap$ grep replication $PGDATA/pg_hba.conf | grep -v "#"
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
DangnoMacBook-Pro:postgres bocap$

Tạo PUBLICATION
Ví dụ bên dưới tạo PUBLICATION đối với một bảng dữ liệu(test_logical_rep). Ta cũng có thể tạo PUBLICATION ở các mức độ nhiều bảng hoặc tất cả các bảng.

10000 postgres@postgres=#  create table test_logical_rep(c1 integer primary key, c2 text);
CREATE TABLE
10000 postgres@postgres=# insert into test_logical_rep select generate_series(1,5),random()::text;
INSERT 0 5
10000 postgres@postgres=# table test_logical_rep
postgres-# ;
 c1 |         c2         
----+--------------------
  1 | 0.709808008279651
  2 | 0.426817617379129
  3 | 0.973640063311905
  4 | 0.729786423034966
  5 | 0.0786650250665843
(5 rows)

10000 postgres@postgres=# \h create publication
Command:     CREATE PUBLICATION
Description: define a new publication
Syntax:
CREATE PUBLICATION name
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
      | FOR ALL TABLES ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ]

10000 postgres@postgres=# create publication test_first_pub for table test_logical_rep ;
CREATE PUBLICATION
10000 postgres@postgres=#

Tạo SUBSCRIPTION
Ví dụ bên dưới tạo SUBSCRIPTION đối với PUBLICATION đã tạo ở 2. để đồng bộ bảng dữ liệu test_logical_rep từ database phía PUBLICATION sang phía SUBSCRIPTION.

10001 bocap@postgres=# create table test_logical_rep(c1 integer primary key, c2 text);
CREATE TABLE
10001 bocap@postgres=# \h create subscription
Command:     CREATE SUBSCRIPTION
Description: define a new subscription
Syntax:
CREATE SUBSCRIPTION subscription_name
    CONNECTION 'conninfo'
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

10001 bocap@postgres=# create subscription test_first_sub connection 'port=10000 dbname=postgres user=bocap' publication test_first_pub;
NOTICE:  created replication slot "test_first_sub" on publisher
CREATE SUBSCRIPTION
10001 bocap@postgres=# select * from test_logical_rep ;
 c1 |         c2         
----+--------------------
  1 | 0.709808008279651
  2 | 0.426817617379129
  3 | 0.973640063311905
  4 | 0.729786423034966
  5 | 0.0786650250665843
(5 rows)

10001 bocap@postgres=#

Mặc định Logical Replication thực hiện đồng bộ dữ liệu ở chế độ không đồng bộ (Async). Nếu muốn thực hiện đồng bộ dữ liệu ở chế độ đồng bộ (Sync), bạn cần:

Chỉ định tên của Subscription qua tham số application_name trong thông tin connection khi CREATE SUBSCRIPTION.

Chỉ đinh tên của Subscription này trong tham số synchronous_standby_names (postgresql.conf) phía Publication.

Thao tác cơ bản

-- Insert dữ liệu phía PUBLICATION  
10000 postgres@postgres=# insert into test_logical_rep select 6,random()::text;
INSERT 0 1
10000 postgres@postgres=# table  test_logical_rep;
 c1 |         c2         
----+--------------------
  1 | 0.709808008279651
  2 | 0.426817617379129
  3 | 0.973640063311905
  4 | 0.729786423034966
  5 | 0.0786650250665843
  6 | 0.50799278402701
(6 rows)

10000 postgres@postgres=#  

-- Xác nhận dữ liệu cập nhật phía SUBSCRIPTION   
10001 bocap@postgres=# table  test_logical_rep;
 c1 |         c2         
----+--------------------
  1 | 0.709808008279651
  2 | 0.426817617379129
  3 | 0.973640063311905
  4 | 0.729786423034966
  5 | 0.0786650250665843
  6 | 0.50799278402701
(6 rows)

-- Cập nhật dữ liệu phía PUBLICATION  
10000 postgres@postgres=# update test_logical_rep set c2 = random()::text where c1 = 6;
UPDATE 1
10000 postgres@postgres=# delete from test_logical_rep where c1 = 6;
DELETE 1
10000 postgres@postgres=# table  test_logical_rep;
 c1 |         c2         
----+--------------------
  1 | 0.709808008279651
  2 | 0.426817617379129
  3 | 0.973640063311905
  4 | 0.729786423034966
  5 | 0.0786650250665843
(5 rows)

-- Xác nhận thông tin cập nhật phía SUBSCRIPTION  
10001 bocap@postgres=# table  test_logical_rep;
 c1 |         c2         
----+--------------------
  1 | 0.709808008279651
  2 | 0.426817617379129
  3 | 0.973640063311905
  4 | 0.729786423034966
  5 | 0.0786650250665843
(5 rows)

Chú ý

Câu lệnh TRUNCATE chưa support

-- Truncate phía PUBLICATION   
10000 postgres@postgres=# truncate test_logical_rep ;
TRUNCATE TABLE
10000 postgres@postgres=# table test_logical_rep;
 c1 | c2 
----+----
(0 rows)

-- Xác nhận cập nhật phía SUBSCRIPTION  
10001 bocap@postgres=# table  test_logical_rep;
 c1 |        c2         
----+-------------------
  1 | 0.709808008279651
  2 | 0.426817617379129
  3 | 0.973640063311905
  4 | 0.729786423034966
  5 | 0.918787303380668
(5 rows)

Conflict có thể xảy ra
Khác với streaming replication, phía SUBSCRIPTION cũng có thể thực hiện các câu lệnh update. Thêm nữa là thao tác giữa PUBLICATION và SUBSCRIPTION là độc lập, không support cơ chế MVCC. Vậy nên dữ liệu có thể xảy ra conflict nếu thao tác xử lý trên SUBSCRIPTION không nhất quán với phía PUBLICATION.

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

bocap gửi vào CN, 11/05/2017 - 00:06

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$

Backup dữ liệu trên PostgreSQL

bocap gửi vào T4, 11/01/2017 - 20:49

Tản mạn: Tối hôm trước làm về sớm, thằng cùng chỗ làm rủ qua quán sashimi thịt ngựa. Không biết ăn thịt but đi ăn cùng cho vui. Nhắm mắt nuốt được vài miếng chấm toàn mù tạc xong tối về đau bụng cả đêm. Đúng là cái gì cũng phải có lửa mới hiệu quả được.
Xin lỗi các bạn thời gian vừa qua blog PostgreSQL Việt Nam hơi thiếu lửa.

Backup dữ liệu trên PostgreSQL

Hôm nay bocap sẽ tái khởi động với bài viết các cách thức backup dữ liệu trên PostgreSQL.
Có một số cách phân but về tổng quan backup trên PostgreSQL được chia làm 2 loại "offline backup", "online backup". Ngoài ra phân loại chi tiết hơn thì có thêm backup vật lý(backup data files) và backup logic(backup dữ liệu qua SQL). Đối với từng loại backup có những đặc điểm tốt xấu riêng. Hãy tìm hiểu và lựa chọn phương pháp phù hợp với môi trường của bạn.

Offline backup

Là hình thức backup dữ liệu khi PostgreSQL Server đã dừng hoạt động. Nên phương pháp này chỉ có thể backup theo phương pháp vật lý.
Sau khi dừng hoạt động từ server (pg_ctl stop -mf), chúng ta copy cả thư mục dữ liệu (database cluster). Nếu có sử dụng tablespace để lưu trữ dữ liệu ngoài thư mục dữ liệu thì phải backup cả những thư mục đó.
Đặc điểm của hình thức backup này.

Ảnh hưởng tới hệ thống: Phải stop database trước khi backup.
Phạm vi backup: Toàn bộ database cluster.
Thời điểm phục hồi khi restore: Thời điểm thực hiện sao lưu.

Chú ý:

Nếu database cluster sử dụng tính năng lưu trữ dữ liệu bên ngoài database cluster (tablespace), hoặc lưu trữ WAL ngoài database cluter, khi backup cũng phải backup các đối tượng này.


[bocap@localhost ~]$ pg_ctl stop -mf
waiting for server to shut down.... done
server stopped
[bocap@localhost ~]$ mkdir /mnt/backup/data_`date +"%m-%d-%y"`
[bocap@localhost ~]$ ls -l /mnt/backup/
合計 0
drwxrwxr-x. 2 bocap bocap 6 11月  1 12:50 data_11-01-17
[bocap@localhost ~]$ tar cvfz /mnt/backup/data_11-01-17/data.tar.gz $PGDATA 
[bocap@localhost ~]$ ll  /mnt/backup/data_11-01-17/
合計 130956
-rw-rw-r--. 1 bocap bocap 134096110 11月  1 12:53 data.tar.gz
[bocap@localhost ~]$

Online backup

Là hình thức backup dữ liệu khi PostgreSQL vẫn đang hoạt động và cho phép ảnh hưởng tới môi trường Database(ví dụ: ACCESS SHARE lock từ pg_dump hay ảnh hưởng I/O từ backup) của bạn ở một mức độ cho phép.
Hình thức backup này có thể thực hiện bởi cả 2 phương pháp backup vật lý và logic.

Online logic backup

Sử dụng lệnh pg_dump (backup theo đơn vị bảng, schema, .., database), hoặc pg_dumpall (backup toàn bộ dữ liệu của database cluster) để backup dữ liệu. Các câu lệnh này sẽ connect tới PostgreSQL và lấy dữ liệu theo dạng logic (SQL) và lưu trữ dưới dạng text hoặc tar(zip). Các câu lệnh này cũng restore dữ liệu về thời điểm chạy câu lệnh pg_dump/pg_dumpall.
Bắt đầu từ phiên bản PostgreSQL 9.3 pg_dump/pg_restore(9.2~) có thêm chức năng backup/restore dữ liệu song song (sử dụng nhiều connections). Trong những môi trường nhiều CPU và Disk có tính năng tốt có thể sử dụng chức năng này một cách hiệu quả.
Đặc điểm của hình thức backup này.

Ảnh hưởng tới hệ thống: Có thể backup khi database đang hoạt động.
Phạm vi backup: bảng, schema, ..database hoặc toàn bộ database cluster.
Thời điểm phục hồi khi restore: Thời điểm thực hiện sao lưu.

Chú ý:

pg_dump/pg_dumpall sẽ lần lượt lock tất cả các đối tượng backup (tables) ở chế độ AccessShareLock, nên cần chú ý khi thực hiện các thao tác có lock xung đột. Tham khảo thêm PostgreSQL Locks
Backup với định dạng text có thể restore bằng psql. Ở các định dạng khác, ta restore bằng lệnh pg_restore.
Với option -Z, pg_dump có thể nén dữ liệu backup với nhiều mức độ (0-9, mặc định là 6). But khi mức độ nén cao tỷ lệ CPU cũng sẽ cao theo. Nên cân đối hợp lý giữa CPU và dung lượng lưu trữ.


DangnoMacBook-Pro:~ bocap$ psql -c "select * from testtbl"
 id 
----
  1
(1 row)

DangnoMacBook-Pro:~ bocap$ pg_dump --table testtbl -f /tmp/testdump
DangnoMacBook-Pro:~ bocap$ cat /tmp/testdump | grep -v -e SET -e "\-" -e "^$"
CREATE TABLE testtbl (
    id integer
);
ALTER TABLE testtbl OWNER TO postgres;
COPY testtbl (id) FROM stdin;
1
\.
DangnoMacBook-Pro:~ bocap$

Online backup vật lý

Là cách tạo 1 basebackup (sử dụng lệnh pg_basebackup hoặc function pg_start_backup/pg_stop_backup) kết hợp với việc xuất archive log (transaction log được lưu trữ thông qua parameter archive_command). Khi restore ta tạo file recovery.conf trong thư mục data (basebackup), điền nội dung restore WAL từ đâu (parameter restore_command) sau đó khởi động database từ thư mục basebackup.
Dữ liệu backup sẽ được phục hồi tới thời điểm archive_command chạy lần cuối cùng (lệnh chỉ định trong archive_command sẽ được chạy khi dung lượng transaction đủ 16MB, hoặc quá thời gian chỉ định trong archive_timeout từ lần archive trước).
Đặc điểm của hình thức backup này.

Ảnh hưởng tới hệ thống: Có thể backup khi database đang hoạt động.
Phạm vi backup: bảng, schema, ..database hoặc toàn bộ database cluster.
Thời điểm phục hồi khi restore: Thời điểm thực hiện sao lưu.

Chú ý:

Xử lý tạo một basebackup giống như copy hoàn toàn một database cluster. Nên nếu dữ liệu lớn sẽ mất nhiều thời gian.
Khi pg_basebackup chạy lệnh này sẽ thực hiện một CHECKPOINT, mặc định CHECKPOINT này chạy ở chế độ chậm spread (có thể chậm hơn so với thông thường = checkpoint_completion_target * checkpoint_timeout = 2.5 phút). Nên cất nhắc chạy ở chế độ nhanh (-c fast) nếu muốn pg_basebackup diễn ra nhanh hơn. But cũng nên chú ý workload lớn khi CHECKPOINT nhanh có thể ảnh hưởng tới hệ thống database.
pg_basebackup mặc định không copy WAL (transaction log) sinh ra trong lúc chạy lệnh. Sử dụng option -x nếu muốn tạo một pg_basebackup sử dụng trong cấu hình replication.


DangnoMacBook-Pro:~ bocap$ grep replication $PGDATA/pg_hba.conf | grep -v "#"
host    replication     postgres        127.0.0.1/32            trust
host    replication     postgres        ::1/128                 trust
DangnoMacBook-Pro:~ bocap$ grep -e max_wal_senders -e wal_level -e archive_ $PGDATA/postgresql.conf
wal_level = replica			# minimal, replica, or logical
archive_mode = on		# enables archiving; off, on, or always
archive_command = 'test ! -f /mnt/archivedir/%f && cp %p /mnt/archivedir/%f'
archive_timeout = 30s		# force a logfile segment switch after this
max_wal_senders = 10		# max number of walsender processes
#max_standby_archive_delay = 30s	# max delay before canceling queries
DangnoMacBook-Pro:~ bocap$ ll /mnt/archivedir/
DangnoMacBook-Pro:~ bocap$ ll /mnt/data
DangnoMacBook-Pro:~ bocap$ pg_basebackup -D /mnt/data -h localhost
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
DangnoMacBook-Pro:~ bocap$ ll /mnt/data 
total 96
-rw-------   1 bocap  wheel      4 Nov  1 20:45 PG_VERSION
-rw-------   1 bocap  wheel    208 Nov  1 20:45 backup_label
drwx------   5 bocap  wheel    170 Nov  1 20:45 base
drwx------  56 bocap  wheel   1904 Nov  1 20:45 global
drwx------   3 bocap  wheel    102 Nov  1 20:45 pg_clog
drwx------   2 bocap  wheel     68 Nov  1 20:45 pg_commit_ts
drwx------   2 bocap  wheel     68 Nov  1 20:45 pg_dynshmem
-rw-------   1 bocap  wheel   4469 Nov  1 20:45 pg_hba.conf
-rw-------   1 bocap  wheel   1636 Nov  1 20:45 pg_ident.conf
drwx------   4 bocap  wheel    136 Nov  1 20:45 pg_logical
drwx------   4 bocap  wheel    136 Nov  1 20:45 pg_multixact
drwx------   3 bocap  wheel    102 Nov  1 20:45 pg_notify
drwx------   2 bocap  wheel     68 Nov  1 20:45 pg_replslot
drwx------   2 bocap  wheel     68 Nov  1 20:45 pg_serial
drwx------   2 bocap  wheel     68 Nov  1 20:45 pg_snapshots
drwx------   2 bocap  wheel     68 Nov  1 20:45 pg_stat
drwx------   2 bocap  wheel     68 Nov  1 20:45 pg_stat_tmp
drwx------   3 bocap  wheel    102 Nov  1 20:45 pg_subtrans
drwx------   2 bocap  wheel     68 Nov  1 20:45 pg_tblspc
drwx------   2 bocap  wheel     68 Nov  1 20:45 pg_twophase
drwx------   3 bocap  wheel    102 Nov  1 20:45 pg_xlog
-rw-------   1 bocap  wheel     88 Nov  1 20:45 postgresql.auto.conf
-rw-------   1 bocap  wheel  22320 Nov  1 20:45 postgresql.conf
DangnoMacBook-Pro:~ bocap$ ll /mnt/archivedir/
total 65544
-rw-------  1 bocap  wheel  16777216 Nov  1 20:45 000000010000000000000014
-rw-------  1 bocap  wheel  16777216 Nov  1 20:45 000000010000000000000015
-rw-------  1 bocap  wheel       305 Nov  1 20:45 000000010000000000000015.00000028.backup
DangnoMacBook-Pro:~ bocap$

Một số lưu ý khi backup

Khi backup, đối với từng hệ thống khác nhau, sẽ có những yêu cầu khác nhau. Thường khi backup users cần để ý tới những quan điểm bên dưới để đối ứng với yêu cầu hệ thống.

Cần restore dữ liệu về thời điểm nào.
Ví dụ: Khi hệ thống bị trouble, muốn restore về thời điểm gần nhất thì nên sử dụng phương pháp Online backup vật lý.
Dung lượng dữ liệu backup.
Ví dụ: Cần backup dữ liệu với dung lượng thấp thì nên sử dụng Online logic backup
Mức độ ảnh hưởng tới hệ thống.
Ví dụ: Tính năng ổ cứng không tốt, không muốn xuất archive log để giảm Disk I/O thì không nên sử dụng phương pháp Online backup vật lý.

VACUUM

bocap gửi vào T7, 08/26/2017 - 18:54

VACUUM và chức năng autovacuum

Tại sao PostgreSQL cần VACUUM ?

Dung lượng đĩa cứng có thể full
Khác với các RDBMS khác (như MySQL), khi người dùng chạy lệnh DELETE hay UPDATE, PostgreSQL không xoá dữ liệu cũ đi luôn mà chỉ đánh dấu "đó là dữ liệu đã bị xoá".
Nên nếu liên tục INSERT/DELETE hoặc UPDATE dữ liệu mà không có cơ chế xoá dữ liệu dư thừa thì dung lượng ổ cứng tăng dẫn đến full.
Lỗi (dữ liệu bị vô hiệu) khi Wraparound Transaction ID
PostgreSQL sử dụng 32 bit Transaction ID (XID) để quản lý transaction(1). Mỗi một record dữ liệu đều có thông tin về XID. Khi dữ liệu được tham chiếu PostgreSQL sử dụng thông tin XID này so sánh với XID hiện tại để đánh giá dữ liệu này có hữu hiệu không. Dữ liệu đang tham chiếu có XID lớn hơn XID hiện tại là dữ liệu không hữu hiệu. Khi sử dụng hết 32 bit XID (khoảng 4 tỷ transactions), để sử dụng tiếp XID sẽ được reset về ban đầu (0). Nếu không có cơ chế chỉnh lại XID trong data thì mỗi lần reset XID, dữ liệu hiện tại sẽ trống trơn (dữ liệu hiện tại luôn có XID lớn hơn XID đã reset (0)).

Một trong những chức năng của VACUUM là quyết những vấn đề như trên.

(1) transaction(Giao dịch cơ sở dữ liệu): Định nghĩa từ wikipedia: Giao dịch cơ sở dữ liệu (database transaction) là đơn vị tương tác của một hệ quản lý cơ sở dữ liệu hoặc các hệ tương tự, mỗi giao dịch được xử lý một cách nhất quán và tin cậy mà không phụ thuộc vào các giao dịch khác. Một hệ cơ sở dữ liệu lý tưởng sẽ phải bảo đảm toàn bộ các tính chất ACID cho mỗi giao dịch. Trên thực tế, các tính chất này thường được nới lỏng để giúp việc thực thi đạt hiệu quả hơn.

PostgreSQL không thực hiện xóa dữ liệu ngay sau khi DELETE hoặc UPDATE nhằm giải quyết vấn đề cùng một lúc nhiều người sử dụng truy cập một vùng dữ liệu (PostgreSQL gọi cơ chế đó là Multiversion Concurrency Control hay MVCC). Theo thông tin gần đây từ cộng đồng PostgreSQL, từ phiên bản 12(?) PostgreSQL có thể bỏ chức năng VACUUM nhờ sử dụng cơ chế lưu trữ zheap.

VACUUM thực hiện những công việc gì

Lấy lại dữ liệu dư thừa để tái sử dụng
Cập nhật thông tin thống kê (statistics)
Giải quyết vấn đề dữ liệu bị vô hiệu khi Wraparound Transaction ID

Lấy lại dữ liệu dư thừa

Như trên, PostgreSQL chưa xoá dữ liệu cũ khi thực hiện thao tác DELETE/UPDATE. Khi VACUUM, những dữ liệu dư thừa đó sẽ được lấy lại và vị trí dư thừa sẽ được cập nhật lại trong bảng vị trí trống (Free Space Map(FSM)). Ngoài ra những block dữ liệu đã được VACUUM sẽ được đánh dấu là đã VACUUM trên bảng khả thị (Visibility Map(VM)), khi UPDATE/DELETE dữ bảng khả thị sẽ cập nhật lại trạng thái là cần VACUUM.

Free Space Map(FSM): Mỗi bảng dữ liệu (hoặc index) tồn tại tương ứng một FSM. FSM chứa thông tin các vị trí trống trong file dữ liệu. Khi dữ liệu mới được ghi PostgreSQL sẽ nhìn vị trí trống từ FSM trước, việc này giảm thiểu truy cập trực tiếp (sinh I/O disk) vào file dữ liệu. File FSM nằm cùng vị trí với file dữ liệu và có tên = file_dữ_liệu_fsm (như ví dụ dưới).
Visibility Map(VM): Mỗi bảng dữ liệu tồn tại tương ứng một visibility map (VM). Một block dữ liệu tương ứng với 1 bit trên VM. VACUUM xem trước thông tin VM của bảng dữ liệu, và chỉ thực hiện trên những block cần được VACUUM. File VM nằm cùng vị trí với file dữ liệu và có tên = file_dữ_liệu_vm (như ví dụ dưới).


testdb=# create table testtbl(id integer);
CREATE TABLE
testdb=# insert into testtbl select generate_series(1,100);
INSERT 0 100
testdb=# delete from testtbl where id < 90;
DELETE 89
testdb=# checkpoint;
CHECKPOINT
testdb=# \! ls -l $PGDATA/base/16384/24576*
-rw------- 1 bocap staff 8192 Jun 24 18:19 /Users/bocap/Downloads/pg94/data/base/16384/24576
-rw------- 1 bocap staff 24576 Jun 24 18:19 /Users/bocap/Downloads/pg94/data/base/16384/24576_fsm
-rw------- 1 bocap staff 8192 Jun 24 18:19 /Users/bocap/Downloads/pg94/data/base/16384/24576_vm

Cập nhật lại thông tin thống kê

Một số bạn lầm tưởng rằng chỉ có chức năng ANALYZE mới cập nhật lại thông tin thống kê dùng bởi planner, nhưng thực tế VACUUM cũng cập nhật thông tin này. Cụ thể là relpages và reltuples trong system catalog pg_class. Do vậy nên có thể nói VACUUM cũng ảnh hưởng tới việc chọn plan thực thi.


postgres=# select relpages,reltuples from pg_class where relname = 'testtbl';
 relpages | reltuples 
----------+-----------
        0 |         0
(1 row)

postgres=# insert into testtbl select generate_series(1,10),random()::text;
INSERT 0 10
postgres=# select relpages,reltuples from pg_class where relname = 'testtbl';
 relpages | reltuples 
----------+-----------
        0 |         0
(1 row)

postgres=# vacuum testtbl;
VACUUM
postgres=# select relpages,reltuples from pg_class where relname = 'testtbl';
 relpages | reltuples 
----------+-----------
        1 |        10
(1 row)

autovacuum

autovacuum là chức năng tự động thực thi VACUUM hoặc ANALYZE khi cần thiết. Chức năng này hoạt động khi tham số autovacuum và track_counts thiết lập là on. Cả 2 tham số này đều mặc định là on nên autovacuum sẽ tự động hoạt động khi khởi động PostgreSQL. Khi tham số autovacuum là on. Sau khi khởi động PostgreSQL process "autovacuum launcher process" sẽ đảm nhận việc này.


BocapnoMacBook-Pro:postgres bocap$ ps -ef | grep autovacuum | grep  -v grep
501  3169  3164   0 13Aug17 ??         0:03.13 postgres: autovacuum launcher process

Launcher process cứ mỗi autovacuum_naptime sẽ kiểm tra thông tin thống kê, nếu thấy bảng nào cần thiết VACUUM hoặc ANALYZE, launcher process sẽ khởi động các worker processes để thực hiện việc VACUUM hoặc ANALYZE. Số lượng process autovacuum worker hoạt động trong cùng một thời điểm được giới hạn bởi tham số autovacuum_max_workers (mặc định là 3).


BocapnoMacBook-Pro:postgres bocap$ ps -ef | grep autovacuum | grep  -v grep
  501 26490 26484   0  1:14AM ??         0:00.01 postgres: autovacuum launcher process   
  501 26618 26484   0  1:16AM ??         0:00.03 postgres: autovacuum worker process   postgres

Điều kiện cần thiết cho bảng được VACUUM hoặc ANALYZE bởi autovacuum như bên dưới.

VACUUM
Bảng sẽ tự động được VACUUM khi số lượng dòng bị xoá hoặc update lớn hơn autovacuum_vacuum_scale_factor * tổng số lượng dòng + autovacuum_vacuum_threshold

Ví dụ: số lượng dòng là 1000, thì mặc định khi số dòng bị xoá hoặc update lớn hơn 0.2*1000 + 50 = 250 bảng sẽ tự động được VACUUM

ANALYZE
Bảng sẽ tự động được ANALYZE khi số lượng dòng bị xoá hoặc update lớn hơn autovacuum_analyze_scale_factor * tổng số lượng dòng + autovacuum_analyze_threshold

Ví dụ: số lượng dòng là 1000, thì mặc định khi số dòng bị xoá, update hoặc insert lớn hơn 0.1*1000 + 50 = 250 bảng sẽ tự động được ANALYZE

Khi chạy một câu truy vấn, thông tin thống kê cần phải chính xác để planner chọn đúng plan tối ưu nhất. autovacuum thực thi việc cập nhật thông tin này, nhưng vì một số lý do nào đó (ví dụ: đối tượng bảng đang bị lock trong transaction khác) , autovacuum không thực thi được tốt. Vì vậy, khi thấy câu truy vấn chạy chậm, nên kiểm tra thông tin thống kê xem ANALYZE, VACUUM đã được thực hiện tốt chưa thông qua view pg_stat_all_tables, và thực hiện VACUUM ANALYZE cũng là một giải pháp

Ngoài ra để process autovacuum không ảnh hưởng tới hệ thống, việc thiết lập autovacuum = off và chạy VACUUM ANALYZE thủ công vào thời điểm thích hợp cũng được sử dụng nhiều

Giám sát hoạt động autovacuum

Để biết đối tượng bảng có được VACUUM, ANALYZE hợp lý không ta có thể xem thông qua view pg_stat_all_tables.

-- ví dụ bên dưới cho thấy bảng testtbl đã được:
-- vacuum thủ công vào lúc: 2017-08-26 23:50:28
-- autovacuum vào lúc: 2017-08-27 01:16:04 
-- autoanalyze vào lúc: 2017-08-27 01:16:04

postgres=# select last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_all_tables where relid = (select oid from pg_class where relname = 'testtbl');
          last_vacuum          |        last_autovacuum        | last_analyze |       last_autoanalyze        
-------------------------------+-------------------------------+--------------+-------------------------------
 2017-08-26 23:50:28.025241+09 | 2017-08-27 01:16:04.664297+09 |              | 2017-08-27 01:16:04.665366+09
(1 row)

Ngoài ra, ta có thể theo dõi tình trạng hoạt động của autovacuum thông qua việc thiết lập tham số log_autovacuum_min_duration = 0 (log tất cả các hoạt động autovacuum).

Ví dụ:


2017-08-27 01:16:04.664 JST [26618] LOG:  automatic vacuum of table "postgres.public.testtbl": index scans: 0
	pages: 1276 removed, 0 remain, 0 skipped due to pins, 0 skipped frozen
	tuples: 100000 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 579
	buffer usage: 3851 hits, 0 misses, 0 dirtied
	avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
	system usage: CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.28 s
2017-08-27 01:16:04.665 JST [26618] LOG:  automatic analyze of table "postgres.public.testtbl" system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

Lock

bocap gửi vào T2, 08/14/2017 - 00:21

Lock

Cũng như các RDBMS khác. PostgreSQL cung cấp chức năng cho phép nhiều người dùng có thể truy cập và xử lý dữ liệu dữ liệu cùng một lúc mà không xảy ra xung đột. Tuỳ thuộc vào mức độ phân ly transaction (transaction isolation level) của PostgreSQL mà dữ liệu được nhìn thấy, xử lý như thế nào trong mỗi transaction. Để thực hiện các xử lý này mỗi khi xử lý một tài nguyên (ví dụ như bảng dữ liệu, dòng dữ liệu), phải có cơ chế lock tài nguyên này lại không cho tiến trình khác có thể can thiệp vào được.
PostgreSQL cung cấp nhiều kiểu lock để xử lý dữ liệu. Ví dụ: khi bạn SELECT nội dung đối tượng SELECT (bảng dữ liệu) sẽ bị yêu cầu ACCESS SHARE lock, khi UPDATE, DELETE dữ liệu đối tượng UPDATE, DELETE (dòng dữ liệu) sẽ bị yêu cầu ROW EXCLUSIVE lock, sau khi lock thành công lock này sẽ tồn tại tới khi transaction trong phiên làm việc đó kết thúc.

Các kiểu lock trên bảng dữ liệu PostgreSQL

Các kiểu lock cho bảng dữ liệu PostgreSQL bạn có thể tham khảo chi tiết từ document của PostgreSQL tại đây.

Câu truy vấn sẽ ở trạng thái chờ khi có một lock xung đột xảy ra trên bảng dữ liệu muốn truy vấn.
Như ví dụ bên dưới, khi bảng testtbl đang bị lock ACCESS SHARE bởi một session khác. Câu truy vấn xung đột với ACCESS SHARE (TRUNCATE) sẽ bị trạng thái chờ (lock wait) cho tới khi lock ACCESS SHARE được giải phóng.


postgres=# select * from pg_locks where relation = (select oid from pg_class where relname = 'testtbl');
-[ RECORD 1 ]------+-------------
locktype           | relation
database           | 12558
relation           | 24576
page               | 
tuple              | 
virtualxid         | 
transactionid      | 
classid            | 
objid              | 
objsubid           | 
virtualtransaction | 3/829
pid                | 5605
mode               | RowShareLock
granted            | t
fastpath           | t

postgres=# truncate testtbl ;

trạng thái chờ (lock wait) nhìn từ process hệ thống.


DangnoMacBook-Pro:work dangminhanh$ ps -ef | grep TRUNCATE |grep -v grep
  501  5696  3164   0  1:19AM ??         0:00.03 postgres: postgres postgres [local] TRUNCATE TABLE waiting

trạng thái lock wait trên database rất hay xảy ra khi logic application chưa đúng. Để troubleshoot trường hợp này ta thường tham khảo 2 view pg_stat_activity và pg_locks. Cách sử dụng xin vui lòng xem ở cuối bài viết này.

Lock wait sẽ xảy ra khi có xung đột như các trường hợp "X" bên dưới (trích dẫn từ Documents của PostgreSQL).

Xung đột lock ở mức độ bảng
Loại Lock yêu cầu	Loại lock đang thực thi
Loại Lock yêu cầu	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

Các trường hợp lock xảy xảy ra ở mức độ dòng

Locks xảy ra với dòng có các mức độ như FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE tương ứng với khi chạy các câu truy vấn SELECT ... FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. Lưu ý là locks xảy ra với dòng không xác nhận qua pg_locks view mà phải xác nhận thông qua pgrowlocks. Các xung đột xảy ra ở mức độ dòng được tóm tắt như bên dưới (trích dẫn từ Documents của PostgreSQL).

Mặc định transaction level cuả PostgreSQL (read committed) cho phép transaction khác ghi dữ liệu khi dữ liệu đó đang được tham chiếu. Điều này có thể làm cho logic của application chạy không đúng, như trường hợp kiểm tra dữ liệu hiện tại sau đó thực hiện câu lệnh cập nhật dữ liệu (dữ liệu lúc cập nhật có thể đã bị thay đổi bởi transaction khác). Trong những trường hợp này ta có thể sử dụng câu lệnh SELECT ... FOR UPDATE để giải quyết, câu lệnh này block dữ liệu đang tham chiếu tại lệnh (SELECT) tới khi transaction hiện tại kết thúc.

Các trường hợp xung đột lock ở mức độ dòng
Requested Lock Mode	Current Lock Mode
Requested Lock Mode	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				X
FOR SHARE			X	X
FOR NO KEY UPDATE		X	X	X
FOR UPDATE	X	X	X	X

Cách xác định tình trạng locks

Thông thường locks xác định thông qua view pg_locks. Trường granted cho biết locks đã thực hiện được chưa. nếu locks trong tình trạng waiting, giá trị của granted sẽ là false(t), trường hợp ngược lại sẽ là true.

như kết quả ví dụ bên dưới. Đối với bảng dữ liệu testtbl, process với PID: 24289 đã lock thành công(granted = t) với mode AccessExclusiveLock. Ở một phiên làm việc (kết nối) khác với PID: 24283 câu truy vấn tới sau cũng muốn lock với mode AccessExclusiveLock và đã bị ở trạng thái chờ(granted = f).


postgres=# select locktype,relation,pid,mode,granted,fastpath from pg_locks where relation = (select oid from pg_class where relname = 'testtbl');
 locktype | relation |  pid  |        mode         | granted | fastpath 
----------+----------+-------+---------------------+---------+----------
 relation |    24576 | 24283 | AccessExclusiveLock | f       | f
 relation |    24576 | 24289 | AccessExclusiveLock | t       | f
(2 rows)

Muốn xác định 2 câu truy vấn nào đang diễn ra, ta xem kết quả của view pg_stat_activity. Như kết quả bên dưới ta xác định được phiên làm việc nào, câu truy vấn nào đang bị ở trạng thái chờ, và phiên làm việc nào đang bị chờ.


postgres=# select datname,pid,wait_event_type,wait_event,state,query from pg_stat_activity where pid = 24283 or pid = 24289;
 datname  |  pid  | wait_event_type | wait_event |        state        |     query      
----------+-------+-----------------+------------+---------------------+----------------
 postgres | 24289 | Client          | ClientRead | idle in transaction | lock testtbl ;
 postgres | 24283 | Lock            | relation   | active              | lock testtbl ;
(2 rows)

Locks wait là một trường hợp điển hình của thiết kế client chưa được tốt, nên khi phát hiện nên xử lý logic phía client hơn là chỉnh tham số lock_timeout. Lưu ý nếu có thiết lập tham số lock_timeout cũng không nên chỉnh trong postgresql.conf, vì nó sẽ ảnh hưởng tới toàn bộ database cluster. Có thể chỉnh tham số này thông qua từng phiên làm việc (câu lệnh SET), hoặc USER, DATABASE (lệnh ALTER ... SET ... ).

Ngoài ra để kiểm tra tình trạng lock wait, ta có thể chỉnh tham số log_lock_waits = on, khi thời gian wait vượt quá giá trị deadlock_timeout PostgreSQL sẽ xuất log messages như bên dưới (đã waited 1001.236 ms nhưng chưa thành công).


LOG:  process 24289 still waiting for AccessExclusiveLock on relation 24576 of database 12558 after 1001.236 ms
DETAIL:  Process holding the lock: 24283. Wait queue: 24289.
STATEMENT:  lock testtbl ;

Khi lock thành công PostgreSQL sẽ xuất log messages như bên dưới (thành công sau 11409.427 ms waited).


LOG:  process 24289 acquired AccessExclusiveLock on relation 24576 of database 12558 after 11409.427 ms
STATEMENT:  lock testtbl ;

Xem thêm về Lock

Cấu trúc processes của PostgreSQL

bocap gửi vào CN, 07/30/2017 - 20:56

Cấu trúc processes của PostgreSQL

Khác với một số RDBMS khác sử dụng thread để xử lý, PostgreSQL sử dụng mỗi process cho những chức năng riêng biệt. Sau khi khởi tạo database và khởi động server, PostgreSQL sẽ có những processes như bên dưới.


[postgres@ip-172-31-31-242 ~]$ ps -ef | grep `head -1 $PGDATA/postmaster.pid    
postgres 17397     1  0 Apr11 pts/3    00:06:44 /usr/pgsql-9.6/bin/postgres    
postgres 17398 17397  0 Apr11 ?        00:00:33 postgres: logger process     
postgres 17400 17397  0 Apr11 ?        00:00:18 postgres: checkpointer process    
postgres 17401 17397  0 Apr11 ?        00:01:24 postgres: writer process     
postgres 17402 17397  0 Apr11 ?        00:01:31 postgres: wal writer process    
postgres 17403 17397  0 Apr11 ?        00:06:13 postgres: autovacuum launcher process    
postgres 17404 17397  0 Apr11 ?        00:11:30 postgres: stats collector process    
postgres  7205 17397  0 03:23 ?        00:00:00 postgres: postgres postgres [local] idle

Cấu trúc tổng quan của các Processes có thể hiểu như bên dưới.

PostgreSQL processes

Postmaster Process


postgres 17397     1  0 Apr11 pts/3    00:06:44 /usr/pgsql-9.6/bin/postgres

Là process khởi tạo đầu tiên sau khi server khởi động. Process này đảm nhiệm việc khởi động hoặc dừng các process khác nếu cần hoặc có yêu cầu. Sau khi khởi động Postmaster sẽ khởi động các processes thường trực như bên dưới để PostgreSQL server có thể hoạt động.

logger process
checkpointer process
writer process
wal writer process
autovacuum launcher process (process này không khởi động khi parameter autovacuum = off)
stats collector process

Tiếp đó Postmaster sẽ thực hiện vòng lặp của mình, nếu có yêu cầu kết nối thoả mãn yêu cầu authenticate, hoặc khởi động các tiến trình thường trực khi bị lỗi. Khi có yêu cầu shutdown từ người dùng (pg_ctl stop), Postmaster sẽ gửi các signal tương ứng tới từng server processes.

logger process


postgres 17400 17397  0 Apr11 ?        00:00:18 postgres: logger process

Process này đảm nhiệm việc ghi log của PostgreSQL.

checkpointer process


postgres 17400 17397  0 Apr11 ?        00:00:18 postgres: checkpointer process

Process này chủ yếu giữ vai trò thực hiện checkpoint (đồng bộ dữ liệu từ bộ nhớ đệm xuống vùng lưu trữ) khi cần thiết.

writer process


postgres 17401 17397  0 Apr11 ?        00:01:24 postgres: writer process

Hay còn gọi là background writer process, process này kết hợp với checkpointer process để đảm bảo việc ghi dữ liệu từ bộ đệm xuống vùng lưu trữ. Thông thường khi checkpoint không hoạt động, process này sẽ ghi từng chút một dữ liệu xuống vùng lưu trữ. Để hiểu thêm về background writer process xin vui lòng tham khảo ở manual tại đây.

wal writer process


postgres 17402 17397  0 Apr11 ?        00:01:31 postgres: wal writer process

Đảm nhiệm việc đồng bộ WAL từ bộ nhớ đệm xuống vùng lưu trữ. Thông thường WAL sẽ được ghi từ bộ đệm xuống vùng lưu trữ khi transaction được commit. Nếu dữ liệu đệm của WAL trên bộ nhớ đệm vượt quá parameter wal_buffers dữ liệu WAL trên vùng nhớ đệm sẽ tự động ghi xuống vùng lưu trữ dữ liệu thông qua process này.

autovacuum launcher process


postgres 17403 17397  0 Apr11 ?        00:06:13 postgres: autovacuum launcher process

Process này thường trú khi paramter autovacuum = on. Proccess này thực hiện chức năng tự động lấy vùng dữ liệu dư thừa sau khi DELETE hoặc UPDATE dữ liệu. Vui lòng thảo khảo bài viết về VACUUM để biết thêm về VACUUM. Process này khởi động các VACUUM worker processes sau mỗi autovacuum_naptime. Các VACUUM worker processes sẽ thực hiện việc VACUUM dữ liệu trên các database.

stats collector process


postgres 17404 17397  0 Apr11 ?        00:11:30 postgres: stats collector process

Process này thực hiện vai trò lưu trữ các thông tin thống kê hoạt động của PostgreSQL và cập nhật vào các system catalog (thông tin nội bộ của PostgreSQL hiện diện bởi các bảng hoặc view pg_stat_*).

Server Process


postgres  7205 17397  0 03:23 ?        00:00:00 postgres: postgres postgres [local] idle

Là các processes được sinh ra khi có yêu cầu từ phía client (aplication). Client gửi yêu cầu (bằng cách chạy các connection API) tới server, phía server sẽ sử dụng thông tin authentication (user, password, database, host) từ client để xem user này có được truy cập vào database không. Nếu được, Postmaster sẽ tạo một server process (bằng API fork()) cho client, từ đó client có thể chạy được các câu truy vấn, ... thông qua server process được tạo.

Một số processes không thường trực

Các processes không thường trực của PostgreSQL
Processes	Chức năng
archiver process	Thực hiện chức năng backup archive log (WAL). Process này khởi động khi parameter archive_mode = on.
startup process	Process này thường trực ở Standby node, thực hiện chức năng phản ảnh dữ liệu WAL nhận được từ Master.
wal receiver process	Thường trực ở standby. Sau khi khởi động process này kết nối tới Master node và tạo kết nối replication.
wal sender process postgres	Thường trực ở Master khi có Standby (hoặc pg_basebackup) kết nối tới. Số lượng process wal sender bằng với số lượng wal receive + pg_basebackup kết nối tới.
bgworker: logical replication launcher	[PostgreSQL 10] Process sử dụng cho chức năng logical replication.

Giới thiệu về chức năng Replication(đồng bộ dữ liệu) của PostgreSQL

bocap gửi vào CN, 07/16/2017 - 04:07

Replication

Bạn cần tạo một standby server để backup dữ liệu, load balance trên nhiều node để giảm tải tăng performance?, ... Chức năng replication sẽ đáp ứng nhu cầu đó của bạn.
Replication là một trong chức năng không thể thiếu của một số RDBMS nói chung và PostgreSQL(Streaming Replication) nói riêng. Ở PostgreSQL bạn có thể liên kiết với một số cluster soft như Pacemaker, hay pgpool-II để triển khai hệ thống High Availablity cho môi trường của bạn. Nguyên lý Streaming Replication của PostgreSQL dựa trên việc chuyển WAL (Transaction log) từ node Master tới Standby, sau đó Standby phản ánh lại nội dung WAL vào dữ liệu.

Chức năng replication của PostgreSQL đầu tiên được thêm vào ở phiên bản 9.0. Sau nhiều cải tiến qua các phiên bản. Hiện tại replication nổi bật với chức năng logical replication trên phiên bản sắp tới PostgreSQL-10. Quá trình phát triển chức năng replication tới phiên bản PostgreSQL 10 được tóm tắt như bên dưới.

Chức năng Replication qua các phiên bản PostgreSQL

Chức năng thiếu đồng bộ - Asynchronous (PostgreSQL 9.0).

Phải nói thêm là cũng ở phiên bản 9.0 này PostgreSQL có thêm chức năng hot_standby. Chức năng này cho phép tham chiếu dữ liệu ở phía Standby. Nếu chức năng này không hoạt động bạn không thể tham chiếu dữ liệu hay đơn giản là kết nối tới Standby server. Chức năng này gọi là thiếu đồng bộ. Vì dữ liệu cập nhật ở phía Master không được cập nhật tức thì ở Standby. Về nguyên lý thì bạn có thể tham khảo hình bên dưới.

Chức năng tiền đồng bộ - Synchronous (PostgreSQL 9.1)

Tiến bộ hơn chức năng Asynchronous, ở chế độ Synchronous Standby Server gửi thông điệp thành công tới Master Server sau khi xác nhận đã nhận được thông tin WAL(Transaction log). Sau khi nhận được được thông điệp từ Standby Server, Master kết thúc xử lý cập nhật dữ liệu. Như vậy sau khi cập nhật dữ liệu ở Master Sever, phải chờ một chút thời gian (thường thì khoảng vài micro giây) để Standby phản ánh (recovery) dữ liệu đó từ WAL vừa nhận được. Lưu ý rằng PostgreSQL chỉ support 1 node là Synchronous. Nếu bạn lập nhiều Standby Server node đầu tiên chỉ định trong tham số synchronous_standby_names.

Chức năng Cascade Replication (PostgreSQL 9.2)

Chức năng này cho phép bạn thiết lập một hoặc nhiều Standby Sever dựa vào một Standby Server chứ không phải Master Server như trước đây. Ở Cascade Replication mặc định chỉ sử dụng Asynchronous.

Chức năng replication slot (PostgreSQL 9.4)

Standby Server dựa vào WAL cung cấp từ Master Server để đồng bộ dữ liệu. Nhưng WAL này cũng có thể bị mất theo cơ chế sử dụng lại WAL của PostgreSQL. Khi WAL chưa được đồng bộ (chuyển tới) ở Standby mà bị mất ở Master Server. Standby này sẽ không thể đồng bộ với Master đó nữa, bạn phải thiết lập lại Standby Server trong trường hợp này.

Chức năng replication slot cho phép tạo một slot tới Master Server, yêu cầu Master Server giữ lại WAL cần thiết cho Standby đó.

Số lượng WAL thông thường phụ thuộc vào tham số max_wal_size (Hay checkpoint_segments ở phiên bản trước 9.5). Bạn cũng có thể chỉnh số lượng WAL giữ lại cho standby server qua tham số wal_keep_segments.

Chức Logical Decoding (PostgreSQL 9.4)

Chức năng Logical Decoding cho phép sử dụng plugin bên ngoài decoding được nội dung WAL thành các câu lệnh SQL. Chức năng này kết hợp với chức năng Replication Slot là nền tảng chính của chức năng Logical Replication trong phiên bản PostgreSQL 10.

Chức năng Full Synchronous

Mình sử dụng chữ Full vì ở chế độ này, sau khi kết thúc câu lệnh cập nhật dữ liệu ở Master Server, có thể đảm bảo là dữ liệu sẽ chắc chắn đã được cập nhật ở Standby Server.

Logical Replication

Chức năng này đang được phát triển ở phiên bản PostgreSQL 10. Master Server public WAL, Standby Server nhận WAL giải mã thành các câu lệnh SQL rồi chạy các câu lệnh đó trên Standby Server. Điều này cho phép PostgreSQL có thể Replication giữa các phiên bản khác nhau, hoặc có thể kỳ vọng là replication với các RDBMS khác.

Các phiên bản PostgreSQL trước phiên bản 10, dữ liệu trên WAL không đủ chi tiết để giải mã thành các câu lệnh SQL. Và phía Server và Client phải nhất quán về phiên bản.

Logical replication

Cấu hình

Thiết lập Paramters

Thiết lập paramters cho từng level Asynchronous, Syncrhonous, Full Synchronous, Logical Replication có thể tóm tắt như bên dưới.

Các parameters liên quan tới Streaming Replication
Parameters	Asynchronous	Syncrhonous	Full Synchronous	Logical Replication
wal_level	lớn hơn minimal	lớn hơn minimal	lớn hơn minimal	logical
synchronous_standby_names	''	'tên Standby hoặc *'	'tên Standby hoặc *'
synchronous_commit			remote_apply
max_wal_senders	số lượng Standby	số lượng Standby	số lượng Standby	số lượng Standby
hot_standby	on nếu muốn tham chiếu Standby	on nếu muốn tham chiếu Standby	on nếu muốn tham chiếu Standby	on nếu muốn tham chiếu Standby

Ngoài ra nếu bạn muốn archive WAL (lưu lại WAL phục vụ cho mục đích backup), bạn cần archive_mode=on và archive_command='comand để lưu WAL mỗi khi update xong 1 file WAL'

Cấu hình

Rất đơn giản để tạo 1 Standby Server trên PostgreSQL. Bạn dùng câu lệnh pg_basebackup để tạo một basebackup chỉnh sửa tham số nếu cần thiết và khởi động là xong.


Bocap-no-MacBook-Pro:~ bocap$ env | grep PG
PGPORT=9100
PGUSER=postgres
PGPASSWORD=postgres
PGDATABASE=postgres
PGDATA=/usr/local/pgsql/pg1000/data


Bocap-no-MacBook-Pro:~ bocap$ psql
psql (10beta1)
Type "help" for help.

postgres=# show synchronous_standby_names;
 synchronous_standby_names 
---------------------------
 
(1 row)

postgres=# \q

Không quên setting authentication cho kết nối replication. Ở đây mình đặt trust(không yêu cầu password) cho kết nối nội bộ.


Bocap-no-MacBook-Pro:~ bocap$ grep "replication     all" $PGDATA/pg_hba.conf
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

Kiểm tra trạng thái replication tại Master Server(chưa có kết nối nào).


Bocap-no-MacBook-Pro:~ bocap$ psql
psql (10beta1)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
(0 rows)

postgres=# \q

Thực hiện sao chép dữ liệu cho Standby Server. Và khởi động Standby Server. 11111 là port cho Standby


dang-no-MacBoook-Pro:~ bocap$ pg_basebackup -R -D ${PGDATA}.standby
Bocap-no-MacBook-Pro:~ bocap$ echo port=11111 >> ${PGDATA}.standby/postgresql.conf
Bocap-no-MacBook-Pro:~ bocap$ pg_ctl start -D ${PGDATA}.standby
waiting for server to start....2017-07-16 03:01:28.912 JST [79722] LOG:  listening on IPv6 address "::1", port 11111
2017-07-16 03:01:28.912 JST [79722] LOG:  listening on IPv6 address "fe80::1%lo0", port 11111
2017-07-16 03:01:28.912 JST [79722] LOG:  listening on IPv4 address "127.0.0.1", port 11111
2017-07-16 03:01:28.921 JST [79722] LOG:  listening on Unix socket "/tmp/.s.PGSQL.11111"
2017-07-16 03:01:28.941 JST [79723] LOG:  database system was interrupted; last known up at 2017-07-16 03:00:38 JST
2017-07-16 03:01:29.126 JST [79723] LOG:  entering standby mode
2017-07-16 03:01:29.134 JST [79723] LOG:  redo starts at 0/2000028
2017-07-16 03:01:29.135 JST [79723] LOG:  consistent recovery state reached at 0/20000F8
2017-07-16 03:01:29.135 JST [79722] LOG:  database system is ready to accept read only connections
2017-07-16 03:01:29.160 JST [79727] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1
 done
server started

Kiểm tra tình trạng Replication hiện tại (async: chưa đồng bộ)


Bocap-no-MacBook-Pro:~ bocap$ psql
psql (10beta1)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 79728
usesysid         | 16385
usename          | postgres
application_name | walreceiver
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2017-07-16 03:01:29.158203+09
backend_xmin     | 
state            | streaming
sent_lsn         | 0/3000060
write_lsn        | 0/3000060
flush_lsn        | 0/3000060
replay_lsn       | 0/3000060
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async

postgres=# create table test_synchronous(id integer);
CREATE TABLE
postgres=# insert into test_synchronous values (1);
INSERT 0 1
postgres=# select * from test_synchronous ;
 id 
----
  1
(1 row)

postgres=# \q
Bocap-no-MacBook-Pro:~ bocap$ psql -p 11111
psql (10beta1)
Type "help" for help.

postgres=# select * from test_synchronous ;
 id 
----
  1
(1 row)

postgres=# \q

Chuyển qua chế độ tiền đồng bộ và kiểm tra tình trạng Replication


Bocap-no-MacBook-Pro:~ bocap$ echo synchronous_standby_names='walreceiver' >> $PGDATA/postgresql.conf
Bocap-no-MacBook-Pro:~ bocap$ pg_ctl reload
server signaled
2017-07-16 03:04:48.747 JST [69073] LOG:  received SIGHUP, reloading configuration files
Bocap-no-MacBook-Pro:~ bocap$ 2017-07-16 03:04:48.755 JST [69073] LOG:  parameter "synchronous_standby_names" changed to "walreceiver"

Bocap-no-MacBook-Pro:~ bocap$ 2017-07-16 03:04:50.068 JST [79728] LOG:  standby "walreceiver" is now a synchronous standby with priority 1


Bocap-no-MacBook-Pro:~ bocap$ psql
psql (10beta1)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# show synchronous_standby_names;
-[ RECORD 1 ]-------------+------------
synchronous_standby_names | walreceiver

postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 79728
usesysid         | 16385
usename          | postgres
application_name | walreceiver
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2017-07-16 03:01:29.158203+09
backend_xmin     | 
state            | streaming
sent_lsn         | 0/3019058
write_lsn        | 0/3019058
flush_lsn        | 0/3019058
replay_lsn       | 0/3019058
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 1
sync_state       | sync

postgres=# \q

Chuyển qua chế độ Full Replication


Bocap-no-MacBook-Pro:~ bocap$ psql
psql (10beta1)
Type "help" for help.

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

postgres=# \q


Bocap-no-MacBook-Pro:~ bocap$ echo synchronous_commit='remote_apply' >> $PGDATA/postgresql.conf
Bocap-no-MacBook-Pro:~ bocap$ pg_ctl reload
server signaled
2017-07-16 03:23:54.258 JST [69073] LOG:  received SIGHUP, reloading configuration files
Bocap-no-MacBook-Pro:~ bocap$ 2017-07-16 03:23:54.261 JST [69073] LOG:  parameter "synchronous_commit" changed to "remote_apply"


Bocap-no-MacBook-Pro:~ bocap$ psql
psql (10beta1)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 79728
usesysid         | 16385
usename          | postgres
application_name | walreceiver
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2017-07-16 03:01:29.158203+09
backend_xmin     | 
state            | streaming
sent_lsn         | 0/3019138
write_lsn        | 0/3019138
flush_lsn        | 0/3019138
replay_lsn       | 0/3019138
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 1
sync_state       | sync

postgres=# show synchronous_commit;
-[ RECORD 1 ]------+-------------
synchronous_commit | remote_apply

postgres=# \q
Bocap-no-MacBook-Pro:~ bocap$

Logical Replication

Về cơ bản để sử dụng chức năng Logical Replication. Chúng ta tạo Publication bằng lệnh CREATE PUBLICATION phía Master Server. Phía Server còn lại (phía nhận WAL không phải là Standby như trên mà là một DB Server có thể ghi dữ liệu bình thường) tạo Subscription bằng lệnh CREATE SUBSCRIPTION như ví dụ bên dưới. Chi tiết về chức năng này sẽ được viết ở bài viết khác, khi PostgreSQL 10 đã được hoàn thành.


Bocap-no-MacBook-Pro:~ bocap$ echo wal_level='logical' >> $PGDATA/postgresql.conf
Bocap-no-MacBook-Pro:~ bocap$ pg_ctl restart
waiting for server to shut down...2017-07-16 03:51:02.705 JST [80462] LOG:  received fast shutdown request
.2017-07-16 03:51:02.705 JST [80462] LOG:  aborting any active transactions
2017-07-16 03:51:02.708 JST [80464] LOG:  shutting down
2017-07-16 03:51:02.750 JST [80462] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2017-07-16 03:51:03.739 JST [80476] LOG:  listening on IPv6 address "::1", port 9100
2017-07-16 03:51:03.739 JST [80476] LOG:  listening on IPv6 address "fe80::1%lo0", port 9100
2017-07-16 03:51:03.739 JST [80476] LOG:  listening on IPv4 address "127.0.0.1", port 9100
2017-07-16 03:51:03.754 JST [80476] LOG:  listening on Unix socket "/tmp/.s.PGSQL.9100"
2017-07-16 03:51:03.787 JST [80477] LOG:  database system was shut down at 2017-07-16 03:51:02 JST
2017-07-16 03:51:03.791 JST [80476] LOG:  database system is ready to accept connections
 done
server started
Bocap-no-MacBook-Pro:~ bocap$
Bocap-no-MacBook-Pro:~ bocap$ psql -c "CREATE TABLE test_logical_rep(id integer)"
CREATE TABLE
Bocap-no-MacBook-Pro:~ bocap$ psql -c "CREATE PUBLICATION pub_test FOR TABLE test_logical_rep"
CREATE PUBLICATION
Bocap-no-MacBook-Pro:~ bocap$ psql -p 5432 -c "CREATE TABLE test_logical_rep(id integer)" 
CREATE TABLE
Bocap-no-MacBook-Pro:~ bocap$ psql -p 5432 -c "CREATE SUBSCRIPTION sub_test CONNECTION 'dbname=postgres port=9100 user=postgres' PUBLICATION pub_test"
NOTICE:  synchronized table states
2017-07-16 03:58:54.172 JST [80532] LOG:  logical decoding found consistent point at 0/3033BB0
2017-07-16 03:58:54.172 JST [80532] DETAIL:  There are no running transactions.
NOTICE:  created replication slot "sub_test" on publisher
CREATE SUBSCRIPTION
Bocap-no-MacBook-Pro:~ bocap$ 2017-07-16 03:58:54.190 JST [80533] LOG:  logical replication apply worker for subscription "sub_test" has started
2017-07-16 03:58:54.196 JST [80534] LOG:  starting logical decoding for slot "sub_test"
2017-07-16 03:58:54.196 JST [80534] DETAIL:  streaming transactions committing after 0/3033BE8, reading WAL from 0/3033BB0
2017-07-16 03:58:54.196 JST [80534] LOG:  logical decoding found consistent point at 0/3033BB0
2017-07-16 03:58:54.196 JST [80534] DETAIL:  There are no running transactions.
2017-07-16 03:58:54.198 JST [80535] LOG:  logical replication table synchronization worker for subscription "sub_test", table "test_logical_rep" has started
2017-07-16 03:58:54.207 JST [80536] LOG:  logical decoding found consistent point at 0/3033BE8
2017-07-16 03:58:54.207 JST [80536] DETAIL:  There are no running transactions.
2017-07-16 03:58:55.197 JST [80535] LOG:  logical replication table synchronization worker for subscription "sub_test", table "test_logical_rep" has finished

Bocap-no-MacBook-Pro:~ bocap$ psql -c "INSERT INTO test_logical_rep VALUES(11111);"
INSERT 0 1
Bocap-no-MacBook-Pro:~ bocap$ psql -p 5432 -c "select * from test_logical_rep"
  id   
-------
 11111
(1 row)

Bocap-no-MacBook-Pro:~ bocap$

Xin vui lòng đặt câu hỏi hoặc thảo luận bằng comments box bên dưới.

CHECKPOINT

bocap gửi vào CN, 07/09/2017 - 03:41

Cơ chế lưu dữ liệu trên PostgreSQL

PostgreSQL cũng như một số RDBMS khác. Khi bạn chạy một câu lệnh cập nhật dữ liệu (UPDATE/INSERT/DELETE), dữ liệu vật lý trên đĩa cứng chưa thay đổi mà chỉ thay đổi trên bộ đệm (bộ nhớ dùng chung: PostgreSQL = shared_buffers). Khi bạn tham chiếu dữ liệu (SELECT), trước hết PostgreSQL xem dữ liệu tồn tại trên bộ đệm không, nếu tồn tại dữ liệu sẽ trả về người dùng. Nếu dữ liệu không tồn tại trên bộ đệm, PostgreSQL sẽ truy cập xuống vùng vật lý (ổ đĩa cứng) để lấy dữ liệu, sau đó trước khi trả về cho người dùng dữ liệu đó sẽ được đưa lên bộ đệm.
Cơ chế nêu trên làm tăng tốc độ xử lý nhờ vào việc giảm thiểu I/O trên đĩa cứng.

Độ lớn bộ đệm?

Độ lớn vùng nhớ đệm của PostgreSQL được chỉ định qua parameter shared_buffers (giá trị mặc định của parameter này tuỳ thuộc vào phiên bản PostgreSQL và bộ nhớ hệ thống).

Xem qua ở trên bạn chắc sẽ băn khoăn nếu dữ liệu vừa cập nhật trên bộ đệm, nếu máy tính bị tắt nguồn hoặc OS panic, thì dữ liệu này sẽ bị mất? Bạn yên tâm vì PostgreSQL cũng như các RDBMS khác PostgreSQL có cơ chế roll forward (phục hồi dữ liệu từ transaction log: WAL). Bạn có thể xem qua hình vẽ bên dưới để hiểu thêm.

CHECKPOINT

Khi buffer bị đầy PostgreSQL sẽ ghi những dirty buffers xuống đĩa để chỗ trống cho dữ liệu mới, quá trình này diễn ra liên tục làm tăng cách thao tác thừa sẽ ảnh hưởng tới người dùng. Vì vậy PostgreSQL cần có chức năng CHECKPOINT. CHECKPOINT là cơ chế đồng bộ dữ liệu cập nhật (đã commit) từ bộ nhớ đệm xuống dưới đĩa cứng.

Bạn có thể hình dung đơn giản chức năng CHECKPOINT thực hiện thông qua cơ chế ghi dữ liệu của PostgreSQL như bên dưới.

PostgreSQL thực hiện tự động xử lý checkpoint thông qua process checkpointer (phiên bản trước 9.2 thông qua background process).


[postgres@ip-172-31-31-242 ~]$ pg_ctl status
pg_ctl: server is running (PID: 17397)
/usr/pgsql-9.6/bin/postgres
[postgres@ip-172-31-31-242 ~]$ ps -ef | grep 17397
postgres 14946 14348  0 13:11 pts/2    00:00:00 grep --color=auto 17397
postgres 17397     1  0 Apr11 pts/3    00:04:48 /usr/pgsql-9.6/bin/postgres
postgres 17398 17397  0 Apr11 ?        00:00:08 postgres: logger process   
postgres 17400 17397  0 Apr11 ?        00:00:10 **postgres: checkpointer process**  
postgres 17401 17397  0 Apr11 ?        00:01:08 postgres: writer process   
postgres 17402 17397  0 Apr11 ?        00:01:12 postgres: wal writer process  
postgres 17403 17397  0 Apr11 ?        00:04:31 postgres: autovacuum launcher process  
postgres 17404 17397  0 Apr11 ?        00:08:14 postgres: stats collector process  
[postgres@ip-172-31-31-242 ~]$

CHECKPOINT diễn ra khi nào

Thông thường trong vận hành CHECKPOINT xảy ra khi có các điều kiện sau.

Khi thời gian từ lần CHECKPOINT trước vượt quá tham số checkpoint_timeout Mặc định parameter này là 5 phút. Như vậy chắc chắn trong vòng 5 phút sẽ có 1 CHECKPOINT xảy ra. Lưu ý: CHECKPOINT xử lý theo cơ chế này, để giảm thiểu disk I/O PostgreSQL làm thao tác này chậm hơn các cơ chế CHECKPOINT khác (sự trì hoãn này phụ thuộc vào giá trị của parameter checkpoint_completion_target).

Khi CHECKPOINT theo cơ chế này xảy ra, PostgreSQL log sẽ có messages như bên dưới(lưu ý set log_checkpoints = on để xuất log khi có CHECKPOINT xảy ra)
```
[2017-07-08 12:57:05.993 EDT ] LOG: checkpoint starting: time
[2017-07-08 12:57:12.020 EDT ] LOG: checkpoint complete: wrote 64 buffers (0.4%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=6.017 s, sync=0.002 s, total=6.026 s; sync files=17, longest=0.001 s, average=0.000 s; distance=730 kB, estimate=970 kB
```
Khi lượng dữ liệu transaction log (WAL) lớn hơn max_wal_size (phiên bản trước 9.5 là tham số checkpoint_segments) Khi dung lượng của WAL (transaction log, được lưu trữ trong <thư mục database>/pg_xlog (PostgreSQL 10 -> pg_wal)) vượt quá max_wal_size, CHECKPOINT sẽ tự động diễn ra.

lưu ý: tương quan giữa max_wal_size và checkpoint_segments bạn có thể hiểu là (max_wal_size = checkpoint_segments*3 + 1)

CHECKPOINT theo cơ chế này xảy ra sẽ có messages như bên dưới trong PostgreSQL's log.
```
[2017-07-08 13:02:42.620 EDT ] LOG:  checkpoint starting: xlog
[2017-07-08 13:02:43.921 EDT ] LOG:  checkpoint complete: wrote 2312 buffers (14.1%); 0 transaction log file(s) added, 1 removed, 1 recycled; write=1.011 s, sync=0.139 s, total=1.311 s; sync files=2, longest=0.139 s, average=0.069 s; distance=32759 kB, estimate=34561 kB
```
Ngoài ra CHECKPOINT còn xảy ra trong các trường hợp sau

Chạy câu lệnh CHECKPOINT từ người dùng (quyền super user)
Khi Shutdown server PostgreSQL
Khi khởi động PostgreSQL trong chế độ recovery (roll forward)
Khi tạo dữ liệu backup từ câu lệnh pg_basebackup

Tinh chỉnh CHECKPOINT sao cho hợp lý để hệ thống chạy tốt cũng là một việc quan trọng. Nếu tăng thời gian tự động CHECKPOINT, thì dữ liệu WAL sẽ nhiều, crash recovery, và CHECKPOINT sẽ mất nhiều thời gian ảnh hưởng tới người dùng. Nếu giảm thời gian này đi thì số lượng CHECKPOINT sẽ tăng lên, làm disk I/O tăng ảnh hưởng tới người dùng. Lưu ý là sau mỗi lần CHECKPOINT, lần cập nhật dữ liệu đầu tiên sẽ lưu cả block dữ liệu (full-page writes) lên WAL điều này làm ảnh hưởng tới disk I/O đáng kể. Vì vậy bạn nên thao khảo sát work-load của người dùng để quyết định tinh chỉnh như thế nào cho PostgreSQL chạy hiệu quả nhé.

Xin vui lòng đặt câu hỏi hoặc thảo luận bằng comments box bên dưới.

Trình đơn chính

Trình đơn phụ

Bạn đang ở đây

Tìm kiếm

Điều hướng

Chức năng phía Server

Chức năng log của PostgreSQL

Chú ý khi thiết kế log

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

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

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

Nơi xuất log

Thời điểm xuất log

Nội dung log

Tags:

Khái quát về các Objects lưu trữ trên PostgreSQL

Database cluster

Database

Schema

TABLESPACE

Bảng(table)

index

Tags:

PostgreSQL Replication

Logical Replication

Thiết lập paramters

Sử dụng Logical Replication

Tags:

Lời mở đầu

PostgreSQL setting files

pg_hba.conf

pg_ident.conf

postgresql.auto.conf

postgresql.conf

recovery.conf

Tags:

Backup dữ liệu trên PostgreSQL

Offline backup

Online backup

Online logic backup

Online backup vật lý

Một số lưu ý khi backup

Tags:

VACUUM và chức năng autovacuum

VACUUM thực hiện những công việc gì

Lấy lại dữ liệu dư thừa

Cập nhật lại thông tin thống kê

autovacuum

Giám sát hoạt động autovacuum

Tags:

Lock

Các kiểu lock trên bảng dữ liệu PostgreSQL

Các trường hợp lock xảy xảy ra ở mức độ dòng

Cách xác định tình trạng locks

Cấu trúc processes của PostgreSQL

Postmaster Process

logger process

checkpointer process

writer process

wal writer process

autovacuum launcher process

stats collector process

Server Process

Một số processes không thường trực

Tags:

Replication

Chức năng thiếu đồng bộ - Asynchronous (PostgreSQL 9.0).

Chức năng tiền đồng bộ - Synchronous (PostgreSQL 9.1)

Chức năng Cascade Replication (PostgreSQL 9.2)

Chức năng replication slot (PostgreSQL 9.4)

Chức Logical Decoding (PostgreSQL 9.4)

Chức năng Full Synchronous

Logical Replication

Cấu hình

Thiết lập Paramters

Cấu hình

Logical Replication

Tags:

Cơ chế lưu dữ liệu trên PostgreSQL

Độ lớn bộ đệm?