PostgreSQL Failover – Using pg_rewind to Show Lost Data

failoverpostgresqlrecoveryreplication

Say I have Postgres setup with a warm standby using the build in replication features. Now, imagine a scenario where the data centre the master loses power and the slave is in another data centre and I promote it to a master. Later, the original master comes back up. At this point, I could use pg_rewind to rewind the original master into to a state where it can follow the new master. Is there any way I can see what changes pg_rewind has has undone so I could, for example, manually fix up the data by rerunning inserts against the new master if it was important?

Best Answer

If you had made a backup of the old master's pg_xlog content that was not applied on the new master after the point of divergence, as they call it, you can use pg_xlogdump (included in the PG distribution since 9.3) to examine the contents of transaction logs on the old master:

$ pg_xlogdump -p <pg_xlog_dir> <segment_file_name>

Unfortunately, WAL only contain the changes to the physical pages in the database cluster, so it is currently impossible to extract full SQL statements, but at least this should give you an idea of whether the number of changes made were significant and whether they hit any relevant relations.

You can use the output's xid field as an indication of how many transactions have been applied to the old master since the split, and the rel field's last number will tell you which relation a change has been applied to (look at pg_class.relfilenode to figure out what exact relation that would be):

SELECT  c.relname AS relname,
        n.nspname AS schema,
        c.relkind AS reltype,
        t.spcname AS tablespace
    FROM pg_class c
        LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
        LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
    WHERE c.relfilenode = '<number>';

In short, it is not going to be an easy task, but it is possible to get at least some information out of it.

Another, much easier way, would be to turn on statement logging, but that will probably cost you quite a bit unless you're storing logs on a separate device so that it doesn't affect server operation too much (log_min_duration_statement = 0).

Related Solutions

Automating Failover in PostgreSQL 9.1 – Best Practices

Check out repmrg:

repmgr is a set of open source tools that helps DBAs and System administrators manage a cluster of PostgreSQL databases..

By taking advantage of the Hot Standby capability introduced in PostgreSQL 9, repmgr greatly simplifies the process of setting up and managing database with high availability and scalability requirements.

repmgr simplifies administration and daily management, enhances productivity and reduces the overall costs of a PostgreSQL cluster by:

monitoring the replication process; allowing DBAs to issue high

availability operations such as switch-overs and fail-overs.

It does two things:

repmgr: command program that performs tasks on your cluster and then exits
repmgrd: management and monitoring daemon that watches the cluster and can automate remote actions.

For automatic failover, repmgrd does the trick and is not a SPOF in your network, like pgPool. However, it is still important to monitor all deamons and bring them back up after failure.

Version 2.0 is about to be released, including RPM's.

MySQL Replication – How to Fetch Entire Database on Start

If you have three or more Slaves, I have a nice suggestion: Use one of the Slaves as a Volunteer to be cloned.

Here is a Topology

MAS --+--> SLV1
      |
      +--> SLV2
      |
      +--> SLV0

Say you want to Spawn SLV3. You could use SLV0 as a Clone

STEP01) Install MySQL Binaries on SLV3 (Same version as all other Slaves)
STEP02) On SLV0, STOP SLAVE;
STEP03) On SLV0, service mysql stop
STEP04) scp -r SLV0:/etc/my.cnf SLV3:/etc/my.cnf
STEP05) scp -r SLV0:/var/lib/mysql SLV3:/var/lib/mysql
STEP06) On SLV0, service mysql start
STEP07) On SLV3, chown -R mysql:mysql /var/lib/mysql
STEP08) On SLV3, change server_id in /etc/my.cnf to be unique from all other Slaves
STEP09) On SLV3, service mysql start

That's it.

This is the same paradigm followed by Percona XtraDB Cluster (PXC). When it comes to PXC, introducing a New Slave in PXC is as simple as adding the MasterIP to my.cnf and starting MySQL. All of the above steps are executed internally by PXC using Quorom Selection to choose which Slave becomes the Donor (a.k.a. Volunteer to be Cloned) as well as one of three methods for copying data (xtrabackup, rsync, mysqldump) This copying method is known as SST (State Snapshot Transfer).

If all of the Application-Level Data are stored in InnoDB only, you should look into using PXC. If you have a mix of InnoDB/MyISAM or all MyISAM, the above 9 steps are to be scripted by you.

Best Answer

Related Solutions

Automating Failover in PostgreSQL 9.1 – Best Practices

MySQL Replication – How to Fetch Entire Database on Start

Related Question