Trong bài viết này, chúng tôi 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 thông số tập tin cấu hình chính và các phương pháp điều chỉnh hiệu suất. PostgreSQL là một hệ quản trị cơ sở dữ liệu quan hệ đối tượng miễn phí phổ biến. Mặc dù nó ít phổ biến hơn MySQL / MariaDB, nhưng nó là một trong những phần mềm chuyên nghiệp nhất.
Điểm mạnh của PostgreSQL:
- Hoàn toàn tuân thủ các tiêu chuẩn SQL;
- Hiệu suất cao nhờ điều khiển đồng thời đa vũ trụ (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ế giao dịch và sao chép linh hoạt;
- Hỗ trợ dữ liệu JSON.
Cách cài đặt PostgreSQL trên CentOS / RHEL?
Mặc dù có thể cài đặt PostgreSQL từ kho lưu trữ CentOS cơ sở, chúng tôi sẽ cài đặt kho lưu trữ dành cho nhà phát triển vì bạn luôn có thể tìm thấy phiên bản gói hiện tại ở đó.
Trước hết, hãy thêm repo PosgreSQL:
# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Kho lưu trữ này chứa cả phiên bản PosgreSQL mới nhất và cũ hơn. Thông tin về kho lưu trữ có dạng như sau:
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 bắt buộc sẽ được cài đặt:
Cài đặt:libicu-50.2-3.el7.x86_64 1/4 Cài đặt:postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4 Cài đặt:postgresql11-11.5-1PGDG.rhel7.x86_64 3/4 Cài đặt:postgresql11-server- 11,5-1PGDG.rhel7.x86_64 4/4
Sau khi các 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
Ngoài ra, hãy kích hoạt daemon PostgreSQL và thêm nó 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 - Máy chủ cơ sở dữ liệu PostgreSQL 11Loaded:đã tải (/usr/lib/systemd/system/postgresql-11.service; đã kích hoạt; đặt trước của nhà cung cấp:đã vô hiệu hóa) Active:hoạt động (đang chạy) kể từ Thứ 4 năm 2020-10 -18 16:02:15 +06; 26 giây trước , status =0 / SUCCESS) PID chính: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:thu thập số liệu thống kê└─8728 postgres:trình khởi chạy sao chép logic 16:02:16 host1.woshub.com systemd [1]:Khởi động máy chủ cơ sở dữ liệu PostgreSQL 11 ...
Nếu bạn muốn truy cập vào PostgreSQL từ bên ngoài, hãy mở Cổng TCP 5432 trong trình tường lửa CentOS mặc định:
# firewall-cmd --get-active-zones
publicinterfaces: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, hãy chạy lệnh sau:
# setsebool -P httpd_can_network_connect_db 1
Sử dụng PSQL để tạo cơ sở dữ liệu, người dùng, cấp quyền trong PostgreSQL
Theo mặc định, khi bạn cài đặt PostgreSQL, chỉ có người dùng trong hệ thống - postgres. Tôi không khuyến khích sử 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 với máy chủ postgres, hãy chạy lệnh sau:
# sudo -u postgres psql
psql (11.5) Nhập "help" để được trợ giúp.
postgres=#
Bảng điều khiển PostgreSQL xuất hiện. Sau đó, chúng tôi sẽ hiển thị một số ví dụ đơn giản về quản lý PostgreSQL từ bảng điều khiển 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, đồng thời cấp cho người dùng toàn quyền truy cập vào 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 với DB:
postgres=# \c databasename
Để hiển thị danh sách các bảng:
postgres=# \dt
Để hiển thị danh sách các kết nối cơ sở dữ liệu:
postgres=# select * from pg_stat_activity where datname='dbname'
Để đặt lại tất cả các kết nối với cơ sở dữ liệu:
postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname = 'dbname'
Để nhận thông tin về phiên hiện tại:
postgres=# \conninfo
Để thoát khỏi bảng điều khiển psql, hãy chạy lệnh sau:
postgres=# \q
Như bạn đã nhận thấy, cú pháp tương tự như MariaDB hoặc MySQL.
Chúng tôi sẽ lưu ý rằng để quản lý cơ sở dữ liệu PostgreSQL từ giao diện web thuận tiện hơn, bạn nên sử dụng pgAdmin4 (viết trên Python và Javascript / jQuery). Nó tương tự như PhpMyAdmin mà nhiều nhà phát triển web quen thuộc.
Đặt thông số cấu hình PostgreSQL chính
Tệp cấu hình postgresql nằm trong / var / lib / pgsql / 11 / data :
- postgresql.conf - tệp cấu hình postgresql;
- pg_hba.conf - một tệp chứa cài đặt truy cập. Trong tệp này, bạn có thể đặt các hạn chế khác nhau cho người dùng của mình hoặc đặt chính sách kết nối cơ sở dữ liệu;
- pg_ident.conf - tệp này được sử dụng để xác định khách hàng qua giao thức nhận dạng.
Để ngăn người dùng cục bộ đăng nhập vào postgres mà không được phép, hãy chỉ định thông tin sau trong pg_hba.conf của bạn:
cục bộ tất cả md5host tất cả 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 các địa chỉ IP mà máy chủ sẽ chấp nhận các kết nối máy khách. Giá trị mặc định là localhost, nghĩa là chỉ có thể kết nối cục bộ. Để nghe tất cả các giao diện IPv4, hãy chỉ định 0.0.0.0 tại đây; -
max_connections–Số lượng tối đa các kết nối đồng thời tới một máy chủ DB; -
temp_buffers- kích thước tối đa của bộ đệm tạm thời; -
shared_buffers- kích thước của bộ nhớ dùng chung được sử dụng bởi một máy chủ cơ sở dữ liệu. Thông thường, giá trị 25% tổng RAM máy chủ được đặt; -
effective_cache_size- một tham số cho phép bộ lập lịch postgres xác định dung lượng bộ nhớ có sẵn để lưu vào bộ nhớ đệm trên ổ đĩa cục bộ. Thông thường, nó được đặt thành 50-75% tổng RAM trên máy chủ; -
work_mem- dung lượng bộ nhớ được sử dụng bởi các hoạt động sắp xếp nội bộ của hệ quản trị cơ sở dữ liệu - ORDER BY, DISTINCT và merge; -
maintenance_work_mem- kích thước bộ nhớ được sử dụng bởi các hoạt động bên trong - VACUUM, TẠO CHỈ SỐ và BẢNG ALTER THÊM TỪ KHÓA NGOẠI LỆ; -
fsync- nếu tham số này được kích hoạt, DBMS sẽ đợi quá trình ghi dữ liệu vật lý trên đĩa cứng. Nếu fsync được bật, bạn sẽ dễ dàng khôi phục cơ sở dữ liệu của mình hơn sau khi hệ thống hoặc phần cứng bị lỗi. Rõ ràng, nếu tham số này được kích hoạt, hệ quản trị 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ó, bạn cũng nên tắt full_page_writes; -
max_stack_depth- kích thước ngăn xếp tối đa (2MB theo mặc định); -
max_fsm_pages- Sử dụng tham số này, bạn có thể quản lý không gian đĩa trống trên máy chủ. Ví dụ:sau khi xóa một số dữ liệu khỏi bảng, không gian bị chiếm trước đó không trở nên trống, nhưng được đánh dấu là trống trên bản đồ dung lượng trống và được sử 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 các bảng trên máy chủ của mình, thì hiệu suất sẽ tăng lên nếu bạn đặt giá trị lớn hơn của thông số này; -
wal_buffers- kích thước bộ nhớ dùng chung (shared_buffers) được sử dụng để giữ dữ liệu WAL; -
wal_writer_delay- thời gian giữa các khoảng thời gian liên tiếp ghi WAL trên đĩa; -
commit_delay- độ trễ giữa việc ghi một giao dịch vào bộ đệm WAL và quá trình ghi của nó vào đĩa; -
synchronous_commit- bộ tham số mà kết quả của giao dịch thành công sẽ được gửi sau khi dữ liệu WAL đã được ghi vật lý trên đĩa.
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 một số cách. Hãy xem xét cách dễ nhất.
Trước hết, hãy kiểm tra xem cơ sở dữ liệu nào đang chạy trên máy chủ của bạn:
postgres=# \list
Chúng tôi có 4 cơ sở dữ liệu, 3 trong số đó là cơ sở dữ liệu hệ thống (postgres và template).
Trước đó, chúng tôi đã tạo một DB với tên mydbtest và bây giờ chúng tôi sẽ sao lưu nó.
Bạn có thể sao lưu cơ sở dữ liệu PostgreSQL của mình bằng cách sử dụng pg_dump công cụ:
# sudo -u postgres pg_dump mydbtest > /root/dupm.sql -
Chạy lệnh này dưới người dùng postgres, chỉ định cơ sở dữ liệu và đường dẫn đến tệp mà bạn sẽ lưu kết xuất cơ sở dữ liệu. Hệ thống sao lưu của bạn có thể thực hiện kết xuất 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 được kết nối của mình trong trường hợp sử dụng máy chủ web.
Để khôi phục kết xuất vào cơ sở dữ liệu, hãy sử dụng psql :
# sudo -u postgres psql mydbtest < /root/dupm.sql
Bạn cũng có thể tạo bản sao lưu ở định dạng kết xuất đặc biệt và nén nó bằng gzip:
# sudo -u postgres pg_dump -Fc mydbtest > /root/dumptest.sql
Sau đó, kết xuất được khôi phục bằng công cụ pg_restore:
# sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql
Điều chỉnh và tối ưu hóa hiệu suất PostgreSQL
Trong bài viết trước liên quan đến MariaDB, chúng tôi đã hướng dẫn cách tối ưu hóa các thông số tệp cấu hình my.cnf bằng cách sử dụng bộ chỉnh. PostgreSQL có PgTun cho điều đó, nhưng tiếc là nó đã không được cập nhật trong một thời gian dài. Đồng thời, có rất nhiều dịch vụ trực tuyến bạn có thể sử dụng để tối ưu hóa cấu hình PostgreSQL của mình. Tôi thích PGTune ( pgtune.leopard.in.ua) .
Giao diện rất đơn giản. Bạn chỉ cần chỉ định các thông số máy chủ của mình (hồ sơ, bộ xử lý, bộ nhớ, loại đĩa) và nhấp vào “Tạo”. Bạn sẽ được cung cấp một biến thể của postgresql.conf chứa các giá trị được đề xuất 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ủ SSD VPS có RAM 4xGB và 4xvCPU:
# Phiên bản DB:11 # Loại hệ điều hành:linux # Loại DB:web # Tổng bộ nhớ (RAM):4 GB # Số CPU:4 # Số kết nối:100 # Bộ nhớ dữ liệu:ssdmax_connections =100shared_buffers =1GBeffective_cache_size =3GBmaintenance_work_mem =256MBcheckpoint_completion_target =0,7wal_buffers =16MBdefault_stosystem_target =100random_page_cost =1.1effective_io_concurrency =200work_mem =5242kBmin_wal_size =1GBmax_wal_size =4GBmax_worker_processes =4max_parallel_workers_per_gather_parawork_ 2maxersllel_workers_per_gather =2max
Trên thực tế, nó không phải là nguồn duy nhất vào thời điểm bài báo được viết. Các dịch vụ tương tự cũng có sẵn:
- Trình cấu hình Cybertec PostgreSQL
- Công cụ cấu hình PostgreSQL
Sử dụng các dịch vụ này, bạn có thể nhanh chóng cấu hình các thông số PostgreSQL cơ bản cho phần cứng và tác vụ của mình. Sau đó, bạn sẽ không chỉ có thể xem xét tài nguyên máy chủ của mình mà còn có thể phân tích hoạt động của cơ sở dữ liệu, kích thước của nó, số lượng kết nối và tinh chỉnh các thông số PostgreSQL của bạn dựa trên những thông tin này.