PostgreSQL DBA

The intent of this guide is to give you an idea about the DBA landscape and to help guide your learning if you are confused. The roadmap is highly opinionated — neither, knowing everything listed in the roadmap, nor the order of items given in the roadmap is required to be followed in order to be a DBA.

Develop Postgres hacker skills

Daily reading and answering in mailing lists

• Article PostgreSQL: PostgreSQL Mailing List Archives

Reviewing patches

• Article PostgreSQL: PostgreSQL Mailing List Archives

Writing patches, attending in Commitfests

• Article PostgreSQL: PostgreSQL Mailing List Archives

Postgres troubleshooting techniques

• Article The USE Method
• Article Linux Performance

Troubleshooting methods

Operating system tools

Postgres tools

External tracing/profiling tools

Query analyzing

Log analyzing

Postgres system views

PostgreSQL Security

• Article PostgreSQL: Documentation: 12: Chapter 20. Client Authentication
• Article PostgreSQL: Documentation: 12: Chapter 21. Database Roles

pg_hba.conf

• Article PostgreSQL: Documentation: 9.1: The pg_hba.conf File

Authentication models

• Article PostgreSQL: Documentation: 9.1: The pg_hba.conf File

SSL settings

• Article PostgreSQL: Documentation: 9.1: The pg_hba.conf File

Advanced Topics

• Article PostgreSQL: Documentation: 9.1: The pg_hba.conf File

Objects privileges

• Article PostgreSQL: Documentation: 9.1: The pg_hba.conf File

roles

• Article PostgreSQL: Documentation: 9.1: The pg_hba.conf File

Infrastructure DBA skills

Applications Load Balancing and Service Discovery

Resource usage and provisioning, capacity planning

Kubernetes

Connection pooling

Upgrading procedures

Backup & Recovery Tools

Infrastructure monitoring

High availability and cluster management tools

Develop architect skills

RDBMS

Differences between Postgres and other RDBMS and NoSQL databases

Postgres forks and extensions

Develop application DBA skills

• Article The Art of PostgreSQL: a modern PostgreSQL book in 2020

Data partitioning and sharding patterns

Data import and export

Bulk Loading and Processing

Queues

Migrations

Database normalization and normal forms

Postgres advanced topics

• Article PostgreSQL: Documentation: 12: Chapter 42. PL/pgSQL - SQL Procedural Language
• Article The Internals of PostgreSQL : Introduction

Low level internals

Advanced SQL topics

Fine-grained tuning

Automate Routine

Automation using shell scripts or any other favourite language

Configuration management

SQL optimization technics

• Book SQL Antipatterns: Avoiding the Pitfalls of Database Programming
• Article SQL Indexing and Tuning e-Book for developers: Use The Index, Luke covers Oracle, MySQL, PostgreSQL, SQL Server, ...

SQL schema design patterns and anti-patterns

SQL queries patterns and anti-patterns

Indexes, and their use cases

basic RDBMS terms and concepts

• Tutorial PostgreSQL: Documentation: 13: Appendix M. Glossary
• Tutorial SQL and Relational Theory - Christopher J. Date, 2009
• Tutorial Database Design and Relational Theory: Normal Forms and All That Jazz | C.J. Date | download

Databases high-level concepts

Object model

Relational model

SQL concepts

• Article PostgreSQL: Documentation: 12: Part II. The SQL Language
• Article PostgreSQL Tutorial - Learn PostgreSQL from Scratch
• Article DB Fiddle - SQL Database Playground
• Article PostgreSQL: Documentation: 12: Chapter 2. The SQL Language

COPY

• Article Import CSV File Into PosgreSQL Table

DDL queries

• Article Import CSV File Into PosgreSQL Table

Understand basic data types

• Article Import CSV File Into PosgreSQL Table

DML queries

• Article Import CSV File Into PosgreSQL Table

Configure Postgres

• Article PostgresqlCO.NF: PostgreSQL configuration for humans

postgresql.conf

• Article PostgresqlCO.NF: PostgreSQL configuration for humans

Install and run PostgreSQL

• Article Docker Hub
• Article PostgreSQL: Downloads

Managing Postgres service

Package managers

Docker

To see a interactive roadmap click on picture