PostgreSQL - Installation and Management

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

# Check version and status
psql --version
sudo systemctl status postgresql

# Add official PostgreSQL repository
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Install specific version
sudo apt install postgresql-16 -y

sudo dnf install postgresql-server postgresql-contrib -y
sudo postgresql-setup --initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Access as postgres
sudo -u postgres psql

# Or
sudo su - postgres
psql

-- Create user
CREATE USER username WITH PASSWORD 'secure_password';

-- Create database
CREATE DATABASE dbname;

-- Assign database to user
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

-- PostgreSQL 15+: also grant the public schema
\c dbname
GRANT ALL ON SCHEMA public TO username;

\q

\l              -- list databases
\c dbname       -- connect to a database
\dt             -- list tables in current database
\du             -- list users/roles
\d tablename    -- structure of a table
\q              -- exit

-- Database backup
-- (from shell, not from psql)

# Backup a single database
pg_dump -U username dbname > /tmp/backup.sql

# Backup with compression
pg_dump -U username -F c dbname > /tmp/backup.dump

# Backup all databases
sudo -u postgres pg_dumpall > /tmp/all-databases.sql

# Restore
psql -U username -d dbname < /tmp/backup.sql

# Restore custom format
pg_restore -U username -d dbname /tmp/backup.dump

sudo nano /etc/postgresql/16/main/postgresql.conf
# Replace 16 with your version

listen_addresses = '*'          # listen on all interfaces
# or: listen_addresses = 'localhost,185.100.x.x'

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

# Type   Database   User        Address             Method
host     dbname     username    0.0.0.0/0           scram-sha-256
# To connect from a specific IP:
host     dbname     username    1.2.3.4/32          scram-sha-256

sudo ufw allow 5432/tcp

sudo systemctl restart postgresql

const { Pool } = require('pg')
const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'dbname',
  user: 'username',
  password: 'secure_password',
})

import psycopg2
conn = psycopg2.connect(
    host="localhost",
    database="dbname",
    user="username",
    password="secure_password"
)

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'dbname',
        'USER': 'username',
        'PASSWORD': 'secure_password',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

# Memory (adjust based on available RAM)
shared_buffers = 256MB          # 25% of RAM
effective_cache_size = 1GB      # 75% of RAM
work_mem = 4MB                  # per sort/hash operation
maintenance_work_mem = 64MB     # for VACUUM, CREATE INDEX

# Connections
max_connections = 100

# WAL
wal_buffers = 16MB
checkpoint_completion_target = 0.9

sudo systemctl restart postgresql

-- Active connections
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE state != 'idle';

-- Database size
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database ORDER BY pg_database_size(datname) DESC;

-- Largest tables
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;