How to Install PostgreSQL on VPS (2025) — Complete Setup

Why PostgreSQL Is the Best Database for Your VPS

PostgreSQL is the most advanced open-source relational database system available. For VPS recommendations, see Best VPS for Database. It supports ACID compliance, complex queries, JSON data types, full-text search, extensions like PostGIS for geospatial data, and has a proven track record in production environments ranging from small startups to Fortune 500 companies. For fast storage, see Best NVMe VPS Europe. PostgreSQL's query planner, MVCC (Multi-Version Concurrency Control), and write-ahead logging make it both reliable and performant under concurrent workloads.

Running PostgreSQL on a VPS gives you full control over your database without the cost or limitations of managed database services. For application deployment alongside PostgreSQL, see Deploy FastAPI on VPS and Docker on Ubuntu VPS. A VPS with NVMe storage can deliver database performance that rivals managed services at a fraction of the cost. For web hosting, see Best VPS for Web Hosting. This guide covers a complete production-ready setup including installation, user management, remote access, connection pooling, automated backups, performance tuning, monitoring, and security hardening.

Prerequisites

Step 1: Install PostgreSQL

The PostgreSQL packages in Ubuntu's default repositories are often outdated. For the latest features and security patches, install from the official PostgreSQL APT repository.

Add the PostgreSQL APT Repository

ssh root@your-vps-ip
apt update && apt upgrade -y
apt install -y wget gnupg2 lsb-release

# Add the PostgreSQL signing key
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 | apt-key add -
apt update

Install PostgreSQL 16

apt install -y postgresql-16 postgresql-server-dev-16

This installs PostgreSQL 16 (the latest major version as of 2025), the server, client tools, and development headers. Verify the installation.

psql --version
systemctl status postgresql

The output should show PostgreSQL 16.x and the service status as "active." PostgreSQL starts automatically after installation and listens on localhost (127.0.0.1) port 5432 by default.

Step 2: Initial Database Configuration

PostgreSQL creates a system user called postgres during installation. This user has superuser access to the database cluster. Set a password for the postgres user.

sudo -u postgres psql

-- Set the postgres user password
ALTER USER postgres WITH PASSWORD 'your_strong_password_here';

-- Exit psql
\q

Review the default configuration files. On Ubuntu, PostgreSQL configuration is split across multiple files in the data directory.

ls /etc/postgresql/16/main/
# postgresql.conf  pg_hba.conf  pg_ident.conf  conf.d/

• postgresql.conf: Main configuration file (memory settings, connections, logging, etc.)
• pg_hba.conf: Client authentication configuration (who can connect and how)
• pg_ident.conf: User name mapping for authentication

Step 3: Create Databases and Users

Create separate databases and users for each application. Never use the postgres superuser in application connection strings.

sudo -u postgres psql

-- Create a new user (role) for your application
CREATE USER appuser WITH PASSWORD 'app_user_password';

-- Create a database owned by the application user
CREATE DATABASE appdb OWNER appuser;

-- Grant the user full privileges on the database
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

-- Connect to the new database and grant schema privileges
\c appdb
GRANT ALL ON SCHEMA public TO appuser;

-- Verify
\l          -- List databases
\du         -- List users/roles
\q

For more granular permissions (recommended in production), create a read-only user.

-- Read-only user
CREATE USER readonly_user WITH PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE appdb TO readonly_user;
\c appdb
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Step 4: Configure pg_hba.conf Authentication

The pg_hba.conf file controls which hosts can connect to which databases, which users can connect, and which authentication method to use. Properly configuring this file is critical for security.

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

Replace the default configuration with the following. Place the rules in order from most specific to least specific — PostgreSQL evaluates them top-to-bottom and uses the first matching rule.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections (Unix socket)
local   all             all                                     peer

# IPv4 local connections (localhost only)
host    all             all             127.0.0.1/32            scram-sha-256

# IPv6 local connections
host    all             all             ::1/128                 scram-sha-256

# Application connections from specific IP (your web server or app server)
host    appdb           appuser         YOUR_APP_SERVER_IP/32   scram-sha-256

# Read-only connections from specific IP
host    appdb           readonly_user   YOUR_APP_SERVER_IP/32   scram-sha-256

# Admin connections from your IP (for pgAdmin, DBeaver, etc.)
host    all             postgres        YOUR_ADMIN_IP/32        scram-sha-256
EOF

Replace YOUR_APP_SERVER_IP and YOUR_ADMIN_IP with the actual IP addresses that need database access. Using specific IP addresses instead of ranges (like 0.0.0.0/0) limits the attack surface.

Reload PostgreSQL to apply the changes.

systemctl reload postgresql

Step 5: Enable Remote Connections

By default, PostgreSQL only accepts connections from localhost. To accept connections from remote hosts, modify the listen_addresses setting.

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

Find and modify the listen_addresses line.

# Listen on all interfaces (required for remote connections)
listen_addresses = '*'

# Or listen on a specific IP for tighter control
# listen_addresses = 'your-vps-ip,127.0.0.1'

Also set the port (default 5432 is fine, but you can change it to avoid scanners).

port = 5432

Reload PostgreSQL.

systemctl reload postgresql

Update the firewall to allow PostgreSQL connections from authorized IPs only.

# Allow PostgreSQL from a specific IP
ufw allow from YOUR_APP_SERVER_IP to any port 5432 proto tcp
ufw allow from YOUR_ADMIN_IP to any port 5432 proto tcp
ufw reload

Test the remote connection from your application server or local machine.

psql -h your-vps-ip -U appuser -d appdb

Step 6: Configure Connection Pooling (PgBouncer)

PostgreSQL handles each client connection with a separate process (the postmaster forks a new backend process per connection). Each process consumes approximately 5-10MB of RAM. On a VPS with limited memory, hundreds of connections can exhaust available RAM before the database even starts processing queries.

PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. It maintains a pool of reusable connections to PostgreSQL (typically 20-100) while accepting thousands of client connections. This dramatically reduces memory usage and improves performance under high concurrency.

Install PgBouncer

apt install -y pgbouncer

Configure PgBouncer

nano /etc/pgbouncer/pgbouncer.ini

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
# Listen address and port
listen_addr = 127.0.0.1
listen_port = 6432

# Authentication file
auth_file = /etc/pgbouncer/userlist.txt

# Admin users
admin_users = postgres

# Connection pool settings
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 3

# Timeouts
server_idle_timeout = 600
server_lifetime = 3600
server_connect_timeout = 15
server_reset_query = DISCARD ALL

# Logging
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
verbose = 0
EOF

Key settings explained:

• pool_mode = transaction: Releases the server connection back to the pool after each transaction. This is the most efficient mode for web applications that have many short-lived transactions. Other modes are session (connection held for entire client session) and statement (connection released after each statement)
• max_client_conn = 1000: Maximum number of client connections PgBouncer accepts
• default_pool_size = 25: Maximum number of server connections to keep per database. 25 is a good starting point for a 2GB VPS
• min_pool_size = 5: Keep at least 5 connections warm even when idle, reducing cold-start latency
• server_reset_query = DISCARD ALL: Resets the connection state between clients, preventing one client from affecting another's session

Create the User List

echo '"appuser" "app_user_password"' > /etc/pgbouncer/userlist.txt
chown postgres:postgres /etc/pgbouncer/userlist.txt
chmod 640 /etc/pgbouncer/userlist.txt

Enable and Start PgBouncer

systemctl enable pgbouncer
systemctl start pgbouncer
systemctl status pgbouncer

Update your application's connection string to use PgBouncer on port 6432 instead of PostgreSQL on port 5432.

# Before (direct to PostgreSQL)
postgresql://appuser:password@your-vps-ip:5432/appdb

# After (through PgBouncer)
postgresql://appuser:password@your-vps-ip:6432/appdb

Step 7: Set Up Automated Backups with pg_dump

Automated backups are non-negotiable for any production database. This script uses pg_dump to create compressed backups, rotates old backups, and logs the results.

Create the Backup Script

mkdir -p /opt/postgres-backups
cat > /opt/postgres-backups/backup.sh << 'SCRIPT'
#!/bin/bash

# PostgreSQL Backup Script
# Run daily via cron

# Configuration
BACKUP_DIR="/opt/postgres-backups"
DB_NAME="appdb"
DB_USER="postgres"
KEEP_DAYS=14
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"
LOG_FILE="${BACKUP_DIR}/backup.log"

# Create backup
echo "[$(date)] Starting backup of ${DB_NAME}" >> "$LOG_FILE"
pg_dump -U "$DB_USER" -d "$DB_NAME" | gzip > "$BACKUP_FILE" 2>> "$LOG_FILE"

if [ $? -eq 0 ]; then
    SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
    echo "[$(date)] Backup completed: ${BACKUP_FILE} (${SIZE})" >> "$LOG_FILE"
else
    echo "[$(date)] ERROR: Backup failed for ${DB_NAME}" >> "$LOG_FILE"
    rm -f "$BACKUP_FILE"
    exit 1
fi

# Remove backups older than KEEP_DAYS
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +$KEEP_DAYS -delete >> "$LOG_FILE" 2>&1

echo "[$(date)] Cleanup complete. Backups older than ${KEEP_DAYS} days removed." >> "$LOG_FILE"
SCRIPT

