Install PostgreSQL on Debian 13: Step-by-Step

This guide walks you through installing PostgreSQL on Debian 13 (Trixie) using the official Debian repositories. You’ll install the server, set up a database user, and verify everything works—no third-party packages needed. By the end, you’ll have a running PostgreSQL instance ready for local development or production use.

What You’ll Need

A Debian 13 system with a non-root user that has sudo privileges.
An internet connection to download packages.
Basic familiarity with the terminal and command-line text editors (like nano or vim).

Step-by-Step

Update package lists – Open a terminal and run:
```
sudo apt update
```
This fetches the latest package lists so you install current versions.
Install PostgreSQL – Run:
```
sudo apt install postgresql postgresql-contrib -y
```
The postgresql package installs the core server and client. The postgresql-contrib package adds extra utilities and extensions (like pg_stat_statements).
Check the service status – After installation, PostgreSQL starts automatically. Verify with:
```
sudo systemctl status postgresql
```
You should see active (running). If it isn’t running, start it with sudo systemctl start postgresql.
Switch to the postgres user – PostgreSQL creates a system user named postgres during installation. Use it to access the database:
```
sudo -i -u postgres
```
This gives you a shell as the postgres user.
Open the PostgreSQL prompt – While logged in as postgres, run:
```
psql
```
You’ll see a prompt like postgres=#. This is the SQL shell.
Set a password for the postgres user – Inside psql, run:
```
ALTER USER postgres WITH PASSWORD 'your_strong_password';
```
Replace your_strong_password with a secure password. Then exit psql with \q.
Create a new database user (optional but recommended) – Still as the postgres user, create a dedicated user for your applications:
```
createuser --interactive
```
Follow the prompts: enter a username and choose whether to make it a superuser (usually n for security).
Create a database – Use createdb to make a database owned by your new user:
```
createdb -O your_username your_database_name
```
This sets the owner to your_username.
Exit the postgres user session – Type exit to return to your normal user.
Test remote access (if needed) – By default, PostgreSQL listens only on localhost. To allow remote connections, edit the configuration files:
```
sudo nano /etc/postgresql/15/main/postgresql.conf
```
(Replace 15 with your installed version, e.g., 16 or 17.) Find listen_addresses and set it to '*'. Then edit pg_hba.conf:
```
sudo nano /etc/postgresql/15/main/pg_hba.conf
```
Add a line like host all all 0.0.0.0/0 scram-sha-256 to allow connections from any IP (be careful—this is insecure unless you have a firewall). After changes, restart:
```
sudo systemctl restart postgresql
```
Verify installation – Connect to your new database as your new user:
```
psql -U your_username -d your_database_name -h localhost
```
If you set a password, you’ll be prompted. Success means everything works.

Common Mistakes

Forgetting to switch to the postgres user – Many commands like psql and createdb require you to be the postgres system user. Running them as your regular user will fail with “role does not exist” errors.
Using the wrong version number – The configuration path includes the PostgreSQL version (e.g., /etc/postgresql/15/main/). If you use a wrong version, the file won’t exist. Check with ls /etc/postgresql/.
Editing pg_hba.conf incorrectly – A syntax error in this file can prevent PostgreSQL from starting. Always back up the file before editing: sudo cp /etc/postgresql/15/main/pg_hba.conf /etc/postgresql/15/main/pg_hba.conf.bak.
Not restarting after config changes – Changes to postgresql.conf or pg_hba.conf require a restart to take effect. Run sudo systemctl restart postgresql.

Tips

Use a strong password – For production, avoid simple passwords. Use a password manager to generate one.
Create separate users per application – Don’t use the superuser postgres for your apps. Create a limited user with only the privileges needed.
Enable automatic updates – Set up unattended upgrades to keep PostgreSQL patched: sudo apt install unattended-upgrades and configure it.
Back up your data – Use pg_dump regularly. For example: pg_dump your_database_name > backup.sql.
Check logs on failure – Logs are in /var/log/postgresql/. If something goes wrong, look there first.

Troubleshooting

PostgreSQL won’t start – Check the logs: sudo tail -f /var/log/postgresql/postgresql-15-main.log. Common causes: a misconfigured pg_hba.conf or a port conflict. Ensure no other process is using port 5432: sudo ss -tulpn | grep 5432.
“Role does not exist” error – This means you’re trying to connect with a user that hasn’t been created. Create the user with createuser or use the postgres user.
“Peer authentication failed” – This happens when you try to connect via local socket as a user that doesn’t match the PostgreSQL role. Use sudo -i -u postgres to connect as postgres, or specify -h localhost to force TCP and use password authentication.
Can’t connect remotely – Check that listen_addresses is set and that pg_hba.conf has a line allowing your IP range. Also ensure your firewall allows port 5432 (e.g., sudo ufw allow 5432/tcp).
Password not working – If you set a password but still get “password authentication failed”, you may be using an old authentication method. Ensure pg_hba.conf uses scram-sha-256 (or md5) for TCP connections, not trust.

Frequently Asked Questions

How do I uninstall PostgreSQL completely? – Run sudo apt remove --purge postgresql postgresql-contrib and then sudo rm -rf /var/lib/postgresql to delete data. Be careful—this destroys all databases.
Which PostgreSQL version does Debian 13 include? – Debian 13 ships with PostgreSQL 16 by default, but you can install other versions from the official PostgreSQL apt repository if needed.
How do I start PostgreSQL after a reboot? – It should start automatically. If not, run sudo systemctl enable --now postgresql to enable and start it.
Can I run PostgreSQL in a Docker container instead? – Yes, but the native install is simpler for a single server. Docker adds overhead and requires managing volumes for persistence.
How do I reset the postgres password if I forgot it? – Edit pg_hba.conf to temporarily change the local connection method to trust, restart PostgreSQL, connect without a password, set a new password, then revert the file and restart again.
Is it safe to expose PostgreSQL to the internet? – Generally no. Use a VPN or SSH tunnel instead. If you must, use strong passwords, firewall rules, and consider using SSL/TLS.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →