PostgreSQL adalah sistem manajemen basis data relasional open-source yang kuat dan andal. Dirancang untuk menangani beban kerja yang kompleks, PostgreSQL mendukung berbagai fitur canggih, seperti:
- ACID compliance → menjamin konsistensi dan keandalan data
- Ekstensibilitas → memungkinkan penambahan fungsi kustom
- Dukungan data modern → JSON, kueri geospasial, dan replikasi
PostgreSQL banyak digunakan untuk aplikasi web, analitik data, hingga penyimpanan data yang kompleks
Instalasi PostgreSQL #
Unduh PostgreSQL sesuai sistem operasi yang digunakan melalui repo resmi PostgreSQL: Downloads
Contoh Instalasi di Rocky Linux
# Tambahkan repository resmi PostgreSQL
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Nonaktifkan module bawaan PostgreSQL
dnf -qy module disable postgresql
# Install PostgreSQL 15 beserta paket pendukung
dnf install -y postgresql15.x86_64 \
postgresql15-server.x86_64 \
postgresql15-docs.x86_64 \
postgresql15-contrib.x86_64
Inisialisasi Database #
Buat cluster database baru dengan perintah:
/usr/pgsql-15/bin/postgresql-15-setup initdb
Aktifkan dan jalankan service PostgreSQL:
systemctl enable --now postgresql-15
Setelah service aktif, Anda dapat masuk ke shell PostgreSQL menggunakan user postgres:
su - postgres -c psql
Client Authentication #
Jika menemukan error berikut saat mencoba login ke PostgreSQL:
psql -U postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
FATAL: Peer authentication failed for user "postgres"
Artinya Anda perlu metode autentikasi default (peer) menjadi md5 atau trust (sementara).
1. Edit File pg_hba.conf
#
Ubah metode autentikasi dari peer menjadi md5 atau trust (sementara).
Lokasi file biasanya ada di:
/var/lib/pgsql/15/data/pg_hba.conf
Contoh konfigurasi:
# TYPE DATABASE USER ADDRESS METHOD
local all all md5
local all postgres trust
Penjelasan metode autentikasi:
- Peer → metode default untuk koneksi lokal berbasis user UNIX, tanpa password tambahan.
- MD5 → meminta password yang disimpan dalam format hash MD5 (lebih aman).
- Trust → tanpa otentikasi (tidak aman). Hanya gunakan sementara, misalnya untuk mengubah password.
2. Restart PostgreSQL #
Setelah mengedit pg_hba.conf, restart service:
systemctl restart postgresql-15
3. Ubah Password User postgres
#
Masuk ke PostgreSQL lalu set password baru:
psql -U postgres
ALTER USER postgres WITH PASSWORD 'new-password';
4. Kembalikan trust ke md5
#
Setelah password diubah, edit kembali pg_hba.conf agar koneksi menggunakan password:
# TYPE DATABASE USER ADDRESS METHOD
local all all md5
local all postgres md5
Restart kembali PostgreSQL:
systemctl restart postgresql-15
5. (Opsional) Autologin dengan .pgpass
#
Agar tidak perlu mengetik password setiap kali login, buat file .pgpass:
touch ~/.pgpass
chmod 600 ~/.pgpass
nano ~/.pgpass
Isi dengan format:
# hostname:port:database:username:password
*:*:*:postgres:new-password
6. Test Login #
Sekarang uji kembali akses:
psql -U postgres
Rekomendasi Konfigurasi PostgreSQL #
Spesifikasi Server (Contoh) #
| Komponen | Rekomendasi |
|---|---|
| RAM | 16 GB (disarankan 32 GB untuk workload berat) |
| CPU | 8 core / 16 thread |
| Disk | SSD / NVMe 200–500 GB dengan IOPS tinggi |
postgresql.conf
#
# Network
listen_addresses = '*' # IP yang diizinkan (gunakan spesifik IP untuk security)
port = 5432 # restart required
max_connections = 400 # restart required
unix_socket_directories = '/var/opt/gitlab/postgresql' # restart required
# SSL
ssl = on
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL:!SSLv3:!TLSv1'
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = '/opt/gitlab/embedded/ssl/certs/cacert.pem'
# Memory
shared_buffers = 256MB # minimum 128kB
shared_preload_libraries = '' # restart required
# WAL & Replication
wal_level = minimal
wal_log_hints = off
wal_buffers = -1 # auto based on shared_buffers
min_wal_size = 80MB
max_wal_size = 1GB
max_replication_slots = 0
max_wal_senders = 0
archive_mode = off
hot_standby = off
# Autovacuum
autovacuum_max_workers = 3
autovacuum_freeze_max_age = 200000000
# Locks
max_locks_per_transaction = 128 # minimum 10
# Monitoring
track_activity_query_size = 1024 # restart required
# Include runtime tuning
include 'runtime.conf'
runtime.conf
#
# Memory
work_mem = 16MB
maintenance_work_mem = 16MB
effective_cache_size = 8GB
# WAL & Checkpoint
min_wal_size = 80MB
max_wal_size = 1GB
checkpoint_timeout = 5min
checkpoint_completion_target = 0.9
checkpoint_warning = 30s
# Archiving (disabled)
archive_command = ''
archive_timeout = 0
wal_keep_size = 160
max_slot_wal_keep_size = -1
# Standby / Replication Delay
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
hot_standby_feedback = off
# Query Planner
random_page_cost = 2.0
effective_io_concurrency = 1
default_statistics_target = 1000
# Logging
log_directory = '/var/log/gitlab/postgresql'
log_min_duration_statement = 1000 # log slow queries > 1s
log_checkpoints = off
log_connections = off
log_disconnections = off
log_temp_files = -1
log_line_prefix = ''
# Autovacuum Tuning
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1
log_autovacuum_min_duration = -1
# Timeouts
statement_timeout = 60000
idle_in_transaction_session_timeout = 60000
# Parallelism
max_worker_processes = 8
max_parallel_workers_per_gather = 0
# Locks & Deadlock
deadlock_timeout = '5s'
log_lock_waits = on
# Locale & Date
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
# Performance Tracking
track_io_timing = off
synchronous_commit = on
synchronous_standby_names = ''
Referensi: