replication issue

So, I am migrating from a single Postgres DB node to a clustered setup.

I want to use BDR and run a multi-master cluster, so I have that configured between my 3 DB nodes.

I restored the DB backup from the single DB node on one of the BDR-connected nodes, and saw that it replicated the schema and tables ok, but I'm finding some weird behavior once I begin to use the cluster.

I have a user who is trying to log into the application that is using this Postgres DB as a backend, but he is unable to. Looking at the logs, it appears that when Postgres is trying to place his id in a table called auth_user, it is complaining that the key already exists. Looking at a table called auth_user_id_seq, I see that the information is different on each of the BDR nodes, and that makes me wonder why, since it should match up with what I restored.

This is what it looks like:

node01:
awx=# SELECT * FROM auth_user_id_seq;
sequence_name | last_value | start_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called | amdata
------------------+------------+-------------+--------------+-------------------
--+-----------+-------------+---------+-----------+-----------+--------
auth_user_id_seq | 50 | 1 | 1 | 922337203685477580
7 | 1 | 1 | 0 | f | t |
(1 row)

awx=#

node02:
awx=# SELECT * FROM auth_user_id_seq;
sequence_name | last_value | start_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called | amdata
------------------+------------+-------------+--------------+-------------------
--+-----------+-------------+---------+-----------+-----------+--------
auth_user_id_seq | 17 | 1 | 1 | 922337203685477580
7 | 1 | 1 | 30 | f | t |
(1 row)

awx=#

node03:
awx=# SELECT * FROM auth_user_id_seq;
sequence_name | last_value | start_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called | amdata
------------------+------------+-------------+--------------+-------------------
--+-----------+-------------+---------+-----------+-----------+--------
auth_user_id_seq | 4 | 1 | 1 | 922337203685477580
7 | 1 | 1 | 31 | f | t |
(1 row)

awx=#

I have to think that I missed a step somewhere during the restore process, or configuring the replication, that would lead to this... My expectation would be that those three entries would be the same.

We are also having other duplicate-key-related issues in the app, like creating a new template.

Any help you could offer would be greatly appreciated.

Thanks.

Brad Keefer
Red Hat Consultant, ITS, RHCSA

Office of Information Technology Services
W A Harriman State Campus, Bldg 8, Floor 7
(919)210-7045 | Brad(dot)Keefer(at)its(dot)ny(dot)gov<mailto:Brad(dot)Keefer(at)its(dot)ny(dot)gov>
www.its.ny.gov<http://www.its.ny.gov/>