Thành Thạo PostgreSQL: Hướng Dẫn Cài Đặt & Cấu Hình

Giới thiệu

PostgreSQL là hệ thống quản lý cơ sở dữ liệu quan hệ (RDBMS) mã nguồn mở tiên tiến nhất thế giới. Nhiều tổ chức hàng đầu, như Apple, IMDB, Skype, Uber, Lockheed Martin, Verizon và nhiều nơi khác, sử dụng PostgreSQL. RDBMS này bắt đầu năm 1986 như một phần của dự án POSTGRES tại Đại học California tại Berkeley và có hơn 30 năm phát triển tích cực trên nền tảng cốt lõi.

PostgreSQL chạy trên tất cả các hệ điều hành chính và đã tuân thủ ACID kể từ năm 2001. Viết tắt ACID bao gồm các thành phần sau:

Atomicity (Tính nguyên tử): Bạn đảm bảo rằng toàn bộ transaction thành công hoặc không có gì xảy ra.
Consistency (Tính nhất quán): Bạn đảm bảo rằng tất cả dữ liệu nhất quán. Tất cả dữ liệu hợp lệ theo các quy tắc được định nghĩa. Các quy tắc bao gồm constraints, cascades và triggers áp dụng cho cơ sở dữ liệu.
Isolation (Tính cách ly): Tất cả transaction xảy ra trong sự cách ly. Transaction không thể đọc dữ liệu từ bất kỳ transaction nào khác chưa hoàn thành.
Durability (Tính bền vững): Sau khi bạn commit một transaction, nó vẫn còn trong hệ thống, ngay cả khi có sự cố hệ thống ngay sau hoạt động.

Không ngạc nhiên, PostgreSQL đóng vai trò là cơ sở dữ liệu quan hệ mã nguồn mở lựa chọn cho nhiều người và tổ chức vì các add-on mạnh mẽ như PostGIS geospatial database extender phổ biến.

Thành Thạo PostgreSQL: Hướng Dẫn Cài Đặt & Cấu Hình – Phần 1

Nguồn hình ảnh: https://postgresql-database.blogspot.com/2013/08/postgresql-architecture.html

Hỗ trợ

Production support SLA có sẵn từ các công ty sau:

https://www.enterprisedb.com
https://www.2ndquadrant.com/
https://www.revsys.com/
https://imperoit.com/PostgreSQL_Support.htm

Các phiên bản được hỗ trợ: Current (12) / 11 / 10 / 9.6 / 9.5 / 9.4 Phiên bản phát triển: devel Phiên bản không được hỗ trợ: 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2

Cài đặt và Cấu hình

Thực hiện các bước sau để cài đặt và cấu hình Postgres 9.3:

Cài đặt Postgres 9.3 trên Linux 7.1

Chạy lệnh sau để cài đặt Postgres 9.3 trên Red Hat® Linux 7.1:

[root@snwdbsolpeprod01 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.1 (Maipo)

Tạo thư mục trống

Tạo thư mục trống cho cài đặt cơ sở dữ liệu.

[root@snwdbsolpeprod01 mnt]# mkdir postt
[root@snwdbsolpeprod01 postt]# pwd
/mnt/postt

Tải xuống RPM

Chạy lệnh sau để tải xuống Red Hat Package Manager (RPM) cho phiên bản OS của bạn để bắt đầu cài đặt Postgres:

[root@snwdbsolpeprod01 postt]# wget https://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-redhat93-9.3-2.noarch.rpm

Cài đặt RPM

Cài đặt gói RPM bằng cách sử dụng lệnh sau:

root@snwdbsolpeprod01 postt]# rpm -ivh pgdg-redhat93-9.3-2.noarch.rpm

Cài đặt các gói bổ sung

Sau khi bạn cài đặt RPM, bạn cần cài đặt một số gói Postgre để cài đặt phần mềm DB.

[root@snwdbsolpeprod01 postt]# yum install postgresql-contrib.x86_64
[root@snwdbsolpeprod01 postt]# yum install postgresql93-server.x86_64

Cấu hình vị trí PGDATA

Xác định nơi lưu trữ dữ liệu. Để sử dụng vị trí không mặc định cho dữ liệu, hãy chỉnh sửa file sysconfig của dịch vụ PostgreSQL và thay đổi đối số PGDATA.

vi /etc/rc.d/init.d/postgresql
vi /etc/sysconfig/pgsql/postgresql

LƯU Ý: Nếu PostgreSQL trong sysconfig/pgsql không tồn tại, hãy tạo nó và thêm một dòng chỉ định nơi bạn muốn lưu trữ dữ liệu, như ví dụ sau:

[root@snwdbsolpeprod01 pgsql]# cd /etc/sysconfig/pgsql/
[root@snwdbsolpeprod01 pgsql]# vi postgresql
[root@snwdbsolpeprod01 pgsql]# cat postgresql
PDGATA=/mnt/postt

Khởi tạo cơ sở dữ liệu

Lệnh đầu tiên (chỉ cần một lần) là khởi tạo cơ sở dữ liệu trong PGDATA.

service  initdb

Ví dụ: cho phiên bản 9.3:

service postgresql-9.3 initdb

hoặc

/usr/pgsql-9.3/bin/postgresql93-setup initdb
[root@snwdbsolpeprod01 data]# /usr/pgsql-9.3/bin/postgresql93-setup initdb
Initializing database ... OK

Thiết lập Postgres tự động khởi động

Nếu bạn muốn PostgreSQL tự động khởi động khi OS khởi động, sử dụng lệnh sau:

[root@snwdbsolpeprod01 data]# chkconfig postgresql-9.3 on

