Câu lệnh hay sử dụng để Quản trị Postgresql?

Tôi đang gom nhanh các lệnh quản trị PostgreSQL theo nhóm thực tế: service, kết nối, DB/user, backup/restore, giám sát và replication. Sau đó tôi sẽ chắt lại thành một “cheat sheet” dễ dùng hằng ngày. Dưới đây là nhóm câu lệnh PostgreSQL hay dùng cho quản trị, mình gom theo tình huống vận hành thực tế.

1) Bật / tắt / kiểm tra service

su - postgres

pg_ctl -D $PGDATA start
pg_ctl -D $PGDATA stop
pg_ctl -D $PGDATA restart
pg_ctl -D $PGDATA status

# Có ghi log khi start
pg_ctl -D $PGDATA -l $PGLOG/logfile_start start

# Xem log nhanh khi có lỗi
tail -n 100 -f $PGLOG/postgresql_*.log
tail -n 100 -f $PGLOG/logfile_start

Ngoài ra có thể dụng:

service postgresql-12 status/start

hoặc systemcl status/start/stop postgresql-12

2) Kết nối và thao tác nhanh trong psql

psql -h localhost -p 5432 -U postgres
psql -h <host> -p 5432 -U <user> -d <dbname>

# Chạy 1 lệnh rồi thoát
psql -h localhost -p 5432 -c "select now();"

Trong psql:

\l          -- list database
\c dbname   -- connect database
\dt         -- list table
\du         -- list role/user
\dn         -- list schema
\df         -- list function
\x          -- expanded display
\timing     -- bật thời gian chạy câu lệnh

3) Quản trị database / schema / bảng

CREATE DATABASE mydb;
DROP DATABASE mydb;

CREATE SCHEMA app;
ALTER SCHEMA app OWNER TO appuser;

CREATE TABLE test (
    id bigint primary key,
    name text,
    created_at timestamp default now()
);

ALTER TABLE test ADD COLUMN status int;
ALTER TABLE test RENAME COLUMN name TO full_name;
DROP TABLE test;
TRUNCATE TABLE test;

Đây là nhóm lệnh nền tảng cho quản trị DB/table, cũng là các phần chuẩn trong bộ tài liệu PostgreSQL chính thức.

4) Quản trị user / role / quyền

-- tạo user
CREATE USER appuser WITH PASSWORD 'StrongPassword';

-- hoặc role có quyền login
CREATE ROLE appuser LOGIN PASSWORD 'StrongPassword';

-- gán quyền
GRANT CONNECT ON DATABASE mydb TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;

-- đổi mật khẩu
ALTER USER appuser WITH PASSWORD 'NewStrongPassword';

-- khóa / mở khóa
ALTER USER appuser NOLOGIN;
ALTER USER appuser LOGIN;

-- xóa user
DROP USER appuser;

5) Backup / restore

Backup logical

pg_dump -Fc mydb > /backup/full_mydb_20260317.tar
pg_dump -U postgres -h localhost -p 5432 mydb > mydb.sql
pg_dumpall > all_db.sql

Restore logical

pg_restore -d mydb /backup/full_mydb_20260317.tar
psql -d mydb -f mydb.sql
psql -f all_db.sql postgres

Tài liệu PostgreSQL chính thức cũng tách riêng phần SQL dump, restore, PITR, WAL archiving.

6) Backup vật lý / clone standby / đồng bộ

pg_basebackup -h <primary_ip> -p 5432 -U replication \
  -D /data/pgdata -Fp -Xs -P -R

Ví dụ thực tế trong tài liệu của bạn:

pg_basebackup -h 10.3.11.68 -U replication -p 5432 \
  -D /database/pgdata -Fp -Xs -P -R -W

Dùng rất nhiều khi dựng standby mới hoặc đồng bộ lại replica.

7) Kiểm tra replication / standby

Trên primary

select * from pg_stat_replication;
select * from pg_replication_slots;
show synchronous_standby_names;

Trên standby

select * from pg_stat_wal_receiver;

