Install & Configure PostgreSQL on VPS for Production

June 2026 · 8 min read

PostgreSQL, commonly known as "Postgres," is one of the world's most popular open-source relational database management systems. With its ability to handle large datasets, support for ACID transactions, and advanced features like JSON, Full-Text Search, and Window Functions, PostgreSQL is ideal for production-level workloads. This guide will walk you through installing and configuring PostgreSQL on a VPS running Ubuntu, covering everything from installation and security setup to performance tuning and automated backups.

Prerequisites

• VPS running Ubuntu 22.04 LTS or 24.04 LTS
• sudo privileges or root access
• At least 1 GB of RAM (2 GB or more recommended for production)
• At least 20 GB of disk space

Step 1 — Install PostgreSQL

While Ubuntu includes PostgreSQL in its official repository, it's recommended to add PostgreSQL's official repository to get the latest version.

# Update package list
sudo apt update && sudo apt upgrade -y

# Install dependencies
sudo apt install -y curl ca-certificates

# Add PostgreSQL APT repository
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

# Install PostgreSQL 16
sudo apt update
sudo apt install -y postgresql-16 postgresql-contrib-16

After installation completes, PostgreSQL will start automatically. Verify its status with:

sudo systemctl status postgresql
sudo systemctl enable postgresql   # Enable autostart on reboot

Step 2 — Configure Authentication and Security

PostgreSQL uses the pg_hba.conf file to control user authentication. Ubuntu's default configuration uses peer authentication for Unix sockets, which is secure, but you'll need to adjust it for application connections.

# Connect to psql with the postgres user
sudo -u postgres psql

# Set password for postgres superuser
ALTER USER postgres WITH PASSWORD 'YourStrongPassword123!';
\q

Edit the pg_hba.conf file:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Example recommended authentication configuration for production:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Unix socket connections (for local admin)
local   all             postgres                                peer
local   all             all                                     md5

# IPv4 local connections
host    all             all             127.0.0.1/32            scram-sha-256

# Allow specific application server IP only
host    myapp_db        myapp_user      10.0.0.5/32             scram-sha-256

Restart PostgreSQL to apply the configuration changes:

sudo systemctl restart postgresql

Step 3 — Create Database and User

Create separate database users and databases for each application instead of using the postgres superuser directly.

sudo -u postgres psql

-- Create user with password
CREATE USER myapp_user WITH PASSWORD 'SecureAppPassword456!';

-- Create database
CREATE DATABASE myapp_db
  WITH OWNER = myapp_user
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8'
  TEMPLATE = template0;

-- Grant necessary permissions
GRANT CONNECT ON DATABASE myapp_db TO myapp_user;
GRANT USAGE ON SCHEMA public TO myapp_user;
GRANT CREATE ON SCHEMA public TO myapp_user;

\q

Step 4 — Tune Performance (postgresql.conf)

PostgreSQL's default settings are tuned for generic hardware. You'll need to adjust them based on your VPS's RAM and workload.

sudo nano /etc/postgresql/16/main/postgresql.conf

Example recommended values for a 4 GB RAM VPS (adjust proportionally):

# Memory Settings
shared_buffers = 1GB               # About 25% of RAM
effective_cache_size = 3GB         # About 75% of RAM
work_mem = 16MB                    # Per query operation
maintenance_work_mem = 256MB       # For VACUUM, CREATE INDEX

# Checkpoint Settings
checkpoint_completion_target = 0.9
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB

# Connection Settings
max_connections = 100              # Adjust based on actual connections

# Query Planner
random_page_cost = 1.1             # Use 1.1 for SSD
effective_io_concurrency = 200     # For SSD

# Logging (Production)
log_min_duration_statement = 1000  # Log queries slower than 1 second
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Reload configuration without restarting (for most settings):

sudo systemctl reload postgresql
# Or
sudo -u postgres psql -c "SELECT pg_reload_conf();"

Step 5 — Configure Firewall

PostgreSQL listens on port 5432 by default. If you don't need external access, block it with UFW:

# Deny external access (if app is on the same server)
sudo ufw deny 5432

# Or, if app server is on a different VPS, specify the IP
sudo ufw allow from 10.0.0.5 to any port 5432 proto tcp

sudo ufw reload
sudo ufw status

To make PostgreSQL listen only on localhost, edit postgresql.conf:

# In postgresql.conf
listen_addresses = 'localhost'     # Bind only to 127.0.0.1

# To allow connections from other servers
listen_addresses = 'localhost,10.0.0.1'   # Specify IPs to listen on

Step 6 — Configure Automated Backups

Backups are critical for production databases. Create a backup script and schedule it with cron:

sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo chmod 750 /var/backups/postgresql

Create the backup script:

sudo nano /usr/local/bin/pg_backup.sh

#!/bin/bash
# PostgreSQL Backup Script

BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
PGUSER="postgres"

# Backup all databases
for DB in $(sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';"); do
  DB=$(echo $DB | tr -d ' ')
  FILENAME="${BACKUP_DIR}/${DB}_${DATE}.sql.gz"
  sudo -u postgres pg_dump "$DB" | gzip > "$FILENAME"
  echo "[$(date)] Backed up: $DB → $FILENAME"
done

# Remove backups older than RETENTION_DAYS
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$(date)] Cleanup: removed backups older than ${RETENTION_DAYS} days"

sudo chmod +x /usr/local/bin/pg_backup.sh

