How to Install and Configure PostgreSQL on a Linux VPS: Complete Guide

PostgreSQL is one of the most powerful and mature open-source relational database management systems (RDBMS) in the world. It stands out for its strict SQL standards compliance, full ACID transaction support, rich extensibility, and excellent performance at scale.

If you're running modern web applications, REST APIs, ERP systems, or analytics platforms on a NVMe VPS, PostgreSQL can be the ideal choice. This guide walks you through installing, securing, and optimizing PostgreSQL on a VPS running Ubuntu 22.04 or Debian 12.

---

Why PostgreSQL Instead of MySQL?

Before diving into the installation, here's why PostgreSQL is gaining ground fast:

• Advanced data types: Native JSON/JSONB, arrays, hstore, geometric types
• Strict SQL compliance: PostgreSQL follows SQL standards more rigorously
• Reliable transactions: Full ACID support, including transactional DDL
• Powerful extensions: PostGIS (geospatial), pg_trgm (full-text search), TimescaleDB and more
• Scalability: Ideal for large databases with advanced indexing (GiST, GIN, BRIN)

For serious projects running on an SSD VPS, PostgreSQL provides a solid and scalable foundation.

---

Prerequisites

• A VPS with Ubuntu 22.04 LTS or Debian 12
• Root access or a user with sudo privileges
• Minimum 1 GB RAM (2 GB+ recommended)
• An active SSH connection

If you don't have a server yet, you can order a Linux VPS from CLIQHOST right now.

---

Step 1: Update the System

Before any installation, update the package list and system:

sudo apt update && sudo apt upgrade -y

This prevents dependency conflicts and ensures you're installing the most recent available packages.

---

Step 2: Install PostgreSQL

Option 1 — From the Ubuntu/Debian Repository (Quick)

sudo apt install postgresql postgresql-contrib -y

The postgresql-contrib package includes useful extensions: uuid-ossp, pg_stat_statements, hstore, and others.

Option 2 — From the Official PostgreSQL Repository (Latest Version)

To install the latest stable version (e.g., PostgreSQL 16):

# Install dependencies
sudo apt install -y gnupg2 wget

# Add GPG key
wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Add repository
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | \
  sudo tee /etc/apt/sources.list.d/pgdg.list

# Update and install
sudo apt update && sudo apt install postgresql-16 postgresql-client-16 -y

Verify Installation

sudo systemctl status postgresql
psql --version

You should see something like: psql (PostgreSQL) 16.x

---

Step 3: First Login — The postgres User

PostgreSQL automatically creates a system user called postgres, associated with the database superuser. Connect like this:

sudo -i -u postgres
psql

Or directly:

sudo -u postgres psql

You'll see the prompt postgres=#. Exit with \q.

---

Step 4: Securing Your PostgreSQL Installation

This is the most critical step. An unprotected installation is a major security risk.

4.1 Set a Password for the Superuser

sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'VeryStrongPassword!2024';
\q

4.2 Create a Dedicated Application User (Recommended)

Never use the postgres superuser directly in applications. Create separate users with minimal privileges:

CREATE USER app_user WITH PASSWORD 'AppPassword!456';
CREATE DATABASE app_db OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;

4.3 Configure pg_hba.conf

The pg_hba.conf file controls authentication. Find it at:

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

For secure local connections, ensure the local and 127.0.0.1 entries use scram-sha-256:

# TYPE  DATABASE  USER  ADDRESS      METHOD
local   all       all                scram-sha-256
host    all       all   127.0.0.1/32 scram-sha-256
host    all       all   ::1/128      scram-sha-256

After changes, restart the service:

sudo systemctl restart postgresql

4.4 Disable Remote Access by Default

If you don't need remote access to PostgreSQL, keep listen_addresses set to localhost in postgresql.conf:

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

listen_addresses = 'localhost'

If you need remote access (e.g., from a separate application server), specify the exact IP and protect port 5432 with a properly configured firewall.

---

Step 5: Basic PostgreSQL Operations

Creating and Listing Databases

-- List databases
\l

-- Create a database
CREATE DATABASE new_project;

-- Connect to a database
\c new_project

-- Drop a database
DROP DATABASE new_project;

Creating Tables

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CRUD Operations

-- Insert
INSERT INTO customers (name, email) VALUES ('John Doe', '[email protected]');

