Skillset: PostgreSQL DBA

Skillset

PostgreSQL DBA

Subject: Computing / Database knowledge / Database administration

Author of 24 expertises

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.

Share in MD format

## [Skills tree PostgreSQL DBA](https://infinite.education/view/PostgreSQL_DBA_2)

**[Install and run PostgreSQL](https://infinite.education/view/install_and_run_postgresql)**
 -- `Article` [PostgreSQL: Downloads](https://www.postgresql.org/download/)
 -- `Article` [Docker Hub](https://hub.docker.com/_/postgres)
* **Docker**
 -- `Video` [Introduction to docker compose | Docker - YouTube](https://www.youtube.com/watch?v=vQmk9moF8vw)
 -- `Video` [What is Docker? - YouTube](https://www.youtube.com/watch?v=u-YWtdbpEhQ)
 -- `Video` [What is Kubernetes | Kubernetes explained in 15 mins - YouTube](https://www.youtube.com/watch?v=VnvRFRk_51k)
* **Package managers**
* **Managing Postgres service**
**[Configure Postgres](https://infinite.education/view/configure_postgres)**
 -- `Article` [PostgresqlCO.NF: PostgreSQL configuration for humans](http://postgresqlco.nf/)
* **postgresql.conf**
 -- `Article` [PostgresqlCO.NF: PostgreSQL configuration for humans](http://postgresqlco.nf/)
**[SQL concepts](https://infinite.education/view/sql_concepts)**
 -- `Article` [PostgreSQL: Documentation: 12: Chapter 2. The SQL Language](https://www.postgresql.org/docs/current/tutorial-sql.html)
 -- `Article` [PostgreSQL Tutorial - Learn PostgreSQL from Scratch](https://www.postgresqltutorial.com/)
 -- `Article` [PostgreSQL: Documentation: 12: Part II. The SQL Language](https://www.postgresql.org/docs/current/sql.html)
 -- `Article` [DB Fiddle - SQL Database Playground](https://www.db-fiddle.com/)
* **DML queries**
* **COPY**
* **DDL queries**
* **Understand basic data types**
**[basic RDBMS terms and concepts](https://infinite.education/view/basic_rdbms_terms_and_concepts)**
 -- `Tutorial` [Database Design and Relational Theory: Normal Forms and All That Jazz | C.J. Date | download](https://b-ok.cc/book/1311299/c298a7)
 -- `Tutorial` [PostgreSQL: Documentation: 13: Appendix M. Glossary](https://www.postgresql.org/docs/13/glossary.html)
 -- `Tutorial` [SQL and Relational Theory - Christopher J. Date, 2009](http://file.allitebooks.com/20151111/SQL%20and%20Relational%20Theory,%203rd%20Edition.pdf)
* **Object model**
* **Relational model**
* **Databases high-level concepts**
**[SQL optimization technics](https://infinite.education/view/sql_optimization_technics)**
 -- `Book` [SQL Antipatterns: Avoiding the Pitfalls of Database Programming](https://www.r-5.org/files/books/computers/languages/sql/style/Bill_Karwin-SQL_Antipatterns-EN.pdf)
 -- `Article` [SQL Indexing and Tuning e-Book for developers: Use The Index, Luke covers Oracle, MySQL, PostgreSQL, SQL Server, ...](https://use-the-index-luke.com/)
* **Indexes, and their use cases**
* **SQL queries patterns and anti-patterns**
* **SQL schema design patterns and anti-patterns**
**[Automate Routine](https://infinite.education/view/automate_routine)**
* **Configuration management**
* **Automation using shell scripts or any other favourite language**
**[Postgres advanced topics](https://infinite.education/view/postgres_advanced_topics)**
 -- `Article` [PostgreSQL: Documentation: 12: Chapter 42. PL/pgSQL - SQL Procedural Language](https://www.postgresql.org/docs/current/plpgsql.html)
 -- `Article` [The Internals of PostgreSQL : Introduction](http://www.interdb.jp/pg/index.html)
* **Low level internals**
* **Advanced SQL topics**
* **Fine-grained tuning**
**[Develop application DBA skills](https://infinite.education/view/develop_application_dba_skills)**
 -- `Article` [The Art of PostgreSQL: a modern PostgreSQL book in 2020](https://theartofpostgresql.com)
* **Bulk Loading and Processing**
* **Data import and export**
* **Queues**
* **Data partitioning and sharding patterns**
* **Migrations**
* **Database normalization and normal forms**
**[Develop architect skills](https://infinite.education/view/develop_architect_skills)**
* **Differences between Postgres and other RDBMS and NoSQL databases**
* **Postgres forks and extensions**
* **RDBMS**
**[Infrastructure DBA skills](https://infinite.education/view/infrastructure_dba_skills)**
* **Kubernetes**
* **Infrastructure monitoring**
* **Replication**
 -- `Article` [Managing PostgreSQL Replication and PostgreSQL Automatic Failover for High Availability](https://www.enterprisedb.com/postgres-tutorials/postgresql-replication-and-automatic-failover-tutorial)
* **High availability and cluster management tools**
* **Resource usage and provisioning, capacity planning**
* **Upgrading procedures**
* **Backup & Recovery Tools**
* **Connection pooling**
* **Applications Load Balancing and Service Discovery**
**[PostgreSQL Security](https://infinite.education/view/postgresql_security)**
 -- `Article` [PostgreSQL: Documentation: 12: Chapter 20. Client Authentication](https://www.postgresql.org/docs/current/client-authentication.html)
 -- `Article` [PostgreSQL: Documentation: 12: Chapter 21. Database Roles](https://www.postgresql.org/docs/current/user-manag.html)
* **Advanced Topics**
* **pg_hba.conf**
 -- `Article` [PostgreSQL: Documentation: 9.1: The pg_hba.conf File](https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html)
* **roles**
* **Objects privileges**
* **SSL settings**
* **Authentication models**
**[Postgres troubleshooting techniques](https://infinite.education/view/postgres_troubleshooting_techniques)**
 -- `Article` [Linux Performance](http://www.brendangregg.com/linuxperf.html)
 -- `Article` [The USE Method](http://www.brendangregg.com/usemethod.html)
* **Troubleshooting methods**
* **Log analyzing**
* **External tracing/profiling tools**
* **Query analyzing**
* **Postgres tools**
* **Operating system tools**
* **Postgres system views**
**[Develop Postgres hacker skills](https://infinite.education/view/develop_postgres_hacker_skills)**
* **Daily reading and answering in mailing lists**
 -- `Article` [PostgreSQL: PostgreSQL Mailing List Archives](https://www.postgresql.org/list/)
* **Writing patches, attending in Commitfests**
 -- `Article` [Commitfests](https://commitfest.postgresql.org/)
* **Reviewing patches**

Must-have Nice to have Optional

Recommended option

PostgreSQL DBA. Instruction on how to learn PostgreSQL DBA

Roadmap

by Artem Naumenko

Courses

Add

Infinite Education

Or

Skillset