Tag: backup and recovery
pg_resetwal: When to reset the WAL in PostgreSQL
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? […]
Dealing with streaming replication conflicts in PostgreSQL
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 them. What is […]
recovery.conf is gone in PostgreSQL v12
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 go into recovery […]
Never lose a PostgreSQL transaction with pg_receivewal
“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, we need more […]
Remote Backup and Restore with pgBackRest
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 to backup PostgreSQL
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 […]
“Exclusive backup” method is deprecated – what now?
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 […]
Verifying PostgreSQL replicas
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: Creating self-sufficient backups
UPDATED September 2023: If you use transaction log shipping (or Point-In-Time-Recovery) in PostgreSQL to keep your data safe, you surely already know about pg_basebackup. The idea behind pg_basebackup is to allow users to create a binary copy of the data which can serve as the basis for Point-In-Time-Recovery. However, recently we saw some support cases […]
Managing backup speed with pg_basebackup
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 […]