How to Install PostgreSQL

Table of Contents

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:

PGTune

Instalasi PostgreSQL #

Inisialisasi Database #

Client Authentication #

1. Edit File pg_hba.conf #

2. Restart PostgreSQL #

3. Ubah Password User postgres #

4. Kembalikan trust ke md5 #

5. (Opsional) Autologin dengan .pgpass #

6. Test Login #

Rekomendasi Konfigurasi PostgreSQL #

Spesifikasi Server (Contoh) #

postgresql.conf #

runtime.conf #

Related