Re: [HACKERS] Comparing primary/HS standby in tests

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