PostgreSQL Administration
PostgreSQL Administration is the work of operating a PostgreSQL server: configuring it, controlling access, maintaining its data, observing it, backing it up, recovering it, and upgrading it. It keeps the database service available and recoverable so applications can use it safely.
itDatabases and data storage | OpenSkills.info
Course pathWalk it in order
Look it upDip in anytime
Go furtherLeaves this page
Intro
PostgreSQL Administration
PostgreSQL administration is the work around the database, not the SQL inside an application. You run the server, decide who may connect, keep storage healthy, observe what it is doing, and make failure recoverable. A query can be correct while the server that runs it is unavailable, full, or impossible to restore. Administration owns those conditions.
PostgreSQL calls one running server instance a database cluster. A cluster contains one or more databases, plus shared configuration and system catalogs. The postgres server process owns that cluster. Client programs such as psql, pg_dump, and pg_basebackup connect to it over the PostgreSQL protocol.
The operating layers
Most administration work fits one of seven layers. Identify the layer before changing anything. It tells you which configuration, command, or view is relevant.
Configuration and connection policy
postgresql.conf holds server settings. It controls items such as memory, logging, network listeners, and write-ahead log behavior. PostgreSQL can also read settings from included files, which lets you separate local values from configuration managed by your deployment system.
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://www.postgresql.org/about/
Supports
- PostgreSQL identity and the 00-novice.md definition
- Reference link rationale for official PostgreSQL documentation
- https://www.postgresql.org/docs/current/admin.html
Supports
- Scope and organization of PostgreSQL server administration
- 01-intro.md, 02-slides.md, 07-video-script.md, and 06-links.yaml overview
- https://www.postgresql.org/docs/current/runtime-config.html
Supports
- postgresql.conf settings, included files, reload and restart contexts
- Cheatsheet configuration section and related quiz framing
- https://www.postgresql.org/docs/current/client-authentication.html
Supports
- pg_hba.conf matching and authentication behavior
- Intro, slides, cheatsheet, video script, quiz, and link rationale
- https://www.postgresql.org/docs/current/user-manag.html
Supports
- Roles, membership, login attributes, ownership, and SQL privileges
- Intro, slides, cheatsheet, video script, and quiz role content
- https://www.postgresql.org/docs/current/predefined-roles.html
Supports
- pg_monitor and other predefined monitoring roles
- Intro and cheatsheet least-privilege monitoring guidance
- https://www.postgresql.org/docs/current/routine-vacuuming.html
Supports
- MVCC dead row versions, VACUUM, ANALYZE, and autovacuum
- Intro, slides, cheatsheet, video script, quiz, and link rationale
- https://www.postgresql.org/docs/current/backup.html
Supports
- Logical backups, physical base backups, WAL archiving, and point-in-time recovery
- Intro, slides, cheatsheet, video script, quiz, and link rationale
- https://www.postgresql.org/docs/current/app-pgbasebackup.html
Supports
- pg_basebackup scope, standby initialization, and backup progress
- Cheatsheet physical backup commands and related content
- https://www.postgresql.org/docs/current/high-availability.html
Supports
- Physical standby, failover, hot standby, and replication concepts
- Intro, slides, cheatsheet, video script, quiz, and link rationale
- https://www.postgresql.org/docs/current/logical-replication.html
Supports
- Publications, subscriptions, monitoring, restrictions, and security
- Intro, slides, cheatsheet, video script, quiz, and link rationale
- https://www.postgresql.org/docs/current/monitoring.html
Supports
- Statistics views, locks, progress reporting, tracing, and disk usage
- Intro, slides, cheatsheet, video script, quiz, and link rationale
- https://www.postgresql.org/docs/current/monitoring-stats.html
Supports
- pg_stat_activity, pg_stat_database, pg_stat_replication, and progress views
- Cheatsheet monitoring and replication queries
- https://www.postgresql.org/docs/current/pgupgrade.html
Supports
- Major upgrade prerequisites and procedure
- Intro, slides, cheatsheet, video script, quiz, and link rationale
- https://www.postgresql.org/support/versioning/
Supports
- Annual major releases, minor fix releases, and five-year major-version support
- Intro and slides upgrade guidance
- https://github.com/sindresorhus/awesome
Supports
- Discovery path to awesome-postgres
- https://github.com/dhamaniasad/awesome-postgres
Supports
- Selection and categories for every 09-awesome-links.yaml entry
- https://www.pgadmin.org/
Supports
- pgAdmin rationale in 09-awesome-links.yaml
- https://www.pgbouncer.org/
Supports
- PgBouncer rationale in 09-awesome-links.yaml
- https://pgbackrest.org/
Supports
- pgBackRest rationale in 09-awesome-links.yaml
- https://www.pgbadger.org/
Supports
- pgBadger rationale in 09-awesome-links.yaml
- https://patroni.readthedocs.io/
Supports
- Patroni rationale in 09-awesome-links.yaml
- https://github.com/darold/pg_repack
Supports
- pg_repack rationale in 09-awesome-links.yaml