Skillset: PostgreSQL DBA

Skillset

Author of 32 skillsets

PostgreSQL DBA

Subject: Computing / Database knowledge / Database administration

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)
* **Package managers**
* **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)
* **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/)
* **Understand basic data types**
* **COPY**
 -- `Article` [Import CSV File Into PosgreSQL Table](https://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/)
* **DDL queries**
* **DML 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)
* **Relational model**
* **Databases high-level concepts**
* **Object 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/)
* **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)**
* **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)
* **Advanced SQL topics**
* **Low level internals**
* **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)
* **Data partitioning and sharding patterns**
* **Bulk Loading and Processing**
* **Migrations**
* **Data import and export**
* **Queues**
* **Database normalization and normal forms**
**[Develop architect skills](https://infinite.education/view/develop_architect_skills)**
* **Postgres forks and extensions**
* **RDBMS**
* **Differences between Postgres and other RDBMS and NoSQL databases**
**[Infrastructure DBA skills](https://infinite.education/view/infrastructure_dba_skills)**
* **High availability and cluster management tools**
* **Resource usage and provisioning, capacity planning**
* **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)
* **Replication**
 -- `Article` [Managing PostgreSQL Replication and PostgreSQL Automatic Failover for High Availability](https://www.enterprisedb.com/postgres-tutorials/postgresql-replication-and-automatic-failover-tutorial)
* **Connection pooling**
* **Upgrading procedures**
* **Infrastructure monitoring**
* **Applications Load Balancing and Service Discovery**
* **Backup & Recovery Tools**
**[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)
* **Authentication models**
* **Objects privileges**
* **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)
* **SSL settings**
* **Advanced Topics**
**[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)
* **External tracing/profiling tools**
* **Troubleshooting methods**
* **Query analyzing**
* **Log analyzing**
* **Operating system tools**
* **Postgres tools**
* **Postgres system views**
**[Develop Postgres hacker skills](https://infinite.education/view/develop_postgres_hacker_skills)**
* **Writing patches, attending in Commitfests**
 -- `Article` [Commitfests](https://commitfest.postgresql.org/)
* **Daily reading and answering in mailing lists**
 -- `Article` [PostgreSQL: PostgreSQL Mailing List Archives](https://www.postgresql.org/list/)
* **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