PostgreSQL Installation and Basic Configuration

sudo apt install -y postgresql postgresql-contrib

# Latest version (official PostgreSQL APT)
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update && sudo apt install -y postgresql-16

sudo systemctl enable --now postgresql

sudo -u postgres psql

# Commands inside psql
\l          -- list databases
\c mydb     -- switch database
\dt         -- list tables
\d users    -- table structure
\du         -- list users
\q          -- quit

CREATE USER myapp WITH PASSWORD 'strongpassword123!';
CREATE DATABASE myappdb OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE myappdb TO myapp;
GRANT ALL ON SCHEMA public TO myapp;
GRANT ALL ON ALL TABLES IN SCHEMA public TO myapp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO myapp;

listen_addresses = '*'          # allow remote connections
max_connections = 100
shared_buffers = 256MB          # recommended: 25% of RAM
work_mem = 4MB
log_slow_query = 1000           # log queries taking 1 second or more

local   all   postgres    peer
local   all   all         md5
host    myappdb   myapp   10.0.0.0/24   scram-sha-256

sudo systemctl reload postgresql

pg_dump -U myapp -h localhost myappdb > backup.sql
pg_dump -U myapp -h localhost -Fc myappdb > backup.dump

sudo -u postgres pg_dumpall > all_databases.sql

psql -U myapp -h localhost myappdb < backup.sql
pg_restore -U myapp -h localhost -d myappdb backup.dump

-- current connection count
SELECT count(*) FROM pg_stat_activity;

-- running queries
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity WHERE state = 'active';

-- table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;