Computer >> Hướng Dẫn Máy Tính >  >> Hệ Thống >> Linux

Cài đặt và Cấu hình PostgreSQL trên CentOS/RHEL

Trong bài viết này, chúng ta sẽ cài đặt PostgreSQL 11 trên Linux CentOS 7, thực hiện cấu hình cơ bản, xem xét các tham số file cấu hình chính và phương pháp tinh chỉnh hiệu suất. PostgreSQL là một hệ thống quản lý cơ sở dữ liệu đối tượng quan hệ miễn phí phổ biến. Mặc dù nó kém phổ biến hơn MySQL/MariaDB, nhưng nó là hệ thống chuyên nghiệp nhất.

Điểm mạnh của PostgreSQL:

  • Tuân thủ đầy đủ tiêu chuẩn SQL;
  • Hiệu suất cao nhờ kiểm soát đồng thời đa phiên bản (MVCC);
  • Khả năng mở rộng (được sử dụng rộng rãi trong môi trường tải cao);
  • Hỗ trợ nhiều ngôn ngữ lập trình;
  • Cơ chế replication và giao dịch bền vững;
  • Hỗ trợ dữ liệu JSON.

Cách cài đặt PostgreSQL trên CentOS/RHEL?

Mặc dù PostgreSQL có thể cài đặt từ kho CentOS cơ bản, chúng ta sẽ cài đặt kho nhà phát triển vì bạn luôn tìm thấy phiên bản gói mới nhất ở đó.

Trước hết, thêm kho PosgreSQL:

# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Kho này chứa cả phiên bản mới nhất và các phiên bản trước của PosgreSQL. Thông tin về kho trông như sau:

Cài đặt và Cấu hình PostgreSQL trên CentOS/RHEL

Hãy cài đặt PostrgeSQL 11 bằng yum.

# yum install postgresql11-server -y

Máy chủ PostgreSQL và các thư viện cần thiết sẽ được cài đặt:

Installing : libicu-50.2-3.el7.x86_64 1/4
Installing : postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4
Installing : postgresql11-11.5-1PGDG.rhel7.x86_64 3/4
Installing : postgresql11-server-11.5-1PGDG.rhel7.x86_64 4/4

Sau khi gói được cài đặt, bạn sẽ phải khởi tạo cơ sở dữ liệu:

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

Cũng kích hoạt daemon PostgreSQL và thêm vào tự động khởi động bằng systemctl:

# systemctl enable postgresql-11
# systemctl start postgresql-11

Kiểm tra trạng thái dịch vụ:

# systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2020-10-18 16:02:15 +06; 26s ago
Docs: https://www.postgresql.org/docs/11/static/
Process: 8714 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 8719 (postmaster)
CGroup: /system.slice/postgresql-11.service
├─8719 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─8721 postgres: logger
├─8723 postgres: checkpointer
├─8724 postgres: background writer
├─8725 postgres: walwriter
├─8726 postgres: autovacuum launcher
├─8727 postgres: stats collector
└─8728 postgres: logical replication launcher
Oct 18 16:02:16 host1.woshub.com systemd[1]: Starting PostgreSQL 11 database server...

Nếu bạn muốn truy cập PostgreSQL từ bên ngoài, mở cổng TCP 5432 trong firewalld mặc định CentOS:
# firewall-cmd --get-active-zones

public
interfaces: eth0
# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload

Hoặc với iptables: 

# iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
# service iptables restart

Nếu SELinux được bật, chạy lệnh sau:

# setsebool -P httpd_can_network_connect_db 1

Sử dụng PSQL để tạo Database, User, Cấp quyền trong PostgreSQL

Mặc định, khi cài đặt PostgreSQL chỉ có một người dùng trong hệ thống — postgres. Tôi không khuyên dùng tài khoản này cho công việc hàng ngày với cơ sở dữ liệu. Tốt hơn là tạo người dùng riêng cho từng cơ sở dữ liệu.

Để kết nối đến máy chủ postgres, chạy lệnh:

# sudo -u postgres psql
psql (11.5)
Type "help" for help.
postgres=#

Console PostgreSQL xuất hiện. Sau đó chúng ta sẽ trình bày một số ví dụ đơn giản quản lý PostgreSQL từ console psql.

Thay đổi mật khẩu người dùng postgres mặc định:

ALTER ROLE postgres WITH PASSWORD 's3tPa$$w0rd!';

