PostgreSQL là hệ quản trị cơ sở dữ liệu mã nguồn mở mạnh mẽ nhất hiện nay. Được dùng bởi Apple, Instagram, Spotify, và nhiều tech giants khác. Bài viết này hướng dẫn setup PostgreSQL trên Ubuntu, cấu hình cơ bản, tạo database và user, kết nối từ xa, và các best practices.

    PostgreSQL là gì?

    PostgreSQL là object-relational database system (ORDBMS), nổi tiếng với:

    • ACID compliant: Đảm bảo tính toàn vẹn dữ liệu tuyệt đối.
    • JSON support: Lưu trữ cả relational và document data.
    • Full-text search: Tích hợp sẵn tìm kiếm text.
    • PostGIS: Hỗ trợ geospatial data.
    • Replication: Hỗ trợ replication và clustering.

    So với MySQL, PostgreSQL mạnh hơn về data integrity và advanced features, nhưng cần more configuration.

    1. Cài đặt PostgreSQL

    Trên Ubuntu/Debian

    # Cập nhật package list
apt update

# Cài đặt PostgreSQL
apt install postgresql postgresql-contrib -y

# Kiểm tra status
systemctl status postgresql

    Trên CentOS/RHEL

    # Cài đặt PostgreSQL
dnf install postgresql-server postgresql-contrib -y

# Initialize database
postgresql-setup --initdb

# Enable và start
systemctl enable postgresql
systemctl start postgresql

    Dùng Docker

    # Chạy PostgreSQL container
docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=your_password \
  -e POSTGRES_USER=admin \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  postgres:16-alpine

# Kiểm tra
docker ps

    2. Cấu hình PostgreSQL

    File cấu hình chính nằm tại /etc/postgresql/16/main/postgresql.conf (Ubuntu).

    Cấu hình cơ bản

    # Mở file cấu hình
nano /etc/postgresql/16/main/postgresql.conf

# Các thông số quan trọng:

# Listen on all interfaces
listen_addresses = '*'

# Port (default 5432)
port = 5432

# Maximum connections
max_connections = 100

# Shared memory buffer (25% RAM)
shared_buffers = 256MB

# WAL settings
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# Logging
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# Query tuning
effective_cache_size = 1GB
maintenance_work_mem = 128MB
work_mem = 64MB

    Cấu hình Authentication

    File pg_hba.conf kiểm soát authentication methods:

    # Mở file
nano /etc/postgresql/16/main/pg_hba.conf

# Cấu hình:
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             postgres                                peer
local   all             all                                     peer

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

# Allow remote connections (thay đổi IP theo nhu cầu)
host    all             all             0.0.0.0/0               md5
host    all             all             192.168.1.0/24          md5

# Restart để áp dụng
systemctl restart postgresql

    3. Quản lý User và Database

    Đăng nhập PostgreSQL

    # Đăng nhập với postgres user
sudo -u postgres psql

# Hoặc đăng nhập với password
psql -h localhost -U postgres -d postgres

    Tạo User

    -- Tạo user mới
CREATE USER myuser WITH PASSWORD 'strong_password';

-- Tạo user với quyền superuser
CREATE USER admin WITH SUPERUSER PASSWORD 'admin_password';

-- Thay đổi password
ALTER USER myuser WITH PASSWORD 'new_password';

-- Xem danh sách users
\du

    Tạo Database

    -- Tạo database
CREATE DATABASE mydb;

-- Tạo database với owner
CREATE DATABASE mydb OWNER myuser;

-- Xem danh sách databases
\l

-- Kết nối vào database
\c mydb

-- Xóa database
DROP DATABASE mydb;

    Phân quyền

    -- Grant all privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

-- Grant schema access
GRANT ALL PRIVILEGES ON SCHEMA public TO myuser;

-- Grant table access
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

-- Grant sequence access
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

-- Set default privileges cho tables tạo sau
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO myuser;

    4. Kết nối từ xa

    Cấu hình Firewall

    # Mở port PostgreSQL
ufw allow 5432/tcp

# Hoặc chỉ cho phép từ specific IP
ufw allow from 192.168.1.0/24 to any port 5432

    Kết nối từ Client

    # Từ command line
psql -h your_server_ip -U myuser -d mydb

# Connection string
psql "postgresql://myuser:password@your_server_ip:5432/mydb"

    Dùng pgAdmin

    pgAdmin là GUI tool phổ biến để quản lý PostgreSQL:

    • Tải pgAdmin từ pgadmin.org
    • Thêm server mới: Server → Register → Server
    • Điền thông tin: Host, Port, Database, Username, Password

    5. Backup và Restore

    Backup Database

    # Backup single database
pg_dump -U postgres -h localhost mydb > backup.sql

# Backup với compression
pg_dump -U postgres -h localhost mydb | gzip > backup.sql.gz

# Backup all databases
pg_dumpall -U postgres -h localhost > all_databases.sql

