Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dzld4-0005CE-44 for pgsql-admin@arkaria.postgresql.org; Wed, 04 Oct 2017 15:30:34 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dzld3-00021e-03 for pgsql-admin@arkaria.postgresql.org; Wed, 04 Oct 2017 15:30:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dzlbB-0007Cn-5h for pgsql-admin@postgresql.org; Wed, 04 Oct 2017 15:28:37 +0000 Received: from mail-dm2gcc01on0073.outbound.protection.outlook.com ([23.103.201.73] helo=gcc01-dm2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dzlax-0006MZ-Oz for pgsql-admin@postgresql.org; Wed, 04 Oct 2017 15:28:36 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=its.ny.gov; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=JokUuEVpT0H9o5oBhFEnZhHi2/HtMJ+COGjN9X1CAhA=; b=aZRe0YuaEC+m1Iv64hqEJPbxvzlJtaDyOqqBI61W59a8vrdfX+WSj696uNGo8p/Tj3i0psoyihdenJCCLucuROXD8bLZ70N8fa46qhEaj3qXK3dvKCwLK7H3NqBjiSA7jBxKAyS44b25Bgu/nhXokRe35jqkAeOXtu5bthnI2PQ= Received: from MWHPR09MB1231.namprd09.prod.outlook.com (10.172.50.9) by MWHPR09MB1231.namprd09.prod.outlook.com (10.172.50.9) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P256) id 15.20.77.7; Wed, 4 Oct 2017 15:28:20 +0000 Received: from MWHPR09MB1231.namprd09.prod.outlook.com ([10.172.50.9]) by MWHPR09MB1231.namprd09.prod.outlook.com ([10.172.50.9]) with mapi id 15.20.0077.018; Wed, 4 Oct 2017 15:28:20 +0000 From: "Keefer, Brad (ITS)" To: "pgsql-admin@postgresql.org" Subject: replication issue Thread-Topic: replication issue Thread-Index: AdM9JDqv9jJ2n+nITR+blMYj1U9EEQ== Date: Wed, 4 Oct 2017 15:28:20 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=Brad.Keefer@its.ny.gov; x-originating-ip: [161.11.225.45] x-ms-publictraffictype: Email x-microsoft-exchange-diagnostics: 1;MWHPR09MB1231;6:HHqEsqVYR11SHUx40dCbmoprrkZGVIrPx36CPE0kbcUX2ltueLwFNn7Xvg4Xyiz09aDhg/IL6TYctvt2sOfy4LIqPozh8SP2PDuhWAmWYqlsi3bx/AcpsBkLqKsU764Irrh9AGUty0UbnxU25wgtvzfpR+raXgSKIWJZDs/I8MIRMmUlpsSk3yrZ+6jPPhC4LLUefPVB2P+3wxibKdT1+ppzNMrFBJSGk1c1RHzWydfpx/TC3L/G8IngibD6g5nqujbINsMdR2DuKQxOB6sPEyOdCmIlCJOTDROIgFZZtI4YuOpwNFUM1kxL4U10+0WH6Mdvc/fGWacFCDUouzMpSQ==;5:CslHR/soFeBDDsOcUQWlgl+sD+qKSSuINk/M/MYbCnQYJaQqyrNcdqvyzMvhUQpxrmZA4ZeYVPcK8HK8SpN0pZAMEBYHvWRfCscUzwMG7O5MzEOClD6stGukh0dR4/MvW39s/vsoASVQsBz71/iYPg==;24:aulsbzDdEsydBBOfXUjs+3Co0DtrZ+4y0kxrPLM0l3ocEl69JqNJ3ChHEvtb2Ugg4jHlCho+SvuO9zvWy/4M9Ru22FsCg5iHKemeyPcum9Q=;7:MdfSAes/UMWoVIu7v6gdS/0R51wZjoAMlFwZMoPkmGQ+UXzkFcahBZ+kZNHJm2oXxjNPKrDfim486dEhbkn38A05wq7sdfN1+yP69MsDPuUoFk90+BzuMm0Tpvja8Bw69nDq2d8Kt8SFKEQbacmwFb0JE1MPJ21fmCEYvNpo9G0pAVlpI0ABtaqTm8k4+W1PmekyX5nKXnHKYAV5CDp+k+JmABp2pSOei0kvos0AcZE= x-ms-exchange-antispam-srfa-diagnostics: SSOS; x-ms-office365-filtering-correlation-id: acbb19a8-d3e1-401d-fb35-08d50b3c8b32 x-ms-office365-filtering-ht: Tenant x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:(22001)(2017030254152)(48565401081)(2017052603199)(201703131423075)(201703031133081)(201702281549075);SRVR:MWHPR09MB1231; x-ms-traffictypediagnostic: MWHPR09MB1231: nysemail_rule: IP Whitelist for Partners x-exchange-antispam-report-test: UriScan:(208664346725987)(21748063052155)(209854054982694); x-microsoft-antispam-prvs: x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(100000700101)(100105000095)(100000701101)(100105300095)(100000702101)(100105100095)(6040450)(2401047)(8121501046)(5005006)(100000703101)(100105400095)(93006095)(93001095)(3002001)(10201501046)(6055026)(6041248)(20161123560025)(20161123555025)(201703131423075)(201702281529075)(201702281528075)(201703061421075)(201703061406153)(20161123564025)(20161123558100)(20161123562025)(6072148)(201708071742011)(100000704101)(100105200095)(100000705101)(100105500095);SRVR:MWHPR09MB1231;BCL:0;PCL:0;RULEID:(100000800101)(100110000095)(100000801101)(100110300095)(100000802101)(100110100095)(100000803101)(100110400095)(100000804101)(100110200095)(100000805101)(100110500095);SRVR:MWHPR09MB1231; x-forefront-prvs: 0450A714CB x-forefront-antispam-report: SFV:NSPM;SFS:(10009020)(346002)(39860400002)(376002)(189002)(199003)(74316002)(7736002)(54896002)(5660300001)(478600001)(25786009)(77096006)(7116003)(81166006)(6306002)(221733001)(53936002)(81156014)(55016002)(105586002)(2906002)(86362001)(8936002)(99286003)(106356001)(8676002)(189998001)(102836003)(6116002)(2351001)(3846002)(3480700004)(97736004)(101416001)(3280700002)(606006)(790700001)(5630700001)(9686003)(66066001)(33656002)(68736007)(3660700001)(2501003)(54356999)(6436002)(7696004)(6506006)(5640700003)(50986999)(786003)(6916009)(316002)(42882006)(2900100001)(72206003)(236005)(14454004);DIR:OUT;SFP:1101;SCL:1;SRVR:MWHPR09MB1231;H:MWHPR09MB1231.namprd09.prod.outlook.com;FPR:;SPF:None;PTR:InfoNoRecords;MX:1;A:1;LANG:en; received-spf: None (protection.outlook.com: its.ny.gov does not designate permitted sender hosts) spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_MWHPR09MB12314F708EFB5EE9268212AFDC730MWHPR09MB1231namp_" MIME-Version: 1.0 X-OriginatorOrg: its.ny.gov X-MS-Exchange-CrossTenant-originalarrivaltime: 04 Oct 2017 15:28:20.2704 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: f46cb8ea-7900-4d10-8ceb-80e8c1c81ee7 X-MS-Exchange-Transport-CrossTenantHeadersStamped: MWHPR09MB1231 List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_000_MWHPR09MB12314F708EFB5EE9268212AFDC730MWHPR09MB1231namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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-connecte= d nodes, and saw that it replicated the schema and tables ok, but I'm findi= ng 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 appe= ars that when Postgres is trying to place his id in a table called auth_use= r, 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 B= DR nodes, and that makes me wonder why, since it should match up with what = I restored. This is what it looks like: node01: awx=3D# SELECT * FROM auth_user_id_seq; sequence_name | last_value | start_value | increment_by | max_valu= e | min_value | cache_value | log_cnt | is_cycled | is_called | amdata ------------------+------------+-------------+--------------+--------------= ----- --+-----------+-------------+---------+-----------+-----------+-------- auth_user_id_seq | 50 | 1 | 1 | 92233720368547= 7580 7 | 1 | 1 | 0 | f | t | (1 row) awx=3D# node02: awx=3D# SELECT * FROM auth_user_id_seq; sequence_name | last_value | start_value | increment_by | max_valu= e | min_value | cache_value | log_cnt | is_cycled | is_called | amdata ------------------+------------+-------------+--------------+--------------= ----- --+-----------+-------------+---------+-----------+-----------+-------- auth_user_id_seq | 17 | 1 | 1 | 92233720368547= 7580 7 | 1 | 1 | 30 | f | t | (1 row) awx=3D# node03: awx=3D# SELECT * FROM auth_user_id_seq; sequence_name | last_value | start_value | increment_by | max_valu= e | min_value | cache_value | log_cnt | is_cycled | is_called | amdata ------------------+------------+-------------+--------------+--------------= ----- --+-----------+-------------+---------+-----------+-----------+-------- auth_user_id_seq | 4 | 1 | 1 | 92233720368547= 7580 7 | 1 | 1 | 31 | f | t | (1 row) awx=3D# 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 w= ould be that those three entries would be the same. We are also having other duplicate-key-related issues in the app, like crea= ting 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 www.its.ny.gov --_000_MWHPR09MB12314F708EFB5EE9268212AFDC730MWHPR09MB1231namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 t= ables 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 applicat= ion that is using this Postgres DB as a backend, but he is unable to. Looki= ng 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 m= akes me wonder why, since it should match up with what I restored.

 

