Install PostgreSQL on Ubuntu 24.04 and Debian 12/13 and allow remote connections

Short answer: sudo apt install postgresql installs the distribution’s version, which is PostgreSQL 16 on Ubuntu 24.04, 15 on Debian 12 and 17 on Debian 13, and starts it right away. Log in as the postgres superuser with sudo -u postgres psql, create a login role and a database owned by it, and you are done. For a newer major version, add the PostgreSQL project’s own APT repository (PGDG) first. Remote access needs listen_addresses, a line in pg_hba.conf and a firewall rule for your IP only.

sudo apt update
sudo apt install -y postgresql
sudo -u postgres psql

-- inside psql
CREATE ROLE app LOGIN PASSWORD 'a-long-random-password';
CREATE DATABASE app OWNER app;
\q

Distribution package or PGDG repository?

• Distribution package — tested with the rest of the OS, patched through normal apt upgrade, and fixed to one major version for the life of the release. The right default.
• PGDG (apt.postgresql.org, run by the PostgreSQL project) — the newest major version plus every older one still supported, and extensions such as PostGIS or pgvector built for each. Use it when an application needs a newer version than your distribution has, or when all your servers should run the same one.

Both use the same Debian packaging, so every command below works either way. The postgresql-common package brings a script that sets up the repository and its signing key:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh    # confirm with Enter

sudo apt install -y postgresql        # now the newest major version
sudo apt install -y postgresql-17     # or a specific one

How do I install PostgreSQL on Ubuntu 24.04 and Debian?

sudo apt update
sudo apt install -y postgresql
pg_lsclusters

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

Debian and Ubuntu create a first cluster called main, start it and enable it at boot. The configuration lives in /etc/postgresql/16/main/ (with your version number), the data in /var/lib/postgresql/16/main/. Out of the box the server listens on localhost only, port 5432.

How do I install PostgreSQL on Rocky Linux 9?

Rocky’s plain package is PostgreSQL 13. Newer versions come as module streams, and unlike on Debian you initialise the cluster yourself:

sudo dnf module list postgresql            # available streams
sudo dnf module enable -y postgresql:16
sudo dnf install -y postgresql-server
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

Configuration and data both sit in /var/lib/pgsql/data/. Rocky’s initial pg_hba.conf uses ident for TCP connections from localhost, which fails for password logins with Ident authentication failed. Change those two host lines to scram-sha-256 and run sudo systemctl reload postgresql.

Why do I get “Peer authentication failed for user”?

$ psql -U app app
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "app"

Connections over the local Unix socket use peer authentication: PostgreSQL asks the kernel which operating-system user is connecting and only lets that user in as the role with the same name. You are logged in as root or your own account, not as app, so it refuses, without ever asking for a password. The same rule causes FATAL: role "root" does not exist when you run plain psql as root.

• For administration, become the matching OS user: sudo -u postgres psql.
• For an application role, connect over TCP, where the default pg_hba.conf asks for the password: psql -h localhost -U app app.
• In applications, use a URL with a host, such as postgresql://app:password@127.0.0.1:5432/app.

Do not “fix” it by changing peer to trust: that lets every local user log in as any role, the superuser included.

How do I create a user and a database?

CREATE ROLE app LOGIN;
\password app
CREATE DATABASE app OWNER app;
\l
\du
\q

\password asks for the password twice and sends only a hash, so it stays out of your shell history and the server log. \l lists databases, \du lists roles. CREATE ROLE app LOGIN PASSWORD '...' works too. From the shell, the same is sudo -u postgres createuser --pwprompt app followed by sudo -u postgres createdb --owner=app app. Passwords are stored as SCRAM-SHA-256 hashes, the default since PostgreSQL 14.

How do I allow remote connections to PostgreSQL?

If you only need to reach the database from your own computer, use an SSH tunnel and leave port 5432 closed:

ssh -N -L 5433:127.0.0.1:5432 you@203.0.113.10

# second terminal, or point pgAdmin / DBeaver at 127.0.0.1:5433
psql -h 127.0.0.1 -p 5433 -U app app

For an application server elsewhere, three settings have to agree. Paths are for Debian and Ubuntu with version 16; on Rocky both files are in /var/lib/pgsql/data/.

1. 1Listen on the network

   listen_addresses = '*'          # or 'localhost,203.0.113.10'

   Debian’s postgresql.conf reads every file in conf.d/, so a small file of your own survives upgrades. This setting needs a restart, not a reload.

2. 2Allow one address in pg_hba.conf

   # TYPE  DATABASE  USER  ADDRESS          METHOD
   host    app       app   198.51.100.7/32  scram-sha-256

   PostgreSQL uses the first line that matches, top to bottom. Debian and Ubuntu enable TLS with a self-signed certificate by default; write hostssl instead of host to refuse unencrypted connections.