chmod +x /opt/postgres-backups/backup.sh

Schedule the Backup with Cron

# Run daily at 3:00 AM
echo "0 3 * * * /opt/postgres-backups/backup.sh" | crontab -
crontab -l

Test the Backup

/opt/postgres-backups/backup.sh
cat /opt/postgres-backups/backup.log
ls -lh /opt/postgres-backups/

Restore from Backup

# Drop and recreate the database
sudo -u postgres psql -c "DROP DATABASE appdb;"
sudo -u postgres psql -c "CREATE DATABASE appdb OWNER appuser;"

# Restore from backup
gunzip -c /opt/postgres-backups/appdb_YYYYMMDD_HHMMSS.sql.gz | sudo -u postgres psql -d appdb

Off-Site Backup (Optional)

For disaster recovery, copy backups to remote storage using rsync or rclone.

# Daily off-site sync to a remote server
echo "30 3 * * * rsync -az --delete /opt/postgres-backups/ user@remote-server:/backups/postgres/" | crontab -

Step 8: Performance Tuning

PostgreSQL's default configuration is intentionally conservative — it is designed to run on any system, even one with very limited resources. On a VPS, tuning the key parameters to match your hardware can dramatically improve performance.

Edit the PostgreSQL configuration file.

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

These settings are tuned for a VPS with 2GB RAM and 2 vCPU. Adjust the values based on your actual resources.

# -------------------------------------------
# Memory Settings
# -------------------------------------------

# Shared buffers: 25% of total RAM (for 2GB VPS = 512MB)
shared_buffers = 512MB

# Effective cache size: 75% of total RAM
effective_cache_size = 1536MB

# Work memory: per-operation memory for sorting, hashing, etc.
# 16MB allows moderate sorts in memory
work_mem = 16MB

# Maintenance work memory: used for VACUUM, CREATE INDEX, etc.
maintenance_work_mem = 128MB

# -------------------------------------------
# WAL (Write-Ahead Log) Settings
# -------------------------------------------

# WAL buffer size: 1/32 of shared_buffers
wal_buffers = 16MB

# Checkpoint settings
checkpoint_completion_target = 0.9
max_wal_size = 1GB
min_wal_size = 256MB

# -------------------------------------------
# Connection Settings
# -------------------------------------------

# If using PgBouncer, reduce max_connections on PostgreSQL
max_connections = 100

# -------------------------------------------
# Query Planner Settings
# -------------------------------------------

# Collect statistics for better query planning
default_statistics_target = 100

# Random page cost (lower for NVMe SSD)
random_page_cost = 1.1

# Effective IO concurrency (for NVMe SSD)
effective_io_concurrency = 200

# -------------------------------------------
# Parallel Query Settings
# -------------------------------------------

# Number of parallel workers per query (use number of CPU cores - 1)
max_parallel_workers_per_gather = 2
max_parallel_workers = 2
max_parallel_maintenance_workers = 1

# -------------------------------------------
# Logging Settings
# -------------------------------------------

log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# Log slow queries (queries taking longer than 500ms)
log_min_duration_statement = 500

# Log lock waits longer than 1 second
log_lock_waits = on
deadlock_timeout = 1s

# -------------------------------------------
# Autovacuum Settings
# -------------------------------------------

# More aggressive autovacuum for busy databases
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 25
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
EOF

Reload PostgreSQL to apply the changes.

systemctl restart postgresql

Key tuning parameters explained:

• shared_buffers = 512MB: The most important tuning parameter. This is the memory PostgreSQL uses for caching data pages. Set it to 25% of your total RAM. Larger values improve cache hit rates for frequently accessed data
• effective_cache_size = 1536MB: This is a hint to the query planner about how much memory is available for caching (including the OS page cache). Set it to 75% of total RAM
• work_mem = 16MB: Memory allocated per sort or hash operation. Increase this if your queries involve large ORDER BY, DISTINCT, or JOIN operations. Be careful — if you have 100 connections each using 16MB, that's 1.6GB of work memory. With PgBouncer and transaction pooling, the actual concurrent operations are limited
• maintenance_work_mem = 128MB: Memory for maintenance operations like VACUUM and CREATE INDEX. Higher values speed up these operations. Since only one maintenance operation runs at a time per database, this has minimal impact on query performance
• random_page_cost = 1.1: The cost of a non-sequential disk page fetch. Default is 4.0, which assumes spinning disks. For NVMe SSDs (like those on Inferno VPS), reduce this to 1.0-1.1 to encourage the planner to use index scans more freely
• log_min_duration_statement = 500: Logs any query that takes longer than 500ms. This is invaluable for identifying slow queries that need optimization. Set to 0 to log all queries (not recommended in production due to log volume)

