2

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?

1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.