This is what it looks like:

node01:

awx=3D# SELECT * FROM auth_user_id_seq;

  sequence_name   | last_value | star= t_value | increment_by |      max_value=

  | min_value | cache_value | log_cnt | is_cycl= ed | is_called | amdata

------------------+------------+------------= -+--------------+-------------------

--+-----------+-------------+---------&#= 43;-----------+-----------+--------

auth_user_id_seq |     &nbs= p;   50 |         &n= bsp; 1 |            = 1 | 922337203685477580

7 |         = 1 |           1 | &n= bsp;     0 | f       = ;  | t         |

(1 row)

 

awx=3D#

 

 

node02:

awx=3D# SELECT * FROM auth_user_id_seq;

  sequence_name   | last_value | star= t_value | increment_by |      max_value=

  | min_value | cache_value | log_cnt | is_cycl= ed | is_called | amdata

------------------+------------+------------= -+--------------+-------------------

--+-----------+-------------+---------&#= 43;-----------+-----------+--------

auth_user_id_seq |     &nbs= p;   17 |         &n= bsp; 1 |            = 1 | 922337203685477580

7 |         = 1 |           1 | &n= bsp;    30 | f       &nbs= p; | t         |

(1 row)

 

awx=3D#

 

 

node03:

awx=3D# SELECT * FROM auth_user_id_seq;

  sequence_name   | last_value | star= t_value | increment_by |      max_value=

  | min_value | cache_value | log_cnt | is_cycl= ed | is_called | amdata

------------------+------------+------------= -+--------------+-------------------

--+-----------+-------------+---------&#= 43;-----------+-----------+--------

auth_user_id_seq |     &nbs= p;    4 |        &nb= sp;  1 |          &n= bsp; 1 | 922337203685477580

7 |         = 1 |           1 | &n= bsp;    31 | f       &nbs= p; | t         |

(1 row)

 

awx=3D#

 

I have to think that I missed a step somewhere durin= g the restore process, or configuring the replication, that would lead to t= his… My expectation would be that those three entries would be the sa= me.

 

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

 

Any help you could offer would be greatly appreciate= d.

 

Thanks.

 

Brad K= eefer

Red Hat C= onsultant, ITS, RHCSA

&nbs= p;

Office= of Information Technology Services

W A Harri= man State Campus, Bldg 8, Floor 7

(919)210-= 7045 | Bra= d.Keefer@its.ny.gov

www.its.ny.gov

 

--_000_MWHPR09MB12314F708EFB5EE9268212AFDC730MWHPR09MB1231namp_--