Database Reference
In-Depth Information
You can then compare the results to understand the replication delay. You can calculate
the apply delay by comparing the preceding value from Master with the following:
SELECT
pg_last_xlog_apply_location();
The comparison can be a little fiddly, as you must use hex arithmetic to convert the two parts
of the file name. You can do this in Perl, Python, Java, C, and so on, or if you are brave you can
have a go at this in SQL, using a function to do the hex-to-decimal conversion as follows:
CREATE OR REPLACE FUNCTION hex2dec(text)
RETURNS bigint LANGUAGE SQL AS
$$
SELECT sum(digit * 16 ^ (length($1)-pos)) ::bigint
FROM (SELECT case
when digit between '0' and '9' then ascii(digit) - 48
when digit between 'A' and 'F' then ascii(digit) - 55
end,
pos as i
FROM (SELECT substring(c from x for 1), x
FROM (values(upper($1))) as a(c),
generate_series(1,length($1)) as t(x))
as u(digit, pos)
) as v(digit, pos);
$$;
Switchover and Failover
Switchover is a controlled switch from Master to Standby. If performed correctly, there will be
no data loss. To be safe, simply shutdown the Master node cleanly, using either smart or fast
shutdown modes. Do not use immediate mode shutdown, because you will almost certainly
lose data that way.
Failover is a forced switch from the Master node to a Standby because of the loss of the Master.
So in that case, there is no action to perform on the Master; we presume it is not there anymore.
Next, we need to promote one of the Standby nodes to be the new Master. A Standby node
can be triggered into becoming a Master node by creating the trigger file specified in the
parameter
trigger_file
. For example:
touch /tmp/postgresql.trigger.5432
The trigger file will be deleted again when the transition is complete. Note that the Standby will
only become the Master once it has fully caught up. If you haven't been monitoring replication,
this could take some time.
To move from a Standby to a Master, the database performs an immediate checkpoint, which
may take some time on database servers with large caches and high rate of changes being
replicated from the Master.
