Install & Connect to PostgreSQL on Ubuntu

This guide walks you through installing PostgreSQL on Ubuntu and connecting to it from the command line, covering both local and remote connections. You’ll learn how to set up the database server, create users and databases, and troubleshoot common connection issues.

What You’ll Need

An Ubuntu system (20.04, 22.04, 24.04, or newer) with sudo access
Internet connection to download packages
Basic familiarity with the terminal

Step-by-Step

Update package lists
Open a terminal and run:
```
sudo apt update
```
This refreshes the package index so you get the latest available versions.
Install PostgreSQL
Install the PostgreSQL server and client packages:
```
sudo apt install postgresql postgresql-contrib
```
The postgresql package includes the server, and postgresql-contrib adds useful extensions and tools.
Check the service status
After installation, PostgreSQL should start automatically. Verify with:
```
sudo systemctl status postgresql
```
If it’s not running, start it with sudo systemctl start postgresql and enable it to start on boot with sudo systemctl enable postgresql.
Access the PostgreSQL prompt
By default, PostgreSQL creates a superuser named postgres. Switch to that user and open the psql shell:
```
sudo -i -u postgres
psql
```
You should see a prompt like postgres=#.
Set a password for the postgres user
While in psql, set a strong password (replace your_password):
```
ALTER USER postgres PASSWORD 'your_password';
```
Type \q to exit psql, then exit to return to your normal user.
Create a new database user (optional but recommended)
It’s safer to use a dedicated user instead of the postgres superuser. From your terminal, run:
```
sudo -i -u postgres psql -c "CREATE USER myuser WITH PASSWORD 'mypassword';"
```
Replace myuser and mypassword with your own.
Create a database
Create a database owned by your new user:
```
sudo -i -u postgres psql -c "CREATE DATABASE mydb OWNER myuser;"
```
Now you have a database ready to use.
Connect locally using psql
Connect to your database as your new user:
```
psql -h localhost -U myuser -d mydb
```
You’ll be prompted for the password you set. If you’re connecting from the same machine, you can omit -h localhost to use Unix socket authentication.
Enable remote connections (optional)
To allow connections from other machines, edit the configuration files:
```
sudo nano /etc/postgresql/*/main/postgresql.conf
```
Find the line #listen_addresses = 'localhost' and change it to:
```
listen_addresses = '*'
```
Then edit the client authentication file:
```
sudo nano /etc/postgresql/*/main/pg_hba.conf
```
Add a line to allow password connections from your network, for example:
```
host    all             all             192.168.1.0/24        md5
```
Replace the IP range with your own. Restart PostgreSQL to apply changes:
```
sudo systemctl restart postgresql
```
Connect from a remote client
From another machine, use psql or any PostgreSQL client (like pgAdmin) with the server’s IP address:
```
psql -h server_ip -U myuser -d mydb
```
Make sure the server’s firewall allows incoming connections on port 5432.

Common Mistakes

Forgetting to set a password – The postgres user has no password by default, so remote connections fail unless you set one.
Editing the wrong config file – There can be multiple PostgreSQL versions, so always check the version directory under /etc/postgresql/.
Not restarting the service – Changes to postgresql.conf or pg_hba.conf require a restart to take effect.
Using the wrong authentication method – If you set md5 but your client sends a plain password, it may fail. Use scram-sha-256 for newer versions.
Firewall blocking port 5432 – Even if PostgreSQL listens on all interfaces, a firewall can block remote connections.

Tips

Use strong passwords and avoid using the postgres superuser for daily tasks.
Back up your databases regularly with pg_dump.
Use \? inside psql to see all available commands.
For production, consider using SSL connections to encrypt data in transit.
Keep PostgreSQL updated with sudo apt upgrade to get security patches.

Troubleshooting

Connection refused – Check if PostgreSQL is running (sudo systemctl status postgresql). If it’s not, start it. Also verify the port with sudo ss -tlnp | grep 5432.
Peer authentication failed – This happens when you try to connect as a user that doesn’t match your system user. Use sudo -i -u postgres or specify -h localhost to force TCP.
Password authentication failed – Double-check the password and that you’re connecting to the right host. If you just changed the password, restart the service.
Role does not exist – You’re trying to connect with a user that hasn’t been created. Create it with CREATE USER.
Database does not exist – Create the database first with CREATE DATABASE.
Cannot connect remotely – Check that listen_addresses includes '*', pg_hba.conf has the right rule, and the firewall allows port 5432.

Frequently Asked Questions

How do I uninstall PostgreSQL completely?

Use sudo apt purge postgresql* to remove all PostgreSQL packages, then delete the data directory (/var/lib/postgresql) if you want to remove all data.

What is the default port for PostgreSQL?

The default port is 5432. You can change it in postgresql.conf, but then you must update your connection strings.

How do I reset the postgres user password if I forgot it?

Edit pg_hba.conf to temporarily change the authentication method to trust, restart PostgreSQL, connect, set a new password, then revert the change.

Can I use PostgreSQL with Python or Node.js?

Yes, use libraries like psycopg2 for Python or pg for Node.js. You’ll need the host, port, database, user, and password.

How do I list all databases and users?

Inside psql, use \l to list databases and \du to list users.

Is it safe to expose PostgreSQL to the internet?

It’s risky. Use a VPN, restrict IP ranges in pg_hba.conf, and enable SSL. Better yet, keep it on a private network.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →