Install PostgreSQL on Ubuntu VPS — A MySQL Alternative

If you're looking for a database that's stable, supports JSON, has advanced functions, and offers stricter transactions than MySQL, the answer most developers reach for is PostgreSQL — the open-source database that powers Discord, Instagram, and Apple iCloud.

This guide walks you through installing PostgreSQL on an Ubuntu 22.04 VPS from scratch — creating a database, creating a user, setting a password, and safely allowing external applications to connect.

What is PostgreSQL and why use it?

PostgreSQL (pronounced "post-gress-Q-L") is an Object-Relational Database Management System (ORDBMS) developed since 1986. Its strengths are strict ACID compliance, native JSONB for storing document-style data alongside relational rows, and a much more granular type system than MySQL.

PostgreSQL vs MySQL — Key Differences

Step 1 — Update the system and install PostgreSQL

Start by updating the package list and installing PostgreSQL along with contrib, which bundles useful extensions:

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

Check the version and status:

sudo systemctl status postgresql
psql --version

If you see Active: active (exited) and a version like psql (PostgreSQL) 14.x or newer, the installation succeeded. Enable autostart on reboot:

sudo systemctl enable postgresql

Step 2 — Access psql as the postgres user

The installer creates a Linux user named postgres automatically. Use it to enter the console:

sudo -i -u postgres
psql

When you see the postgres=# prompt, you're inside the PostgreSQL console. List existing databases:

\l

Exit psql with \q and return to your normal shell with exit.

Step 3 — Create a new database and user

In production, don't use the postgres user directly — create a dedicated user per application instead. Open psql again:

sudo -u postgres psql

Create user, database, and grant privileges:

-- Create user with password
CREATE USER myapp WITH PASSWORD 'StrongPassword123!';

-- Create database owned by the user above
CREATE DATABASE myapp_db OWNER myapp;

-- Grant full privileges on the database
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp;

\q

Test login with the new user:

psql -U myapp -d myapp_db -h 127.0.0.1 -W

Enter the password you set. If it logs in, your app can use these credentials.

Step 4 — Allow external connections

By default PostgreSQL only accepts connections from localhost. To let an application on another server connect, you need to edit two files.

4.1 Edit postgresql.conf

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

Find listen_addresses and change it to:

listen_addresses = '*'

4.2 Edit pg_hba.conf to allow specific IPs

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

