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