Postgresql guides
Hands-on guides that cover Postgresql. Every command is run and verified on a live server before it is published.
GuideRoles and Least Privilege
Build PostgreSQL roles with least privilege on a live database, then prove each limit holds by watching it refuse the operations an attacker would try.
GuideStreaming Replication: A Live Standby
Set up PostgreSQL streaming replication on Ubuntu 24.04: clone a standby with pg_basebackup, then prove a real row crosses from the primary to the standby.
GuideSynchronous Replication and Slots
Create a PostgreSQL replication slot, then switch a commit to synchronous replication and watch it wait for the standby before it confirms the write.
GuideBackups That Actually Restore
Take a logical backup of a live PostgreSQL database, then do the step everyone skips: restore it into a clean database and check the row counts match.
GuideFailover and Promotion
Perform a PostgreSQL failover on a live server: promote the standby, watch the timeline bump, then rewind the diverged old primary with pg_rewind.
GuideConnections and Pooling
PostgreSQL runs one process per connection. Put PgBouncer connection pooling in front so hundreds of app connections ride on a handful of real ones.
GuideConnection Routing with PgBouncer
PostgreSQL connection routing with PgBouncer: send writes to the primary and reads to the standby over pooled connections, proven with the pool stats.
GuideTuning and Monitoring
PostgreSQL tuning and monitoring on a 1.9 GB VPS: size its memory to your RAM, then use EXPLAIN and pg_stat_statements to find the query costing you the most.
GuideBackups and Point-in-Time Recovery
Set up PostgreSQL point-in-time recovery with WAL archiving, then restore to a chosen second and prove one row is present and a later one is gone.