Skillset: PostgreSQL DBA

Skillset

PostgreSQL DBA

Subject: Computing / Database knowledge / Database administration

Author of 26 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)
* **Managing Postgres service**
* **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` [CURSO DE DOCKER 💥2021, Curso Práctico para principiantes (desde Linux) - YouTube](https://www.youtube.com/watch?v=3j1oXqJFlLw&list=PLdOotbFwzDIjPK7wcu4MBCZhm9Lj6mX11)
 -- `Video` [What is Kubernetes | Kubernetes explained in 15 mins - YouTube](https://www.youtube.com/watch?v=VnvRFRk_51k)
* **Package managers**
**[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**
* **Understand basic data types**
* **COPY**
* **DDL queries**
**[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**
* **Databases high-level concepts**
* **Relational model**
**[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/)
* **SQL schema design patterns and anti-patterns**
* **SQL queries patterns and anti-patterns**
* **Indexes, and their use cases**
**[Automate Routine](https://infinite.education/view/automate_routine)**
* **Automation using shell scripts or any other favourite language**
* **Configuration management**
**[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**
* **Fine-grained tuning**
* **Advanced SQL topics**
**[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)
* **Data import and export**
* **Bulk Loading and Processing**
* **Queues**
* **Migrations**
* **Data partitioning and sharding patterns**
* **Database normalization and normal forms**
**[Develop architect skills](https://infinite.education/view/develop_architect_skills)**
* **RDBMS**
* **Postgres forks and extensions**
* **Differences between Postgres and other RDBMS and NoSQL databases**
**[Infrastructure DBA skills](https://infinite.education/view/infrastructure_dba_skills)**
* **Applications Load Balancing and Service Discovery**
* **Connection pooling**
* **High availability and cluster management tools**
* **Backup & Recovery Tools**
* **Kubernetes**
 -- `Video` [Curso 2021 KUBERNETES en español PARA PRINCIPIANTES; TODO LO QUE NECESITAS PARA DOMINAR - YouTube](https://www.youtube.com/watch?v=r4lk4LSKmkY&list=PLdOotbFwzDIhRnAAhOkdwnBM9MNkaj0yi)
* **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)
* **Upgrading procedures**
* **Resource usage and provisioning, capacity planning**
**[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)
* **SSL settings**
* **Objects privileges**
* **Advanced Topics**
* **Authentication models**
* **roles**
* **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)
**[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)
* **Log analyzing**
* **Postgres tools**
* **Query analyzing**
* **Operating system tools**
* **Postgres system views**
* **Troubleshooting methods**
* **External tracing/profiling tools**
**[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/)
* **Reviewing patches**
* **Writing patches, attending in Commitfests**
 -- `Article` [Commitfests](https://commitfest.postgresql.org/)

Must-have Nice to have Optional

Recommended option

PostgreSQL DBA. Instruction on how to learn PostgreSQL DBA

Roadmap

by Artem Naumenko

Courses

Add