PostgreSQL administration
You install and configure a PostgreSQL cluster, manage roles, connections, extensions, logs and statistics views, and upgrade versions safely.
Level 301 · 19 lessons · 15 practice labs
Lessons
The cluster
- Cluster architecture: processes, memory and files
- initdb and the data directory
- postgresql.conf and setting contexts
- Managing the service: pg_ctl and systemd
- Tablespaces
- Short task: set up a second cluster
Access
- pg_hba.conf rules
- Roles, membership and inheritance
- Schema privileges and search_path
- Connection limits and timeouts
- Short task: an access layout for three teams
Operations
- Logging settings and log_line_prefix
- Statistics views: pg_stat_activity and friends
- Installing and managing extensions
- VACUUM and autovacuum basics
- Measuring bloat
- Major version upgrades: pg_upgrade
- Maintenance scripts and scheduling
- Capstone: bring a neglected server into shape
Practice labs
- Find which context a setting changes in: Change three settings and confirm with pg_settings which needs a reload and which a restart.
- Set up a second cluster on one server: Create a second cluster with initdb on its own data directory and port and run it as a service.
- Move data to a new disk with a tablespace: Move a big archive table from a full disk to a new tablespace and plan for the lock time.
- Allow logins from a team network: Add a pg_hba.conf rule allowing logins only from the office network with SCRAM and reload.
- Solve a rule order mistake: Find a restriction that does nothing because a broader rule above it allows everyone, and fix the order.
- Diagnose a client that can't connect: Use the server log and pg_hba_file_rules to find which rule rejects a connection.
- Build team roles with membership: Create developer, analyst and operations groups and make users members of them.
- Sort out a search_path mix-up: Fix a search_path setting that makes the wrong one of two same-named tables get read.
- Close the hole in the public schema: Show that everyone can create objects in the public schema, narrow the privileges and verify the app still works.
- Find a session idle in transaction: Use pg_stat_activity to find a connection idle in transaction for hours and terminate it.
- Make log lines meaningful: Set log_line_prefix and the slow-query threshold, then find the slowest user from the log.
- Install and upgrade an extension: Install an extension, list its version and update it to a new version without stopping the server.
- Measure a bloated table: Compare a table's actual and expected size after a mass update and work out the bloat.
- Find why autovacuum isn't running: Find the setting or transaction that stops autovacuum on a table piling up dead rows, and fix it.
- Upgrade a major version with pg_upgrade: Try upgrading an old-major-version cluster in check mode, then upgrade with link mode and refresh statistics.
About this course
You install and configure a PostgreSQL cluster, manage roles, connections, extensions, logs and statistics views, and upgrade versions safely.
What you will learn
- The cluster
- Access
- Operations
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.