Status of PostgreSQL replication after pause and resume To backup the PostgreSQL database we setup a slave server which uses streaming replication.

We found the commands pg_xlog_replay_pause() and pg_xlog_replay_resume(). So we thought we use the commands to pause the replication (the replaying of the xlogs), start pg_dump and then resume the replication.

How do I find out when the PostgreSQL database is again in sync after the "resume"? Usually I used this command to see the "lag":

SELECT extract(seconds from (now() - pg_last_xact_replay_timestamp())) AS time_lag;


If I use it after pg_xlog_replay_pause() then it shows only a "lag" in seconds. So it seems that it is not the right query here. So is there a good solution for this?

One solution would be to write a cronjob on the master server to update a timestamp every minute and then check on the slave when the timestamp is more or less up to date. But I think there should be a better solution from PostgreSQL itself.
Unless I misunderstood your question, SELECT extract(epoch from (now() - pg_last_xact_replay_timestamp())) AS time_lag; should give you what you are looking for - the lag in seconds, not seconds portion of the lag as your original query returns.  You can achieve the same with a number of extract (seconds ...) + extract(minutes ...)*60 + extract (hours ...) *60*24 +...