نصب و پیکربندی PostgreSQL

در این فصل، PostgreSQL 16 را روی Windows و Linux نصب می‌کنیم، فایل‌های پیکربندی postgresql.conf و pg_hba.conf را بررسی می‌کنیم و tuning اولیه برای پروداکشن انجام می‌دهیم.

نصب در ویندوز

مرحله ۱: دانلود

1. به سایت رسمی برو: postgresql.org/download/windows
2. EnterpriseDB Installer را دانلود کن (نسخه ۱۶)
3. فایل را با Run as Administrator اجرا کن

مرحله ۲: نصب

در wizard:

• Installation Directory: مسیر نصب (پیش‌فرض C:Program FilesPostgreSQL16)
• Data Directory: مسیر داده (پیش‌فرض C:Program FilesPostgreSQL16data)
• Password: رمز user postgres (به یاد بسپارید!)
• Port: پیش‌فرض 5432
• Locale: Persian, Iran یا Default
• Components: همه (PostgreSQL Server، pgAdmin 4، Stack Builder، Command Line Tools)

مرحله ۳: تست

REM در Command Prompt
psql -U postgres -h localhost
REM رمز را وارد کنید

postgres=# SELECT version();

نصب در Linux (Ubuntu/Debian)

# افزودن repository رسمی PostgreSQL
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt update
sudo apt install -y postgresql-16 postgresql-contrib-16 postgresql-client-16

# سرویس را شروع کن
sudo systemctl start postgresql
sudo systemctl enable postgresql

# تست
sudo -u postgres psql -c "SELECT version();"

تنظیم رمز postgres

sudo -u postgres psql

postgres=# ALTER USER postgres WITH PASSWORD 'your_strong_password';
postgres=# q

نصب با Docker

docker run -d 
  --name postgres16 
  -e POSTGRES_PASSWORD=secret 
  -e POSTGRES_USER=postgres 
  -e POSTGRES_DB=mydb 
  -p 5432:5432 
  -v pgdata:/var/lib/postgresql/data 
  postgres:16

# اتصال
docker exec -it postgres16 psql -U postgres -d mydb

docker-compose

version: "3.9"
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: secret
      POSTGRES_USER: shop
      POSTGRES_DB: shopdb
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./initdb:/docker-entrypoint-initdb.d
    ports:
      - "5432:5432"
    restart: unless-stopped
volumes:
  pgdata:

آشنایی با psql

psql command-line client رسمی PostgreSQL است:

psql -U postgres -h localhost -p 5432 -d postgres

# با pass via env
PGPASSWORD=secret psql -U postgres ...

# اجرای فایل SQL
psql -U postgres -d mydb -f script.sql

# اجرای یک query
psql -U postgres -d mydb -c "SELECT NOW();"

دستورات meta در psql

l                  لیست دیتابیس‌ها
c dbname           اتصال به دیتابیس
dt                 لیست جدول‌ها (در schema جاری)
dt+ schema.*       لیست جدول‌ها در schema خاص
d table_name       ساختار جدول
d+ table_name      ساختار جدول با اطلاعات بیشتر
du                 لیست users و roles
dn                 لیست schema‌ها
df                 لیست توابع
dv                 لیست view‌ها
di                 لیست ایندکس‌ها
dx                 لیست extensions نصب‌شده
timing             نمایش زمان query
x                  حالت expanded display
e                  ویرایش query در editor
q                  خروج
?                  راهنمای دستورات meta
h CREATE TABLE     راهنمای SQL

میانبرهای مفید

-- query‌های قبلی (Up arrow)
-- SELECT NOW();

-- اجرای فایل
i /path/to/file.sql

-- ذخیره خروجی به فایل
o output.txt
SELECT * FROM users;
o
-- خروجی در output.txt ذخیره شد

-- اجرای دستور سیستمی
! ls -la

-- اجرای query مهم به‌صورت expanded
SELECT * FROM users LIMIT 1 gx

postgresql.conf – فایل پیکربندی اصلی

این فایل تمام تنظیمات سرور را دارد. پیدا کردن مسیر:

SHOW config_file;
-- /etc/postgresql/16/main/postgresql.conf  (Ubuntu)
-- C:/Program Files/PostgreSQL/16/data/postgresql.conf  (Windows)

SHOW data_directory;
SHOW hba_file;

تنظیمات مهم پیکربندی

#----------------- اتصال -----------------
listen_addresses = 'localhost'        # یا '*' برای همه
port = 5432
max_connections = 100                 # برای پروداکشن، با pgBouncer این را پایین نگه دارید

#----------------- حافظه -----------------
shared_buffers = 256MB                # 25% از RAM (برای 1GB RAM = 256MB)
work_mem = 4MB                        # حافظه برای sort/hash هر operation
maintenance_work_mem = 64MB           # برای CREATE INDEX، VACUUM
effective_cache_size = 1GB            # تخمین کش OS - 50-75% RAM

#----------------- WAL -----------------
wal_level = replica                   # minimal، replica، logical
max_wal_size = 1GB
min_wal_size = 80MB
checkpoint_timeout = 5min
checkpoint_completion_target = 0.9    # smoother checkpoints

#----------------- Query Planner -----------------
random_page_cost = 1.1                # برای SSD (پیش‌فرض 4 برای HDD)
effective_io_concurrency = 200        # SSD = 200، HDD = 2

#----------------- لاگ -----------------
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000     # log query‌های > 1 ثانیه
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a '
log_lock_waits = on
log_temp_files = 0

