Mastering PostgreSQL Installation on Linux: A Practical Engineering Guide

Production-grade PostgreSQL installation on Linux isn’t a copy-paste affair—it’s the foundation for data durability, low-latency queries, and robust access control. Poor initial setups often yield performance bottlenecks, backup failures, or authentication headaches down the line. Below is a pragmatic installation sequence, tested on both Ubuntu 22.04 LTS and RHEL 8.9, incorporating nuances rarely mentioned in basic how-tos.

1. Preflight: Clean and Update the Host

Residual files or outdated packages from past PostgreSQL attempts frequently cause “cluster is already running” errors during initialization. Always start with a clean slate:

# Ubuntu/Debian
sudo apt update && sudo apt upgrade -y
sudo apt remove --purge postgresql* -y

# RHEL/CentOS
sudo yum update -y
sudo yum remove postgresql* -y

Note: Old clusters may leave /var/lib/postgresql or /var/lib/pgsql populated. Check and delete if this is a fresh deployment.

2. Use the Official PGDG Repo

Both Ubuntu LTS and enterprise RHEL distros may lag the official PostgreSQL release by major versions—critical for LTS security patching and feature parity. The official PGDG repo delivers 14.x, 15.x, and often the latest 16.x.

Ubuntu Example: PostgreSQL 15

wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update

RHEL Example: PostgreSQL 15

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql

3. Install PostgreSQL Server and Client Tools

Always specify the version explicitly, especially when running production clusters alongside old versions for migration.

# Ubuntu, target PostgreSQL 15
sudo apt install -y postgresql-15 postgresql-client-15

# RHEL/CentOS
sudo dnf install -y postgresql15-server postgresql15

Tip: psql --version should report the desired major version. If not, check for conflicting paths in /usr/bin.

4. Database Cluster Initialization

Ubuntu/Debian

Cluster initialization is usually automatic. To confirm:

sudo systemctl status postgresql
ls /etc/postgresql/15/main/

RHEL/CentOS

Explicit initialization required:

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

Check for errors like:

FATAL:  could not create directory "/var/lib/pgsql/15/data/pg_wal": File exists

If present, clear or reassign your data directory.

5. Enable and Start the PostgreSQL Service

# Ubuntu
sudo systemctl enable postgresql
sudo systemctl start postgresql

# RHEL
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

Service not starting? Inspect logs:

journalctl -u postgresql
# or
cat /var/lib/pgsql/15/data/pg_log/postgresql-*.log

6. Secure PostgreSQL: Role Passwords and Auth Config

By default, PostgreSQL enforces 'peer' (socket) auth for local connections. This is secure but inconvenient for application users or for tooling that expects password auth.

Change password and convert to password-based access:

sudo -i -u postgres
psql -c "ALTER USER postgres WITH PASSWORD 'ReplaceWithStrongPassword';"

Then edit pg_hba.conf:

Modify or add:

host    all    all    0.0.0.0/0    md5
local   all    all                md5

Note: Expose 0.0.0.0/0 in dev/test only. For production, restrict by subnet.

Reload to apply:

sudo systemctl reload postgresql

7. Validate Installation and Connectivity

Test access using psql. If -W (prompt for password) fails, retrace pg_hba.conf.

psql -U postgres -h 127.0.0.1 -W

Quick DDL check:

CREATE DATABASE diagtest;
\c diagtest
CREATE TABLE t(id serial PRIMARY KEY, val text);
INSERT INTO t(val) VALUES ('test row');
SELECT * FROM t;

8. Core Optimizations (For Production Deployments)

Default configs (postgresql.conf) work for small VMs, but under-provision for large RAM/CPU nodes. Common adjustments for a 16GB RAM VM:

# Memory
shared_buffers = 4GB
work_mem = 64MB
maintenance_work_mem = 1GB

# WAL and checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 128MB
max_wal_size = 2GB

# Logging
logging_collector = on
log_directory = 'pg_log'
log_filename = 'pgsql-%a.log'
log_min_duration_statement = 250  # flag slow queries

On Ubuntu, pg_config --sysconfdir prints the /etc/postgresql/15/main path. On RHEL, config is under /var/lib/pgsql/15/data/.

Reload the service after edits.

9. Common Issues and Engineering Side Notes

• Port Conflicts: ERROR: could not bind IPv4 socket: Address already in use → Edit postgresql.conf, set port = 5433 if 5432 is occupied.

• Firewalls: PostgreSQL won't accept remote connections if host firewall blocks 5432/tcp. Open with:

  sudo ufw allow 5432/tcp               # Ubuntu
  sudo firewall-cmd --add-port=5432/tcp --permanent && sudo firewall-cmd --reload  # RHEL
  

• Systemd Unit Fails: Sometimes systemd service files reference a version you didn’t install. Check /etc/systemd/system/postgresql@.service for misconfigurations.

• Known issue: On some Debian images, /tmp is mounted noexec by default, breaking extensions like pg_stat_statements. Mount /tmp with exec permissions for extension support.

Practical Postgresql Healthcheck

For monitoring readiness in orchestration or automation, use:

pg_isready -U postgres -h 127.0.0.1
# Returns “accepting connections” or details if startup failed

Conclusion

PostgreSQL on Linux rewards considered initial setup—by tuning data directory ownership, authentication, and core memory settings, long-term maintainability is far easier. Shortcuts lead to troubleshooting overhead later. Bookmark this as a checklist; refer back during upgrades, disaster recovery simulations, and new host builds.

Non-obvious tip: Consider placing the WAL (/var/lib/pgsql/15/data/pg_wal) on a dedicated SSD for sustained write-heavy workloads; the improvement can be substantial.

For further tuning, consult the official PostgreSQL documentation—version mismatches are common sources of config confusion.

(Any gaps or improvements noted in practice? Community feedback is where operational knowledge matures.)