How to Install PostgreSQL on a VPS

Your application is only as reliable as the database behind it. PostgreSQL, nicknamed Postgres by its loyal community, has spent nearly three decades proving itself as one of the most robust and standards-compliant relational databases ever built. Banks trust it. Startups scale with it. And once you get it running on your own VPS, you’ll understand exactly why the world’s top engineering teams keep reaching for it.

This guide walks you through a complete, production-ready PostgreSQL installation on Ubuntu 22.04 or 24.04 LTS. You’ll install from the official PGDG repository, lock down the default superuser account, create a dedicated database and user, tune the core configuration for real hardware and set up remote access securely. By the end, you’ll have a fully operational Postgres server ready to back any application you throw at it.

What Is PostgreSQL?

PostgreSQL is a free, open-source object-relational database system with active development stretching back to 1986. It supports virtually every SQL standard feature you can name: ACID transactions, foreign keys, triggers, views, stored procedures and complex joins. Beyond standard SQL, it handles JSON and JSONB documents, full-text search, geospatial data through the PostGIS extension and custom data types you define yourself.

Under the hood, Postgres uses Multi-Version Concurrency Control (MVCC) to manage simultaneous reads and writes without table-level locking. That means high-traffic applications stay responsive even when dozens of processes are hammering the database at once. Companies like Apple, Reddit, Instagram and Skype have all relied on it at scale so it’s fair to say it punches well above its price tag (which happens to be zero).

Prerequisites

Before diving in, confirm your server meets the minimum requirements. The figures below assume a general-purpose deployment. For heavy analytical workloads or high connection counts, lean toward the recommended column or beyond.

You’ll also need the following ready before you start:

• A non-root user with sudo privileges (or direct root access)
• SSH access to your VPS
• UFW installed and active
• Basic familiarity with the Linux command line

How to Install PostgreSQL on a VPS

Step 1: Update Your System

Start fresh. Refreshing your package index and applying any pending upgrades prevents dependency conflicts later in the process.

sudo apt update && sudo apt upgrade -y

If your kernel receives an update, reboot before continuing.

sudo reboot

Step 2: Add the Official PostgreSQL Repository

Ubuntu’s default apt repositories include PostgreSQL but they often lag behind the current stable release by several months. The official PostgreSQL Global Development Group (PGDG) repository fixes that. It delivers the latest version and timely security patches the moment they ship.

Install the required dependencies for adding a signed repository:

sudo apt install -y curl ca-certificates

Create the directory for the signing key and download it from the PGDG servers:

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

Add the PGDG repository to your apt sources list:

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'

Refresh apt to pull in the new package listings:

sudo apt update

Step 3: Install PostgreSQL

With the PGDG repository active, a single command pulls in the latest stable PostgreSQL release along with the client tools and the contrib module collection.

sudo apt install -y postgresql postgresql-contrib

The postgresql-contrib package bundles a set of community extensions that don’t ship in the core install. Useful additions like pg_stat_statements for query-level performance analysis and uuid-ossp for UUID generation live here. They’re disabled until you activate them but it’s worth having them on hand.

Need a specific major version alongside the latest? You can install it by name:

sudo apt install -y postgresql-16

Step 4: Start and Enable the Service

PostgreSQL starts automatically after installation on most systems. Verify it’s running and configure it to launch on every reboot.

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

Look for the Active: active (running) line in the output. If it’s there, Postgres is up and listening on port 5432. Confirm that with:

ss -tlnp | grep 5432

By default, PostgreSQL binds only to 127.0.0.1. That’s intentional. No external connection is possible until you explicitly allow it, which means the database is already secure the moment it starts.

Step 5: Secure the postgres Superuser Account

Installation creates a Linux system user named postgres that maps directly to the PostgreSQL superuser of the same name. By default, this account uses peer authentication: anyone logged in to the system as postgres can access the database without entering a password. That’s convenient locally but you’ll want a strong password set for any application connections or future remote access.

Switch to the postgres system user and open the interactive psql shell:

sudo -i -u postgres
psql

Set a password for the superuser account:

/password postgres

You’ll be prompted to enter and confirm the new password. Choose something long and random; a password manager handles that without a second thought. Exit psql and return to your regular sudo user when done:

/q
exit