# Backup với format custom (nén hơn, có thể restore chọn lọc)
pg_dump -U postgres -h localhost -F c mydb > backup.dump

    Restore Database

    # Restore từ SQL file
psql -U postgres -h localhost mydb < backup.sql

# Restore từ compressed file
gunzip -c backup.sql.gz | psql -U postgres -h localhost mydb

# Restore từ custom format
pg_restore -U postgres -h localhost -d mydb backup.dump

# Restore all databases
psql -U postgres -h localhost -f all_databases.sql

    Auto Backup với Cron

    # Tạo script backup
cat > /usr/local/bin/backup-postgres.sh << 'EOF'
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/var/backups/postgres"
DB_NAME="mydb"
DB_USER="postgres"

# Tạo thư mục backup nếu chưa có
mkdir -p $BACKUP_DIR

# Backup
pg_dump -U $DB_USER -h localhost -F c $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.dump.gz

# Xóa backup cũ hơn 7 ngày
find $BACKUP_DIR -name "*.gz" -mtime +7 -delete

echo "Backup completed: $DATE"
EOF

chmod +x /usr/local/bin/backup-postgres.sh

# Thêm vào crontab
crontab -e

# Chạy backup hàng ngày lúc 2h sáng
0 2 * * * /usr/local/bin/backup-postgres.sh >> /var/log/backup-postgres.log 2>&1

    6. Performance Tuning

    Indexing

    -- Tạo index cho columns thường query
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(created_at);
CREATE INDEX idx_products_category ON products(category_id);

-- Index cho full-text search
CREATE INDEX idx_articles_content ON articles USING gin(to_tsvector('english', content));

-- Xem indexes
\d users

-- Xóa index
DROP INDEX idx_users_email;

    Query Analysis

    -- Explain query để xem execution plan
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- Kết quả cho biết:
-- - Seq Scan vs Index Scan
-- - Cost estimation
-- - Actual execution time

    Vacuum và Analyze

    -- Vacuum để reclaim space và update statistics
VACUUM ANALYZE;

-- Vacuum full (khóa bảng, chạy khi không có user)
VACUUM FULL;

-- Auto-vacuum chạy tự động, nhưng có thể tune
-- Trong postgresql.conf:
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 1min

    7. Security Best Practices

    • [ ] Đổi postgres password: Password mặc định rỗng, phải đổi ngay.
    • [ ] Dùng md5 hoặc scram-sha-256: Không dùng trust cho remote connections.
    • [ ] Giới hạn connections: max_connections phù hợp với RAM.
    • [ ] Backup regularly: Automate backup với cron.
    • [ ] Update thường xuyên: PostgreSQL có security updates.
    • [ ] Firewall: Chỉ mở port cho IPs cần thiết.
    • [ ] SSL: Bật SSL cho connections từ xa.

    Bật SSL

    # Tạo self-signed certificate
openssl req -new -x509 -days 365 -nodes \
  -out /etc/postgresql/ssl/server.crt \
  -keyout /etc/postgresql/ssl/server.key

chown postgres:postgres /etc/postgresql/ssl/server.*
chmod 600 /etc/postgresql/ssl/server.key

# Bật SSL trong postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file = '/etc/postgresql/ssl/server.key'

systemctl restart postgresql

    8. Troubleshooting

    Lỗi kết nối

    • Connection refused: Kiểm tra PostgreSQL đang chạy, port đúng.
    • Password authentication failed: Kiểm tra username/password.
    • No route to host: Kiểm tra firewall, network.
    # Kiểm tra PostgreSQL đang chạy
systemctl status postgresql

# Kiểm tra port listening
ss -tlnp | grep 5432

# Kiểm tra logs
tail -f /var/log/postgresql/postgresql-*-main.log

    Lỗi disk full

    # Xem disk usage
df -h

# Xem database size
SELECT pg_size_pretty(pg_database_size('mydb'));

# Xem table size
SELECT table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_total_relation_size(quote_ident(table_name)) DESC;

# Vacuum để reclaim space
VACUUM FULL;

    Kết Luận

    PostgreSQL là lựa chọn tuyệt vời cho production databases. Với setup và configuration đúng, nó có thể xử lý hàng triệu transactions mỗi ngày.

    • Cài đặt và cấu hình cơ bản.
    • Tạo users và databases với proper permissions.
    • Kết nối từ xa an toàn.
    • Backup và restore tự động.
    • Performance tuning với indexing và vacuum.

    Kết hợp với Docker Security để deploy PostgreSQL container an toàn, hoặc Uptime Monitoring để theo dõi database availability.

    Bài viết liên quan

    Chào các bạn mình là Quốc Hùng , mình sinh ra thuộc cung song tử ,song tử luôn khẳng định chính mình ,luôn luôn phấn đấu vượt lên phía trước ,mình sinh ra và lớn lên tại vùng đất võ cổ truyền ,đam mê của mình là coder ,ngày đi học tối về viết blog ...