PostgreSQLDatabaseSQL6 min read

Install PostgreSQL

Install PostgreSQL, create roles and databases, and configure authentication for local and remote connections.

PostgreSQL is a powerful open-source relational database known for its reliability, feature set and standards compliance.

Step 1 — Install PostgreSQL

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

Step 2 — Verify the service

bash
sudo systemctl status postgresql

Step 3 — Access the PostgreSQL console

PostgreSQL creates a system user called postgres:

bash
sudo -u postgres psql

Step 4 — Create a role (user)

sql
CREATE ROLE myapp_user WITH LOGIN PASSWORD 'SecurePassword123!';
ALTER ROLE myapp_user CREATEDB;

Step 5 — Create a database

sql
CREATE DATABASE myapp_db OWNER myapp_user;
\q

Step 6 — Test the connection

bash
psql -U myapp_user -d myapp_db -h localhost

Configure remote access (optional)

Edit postgresql.conf:

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

Change:

terminal
listen_addresses = '*'

Edit pg_hba.conf to allow remote connections:

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

Add at the end:

terminal
host    all    all    0.0.0.0/0    scram-sha-256

Restart and allow through firewall:

bash
sudo systemctl restart postgresql
sudo ufw allow 5432/tcp

Useful commands

bash
# List databases
sudo -u postgres psql -c "\l"

# List roles
sudo -u postgres psql -c "\du"

# Backup a database
pg_dump -U myapp_user myapp_db > backup.sql

# Restore a database
psql -U myapp_user myapp_db < backup.sql

Performance tip

Adjust shared_buffers to 25% of total RAM and effective_cache_size to 75% of total RAM for optimal performance on your Baires Host VPS.

Was this guide helpful?

← Back to Linux