Postgres-BDR: 2 Years in Production
Postgres-BDR has now reached 1.0 production status.
Over the last 2 years, Postgres-BDR has been used daily for mission critical production systems.
As you might imagine, it’s been improved by both bug fixes and feature enhancements that allow it to be used smoothly, so its mature, robust and feature-rich.
The BDR Project introduced logical replication for PostgreSQL, now available as pglogical. In addition, it introduced replication slots, background workers and many other features. But is it still relevant?
Postgres-BDR delivers all of these features that aren’t yet in PostgreSQL 9.6, and likely won’t all be in PostgreSQL 10.0 either
- Automatic replication of DDL, reducing maintenance costs for DBAs
- Automatic replication of sequences
- Conflict resolution and logging
Great Product. A issue that we have encountered but don’t seem to have a solution for is where the is a table with a secondary key and lag. This produces a conflict that brings down the BDR. I can replicate by issuing the following on a 2 way group.
CREATE TABLE billing2 (
billingid int8 NOT NULL DEFAULT nextval(‘billing_seq’::regclass),
requestid int8 NOT NULL,
billingindex int8 NOT NULL,
billingtype int8 NOT NULL,
billingtypeother varchar(100) NULL,
CONSTRAINT billing2_pkey PRIMARY KEY (billingid)
)
WITH (
OIDS=FALSE
);
CREATE UNIQUE INDEX billing_requestid_billingtype_billingtypeother_key2 ON wsill_ov_s.billing2 (requestid,billingtype,(COALESCE(billingtypeother, ‘Empty’::character varying)));
I then wrote some linux commands to manually insert.
for x in `seq 5 900000`;do psql -p5608 testdb -c”insert into billing2 values (nextval(‘billing_seq’),${x},1,1,null)”;done
To make sure I get lag I run a pgbench
pgbench -h postgresdb03dxdu.dev -U postgres -T 600 -j 20 -s 10 -c 60 testdb -p5608
Once the system gets behind on the first conflict the system never catches up, when trying to fix the records one by one. I was hoping that the new version would have a way to deal with this issue, but seems to still be a problem. Any help would greatly be appreciated.