PostgreSQL is the default choice for a relational database on Linux, and the install is straightforward. The parts that trip people up are authentication and backups, so this covers those properly.

Installing

# Debian and Ubuntu
sudo apt install postgresql postgresql-contrib

# Fedora and RHEL
sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb

sudo systemctl enable --now postgresql
sudo systemctl status postgresql

Debian and Ubuntu initialise the cluster for you. Red Hat derivatives require the explicit --initdb step, and forgetting it produces a service that fails to start with a message about a missing data directory.

The distribution packages lag upstream. If you need a current major version, the PostgreSQL apt and yum repositories carry every supported release.

Authentication, which is where everyone gets stuck

sudo -u postgres psql

That works. This does not:

psql
# psql: error: FATAL: role "colton" does not exist

The reason is pg_hba.conf, the host-based authentication file. Its default first rule is:

# TYPE  DATABASE  USER  ADDRESS  METHOD
local   all       all            peer

peer authenticates by comparing your operating system username to the database role name. There is a postgres OS user and a postgres role, so sudo -u postgres psql matches. Your own username has no corresponding role.

Two ways forward.

Create a role matching your username:

sudo -u postgres createuser --interactive --pwprompt colton
sudo -u postgres createdb colton
psql    # now works

Or switch to password authentication:

local   all   all             scram-sha-256
host    all   all  127.0.0.1/32  scram-sha-256

sudo systemctl reload postgresql

Use scram-sha-256, not md5. MD5 password authentication is legacy and weaker; SCRAM has been the default since PostgreSQL 14 and is what new configurations should use.

Find the file:

sudo -u postgres psql -c "SHOW hba_file;"
sudo -u postgres psql -c "SHOW config_file;"

Editing pg_hba.conf needs a reload, not a restart. Order matters: the first matching rule wins, so a permissive rule above a restrictive one makes the restrictive one dead.

# check the rules as parsed, including which are unreachable
sudo -u postgres psql -c "SELECT * FROM pg_hba_file_rules;"

Creating a database and a user for an application

CREATE USER myapp WITH PASSWORD 'use-a-real-password';
CREATE DATABASE myapp_production OWNER myapp;

-- from PostgreSQL 15 onward, the public schema is locked down by default
\c myapp_production
GRANT ALL ON SCHEMA public TO myapp;

That last step catches people upgrading from older versions. PostgreSQL 15 removed the implicit CREATE permission on the public schema, so an application that worked on 14 gets permission errors on 15 until the grant is added.

Give each application its own role and database. A single shared superuser across five applications means any one of them compromises all five.

Listening on the network

By default PostgreSQL listens only on localhost, which is the correct default.

# postgresql.conf
listen_addresses = 'localhost'

If an application on another host needs access:

listen_addresses = '10.0.1.5'      # a specific interface, not '*'

# pg_hba.conf, narrow rather than broad
host  myapp_production  myapp  10.0.1.0/24  scram-sha-256

listen_addresses = '*' combined with a permissive pg_hba.conf is how databases end up exposed to the internet. Restrict at both layers, and put a firewall in front as well.

Better still, do not expose it. Reach it over WireGuard or an SSH tunnel:

ssh -L 5432:localhost:5432 user@dbserver
psql -h localhost -p 5432 -U myapp myapp_production

Tuning worth doing

The shipped defaults are deliberately tiny so PostgreSQL starts on a Raspberry Pi. They are not appropriate for a real server.

# postgresql.conf, for a machine with 16GB RAM

shared_buffers = 4GB              # ~25% of RAM
effective_cache_size = 12GB       # ~75%, a hint not an allocation
work_mem = 32MB                   # PER SORT, not per connection
maintenance_work_mem = 1GB
random_page_cost = 1.1            # for SSD; leave at 4 for spinning disks
max_connections = 100

Two of these deserve explanation.

work_mem is per sort operation. A single complex query with several sorts, across many connections, can multiply this considerably. Raising it to 1GB because you have memory is how a server runs out of memory under load.

random_page_cost defaults to 4, which reflects the seek cost of a spinning disk. On SSD or NVMe, random reads cost nearly the same as sequential ones, and leaving it at 4 makes the planner avoid index scans it should be using. This one setting frequently produces the largest single improvement on modern hardware.

Restart after changing shared_buffers; most others take a reload.

sudo systemctl restart postgresql

Backups

The part that matters, done wrong more often than any other.

Logical dumps

# one database, custom format: compressed and selectively restorable
sudo -u postgres pg_dump -Fc myapp_production > myapp_$(date +%F).dump

# everything including roles and tablespaces
sudo -u postgres pg_dumpall > cluster_$(date +%F).sql

Use -Fc. Plain SQL output is human-readable and cannot be restored selectively, restores single-threaded, and is far larger.

# restore
sudo -u postgres pg_restore -d myapp_production myapp_2026-09-14.dump

# restore one table only
sudo -u postgres pg_restore -d myapp_production -t users myapp_2026-09-14.dump

# parallel restore, much faster on a large database
sudo -u postgres pg_restore -j 4 -d myapp_production myapp_2026-09-14.dump

pg_dump does not include roles or passwords. Restoring a dump onto a fresh server produces a database whose owner does not exist. Either use pg_dumpall --globals-only alongside it, or recreate the roles by hand.

sudo -u postgres pg_dumpall --globals-only > globals.sql

Physical backups

sudo -u postgres pg_basebackup -D /backup/base -Ft -z -P

pg_basebackup coordinates with the running server to produce a consistent copy of the data directory. This is the foundation for replication and point-in-time recovery.

Do not simply cp or rsync a live data directory. You will capture files mid-write and the result frequently restores into a corrupt cluster. Either stop the server first or use pg_basebackup.

For point-in-time recovery you need continuous WAL archiving, which is a larger topic. pgBackRest and Barman handle it properly and are worth using rather than assembling yourself.

The step people skip

# restore into a scratch database and confirm it works
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore -d restore_test myapp_2026-09-14.dump
sudo -u postgres psql -d restore_test -c "SELECT count(*) FROM users;"
sudo -u postgres dropdb restore_test

Do this monthly. A backup you have never restored is a hypothesis. Our backup guide covers automating the surrounding pieces, and the systemd timer builder will generate the schedule.

Maintenance

Autovacuum is on by default and mostly handles itself. Two things to watch.

-- table bloat and last vacuum
SELECT schemaname, relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- transaction ID age, which matters at very high values
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

High n_dead_tup with an old last_autovacuum means autovacuum is not keeping up, usually because the table is very write-heavy and the default thresholds are scaled for smaller tables.

Enable slow query logging, because you will want it eventually:

log_min_duration_statement = 1000    # log anything over 1 second
log_checkpoints = on
log_lock_waits = on

Our journalctl guide covers reading the output where PostgreSQL logs to the journal.

pg_stat_statements is the extension worth enabling on any server you care about:

CREATE EXTENSION pg_stat_statements;

SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

Frequently Asked Questions

Why can I connect to PostgreSQL as postgres but not as my own user?

The default pg_hba.conf uses peer authentication for local connections, which matches your operating system username against the database role name. Since there is a postgres OS user and a postgres database role, that combination works, while your own username has no matching role until you create one.

What is the difference between pg_hba.conf and postgresql.conf?

postgresql.conf controls how the server behaves, including memory, connections, and logging. pg_hba.conf controls who may connect, from where, to which database, and using which authentication method. Connection refusals are almost always pg_hba.conf, and performance problems are almost always postgresql.conf.

How do I back up a PostgreSQL database properly?

Use pg_dump for a single database or pg_dumpall to include roles and cluster-wide settings. The custom format with -Fc is preferable because it compresses and allows selective restore. For point-in-time recovery you need continuous WAL archiving rather than periodic dumps.

Is it safe to copy the PostgreSQL data directory as a backup?

Not while the server is running, because you will capture an inconsistent state mid-write. Either stop the server first, or use pg_basebackup which coordinates with the running server to produce a consistent copy. A file-level copy of a live data directory frequently restores into a corrupt cluster.

What should I change from the default PostgreSQL configuration?

shared_buffers to roughly a quarter of system memory, effective_cache_size to about three quarters, and work_mem raised from the very low default. The shipped defaults are deliberately conservative so PostgreSQL starts on almost any hardware, not because they are good settings for a real workload.

Should I run PostgreSQL in Docker?

For development, yes, it is convenient and disposable. For production it is workable but adds considerations around volume persistence, backup access, and upgrades that a native install does not have. Many people run the database natively and the application in containers, which avoids the trickiest part.