PostgreSQL on Ubuntu: A Practical Setup Guide

PostgreSQL is a powerful open-source relational database that suits everything from a small personal project to a production service. Ubuntu makes it straightforward to install, update, and manage PostgreSQL using standard system tools, while the database itself provides strong support for SQL, transactions, extensions, and reliable data storage.

This guide covers a practical PostgreSQL installation on Ubuntu, including user accounts, databases, local connections, basic security, backups, and common maintenance tasks. The commands work well on current Ubuntu releases such as 22.04 LTS and 24.04 LTS, although package versions and configuration paths can change over time.

Preparing Ubuntu for PostgreSQL

Before installing a database server, update the local package index and apply available system updates. Open Terminal and run:

sudo apt update
sudo apt upgrade

Installing updates first reduces the chance of package conflicts and ensures that PostgreSQL receives the latest version available in Ubuntu’s configured repositories. On a fresh VPS in Sydney, Melbourne, or another Australian data centre, this step also confirms that the machine can reach the package mirrors correctly.

Install PostgreSQL and its commonly used additional utilities with:

sudo apt install postgresql postgresql-contrib

Ubuntu normally creates and starts the PostgreSQL service automatically. Check its status with:

sudo systemctl status postgresql

If the service is not running, start it and enable it at boot:

sudo systemctl start postgresql
sudo systemctl enable postgresql

The postgresql-contrib package includes useful extensions and tools. You may not need all of them immediately, but installing the package avoids having to search for common additions later.

Creating a Database and Login Role

PostgreSQL creates a Linux system account and database administrator role named postgres. Ubuntu commonly uses peer authentication for local administrative access, so you can open the PostgreSQL console without knowing a password by switching to that system account:

sudo -u postgres psql

You should see a prompt similar to postgres=#. Create a login role for an application or personal project. Replace the example password with a long, unique value:

CREATE ROLE appuser WITH LOGIN PASSWORD 'replace-with-a-strong-password';

Now create a database owned by that role:

CREATE DATABASE appdb OWNER appuser;

Exit the PostgreSQL console with:

\q

A separate role and database are preferable to using the powerful postgres administrator account for everyday work. If an application is compromised, limiting its permissions reduces the potential damage. You can also create a role without login access for ownership or administration, depending on how your project is organised.

Test the new account over a local TCP connection:

psql -h localhost -U appuser -d appdb

PostgreSQL asks for the password assigned to appuser. If the connection succeeds, run \conninfo inside the console to display the active database, user, and connection method.

Working with the Psql Console

The psql command-line client is the quickest way to inspect and manage a PostgreSQL installation. SQL statements end with a semicolon, while commands beginning with a backslash are psql shortcuts rather than SQL.

For example, create a small table and add a record:

CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO customers (name, email)
VALUES ('Alex Nguyen', 'alex@example.com');

SELECT * FROM customers;

The BIGSERIAL column automatically generates numeric IDs, while TIMESTAMPTZ stores timestamps with time-zone awareness. For newer applications, you may also see identity columns used instead:

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Useful inspection commands include:

\l
\c appdb
\dt
\d customers
\du

These list databases, connect to a database, show tables, describe a table, and list roles. Use \q to leave psql, and \? to display available client commands. SQL help is available with \h, followed by a command such as \h CREATE TABLE.

Ubuntu users who prefer graphical tools can install a database client such as pgAdmin, DBeaver, or a code editor extension. A graphical interface is convenient for browsing rows, but learning basic psql commands makes remote administration and troubleshooting much easier.

Connecting PostgreSQL to Applications

Most web frameworks and programming languages connect to PostgreSQL through a driver or library. A connection usually needs five values: host, port, database name, username, and password. The default PostgreSQL port is 5432.

A typical connection string looks like this:

postgresql://appuser:password@localhost:5432/appdb

Avoid placing a real password directly in source code. Store connection details in environment variables or a protected configuration file. For example:

export DATABASE_URL="postgresql://appuser:password@localhost:5432/appdb"

The application should use a dedicated role with only the permissions it needs. For a development project, ownership of the application database may be acceptable. For production, you might create separate migration, application, and reporting roles with different privileges.

Connection pooling is important when many requests arrive at once. PostgreSQL handles concurrent work well, but opening a new database connection for every web request can waste memory and exhaust the server’s connection limit. Libraries such as SQLAlchemy, Django’s database layer, Node.js pg, and Java connection pools provide ways to reuse connections.

If you are building a project on an Australian NBN connection, keep PostgreSQL bound to the local machine unless remote access is genuinely required. Home internet connections can change address, and exposing a database port directly creates unnecessary security risk. A VPN or SSH tunnel is usually safer for occasional remote administration.

Securing a PostgreSQL Installation

A basic Ubuntu installation is suitable for local development, but production systems need deliberate access controls. PostgreSQL’s client authentication rules are stored in pg_hba.conf, usually under a versioned directory such as:

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

The exact version may differ. Find the active configuration location from psql:

SHOW hba_file;

Local connections may use peer, which checks whether the Linux username matches the PostgreSQL role. Password-based connections commonly use scram-sha-256, a stronger modern authentication method. After changing authentication rules, reload the service:

sudo systemctl reload postgresql

Do not open port 5432 to the entire internet. If remote connections are needed, restrict access to a specific trusted IP range using the Ubuntu firewall and PostgreSQL’s host rules:

sudo ufw allow from YOUR_TRUSTED_IP to any port 5432 proto tcp

Replace the placeholder with an actual address or network, and avoid broad rules such as allowing the port from 0.0.0.0/0. SSH tunnels are often a better choice for an individual administrator.

Data protection also includes privacy. An Australian organisation handling personal information may need to consider the Privacy Act 1988, the Australian Privacy Principles, and the Notifiable Data Breaches scheme. Legal obligations depend on the organisation and the data involved, so database permissions, encryption, retention, and incident procedures should be reviewed alongside technical configuration.

Backups, Restores, and Routine Maintenance

A database is not protected simply because it runs on reliable hardware. Use pg_dump for a logical backup of one database:

pg_dump -U appuser -h localhost -Fc appdb > appdb.dump

The custom format created by -Fc works well with pg_restore. Create an empty destination database and restore the backup with:

createdb -U postgres restored_appdb
pg_restore -U postgres -d restored_appdb appdb.dump

For a plain SQL backup, use:

pg_dump -U appuser -d appdb > appdb.sql

Restore that format with:

psql -U postgres -d restored_appdb < appdb.sql

Keep backups on a different storage device or service from the main server. A backup stored on the same failed disk does not provide meaningful protection. Australian users may choose a provider with Sydney or Melbourne regions to reduce latency and keep data within a desired jurisdiction, but availability, encryption, access controls, and provider terms still need checking.

Schedule backups with cron, a systemd timer, or a managed backup service. Test restoration regularly rather than assuming the files are valid. For broader self-hosting reference material, these server administration notes may also be useful when organising backup locations and maintenance routines.

PostgreSQL performs routine vacuuming and analysis through the autovacuum system. Normally, leave autovacuum enabled. It reclaims space from updated or deleted rows and helps the query planner make better decisions. Monitor disk usage because a full filesystem can stop writes even when PostgreSQL itself appears healthy.

Diagnosing Common Problems

When a command fails, first check whether PostgreSQL is running:

sudo systemctl status postgresql
sudo journalctl -u postgresql --since today

A “role does not exist” error usually means the username was not created in PostgreSQL, or the connection is reaching a different server instance. A “database does not exist” message indicates that the database name in the connection command or application configuration is incorrect.

Authentication errors often come from a mismatch between the connection method and pg_hba.conf. A local command using psql -U appuser -d appdb may use a Unix socket and peer authentication, while adding -h localhost uses TCP and may require a password. Testing both forms helps identify the rule being applied.

To see active connections and database activity, connect as an administrator and run:

SELECT pid, usename, datname, client_addr, state
FROM pg_stat_activity;

For slow queries, PostgreSQL offers EXPLAIN and EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email = 'alex@example.com';

Use EXPLAIN ANALYZE carefully with statements that modify data, since it executes the query. Indexes can speed up searches on frequently filtered columns, but unnecessary indexes increase storage use and slow inserts and updates.

Managing PostgreSQL Over Time

Keep Ubuntu and PostgreSQL packages updated through the normal package management process. Before a major version upgrade, read the Ubuntu release notes and PostgreSQL documentation, confirm that application drivers support the target version, and make a tested backup.

Check databases, roles, and service status periodically:

sudo -u postgres psql -c "\l"
sudo -u postgres psql -c "\du"
sudo systemctl is-active postgresql

Use clear naming conventions and migrations for schema changes. Editing production tables manually makes it difficult to reproduce an environment or roll back an application release. Migration tools supplied by Django, Laravel, Rails, Prisma, Flyway, and similar frameworks help keep database structure under version control.

For a small home server, a modest PostgreSQL configuration is usually enough. For a busy service, monitor memory, CPU, storage latency, connection counts, and query performance before changing settings. Hardware in Brisbane, Perth, or regional Australia can be perfectly suitable for a local service, while an application serving users across the country may benefit from a carefully selected cloud region and a tested recovery plan.

Once PostgreSQL is installed, create the appuser role and appdb database, connect with psql, and run a test query to verify the complete setup.