Tạo cơ sở dữ liệu và người dùng mới, và cấp cho người dùng toàn quyền truy cập cơ sở dữ liệu mới:

postgres=# CREATE DATABASE newdbtest;
postgres=# CREATE USER mydbuser WITH password '!123456789';
postgres=# GRANT ALL PRIVILEGES ON DATABASE newdbtest TO mydbuser;

Để kết nối đến DB:

postgres=# \c databasename

Để hiển thị danh sách bảng:

postgres=# \dt

Để hiển thị danh sách kết nối cơ sở dữ liệu:

postgres=# select * from pg_stat_activity where datname='dbname'

Để reset tất cả kết nối đến cơ sở dữ liệu:

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname = 'dbname'

Để lấy thông tin về phiên hiện tại:

postgres=# \conninfo

Để thoát console psql, chạy lệnh:

postgres=# \q

Như bạn đã nhận ra, cú pháp tương tự MariaDB hoặc MySQL.

Chúng ta lưu ý rằng để quản lý cơ sở dữ liệu PostgreSQL từ giao diện web thuận tiện hơn, khuyến khích sử dụng pgAdmin4 (viết bằng Python và Javascript/jQuery). Nó tương tự PhpMyAdmin mà nhiều nhà phát triển web quen thuộc.

Cài đặt và Cấu hình PostgreSQL trên CentOS/RHEL

Thiết lập các tham số cấu hình PostgreSQL chính

File cấu hình Postgresql nằm trong /var/lib/pgsql/11/data:

  1. postgresql.conf — file cấu hình postgresql;
  2. pg_hba.conf — file chứa cài đặt truy cập. Trong file này, bạn có thể đặt các giới hạn khác nhau cho người dùng hoặc đặt chính sách kết nối cơ sở dữ liệu;
  3. pg_ident.conf — file này dùng để xác định client qua giao thức ident.

Để ngăn người dùng cục bộ đăng nhập postgres không có ủy quyền, chỉ định sau trong pg_hba.conf của bạn:

local all all md5
host all all 127.0.0.1/32 md5

Hãy xem xét các tham số quan trọng nhất trong postgresql.conf:

  • listen_addresses — đặt địa chỉ IP máy chủ sẽ chấp nhận kết nối client. Giá trị mặc định là localhost, nghĩa là chỉ kết nối cục bộ mới có thể. Để lắng nghe tất cả giao diện IPv4, chỉ định 0.0.0.0 ở đây;
  • max_connections – số lượng kết nối đồng thời tối đa đến máy chủ DB;
  • temp_buffers – kích thước tối đa bộ đệm tạm thời;
  • shared_buffers — kích thước bộ nhớ chia sẻ được máy chủ cơ sở dữ liệu sử dụng. Thông thường, đặt giá trị 25% tổng RAM máy chủ;
  • effective_cache_size – tham số cho phép bộ lập lịch postgres xác định lượng bộ nhớ khả dụng cho caching trên ổ cục bộ. Thường đặt 50-75% tổng RAM trên máy chủ;
  • work_mem – kích thước bộ nhớ dùng cho các hoạt động sắp xếp nội bộ của hệ thống quản lý DB — ORDER BY, DISTINCT và hợp nhất;
  • maintenance_work_mem – kích thước bộ nhớ dùng cho các hoạt động nội bộ — VACUUM, CREATE INDEX và ALTER TABLE ADD FOREIGN KEY;
  • fsync – nếu tham số này được bật, DBMS sẽ chờ ghi vật lý dữ liệu trên ổ cứng. Nếu fsync được bật, sẽ dễ dàng hơn cho bạn khôi phục cơ sở dữ liệu sau sự cố hệ thống hoặc phần cứng. Rõ ràng, nếu tham số này được bật, hệ thống quản lý cơ sở dữ liệu sẽ có hiệu suất thấp hơn, nhưng độ tin cậy lưu trữ cao hơn. Nếu bạn tắt nó, có giá trị tắt full_page_writes cũng;
  • max_stack_depth — kích thước ngăn xếp tối đa (2MB mặc định);
  • max_fsm_pages — dùng tham số này, bạn có thể quản lý không gian ổ cứng trống trên máy chủ. Ví dụ, sau khi xóa dữ liệu từ bảng, không gian đã chiếm trước đó không trở thành trống, nhưng được đánh dấu là trống trên bản đồ không gian trống và dùng cho các mục nhập mới. Nếu bạn thường xuyên ghi/xóa dữ liệu trong bảng trên máy chủ, hiệu suất sẽ tăng nếu bạn đặt giá trị lớn hơn cho tham số này;
  • wal_buffers – kích thước bộ nhớ chia sẻ (shared_buffers) dùng để giữ dữ liệu WAL;
  • wal_writer_delay – thời gian giữa các kỳ ghi WAL liên tiếp trên ổ cứng;
  • commit_delay — độ trễ giữa ghi giao dịch vào bộ đệm WAL và ghi thông qua đến ổ cứng;
  • synchronous_commit — tham số đặt kết quả giao dịch thành công sẽ được gửi sau khi dữ liệu WAL đã được ghi vật lý trên ổ cứng.

Sao lưu và Khôi phục cơ sở dữ liệu PostgreSQL

Bạn có thể sao lưu cơ sở dữ liệu PostgreSQL theo nhiều cách. Hãy xem cách dễ nhất.

Trước hết, kiểm tra cơ sở dữ liệu nào đang chạy trên máy chủ của bạn:

postgres=# \list

Cài đặt và Cấu hình PostgreSQL trên CentOS/RHEL

Chúng ta có 4 cơ sở dữ liệu, 3 trong số đó là hệ thống (postgres và template).

Trước đó chúng ta đã tạo DB tên mydbtest và bây giờ chúng ta sẽ sao lưu nó.

Bạn có thể sao lưu cơ sở dữ liệu PostgreSQL bằng công cụ pg_dump:

# sudo -u postgres pg_dump mydbtest > /root/dupm.sql

Chạy lệnh này dưới user postgres, chỉ định cơ sở dữ liệu và đường dẫn đến file bạn sẽ lưu dump cơ sở dữ liệu. Hệ thống sao lưu của bạn có thể lấy dump cơ sở dữ liệu, hoặc bạn có thể gửi nó đến tài khoản lưu trữ đám mây đã kết nối trong trường hợp dùng máy chủ web.

Để khôi phục dump vào cơ sở dữ liệu, dùng psql:

# sudo -u postgres psql mydbtest < /root/dupm.sql

Cài đặt và Cấu hình PostgreSQL trên CentOS/RHEL

Bạn cũng có thể tạo bản sao lưu ở định dạng dump đặc biệt và nén nó bằng gzip:

# sudo -u postgres pg_dump -Fc mydbtest > /root/dumptest.sql

Dump sau đó được khôi phục bằng công cụ pg_restore:

# sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql

Tinh chỉnh hiệu suất và Tối ưu PostgreSQL

Trong bài viết trước liên quan đến MariaDB, chúng ta đã chỉ cách tối ưu tham số file cấu hình my.cnf bằng các công cụ tinh chỉnh. PostgreSQL có PgTun cho việc đó, nhưng tiếc là nó không được cập nhật depuis lâu. Đồng thời, có nhiều dịch vụ trực tuyến bạn có thể dùng để tối ưu cấu hình PostgreSQL. Tôi thích PGTune (pgtune.leopard.in.ua).

Giao diện rất đơn giản. Bạn chỉ cần chỉ định tham số máy chủ (hồ sơ, bộ xử lý, bộ nhớ, loại ổ cứng) và nhấn "Generate". Bạn sẽ được cung cấp một biến thể postgresql.conf chứa các giá trị khuyến nghị của các tham số PostgreSQL chính.

Ví dụ, các cài đặt postgresql.conf sau được khuyến nghị cho máy chủ VPS SSD với 4xGB RAM và 4xvCPU:

# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 4 GB
# CPUs num: 4
# Connections num: 100
# Data Storage: ssd
max_connections = 100
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

Cài đặt và Cấu hình PostgreSQL trên CentOS/RHEL

Thực tế, đó không phải là tài nguyên duy nhất vào thời điểm bài viết được viết. Các dịch vụ tương tự cũng có sẵn:

  • Cybertec PostgreSQL Configurator
  • PostgreSQL Configuration Tool

Sử dụng các dịch vụ này, bạn có thể nhanh chóng cấu hình tham số PostgreSQL cơ bản cho phần cứng và nhiệm vụ của bạn. Sau đó bạn không chỉ có thể xem xét tài nguyên máy chủ, mà còn phân tích hoạt động cơ sở dữ liệu, kích thước của nó, số lượng kết nối và tinh chỉnh tham số PostgreSQL dựa trên thông tin này.