Percona Live Online

TRICKS OF THE TRADE; A COLLECTION OF TECHNIQUES ADRESSING COMMON ADMINISTRATION MISTEPS AND MISTAKES

PostgreSQL is not only the most sophisticated open source database management system in world it's also among the most reliable and easy to setup. But even under the best of circumstances there are situations where things can just plain go wrong by making a wrong assumption. This purpose of this talk is to review the most common missteps and mistakes administrating a Postgres data cluster and how to prevent them from escalating into production-level issues.

For the purposes of this presentation, we will not cover query tuning per se.

We'll first start with the most common issues and gradually review some of the more esoteric challenges a DBA can encounter.

Here's a breakdown of the topics that will be covered:

- Host Based Authentication Rules
- rules that are never reached
- METHOD mangling
- appreciating "peer"
- too much "trust"
- the much maligned "reject"
- about password hashing: password vs md5 vs scram-sha-256
- SSL laxity i.e. host vs hostssl
- Over using the superuser
- SSL
- Certificates
- CA signed vs self-signed
- life span: too long vs too short
- About Ciphers: weak (peformance) vs strong (security)
- Replication
- postgres logging
- where to put it
- too much vs too little
- log rotation
- Over Allocation Of System Resources
- swap vs noswap
- Linux's OOM Process Killer
- some runtime parameters of interest
- max_connections
- effective_cache_size
- work_mem
- maintenance_work_mem
- Good autovacuuming hygiene