Postgres setup with external volume for Fedora server

postgres_fedora.md

Postgres setup with external volume for Fedora server

Setup and install Postgres

sudo dnf update
sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb

Use the external volume

Replace instances of /mnt/your_volume with the correct volume path.

sudo rsync -av /var/lib/pgsql/data /mnt/your_volume/
# Backup the exiting data folder.
sudo mv /var/lib/pgsql/data /var/lib/pgsql/data.backup
sudo ln -s /mnt/your_volume/data /var/lib/pgsql/data

Modify the PostgreSQL config file /var/lib/pgsql/data/pg_hba.conf. Change METHOD to "trust" in the line that should look like the below:

# TYPE  DATABASE    USER        ADDRESS                 METHOD
local   all         all                                 peer

Ensure data ownership for the postgres user:

sudo chown -h postgres:postgres /var/lib/pgsql/data
sudo chown -R postgres:postgres /mnt/your_volume/data

Also ensure correct SELinux context:

sudo dnf install policycoreutils-python-utils
sudo semanage fcontext -a -t postgresql_db_t "/mnt/your_volume(/.*)?"
sudo restorecon -Rv /mnt/your_volume

Listen on all addresses

This allows accepting connections from the internet.

Modify /var/lib/pgsql/data/postgresql.conf: find the line that starts with #listen_addresses = 'localhost' and edit it to allow all addresses:

listen_addresses = '*'

Modify /var/lib/pgsql/data/pg_hba.conf adding a line at the bottom:

host    all             all              0.0.0.0/0              md5

Enable and start the service

sudo systemctl start postgresql
sudo systemctl enable postgresql

Confirm everything is fine:

sudo systemctl status postgresql

Create new user

sudo su - postgres
psql

CREATE USER mysuperuser WITH SUPERUSER CREATEDB CREATEROLE PASSWORD 'mypassword';