LLI Wiki — My Digital Knowledge Base

Installing PostgreSQL on Linux with a dedicated data directory

Installing PostgreSQL on CentOS and Ubuntu, initialising a separate data directory, logging and network access.

This guide describes how to install PostgreSQL on Linux (CentOS and Ubuntu) with dedicated directories for data, logs and backups, then how to configure logging and network access.

Prepare the directories

sudo mkdir -p /pgdata/pgsql/data /pgdata/pgsql/log /pgdata/pgsql/backup
sudo chown -R postgres:postgres /pgdata/pgsql

Installation

CentOS / RHEL 7

sudo yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
sudo yum install -y postgresql10-server

Ubuntu / Debian (distribution packages)

sudo apt install postgresql postgresql-contrib postgresql-client

Ubuntu / Debian (official PGDG repository)

For a specific version, replace postgresql with postgresql-12 (or the version you need).

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql-12 postgresql-contrib postgresql-client

Initialise the data directory

sudo su - postgres
/usr/lib/postgresql/12/bin/initdb --encoding=UTF8 --pgdata=/pgdata/pgsql/data

Manual start of the cluster:

/usr/lib/postgresql/12/bin/pg_ctl -D /pgdata/pgsql/data -l logfile start

Make systemd use the data directory

Create a drop-in override for the cluster's systemd unit (<postgresql-unit>), then add the PGDATA variable:

[Service]
Environment=PGDATA=/pgdata/pgsql/data

Reload systemd:

sudo systemctl daemon-reload

Logging

In postgresql.conf (on Debian/Ubuntu: /etc/postgresql/<version>/main/):

log_destination = 'stderr'
logging_collector = on
log_directory = '/pgdata/pgsql/log/'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0644
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
log_min_messages = warning
log_min_error_statement = error
log_checkpoints = on
log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] %q%u@%d '

# Used only when syslog is a log destination
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
syslog_sequence_numbers = on
syslog_split_messages = on

The data_directory parameter in the same file sets the data directory; changing it requires a restart.

Network access

In postgresql.conf:

listen_addresses = '*'

In pg_hba.conf:

# TYPE  DATABASE  USER  ADDRESS      METHOD
host    all       all   0.0.0.0/0    md5

Restart and check

sudo systemctl restart postgresql
sudo systemctl status postgresql

Notes

Mastodon LLIMastodon SocialFosstodon