Add a line at the end (replace 192.168.1.100/32 with your app's real IP):

# TYPE  DATABASE    USER    ADDRESS            METHOD
host    myapp_db    myapp   192.168.1.100/32   scram-sha-256

Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

4.3 Open the firewall

PostgreSQL listens on port 5432. With UFW, allow only the IP you need:

sudo ufw allow from 192.168.1.100 to any port 5432

Step 5 — Backup with pg_dump

PostgreSQL ships with a backup tool called pg_dump that exports a database as a SQL file:

sudo -u postgres pg_dump myapp_db > /backup/myapp_db_$(date +%F).sql

Restore from a backup file:

sudo -u postgres psql myapp_db < /backup/myapp_db_2026-05-13.sql

Step 6 — Essential psql commands

• \l — list all databases
• \c dbname — switch to another database
• \dt — list tables in the current database
• \d tablename — show a table's schema
• \du — list users / roles
• \q — quit psql

Installing PostgreSQL on an Ubuntu VPS — In Depth

If you've just spun up a fresh VPS from AsiaGB (Ubuntu 22.04, from THB 500/month), the first thing to do after you SSH in is to check which PostgreSQL version ships in the Ubuntu repositories. The default repository sometimes lags several major releases behind the latest version. If you want the newest release, add the official PostgreSQL APT repository first:

# Add the PostgreSQL official repository (for the latest version)
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Then install as usual
sudo apt update
sudo apt install -y postgresql postgresql-contrib

Once installed, the service is set to start automatically. Verify that PostgreSQL is actually listening on port 5432:

sudo ss -tlnp | grep 5432

If you see a line referencing 127.0.0.1:5432, PostgreSQL is ready to accept local connections. This step matters because if the port doesn't come up, the usual cause is a service that failed to start or another process holding the port. Check the logs under /var/log/postgresql/ to find the reason.

Create Database, User and Manage Privileges in Detail

Earlier we created a user and database quickly using SQL inside psql. PostgreSQL also ships command-line wrappers you can call straight from the shell without entering psql first — createuser and createdb — which are handy in automation scripts:

# Create a user interactively (it asks about privileges)
sudo -u postgres createuser --interactive --pwprompt

# Create a database with an explicit owner
sudo -u postgres createdb -O myapp myapp_db

Privileges in PostgreSQL are more nuanced than many expect. GRANT ALL PRIVILEGES ON DATABASE only grants the right to connect and create schemas — it does not automatically cover tables created later. Since PostgreSQL 15, permissions on the public schema have been tightened, so you must grant schema-level rights explicitly:

sudo -u postgres psql -d myapp_db
-- Make the user own the public schema
GRANT ALL ON SCHEMA public TO myapp;

-- Auto-grant privileges on future tables/sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON TABLES TO myapp;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL ON SEQUENCES TO myapp;
\q

List roles and their attributes with \du inside psql, and inspect per-table privileges with \dp tablename. This makes it much faster to debug the common "permission denied for table" error that appears when an app connects as a user that isn't the table owner.

Harden Security and Remote Access

Exposing PostgreSQL to external connections is the riskiest part of the setup. Configured carelessly, bots scanning the internet for port 5432 will start brute-forcing your password immediately. The golden rule is to open only what you need and layer your defenses (defense in depth).

In postgresql.conf, if the app and database live on different machines within a private network, specify only the internal IP instead of using '*', which listens on every interface:

# Listen only on the VPS private IP instead of '*' (safer)
listen_addresses = 'localhost,10.0.0.5'

In pg_hba.conf, line order matters — PostgreSQL reads top to bottom and uses the first rule that matches a connection. Put specific rules (a single IP) before broader ones, and always use scram-sha-256 as the authentication method:

# TYPE  DATABASE   USER    ADDRESS           METHOD
local   all        all                       peer
host    myapp_db   myapp   10.0.0.10/32      scram-sha-256
host    all        all     127.0.0.1/32      scram-sha-256

For stronger security, enable SSL on connections between the app and the database to encrypt traffic crossing the network. Configure it in postgresql.conf:

ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'

Then force external connections to use SSL by changing host to hostssl in pg_hba.conf, and append ?sslmode=require to the connection string on the app side.

Backup, Restore and Basic Tuning

Beyond pg_dump for backing up a single database, PostgreSQL provides pg_dumpall to back up an entire cluster (including roles and global settings), and pg_restore to restore from a compressed custom-format backup that restores faster than a plain SQL file. Backing up in custom format is recommended:

# Backup in custom format (compressed + selective restore)
sudo -u postgres pg_dump -Fc myapp_db > /backup/myapp_db_$(date +%F).dump

# Restore from a custom-format file
sudo -u postgres pg_restore -d myapp_db --clean /backup/myapp_db_2026-06-07.dump

# Back up the whole cluster (roles + permissions)
sudo -u postgres pg_dumpall > /backup/full_cluster_$(date +%F).sql

On tuning, PostgreSQL's defaults are set very conservatively so it runs on tiny hardware. On a VPS with a reasonable amount of RAM you should raise these key values in postgresql.conf to match your machine:

If your app opens many connections, put a connection pooler such as PgBouncer in front rather than raising max_connections sky-high, since every PostgreSQL connection consumes a fair amount of memory. Remember to run sudo systemctl restart postgresql after every change to postgresql.conf, and run VACUUM ANALYZE periodically so the planner has fresh statistics and reclaims deleted space.

Set a password for the postgres user

After install, the postgres user has no database-level password. Set one:

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

Post-install Checklist

1. Install postgresql + postgresql-contrib and enable autostart
2. Set a password for the postgres user immediately
3. Create one user + database per application — never use postgres in production
4. Edit postgresql.conf + pg_hba.conf only when remote access is required
5. Open the firewall only for the IPs that need it — never 0.0.0.0/0
6. Schedule daily pg_dump backups and ship them off the server

Frequently Asked Questions (FAQ)

Should I choose PostgreSQL or MySQL for a typical website?

If your site is WordPress or a turnkey CMS built primarily for MySQL, using MySQL/MariaDB is easier to install and more compatible. But if you're building your own app that needs strict transactions, JSONB document storage, or complex analytics, PostgreSQL is the better fit and scales further over time. Both run comfortably on an AsiaGB VPS starting at THB 500/month.

What VPS specs do I need to install PostgreSQL?

PostgreSQL runs on a minimal VPS with 1GB RAM for small or test projects, but for real workloads with traffic or tens of thousands of rows, plan for at least 2GB RAM so you can tune shared_buffers and work_mem properly. AsiaGB's Linux SSD VPS gives you full root access, so you can adjust these values freely.

I forgot the postgres user password — what now?

Because the Linux postgres user can enter psql via peer authentication without a password, you can always reset it with sudo -u postgres psql followed by ALTER USER postgres WITH PASSWORD 'newpass'; — no knowledge of the old password is required, just sudo access on the VPS.

How do I run pg_dump backups automatically every day?

Use a system cron job: run sudo crontab -u postgres -e and add a line such as 0 3 * * * pg_dump -Fc myapp_db > /backup/myapp_db_$(date +\%F).dump to back up daily at 3 AM. Ship the file to cloud storage and set a rule to auto-delete backups older than 14 days to save space.