Schedule the backup to run nightly at 2:00 AM:

sudo crontab -u postgres -e

# Add this line
0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Step 7 — Essential Maintenance Tasks

VACUUM and ANALYZE

PostgreSQL has autovacuum running automatically, but for large tables with frequent updates/deletes, you may need to run manual maintenance:

-- Check dead tuples
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric/nullif(n_live_tup,0)*100,2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- VACUUM with verbose output
VACUUM VERBOSE ANALYZE mytable;

-- Use VACUUM FULL for severe bloat (locks table)
VACUUM FULL ANALYZE mytable;

Check Slow Queries

-- Enable pg_stat_statements in postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'

-- After restart, create the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- View top 10 slow queries
SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Monitor Connections and Locks

-- View active connections
SELECT pid, usename, application_name, client_addr,
       state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Terminate long-running queries
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '5 minutes'
  AND pid != pg_backend_pid();

Step 8 — Test Connections

Verify everything is working correctly:

# Connect as app user
psql -U myapp_user -d myapp_db -h 127.0.0.1 -W

# Test creating a table and inserting data
CREATE TABLE test_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO test_table (name) VALUES ('Hello PostgreSQL');
SELECT * FROM test_table;

DROP TABLE test_table;

Summary and Checklist

You've successfully installed and configured PostgreSQL for production on Ubuntu VPS. Here's what you accomplished:

• Installed PostgreSQL 16 from the official repository
• Configured authentication with scram-sha-256 and peer methods
• Created separate users and databases per application using Least Privilege principle
• Tuned performance settings including shared_buffers, work_mem, and other parameters
• Secured port 5432 with UFW to restrict access
• Automated nightly backups with retention policy
• Monitored slow queries and connections using pg_stat_statements

When properly configured, PostgreSQL provides excellent stability and performance for production workloads. It's recommended to monitor database activity regularly through logs and pg_stat_activity to catch issues before they impact users.

Verify PostgreSQL is Working Correctly

# Check service status
sudo systemctl status postgresql

# Verify version and connection readiness
sudo -u postgres psql -c "SELECT version();"

# Test connection with app user
psql -U myapp_user -d myapp_db -h 127.0.0.1 -c "\conninfo"

# Check autovacuum is running
sudo -u postgres psql -c "SHOW autovacuum;"

# View all database sizes
sudo -u postgres psql -c "\l+"

Common Issues & Troubleshooting

Error: "FATAL: password authentication failed for user"

The password is incorrect, or the authentication method in pg_hba.conf doesn't match your connection attempt.

# Check pg_hba.conf for matching method
sudo cat /etc/postgresql/16/main/pg_hba.conf | grep -v "^#" | grep -v "^$"

# Reset the user's password
sudo -u postgres psql -c "ALTER USER myapp_user WITH PASSWORD 'NewPassword!';"

# If you changed pg_hba.conf, reload it
sudo systemctl reload postgresql

Error: "could not connect to server: Connection refused"

PostgreSQL isn't listening on that IP/port, or the service isn't running.

# Check if PostgreSQL is running
sudo systemctl status postgresql

# See which ports PostgreSQL is listening on
ss -tlnp | grep 5432

# Check listen_addresses in postgresql.conf
sudo grep listen_addresses /etc/postgresql/16/main/postgresql.conf

Error: "pg_hba.conf: no pg_hba.conf entry for host"

No rule in pg_hba.conf allows this host/user/database combination.

# Edit pg_hba.conf and add the necessary rule
sudo nano /etc/postgresql/16/main/pg_hba.conf

# Example: Allow myapp_user from localhost
# host    myapp_db    myapp_user    127.0.0.1/32    scram-sha-256

# Reload after making changes
sudo systemctl reload postgresql

Performance Issues — Queries Running Slowly

Missing indexes or outdated statistics. Use EXPLAIN ANALYZE first.

-- View query plan
EXPLAIN ANALYZE SELECT * FROM mytable WHERE user_id = 123;

-- Create index if missing
CREATE INDEX CONCURRENTLY idx_mytable_user_id ON mytable(user_id);

-- Update statistics
ANALYZE mytable;

Disk Full — autovacuum Failing / High Bloat

If disk is full, PostgreSQL will stop accepting writes. You need to add space or VACUUM FULL bloated tables.

-- View disk usage by table
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- VACUUM FULL large table (temporarily locks table)
VACUUM FULL ANALYZE bigtable;

Frequently Asked Questions

How is PostgreSQL different from MySQL/MariaDB?

PostgreSQL has more advanced features like JSONB, Full-Text Search, Window Functions, Common Table Expressions (CTEs), Foreign Data Wrappers, and stricter ACID compliance. It's better suited for complex queries. PostgreSQL is popular with Python (Django/SQLAlchemy), while MySQL is more common with PHP (WordPress/Laravel).

How often should I VACUUM the database?

PostgreSQL runs autovacuum automatically, so manual VACUUM isn't usually necessary. However, for tables with very high update/delete rates (like queue or log tables) or after bulk deletes, you may need to manually VACUUM since autovacuum might lag. Check pg_stat_user_tables for dead tuples.

What's the difference between Schema and Database in PostgreSQL?

A Database is the top-level container. Within a database, you can have multiple Schemas. A Schema is a namespace for organizing tables, functions, and views within a single database. Best practice: separate databases per application, separate schemas per module or microservice within the app. This provides better permission control.