#----------------- Stats -----------------
track_activities = on
track_counts = on
track_io_timing = on                  # برای EXPLAIN ANALYZE دقیق

#----------------- Autovacuum -----------------
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

اعمال تغییرات

# بعد از تغییر postgresql.conf:

# در psql:
SELECT pg_reload_conf();   -- برای تنظیمات بدون نیاز به restart

# یا restart کامل:
sudo systemctl restart postgresql

# دیدن تنظیم فعلی:
SHOW shared_buffers;
SHOW work_mem;

# دیدن همه تنظیمات:
SELECT name, setting, unit, context FROM pg_settings WHERE name LIKE '%mem%';

pg_hba.conf – احراز هویت

این فایل کنترل می‌کند چه کسی از کجا با چه روشی می‌تواند وصل شود:

# TYPE    DATABASE  USER       ADDRESS         METHOD
local     all       all                        peer
host      all       all        127.0.0.1/32    scram-sha-256
host      all       all        ::1/128         scram-sha-256
host      all       all        0.0.0.0/0       scram-sha-256
host      shopdb    shop_user  10.0.0.0/24     scram-sha-256
hostssl   all       all        0.0.0.0/0       scram-sha-256

توضیح فیلدها

• TYPE: local (Unix socket)، host (TCP)، hostssl (TCP+SSL)، hostnossl
• DATABASE: نام دیتابیس یا all
• USER: نام user یا all
• ADDRESS: IP و subnet (CIDR)
• METHOD: trust (بدون رمز)، md5، scram-sha-256 (پیشنهاد)، peer (Unix user matching)

# بعد از تغییر pg_hba.conf:
sudo systemctl reload postgresql
# یا
SELECT pg_reload_conf();

ساخت User و Database

-- اتصال به‌عنوان postgres (superuser)
psql -U postgres

-- ساخت user
CREATE USER shop_user WITH PASSWORD 'StrongPass123!';

-- ساخت دیتابیس
CREATE DATABASE shopdb OWNER shop_user;

-- یا بدون owner، بعد grant دهید
CREATE DATABASE shopdb;
GRANT ALL PRIVILEGES ON DATABASE shopdb TO shop_user;

-- ساخت user با امکانات خاص
CREATE USER readonly_user WITH PASSWORD 'pass' NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT CONNECT ON DATABASE shopdb TO readonly_user;
c shopdb
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Tuning اولیه

ابزار pgtune می‌تواند بر اساس مشخصات سرور پیکربندی پیشنهاد دهد:

pgtune.leopard.in.ua

مثال: سرور با 8GB RAM، SSD، Web App

max_connections = 200
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
huge_pages = try
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

pgAdmin 4 – رابط گرافیکی

pgAdmin محبوب‌ترین GUI برای PostgreSQL است. در ویندوز همراه نصب می‌آید. در Linux:

# pgAdmin desktop
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'
sudo apt update
sudo apt install pgadmin4-desktop

# یا web version با Docker
docker run -d 
  --name pgadmin 
  -e PGADMIN_DEFAULT_EMAIL=admin@example.com 
  -e PGADMIN_DEFAULT_PASSWORD=secret 
  -p 8080:80 
  dpage/pgadmin4

گزینه‌های دیگر GUI

• DBeaver: رایگان، چندپلتفرم، چنددیتابیس
• DataGrip: محصول JetBrains، قدرتمند، پولی
• TablePlus: زیبا، محبوب در Mac
• Beekeeper Studio: متن‌باز، رایگان

Backup و Restore سریع

# Backup
pg_dump -U postgres -d shopdb -F c -f shopdb.backup

# Backup همه دیتابیس‌ها
pg_dumpall -U postgres > all_dbs.sql

# Restore
pg_restore -U postgres -d shopdb_new shopdb.backup

# Backup با تمام options
pg_dump -U postgres 
  --host=localhost 
  --format=custom 
  --compress=9 
  --verbose 
  --file=shopdb_$(date +%Y%m%d).backup 
  shopdb

(در فصل ۱۲ مفصل بحث می‌کنیم.)

بررسی سلامت سرور

-- نسخه و platform
SELECT version();

-- اندازه دیتابیس‌ها
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- اتصالات فعال
SELECT count(*) FROM pg_stat_activity;

-- اتصالات هر دیتابیس
SELECT datname, count(*) 
FROM pg_stat_activity
WHERE state IS NOT NULL
GROUP BY datname;

-- query‌های در حال اجرا
SELECT pid, usename, datname, state, query, query_start
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

-- بزرگ‌ترین جدول‌ها
SELECT schemaname, tablename, 
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 10;

بهترین شیوه‌ها

• برای پروداکشن، PostgreSQL رسمی (نه نسخه packaged) را نصب کنید
• data directory را روی volume جداگانه (SSD) قرار دهید
• برای backup فضای جدا داشته باشید
• tuning اولیه با pgtune
• scram-sha-256 به‌جای md5
• اتصال شبکه را با firewall محدود کنید
• SSL برای اتصال‌های خارجی
• monitoring از روز اول راه‌اندازی کنید

جمع‌بندی

• PostgreSQL در Windows با installer EnterpriseDB
• در Linux با apt یا yum
• postgresql.conf: تنظیمات سرور
• pg_hba.conf: کنترل اتصال و احراز هویت
• psql: ابزار اصلی command-line
• pgAdmin / DBeaver: GUI
• tuning اولیه با pgtune