PostgreSQL Installation

Distribution packages often lag several major versions behind. For production, use the PostgreSQL Global Development Group (PGDG) repositories so you can choose the major version and receive minor-version security updates promptly.

Decisions to make before installing

• Major version. Pick a version with several years of support remaining. Upgrading major versions is a project; upgrading minor versions is a restart.
• Data directory location. Put PGDATA on its own filesystem, sized and monitored separately from the root volume. A full root filesystem should not be able to stop the database, and a growing database should not fill the root filesystem.
• Filesystem. ext4 or XFS with noatime. Avoid network filesystems for the data directory.
• Locale and encoding. UTF8 encoding. The collation chosen at initdb time affects index ordering, and changing it later requires reindexing every text index.

Install and initialise

sudo apt install -y postgresql-common && sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

sudo apt install -y postgresql-17
 
# Initialise a cluster on a dedicated filesystem with an explicit collation.
sudo pg_dropcluster --stop 17 main
sudo pg_createcluster 17 main \
  --datadir=/srv/pgdata/17/main \
  --locale=en_US.UTF-8 \
  --encoding=UTF8
sudo pg_ctlcluster 17 main start

Immediately after the first start

1. Set a password for the postgres role, or configure certificate authentication.
2. Replace the default pg_hba.conf entries with specific host and network rules — see Connection Management.
3. Apply the production configuration and restart once, rather than incrementally.
4. Configure WAL archiving before the database receives real data, so point-in-time recovery is available from the beginning.
5. Create the application role with limited privileges, and use it from the application.