Lưu ý: Chuyển tiếp yêu cầu đến 'systemctl enable postgresql-9.3.service'.

Khởi động dịch vụ PostgreSQL

Để khởi động dịch vụ PostgreSQL, chạy lệnh sau:

[root@snwdbsolpeprod01 data]# systemctl start postgresql-9.3.service

Cấu hình cơ sở dữ liệu

Bạn có thể dễ dàng cấu hình cơ sở dữ liệu bằng cách cập nhật postgresql.conf như ví dụ sau:

vi /var/lib/pgsql/9.3/data/postgresql.conf

Thay đổi các thông số sau:

listen_address = '*'
port = 15000
max_connections=300
shared_buffers = 8192MB # min 128kB # (change requires restart)
temp_buffers = 128MB # min 800kB
max_prepared_transactions = 20 # zero disables the feature
log_destination = 'csvlog'
logging_collector = on
log_directory = '/mnt/pgsql/logs'
log_filename = 'postgresql-%a.log'
#------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------
autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least this number # of milliseconds.
autovacuum_max_workers = 3 # max number of autovacuum subprocesses # (change requires restart)
autovacuum_naptime = 10080min # time between autovacuum runs
autovacuum_vacuum_threshold = 1000 # min number of row updates before # vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before # analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000 # maximum Multixact age # before forced vacuum # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit

Cấu hình cài đặt kết nối cơ sở dữ liệu

Để hạn chế hoặc quản lý cài đặt kết nối cơ sở dữ liệu, chạy lệnh sau:

vi /var/lib/pgsql/9.3/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
local all postgres md5
local all postgres ident
# IPv4 local connections:
# IPv6 local connections:
host all all ::1/128 ident
host all all 0.0.0.0/0 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident

Cấu hình tường lửa

Để cấu hình cổng trên tường lửa, chạy các lệnh sau:

iptables -I INPUT -p tcp --dport 15000 --syn -j ACCEPT
service iptables save
service iptables restart
root@snwdbsolpeprod01 postt]# service postgresql-9.3 restart

Tạo user hoặc role

Để tạo user hoặc role mới trong cơ sở dữ liệu, chạy các lệnh sau:

su – postgres
psql -p 15000
postgres=# CREATE ROLE OCT1 LOGIN UNENCRYPTED PASSWORD 'test@123' INHERIT REPLICATION;

Tạo tablespace

Để tạo tablespace mới trên cơ sở dữ liệu, chạy lệnh sau:

postgres=# CREATE TABLESPACE OCT1_tablespace OWNER ilusr LOCATION '/usrdata/pgsql/data/oct';

Tạo database

Để tạo database mới, chạy lệnh sau:

postgres=# CREATE DATABASE OCT1 WITH ENCODING='UTF8' OWNER=test LC_CTYPE='en_US.UTF-8' CONNECTION LIMIT=-1 TABLESPACE=OCT1_tablespace;

Các lệnh cơ bản

Một số lệnh quản trị cơ bản bao gồm:

Dừng và khởi động PostgreSQL

/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data stop
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data start
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data restart
/opt/PostgreSQL/9.3/bin/pg_ctl -D opt/PostgreSQL/9.4/data –m smart stop #wait for complete the transactions
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m fast stop #Immediate stop
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m immediate stop #Abort the DB
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m smart restart
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m fast restart
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m immediate restart

Kiểm tra phiên bản PostgreSQL

postgres=# select version();

Xác định hoạt động trên cấp độ database cụ thể

select pid,backend_xid,backend_xmin,query from pg_stat_activity;

Phân tích trạng thái bảng

select relname,last_autoanalyze,last_analyze,n_mod_since_analyze from pg_stat_all_tables;

Tìm đường dẫn vật lý của một bảng

postgres=# SELECT pg_relation_filepath('testpitr1');
pg_relation_filepath
----------------------
base/13003/16399
[postgres@postgres221 data]$ ls -l /mnt/postt/data/base/13003/16399
-rw------- 1 postgres postgres 256024576 Feb 21 06:36 /mnt/postt/data/base/13003/16399

Lấy tên schema bên trong instance hoặc cluster

select schema_name from information_schema.schemata;
select nspname from pg_catalog.pg_namespace;
post_gre=# \dn
List of schemas
Name | Owner
--------------+----------
kailash_test | postgres
public | postgres
(2 rows)

Lấy tên db bên trong instance hoặc cluster

template1=# select datname from pg_database;
template1
template0
post_gre
template1=# \l
post_gre | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres + | | | | | kailash_s=CTc/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | template1=# select usename from pg_catalog.pg_user;
kailash
kailash_s
postgres
template1=# \du
kailash | | {}
kailash_s | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}

Kết luận

Slogan của PostgreSQL khẳng định rằng nó là "Cơ sở dữ liệu mã nguồn mở tiên tiến nhất thế giới." Tuy nhiên, PostgreSQL không chỉ là quan hệ, nó là object-relational. Sự khác biệt này给它一些优势优于其他开源SQL数据库，如MySQL、MariaDB和Firebird。PostgreSQL是AWS云上将内部RDBMS迁移到云上开源RDBMS的显而易见的选择。

Trong Phần 2 của blog này, tôi sẽ bao gồm Postgre backup restore và recovery.

Sử dụng tab Feedback để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi. Bạn cũng có thể trò chuyện ngay để bắt đầu cuộc hội thoại.

Tìm hiểu thêm về dịch vụ Cơ sở dữ liệu của chúng tôi

Thành Thạo PostgreSQL: Hướng Dẫn Cài Đặt & Cấu Hình – Phần 1