PostgreSQL Scripts for Database Administration

Backing Up a Remote PostgreSQL Database with pg_dump The database lives on a managed host three networks away, the only backup is whatever the provider snapshots, and nobody has ever tried to restore from it. A logical dump taken from a laptop or a jump box fixes that in one command, because pg_dump is an ordinary …

Find the Largest PostgreSQL Indexes by Size Which indexes are taking up the most disk in this database? pg_index joined to pg_class answers that in one query: every index with its table, schema, tablespace, size, and the command that would recreate it, sorted with the biggest first. It is the inventory step before any …

Monitoring PostgreSQL Index Usage: Keeping Your Queries Speedy In the fast-paced world of web applications, ensuring efficient database queries is paramount. PostgreSQL's powerful indexing capabilities play a crucial role in achieving optimal performance. This post delves into a SQL query that helps you monitor index …