How to install PostgreSQL on Ubuntu

PostgreSQL is a powerful open-source relational database. It is known for reliability, advanced features, and good performance. If you need a database that handles complex queries and large datasets well, PostgreSQL is a solid choice.

Step 1: Update and install

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

The postgresql-contrib package adds extra utilities and extensions.

Step 2: Check that it is running

sudo systemctl status postgresql

You should see "active (exited)" or "active (running)". PostgreSQL starts automatically after installation.

Step 3: Understand the default user

PostgreSQL creates a system user called postgres during installation. To work with the database, you switch to this user:

sudo -i -u postgres

Now you can access the PostgreSQL prompt:

psql

You are now in the PostgreSQL interactive shell. Type \q to exit, or \? to see all commands.

Step 4: Create a database and user

While still logged in as the postgres user, create a dedicated user and database for your application:

# From the psql prompt
CREATE USER myapp_user WITH PASSWORD 'strongpassword';

CREATE DATABASE myapp_db OWNER myapp_user;

GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

\q

Then exit the postgres system user:

exit

Step 5: Configure remote access (optional)

By default, PostgreSQL only listens on localhost. If you need to connect from another machine, edit the config file:

sudo nano /etc/postgresql/16/main/postgresql.conf

Find the line that says #listen_addresses = 'localhost' and change it to:

listen_addresses = '*'

Then update the client authentication file:

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

Add this line at the bottom to allow password-based connections from any IP:

host    myapp_db    myapp_user    0.0.0.0/0    md5

For better security, replace 0.0.0.0/0 with your specific IP.

Step 6: Restart PostgreSQL

sudo systemctl restart postgresql

If you use UFW, allow the PostgreSQL port:

sudo ufw allow 5432/tcp

Step 7: Test the connection

From your local machine, test connecting to the remote database:

psql -h your-server-ip -U myapp_user -d myapp_db

Enter the password when prompted, and you should be in.

Useful psql commands

Command	What it does
\l	List all databases
\c dbname	Connect to a database
\dt	Show tables in current database
\du	List users/roles
\d tablename	Describe a table
\q	Exit psql
\?	List all commands

Backup and restore

Backup a database:

pg_dump myapp_db > myapp_db_backup.sql

Restore a backup:

psql myapp_db < myapp_db_backup.sql

For PostgreSQL 16, the version number in paths may differ. Check your installed version with psql --version and adjust paths accordingly.

Common issues

"Connection refused" Check that PostgreSQL is running and listening on the right address. Verify the listen_addresses setting in postgresql.conf.

"Peer authentication failed" This happens when trying to connect as a different Linux user. Use sudo -u postgres psql or switch to the postgres system user.

"FATAL: password authentication failed" Reset the password:

sudo -u postgres psql -c "ALTER USER myapp_user WITH PASSWORD 'newpassword';"

Tuning for small VPS

If your VPS has limited memory, add these to postgresql.conf:

shared_buffers = 256MB
effective_cache_size = 512MB
work_mem = 16MB
maintenance_work_mem = 64MB

Adjust the values based on your available RAM. A good rule is to set shared_buffers to about 25% of total RAM.

Conclusion

PostgreSQL is now installed and ready. Whether you are running a web application, a game server, or an analytics tool, it will handle your data reliably.