Hi,
On 2015-03-03 16:49:22 +0100, Andres Freund wrote:
> I every now and then run installcheck against a primary, verify that
> replay works without errors, and then compare pg_dumpall from both
> clusters. Unfortunately that currently requires hand inspection of
> dumps, there are differences like:
> -SELECT pg_catalog.setval('default_seq', 1, true);
> +SELECT pg_catalog.setval('default_seq', 33, true);
>
> The reason these differences is that the primary increases the
> sequence's last_value by 1, but temporarily sets it to +SEQ_LOG_VALS
> before XLogInsert(). So the two differ.
>
> Does anybody have a good idea how to get rid of that difference? One way
> to do that would be to log the value the standby is sure to have - but
> that's not entirely trivial.
I found a way that's actually fairly simple. On the primary call nextval
often enough to use up all the cached values. The below query does so:
DO $$
DECLARE
s regclass;
BEGIN
FOR s IN SELECT oid::regclass FROM pg_class WHERE relkind = 'S' LOOP
EXECUTE format($s$SELECT nextval(%s), generate_series(1, log_cnt) FROM %s;$s$, s::oid, s::text);
END LOOP;
END;$$;
After that dumps on master generate the same dump on primary / standby
for me, after running a regression test.
Greetings,
Andres Freund