Install and set up PostgreSQL on a Linux VPS

PostgreSQL is a powerful open-source relational database favoured by Django, Rails and many modern apps for its strict SQL, JSON support and reliability. This guide shows how to install PostgreSQL on a Linux VPS, create a database for your app and back it up.

Before you start

• Root or sudo access to an Ubuntu/Debian or AlmaLinux/Rocky VPS.
• At least 1 GB RAM for a small production database.

Install PostgreSQL

Ubuntu/Debian (the distro version is fine for most apps):

apt update && apt install postgresql postgresql-contrib

The service starts automatically. On AlmaLinux/Rocky, install, initialise the data directory and start it:

dnf install postgresql-server postgresql-contrib

postgresql-setup --initdb && systemctl enable --now postgresql

For a newer major version than your distro ships, use the official PGDG repository from postgresql.org.

Create a role and database

PostgreSQL creates a postgres system user that is the database superuser. Open the SQL shell as that user:

sudo -u postgres psql

Create a login role and a database it owns:

CREATE ROLE appuser WITH LOGIN PASSWORD 'strong-password';
CREATE DATABASE appdb OWNER appuser;
\q

Test the login over TCP:

psql -h 127.0.0.1 -U appuser appdb

Your app's connection string then looks like postgresql://appuser:strong-password@127.0.0.1:5432/appdb.

Useful psql commands

Allow remote connections (only if needed)

By default PostgreSQL listens only on localhost, which is safest. If an app server elsewhere must connect:

1. In postgresql.conf (find it with sudo -u postgres psql -c 'SHOW config_file') set listen_addresses = '*' or the private IP.
2. In pg_hba.conf allow just that client with password auth:
   Copy

   host  appdb  appuser  10.0.0.5/32  scram-sha-256

3. Restart: systemctl restart postgresql
4. Open port 5432 in the firewall only for that IP.

Basic tuning

In postgresql.conf, two settings matter most on a small VPS:

shared_buffers = 512MB
effective_cache_size = 1536MB

A rule of thumb is about 25% of RAM for shared_buffers and 50–75% for effective_cache_size (the values above suit 2 GB). Restart to apply.

Back up and restore with pg_dump

sudo -u postgres pg_dump -Fc appdb > /backup/appdb-$(date +%F).dump

Restore into an empty database:

sudo -u postgres pg_restore -d appdb --clean /backup/appdb-2025-01-01.dump

The custom format (-Fc) is compressed and lets you restore single tables. Use pg_dumpall --globals-only to save roles too. Schedule dumps with cron and copy them off the server.

Troubleshooting

• Peer authentication failed for user: local socket logins use the Linux username. Connect with -h 127.0.0.1 to use password auth, or change the local line in pg_hba.conf.
• Connection refused remotely: check listen_addresses, pg_hba.conf, the firewall and ss -ltnp | grep 5432.
• permission denied for schema public (PostgreSQL 15+): make the app role the database owner, or run GRANT ALL ON SCHEMA public TO appuser; inside the database.

PostgreSQL FAQ

PostgreSQL or MySQL?

Both are excellent. Use what your application recommends; PostgreSQL shines with complex queries and JSON, MySQL/MariaDB is the default for WordPress and most PHP apps.

Can I use PostgreSQL on cPanel hosting?

Only if the server administrator has enabled it in WHM. Most cPanel hosting, including typical WordPress setups, uses MySQL/MariaDB.

How do I upgrade to a new major version?

Install the new version alongside, then use pg_upgrade or dump and restore. On Debian/Ubuntu, pg_upgradecluster automates it.

Next steps

• Back up MySQL and MariaDB
• Schedule backups with cron
• Deploy a Python app with Gunicorn