pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedreplication issue
6+ messages / 4 participants
[nested] [flat]
* replication issue
@ 2017-10-04 15:28 Keefer, Brad (ITS) <Brad.Keefer@its.ny.gov>
0 siblings, 1 reply; 6+ messages in thread
From: Keefer, Brad (ITS) @ 2017-10-04 15:28 UTC (permalink / raw)
To: pgsql-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/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: replication issue
@ 2017-10-04 16:00 Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
parent: Keefer, Brad (ITS) <Brad.Keefer@its.ny.gov>
0 siblings, 0 replies; 6+ messages in thread
From: Alvaro Aguayo Garcia-Rada @ 2017-10-04 16:00 UTC (permalink / raw)
To: Keefer, Brad (ITS) <Brad.Keefer@its.ny.gov>; +Cc: pgsql-admin
Hi.
Just restoring your non-BDR backup into a BDR cluster will not avoid future problems with sequences.
At first glance, the problem you are having is that all your sequences are local sequences(the only sequence type without BDR). That works fine on an standalone, non-BDR node, but it could not work on a BDR cluster.
If you create a sequence on a BDR cluster, that sequence will be created on all nodes. However, if you call nextval for that sequence on your 3 nodes, you will probably get the same values on each one. That's why BDR have a different type of sequences, "global sequences".
BDR Global Sequences are split into chunks, and each group of chunks is assigned to each server in the cluster; nextval get the next value on a chunk assigned to the server you call it, guaranteeing that you will get different values on each server. It also guarantees that a second call to nextval(in the same server) will produce a value higher than the previous call. However, it does not guarantee time-sequence correlation across servers. More info about global sequences: http://bdr-project.org/docs/stable/global-sequences.html
To solve your problem, you must use global sequences. There's no conversion process, so you must drop your current sequences and recreate them as global sequences. Something like this:
SELECT nextval('seq_nnnn'); -- Will give you a value, let's say "x"
DROP SEQUENCE seq_nnnn;
CREATE SEQUENCE seq_nnnn START x USING bdr; -- Replace "x" with
Remember to assign USAGE permission as needed.
That will do the trick.
Saludos,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
----- Original Message -----
From: "Keefer, Brad (ITS)" <Brad.Keefer@its.ny.gov>
To: "pgsql-admin" <pgsql-admin@postgresql.org>
Sent: Wednesday, 4 October, 2017 10:28:20
Subject: [ADMIN] 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.Keefer@its.ny.gov<mailto:Brad.Keefer@its.ny.gov>
www.its.ny.gov<http://www.its.ny.gov/;
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* replication issue
@ 2018-04-09 08:41 Dhandapani Shanmugam <postgresql95@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dhandapani Shanmugam @ 2018-04-09 08:41 UTC (permalink / raw)
To: pgsql-admin
Hi All,
I am setting up streaming replication and getting below error when starting
the postgreSQL instance on stanby server. Need your expert advise
postgres 26110 26108 0 16:35 ? 00:00:00 postgres: startup process
recovering 00000002000000000000001F
Below are my content of recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=188.33.44.33 port=5432 user=replication'
restore_command = 'cp /u101/oradata/postgres/standby/pg_xlog/%f %p'
-D
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: replication issue
@ 2018-04-09 08:51 Dhandapani Shanmugam <postgresql95@gmail.com>
parent: Dhandapani Shanmugam <postgresql95@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dhandapani Shanmugam @ 2018-04-09 08:51 UTC (permalink / raw)
To: pgsql-admin
I am getting below error in logfile
2018-04-09 16:51:00 SGT LOG: invalid record length at 0/1F000098: wanted
24, got 0
2018-04-09 16:51:00 SGT FATAL: highest timeline 1 of the primary is behind
recovery timeline 2
On Mon, Apr 9, 2018 at 2:11 PM, Dhandapani Shanmugam <postgresql95@gmail.com
> wrote:
> Hi All,
>
> I am setting up streaming replication and getting below error when
> starting the postgreSQL instance on stanby server. Need your expert advise
>
> postgres 26110 26108 0 16:35 ? 00:00:00 postgres: startup process
> recovering 00000002000000000000001F
>
> Below are my content of recovery.conf
>
> standby_mode = 'on'
> primary_conninfo = 'host=188.33.44.33 port=5432 user=replication'
> restore_command = 'cp /u101/oradata/postgres/standby/pg_xlog/%f %p'
>
> -D
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: replication issue
@ 2018-04-09 08:59 Samed YILDIRIM <samed@reddoc.net>
parent: Dhandapani Shanmugam <postgresql95@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Samed YILDIRIM @ 2018-04-09 08:59 UTC (permalink / raw)
To: Dhandapani Shanmugam <postgresql95@gmail.com>; pgsql-admin
<div>Hi Dhandapani,</div><div> </div><div>It looks a recover operation or a switchover/failover operation has been applied. You can add following line to your recovery.conf file to follow new timeline.</div><div> </div><div>recovery_target_timeline = 'latest'</div><div> </div><div><a href="https://www.postgresql.org/docs/10/static/recovery-target-settings.html">https://www.postgres... href="https://www.postgresql.org/docs/10/static/continuous-archiving.html#BACKUP-TIMELINES">https:/... regards.</div><div><br /></div><div><br /></div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div><br /></div><div><br /></div><div><br /></div><div>09.04.2018, 11:52, "Dhandapani Shanmugam" <postgresql95@gmail.com>:</div><blockquote type="cite"><div dir="ltr">I am getting below error in logfile<div><br /></div><div><span>2018-04-09 16</span>:51:00 SGT LOG: invalid record length at 0/1F000098: wanted 24, got 0</div><div><span>2018-04-09 16</span>:51:00 SGT FATAL: highest timeline 1 of the primary is behind recovery timeline 2</div><div><br /></div></div><div><br /><div>On Mon, Apr 9, 2018 at 2:11 PM, Dhandapani Shanmugam <span dir="ltr"><<a href="mailto:postgresql95@gmail.com" target="_blank">postgresql95@gmail.com</a>></span> wrote:<br /><blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div dir="ltr"><div>Hi All,</div><div><br /></div><div>I am setting up streaming replication and getting below error when starting the postgreSQL instance on stanby server. Need your expert advise</div><div><br /></div><div>postgres <span>26110 26108</span> 0 16:35 ? 00:00:00 postgres: startup process recovering <span>00000002000000000000001</span>F</div><div><br /></div><div>Below are my content of recovery.conf</div><div><br /></div><div>standby_mode = 'on'</div><div>primary_conninfo = 'host=188.33.44.33 port=5432 user=replication'</div><div>restore_command = 'cp /u101/oradata/postgres/<wbr />standby/pg_xlog/%f %p'</div><span><font color="#888888"><div><br /></div><div>-D</div></font></span></div>
</blockquote></div><br /></div>
</blockquote>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: replication issue
@ 2018-04-09 09:09 Dhandapani Shanmugam <postgresql95@gmail.com>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 0 replies; 6+ messages in thread
From: Dhandapani Shanmugam @ 2018-04-09 09:09 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; +Cc: pgsql-admin
Thanks for your response Samed.
I have set recovery_target_timeline='latest', however getting below error.
2018-04-09 16:58:26 SGT FATAL: highest timeline 1 of the primary is behind
recovery timeline 2
cp: cannot stat ‘/u101/oradata/postgres/standby/pg_xlog/00000003.history’:
No such file or directory
On Mon, Apr 9, 2018 at 2:29 PM, Samed YILDIRIM <samed@reddoc.net> wrote:
> Hi Dhandapani,
>
> It looks a recover operation or a switchover/failover operation has been
> applied. You can add following line to your recovery.conf file to follow
> new timeline.
>
> recovery_target_timeline = 'latest'
>
> https://www.postgresql.org/docs/10/static/recovery-target-settings.html
> https://www.postgresql.org/docs/10/static/continuous-
> archiving.html#BACKUP-TIMELINES
>
> Best regards.
>
>
> İyi çalışmalar.
> Samed YILDIRIM
>
>
>
> 09.04.2018, 11:52, "Dhandapani Shanmugam" <postgresql95@gmail.com>:
>
> I am getting below error in logfile
>
> 2018-04-09 16:51:00 SGT LOG: invalid record length at 0/1F000098: wanted
> 24, got 0
> 2018-04-09 16:51:00 SGT FATAL: highest timeline 1 of the primary is
> behind recovery timeline 2
>
>
> On Mon, Apr 9, 2018 at 2:11 PM, Dhandapani Shanmugam <
> postgresql95@gmail.com> wrote:
>
> Hi All,
>
> I am setting up streaming replication and getting below error when
> starting the postgreSQL instance on stanby server. Need your expert advise
>
> postgres 26110 26108 0 16:35 ? 00:00:00 postgres: startup
> process recovering 00000002000000000000001F
>
> Below are my content of recovery.conf
>
> standby_mode = 'on'
> primary_conninfo = 'host=188.33.44.33 port=5432 user=replication'
> restore_command = 'cp /u101/oradata/postgres/standby/pg_xlog/%f %p'
>
> -D
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-04-09 09:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-10-04 15:28 replication issue Keefer, Brad (ITS) <Brad.Keefer@its.ny.gov>
2017-10-04 16:00 ` Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2018-04-09 08:41 replication issue Dhandapani Shanmugam <postgresql95@gmail.com>
2018-04-09 08:51 ` Dhandapani Shanmugam <postgresql95@gmail.com>
2018-04-09 08:59 ` Samed YILDIRIM <samed@reddoc.net>
2018-04-09 09:09 ` Dhandapani Shanmugam <postgresql95@gmail.com>
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