PostgreSQL Installation and Basic Administration

Installation

sudo apt update
sudo apt install -y postgresql postgresql-client

PostgreSQL creates a system user called postgres. Switch to it:

sudo -u postgres psql

Creating Databases and Users

-- Create a user
CREATE USER myapp WITH PASSWORD 'secure_password';

-- Create a database owned by that user
CREATE DATABASE mydb OWNER myapp;

-- Grant permissions
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

Remote Access

Edit /etc/postgresql/16/main/postgresql.conf:

listen_addresses = '*'

Edit /etc/postgresql/16/main/pg_hba.conf:

# Allow specific IP
host    mydb    myapp    198.51.100.0/24    scram-sha-256

sudo systemctl restart postgresql

Backup and Restore

# Backup single database
pg_dump -U postgres mydb > mydb_backup.sql

# Backup all databases
pg_dumpall -U postgres > all_databases.sql

# Restore
psql -U postgres mydb < mydb_backup.sql

Useful Commands

\l              -- List databases
\c mydb         -- Connect to database
\dt             -- List tables
\d+ tablename   -- Describe table
\du             -- List users/roles
\q              -- Quit