I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased
with the experience. This is promising stuff. Perhaps it is a bit too soon to
ask questions here but here it is:
1. Speed of recovery
With a archive_timeout of 60 seconds, it can take about 4 minutes before I see
the reflected changes in the replica. This is normal since, in addition to
the WAL log shipping, it takes more time to do the recovery itself. Still, is
there any way besides the archive_timeout config option to speed up the
recovery of WAL logs on the hot standby?
2. last modified since timestamp:
Is there a way to get the "last modified since" timestamp on the hot standby
replica? Since the replication is asynchronous, it is necessary to know how
up to date the replication is. In our case, the timestamp is used this way:
select * from resource where not_modified_since >=
to_timestamp('$not_modified_since', 'YYYY/MM/DD HH:MI:SS');
The $not_modified_since is set to now() for the next time this query will be
run on the master database. This way the application keeps a cache and it is
not necessary to fetch everything every time. With an asynchronous replica
however, "now()" cannot be used and so I am looking into other possibilities
to get an accurate "last modified since" on the hot standby itself. I
tried "select pg_last_recovered_xact_timestamp();"
(http://wiki.postgresql.org/wiki/Hot_Standby) but this is not 100% accurate.
It looks like it has more to do with recovery transactions then the original
database itself.
So the question is: is there any clean way to get the last_modified_since
without making modifications to the schema?
