How to set up PostgreSQL on Ubuntu for your applications

PostgreSQL is a powerful, dependable database that many applications and larger bots prefer. It is strict about correctness, handles many users at once and has features such as rich data types and good support for complex queries. Setting it up on a VPS is straightforward, and the steps below give you a safe starting point.

Install

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl status postgresql

The installation creates a cluster and starts it. On Ubuntu it listens only on the local machine by default, which is what you want in most cases.

How logins work

PostgreSQL uses a database user called postgres as the administrator, tied to the operating system account of the same name. To get a prompt as that user:

sudo -u postgres psql

You are now inside psql, the command line client. Type \q to leave, and \l lists databases, and \du lists users, which you will use often.

Create a user and database for each application

Give every application its own user and database, so that a problem in one cannot reach another:

CREATE USER appuser WITH PASSWORD 'a-long-random-password';
CREATE DATABASE appdb OWNER appuser;

The user owns that database and has full control over it, and nothing else. Check with \l and \du.

If you want to limit the user further, grant only what is needed, for example read-only access for a reporting tool, by creating a second user with GRANT SELECT permissions.

Connect from your application

The usual way is a connection string:

postgresql://appuser:a-long-random-password@127.0.0.1:5432/appdb

The default port is 5432. Put the string in an environment variable or private config file, never in shared code. See Environment variables and keeping your bot token safe. Special characters in the password, such as @ or /, must be percent-encoded in a connection string, or the connection fails in confusing ways, so choose a password with only letters and numbers if that is a problem.

Test from the server:

psql "postgresql://appuser:a-long-random-password@127.0.0.1:5432/appdb"

Keep it private

Two files control who can connect. On Ubuntu they live in /etc/postgresql/VERSION/main/, where VERSION is the number installed, such as 16:

• postgresql.conf has listen_addresses. The default, localhost, means only the same machine can connect. Leave it that way when the application is on the same server.
• pg_hba.conf decides which users may connect from which addresses, and how they must authenticate.

If you need access from your own computer, use an SSH tunnel rather than exposing the port:

ssh -L 5433:127.0.0.1:5432 alex@YOUR_SERVER_IP

Then point your database tool at 127.0.0.1 port 5433. For how SSH works, see Connect to your VPS over SSH.

If you must allow remote connections, set listen_addresses to include the interface, add a line in pg_hba.conf restricted to the exact addresses and with scram-sha-256 authentication, and open only that port to those addresses in your firewall. See Set up a firewall on your VPS with UFW. After editing either file, run sudo systemctl reload postgresql. A database port open to the world will be attacked within hours.

Back up

Use pg_dump, which makes a consistent copy while the database is running:

sudo -u postgres pg_dump appdb > appdb-backup.sql

Or in a compressed custom format that restores selectively:

sudo -u postgres pg_dump -Fc appdb > appdb-backup.dump

Restore a custom format dump with pg_restore. Schedule backups with cron, as in How to schedule tasks with cron, keep several days of them, and store copies off the server. Test a restore into a scratch database now and then. See The 3-2-1 backup rule.

Maintenance

PostgreSQL cleans up after itself with a background process called autovacuum, so you rarely need to do anything. Things worth checking occasionally:

• Disk space. Databases grow, and a full disk can corrupt data. See Fixing "No space left on device".
• Updates. Minor versions fix bugs and security issues and are safe to install through the normal updates. Major upgrades need planning, a backup and the upgrade tools, so never do one casually.
• Slow queries. Add indexes for columns you filter by. The EXPLAIN command shows how a query runs.

Choosing between PostgreSQL and MariaDB

Both are excellent. PostgreSQL has a stricter, richer feature set. MariaDB, a MySQL-compatible database, is the default in many web hosting setups, such as WordPress. If your software requires one of them, use that one. See How to install and secure MariaDB on Ubuntu for the other.

Common problems

Problem	Likely cause
"password authentication failed"	Wrong password, or the user does not exist
"peer authentication failed"	You used psql as the wrong operating system user
"connection refused"	The service is stopped, or you connect to the wrong port
"no pg_hba.conf entry"	Remote connection not allowed for that address
"too many connections"	The application does not release connections

---

Still stuck? Open a ticket on Discord - run /ticket in any channel, pick a department, and include your Server ID or Invoice ID so we can jump straight in.