pg_resetwal (formerly known as pg_resetxlog) is a command provided by PostgreSQL which allows you to reset the WAL (Write Ahead Log) in case of trouble. It is a means of last resort to get a broken database server to start. However, what happens behind the scenes when this command is called? Is it dangerous? Required? […]

© Laurenz Albe 2020 Streaming replication in PostgreSQL is a well-established master-slave replication technique. It is simple to set up, stable and performs well. So many people are surprised when they learn about replication conflicts — after all, the standby server is read-only. This article describes replication conflicts and tells you how to deal with […]

© Laurenz Albe 2019 The biggest change in PostgreSQL v12 from the viewpoint of backward compatibility is that recovery.conf has been absorbed into postgresql.conf. This article describes the changes and how you can adapt to them. Getting rid of recovery.conf Up to now, the presence of the file recovery.conf was the trigger for PostgreSQL to […]

© Laurenz Albe 2019   “Durability”, the D of ACID, demands that a committed database transaction remains committed, no matter what. For normal outages like a power failure, this is guaranteed by the transaction log (WAL). However, if we want to guarantee durability even in the face of more catastrophic outages that destroy the WAL, […]

In my previous post about pgBackRest, we saw how to install and setup pgBackRest and make a backup of a PostgreSQL database with it. It was a very basic single server setup,  only intended to get the hang of the tool. Such setups are not used in a production environment, as it is not recommended […]

pgBackRest is an open source backup tool for PostgreSQL which offers easy configuration and reliable backups. So if you want to protect your database and create backups easily, pgBackRest is a good solution to make that happen. In this blog, we are going to go through the basic steps of using pgBackRest for full and […]

UPDATE 12.07.2022: The “exclusive backup” method of calling pg_start_backup(‘label’) before backup and pg_stop_backup() afterwards has been removed in PostgreSQL v15. This article describes the problems with the old method and discusses the options for those who still use this backup method. I include scripts below to help you. The “exclusive” backup method Before pg_basebackup was […]

BY Kaarel Moppel – Although Postgres is a great all-around product that can’t be beaten in price-performance ratio, there’s one area where things could be a bit better. That’s in the validation of replicas, or maybe even more widely, all clusters. What’s the problem? After building a new replica there’s actually no way to tell […]

pg_basebackup and backup speed: In the past couple of years, replication has been made easier and easier. Setting up streaming replication is pretty easy these days. When I train people I get a lot of positive feedback. People seem to like the way entire database instances can be cloned. In many cases, DBAs use a […]