Re: BDR Global Sequence (converted from normal sequence)

I guess I'm going to answer my own question!

So, it turns out that simply converting the sequences to a bdr global sequence by running alter sequence....does not actually set the starting value on all of the cluster instances. What I ended up doing that does seem to work is as follows.

1. Get the current value of each sequence

2. Drop the current sequence

3. Create the same sequence using bdr with the start with clause set to step 1 above.

Here is the SQL that I ran to accomplish in case anybody else wants/needs to do this.

DO $$
DECLARE
seqs RECORD;
seq_val integer;
BEGIN

FOR seqs IN SELECT relname FROM pg_class INNER JOIN pg_seqam ON (pg_class.relam = pg_seqam.oid) WHERE pg_seqam.seqamname != 'bdr' AND relkind = 'S' and relname not like 'bdr%' LOOP
execute 'select last_value from ' || seqs.relname into seq_val;
RAISE NOTICE 'Found Sequence %s with current value %', quote_ident(seqs.relname), seq_val;
raise notice 'Converting sequence % to global sequence', seqs.relname;
raise notice 'Dropping non-bdr sequence %', seqs.relname;
execute 'drop sequence ' || quote_ident(seqs.relname);
raise notice 'Re-creating sequence % starting at % using bdr', seqs.relname, seq_val;
execute 'create sequence ' || quote_ident(seqs.relname) || ' start with ' || seq_val || ' using bdr';
raise notice ' ';
END LOOP;
END$$;

From: pgsql-cluster-hackers-owner(at)postgresql(dot)org [mailto:pgsql-cluster-hackers-owner(at)postgresql(dot)org] On Behalf Of Cliff De Carlo
Sent: Wednesday, September 30, 2015 5:19 PM
To: pgsql-cluster-hackers(at)postgresql(dot)org
Subject: [pgsql-cluster-hackers] BDR Global Sequence (converted from normal sequence)

We have a third party application that uses a Postgres database. I have been trying to setup this application for high availability. The product itself supports clustering with a load-balancer but all server nodes still need to point to the same instance of the database. What I've been trying to do is have each node connect to a Postgres database running locally on the same box as the server process (this will isolate any kind of networking issues preventing connections to the database). This application uses sequences for a lot (but not all) of its tables PK column values. I read the documentation about BDR global sequences and this sounds like exactly what I need.

The way I have been approaching this is the following.

1. Take a full backup of the non-BDR enabled database (pg_dumpall.....)

2. Restore this backup to my BDR-enabled version of Postgres (I do not have default_sequenceam = bdr set at this point).

3. Change all of the sequences to be BDR global sequences (I used the following SQL statement).

DO $$

DECLARE

seqs RECORD;

BEGIN

FOR seqs IN SELECT c.relname FROM pg_class c WHERE c.relkind = 'S' and c.relname not like 'bdr%' LOOP

RAISE NOTICE 'Altering Sequence %s ...', quote_ident(seqs.relname);

execute 'alter sequence ' || quote_ident(seqs.relname) || ' using bdr';

END LOOP;

END$$;

4. Setup the replication group (bdr.bdr_group_create() etc)

5. Use bdr_init_copy to replicate this to two other nodes.

Looking through the backup file it seems the way the sequences are created is as follows.

CREATE SEQUENCE foo;
SELECT pg_catalog.setval('foo', 5000, true);

Once I do this I can see that all three nodes are seeing the sequences as BDR global type sequences (using the SQL statement from the BDR documentation). I can also confirm that my sequences have the correct starting value.

However, when I startup the application software, the value of the sequences on that Postgres node seem to get reset. So say they all are starting at 5000, as soon as the server starts up and connects to the database and asks for the nextval of the sequence it will reset to 1 and now I'm getting duplicate PK errors.

I guess what I'm asking is can I convert a normal sequence to a BDR global sequence preserving the current value?

Thanks,

Cliff DeCarlo