From: Keefer, Brad (ITS) <Brad.Keefer@its.ny.gov>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: replication issue
Date: Wed, 4 Oct 2017 15:28:20 +0000
Message-ID: <MWHPR09MB12314F708EFB5EE9268212AFDC730@MWHPR09MB1231.namprd09.prod.outlook.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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.Keefer@its.ny.gov<mailto:Brad.Keefer@its.ny.gov>
www.its.ny.gov<http://www.its.ny.gov/;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Brad.Keefer@its.ny.gov
Subject: Re: replication issue
In-Reply-To: <MWHPR09MB12314F708EFB5EE9268212AFDC730@MWHPR09MB1231.namprd09.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox