Category: Learn PostgreSQL

Hooray! I finished reading “Learn PostgreSQL” book – a 700 page brick 😉 It took over a month of waking up at 6am reading, making notes in my notebooks and summaries on this blog. I was exposed to many new concepts and feature of Postgres that I was not familiar with before. Now I am…

I finished the logical replication chapter and started the last chapter of the book – useful extensions.Postgres provide replication monitoring utilities – it’s the same table that monitors physical replication – pg_stat_replication table. You can also get more information about publishing and subscribing processes by querying pg_publication and pg_subscribtion tables on primary and replica nodes…

Postgres logical replication relies on pub/sub mechanism. Primary node decodes WAL segments and extrapolates SQL queries from them, then it publishes the commands while replica node subscribes to it and passes received queries directly to query executor. Logical replication:– is a bit slower then physical replications– allows replication across Postgres versions– allows selective data replication…

The remaining part of the chapter about physical replication consisted mainly of a walk-through the replication setup steps. It is recommended to replicate clusters on machines with the same hardware parameters – this way replicas can reuse configuration from primary node without risk of running out of resources. Having multiple replicas can put too much…

Physical replication relies on the concept of streaming WAL segments from primary node to a replica node. The replica is in the state of continuous recovery – it continuously execute WAL segments received from primary node.In Synchronous replication primary node ensures replica received WAL segments before completion, while in asynchronous mode WAL segments are streamed…

I was mainly writing down notes about Postgres configuration. I have started reading about physical replication but I will include the notes about this chapter later.

I completed the configuration & monitoring chapter. Postgres allows configuration modifications while cluster runs by means of ALTER SYSTEM command. Modifications applied this way will be appended to postgresql.auto.conf file.You can use configuration generators like PgConfig with simple GUI interface.Information about running queries, sessions and backend processes can be read from pg_stat_activity catalog. Information about…

[Book “Learn PostgreSQL”] Postgres stores configuration across several files – posgresql.conf, postgressql.auto.conf and pg_hba.conf. You can extend settings with additional configuration files, this allows to organize settings by topic – use include_file, include_dir or include_if_exists directives. You can inspect settings using SHOW command or by querying pg_setting catalog.Each configuration belongs to a context which defines…

[Book “Learn PostgreSQL”] I completed the chapter about physical backups. They are performed by coping PGDATA, tablespaces and WALs to another machine or different directory. You can leverage a buid-in postgres command pg_basebackup or 3rd party tool pgBackRest. Physical backups require careful approach to configuration e.g. connections values and hba rules. Point in time recovery is…

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…