How to Install and Secure PostgreSQL on an Ubuntu VPS Print

How to Install and Secure PostgreSQL on an Ubuntu VPS

PostgreSQL is a powerful open-source relational database system widely used for web applications, business platforms, analytics, and development environments.

This guide explains how to install and secure PostgreSQL on an Ubuntu VPS, create databases and users, configure authentication, enable optional remote access, and manage database backups.

The instructions apply to Ubuntu 24.04 LTS, Ubuntu 26.04 LTS, and other supported Ubuntu releases. The PostgreSQL version installed depends on your Ubuntu release and configured repositories.

Prerequisites

• An Ubuntu VPS with root or sudo access.
• A supported Ubuntu release.
• SSH access to your server.
• Updated system packages.

Need a database server? Explore Owned Networks KVM VPS hosting for full root access and flexible server resources.

1. Install PostgreSQL on Ubuntu

Update your system and install PostgreSQL:

sudo apt update && sudo apt upgrade -y
sudo apt install -y postgresql postgresql-contrib

Verify the installation:

psql --version
sudo systemctl status postgresql --no-pager
sudo pg_lsclusters

Ubuntu's PostgreSQL packages typically create a default database cluster during installation.

2. Access the PostgreSQL Console

PostgreSQL creates a system account named postgres for database administration.

Access the PostgreSQL console:

sudo -u postgres psql

Check the database server version:

SELECT version();

Exit the console:

\q

3. Create a PostgreSQL Database and User

Applications should use dedicated database accounts instead of the administrative PostgreSQL account.

Open the PostgreSQL console:

sudo -u postgres psql

Create an application user:

CREATE USER app_user WITH PASSWORD 'REPLACE_WITH_A_STRONG_PASSWORD';

Create a database owned by that user:

CREATE DATABASE app_database OWNER app_user;

Verify the database:

\l

Exit PostgreSQL:

\q

Security recommendation: Use a unique, strong password for each application account. Avoid granting superuser privileges to application users.

4. Test PostgreSQL Authentication

Test the database connection using TCP authentication:

psql -h 127.0.0.1 -U app_user -d app_database

Enter the application password when prompted.

Inside PostgreSQL, verify the connection:

SELECT current_user;
SELECT current_database();
\q

Using 127.0.0.1 explicitly connects through TCP rather than the local Unix socket.

5. Locate PostgreSQL Configuration Files

PostgreSQL configuration paths include the installed major version and cluster name.

Identify the active configuration files:

sudo -u postgres psql -tAc "SHOW config_file;"
sudo -u postgres psql -tAc "SHOW hba_file;"

The main configuration files are:

• postgresql.conf — server settings, networking, and performance.
• pg_hba.conf — client authentication rules.

Use the paths returned by PostgreSQL rather than assuming a specific installed version.

6. Secure PostgreSQL Network Access

For applications running on the same VPS, PostgreSQL should generally remain accessible only through local connections.

Check the current listening ports:

sudo ss -tlnp | grep 5432

Open the active postgresql.conf file and verify:

listen_addresses = 'localhost'

This limits TCP connections to the local server.

Apply configuration changes:

sudo systemctl restart postgresql

Important: Do not expose PostgreSQL directly to the public internet unless there is a specific operational requirement and appropriate network security controls are in place.

7. Configure Remote PostgreSQL Access (Optional)

For applications running on a separate server, PostgreSQL can listen on a private network interface.

For example, configure:

listen_addresses = '10.0.0.10'

Replace the example address with the actual private IP assigned to your PostgreSQL server.

Next, edit the active pg_hba.conf file and add an authentication rule:

host    app_database    app_user    10.0.0.20/32    scram-sha-256

This permits the application user to connect from the specified private IP address using SCRAM authentication.

Allow PostgreSQL traffic only from the authorized application server:

sudo ufw allow from 10.0.0.20 to any port 5432 proto tcp

Restart PostgreSQL:

sudo systemctl restart postgresql

Verify the listening address:

sudo ss -tlnp | grep 5432

Security recommendation: Use private networking, VPN connectivity, or SSH tunneling. Configure TLS when database traffic crosses untrusted networks.

8. Configure Basic PostgreSQL Performance Settings

PostgreSQL performance depends on available memory, storage speed, query patterns, and concurrent connections.

For a small VPS with approximately 2 GB of RAM, the following settings can serve as an initial reference:

shared_buffers = 256MB
effective_cache_size = 768MB
work_mem = 4MB
maintenance_work_mem = 64MB
max_connections = 50

These are example values, not universal recommendations. Adjust them according to available RAM, application requirements, and other services running on the VPS.

Note: work_mem can be allocated multiple times per query and across concurrent connections. Increasing it excessively may cause memory exhaustion.

After modifying postgresql.conf, restart PostgreSQL:

sudo systemctl restart postgresql

Verify the service is running:

sudo systemctl status postgresql --no-pager
sudo pg_lsclusters

9. Back Up a PostgreSQL Database

PostgreSQL includes pg_dump for creating logical database backups.

Create a backup directory:

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql

Create a compressed backup using PostgreSQL's custom format:

sudo -u postgres pg_dump \
-Fc \
-f /var/backups/postgresql/app_database.dump \
app_database

The custom format supports flexible restoration using pg_restore.

10. Restore a PostgreSQL Database

To restore a backup into a new database, first create the destination database:

sudo -u postgres createdb \
-O app_user \
app_database_restored

Restore the backup:

sudo -u postgres pg_restore \
--no-owner \
--role=app_user \
-d app_database_restored \
/var/backups/postgresql/app_database.dump

Important: Test backups regularly. Restoring data into an existing production database can modify or overwrite application data.

11. Automate PostgreSQL Backups

Create a daily backup script:

sudo nano /usr/local/sbin/postgresql-backup.sh

Add:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/var/backups/postgresql"
DATABASE="app_database"
DATE=$(date +%Y-%m-%d_%H-%M-%S)

umask 077
mkdir -p "$BACKUP_DIR"

pg_dump \
  -Fc \
  -f "$BACKUP_DIR/${DATABASE}_${DATE}.dump" \
  "$DATABASE"

find "$BACKUP_DIR" \
  -type f \
  -name "${DATABASE}_*.dump" \
  -mtime +7 \
  -delete

Set the correct permissions:

sudo chown postgres:postgres /usr/local/sbin/postgresql-backup.sh
sudo chmod 700 /usr/local/sbin/postgresql-backup.sh

Test the backup script:

sudo -u postgres /usr/local/sbin/postgresql-backup.sh

Schedule the backup using the postgres user's crontab:

sudo crontab -u postgres -e

Add:

0 3 * * * /usr/local/sbin/postgresql-backup.sh

This creates a database backup every day at 03:00 server time and removes matching backups older than seven days.

Important: Store additional encrypted backups outside the VPS. Local backups alone do not protect against server failure or storage loss. Monitor scheduled backup jobs and periodically test restoration.

12. Useful PostgreSQL Management Commands

# Check PostgreSQL version
psql --version

# Check database clusters
sudo pg_lsclusters

# Check service status
sudo systemctl status postgresql --no-pager

# Restart PostgreSQL
sudo systemctl restart postgresql

# View PostgreSQL logs
sudo journalctl -u postgresql -n 50 --no-pager

# List databases
sudo -u postgres psql -c "\l"

# List database roles
sudo -u postgres psql -c "\du"

# Check listening ports
sudo ss -tlnp | grep 5432

13. Troubleshooting Common PostgreSQL Problems

Authentication failed: Verify the database username, password, connection address, and authentication rules in pg_hba.conf.

Connection refused: Confirm PostgreSQL is running and listening on the expected network interface.

sudo pg_lsclusters
sudo ss -tlnp | grep 5432

Permission denied: Review database ownership, role privileges, and schema permissions.

PostgreSQL fails to start: Check the active cluster status and PostgreSQL logs.

sudo pg_lsclusters
sudo journalctl -u postgresql -n 100 --no-pager

Slow queries: Review indexes, query execution plans, memory settings, and storage performance.

Related Ubuntu VPS Tutorials

• How to Set Up and Secure a New Ubuntu VPS
• How to Install and Configure Nginx on an Ubuntu VPS
• How to Install PHP and PHP-FPM on an Ubuntu VPS
• How to Install and Secure MariaDB on an Ubuntu VPS
• How to Install Docker and Docker Compose on Ubuntu

Deploy PostgreSQL with Owned Networks

Host PostgreSQL databases, business applications, development environments, and custom backend services on an Owned Networks KVM VPS with full root access and flexible server resources.

A Linux VPS gives you control over database configuration, authentication, storage, backups, and performance optimization.

Explore Owned Networks VPS Hosting and deploy your PostgreSQL server today.