How to install and set up PostgreSQL

This guide needs root access, so it fits VPS and dedicated server customers.

What is PostgreSQL?

PostgreSQL is a free system for storing data. A database is like a filing cabinet for your app's information. PostgreSQL is a rival to MySQL. Many people call it "Postgres". The steps below use Ubuntu or Debian.

Install

1. Log in to your server with SSH. SSH is a safe way to type commands on a remote computer.
2. Update the package list:

   sudo apt update

   This refreshes the list of software you can install.
3. Install PostgreSQL:

   sudo apt install postgresql postgresql-contrib

   This installs the server and some extra tools.

   

   

   

4. Check it runs:

   sudo systemctl status postgresql

   You should see the word "active".

Create a user and a database

Postgres makes an admin account called postgres during install.

1. Switch to that account and open the Postgres prompt:

   sudo -u postgres psql

   This opens psql, the Postgres command tool.
2. Create a user:

   CREATE USER myuser WITH PASSWORD 'StrongPassword';

   This makes a login with a password.
3. Create a database owned by that user:

   CREATE DATABASE mydb OWNER myuser;

   

4. Leave psql with \q.

Test the login

psql -h localhost -U myuser -d mydb

This connects as myuser to mydb. Type the password when asked.

Basic settings

Two files control access. Their folder depends on the version, for example /etc/postgresql/16/main/.

• postgresql.conf: general settings. The line listen_addresses says which addresses to listen on. The default is only this server, which is safest.
• pg_hba.conf: who may connect, and how.

Warning: Do not open Postgres to the whole internet without a firewall and strong passwords. Take a backup of a config file before you edit it. After changes, restart with sudo systemctl restart postgresql.

Quick recap

• Install with apt install postgresql postgresql-contrib.

  

• Use sudo -u postgres psql to manage it.
• Create a user and a database, and link them.
• Keep it closed to outsiders by default.
• Restart the service after config changes.