PostgreSQL

Introduction

PostgreSQL is a powerful, open-source object-relational database with over 35 years of active development. It is fully ACID compliant and supports advanced features such as MVCC, partial and expression indexes, full-text search, JSONB storage, geospatial types (via PostGIS), logical replication, and extensible procedural languages. PostgreSQL is frequently chosen for workloads demanding strict data integrity, complex queries, and rich extension support.

How to Install

Starting via Binary

Quick Start

# download source
wget https://ftp.postgresql.org/pub/source/v15.1/postgresql-15.1.tar.gz
tar xf postgresql-15.1.tar.gz && rm -f postgresql-15.1.tar.gz
cd postgresql-15.1/

# compile
mkdir bld && cd bld
../configure --prefix=/opt/pgsql --with-systemd
make -j `grep processor /proc/cpuinfo | wc -l`
make install

# postinstallation
groupadd postgres
useradd -r -g postgres -s /bin/false postgres
mkdir /opt/pgsql/data /opt/pgsql/logs
chown postgres:postgres /opt/pgsql -R

# startup
/opt/pgsql/bin/pg_ctl -D /opt/pgsql/data initdb
/opt/pgsql/bin/pg_ctl -D /opt/pgsql/data -l /opt/pgsql/logs/pgsql.log start

Config and Boot

Config

/opt/pgsql/data/postgresql.conf

# PostgreSQL configuration file
# DB Version: 15
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 4 GB
# CPUs num: 8
# Data Storage: ssd

max_connections = 200
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 1310kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# Data directory
data_directory = '/opt/pgsql/data'
# HBA file
hba_file = '/opt/pgsql/data/pg_hba.conf'
# Ident file
ident_file = '/opt/pgsql/data/pg_ident.conf'

Boot(systemd)

# boot
cat > /etc/systemd/system/postgresql.service << "EOF"
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)

[Service]
Type=notify
User=postgres
ExecStart=/opt/pgsql/bin/postgres -D /opt/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl start postgresql.service
systemctl enable postgresql.service

Verify

# syntax check
/opt/pgsql/bin/postgres --version
postgres (PostgreSQL) 15.1

Troubleshooting

# problem 1
# configure: error: readline library not found
apt install libreadline-dev

# problem 2
# configure: error: header file <systemd/sd-daemon.h> is required for systemd support
apt install libsystemd-dev

Starting via Kubernetes

# add and update repo
helm repo add bitnami https://charts.bitnami.com/bitnami
helm update

# get charts package
helm pull bitnami/postgresql --untar
cd postgresql

# configure and run
vim values.yaml
...
helm -n middleware install postgresql .

Reference:

1. Official Website
2. Repository
3. Percona Distribution for PostgreSQL
4. PGsqlConfig Generate