select pg_is_in_recovery(),
       pg_is_wal_replay_paused(),
       pg_last_wal_receive_lsn(),
       pg_last_wal_replay_lsn(),
       pg_last_xact_replay_timestamp();

Kiểm tra lag

select pid,
       application_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)  as sending_lag,
       pg_wal_lsn_diff(sent_lsn, flush_lsn)             as receiving_lag,
       pg_wal_lsn_diff(flush_lsn, replay_lsn)           as replaying_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) as total_lag
from pg_stat_replication;

Các view pg_stat_replication, pg_stat_wal_receiver, pg_stat_replication_slots hay dùng.

8) Giám sát session / query / lock

-- session đang chạy
select pid, usename, datname, state, wait_event_type, wait_event, query
from pg_stat_activity;

-- session chạy lâu
select pid, now() - query_start as duration, usename, datname, state, query
from pg_stat_activity
where state <> 'idle'
order by duration desc;

-- kill query
select pg_cancel_backend(<pid>);

-- kill session
select pg_terminate_backend(<pid>);

pg_stat_activity là view quan trọng nhất để xem phiên kết nối và truy vấn đang chạy. Docs chính thức có mục monitoring riêng cho view này.

9) Giám sát hiệu năng query

-- cần enable extension trước
create extension if not exists pg_stat_statements;

select query, calls, total_exec_time, mean_exec_time, rows
from pg_stat_statements
order by total_exec_time desc
limit 20;

10) Bảo trì định kỳ

VACUUM;
VACUUM ANALYZE;
ANALYZE;
REINDEX DATABASE mydb;
REINDEX TABLE public.mytable;

Kiểm tra dead tuples:

select relname,
       n_live_tup as live_tuples,
       n_dead_tup as dead_tuples,
       last_autovacuum
from pg_stat_user_tables
order by n_dead_tup desc;

Docs PostgreSQL có hẳn phần Routine Vacuuming và Routine Reindexing.

11) Kiểm tra dung lượng / tablespace / WAL

select pg_size_pretty(pg_database_size('mydb'));

select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;

select schemaname, relname,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname))
from pg_stat_user_tables
order by pg_total_relation_size(schemaname||'.'||relname) desc
limit 20;

Clean archive WAL:

pg_archivecleanup -d /data01/pgdata/pg_wal 00000002000000110000009C

12) EXPLAIN để troubleshoot query chậm

EXPLAIN SELECT * FROM foo WHERE id = 1;
EXPLAIN ANALYZE SELECT * FROM foo WHERE id = 1;

Đây là lệnh cực hay dùng khi tuning SQL; docs chính thức có ví dụ EXPLAIN và EXPLAIN ANALYZE.

13) Lệnh OS đi kèm mà DBA PostgreSQL hay dùng

df -h              # kiểm tra disk
top                # CPU
free -g            # RAM
iostat -xn 5       # IO
tail -f /var/log/messages

Bạn dùng các lệnh này để kiểm tra tài nguyên OS trước khi kết luận lỗi DB.

---

Bộ “20 lệnh nên nhớ” hằng ngày

Nếu cần nhớ nhanh, tôi khuyên ưu tiên thuộc nhóm này:

pg_ctl start
pg_ctl stop
pg_ctl restart
pg_ctl status
psql -h <host> -p 5432 -U <user> -d <db>
pg_dump -Fc <db> > backup.tar
pg_restore -d <db> backup.tar
pg_basebackup -h <primary> -U replication -D <dir> -Fp -Xs -P -R

\l
\c dbname
\dt
\du
select * from pg_stat_activity;
select * from pg_stat_replication;
select * from pg_stat_wal_receiver;
select * from pg_replication_slots;
select * from pg_stat_statements order by total_exec_time desc limit 20;
select * from pg_stat_user_tables order by n_dead_tup desc;
vacuum analyze;
reindex database mydb;
explain analyze <sql>;
select pg_cancel_backend(<pid>);
select pg_terminate_backend(<pid>);

=============================
TƯ VẤN: Click Here hoặc Hotline/Zalo 090.29.12.888
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
cơ sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c/21c/23c/23ai, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty