Category: Thread

Postgres is equipped in three tools designated to handle logical backups: pg_dump, pg_dumpall and pg_restore. They offer rich customization for text and binary backups that can copy selected database objects. Dumping and restoring database can be performed by parallel workers to decrease operation time. The COPY and the native Postgres \copy commands allow integrating backup…

Backup & Restoration – Postgres supports two types of backups – logical and physical. Logical backup copies table by table as if any client performing series of SELECT statements. It is a reliable and simple method that works across major versions. However it takes more time and puts additional cpu and memory strain on the…

I completed the chapter about logging and auditing. Main takeaways are: – Postgres logs information about it’s actions. Quantity, frequency, destinaiton and content of these logs is highly configurable. – There are two types of logging: syslog for logging to third party systems. This method can drop logs under heavy traffic. logging collector it’s the…

I finished the query tuning chapter. Toady I read about query optimization and auto explain . Main takeaways are:– Usually query optimization boils down to leveraging an appropriate index.– Avoid subqueries since they will likely cause redundant looping.– Keep an eye on unused indexes (pg_stat_user_indexes) since they take up space and effort when inserting new…

I completed the section about indexes and started reading about EXPLAIN and ANALYZE statements. Several recent chapter were theory heavy and required a lot of note taking – this slowed down my progress. I will probably skip notes taking in next chapters – Audit Logging and Configuration … or at least restrain notes to crucial…