-- Read
SELECT * FROM customers WHERE email LIKE '%@example.com';

-- Update
UPDATE customers SET name = 'John Smith' WHERE id = 1;

-- Delete
DELETE FROM customers WHERE id = 1;

Useful psql Commands

Command	Description
\l	List databases
\c <db>	Connect to database
\dt	List tables
\d <table>	Table structure
\du	List users
\timing	Show query execution time
\q	Quit psql

---

Step 6: Optimizing PostgreSQL Performance

The default values in postgresql.conf are intentionally conservative. On a VPS with dedicated NVMe storage, you can tune them for better performance.

Open the configuration file:

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

Key Parameters to Tune

# Memory
shared_buffers = 256MB          # ~25% of available RAM
effective_cache_size = 768MB    # ~75% of total RAM
work_mem = 16MB                 # per sort/hash operation
maintenance_work_mem = 128MB    # for VACUUM, CREATE INDEX

# Connections
max_connections = 100           # adjust to your application's needs

# Checkpoint
checkpoint_completion_target = 0.9
wal_buffers = 16MB

# Useful logging
log_min_duration_statement = 500  # log queries taking > 500ms
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

After changes:

sudo systemctl reload postgresql

PGTune Configuration Tool

Use the online PGTune tool to automatically generate optimal parameters based on your RAM, CPU count, and workload type.

---

Step 7: Backup and Restore

Regular backups are essential. PostgreSQL provides pg_dump and pg_dumpall for this purpose. If you need professional backup configuration, the CLIQHOST team offers server management services to handle everything for you.

Backup a Single Database

sudo -u postgres pg_dump app_db > /backup/app_db_$(date +%Y%m%d_%H%M%S).sql

Compressed Backup

sudo -u postgres pg_dump -Fc app_db > /backup/app_db_$(date +%Y%m%d).dump

Backup All Databases

sudo -u postgres pg_dumpall > /backup/all_databases_$(date +%Y%m%d).sql

Restore

# From SQL file
sudo -u postgres psql app_db < /backup/app_db_20240115.sql

# From custom format (.dump)
sudo -u postgres pg_restore -d app_db /backup/app_db_20240115.dump

Automate with Cron

crontab -e

Add:

0 2 * * * sudo -u postgres pg_dump -Fc app_db > /backup/app_db_$(date +\%Y\%m\%d).dump 2>/var/log/pg_backup.log

---

Step 8: Monitoring PostgreSQL

Active and Slow Queries

-- Active queries
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- Top slow queries (requires pg_stat_statements)
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Enabling pg_stat_statements

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

shared_preload_libraries = 'pg_stat_statements'

Restart and activate the extension:

sudo systemctl restart postgresql
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Database Sizes

SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

---

Step 9: Maintenance — VACUUM and REINDEX

PostgreSQL uses an MVCC storage mechanism that requires periodic cleanup via VACUUM:

-- Vacuum a specific table
VACUUM ANALYZE customers;

-- Full vacuum (locks the table!)
VACUUM FULL customers;

-- Reindex a table
REINDEX TABLE customers;

Autovacuum is enabled by default and handles this automatically. For large databases, you can fine-tune its parameters in postgresql.conf.

---

Security Best Practices

1. Don't expose port 5432 publicly — use SSH tunneling or a VPN for remote access
2. Rotate passwords regularly and use complex, unique credentials
3. Principle of least privilege: each application gets only the permissions it needs
4. Enable SSL for PostgreSQL connections — set ssl = on in postgresql.conf
5. Audit access: enable logging for all connections
6. Keep PostgreSQL updated to benefit from the latest security patches

For advanced server-level security and a fully managed environment, consider CLIQHOST's managed dedicated servers or our server administration services.

---

Conclusion

PostgreSQL is an excellent choice for any project that demands high data integrity, scalability, and flexibility. Once properly installed and configured on your Linux VPS, it becomes a reliable foundation for web applications, APIs, and business systems.

Need a high-performance server to run PostgreSQL? Explore CLIQHOST's NVMe VPS plans — fast NVMe storage, dedicated resources, and expert technical support included. Or browse our blog for more Linux server tutorials.

Have questions or need a hand? Contact our team — we're ready to help you build a solid infrastructure.