Installing And Configuring PostgreSQL On CentOS
PostgreSQL is a reliable relational database for web applications, internal tools, and reporting systems running on CentOS. A careful setup covers repository selection, initialisation, network access, authentication, service management, and backups rather than stopping after package installation.
CentOS systems in Australian offices, hosting facilities, and cloud regions such as Sydney often run applications on the same private network as the database. Keeping PostgreSQL private by default reduces exposure and makes routine administration easier across Australian Eastern Standard Time and daylight-saving periods.
Choosing A Compatible PostgreSQL Package
Check the operating system before installing anything:
cat /etc/centos-release
uname -m
CentOS Linux 7 is end of life, and older CentOS Stream releases also have limited or discontinued support. For a maintained deployment, use a supported CentOS Stream release or an Enterprise Linux-compatible system. The PostgreSQL version should match the application, backup tools, and driver libraries you intend to use.
The PostgreSQL Global Development Group repository, commonly called PGDG, provides current packages independently of the operating system’s default AppStream modules. On CentOS Stream 9, the following commands install PostgreSQL 16:
sudo dnf install -y \
https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql16-server postgresql16
Preparing The CentOS Host
Set a meaningful hostname and confirm that DNS resolves correctly. A database server should use a static address or a dependable DHCP reservation, particularly when application servers in Sydney, Melbourne, or Brisbane connect to it by hostname.
sudo hostnamectl set-hostname db01.example.internal
hostname --fqdn
Keep the operating system updated, and check available disk space before initialising the cluster:
sudo dnf update -y
df -h
free -h
PostgreSQL stores its data under /var/lib/pgsql/16/data when installed from PGDG. Ensure the filesystem has room for database growth, transaction logs, and temporary files. File ownership matters; review Linux file permissions before changing ownership or applying a restrictive umask.
Initialising And Starting The Database
Create the database cluster with the version-specific setup utility:
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16
sudo systemctl status postgresql-16
Check that PostgreSQL is listening locally:
sudo ss -ltnp | grep 5432
sudo -u postgres psql -c "SELECT version();"
The default administrative operating-system account is postgres. Use it for initial local administration, then create named database roles for people and applications. Avoid sharing the postgres login between administrators because individual accounts produce clearer audit records.
Create a database and application role with strong credentials:
sudo -u postgres psql
CREATE ROLE appuser LOGIN PASSWORD 'replace-with-a-long-random-secret';
CREATE DATABASE appdb OWNER appuser;
\q
Configuring Localisation And Connections
The primary configuration file is usually /var/lib/pgsql/16/data/postgresql.conf. Set the server timezone explicitly if reports should use Australian local time:
timezone = 'Australia/Sydney'
This setting handles daylight saving for Sydney and Melbourne. Systems operating in Queensland, which does not observe daylight saving, may need Australia/Brisbane instead. Store timestamps in UTC when possible and convert them in reports or applications.
For remote connections, change the listening address carefully:
listen_addresses = '10.20.30.15'
Then edit pg_hba.conf and allow only the required private subnet:
host appdb appuser 10.20.30.0/24 scram-sha-256
Reload the service after configuration changes:
sudo systemctl reload postgresql-16
Securing Roles And Network Access
Use SCRAM password authentication and avoid broad entries such as 0.0.0.0/0. PostgreSQL access rules are evaluated from top to bottom, so a permissive earlier rule can undermine a restrictive rule later in the file.
Limit the firewall to the application network. For example:
sudo firewall-cmd --permanent \
--add-rich-rule='rule family="ipv4" source address="10.20.30.0/24" port port="5432" protocol="tcp" accept'
sudo firewall-cmd --reload
Do not expose port 5432 directly to the public internet. If remote administration is required, use a VPN or an SSH tunnel. Australian organisations handling personal information should also consider the Privacy Act 1988, the Australian Privacy Principles, and Notifiable Data Breaches obligations. A privacy policy can explain website data handling, but it does not replace an organisation’s database security controls or legal advice.
Checking Operations And Backups
Test a connection from the application host:
psql "host=10.20.30.15 dbname=appdb user=appuser"
For an application that stores uploaded images, keep large files in suitable object or filesystem storage when practical and store metadata in PostgreSQL. A mobile workflow may use a camera intent guide before sending image details to an API backed by the database.
Useful operational checks include:
systemctl status postgresql-16journalctl -u postgresql-16pg_isready -h 10.20.30.15du -sh /var/lib/pgsql/16/data
A logical backup can be created with:
sudo -u postgres pg_dump -Fc appdb > /var/backups/appdb-$(date +%F).dump
Restore testing is essential:
createdb restore_test
pg_restore -d restore_test /var/backups/appdb-2026-01-15.dump
Schedule backups, copy them to separate storage, and test recovery regularly. The important points are to use a supported package source, restrict network access, manage roles deliberately, set the correct local timezone, and prove that backups can actually restore the PostgreSQL database.