Step 6: Create a Database and Dedicated User

Connecting your application directly to the postgres superuser is one of those habits that seems harmless until it really isn’t. Superusers can drop tables, delete entire databases and modify system configuration. Instead, create a dedicated user and database per application. That’s the principle of least privilege in practice, and it takes about thirty seconds.

Switch to the postgres user and open psql:

sudo -i -u postgres
psql

Create a new role with login capability and a password:

CREATE USER appuser WITH PASSWORD 'your_strong_password';

Create the database and assign ownership to that user:

CREATE DATABASE appdb OWNER appuser;

Grant full privileges on the database to the new user:

GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

While you’re in there, also grant schema-level access so the user can work with tables created by other roles:

GRANT ALL ON SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO appuser;

Exit psql and return to your sudo user:

/q
exit

Test the connection as your new user to confirm everything is wired up correctly:

psql -U appuser -d appdb -h 127.0.0.1 -W

Enter the password you set and you should land in a psql prompt scoped to appdb. That’s a clean, isolated environment ready for your application.

Step 7: Tune postgresql.conf for Your Server

Fresh out of the box, PostgreSQL ships with conservative defaults designed to run on virtually any hardware. On a real VPS with dedicated RAM, those defaults leave performance on the table. The main configuration file lives at:

/etc/postgresql/17/main/postgresql.conf

Open it with your preferred editor:

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

The table below covers the most impactful parameters to adjust and what values to target based on available RAM. Treat these as a solid starting point rather than gospel; monitor query performance after applying them and refine from there.

Apply the changes with a reload:

sudo systemctl reload postgresql

For a faster path to tuned settings, PGTune generates tailored postgresql.conf values based on your server’s hardware profile and workload type. Worth bookmarking.

Step 8: Enable Remote Access (Optional)

If your application and PostgreSQL live on the same VPS, skip this step entirely. Localhost connections work without any changes and your database stays safely unexposed. However, if you need to connect from a remote server, a development machine or a GUI client like DBeaver or TablePlus, you’ll need to open Postgres to external connections carefully.

First, tell PostgreSQL which interfaces to listen on. Open postgresql.conf:

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

Find the listen_addresses line and update it. Setting '*' accepts connections on all interfaces. For tighter control, specify a comma-separated list of IP addresses instead:

listen_addresses = '*'

Next, open the client authentication file:

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

Add a line at the bottom that allows your specific remote IP to authenticate with a password. Replace 203.0.113.50 with the actual IP address of your connecting machine:

# TYPE  DATABASE    USER        ADDRESS             METHOD
host    appdb       appuser     203.0.113.50/32     scram-sha-256

Using a specific IP rather than 0.0.0.0/0 limits exposure dramatically. Avoid opening PostgreSQL to the entire internet unless you’ve layered additional protections like VPN-based access or SSL certificate authentication on top.

Restart PostgreSQL to apply both changes. A reload isn’t enough here because listen_addresses requires a full restart:

sudo systemctl restart postgresql

Step 9: Configure the UFW Firewall

If you skipped Step 8, there’s nothing to open in the firewall. PostgreSQL stays internal and port 5432 never needs to be reachable from outside the server. If you did configure remote access, add a targeted UFW rule that allows only the specific IP you whitelisted in pg_hba.conf.

sudo ufw allow from 203.0.113.50 to any port 5432

Apply the updated ruleset and confirm it’s active:

sudo ufw reload
sudo ufw status

Exposing port 5432 to all IPs (0.0.0.0/0) is an open invitation to automated credential-stuffing bots. PostgreSQL is a frequent target. Keep it scoped to trusted IPs or route remote access through an SSH tunnel whenever possible.

Keeping PostgreSQL Updated

Minor version updates (for example, moving from 17.2 to 17.3) are straightforward. They deliver bug fixes and security patches without touching your data or configuration files.

sudo apt update
sudo apt upgrade postgresql
sudo systemctl restart postgresql

Major version upgrades (say, PostgreSQL 16 to 17) require more deliberate handling. The data directory format changes between major versions so you’ll use the pg_upgradecluster tool from the postgresql-common package to migrate your data in place.

Always take a full backup before starting a major upgrade:

sudo -i -u postgres
pg_dumpall > /home/your_user/postgres_full_backup.sql
exit

Install the target major version and run the cluster migration:

sudo apt install -y postgresql-17
sudo pg_upgradecluster 16 main

Review the official PostgreSQL release notes before every major upgrade. Each version documents deprecations and breaking changes that might affect your setup.

Troubleshooting PostgreSQL

The PostgreSQL Service Fails to Start

Port conflicts are the most common culprit. If something else is already bound to 5432, PostgreSQL can’t claim it and crashes on startup. Find out what’s occupying the port:

sudo ss -tlnp | grep 5432

If another process is there, stop it or reconfigure Postgres to use a different port via the port parameter in postgresql.conf. For a full error breakdown, check the service journal:

sudo journalctl -xeu postgresql

Authentication Failed for User

This error usually points to one of three things: an incorrect password, a mismatched authentication method in pg_hba.conf or a connection from an IP that isn’t listed. Open pg_hba.conf and verify the relevant entry matches the user, database and connection method you’re actually using.

If you’re connecting locally and want password authentication instead of peer authentication, update the local method in pg_hba.conf:

# In pg_hba.conf
local   all    all    scram-sha-256

Reload PostgreSQL after any pg_hba.conf change:

sudo systemctl reload postgresql

Cannot Connect from a Remote Host

Three independent settings have to line up for a remote connection to succeed. Work through this checklist in order:

• listen_addresses in postgresql.conf must include the server’s IP or be set to '*'
• pg_hba.conf must contain a matching host entry for the user, database and remote IP
• UFW must permit the remote IP on port 5432

Also confirm that PostgreSQL fully restarted after your changes. A reload doesn’t re-read listen_addresses; only a full restart does.

Permission Denied on Table or Schema

Granting ALL PRIVILEGES ON DATABASE doesn’t automatically extend to individual tables created afterward. PostgreSQL’s permission model is granular: database, schema and object privileges are separate layers that each require explicit grants.

Connect as the superuser and run these grants to cover both existing and future objects:

GRANT ALL ON SCHEMA public TO appuser;
GRANT ALL ON ALL TABLES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO appuser;

The ALTER DEFAULT PRIVILEGES line is the one that saves you future headaches. It applies the grant to every table created in that schema going forward so you don’t have to repeat this command each time your schema evolves.

pg_wal Is Filling Up Disk Space

Write-Ahead Logging (WAL) files accumulate at /var/lib/postgresql/17/main/pg_wal/ and can balloon fast on write-heavy workloads. When that directory is eating your disk, a stale replication slot that nothing is consuming is often the real cause. Check for them:

SELECT slot_name, active, restart_lsn FROM pg_replication_slots;

Drop any inactive slot you no longer need:

SELECT pg_drop_replication_slot('slot_name');

On a standalone server without replication, you can also reduce WAL retention by setting wal_keep_size to a reasonable value like 64MB in postgresql.conf and reloading the service.

What to Build Next

With PostgreSQL running on your VPS, the foundation is solid. A few natural next steps will get a lot more out of it:

• Set up pgAdmin 4: A web-based GUI for managing databases, running queries and visualizing schemas without ever touching the terminal.
• Install PgBouncer: A lightweight connection pooler that sits between your application and Postgres. Essential for high-concurrency web apps that open large numbers of short-lived connections.
• Configure automated backups: Use pg_dump with a cron job or a dedicated tool like pgBackRest for continuous archiving. A backup you haven’t taken is one you can’t restore from.
• Enable SSL connections: PostgreSQL supports TLS natively. Pairing it with a Let’s Encrypt certificate encrypts all traffic between your application and the database at zero cost.
• Add the PostGIS extension: If geospatial data is anywhere in your roadmap, a single command turns Postgres into a capable GIS platform: CREATE EXTENSION postgis;

A Reliable Database for Whatever You’re Building

Getting PostgreSQL on a VPS used to mean wrestling with package conflicts and guessing at configuration values. With the PGDG repository, explicit security defaults and targeted firewall rules, you now have a database server that’s production-ready from day one. Whether you’re powering a side project or a customer-facing platform, Postgres will hold up its end of the deal. The rest is up to you.

The post How to Install PostgreSQL on a VPS appeared first on GreenGeeks.