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
- The
0.0.0.0/0rule accepts every IPv4 address: in production, restrict it to the client networks. apt-keyis deprecated on recent distributions; the repository key can be stored in/etc/apt/keyrings/instead.