PostgreSQL Essentials for DevOps

1. 1

   Start Postgres and look around

   psql meta-commands start with a backslash: \l lists databases, \dt tables, \du roles, and \x toggles expanded output.

   terminalcopy cmd

   $ docker run -d --name pg -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:17

   psql postgresql://postgres:pw@localhost:5432/postgres -c 'select version()' -c 'show shared_buffers' -c 'show max_connections'

   ── expected output ──

   PostgreSQL 17.6 (Debian 17.6-1.pgdg120+1) on x86_64-pc-linux-gnu ...

   shared_buffers

   ----------------

   128MB

   max_connections

   -----------------

   100

2. 2

   Create least-privilege roles

   The owner creates tables; the app role can only read and write data; default privileges make future tables follow the same rule.

   CREATE DATABASE shop;
   \c shop
   CREATE ROLE shop_owner LOGIN PASSWORD 'owner-pw';
   CREATE ROLE shop_app   LOGIN PASSWORD 'app-pw' CONNECTION LIMIT 50;
   CREATE ROLE shop_ro    LOGIN PASSWORD 'ro-pw';
   CREATE SCHEMA shop AUTHORIZATION shop_owner;
   GRANT USAGE ON SCHEMA shop TO shop_app, shop_ro;
   ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA shop
     GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO shop_app;
   ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA shop
     GRANT SELECT ON TABLES TO shop_ro;
   REVOKE CREATE ON SCHEMA public FROM PUBLIC;

   terminalcopy cmd

   $ psql postgresql://postgres:pw@localhost/postgres -f roles.sql

   psql postgresql://shop_app:app-pw@localhost/shop -c 'create table shop.x(id int)'

   ── expected output ──

   ERROR: permission denied for schema shop

3. 3

   Watch MVCC create dead rows

   Update every row once and the table doubles in dead tuples until vacuum runs. pg_stat_user_tables is where you watch bloat and autovacuum activity.

   terminalcopy cmd

   $ psql postgresql://postgres:pw@localhost/shop <<'SQL'

   create table t(id int, v int); insert into t select g, 0 from generate_series(1,1000000) g;

   update t set v = 1;

   select n_live_tup, n_dead_tup, last_autovacuum from pg_stat_user_tables where relname='t';

   vacuum t;

   select n_live_tup, n_dead_tup from pg_stat_user_tables where relname='t';

   SQL

   ── expected output ──

   n_live_tup | n_dead_tup | last_autovacuum

   ------------+------------+-----------------

   1000000 | 1000000 |

   n_live_tup | n_dead_tup

   ------------+------------

   1000000 | 0

4. 4

   Find sessions blocking vacuum

   pg_stat_activity is the first query in any Postgres incident: who is connected, what are they running, and for how long.

   terminalcopy cmd

   $ psql postgresql://postgres:pw@localhost/shop -c "select pid, usename, state, now()-xact_start as xact_age, left(query,40) from pg_stat_activity where state <> 'idle' order by xact_age desc nulls last limit 5"

   ── expected output ──

   pid | usename | state | xact_age | left

   -------+----------+---------------------+-----------------+-----------------------------------------

   4127 | analyst | idle in transaction | 03:12:44.10912 | select * from orders where created_at >

   4190 | postgres | active | 00:00:00.00121 | select pid, usename, state, now()-xact_

Each drill is a real failure mode. Read the scenario and the output, decide what's wrong, then reveal the diagnosis.