Step 9: Monitor with pg_stat_activity

PostgreSQL provides built-in monitoring views that give you real-time insight into database activity, connection states, and query performance.

View Active Queries

sudo -u postgres psql -d appdb

-- Currently running queries
SELECT pid, now() - query_start AS duration, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Connections grouped by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- Connections per user
SELECT usename, count(*), state
FROM pg_stat_activity
GROUP BY usename, state;

-- Long-running queries (more than 30 seconds)
SELECT pid, now() - query_start AS duration, query, usename
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '30 seconds'
ORDER BY duration DESC;

-- Blocked queries
SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query
FROM pg_catalog.pg_locks blocked
JOIN pg_catalog.pg_stat_activity blocking ON blocking.pid = blocked.blocking_pid
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked.pid
WHERE NOT blocked.granted;
EOF

Kill a Long-Running or Blocked Query

-- Cancel a specific query (graceful)
SELECT pg_cancel_backend(pid);

-- Terminate a specific connection (immediate)
SELECT pg_terminate_backend(pid);

Database Size and Table Statistics

-- Database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Table sizes in the current database
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Index usage (find unused indexes)
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
EOF

Step 10: Security Hardening

Apply these security measures to protect your PostgreSQL installation.

1. Restrict Network Access

As configured in Steps 4 and 5, PostgreSQL should only accept connections from specific IP addresses defined in pg_hba.conf and the firewall. Never use 0.0.0.0/0 in pg_hba.conf.

2. Encrypt Remote Connections

Force SSL for remote connections to encrypt data in transit.

# In postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

# Force SSL for remote connections in pg_hba.conf
hostssl  all  all  0.0.0.0/0  scram-sha-256

3. Disable Unused Extensions

-- List installed extensions
SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;

-- Remove unused extensions
DROP EXTENSION IF EXISTS plpython3u;
DROP EXTENSION IF EXISTS dblink;
EOF

4. Prevent Superuser Remote Login

The postgres superuser should only connect locally. Remove any pg_hba.conf entries that allow remote postgres connections. Use regular database users for all application access.

5. Regular Security Updates

# Check for PostgreSQL security updates
apt update
apt list --upgradable | grep postgresql

Subscribe to the PostgreSQL Security Advisory mailing list to receive notifications about CVEs and patches.

Frequently Asked Questions

How much RAM does PostgreSQL need?

PostgreSQL can run on 512MB RAM for very small databases. For production workloads with moderate traffic, 2GB is a practical minimum. Allocate 25% of total RAM to shared_buffers and 75% to effective_cache_size. With PgBouncer handling connection pooling, even a 1GB VPS can serve a surprising number of queries.

Should I use NVMe or SATA for PostgreSQL?

NVMe SSD storage provides dramatically better performance for database workloads. Random 4K IOPS on NVMe can exceed 300,000 compared to 55,000 for SATA SSDs. PostgreSQL's random page reads benefit enormously from NVMe's low latency and high IOPS. The difference is most noticeable during VACUUM, index creation, and queries with many random reads.

How do I change the PostgreSQL port?

Edit /etc/postgresql/16/main/postgresql.conf and change the port directive. Also update the firewall rules and pg_hba.conf to reflect the new port. Restart PostgreSQL with systemctl restart postgresql.

Can I run PostgreSQL and a web server on the same VPS?

Yes, and this is common for small to medium workloads. Use PgBouncer to limit PostgreSQL's memory footprint. Ensure the web server (Nginx, Apache) and PostgreSQL share the available resources appropriately. Monitor memory usage with htop or docker stats if using containers.

How do I upgrade PostgreSQL to a new major version?

Use pg_upgradecluster on Ubuntu, which handles the data migration. Always back up your database before upgrading. Alternatively, use pg_dumpall and restore to the new version. Major version upgrades (e.g., 15 to 16) require a data migration. Minor version upgrades (e.g., 16.1 to 16.2) are handled by apt upgrade.

What is the difference between PgBouncer and connection pooling in the application?

PgBouncer operates at the PostgreSQL protocol level, pooling connections server-side. Application-level pooling (like HikariCP for Java, SQLAlchemy pool for Python) only pools connections within the application process. PgBouncer reduces the total number of connections to PostgreSQL regardless of how many application instances are running, which is essential when running multiple application processes or containers.

How do I monitor PostgreSQL performance over time?

For long-term monitoring, set up Prometheus with the postgres_exporter agent, and visualize metrics in Grafana. For simpler monitoring, log slow queries with log_min_duration_statement and analyze the logs. The pg_stat_statements extension tracks query statistics over time.