Dealing with timelines

Timelines are an important concept you have to be aware of, especially when you are planning a large-scale setup.

So what is a timeline? In fact, it is a certain branch of the XLOG. Normally, a database instance that has been freshly set up utilizes timeline number 1. Let's assume that we are starting to replicate our master database to a slave system. The slave will also operate in timeline 1. At some point, your master might die and your slave will be promoted to a new master. This is the time when a timeline switch happens. The new master will create a transaction log of its own now. Logically, we want to make sure that its XLOG is not mixed with some other XLOG made in the good old times.

How can we figure out that the timeline has advanced? Let's take a look at the XLOG directory of a system that was just turned into a master:

00000002.history
000000020000000000000006
000000020000000000000007
000000020000000000000008

The first part of the XLOG files is an interesting thing. You can observe that so far, there was always a 1 near the beginning of our filename. This is not so anymore. By checking the first part of the XLOG filename, you can see that the number has changed over time (after turning the slave into a master, we have reached timeline number 2).

It is important to mention that (as of PostgreSQL 9.4) you cannot simply pump the XLOG of timeline 5 into a database instance that is already at timeline 9. It is simply not possible.

In PostgreSQL 9.3, we are able to handle these timelines a little more flexibly. This means that timeline changes will be put to the transaction log, and a slave can follow a timeline shift easily.

Tip

Timelines are especially something to be aware of when cascading replication and working with many slaves. After all, you have to connect your slaves to some server if your master fails.