Install PostgreSQL

PostgreSQL is a powerful, open-source relational database. Ubuntu 24.04 ships PostgreSQL 16 in its standard repositories, so one command installs it. In this guide you will create a login role for yourself, a database it owns, and connect to it from the server.

Before you begin

• An Ubuntu 24.04 LTS VPS with at least 1 GB of RAM.
• SSH access as a regular user with sudo rights, such as alex.
• A few minutes to read the output of each command. Nothing here changes files outside PostgreSQL's own folders.

Step 1: Install PostgreSQL

Refresh the package list, then install the server plus postgresql-contrib, a bundle of optional extensions. Ubuntu starts the server automatically and creates a default cluster named main on port 5432.

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

Check that the cluster is online and confirm the client version:

pg_lsclusters
psql --version

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

psql (PostgreSQL) 16.x (Ubuntu 16.x-...)

Step 2: Run a first command as the postgres user

The installer creates a Linux user named postgres, and PostgreSQL has a superuser role with the same name. Administrative tasks run through that account. The -c option runs a single SQL statement and exits, which is handy for quick checks.

sudo -u postgres psql -c "SELECT version();"

                                   version
-------------------------------------------------------------------------------
 PostgreSQL 16.x on x86_64-pc-linux-gnu, compiled by gcc ...

Step 3: Create a role and a database for yourself

A role is a database user. Rather than working as the superuser, create a normal role for yourself and one for each application. createuser --pwprompt asks you for a password without displaying it. Then createdb --owner creates a database that your role owns.

sudo -u postgres createuser --pwprompt alex
sudo -u postgres createdb --owner=alex appdb

Enter the password twice when prompted. Choose a long one and store it in a password manager.

Step 4: Connect locally

Connect with the role you just made. The -h localhost option forces a TCP connection, so PostgreSQL asks for your password. Without it, PostgreSQL may try "peer" login, which only works when your Linux username matches the role name.

psql -h localhost -U alex -d appdb

After you type the password, the prompt changes to appdb=>. Try two commands. Inside psql, end every SQL statement with a semicolon. Commands that start with a backslash are psql's own.

SELECT current_user, current_database();
\l
\q

\l lists databases, and \q quits.

Step 5: Confirm it listens only on localhost

By default PostgreSQL accepts connections only from the server itself. That is what you want. Check the listening address:

sudo ss -tlnp | grep 5432

LISTEN 0  200  127.0.0.1:5432  0.0.0.0:*  users:(("postgres",pid=...))
LISTEN 0  200      [::1]:5432     [::]:*  users:(("postgres",pid=...))

Troubleshooting

• Peer authentication failed for user "alex": you left out -h localhost. Add it so password login is used.
• Password authentication failed: retype the password, or reset it inside sudo -u postgres psql with \password alex. That command prompts you, so the password stays out of your shell history.
• Connection refused: the cluster is stopped. Run pg_lsclusters and then sudo systemctl restart postgresql.
• Database "appdb" does not exist: run the createdb command from Step 3 again, checking the spelling.

Next steps

Your database is ready for a first table. Protect it early with the PostgreSQL backup guide, and when you want to reach it from your laptop, follow the SSH tunnel guide rather than opening the port.