3. 3Restart and open the firewall for that address

   sudo systemctl restart postgresql
   sudo ss -tlnp | grep 5432                 # 0.0.0.0:5432 now
   
   # Ubuntu, Debian
   sudo ufw allow from 198.51.100.7 to any port 5432 proto tcp
   
   # Rocky
   sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="198.51.100.7/32" port port="5432" protocol="tcp" accept'
   sudo firewall-cmd --reload

4. 4Test from the application server

   psql -h 203.0.113.10 -U app app

   Connection refused or a timeout means the server does not listen or the firewall blocks: check steps 1 and 3. no pg_hba.conf entry for host "198.51.100.7" means you got through but no pg_hba.conf line matches that address, user and database.

How do I tune PostgreSQL for a small VPS?

The defaults are deliberately modest: shared_buffers 128 MB, work_mem 4 MB, max_connections 100. Three settings matter most:

• shared_buffers — PostgreSQL’s own cache. The common rule of thumb is about 25% of RAM on a server dedicated to the database, less when a web stack shares the machine.
• effective_cache_size — only a hint for the query planner about how much the OS caches; it allocates nothing. Half to three quarters of RAM is typical.
• work_mem — memory per sort or hash step, per connection. A query can use it several times, and many connections at once multiply it, so raising it carelessly is a classic way to run out of memory. Keep it small globally and raise it per session for heavy reports.

sudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '1GB';"
sudo -u postgres psql -c "ALTER SYSTEM SET effective_cache_size = '3GB';"
sudo -u postgres psql -c "ALTER SYSTEM SET work_mem = '16MB';"
sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"

ALTER SYSTEM writes to postgresql.auto.conf, which overrides postgresql.conf. For many short-lived connections, a pooler such as PgBouncer saves more memory than any setting. If the kernel has already killed a postgres process, see Killed: why Linux kills your process when memory runs out.

How do I back up PostgreSQL?

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql

# one database, in the compressed custom format
sudo -u postgres pg_dump -Fc -f /var/backups/postgresql/app-$(date +%F).dump app

# roles and passwords live outside the databases
sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgresql/globals.sql

# restore into a fresh database
sudo -u postgres createdb -O app app_restore
sudo -u postgres pg_restore -d app_restore /var/backups/postgresql/app-2026-11-12.dump

pg_dump takes a consistent snapshot while the database keeps running. Write with -f rather than a shell redirect: with sudo -u postgres … > file the redirect runs as you, not as postgres. Scheduled from /etc/cron.d as the postgres user, peer authentication logs it in without a stored password; remember that cron needs % escaped:

30 3 * * * postgres pg_dump -Fc -f /var/backups/postgresql/app-$(date +\%F).dump app
15 4 * * * postgres find /var/backups/postgresql -name 'app-*.dump' -mtime +14 -delete

Copy the dumps off the server too. If the job stays silent, see cron job not running.

How do I upgrade to a new major version?

On Debian and Ubuntu, pg_upgradecluster moves a cluster to the new version and hands it the old port. Installing the new version creates an empty main cluster on the next free port, which has to go first. Take a dump before you start:

sudo apt install -y postgresql-17
sudo pg_dropcluster 17 main --stop          # the new, empty cluster
sudo pg_upgradecluster -m upgrade 16 main   # pg_upgrade method, faster than -m dump
pg_lsclusters                               # 17 on 5432, 16 on 5433 and down
sudo pg_dropcluster 16 main                 # deletes 16's data for good: only after testing

On Rocky, switch the module stream and use postgresql-setup --upgrade from the postgresql-upgrade package, or simply dump and restore.

Can I run PostgreSQL in Docker instead?

Yes, and it is handy for running several versions side by side. Publish the port on localhost only: ports published by Docker bypass ufw rules, so -p 5432:5432 would put the database on the internet whatever ufw says.

docker run -d --name pg --restart unless-stopped \
  -e POSTGRES_PASSWORD=change-me \
  -v pgdata:/var/lib/postgresql/data \
  -p 127.0.0.1:5432:5432 \
  postgres:17

Use another host port if a native PostgreSQL already listens on 5432. Setting Docker up is covered in installing Docker on Ubuntu and Debian.

PostgreSQL or MariaDB?

Let the application decide. Many PHP applications, WordPress first, expect MySQL or MariaDB; many newer projects, from automation tools to Mastodon, prefer or require PostgreSQL. Where you are free to choose, PostgreSQL’s stricter SQL, JSONB and extensions such as PostGIS and pgvector are strong arguments. The MariaDB side is covered in installing MariaDB or MySQL.

On a VMHeaven Standard KVM server, the disks sit on replicated Ceph storage and memory grows with each plan, so moving to a bigger plan is the simple answer when shared_buffers outgrows the box. The Ubuntu and Debian images run every command on this page unchanged.

Frequently asked