Re: [GENERAL] BDR

[James Keener]

Also, what did you run exactly (sanitized of course).

On March 14, 2016 5:38:19 PM EDT, John R Pierce  wrote:
>On 3/14/2016 2:17 PM, Dustin Kempter wrote:
>> However my instances are not on the same server and I attempted to 
>> simply add a host=(the ip) but that failed. Please help 
>
>did you get an error?   if so what error, exactly?
>
>
>
>-- 
>john r pierce, recycling bits in santa cruz
>
>
>
>-- 
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: [GENERAL] BDR

[Roland van Laar]

On 14-03-16 22:17, Dustin Kempter wrote:
Hello all, I am attempting to set up BDR between 2 separate nodes. I 
have been following the guide and got to here 
http://bdr-project.org/docs/0.9.0/quickstart-enabling.html

I am now stuck on this section

"Then you run a function that identifies a BDR group that delineates a 
connection string for other nodes to communicate with (for the first 
node, we will use port 5598) from the same SQL session as above on 
port 5598:


SELECT bdr.bdr_group_create(
  local_node_name := 'node1',
  node_external_dsn := 'port=5598 dbname=bdrdemo'
);"

However my instances are not on the same server and I attempted to 
simply add a host=(the ip) but that failed.

There are a couple of other factors:
- is postgres running on an external available ip?
- is there a replication user with a password?

Roland


Please help

Thanks in advance!







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR

[John R Pierce]

On 3/14/2016 2:17 PM, Dustin Kempter wrote:
However my instances are not on the same server and I attempted to 
simply add a host=(the ip) but that failed. Please help 


did you get an error?   if so what error, exactly?



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR

[Dustin Kempter]

Hello all, I am attempting to set up BDR between 2 separate nodes. I 
have been following the guide and got to here 
http://bdr-project.org/docs/0.9.0/quickstart-enabling.html

I am now stuck on this section

"Then you run a function that identifies a BDR group that delineates a 
connection string for other nodes to communicate with (for the first 
node, we will use port 5598) from the same SQL session as above on port 
5598:


SELECT bdr.bdr_group_create(
  local_node_name := 'node1',
  node_external_dsn := 'port=5598 dbname=bdrdemo'
);"

However my instances are not on the same server and I attempted to 
simply add a host=(the ip) but that failed. Please help


Thanks in advance!



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR not catching up

[cchee-ob]

I'm getting this message repeating on the UDR node that I just added today. 
Any way to get it start applying?
svp2=# select * from bdr.bdr_nodes;
 node_sysid  | node_timeline | node_dboid | node_status |   
node_name|node_local_dsn |   
node_init_from_dsn  
   
-+---++-+-+---+
---
 6206439726032130602 | 1 |  16385 | r   | UDR1  
 
|   | 
 6260914790689848233 | 1 |  16385 | c   |
UDR1-subscriber | host=10.253.0.8 port=5432 dbname=svp2 |
host=10.253.228.105 port=5432 dbnam
e=svp2
(2 rows)



t=2016-03-11 15:23:51 PST d= h= p=7226 a=DEBUG:  0: per-db worker for
node bdr (6260914790689848233,1,16385,) starting
t=2016-03-11 15:23:51 PST d= h= p=7226 a=LOCATION:  bdr_perdb_worker_main,
bdr_perdb.c:707
t=2016-03-11 15:23:51 PST d= h= p=7226 a=DEBUG:  0: init_replica init
from remote host=10.253.228.105 port=5432 dbname=svp2
t=2016-03-11 15:23:51 PST d= h= p=7226 a=LOCATION:  bdr_init_replica,
bdr_init_replica.c:830
t=2016-03-11 15:23:51 PST d= h= p=7226 a=DEBUG:  0: found valid
replication identifier 1
t=2016-03-11 15:23:51 PST d= h= p=7226 a=LOCATION: 
bdr_establish_connection_and_slot, bdr.c:572
t=2016-03-11 15:23:51 PST d= h= p=7226 a=DEBUG:  0: launching catchup
mode apply worker
t=2016-03-11 15:23:51 PST d= h= p=7226 a=LOCATION:  bdr_init_replica,
bdr_init_replica.c:1043
t=2016-03-11 15:23:51 PST d= h= p=7226 a=DEBUG:  0: Registering bdr
apply catchup worker for bdr (6206439726032130602,1,16385,) to lsn
19E/10AC4F0
t=2016-03-11 15:23:51 PST d= h= p=7226 a=LOCATION:  bdr_catchup_to_lsn,
bdr_init_replica.c:1161
t=2016-03-11 15:23:51 PST d= h= p=4718 a=LOG:  0: registering background
worker "bdr: catchup apply to 19E/10AC4F0"
t=2016-03-11 15:23:51 PST d= h= p=4718 a=LOCATION: 
BackgroundWorkerStateChange, bgworker.c:347
t=2016-03-11 15:23:51 PST d= h= p=4718 a=LOG:  0: starting background
worker process "bdr: catchup apply to 19E/10AC4F0"
t=2016-03-11 15:23:51 PST d= h= p=4718 a=LOCATION:  do_start_bgworker,
postmaster.c:5412
t=2016-03-11 15:23:51 PST d= h= p=7227 a=NOTICE:  0: version "1.0" of
extension "btree_gist" is already installed
t=2016-03-11 15:23:51 PST d= h= p=7227 a=LOCATION:  ExecAlterExtensionStmt,
extension.c:2700
t=2016-03-11 15:23:51 PST d= h= p=7227 a=NOTICE:  0: version "0.9.2.0"
of extension "bdr" is already installed
t=2016-03-11 15:23:51 PST d= h= p=7227 a=LOCATION:  ExecAlterExtensionStmt,
extension.c:2700
t=2016-03-11 15:23:51 PST d= h= p=7227 a=NOTICE:  42622: identifier "bdr
(6260914790689848233,1,16385,): apply catchup up to 19E/10AC4F0" will be
truncated to "bdr (6260914790689848233,1,16385,): apply catchup up to
19E/10A"
t=2016-03-11 15:23:51 PST d= h= p=7227 a=LOCATION:  truncate_identifier,
scansup.c:195
t=2016-03-11 15:23:51 PST d= h= p=7227 a=DEBUG:  0: found valid
replication identifier 1
t=2016-03-11 15:23:51 PST d= h= p=7227 a=LOCATION: 
bdr_establish_connection_and_slot, bdr.c:572
t=2016-03-11 15:23:51 PST d= h= p=7227 a=INFO:  0: starting up
replication from 1 at 19D/D204D0C8
t=2016-03-11 15:23:51 PST d= h= p=7227 a=LOCATION:  bdr_apply_main,
bdr_apply.c:2550
t=2016-03-11 15:23:51 PST d= h= p=7227 a=DEBUG:  0: bdr_apply: BEGIN
origin(source, orig_lsn, timestamp): 19D/D204D3A0, 2016-03-11
13:49:47.293208-08
t=2016-03-11 15:23:51 PST d= h= p=7227 a=LOCATION:  process_remote_begin,
bdr_apply.c:198
t=2016-03-11 15:23:51 PST d= h= p=7227 a=ERROR:  XX000: tuple natts
mismatch, 26 vs 28
t=2016-03-11 15:23:51 PST d= h= p=7227 a=LOCATION:  read_tuple_parts,
bdr_apply.c:1892
t=2016-03-11 15:23:51 PST d= h= p=4718 a=LOG:  0: worker process: bdr:
catchup apply to 19E/10AC4F0 (PID 7227) exited with exit code 1
t=2016-03-11 15:23:51 PST d= h= p=4718 a=LOCATION:  LogChildExit,
postmaster.c:3325
t=2016-03-11 15:23:51 PST d= h= p=4718 a=LOG:  0: unregistering
background worker "bdr: catchup apply to 19E/10AC4F0"
t=2016-03-11 15:23:51 PST d= h= p=4718 a=LOCATION:  ForgetBackgroundWorker,
bgworker.c:376
t=2016-03-11 15:23:52 PST d= h= p=7226 a=ERROR:  XX000: catchup worker
exited before catching up to target LSN 19E/10AC4F0
t=2016-03-11 15:23:52 PST d= h= p=7226 a=LOCATION:  bdr_catchup_to_lsn,
bdr_init_replica.c:1273
t=2016-03-11 15:23:52 PST d= h= p=4718 a=LOG:  0: worker process: bdr
db: svp2 (PID 7226) exited with exit code 1
t=2016-03-11 15:23:52 PST d= h= p=4718 a=LOCATION:  LogChildExit,
postmaster.c:3325
t=2016-03-11 15:23:54 PST d= h= p=7228 a=DEBUG:  0: autovacuum:
processing database "bdr_supervisordb"
t=2016-03-11 15:23:54 PST d= h= p=7228 a=LOCATION:  AutoVacWorkerMain,
autovacuum.c:1684
t=2016-03-11 15:23:57 PST d= h= p=4718 a=LOG:  0: starting background
worker process "bdr db: svp2"
t=2016-03-11 15:23:57 PST d= h= p=4718 a

Re: [GENERAL] BDR concern/issue

[Craig Ringer]

On 3 March 2016 at 07:54, cchee-ob  wrote:

> I queried pg_replication_slots after I removed an BDR node and I noticed a
> slot_name that isn't in bdr.bdr_node_slots.  And active is 'f' and it has
> been retaining bytes.  Should I be concerned and is there a way to remove
> it.  I do still have one UDR node which is running
> (bdr_16385_6228994276814368133_1_16384).  Any suggestions?
>
> svp2=# SELECT
>   slot_name, database, active,
>   pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn)
> AS retained_bytes
> FROM pg_replication_slots
> WHERE plugin = 'bdr';
> slot_name| database | active |
> retained_bytes
>
> -+--++
>  bdr_16385_6206441431541275808_1_16385__ | svp2 | f  |
> 410036551440
>  bdr_16385_6228994276814368133_1_16384__ | svp2 | t  |
> 285760
> (2 rows)
>
> svp2=# SELECT * FROM pg_stat_replication;
> -[ RECORD 1 ]+---
> pid  | 1122
> usesysid | 10
> usename  | postgres
> application_name | bdr (6228994276814368133,1,16384,):receive
> client_addr  | 10.253.0.8
> client_hostname  |
> client_port  | 43724
> backend_start| 2016-02-25 17:53:21.10519-08
> backend_xmin |
> state| streaming
> sent_location| 184/AE210BC8
> write_location   | 184/AE210BC8
> flush_location   | 184/AE20E748
> replay_location  | 184/AE210BC8
> sync_priority| 0
> sync_state   | async
>
> svp2=# select * from bdr.bdr_node_slots;
>  node_name | slot_name
> ---+---
> (0 rows)
>
> svp2=# SELECT * FROM bdr.bdr_nodes;
> -[ RECORD 1 ]--+--
> node_sysid | 6206439726032130602
> node_timeline  | 1
> node_dboid | 16385
> node_status| r
> node_name  | BDR1
> node_local_dsn | host=10.253.228.105 port=5432 dbname=svp2
> node_init_from_dsn |
> -[ RECORD 2 ]--+--
> node_sysid | 6206440469625465777
> node_timeline  | 1
> node_dboid | 16385
> node_status| k
> node_name  | BDR2
> node_local_dsn | host=10.253.16.25 port=5432 dbname=svp2
> node_init_from_dsn | host=10.253.228.105 port=5432 dbname=svp2
>
>
If you have a slot you know is unused, drop it. You can check it's the slot
for the parted node by comparing the slot name against the bdr local node
identity for the parted node (see the bdr docs for relevant functions to
get node identity).

BDR makes a best-effort attempt at dropping slots when parting a node but
there are known race conditions. We really need a two-phase part, where we
first agree to part and *then* actually remove the node, but that's not yet
implemented.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

[GENERAL] BDR concern/issue

[cchee-ob]

I queried pg_replication_slots after I removed an BDR node and I noticed a
slot_name that isn't in bdr.bdr_node_slots.  And active is 'f' and it has
been retaining bytes.  Should I be concerned and is there a way to remove
it.  I do still have one UDR node which is running
(bdr_16385_6228994276814368133_1_16384).  Any suggestions?

svp2=# SELECT   
  slot_name, database, active,
  pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn)
AS retained_bytes
FROM pg_replication_slots
WHERE plugin = 'bdr';
slot_name| database | active |
retained_bytes 
-+--++
 bdr_16385_6206441431541275808_1_16385__ | svp2 | f  |  
410036551440
 bdr_16385_6228994276814368133_1_16384__ | svp2 | t  |
285760
(2 rows)

svp2=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]+---
pid  | 1122
usesysid | 10
usename  | postgres
application_name | bdr (6228994276814368133,1,16384,):receive
client_addr  | 10.253.0.8
client_hostname  | 
client_port  | 43724
backend_start| 2016-02-25 17:53:21.10519-08
backend_xmin | 
state| streaming
sent_location| 184/AE210BC8
write_location   | 184/AE210BC8
flush_location   | 184/AE20E748
replay_location  | 184/AE210BC8
sync_priority| 0
sync_state   | async

svp2=# select * from bdr.bdr_node_slots;
 node_name | slot_name 
---+---
(0 rows)

svp2=# SELECT * FROM bdr.bdr_nodes;
-[ RECORD 1 ]--+--
node_sysid | 6206439726032130602
node_timeline  | 1
node_dboid | 16385
node_status| r
node_name  | BDR1
node_local_dsn | host=10.253.228.105 port=5432 dbname=svp2
node_init_from_dsn | 
-[ RECORD 2 ]--+--
node_sysid | 6206440469625465777
node_timeline  | 1
node_dboid | 16385
node_status| k
node_name  | BDR2
node_local_dsn | host=10.253.16.25 port=5432 dbname=svp2
node_init_from_dsn | host=10.253.228.105 port=5432 dbname=svp2





--
View this message in context: 
http://postgresql.nabble.com/BDR-concern-issue-tp5890301.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR error trying to replay a invalid statement

[Craig Ringer]

On 2 February 2016 at 05:07, cchee-ob  wrote:

> I noticed that the BDR replication continually trying to replay a ddl
> statement that has a syntax error.  Is there anything that can be done to
> skip this statement or do I need to rebuild the replicated node?


That's a DDL deparse bug. Ouch. Noted.

Honestly, rebuilding the node is the easiest option, but you can otherwise
skip the transaction. It's a bit tricky and I haven't had to do it in a
while or tested this procedure recently, please be cautious.

Stop the node(s) that are stuck. On the upstream node they're receiving the
statement from, check pg_replication_slots to get the slot name(s).

For each slot use pg_logical_slot_peek_binary_changes('slot_name', NULL,
NULL, 'interactive', 't') to find the problem xact. This is made more
exciting by the fact that the protocol is binary, but you'll be able to
find the SQL text in question in the stream. Determine the LSN associated
with the statement. Look further down the stream until you find the commit
record for it (command type 'c' in the binary protocol) and note the LSN
there.

Now call pg_logical_slot_get_binary_changes('slot_name', '123/456', NULL,
'interactive', 't') to skip up to the end of the problem transaction.

When you start the BDR nodes back up they'll ask for the old replay
position but it won't be available and they'll resume replay from the
restart position on the slot.

This process probably seems a bit baroque. It is. One of the things we're
working on right now with pglogical is a better way to skip over part of
the change stream, for whatever reason desired. Starting with a decoder
that lets you see the human readable protocol stream and reports the commit
boundaries.



> t=2016-02-01 13:02:27 PST d= h= p=21795 a=ERROR:  42601: syntax error at or
> near "ON" at character 8
> t=2016-02-01 13:02:27 PST d= h= p=21795 a=CONTEXT:  during DDL replay of
> ddl
> statement: GRANT  ON TABLE table1 TO user2 WITH GRANT OPTION
> 


Yup. Deparse bug.

Do you know what the original statement was?


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

[GENERAL] BDR error trying to replay a invalid statement

[cchee-ob]

I noticed that the BDR replication continually trying to replay a ddl
statement that has a syntax error.  Is there anything that can be done to
skip this statement or do I need to rebuild the replicated node?  Here's
what I see in the logs:

t=2016-02-01 13:02:27 PST d= h= p=21795 a=LOCATION: 
bdr_establish_connection_and_slot, bdr.c:572
t=2016-02-01 13:02:27 PST d= h= p=21795 a=INFO:  0: starting up
replication from 1 at 125/360AF118
t=2016-02-01 13:02:27 PST d= h= p=21795 a=LOCATION:  bdr_apply_main,
bdr_apply.c:2550
t=2016-02-01 13:02:27 PST d= h= p=21795 a=DEBUG:  0: bdr_apply: BEGIN
origin(source, orig_lsn, timestamp): 125/360AF1B8, 2016-01-28
19:34:50.180915-08
t=2016-02-01 13:02:27 PST d= h= p=21795 a=LOCATION:  process_remote_begin,
bdr_apply.c:198
t=2016-02-01 13:02:27 PST d= h= p=21795 a=ERROR:  42601: syntax error at or
near "ON" at character 8
t=2016-02-01 13:02:27 PST d= h= p=21795 a=CONTEXT:  during DDL replay of ddl
statement: GRANT  ON TABLE table1 TO user2 WITH GRANT OPTION

Thanks in advance!




--
View this message in context: 
http://postgresql.nabble.com/BDR-error-trying-to-replay-a-invalid-statement-tp5885230.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR replication on Postgresql 9.5.0

[Kaushal Shriyan]

On Mon, Feb 1, 2016 at 12:24 AM, John R Pierce  wrote:

> On 1/29/2016 3:00 AM, Kaushal Shriyan wrote:
>
>>
>> Do i need to install any BDR specific package to enable it in postgresql
>> 9.5 version. While reading
>> http://bdr-project.org/docs/0.9.0/install-requirements.html i assumed
>> that it is available in 9.5 version by default without using any patches.
>> Please comment.
>>
>
> "As of the time of writing, the upcoming PostgreSQL 9.5 release is not yet
> supported. Neither is Microsoft Windows. Support for both will be added in
> later releases; please check the BDR website for the latest information."
>
>
>
Thanks Pierce for the explanation.

Re: [GENERAL] BDR replication on Postgresql 9.5.0

[John R Pierce]

On 1/29/2016 3:00 AM, Kaushal Shriyan wrote:


Do i need to install any BDR specific package to enable it in 
postgresql 9.5 version. While reading 
http://bdr-project.org/docs/0.9.0/install-requirements.html i assumed 
that it is available in 9.5 version by default without using any 
patches. Please comment.


"As of the time of writing, the upcoming PostgreSQL 9.5 release is not 
yet supported. Neither is Microsoft Windows. Support for both will be 
added in later releases; please check the BDR website for the latest 
information."




--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR replication on Postgresql 9.5.0

[Sachin Jain]

BDR extension is not available in 9.5. You need to install it separately.

- Sachin
On 29 Jan 2016 4:31 p.m., "Kaushal Shriyan" 
wrote:

> Hi,
>
> I am following http://bdr-project.org/docs/stable/index.html to setup BDR
> for postgresql version 9.5 as per
>
> [root@ip-172-31-1-17 9.5]# cat /etc/redhat-release
> *Red Hat Enterprise Linux Server release 6.6 (Santiago)*
> [root@ip-172-31-1-17 9.5]# rpm -qa | grep postgre
> postgresql95-libs-9.5.0-2PGDG.rhel6.x86_64
> postgresql95-server-9.5.0-2PGDG.rhel6.x86_64
> postgresql95-9.5.0-2PGDG.rhel6.x86_64
> [root@ip-172-31-1-17 9.5]#
>
> cat pgstartup.log
> < 2016-01-29 05:42:01.716 EST >FATAL:  could not access file "bdr": No
> such file or directory
>
> Do i need to install any BDR specific package to enable it in postgresql
> 9.5 version. While reading
> http://bdr-project.org/docs/0.9.0/install-requirements.html i assumed
> that it is available in 9.5 version by default without using any patches.
> Please comment.
>
> Any help will be highly appreciable.
>
> Regards,
>
> Kaushal
>
>
>

Re: [GENERAL] BDR replication

[Craig Ringer]

On 29 January 2016 at 18:27, Nikhil  wrote:


> Is there any way to specify priority for replication. or any parameter
> which guarantees something about replication (speed at which it replicates,
> number of minimum replicas to write).
>

Not yet. Not in core PostgreSQL streaming replication, nor in BDR or
pglogical etc.

Right now you have "synchronous" or "not synchronous" and at most one
synchronous node.

Does BDR has a configuration for differentiated services in replication.
>

No. It's mesh multimaster and all replicated data is treated equally.
There's no concept of replication priority, nor am I sure how we could
implement such a thing. Data is either replicated or not replicated.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

[GENERAL] BDR replication on Postgresql 9.5.0

[Kaushal Shriyan]

Hi,

I am following http://bdr-project.org/docs/stable/index.html to setup BDR
for postgresql version 9.5 as per

[root@ip-172-31-1-17 9.5]# cat /etc/redhat-release
*Red Hat Enterprise Linux Server release 6.6 (Santiago)*
[root@ip-172-31-1-17 9.5]# rpm -qa | grep postgre
postgresql95-libs-9.5.0-2PGDG.rhel6.x86_64
postgresql95-server-9.5.0-2PGDG.rhel6.x86_64
postgresql95-9.5.0-2PGDG.rhel6.x86_64
[root@ip-172-31-1-17 9.5]#

cat pgstartup.log
< 2016-01-29 05:42:01.716 EST >FATAL:  could not access file "bdr": No such
file or directory

Do i need to install any BDR specific package to enable it in postgresql
9.5 version. While reading
http://bdr-project.org/docs/0.9.0/install-requirements.html i assumed that
it is available in 9.5 version by default without using any patches. Please
comment.

Any help will be highly appreciable.

Regards,

Kaushal

Re: [GENERAL] BDR replication

[John R Pierce]

On 1/29/2016 2:27 AM, Nikhil wrote:


Is there any way to specify priority for replication. or any parameter 
which guarantees something about replication (speed at which it 
replicates, number of minimum replicas to write).


Does BDR has a configuration for differentiated services in 
replication. I want to categorize my data that i replicate. I am ok if 
some logs are lost in replication. journal entries must not be lost in 
replication.


its all or nothing, postgres doesn't do 'data maybe'.



--
john r pierce, recycling bits in santa cruz

[GENERAL] BDR replication

[Nikhil]

Hello,

Is there any way to specify priority for replication. or any parameter
which guarantees something about replication (speed at which it replicates,
number of minimum replicas to write).

Does BDR has a configuration for differentiated services in replication. I
want to categorize my data that i replicate. I am ok if some logs are lost
in replication. journal entries must not be lost in replication.

Best Regards,
Nikhil

Re: [GENERAL] BDR with postgres 9.5

[Craig Ringer]

On 22 January 2016 at 04:24, Merlin Moncure  wrote:

> On Wed, Jan 20, 2016 at 12:52 PM, Vik Fearing  wrote:
> > On 01/20/2016 11:41 AM, Nikhil wrote:
> >> Hello All,
> >>
> >>
> >> What is the timeline for BDR with postgres 9.5 released version.
> >
> > Currently there are no plans for BDR with 9.5.
> > https://github.com/2ndQuadrant/bdr/issues/157#issuecomment-172402366
>
> 9.6 looks like a possibility though.  I have big plans for BDR
> personally, but for various reasons need to lay it on top of a stock
> postgres.


Yeah. 9.6 is the goal right now, with it built around pglogical and
(hopefully) stock 9.6 to make it more maintainable and modular.

If you want BDR on 9.6, help with getting sequence access methods
committed, the logical decoding for sequences patch, and/or failover slots
would be valuable since they are all going to be important for BDR on 9.6.
If you want to use it please help make it happen.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] BDR with postgres 9.5

[Merlin Moncure]

On Wed, Jan 20, 2016 at 12:52 PM, Vik Fearing  wrote:
> On 01/20/2016 11:41 AM, Nikhil wrote:
>> Hello All,
>>
>>
>> What is the timeline for BDR with postgres 9.5 released version.
>
> Currently there are no plans for BDR with 9.5.
> https://github.com/2ndQuadrant/bdr/issues/157#issuecomment-172402366

9.6 looks like a possibility though.  I have big plans for BDR
personally, but for various reasons need to lay it on top of a stock
postgres.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR with postgres 9.5

[Vik Fearing]

On 01/20/2016 11:41 AM, Nikhil wrote:
> Hello All,
> 
> 
> What is the timeline for BDR with postgres 9.5 released version.

Currently there are no plans for BDR with 9.5.
https://github.com/2ndQuadrant/bdr/issues/157#issuecomment-172402366
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR with postgres 9.5

[Nikhil]

Hello All,


What is the timeline for BDR with postgres 9.5 released version.


Best Regards,
Nikhil

[GENERAL] BDR install broken on Ubuntu 14.04

[LOUBRADOU , Rémy]

Hi,

I have the same issue. It was working fine a month ago. I wrote a cookbook
with chef to install BDR a month ago since then I did not change the
cookbook but now I got this error.

This seem to be the heart of the issue:

Removing obsolete dictionary files:
==> ldev4:  * No PostgreSQL clusters exist; see "man pg_createcluster"
==> ldev4: /usr/share/postgresql-common/maintscripts-functions: line
95: db_stop: command not found
==> ldev4: dpkg: error processing package postgresql-bdr-9.4
(--configure):
==> ldev4:  subprocess installed post-installation script returned
error exit status 127


Anyone know what's going on ? I'm going to investigate.

Thanks for your replies,

Rémy

Re: [GENERAL] [BDR] Best practice to automatically abort a DDL operation when one node is down

[Sylvain MARECHAL]

What is the best practice to make sure the DDL operation will
fail, possibly after a timeout, if one of the node is down?


statement_timeout


Ok. Thank-you for pointing this. I have just tried it, and this work 
great even for nodes that are not properly power off (plug removed).


I could check the state of the node before issuing the DDL
operation, but this solution is far from being perfect as the node
may fail right after this.


Correct, but it's still useful to do.

I'd check to see all nodes are connected in pg_stat_replication then 
I'd issue the DDL with a statement_timeout set.


Ok. For the first check, I was using 
|bdr.bdr_test_remote_connectback(/peer_dsn/, /local_dsn/), getting the 
dsn from the bdr.bdr_nodes table; but using the |pg_stat_replication 
table is problably quicker and simpler.


Thank-you again,

Sylvain

Re: [GENERAL] BDR: cascading setup

[Craig Ringer]

On 11 January 2016 at 18:55, Oleksii Kliukin  wrote:


> We are evaluating BDR for a multi-master cross-datacenter replication,
> with 2 masters actually communicating across datacenter, supplemented by a
> local in-datacenter replicas to provide HA.
>

This is strongly desirable ... but not currently supported.



> As far as I see, I cannot make a promoted physical replica a member of the
> multi-master group without re-joining the group after promotion (which
> leads to re-transfering the whole database over the network from the
> surviving master), see https://github.com/2ndQuadrant/bdr/issues/98
>

Correct.

The fundamental issue is that logical replication slots are not copied by
pg_basebackup or replicated via physical WAL-based replication. Without
this a promoted replica has no knowledge of the replay position of its
peers, nor does it know how much extra WAL to retain to allow them to catch
up on replication.

This will hopefully be fixed in 9.6 as there are patches in the queue to
address these issues.

There are


> - BDR multi-master being part of more than one replication group (I could
> create one group for cross-DS multi-masters, and another for DS-local
> communications)?
>

It won't help. BDR always replicates in a mesh and doesn't do cascading.
Replication sets won't change that.


> - BDR multi-master being also master with several UDR replicas attached
> (so that DS-local nodes will be running as UDR replicas of a master, that
> at the same time communicates via BDR to another master in another DS), and
> allowing the UDR replica to join the BDR group if the master dies.
>

Again this won't work. You can't promote a UDR replica to a full BDR peer.
For now you're stuck with the extra replication traffic of the two local
nodes speaking directly to their remote peers.

What you need is non-mesh topologies and support for selective changeset
forwarding, and/or support for promotion of physical replicas to replace
failed nodes.

Both of those are in the pipeline.

pglogical has a more flexible model of replication topology and forwarding,
and we plan to rewrap BDR around pglogical for 9.6. This should (time
permitting) allow for non-mesh topologies and cascading. To make it work
well we'll need logical decoding support for logical slots, though, and
that may not make it into 9.6. There's no practical way to add this to
9.4bdr since it relies heavily on (sysid,timeline,dboid) tuples to identify
nodes, so it'll be 9.6-only.

If the pg_basebackup and replication patches to replicate slots are
accepted into 9.6 then we'll be able to have physical standbys of
pglogical/bdr nodes. It may be possible to backport this to 9.4bdr but I'm
not aware of any plans to do so and available time/resources are mainly
focused on driving 9.6/pglogical forward. Get in touch if you think this is
something you could use more urgently.

I realise this isn't quite the answer you hoped for, but at least there's
improvement on the horizon.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] [BDR] Best practice to automatically abort a DDL operation when one node is down

[Craig Ringer]

On 13 January 2016 at 21:45, Sylvain MARECHAL 
wrote:


> The problem is that the (1) DDL request will wait indefinitely, meaning
> all transactions will continue to fail until the DDL operation is manually
> aborted (for example, doing CTRL C in psql to abort the "CREATE TABLE").
>

Correct, and by design.

I'd like to do a pre-check where we sync up with the peer nodes and see if
they're all alive before we take the DDL lock. This would reduce the impact
a bit and allow an early ERROR like "ERROR: cannot perform DDL when one or
more nodes is unreachable".

However... we have something pretty close already. You can just set a
statement_timeout in the session doing the DDL. It'll cancel the operation
if it takes too long.

Note that a lock_timeout will NOT work because the BDR global DDL lock is
not recognised as a true lock by PostgreSQL.



> What is the best practice to make sure the DDL operation will fail,
> possibly after a timeout, if one of the node is down?


statement_timeout


> I could check the state of the node before issuing the DDL operation, but
> this solution is far from being perfect as the node may fail right after
> this.
>

Correct, but it's still useful to do.

I'd check to see all nodes are connected in pg_stat_replication then I'd
issue the DDL with a statement_timeout set.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] BDR install broken on Ubuntu 14.04

[Roland van Laar]

On 13-01-16 17:48, LOUBRADOU, Rémy wrote:

Hi,

I have the same issue. It was working fine a month ago. I wrote a 
cookbook with chef to install BDR a month ago since then I did not 
change the cookbook but now I got this error.


This seem to be the heart of the issue:

Removing obsolete dictionary files:
==> ldev4:  * No PostgreSQL clusters exist; see "man pg_createcluster"
==> ldev4: /usr/share/postgresql-common/maintscripts-functions: line 
95: db_stop: command not found
==> ldev4: dpkg: error processing package postgresql-bdr-9.4 
(--configure):
==> ldev4:  subprocess installed post-installation script returned 
error exit status 127



Anyone know what's going on ? I'm going to investigate.


The debconf source file is missing.
db_stop, stands for debconf stop.

It is supposed to be fixed but it is not.
See this closed issue on Github: 
https://github.com/2ndQuadrant/bdr/issues/156


I "fixed" it in ansible by

- installing bdr accepting the fail
- removing the db_stop line
- installing bdr again.

This is the relevant part of my ansible configuration:

- name: install postgresql bdr
  apt:
name=postgresql-bdr-9.4-bdr-plugin
state=present
  ignore_errors: yes

- name: remove db_stop from postgres postinstall script
  lineinfile:
dest=/usr/share/postgresql-common/maintscripts-functions
regexp=db_stop
state=absent

- name: reinstall postgresql bdr to mitigate the db_stop problem
  apt:
name=postgresql-bdr-9.4-bdr-plugin
state=present

Regards,

Roland



Thanks for your replies,

Rémy





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] [BDR] Best practice to automatically abort a DDL operation when one node is down

[Sylvain MARECHAL]

Hello all,

I am using BDR with two nodes 1 and 2.
If I issue a DDL operation in node 1 when node 2 is down, for example:
  CREATE TABLE test (i int PRIMARY KEY); (1)

all other transactions fail with the following error:
  Database is locked against DDL operations

The problem is that the (1) DDL request will wait indefinitely, meaning 
all transactions will continue to fail until the DDL operation is 
manually aborted (for example, doing CTRL C in psql to abort the "CREATE 
TABLE").


What is the best practice to make sure the DDL operation will fail, 
possibly after a timeout, if one of the node is down? I could check the 
state of the node before issuing the DDL operation, but this solution is 
far from being perfect as the node may fail right after this.


Thanks and Regards,
--
Sylvain


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR: cascading setup

[Oleksii Kliukin]

Hi,

We are evaluating BDR for a multi-master cross-datacenter replication, with 2 
masters actually communicating across datacenter, supplemented by a local 
in-datacenter replicas to provide HA.

Basically, something like 

M <——> M
 ||
S   S

I could run all nodes as multi-masters, but I don’t want many-to-many 
cross-datacenter communications, primary because of the latency issues, and 
also to avoid locking too many nodes on DDL changes.

As far as I see, I cannot make a promoted physical replica a member of the 
multi-master group without re-joining the group after promotion (which leads to 
re-transfering the whole database over the network from the surviving master), 
see https://github.com/2ndQuadrant/bdr/issues/98, so running multi-master with 
physical datacenter-local replicas is tough, but is there a better alternative 
at the moment, i.e:

- BDR multi-master being part of more than one replication group (I could 
create one group for cross-DS multi-masters, and another for DS-local 
communications)?
- BDR multi-master being also master with several UDR replicas attached (so 
that DS-local nodes will be running as UDR replicas of a master, that at the 
same time communicates via BDR to another master in another DS), and allowing 
the UDR replica to join the BDR group if the master dies. 

Kind regards,
--
Oleksii

Re: [GENERAL] BDR and TX obeyance

[Simon Riggs]

On 4 January 2016 at 20:09, Riley Berton  wrote:


> The conflict on the "thingy" table has resulted in node2 winning based
> on last_update wins default resolution.  However, both inserts have
> applied.  My expectation is that the entire TX applies or does not
> apply.  This expectation is clearly wrong.
>

I'm sorry to say: Yes, I think so.

If you try to update the same data at the same time in multiple locations,
your application has a significant problem, period. That's just physics.

How that problem manifests itself is really based upon your choice of
technology. Choosing Postgres, Oracle or ProblemoDB won't change that.

If you choose single master, then you get an error because one of the nodes
can't be updated at all. If you have multiple masters, then you get to
choose between an early abort because of serialization errors (which causes
a huge performance overhead), or a later difficulty when conflict
resolution kicks in (which is why BDR supports post-commit conflict
resolution semantics). Or it you use a shared cache system like RAC then
you get significant performance degradation as the data blocks ping around
the cluster.

I'm personally in favour of giving people choice about how they configure
their databases. So you will see me acting to extend the range of options
available to users, allowing them to make informed choices.


> Question is: is there a way (via a custom conflict handler) to have the
> TX obeyed?  I can't see a way to even implement a simple bank account
> database that changes multiple tables in a single transaction without
> having the data end up in an inconsistent state.  Am I missing something
> obvious here?
>

Don't use updates in that way. Banks never do, but financial institutions
and many others have been using replication technology that supports
post-commit conflict resolution for more than a decade in products from
SQLServer, Informix and others. BDR was specified by a customer/user with
expert knowledge of that area and who knew what he wanted and didn't want
to see in the final product. I think those choices were good ones.

Design your applications carefully, understanding the trade-offs between
availability, local access times, serializability and performance.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [GENERAL] BDR and TX obeyance

[Riley Berton]

Craig Ringer  writes:

> On 5 January 2016 at 04:09, Riley Berton  wrote:
>
>>
>> The conflict on the "thingy" table has resulted in node2 winning based
>> on last_update wins default resolution.  However, both inserts have
>> applied.  My expectation is that the entire TX applies or does not
>> apply.  This expectation is clearly wrong.
>>
>
> Correct. Conflicts are resolved row-by-row. Their outcomes are determined
> (by default) by transaction commit timestamps, but the conflicts themselves
> are row-by-row.
>
> Because BDR:
>
> * applies changes to other nodes only AFTER commit on the origin node; and
> * does not take row and table locks across nodes
>
> it has no way to sensibly apply all or none of a transaction on downstream
> peers because the client has already committed and moved on to other
> things. If the xact doesn't apply, what do we do? Log output on the failing
> node(s) and throw it away?

Yes.  This is impossible.  I understand that clearly now.

>
> It's probably practical to have xacts abort on the first conflict, though
> some thought would be needed about making sure that doesn't break
> consistency requirements across nodes. It's not clear if doing so is useful
> though.
>
> For that you IMO want synchronous replication where the client doesn't get
> a local COMMIT until all nodes have confirmed they can commit the xact.
> That's something that could be added to BDR in future, but doing it well it
> requires support for logical decoding of prepared transactions which is
> currently missing from PostgreSQL's logical decoding support. If it's
> something you think is important/useful you might want to explore what's
> involved in implementing that.

I have considered 2 paths here.

1. What you suggest above.
2. Write sharding across the masters with RLS to prevent writes to the
wrong master.  I have not fully thought through whether this will work
in practice, but as long as the constraints are identical on all the
masters and we never mutate the same row(s) on multiple masters we
should never get conflicts.  This requires application design that ties
all the data to some root node which can be used to shard on and is not
applicable generally.

>
> Question is: is there a way (via a custom conflict handler) to have the
>> TX obeyed?
>
>
> No.
>
> Even if you ERROR in your handler, BDR will just retry the xact. It has no
> concept of "throw this transaction away forever".
>
>
>> I can't see a way to even implement a simple bank account
>> database that changes multiple tables in a single transaction without
>> having the data end up in an inconsistent state.  Am I missing something
>> obvious here?
>>
>
> You're trying to use asynchronous multimaster replication as if it was an
> application-transparent synchronous cluster with a global transaction
> manager and global lock manager.
>
> BDR is not application-transparent. You need to understand replication
> conflicts and think about them. It does not preserve full READ COMMITTED
> semantics across nodes. This comes with big benefits in partition
> tolerance, performance and latency tolerance, but it means you can't point
> an existing app at more than one node and expect it to work properly.
>
> The documentation tries over and over to emphasise this. Can you suggest
> where it can be made clearer or more prominent?

I was not the only one to be confused by this.  I think the reputation
of PostgreSQL is for correct transactional semantics by default.  BDR
requires a different way of thinking about it.  You might prevent future
confusion by giving some example scenarios in the Overview (or Concepts)
where a traditional single master would result in X but BDR across 2
masters would result in Y.

Thanks so much for the detailed response.

riley

>
> -- 
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR and TX obeyance

[Craig Ringer]

On 5 January 2016 at 04:09, Riley Berton  wrote:

>
> The conflict on the "thingy" table has resulted in node2 winning based
> on last_update wins default resolution.  However, both inserts have
> applied.  My expectation is that the entire TX applies or does not
> apply.  This expectation is clearly wrong.
>

Correct. Conflicts are resolved row-by-row. Their outcomes are determined
(by default) by transaction commit timestamps, but the conflicts themselves
are row-by-row.

Because BDR:

* applies changes to other nodes only AFTER commit on the origin node; and
* does not take row and table locks across nodes

it has no way to sensibly apply all or none of a transaction on downstream
peers because the client has already committed and moved on to other
things. If the xact doesn't apply, what do we do? Log output on the failing
node(s) and throw it away?

It's probably practical to have xacts abort on the first conflict, though
some thought would be needed about making sure that doesn't break
consistency requirements across nodes. It's not clear if doing so is useful
though.

For that you IMO want synchronous replication where the client doesn't get
a local COMMIT until all nodes have confirmed they can commit the xact.
That's something that could be added to BDR in future, but doing it well it
requires support for logical decoding of prepared transactions which is
currently missing from PostgreSQL's logical decoding support. If it's
something you think is important/useful you might want to explore what's
involved in implementing that.

Question is: is there a way (via a custom conflict handler) to have the
> TX obeyed?


No.

Even if you ERROR in your handler, BDR will just retry the xact. It has no
concept of "throw this transaction away forever".


> I can't see a way to even implement a simple bank account
> database that changes multiple tables in a single transaction without
> having the data end up in an inconsistent state.  Am I missing something
> obvious here?
>

You're trying to use asynchronous multimaster replication as if it was an
application-transparent synchronous cluster with a global transaction
manager and global lock manager.

BDR is not application-transparent. You need to understand replication
conflicts and think about them. It does not preserve full READ COMMITTED
semantics across nodes. This comes with big benefits in partition
tolerance, performance and latency tolerance, but it means you can't point
an existing app at more than one node and expect it to work properly.

The documentation tries over and over to emphasise this. Can you suggest
where it can be made clearer or more prominent?

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] BDR and TX obeyance

[Edson Richter]

Em 05/01/2016 11:42, Riley Berton escreveu:

Edson Richter  writes:


BTW, I'm also looking for a "synchronous multi-master" solution... If
you find one, please share :-)
The only solution I've found so far is a middleware that is close, the
C-Jdbc/Sequoia, which seems not being actively maintained for a while
now.

See Postgres-R for sync multi-master.
http://www.postgres-r.org/documentation/

Note that it is specifically geared towards low-latency environments and
is likely not suitable for geo-distributed applications. It hasn't been
touched in 4 years so likely not actively maintained.

riley


That seems to be what I'm looking for...
As soon as I get some free time, I'll give a try.

Regards,

Edson




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR install broken on Ubuntu 14.04

[Roland van Laar]

Hi,

I'm installing BDR on a ubuntu 14.04 (Trusty) machine. I can reproduce 
this error.

It happens locally with a new VM and on Digital ocean.
The error is:

/var/lib/dpkg/info/postgresql-bdr-9.4.postinst: 95: 
/var/lib/dpkg/info/postgresql-bdr-9.4.postinst: db_stop: not found


How can I fix this?

The full installation log is included below.

Regards,

Roland van Laar

sudo apt-get update
sudo apt-get upgrade
wget -qO - http://packages.2ndquadrant.com/bdr/apt/AA7A6805.asc |sudo 
apt-key add -
wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc |sudo 
apt-key add -
sudo add-apt-repository 'deb http://packages.2ndquadrant.com/bdr/apt/ 
trusty-2ndquadrant main'
sudo add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ 
trusty-pgdg main'

sudo apt-get update
sudo apt-get install postgresql-bdr-9.4-bdr-plugin


The full installation log:
$ sudo apt-get install postgresql-bdr-9.4-bdr-plugin
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer 
required:

  linux-headers-3.13.0-57 linux-headers-3.13.0-57-generic
  linux-headers-3.13.0-61 linux-headers-3.13.0-61-generic
  linux-image-3.13.0-57-generic linux-image-3.13.0-61-generic
  linux-image-extra-3.13.0-57-generic linux-image-extra-3.13.0-61-generic
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  libpq5 libxslt1.1 pgdg-keyring postgresql-bdr-9.4 
postgresql-bdr-client-9.4

  postgresql-bdr-contrib-9.4 postgresql-client-common postgresql-common
  ssl-cert
Suggested packages:
  oidentd ident-server locales-all postgresql-bdr-doc-9.4 libdbd-pg-perl
  openssl-blacklist
The following NEW packages will be installed:
  libpq5 libxslt1.1 pgdg-keyring postgresql-bdr-9.4
  postgresql-bdr-9.4-bdr-plugin postgresql-bdr-client-9.4
  postgresql-bdr-contrib-9.4 postgresql-client-common postgresql-common
  ssl-cert
0 upgraded, 10 newly installed, 0 to remove and 3 not upgraded.
Need to get 5,995 kB of archives.
After this operation, 28.4 MB of additional disk space will be used.
Do you want to continue? [Y/n]
Get:1 http://mirrors.digitalocean.com/ubuntu/ trusty/main libxslt1.1 
amd64 1.1.28-2build1 [145 kB]
Get:2 http://mirrors.digitalocean.com/ubuntu/ trusty/main ssl-cert all 
1.0.33 [16.6 kB]
Get:3 http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant/main 
postgresql-bdr-client-9.4 amd64 9.4.5-1trusty [1,068 kB]
Get:4 http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant/main 
postgresql-bdr-9.4 amd64 9.4.5-1trusty [3,677 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main libpq5 
amd64 9.4.5-1.pgdg14.04+1 [122 kB]
Get:6 http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant/main 
postgresql-bdr-contrib-9.4 amd64 9.4.5-1trusty [443 kB]
Get:7 http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant/main 
postgresql-bdr-9.4-bdr-plugin amd64 0.9.3-1trusty [230 kB]
Get:8 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main 
pgdg-keyring all 2014.1 [5,898 B]
Get:9 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main 
postgresql-client-common all 171.pgdg14.04+1 [76.6 kB]
Get:10 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main 
postgresql-common all 171.pgdg14.04+1 [210 kB]

Fetched 5,995 kB in 1s (4,540 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libpq5:amd64.
(Reading database ... 146095 files and directories currently installed.)
Preparing to unpack .../libpq5_9.4.5-1.pgdg14.04+1_amd64.deb ...
Unpacking libpq5:amd64 (9.4.5-1.pgdg14.04+1) ...
Selecting previously unselected package libxslt1.1:amd64.
Preparing to unpack .../libxslt1.1_1.1.28-2build1_amd64.deb ...
Unpacking libxslt1.1:amd64 (1.1.28-2build1) ...
Selecting previously unselected package pgdg-keyring.
Preparing to unpack .../pgdg-keyring_2014.1_all.deb ...
Unpacking pgdg-keyring (2014.1) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../postgresql-client-common_171.pgdg14.04+1_all.deb ...
Unpacking postgresql-client-common (171.pgdg14.04+1) ...
Selecting previously unselected package postgresql-bdr-client-9.4.
Preparing to unpack 
.../postgresql-bdr-client-9.4_9.4.5-1trusty_amd64.deb ...

Unpacking postgresql-bdr-client-9.4 (9.4.5-1trusty) ...
Selecting previously unselected package ssl-cert.
Preparing to unpack .../ssl-cert_1.0.33_all.deb ...
Unpacking ssl-cert (1.0.33) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../postgresql-common_171.pgdg14.04+1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev 
by postgresql-common'

Unpacking postgresql-common (171.pgdg14.04+1) ...
Selecting previously unselected package postgresql-bdr-9.4.
Preparing to unpack .../postgresql-bdr-9.4_9.4.5-1trusty_amd64.deb ...
Unpacking postgresql-bdr-9.4 (9.4.5-1trusty) ...
Selecting previously unselected package postgresql-bdr-contrib-9.4.
Preparing to unpack 
.../post

Re: [GENERAL] BDR and TX obeyance

[Riley Berton]

Edson Richter  writes:

> BTW, I'm also looking for a "synchronous multi-master" solution... If 
> you find one, please share :-)
> The only solution I've found so far is a middleware that is close, the 
> C-Jdbc/Sequoia, which seems not being actively maintained for a while
> now.

See Postgres-R for sync multi-master.
http://www.postgres-r.org/documentation/

Note that it is specifically geared towards low-latency environments and
is likely not suitable for geo-distributed applications. It hasn't been
touched in 4 years so likely not actively maintained.

riley

>
> Regards,
>
> Edson
>
> Atenciosamente,
>
> Edson Carlos Ericksson Richter
>
> Em 04/01/2016 18:09, Riley Berton escreveu:
>> I have been experimenting with BDR and have a question about how BDR
>> interacts with transactions.
>>
>> bdrdemo=# create table thingy (id INT, value TEXT, PRIMARY KEY(id));
>> CREATE TABLE
>> bdrdemo=# create table tx_log(id INT, msg TEXT, PRIMARY KEY(id));
>> CREATE TABLE
>> bdrdemo=# insert into thingy (id, value) VALUES (1, 'insert from node1');
>> INSERT 0 1
>>
>>  From node1:
>>
>> bdrdemo=# begin;
>> BEGIN
>> bdrdemo=# update thingy set value='update from node1' where id=1;
>> UPDATE 1
>> bdrdemo=# insert into tx_log (id, msg) values (1, 'tx log insert from 
>> node1');
>> INSERT 0 1
>> bdrdemo=# commit;
>> COMMIT
>>
>> Simultaneously from node2:
>>
>> bdrdemo=# begin;
>> BEGIN
>> bdrdemo=# update thingy set value='update from node2' where id=1;
>> UPDATE 1
>> bdrdemo=# insert into tx_log (id, msg) values (2, 'tx log insert from 
>> node2');
>> INSERT 0 1
>> bdrdemo=# commit;
>> COMMIT
>>
>> ...
>>
>> bdrdemo=# select * from tx_log ;
>>   id |   msg
>> +--
>>1 | tx log insert from node1
>>2 | tx log insert from node2
>> (2 rows)
>>
>> bdrdemo=# select * from thingy ;
>>   id |   value
>> +---
>>1 | update from node2
>> (1 row)
>>
>> The conflict on the "thingy" table has resulted in node2 winning based
>> on last_update wins default resolution.  However, both inserts have
>> applied.  My expectation is that the entire TX applies or does not
>> apply.  This expectation is clearly wrong.
>>
>> Question is: is there a way (via a custom conflict handler) to have the
>> TX obeyed?  I can't see a way to even implement a simple bank account
>> database that changes multiple tables in a single transaction without
>> having the data end up in an inconsistent state.  Am I missing something
>> obvious here?
>>
>> Thanks in advance for any help.
>>
>> riley
>>
>
>
>
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR and TX obeyance

[Edson Richter]

BTW, I'm also looking for a "synchronous multi-master" solution... If 
you find one, please share :-)
The only solution I've found so far is a middleware that is close, the 
C-Jdbc/Sequoia, which seems not being actively maintained for a while now.


Regards,

Edson

Atenciosamente,

Edson Carlos Ericksson Richter

Em 04/01/2016 18:09, Riley Berton escreveu:

I have been experimenting with BDR and have a question about how BDR
interacts with transactions.

bdrdemo=# create table thingy (id INT, value TEXT, PRIMARY KEY(id));
CREATE TABLE
bdrdemo=# create table tx_log(id INT, msg TEXT, PRIMARY KEY(id));
CREATE TABLE
bdrdemo=# insert into thingy (id, value) VALUES (1, 'insert from node1');
INSERT 0 1

 From node1:

bdrdemo=# begin;
BEGIN
bdrdemo=# update thingy set value='update from node1' where id=1;
UPDATE 1
bdrdemo=# insert into tx_log (id, msg) values (1, 'tx log insert from node1');
INSERT 0 1
bdrdemo=# commit;
COMMIT

Simultaneously from node2:

bdrdemo=# begin;
BEGIN
bdrdemo=# update thingy set value='update from node2' where id=1;
UPDATE 1
bdrdemo=# insert into tx_log (id, msg) values (2, 'tx log insert from node2');
INSERT 0 1
bdrdemo=# commit;
COMMIT

...

bdrdemo=# select * from tx_log ;
  id |   msg
+--
   1 | tx log insert from node1
   2 | tx log insert from node2
(2 rows)

bdrdemo=# select * from thingy ;
  id |   value
+---
   1 | update from node2
(1 row)

The conflict on the "thingy" table has resulted in node2 winning based
on last_update wins default resolution.  However, both inserts have
applied.  My expectation is that the entire TX applies or does not
apply.  This expectation is clearly wrong.

Question is: is there a way (via a custom conflict handler) to have the
TX obeyed?  I can't see a way to even implement a simple bank account
database that changes multiple tables in a single transaction without
having the data end up in an inconsistent state.  Am I missing something
obvious here?

Thanks in advance for any help.

riley





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR and TX obeyance

[Edson Richter]

I think this is the nature of "async multi master"...
IMHO, It would be necessary to be "sync multi master" (with two-phase 
commit?) to get the behavior you expect.


Atenciosamente,

Edson Carlos Ericksson Richter

Em 04/01/2016 18:09, Riley Berton escreveu:

I have been experimenting with BDR and have a question about how BDR
interacts with transactions.

bdrdemo=# create table thingy (id INT, value TEXT, PRIMARY KEY(id));
CREATE TABLE
bdrdemo=# create table tx_log(id INT, msg TEXT, PRIMARY KEY(id));
CREATE TABLE
bdrdemo=# insert into thingy (id, value) VALUES (1, 'insert from node1');
INSERT 0 1

 From node1:

bdrdemo=# begin;
BEGIN
bdrdemo=# update thingy set value='update from node1' where id=1;
UPDATE 1
bdrdemo=# insert into tx_log (id, msg) values (1, 'tx log insert from node1');
INSERT 0 1
bdrdemo=# commit;
COMMIT

Simultaneously from node2:

bdrdemo=# begin;
BEGIN
bdrdemo=# update thingy set value='update from node2' where id=1;
UPDATE 1
bdrdemo=# insert into tx_log (id, msg) values (2, 'tx log insert from node2');
INSERT 0 1
bdrdemo=# commit;
COMMIT

...

bdrdemo=# select * from tx_log ;
  id |   msg
+--
   1 | tx log insert from node1
   2 | tx log insert from node2
(2 rows)

bdrdemo=# select * from thingy ;
  id |   value
+---
   1 | update from node2
(1 row)

The conflict on the "thingy" table has resulted in node2 winning based
on last_update wins default resolution.  However, both inserts have
applied.  My expectation is that the entire TX applies or does not
apply.  This expectation is clearly wrong.

Question is: is there a way (via a custom conflict handler) to have the
TX obeyed?  I can't see a way to even implement a simple bank account
database that changes multiple tables in a single transaction without
having the data end up in an inconsistent state.  Am I missing something
obvious here?

Thanks in advance for any help.

riley





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR and TX obeyance

[Riley Berton]

I have been experimenting with BDR and have a question about how BDR
interacts with transactions.

bdrdemo=# create table thingy (id INT, value TEXT, PRIMARY KEY(id));
CREATE TABLE
bdrdemo=# create table tx_log(id INT, msg TEXT, PRIMARY KEY(id));
CREATE TABLE
bdrdemo=# insert into thingy (id, value) VALUES (1, 'insert from node1');
INSERT 0 1

From node1:

bdrdemo=# begin;
BEGIN
bdrdemo=# update thingy set value='update from node1' where id=1;
UPDATE 1 
bdrdemo=# insert into tx_log (id, msg) values (1, 'tx log insert from node1');
INSERT 0 1
bdrdemo=# commit;
COMMIT

Simultaneously from node2:

bdrdemo=# begin;
BEGIN
bdrdemo=# update thingy set value='update from node2' where id=1;
UPDATE 1
bdrdemo=# insert into tx_log (id, msg) values (2, 'tx log insert from node2');
INSERT 0 1
bdrdemo=# commit;
COMMIT

...

bdrdemo=# select * from tx_log ;
 id |   msg
+--
  1 | tx log insert from node1
  2 | tx log insert from node2
(2 rows)

bdrdemo=# select * from thingy ;
 id |   value
+---
  1 | update from node2
(1 row)

The conflict on the "thingy" table has resulted in node2 winning based
on last_update wins default resolution.  However, both inserts have
applied.  My expectation is that the entire TX applies or does not
apply.  This expectation is clearly wrong.

Question is: is there a way (via a custom conflict handler) to have the
TX obeyed?  I can't see a way to even implement a simple bank account
database that changes multiple tables in a single transaction without
having the data end up in an inconsistent state.  Am I missing something
obvious here?

Thanks in advance for any help.

riley

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR and synchronous replication

[Craig Ringer]

On 26 December 2015 at 19:29, Nikhil  wrote:

> Hello,
>
> i am experimenting BDR project. As BDR does asynchronous replication, i
> have a query regarding bdr.synchronous_commit=on option.
>
> Will aforementioned configuration in postgresql.conf makes the replication
> synchronous?
>

No. It does not make replication synchronous. It makes the BDR downstream
apply process commit and flush to disk each transaction it replays as soon
as it has replayed it, instead of waiting until it's time to send replay
feedback to the upstream. This means there's a pause for fsync with each
applied xact, which performs terribly for high transaction rates. By
default with synchronous_commit off BDR instead sends confirmation lazily
as the transactions actually flush to disk.

See bdr_send_feedback in the source code for details.

If you want synchronous replication you can use synchronous_standby_names
to make replication to exactly one peer server synchronous (ish). You have
to set an application_name in the connection dsn on the peer server so you
can identify it in synchronous_standby_names. It is not necessary to set
bdr.synchronous_commit = on. Doing so may result in quicker confirmation of
commit on the upstream at the cost of lower overall performance.

There is no way to get n-safe synchronous replication because PostgreSQL
doesn't support that, and BDR uses PostgreSQL's synchronous replication
support. You can only have one synchronous replica active at a time and
PostgreSQL will automatically choose the first reachable one in the replica
list.

> Does this require any other setting? any side effect for using this setup?
>
The usual downsides of synchronous replication. A potentially significant
performance hit, the upstream stopping when the downstream isn't reachable,
etc.

Personally I don't think it's a good idea to try to combine BDR and
synchronous replication. There are too many pitfalls, especially around the
1-synchronous-replica limitation. It'll be better if/when core gets support
for n-safe synchronous replication.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

[GENERAL] BDR and synchronous replication

[Nikhil]

Hello,

i am experimenting BDR project. As BDR does asynchronous replication, i
have a query regarding bdr.synchronous_commit=on option.

Will aforementioned configuration in postgresql.conf makes the replication
synchronous?

Does this require any other setting? any side effect for using this setup?

Best Regards,

Nikhil

Re: [GENERAL] BDR error while adding 3rd node to cluster

[Amit Bondwal]

Thanks you very much Craig for this help. This is the error due to dns
entry I pointed pg3 on two different IP.
I regret for wasting your precious time that I could not pick such a small
thing.

thanks again.

On Thu, Dec 24, 2015 at 3:55 PM, Craig Ringer  wrote:

>
>
> On 22 December 2015 at 17:00, Amit Bondwal  wrote:
>
>
>> I remove all the bdr packages and reinstall it and setup again the BDR
>> cluster, still facing the same issue on 3rd node.
>>
>
> At this point I'd really need to see the steps taken, in detail, to get to
> that point from a clean initial state.
>
> If I had to guess right now I'd say that the host pg3 isn't actually the
> node node3 that you are connected to when you're joining the node, i.e. the
> error message is correctly telling you that you've given the wrong external
> DSN.
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [GENERAL] BDR error while adding 3rd node to cluster

[Craig Ringer]

On 22 December 2015 at 17:00, Amit Bondwal  wrote:


> I remove all the bdr packages and reinstall it and setup again the BDR
> cluster, still facing the same issue on 3rd node.
>

At this point I'd really need to see the steps taken, in detail, to get to
that point from a clean initial state.

If I had to guess right now I'd say that the host pg3 isn't actually the
node node3 that you are connected to when you're joining the node, i.e. the
error message is correctly telling you that you've given the wrong external
DSN.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] BDR error while adding 3rd node to cluster

[Amit Bondwal]

Hi Craig,

I remove all the bdr packages and reinstall it and setup again the BDR
cluster, still facing the same issue on 3rd node.

On Tue, Dec 22, 2015 at 10:58 AM, Amit Bondwal 
wrote:

>
> On Tue, Dec 22, 2015 at 10:05 AM, Craig Ringer 
> wrote:
>
>> select * from bdr.bdr_connections;
>>
>
>
> Hi Craig, Thanks for your reply, These commands shows nothing on 3rd node.
>
>
>
> *on node3:-*hakuna=# select * from bdr.bdr_nodes;
>  node_sysid | node_timeline | node_dboid | node_status | node_name |
> node_local_dsn | node_init_from_dsn
>
> +---++-+---++
> (0 rows)
>
>  conn_sysid | conn_timeline | conn_dboid | conn_origin_sysid |
> conn_origin_timeline | conn_origin_dboid | conn_is_unidirectional |
> conn_dsn | conn_apply_delay | conn_replication_sets
>
> +---++---+--+---++--+--+---
> (0 rows)
>
>
>
> *on node1:-*
>
> hakuna=# select * from bdr.bdr_nodes;
>  node_sysid  | node_timeline | node_dboid | node_status |
> node_name |  node_local_dsn  |
> node_init_from_dsn
>
> -+---++-+---+--+--
>  6229651184159874988 | 1 |  18719 | r   |
> node1 | host=pg1 port=5432 dbname=hakuna |
>  6229651217067355961 | 1 |  17161 | r   |
> node2 | host=pg2 port=5432 dbname=hakuna | host=pg1 port=5432
> dbname=hakuna
> (2 rows)
>
> It is conflicting with ID of node 2 as it shows output of above command on
> node 1.
>
> My OS is debian Jessie and I installed it from bdr repo. Current version
> of bdr is 0.9.3
>
>
>

Re: [GENERAL] BDR error while adding 3rd node to cluster

[Amit Bondwal]

On Tue, Dec 22, 2015 at 10:05 AM, Craig Ringer 
wrote:

> select * from bdr.bdr_connections;
>


Hi Craig, Thanks for your reply, These commands shows nothing on 3rd node.



*on node3:-*hakuna=# select * from bdr.bdr_nodes;
 node_sysid | node_timeline | node_dboid | node_status | node_name |
node_local_dsn | node_init_from_dsn
+---++-+---++
(0 rows)

 conn_sysid | conn_timeline | conn_dboid | conn_origin_sysid |
conn_origin_timeline | conn_origin_dboid | conn_is_unidirectional |
conn_dsn | conn_apply_delay | conn_replication_sets
+---++---+--+---++--+--+---
(0 rows)



*on node1:-*

hakuna=# select * from bdr.bdr_nodes;
 node_sysid  | node_timeline | node_dboid | node_status | node_name
|  node_local_dsn  |node_init_from_dsn
-+---++-+---+--+--
 6229651184159874988 | 1 |  18719 | r   | node1
| host=pg1 port=5432 dbname=hakuna |
 6229651217067355961 | 1 |  17161 | r   | node2
| host=pg2 port=5432 dbname=hakuna | host=pg1 port=5432 dbname=hakuna
(2 rows)

It is conflicting with ID of node 2 as it shows output of above command on
node 1.

My OS is debian Jessie and I installed it from bdr repo. Current version of
bdr is 0.9.3

Re: [GENERAL] BDR error while adding 3rd node to cluster

[Craig Ringer]

On 21 December 2015 at 22:57, Amit Bondwal  wrote:

> Hi Everyone,
>
> I am trying to setup three node bdr cluster, I am following the quick
> start guide,
> It is working well between first and 2nd node, but When I try to add 3rd
> node, it give the below error.
>
> hakuna=# SELECT bdr.bdr_group_join(
>   local_node_name := 'node3',
>   node_external_dsn := 'host=pg3 port=5432 dbname=hakuna',
>   join_using_dsn := 'host=pg1 port=5432 dbname=hakuna'
> );
> ERROR:  node identity for node_external_dsn does not match current node
> when connecting back via remote
> DETAIL:  The dsn '' connects to a node with identity
> (6229651217067355961,1,17161) but the local node is
> (6229649404569370556,1,19247)
>

Huh. That's interesting. The dsn ''.

How'd we get there?

Can you show the output of

select * from bdr.bdr_nodes;

select * from bdr.bdr_connections;


on the new node you're trying to join?



-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

[GENERAL] BDR error while adding 3rd node to cluster

[Amit Bondwal]

Hi Everyone,

I am trying to setup three node bdr cluster, I am following the quick start
guide,
It is working well between first and 2nd node, but When I try to add 3rd
node, it give the below error.

hakuna=# SELECT bdr.bdr_group_join(
  local_node_name := 'node3',
  node_external_dsn := 'host=pg3 port=5432 dbname=hakuna',
  join_using_dsn := 'host=pg1 port=5432 dbname=hakuna'
);
ERROR:  node identity for node_external_dsn does not match current node
when connecting back via remote
DETAIL:  The dsn '' connects to a node with identity
(6229651217067355961,1,17161) but the local node is
(6229649404569370556,1,19247)
HINT:  The 'node_external_dsn' parameter must refer to the node you're
running this function from, from the perspective of the node pointed to by
join_using_dsn

I tried to delete database on 3rd node and recreate it but same error. I
also tried to connect to 2nd node as external dsn but same error.
I am able to access all three nodes to each other using psql.



-- 
Regards
Amit Bondwal

Re: [GENERAL] BDR

[Andreas Kretschmer]

Craig Ringer  wrote:

> On 15 December 2015 at 16:49, Andreas Kretschmer 
> wrote:
> 
> 
> > BDR is currently an addon for 9.4, I don't believe its available for 9.5
> > yet.
> 
> apparently, thx for the answer.
> 
> 
> Correct, there's no BDR for 9.5.
> 
> There's a pretty good chance we'll skip 9.5 entirely and deliver an improved
> BDR on top of 9.6 down the track, but that's not something that'll be 
> happening
> until 9.6 is much closer to ready.

Ok, nice to know that. Exists a mailinglist about BDR?


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR

[Craig Ringer]

On 15 December 2015 at 16:49, Andreas Kretschmer 
wrote:

> BDR is currently an addon for 9.4, I don't believe its available for 9.5
> > yet.
>
> apparently, thx for the answer.


Correct, there's no BDR for 9.5.

There's a pretty good chance we'll skip 9.5 entirely and deliver an
improved BDR on top of 9.6 down the track, but that's not something that'll
be happening until 9.6 is much closer to ready.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] BDR

[Andreas Kretschmer]

John R Pierce  wrote:

> On 12/15/2015 12:01 AM, Andreas Kretschmer wrote:
>> I would like to play with BDR, can i use my 9.5 / 9.6 installation
>> (first attempt fails) or do i have to use 9.4 stable?
>
> 9.5 is a in-development version, 9.6 doesn't even exist, why would you  
> want to use anything OTHER than the stable and reliable 9.4 unless  
> you're explicitly testing for bugs in 9.5 ?

playing with new features. (9.6 exists as devel, with great features
like parallel seq. scan)

>
> BDR is currently an addon for 9.4, I don't believe its available for 9.5  
> yet.

apparently, thx for the answer.


Regards, Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR

[John R Pierce]

On 12/15/2015 12:01 AM, Andreas Kretschmer wrote:

I would like to play with BDR, can i use my 9.5 / 9.6 installation
(first attempt fails) or do i have to use 9.4 stable?


9.5 is a in-development version, 9.6 doesn't even exist, why would you 
want to use anything OTHER than the stable and reliable 9.4 unless 
you're explicitly testing for bugs in 9.5 ?


BDR is currently an addon for 9.4, I don't believe its available for 9.5 
yet.


--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR

[Andreas Kretschmer]

Hi @ll,

I would like to play with BDR, can i use my 9.5 / 9.6 installation
(first attempt fails) or do i have to use 9.4 stable?


Regards, Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bdr manual cleanup required

[Craig Ringer]

I really couldn't say with the available information.

Can you set provide a step-by-step process by which you set up these nodes?
​

Re: [GENERAL] bdr manual cleanup required

[Sylvain MARECHAL]

Le 09/12/2015 05:18, Craig Ringer a écrit :

Are you adding more than one node at once?

BDR isn't currently smart enough to handle that. Make sure to wait 
until one node is fully synced up before adding another.

​
In other words, one shall not attemp to add a new node if the other 
nodes are not in the 'r'eady state, when more than two nodes ?


But what about if one gets this 'i' state with two nodes only? in my 
case, with two node only, in one side, both nodes had the state 'r', 
while the states were 'r' and 'i' on the other side.


Thank-you,

Sylvain


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bdr manual cleanup required

[Craig Ringer]

Are you adding more than one node at once?

BDR isn't currently smart enough to handle that. Make sure to wait until
one node is fully synced up before adding another.
​

Re: [GENERAL] bdr manual cleanup required

[Sylvain MARECHAL]

I notice this 'i' state with bdr 0.9.1 
(https://github.com/2ndQuadrant/bdr/issues/145)

But this is not the same problem as far as I understand.
In my case, I notice this problem when constantly updating the database. 
(I was not able to reproduce it with 0.9.3)


Note that I sometimes saw this 'i' state with two nodes only and 0.9.3 
version, but it didn't seem to affect the replication, even if I am not 
confortable with this ...


Sylvain

Le 08/12/2015 18:36, Selim Tuvi a écrit :
Thanks Sylvain, I ran the following on all nodes and dropped the db on 
all but the first node and rejoined them to the cluster.


Unfortunately the node_status still says "i" for the second and third 
nodes when I look at bdr.bdr_nodes under the first node.


Under the second node, the node_status has "r" for all and under the 
third node it has "i" only for the second node.


No warning or error entries in the log file on all nodes but the 
replication works only from the first node to the second and third 
nodes and from the second node to the third node.


-Selim


*From:* Sylvain Marechal [marechal.sylva...@gmail.com]
*Sent:* Sunday, December 06, 2015 4:23 AM
*To:* Selim Tuvi
*Cc:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] bdr manual cleanup required

Did you try this :

https://github.com/2ndQuadrant/bdr/issues/127 :
<<<
|BEGIN; SET LOCAL bdr.skip_ddl_locking = on; SET LOCAL 
bdr.permit_unsafe_ddl_commands = on; SET LOCAL 
bdr.skip_ddl_replication = on; SECURITY LABEL FOR bdr ON DATABASE mydb 
IS NULL; DELETE FROM bdr.bdr_connections; DELETE FROM bdr.bdr_nodes; 
SELECT bdr.bdr_connections_changed(); COMMIT; SELECT 
pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 
current_database() AND application_name LIKE '%): perdb'; |

>>>

For now, I never went into situations where I had to destroy all the 
databases in all nodes.



Sylvain


2015-12-04 20:40 GMT+01:00 Selim Tuvi <mailto:st...@ilm.com>>:


I am trying to repair a broken bdr cluster setup and so far
everything I tried failed. Under the original node that ran
bdr.bdr_group_create I am getting the following error:

2015-12-04 19:34:29.063 UTC,,,22991,,5661eac4.59cf,1,,2015-12-04
19:34:28 UTC,3/0,0,ERROR,55000,"previous init failed, manual
cleanup is required","Found bdr.bdr_nodes entry for bdr
(6224504646761731677,1,16389,) with state=i in remote
bdr.bdr_nodes","Remove all replication identifiers and slots
corresponding to this node from the init target node then drop and
recreate this database and try again",,,"bdr
(6224504646761731677,1,16389,): perdb"

Is there a way to get the cluster in a correct state without
having to drop the db?

Thanks
-Selim

Re: [GENERAL] bdr manual cleanup required

[Selim Tuvi]

Thanks Sylvain, I ran the following on all nodes and dropped the db on all but 
the first node and rejoined them to the cluster.

Unfortunately the node_status still says "i" for the second and third nodes 
when I look at bdr.bdr_nodes under the first node.

Under the second node, the node_status has "r" for all and under the third node 
it has "i" only for the second node.

No warning or error entries in the log file on all nodes but the replication 
works only from the first node to the second and third nodes and from the 
second node to the third node.

-Selim


From: Sylvain Marechal [marechal.sylva...@gmail.com]
Sent: Sunday, December 06, 2015 4:23 AM
To: Selim Tuvi
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] bdr manual cleanup required

Did you try this :

https://github.com/2ndQuadrant/bdr/issues/127 :
<<<

BEGIN;
SET LOCAL bdr.skip_ddl_locking = on;
SET LOCAL bdr.permit_unsafe_ddl_commands = on;
SET LOCAL bdr.skip_ddl_replication = on;
SECURITY LABEL FOR bdr ON DATABASE mydb IS NULL;
DELETE FROM bdr.bdr_connections;
DELETE FROM bdr.bdr_nodes;
SELECT bdr.bdr_connections_changed();
COMMIT;

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = current_database() AND application_name LIKE '%): perdb';


>>>

For now, I never went into situations where I had to destroy all the databases 
in all nodes.
[https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif]


Sylvain


2015-12-04 20:40 GMT+01:00 Selim Tuvi mailto:st...@ilm.com>>:
I am trying to repair a broken bdr cluster setup and so far everything I tried 
failed. Under the original node that ran bdr.bdr_group_create I am getting the 
following error:

2015-12-04 19:34:29.063 UTC,,,22991,,5661eac4.59cf,1,,2015-12-04 19:34:28 
UTC,3/0,0,ERROR,55000,"previous init failed, manual cleanup is required","Found 
bdr.bdr_nodes entry for bdr (6224504646761731677,1,16389,) with state=i in 
remote bdr.bdr_nodes","Remove all replication identifiers and slots 
corresponding to this node from the init target node then drop and recreate 
this database and try again",,,"bdr (6224504646761731677,1,16389,): perdb"

Is there a way to get the cluster in a correct state without having to drop the 
db?

Thanks
-Selim

Re: [GENERAL] BDR: ALTER statement hanging

[Selim Tuvi]

Thanks Craig, the problem was that (if I remember correctly) there were 
absolutely no errors or warnings logged when I issued the ALTER statement. 
Everything seemed to operate normally except that the execution never 
completed. Even the fact that the node_status was set to 'i' didn't result in 
any log messages and the replication was working as it should.

-Selim


From: Craig Ringer [cr...@2ndquadrant.com]
Sent: Sunday, December 06, 2015 7:05 PM
To: Selim Tuvi
Cc: Sylvain MARECHAL; pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR: ALTER statement hanging


​If you're not sure what's going on on a node, look at its logs.

The background worker API and PostgreSQL's lack of autonomous transactions 
makes it quite challenging for BDR workers to capture logs and expose them to 
users at the SQL level. So always, if in doubt, examine the log files.

Re: [GENERAL] BDR: ALTER statement hanging

[Craig Ringer]

​If you're not sure what's going on on a node, look at its logs.

The background worker API and PostgreSQL's lack of autonomous transactions
makes it quite challenging for BDR workers to capture logs and expose them
to users at the SQL level. So always, if in doubt, examine the log files.

Re: [GENERAL] bdr manual cleanup required

[Sylvain Marechal]

Did you try this :

https://github.com/2ndQuadrant/bdr/issues/127 :
<<<

BEGIN;
SET LOCAL bdr.skip_ddl_locking = on;
SET LOCAL bdr.permit_unsafe_ddl_commands = on;
SET LOCAL bdr.skip_ddl_replication = on;
SECURITY LABEL FOR bdr ON DATABASE mydb IS NULL;
DELETE FROM bdr.bdr_connections;
DELETE FROM bdr.bdr_nodes;
SELECT bdr.bdr_connections_changed();
COMMIT;

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = current_database() AND application_name LIKE '%): perdb';

>>>

For now, I never went into situations where I had to destroy all the
databases in all nodes.


Sylvain


2015-12-04 20:40 GMT+01:00 Selim Tuvi :

> I am trying to repair a broken bdr cluster setup and so far everything I
> tried failed. Under the original node that ran bdr.bdr_group_create I am
> getting the following error:
>
> 2015-12-04 19:34:29.063 UTC,,,22991,,5661eac4.59cf,1,,2015-12-04 19:34:28
> UTC,3/0,0,ERROR,55000,"previous init failed, manual cleanup is
> required","Found bdr.bdr_nodes entry for bdr (6224504646761731677,1,16389,)
> with state=i in remote bdr.bdr_nodes","Remove all replication identifiers
> and slots corresponding to this node from the init target node then drop
> and recreate this database and try again",,,"bdr
> (6224504646761731677,1,16389,): perdb"
>
> Is there a way to get the cluster in a correct state without having to
> drop the db?
>
> Thanks
> -Selim
>
>

[GENERAL] bdr manual cleanup required

[Selim Tuvi]

I am trying to repair a broken bdr cluster setup and so far everything I tried 
failed. Under the original node that ran bdr.bdr_group_create I am getting the 
following error:

2015-12-04 19:34:29.063 UTC,,,22991,,5661eac4.59cf,1,,2015-12-04 19:34:28 
UTC,3/0,0,ERROR,55000,"previous init failed, manual cleanup is required","Found 
bdr.bdr_nodes entry for bdr (6224504646761731677,1,16389,) with state=i in 
remote bdr.bdr_nodes","Remove all replication identifiers and slots 
corresponding to this node from the init target node then drop and recreate 
this database and try again",,,"bdr (6224504646761731677,1,16389,): perdb"

Is there a way to get the cluster in a correct state without having to drop the 
db?

Thanks
-Selim

Re: [GENERAL] BDR: ALTER statement hanging

[Selim Tuvi]

Yes, bdr_connections had the same number of rows:

deliver=# select * from bdr.bdr_connections;
 conn_sysid  | conn_timeline | conn_dboid | conn_origin_sysid | 
conn_origin_timeline | conn_origin_dboid | conn_is_unidirectional | 
  
 conn_dsn| 
conn_apply_delay | conn_replication_sets
   
-+---++---+--+---++---
-+--+---
  
 6212648563684174798 | 1 | 533136 | 0 | 
   0 | 0 | f  | 
host=pe-deliverdb-sf-01v port=5432 dbname=deliver user=deliver_admin 
password=x   |  | {default} 
  
 6223770712502831127 | 1 |  16389 | 0 | 
   0 | 0 | f  | 
host=pe-deliverdb-sing-01v port=5432 dbname=deliver user=deliver_admin 
password=x |  | {default}   

 6223800735012265413 | 1 |  16389 | 0 | 
   0 | 0 | f  | 
host=pe-deliverdb-lon-01v port=5432 dbname=deliver user=deliver_admin 
password=x  |  | {default}  
 
(3 rows)   

One other thing I noticed is that the conn_dboid is the same for two of the 
nodes. Is that normal?

-Selim


From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
on behalf of Sylvain MARECHAL [marechal.sylva...@gmail.com]
Sent: Friday, December 04, 2015 10:14 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR: ALTER statement hanging

Le 04/12/2015 18:59, Andreas Kretschmer a écrit :
>
>
> I think, the state 'i' is the main reason for your problem, because of: "i-
> Joining: The node is doing initial slot creation or an initial dump and load".
>
> But i can't tell you why this nodes are in this state.
>
>
> Regards, Andreas
>
>
Did-you check the bdr.bdr_connections table?
It should have as many lines as the bdr.bdr_nodes tables.

Sylvain


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: ALTER statement hanging

[Selim Tuvi]

Thanks, I removed the other nodes from bdr.bdr_nodes table, deleted all the 
bdr_connections and pg_replication_identifier entries, dropped the 
pg_replication_slots restarted the instance and then trying the ALTER statement 
resulted in:

ERROR:  No peer nodes or peer node count unknown, cannot acquire DDL lock
HINT:  BDR is probably still starting up, wait a while  

The only way I could issue the statement is run the following to convert the 
node to a standalone instance:

BEGIN;
SET LOCAL bdr.permit_unsafe_ddl_commands = true;
SET LOCAL bdr.skip_ddl_locking = true;
security label for 'bdr' on database deliver is '{"bdr": false}';
COMMIT;

I am still puzzled as to why the bdr_nodes node_status was reporting "i" when 
there were no errors in the logs.

-Selim


From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
on behalf of Andreas Kretschmer [andr...@a-kretschmer.de]
Sent: Friday, December 04, 2015 9:59 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR: ALTER statement hanging

> Selim Tuvi  hat am 4. Dezember 2015 um 18:46 geschrieben:
>
>
> Yes they seem to be active:
>
> deliver=# select * from pg_replication_slots;
> slot_name | plugin | slot_type | datoid |
> database | active | xmin | catalog_xmin | restart_lsn
> --++---++--++--+--+-
>  bdr_533136_6223770712502831127_1_16389__ | bdr| logical   | 533136 |
> deliver  | t  |  |   182302 | 0/9C8A5598
>  bdr_533136_6223800735012265413_1_16389__ | bdr| logical   | 533136 |
> deliver  | t  |  |   182302 | 0/9C8A5598
> (2 rows)
>
> Although when I look at bdr.bdr_nodes I see the status as still initializing
> for the other two nodes, I don't know if that could cause this problem:
>
> deliver=# select * from bdr.bdr_nodes;
>  node_sysid  | node_timeline | node_dboid | node_status |
>  node_name  |
> node_local_dsn
>  |
>  node_init_from_dsn
> -+---++-+-+---
> -+--
>  6212648563684174798 | 1 | 533136 | r   |
> pe-deliverdb-sf-01v | host=pe-deliverdb-sf-01v port=5432 dbname=deliver
> user=deliver_admin password=x   |
>  6223770712502831127 | 1 |  16389 | i   |
> pe-deliverdb-sing-01v | host=pe-deliverdb-sing-01v port=5432 dbname=deliver
> user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432
> dbname=deliver user=deliver_admin password=x
>  6223800735012265413 | 1 |  16389 | i   |
> pe-deliverdb-lon-01v | host=pe-deliverdb-lon-01v port=5432 dbname=deliver
> user=deliver_admin password=x  | host=pe-deliverdb-sf-01v port=5432
> dbname=deliver user=deliver_admin password=x
>
> -Selim
>


I think, the state 'i' is the main reason for your problem, because of: "i-
Joining: The node is doing initial slot creation or an initial dump and load".

But i can't tell you why this nodes are in this state.


Regards, Andreas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: ALTER statement hanging

[Sylvain MARECHAL]

Le 04/12/2015 18:59, Andreas Kretschmer a écrit :



I think, the state 'i' is the main reason for your problem, because of: "i-
Joining: The node is doing initial slot creation or an initial dump and load".

But i can't tell you why this nodes are in this state.


Regards, Andreas



Did-you check the bdr.bdr_connections table?
It should have as many lines as the bdr.bdr_nodes tables.

Sylvain


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: ALTER statement hanging

[Andreas Kretschmer]

> Selim Tuvi  hat am 4. Dezember 2015 um 18:46 geschrieben:
> 
> 
> Yes they seem to be active:
> 
> deliver=# select * from pg_replication_slots;
> slot_name | plugin | slot_type | datoid |
> database | active | xmin | catalog_xmin | restart_lsn 
> --++---++--++--+--+-
>  bdr_533136_6223770712502831127_1_16389__ | bdr| logical   | 533136 |
> deliver  | t  |  |   182302 | 0/9C8A5598  
>  bdr_533136_6223800735012265413_1_16389__ | bdr| logical   | 533136 |
> deliver  | t  |  |   182302 | 0/9C8A5598  
> (2 rows) 
> 
> Although when I look at bdr.bdr_nodes I see the status as still initializing
> for the other two nodes, I don't know if that could cause this problem:
> 
> deliver=# select * from bdr.bdr_nodes;
>  node_sysid  | node_timeline | node_dboid | node_status |
>  node_name  |
> node_local_dsn
>  |
>  node_init_from_dsn
> -+---++-+-+---
> -+--
>  6212648563684174798 | 1 | 533136 | r   |
> pe-deliverdb-sf-01v | host=pe-deliverdb-sf-01v port=5432 dbname=deliver
> user=deliver_admin password=x   |
>  6223770712502831127 | 1 |  16389 | i   |
> pe-deliverdb-sing-01v | host=pe-deliverdb-sing-01v port=5432 dbname=deliver
> user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432
> dbname=deliver user=deliver_admin password=x
>  6223800735012265413 | 1 |  16389 | i   |
> pe-deliverdb-lon-01v | host=pe-deliverdb-lon-01v port=5432 dbname=deliver
> user=deliver_admin password=x  | host=pe-deliverdb-sf-01v port=5432
> dbname=deliver user=deliver_admin password=x
> 
> -Selim
> 


I think, the state 'i' is the main reason for your problem, because of: "i-
Joining: The node is doing initial slot creation or an initial dump and load".

But i can't tell you why this nodes are in this state.


Regards, Andreas


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: ALTER statement hanging

[Selim Tuvi]

Yes they seem to be active:

deliver=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | 
database | active | xmin | catalog_xmin | restart_lsn 
--++---++--++--+--+-
 bdr_533136_6223770712502831127_1_16389__ | bdr| logical   | 533136 | 
deliver  | t  |  |   182302 | 0/9C8A5598  
 bdr_533136_6223800735012265413_1_16389__ | bdr| logical   | 533136 | 
deliver  | t  |  |   182302 | 0/9C8A5598  
(2 rows) 

Although when I look at bdr.bdr_nodes I see the status as still initializing 
for the other two nodes, I don't know if that could cause this problem:

deliver=# select * from bdr.bdr_nodes;
 node_sysid  | node_timeline | node_dboid | node_status |  
node_name  | 
node_local_dsn
 |  
node_init_from_dsn
-+---++-+-+---
-+--
 6212648563684174798 | 1 | 533136 | r   | 
pe-deliverdb-sf-01v | host=pe-deliverdb-sf-01v port=5432 dbname=deliver 
user=deliver_admin password=x   |
 6223770712502831127 | 1 |  16389 | i   | 
pe-deliverdb-sing-01v | host=pe-deliverdb-sing-01v port=5432 dbname=deliver 
user=deliver_admin password=x | host=pe-deliverdb-sf-01v port=5432 
dbname=deliver user=deliver_admin password=x
 6223800735012265413 | 1 |  16389 | i   | 
pe-deliverdb-lon-01v | host=pe-deliverdb-lon-01v port=5432 dbname=deliver 
user=deliver_admin password=x  | host=pe-deliverdb-sf-01v port=5432 
dbname=deliver user=deliver_admin password=x

-Selim


From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
on behalf of Andreas Kretschmer [akretsch...@spamfence.net]
Sent: Thursday, December 03, 2015 10:49 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR: ALTER statement hanging

Selim Tuvi  wrote:

> Hi, I am running a three node BDR cluster. BDR version is 0.9.3. Postgres
> version is 9.4.5.
>
> With 0.9.2, I used to be able to issue ALTER statements using psql and it 
> would
> go through. This time it is just hanging. The statement is this:

for ddl-commands all nodes MUST be active in replication, so have you
checked that in pg_replication_slots?



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: ALTER statement hanging

[Andreas Kretschmer]

Selim Tuvi  wrote:

> Hi, I am running a three node BDR cluster. BDR version is 0.9.3. Postgres
> version is 9.4.5.
> 
> With 0.9.2, I used to be able to issue ALTER statements using psql and it 
> would
> go through. This time it is just hanging. The statement is this:

for ddl-commands all nodes MUST be active in replication, so have you
checked that in pg_replication_slots?



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR: ALTER statement hanging

[Selim Tuvi]

Hi, I am running a three node BDR cluster. BDR version is 0.9.3. Postgres 
version is 9.4.5.

With 0.9.2, I used to be able to issue ALTER statements using psql and it would 
go through. This time it is just hanging. The statement is this:

alter table pts alter column shot drop not null;

I also tried to add a column but that hangs as well:

alter table pts add column shot_tmp text;

Thanks
-Selim

Re: [GENERAL] BDR and Backup and Recovery

[Craig Ringer]

On 19 November 2015 at 00:54, Will McCormick  wrote:


> The below is from the 0.9.3 BDR documentation:
>
> "Because logical replication is only supported in streaming mode (rather
> than WAL archiving) it isn't suitable for point-in-time recovery. Logical
> replication may be used in conjunction with streaming physical replication
> and/or PITR, though; it is not necessary to choose one or the other."
>
> Am I misinterpreting that BDR uses Logical Decoding and as such I cannot
> perform PITR?
>

The point is that you cannot use the logical decoding data stream for
point-in-time recovery. Nothing stops you archiving WAL like normal from a
node that's participating in logical replication as an upstream and/or
downstream. You just can't use the logical replication data stream its self
for that purpose. Sounds like I need to clarify that part of the docs.

Note the caveats in my prior mail re PITR and BDR, though; you can't just
PITR-restore a replacement for a failed node and have it catch up and
rejoin replication.

Regarding logical PITR: Theoretically we could actually save a base pg_dump
and a change stream as logical changes from pg_recvlogical, then use that
for transaction-level logical PITR. It's not impossible, but it'd require
new tools and require changes to BDR/UDR to allow the stream to be applied.
Nobody's written them yet. I don't have any plans to do this in the near to
mid term.

It'd be an interesting project to build with pglogical. Its protocol is
better suited to this than BDR's. You could do selective PITR of just a
subset of tables you were interested in. If anyone's keen to tackle that,
get in touch and I'll see if I can offer any help.


>> I don't know why PITR wouldn't work with BDR, other than you can't use
>> binary backups across incompatible versions and BDR might be considered
>> incompatible with community Postgres. I would think it should still work
>> fine if you try to restore to a BDR server.
>>
>
It does, with the caveat that it can't be a drop-in replacement for a
failed node due to the timeline increment. The data is there, but it won't
participate in replication. See the steps outlined in my prior mail for
details.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] BDR and Backup and Recovery

[Craig Ringer]

On 18 November 2015 at 23:46, Will McCormick  wrote:

> What viable options exist for Backup & Recovery in a BDR environment? From
> the reading I have done PITR recovery is not an option with BDR. It's
> important to preface this that I have almost no exposure to postgres backup
> and recovery. Is PITR not an option with BDR?
>
> If a user fat fingers something and deletes records from a table without a
> where clause what is the correct course of action is to recover as much
> data as possible. What type of backup do I require to restore as much data
> as possible before the incident in a BDR environment.
>
> Sorry for such an open ended question. :D I'm continuing to read as I
> solicit feedback.
>
> Is there a document outlining recovery with BDR?
>

Not yet, and that's a signficant oversight.

There are really two options:

* Periodic dumps with pg_dump; or
* PITR, like with PgBarman

Both have associated challenges.

Importantly, you *cannot* run a physical streaming replica of a node and
promote it to replace a failed node. I'll explain why below.


PG_DUMP
---

The simplest option is to take periodic dumps of one of the nodes. If you
have to restore, you tear down the whole system, create a new standalone
node, restore the dump, strip out the old bdr.bdr_nodes and
bdr.bdr_connections data, then bring up a new cluster with the restored
node as the first node.

0.10.0 will include a built-in function to strip all BDR state from a node
and turn it back into a standalone Pg database, which will make this
easier. For now, see https://github.com/2ndQuadrant/bdr/issues/127 for
steps to de-BDR-ize a database.



PITR
---

Alternately you can use the usual physical WAL archiving and base backup
method.

If you have to restore to recover a node it cannot just rejoin the cluster.
Its connections will be rejected because its timeline has changed. This is
because the other peers might've replayed data to the old node that has
been discarded, and will not be replayed again to the restored node. So
it'd create a gap in history and as a result, divergence between nodes.

Instead you must re-clone the node from another still-alive node.

If the whole cluster is lost you you can do a PITR restore, strip all BDR
state from the restored database, then bring it up as the first node in a
new cluster, exactly as if you'd restored a dump.


Why can't you have local physical replicas for node failover?
---

It'd be nice to be able to have local streaming replicas for each node in a
distributed setup. That way if you lose a node, instead of having to
re-clone it over a possibly slow/expensive WAN link, you can just promote
the standby.

This isn't currently possible.  The main reason is that PostgreSQL does not
replay replication state (and in 9.5, replication identifier) state and
replay it to standbys. The standby node that gets promoted has no idea what
the replay position of the BDR peer nodes is or what position it had
replayed to from its peers. It could replay data twice, or miss data, and
the same could happen to its peers. Divergence would result.

To fix this we need PostgreSQL to replicate slot and replication identifier
state to physical streaming replicas. It'd be usable for PITR too, that
way. There's work afoot to make that possible in 9.6, but it's never going
to be possible in 9.4-based BDR, so you can't use a streaming replica
standby to replace a failed node without a whole-cluster rebuild.

(There are more complexities here, too, regarding async replicas, slots
that get advanced past the point the replica has data for, etc. We need a
way to delay advancing a peer's slot until we've confirmed the local
streaming replica has committed.)

See https://github.com/2ndQuadrant/bdr/issues/98



REPLICATION SETS


If you have replication sets where no single node has complete information,
it's harder.

Neither pg_dump or WAL archiving for PITR can capture non-replicated tables
that aren't on the local node. So if you have a complicated arrangement of
replication sets you have to do some hoop-jumping with pg_dump and
scripting to make sure you get a complete set of dumps of all your tables
in different replication sets on different nodes. Recovery in this case
consists of creating a new cluster, restoring the dump from one node to it,
then configuring the replication sets on each other node and restoring the
separately-dumped node-local tables to those node(s). The gentler DDL lock
in 0.10.0 should make it possible to quiesce writes and force global
consistency for long enough to acquire a snapshot on each node so you can
get consistent dumps even with replication sets, but there's still a fair
bit of manual work involved.

If you're using PITR the concept is similar. You PITR-restore one node,
strip all BDR configuration from it to make it back into a standalone DB,
then use it to set up a new BDR cluster with all new nodes. On the other
nodes you have to restore them temporarily, dump the tables that aren't 

Re: [GENERAL] BDR and Backup and Recovery

[Adrian Klaver]

On 11/18/2015 09:31 AM, Will McCormick wrote:

Ccing list

Thanks Adrian. I think I have it

Lets say we have 2 nodes:

Node A
Node B


GOOD

Application Writes only occurring against Node A

1) Node A Base Backup taken
2) User Error occurs that replicates

Can restore and Recover Node A to PITR before 2)


BAD

1) Writes at Node A
2) Backups of Node A and Node B taken
3) Hardware Failure on Node A
4) Traffic now on Node B
5) Node B user error
6) Restore of Node B from 2) possible

As logs not shipped from Node A to Node B, PITR would only have a
partial view?

Is this right?


Someone more versed in BDR than I will need to comment on the above. 
Though it seems to me a possible solution would be to have a third 
machine that has WAL file archive directories for each node. This could 
get complicated though. First keeping the WAL files from each server 
going to the correct directory. Second, determining which node in the 
universe of nodes you want do PITR on.




On Wed, Nov 18, 2015 at 12:12 PM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 11/18/2015 08:54 AM, Will McCormick wrote:

Re-sending to group as well Jim :D

Regarding testing backups, Well said Jim. Thanks for taking the
time to
respond. I will test regularly whatever we decide to put in place.

The below is from the 0.9.3 BDR documentation:

"Because logical replication is only supported in streaming mode
(rather
than WAL archiving) it isn't suitable for point-in-time recovery.
Logical replication may be used in conjunction with streaming
physical
replication and/or PITR, though; it is not necessary to choose
one or
the other."

Am I misinterpreting that BDR uses Logical Decoding and as such
I cannot
perform PITR?


As I read it as, you can not use the BDR stream to do PITR, if for
no other reason then that it can be a subset of a database or
database cluster. Further reason, it does not transfer WAL files
that have the entire picture of the database cluster. As the above
says though, there is nothing stopping you from doing WAL
archiving/PITR in parallel to the BDR stream.


On Wed, Nov 18, 2015 at 11:19 AM, Jim Nasby
mailto:jim.na...@bluetreble.com>
>> wrote:

 On 11/18/15 9:46 AM, Will McCormick wrote:

 What viable options exist for Backup & Recovery in a BDR
 environment?
   From the reading I have done PITR recovery is not an
option
 with BDR.
 It's important to preface this that I have almost no
exposure to
 postgres backup and recovery. Is PITR not an option
with BDR?

 If a user fat fingers something and deletes records
from a table
 without
 a where clause what is the correct course of action is
to recover as
 much data as possible. What type of backup do I require to
 restore as
 much data as possible before the incident in a BDR
environment.

 Sorry for such an open ended question. :D I'm
continuing to read
 as I
 solicit feedback.

 Is there a document outlining recovery with BDR?


 I don't know why PITR wouldn't work with BDR, other than
you can't
 use binary backups across incompatible versions and BDR
might be
 considered incompatible with community Postgres. I would
think it
 should still work fine if you try to restore to a BDR server.

 That said, remember that if you are not regularly (preferably
 automatically) testing your backups by doing a restore and
testing
 the restore, then you don't have a backup. You have a hope
and a
 prayer. :)
 --
 Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
 Experts in Analytics, Data Architecture and PostgreSQL
 Data in Trouble? Get it in Treble! http://BlueTreble.com




--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR and Backup and Recovery

[Jim Nasby]

On 11/18/15 10:53 AM, Will McCormick wrote:

Regarding testing backups, Well said Jim. Thanks for taking the time to
respond. I will test regularly whatever we decide to put in place.

The below is from the 0.9.3 BDR documentation:

"Because logical replication is only supported in streaming mode (rather
than WAL archiving) it isn't suitable for point-in-time recovery.
Logical replication may be used in conjunction with streaming physical
replication and/or PITR, though; it is not necessary to choose one or
the other."

Am I misinterpreting that BDR uses Logical Decoding and as such I cannot
perform PITR?


Please keep replies on-list, and don't top-post. :)

What that's saying is that you can't use logical decoding (which BDR 
uses) as a backup mechanism. That doesn't mean you can't use PITR. The 
only thing PITR really has in common with Logical Decoding is that they 
both use WAL.


So my expectation is (I'm not a BDR expert) that you can backup a BDR 
database just like any other.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR and Backup and Recovery

[Adrian Klaver]

On 11/18/2015 08:54 AM, Will McCormick wrote:

Re-sending to group as well Jim :D

Regarding testing backups, Well said Jim. Thanks for taking the time to
respond. I will test regularly whatever we decide to put in place.

The below is from the 0.9.3 BDR documentation:

"Because logical replication is only supported in streaming mode (rather
than WAL archiving) it isn't suitable for point-in-time recovery.
Logical replication may be used in conjunction with streaming physical
replication and/or PITR, though; it is not necessary to choose one or
the other."

Am I misinterpreting that BDR uses Logical Decoding and as such I cannot
perform PITR?


As I read it as, you can not use the BDR stream to do PITR, if for no 
other reason then that it can be a subset of a database or database 
cluster. Further reason, it does not transfer WAL files that have the 
entire picture of the database cluster. As the above says though, there 
is nothing stopping you from doing WAL archiving/PITR in parallel to the 
BDR stream.




On Wed, Nov 18, 2015 at 11:19 AM, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote:

On 11/18/15 9:46 AM, Will McCormick wrote:

What viable options exist for Backup & Recovery in a BDR
environment?
  From the reading I have done PITR recovery is not an option
with BDR.
It's important to preface this that I have almost no exposure to
postgres backup and recovery. Is PITR not an option with BDR?

If a user fat fingers something and deletes records from a table
without
a where clause what is the correct course of action is to recover as
much data as possible. What type of backup do I require to
restore as
much data as possible before the incident in a BDR environment.

Sorry for such an open ended question. :D I'm continuing to read
as I
solicit feedback.

Is there a document outlining recovery with BDR?


I don't know why PITR wouldn't work with BDR, other than you can't
use binary backups across incompatible versions and BDR might be
considered incompatible with community Postgres. I would think it
should still work fine if you try to restore to a BDR server.

That said, remember that if you are not regularly (preferably
automatically) testing your backups by doing a restore and testing
the restore, then you don't have a backup. You have a hope and a
prayer. :)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR and Backup and Recovery

[Will McCormick]

Re-sending to group as well Jim :D

Regarding testing backups, Well said Jim. Thanks for taking the time to
respond. I will test regularly whatever we decide to put in place.

The below is from the 0.9.3 BDR documentation:

"Because logical replication is only supported in streaming mode (rather
than WAL archiving) it isn't suitable for point-in-time recovery. Logical
replication may be used in conjunction with streaming physical replication
and/or PITR, though; it is not necessary to choose one or the other."

Am I misinterpreting that BDR uses Logical Decoding and as such I cannot
perform PITR?

On Wed, Nov 18, 2015 at 11:19 AM, Jim Nasby 
wrote:

> On 11/18/15 9:46 AM, Will McCormick wrote:
>
>> What viable options exist for Backup & Recovery in a BDR environment?
>>  From the reading I have done PITR recovery is not an option with BDR.
>> It's important to preface this that I have almost no exposure to
>> postgres backup and recovery. Is PITR not an option with BDR?
>>
>> If a user fat fingers something and deletes records from a table without
>> a where clause what is the correct course of action is to recover as
>> much data as possible. What type of backup do I require to restore as
>> much data as possible before the incident in a BDR environment.
>>
>> Sorry for such an open ended question. :D I'm continuing to read as I
>> solicit feedback.
>>
>> Is there a document outlining recovery with BDR?
>>
>
> I don't know why PITR wouldn't work with BDR, other than you can't use
> binary backups across incompatible versions and BDR might be considered
> incompatible with community Postgres. I would think it should still work
> fine if you try to restore to a BDR server.
>
> That said, remember that if you are not regularly (preferably
> automatically) testing your backups by doing a restore and testing the
> restore, then you don't have a backup. You have a hope and a prayer. :)
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

Re: [GENERAL] BDR and Backup and Recovery

[Jim Nasby]

On 11/18/15 9:46 AM, Will McCormick wrote:

What viable options exist for Backup & Recovery in a BDR environment?
 From the reading I have done PITR recovery is not an option with BDR.
It's important to preface this that I have almost no exposure to
postgres backup and recovery. Is PITR not an option with BDR?

If a user fat fingers something and deletes records from a table without
a where clause what is the correct course of action is to recover as
much data as possible. What type of backup do I require to restore as
much data as possible before the incident in a BDR environment.

Sorry for such an open ended question. :D I'm continuing to read as I
solicit feedback.

Is there a document outlining recovery with BDR?


I don't know why PITR wouldn't work with BDR, other than you can't use 
binary backups across incompatible versions and BDR might be considered 
incompatible with community Postgres. I would think it should still work 
fine if you try to restore to a BDR server.


That said, remember that if you are not regularly (preferably 
automatically) testing your backups by doing a restore and testing the 
restore, then you don't have a backup. You have a hope and a prayer. :)

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR and Backup and Recovery

[Will McCormick]

What viable options exist for Backup & Recovery in a BDR environment? From
the reading I have done PITR recovery is not an option with BDR. It's
important to preface this that I have almost no exposure to postgres backup
and recovery. Is PITR not an option with BDR?

If a user fat fingers something and deletes records from a table without a
where clause what is the correct course of action is to recover as much
data as possible. What type of backup do I require to restore as much data
as possible before the incident in a BDR environment.

Sorry for such an open ended question. :D I'm continuing to read as I
solicit feedback.

Is there a document outlining recovery with BDR?

Re: [GENERAL] bdr appears to be trying to replicate to itself

[Cj B]

> On 17 November 2015 at 00:33, Cj B  > wrote:
> 
> This doesn't explain how the system got into this state. For that it'd really 
> be necessary to see the steps taken during setup. BDR tries to protect 
> against attempts to replicate-from-self. Presumably there's an oversight in 
> those checks. If you're able to reproduce this state I'd like to hear details 
> on how.

Thanks for the help that seems to have done it. I’m not sure how it got into 
this state either. I do have the commands still that I ran but I doubt that’ll 
help much. What’s strange is that everything was working fine since May then on 
Oct 29th it appears to have started keeping copies of the pg_xlog, so something 
must have happened, but sadly I don’t know what. 

I’ll keep an eye on it to see if it happens again.

Re: [GENERAL] bdr appears to be trying to replicate to itself

[Craig Ringer]

On 17 November 2015 at 00:33, Cj B  wrote:


> select pg_drop_replication_slot(‘bdr_16385_6188730679935789649_1_16385__’)
>

Correct.


> What impact will this have?


If the slot is unused, it'll allow the WAL that's being held by the slot to
be removed. It'll also unpin the catalog xmin to allow autovacuum to clean
up dead tuples in the catalogs.

This doesn't explain how the system got into this state. For that it'd
really be necessary to see the steps taken during setup. BDR tries to
protect against attempts to replicate-from-self. Presumably there's an
oversight in those checks. If you're able to reproduce this state I'd like
to hear details on how.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] bdr appears to be trying to replicate to itself

[Cj B]

Hi,

Yes, I posted on github because I wasn’t sure where to post. And the reason I’m 
posting here is because I’m not clear about the answer "Drop the slot 
bdr_16385_6188730679935789649_1_16385__ on the first host.”

Do this just mean to 
select pg_drop_replication_slot(‘bdr_16385_6188730679935789649_1_16385__’)

What impact will this have? 

Thanks
Cj B

> On Nov 16, 2015, at 8:31 AM, Alvaro Herrera  wrote:
> 
> Cj B wrote:
>> I noticed a very strange issue starting about 20 days ago and my pg_xlog has 
>> just been filling up since then.
> 
> For reference, this was also asked on github, and answered there.
> See https://github.com/2ndQuadrant/bdr/issues/143
> 
> 
> -- 
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bdr appears to be trying to replicate to itself

[Alvaro Herrera]

Cj B wrote:
> I noticed a very strange issue starting about 20 days ago and my pg_xlog has 
> just been filling up since then.

For reference, this was also asked on github, and answered there.
See https://github.com/2ndQuadrant/bdr/issues/143


-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] bdr appears to be trying to replicate to itself

[Cj B]

I noticed a very strange issue starting about 20 days ago and my pg_xlog has 
just been filling up since then.

```
HOST A: 
postgres=# select * from pg_replication_slots;
slot_name| plugin | slot_type | datoid | 
database  | active | xmin | catalog_xmin | restart_lsn
-++---++---++--+--+-
 bdr_16385_6188730679935789649_1_16385__ | bdr| logical   |  16385 | 
database_name | f  |  | 28174997 | 25/7677F300
 bdr_16385_6188733518371128845_2_16385__ | bdr| logical   |  16385 | 
database_name | t  |  | 38613316 | 41/7B7EDF80

select bdr.bdr_get_local_nodeid();
 bdr_get_local_nodeid
---
 (6188730679935789649,1,16385)

SELECT slot_name, database, active, 
pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS 
retained_bytes  FROM pg_replication_slots WHERE plugin = 'bdr';
slot_name| database  | active | retained_bytes
-+---++
 bdr_16385_6188730679935789649_1_16385__ | database_name | f  |   
120353015152
 bdr_16385_6188733518371128845_2_16385__ | database_name | t  |   
2288
(2 rows)
```

HOST B: 

```
select * from pg_replication_slots;
slot_name| plugin | slot_type | datoid | 
database  | active | xmin | catalog_xmin | restart_lsn
-++---++---++--+--+-
 bdr_16385_6188730679935789649_1_16385__ | bdr| logical   |  16385 | 
database_name | t  |  |  3499719 | 3/B53F00A8

select bdr.bdr_get_local_nodeid();
 bdr_get_local_nodeid
---
 (6188733518371128845,2,16385)

SELECT slot_name, database, active, 
pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS 
retained_bytes  FROM pg_replication_slots WHERE plugin = 'bdr';
slot_name| database  | active | retained_bytes
-+---++
 bdr_16385_6188730679935789649_1_16385__ | database_name | t  |  
68736
```

So it almost looks like HOST A is trying to replicate to itself since the 
replication_slots has the same node_id of itself and it's just filling up the 
pg_xlog.

This server has been setup since mid-may and I haven't added any new nodes but 
I did upgrade just this past weekend before I noticed there was a problem:
dpkg -l | grep '^ii' | grep postgre
ii  pgdg-keyring2014.1  all 
 keyring for apt.postgresql.org
ii  postgresql-bdr-9.4  9.4.5-1trusty   
amd64object-relational SQL database, version 9.4 server
ii  postgresql-bdr-9.4-bdr-plugin   0.9.3-1trusty   
amd64BDR Plugin for PostgreSQL-BDR 9.4
ii  postgresql-bdr-client-9.4   9.4.5-1trusty   
amd64front-end programs for PostgreSQL-BDR 9.4
ii  postgresql-bdr-contrib-9.4  9.4.5-1trusty   
amd64additional facilities for PostgreSQL
ii  postgresql-client-common170.pgdg14.04+1 all 
 manager for multiple PostgreSQL client versions
ii  postgresql-common   170.pgdg14.04+1 all 
 PostgreSQL database-cluster manager
ii  postgresql-contrib  9.4+170.pgdg14.04+1 all 
 additional facilities for PostgreSQL (supported version)

Before I was on postgresql-bdr-client-9.4   9.4.4-1trusty 

Can anyone help me fix this? I'm running out of HD space.

Thanks!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR - Remove & Join

[Craig Ringer]

On 10 November 2015 at 05:10, Will McCormick  wrote:

> I then run a script which I used to setup replication before removal. The
> problem I encounter is node B after join in bdr.bdr_nodes is stuck in status
> 'c'.

[snip]

> We are using the following version of bdr: 0.9.2.0

Please update to 0.9.3, which fixes this issue, per
https://github.com/2ndQuadrant/bdr/issues/126

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR - Remove & Join

[Will McCormick]

I have a script which is meant to remove a node from bdr replication. Two
nodes in replication node A and node B. The script does the following.

1.  On node A - SELECT bdr.bdr_part_by_node_names(ARRAY['node B']);
2.  On node A -Checks that the node_status is marked as 'k' in bdr.bdr_nodes
3.  I then drop the database on node B
4. On node A I remove all nodes from bdr.bdr_nodes which have a status 'k'

I then run a script which I used to setup replication before removal. The
problem I encounter is node B after join in bdr.bdr_nodes is stuck in
status 'c'.

Any advice here would be appricated. I'm not sure if I need to drop all
databases and reinstall postgres instead of step 3. Or f this should be
enough?

We are using the following version of bdr bdr|16385 |
11 | f  | 0.9.2.0|
{18080,18095,18108,18143,18173,18183,18192,18199,18212,18281} |
{"","","","","","","","","",""} according to pg_extension.

Please let me know if there is further information I can provide to
troubleshoot or if this is a known issue.

Regards and thank you.

Re: [GENERAL] BDR: SSL error: bad write retry

[Florin Andrei]

Nevermind, this was fixed with:

ssl_renegotiation_limit = 0

--
Florin Andrei
http://florin.myip.org/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR: SSL error: bad write retry

[Florin Andrei]

BDR-0.9.3 and PG-9.4.4 on Ubuntu 14.04

Two nodes, BDR replication. Cluster is newly created, no nodes have been 
removed from it. Making/deleting small tables works well across the 
cluster.


Now I'm trying to pg_restore a larger database from another system 
(pg_dump output file is 3.1 GB compressed). I am running pg_restore on 
one node in the cluster, and hoping that BDR would replicate changes to 
the other node.


It went well through creating schemas, extensions, comments, views, 
functions, sequences and tables. It imported data into tables. Now it's 
trying to create indexes - but it takes a very long time and I see 
errors in the logs.


I see periodic data transfers between instances. CPU usage shoots up, 
then back down. There's a cycle of a few minutes where these things 
occur.


On the instance where I'm running pg_restore, I see this in the logs:

###
2015-11-03 21:45:35.328 
UTC,"postgres","pgmirror",4918,"10.1.1.169:36334",56392a26.1336,5,"idle",2015-11-03 21:41:58 UTC,5/146,0,LOG,08P01,"SSL error: bad write retry","slot ""bdr_16387_6212727469166484615_1_16387__"", output plugin ""bdr"", in the change callback, associated LSN 0/2B29E50""bdr (6212727469166484615,1,16387,):receive"
2015-11-03 21:45:35.328 
UTC,"postgres","pgmirror",4918,"10.1.1.169:36334",56392a26.1336,6,"idle",2015-11-03 21:41:58 UTC,5/146,0,LOG,08006,"could not receive data from client: Connection reset by peer","slot ""bdr_16387_6212727469166484615_1_16387__"", output plugin ""bdr"", in the change callback, associated LSN 0/2B29E50""bdr (6212727469166484615,1,16387,):receive"
2015-11-03 21:45:35.328 
UTC,"postgres","pgmirror",4918,"10.1.1.169:36334",56392a26.1336,7,"idle",2015-11-03 21:41:58 UTC,5/146,0,LOG,08P01,"unexpected EOF on standby connection","slot ""bdr_16387_6212727469166484615_1_16387__"", output plugin ""bdr"", in the change callback, associated LSN 0/2B29E50""bdr (6212727469166484615,1,16387,):receive"
2015-11-03 21:45:35.330 
UTC,"postgres","pgmirror",4918,"10.1.1.169:36334",56392a26.1336,8,"idle",2015-11-03 21:41:58 UTC,,0,LOG,0,"disconnection: session time: 0:03:37.234 user=postgres database=pgmirror host=10.1.1.169 port=36334","bdr (6212727469166484615,1,16387,):receive"
2015-11-03 21:45:40.421 UTC,,,5066,"",56392b04.13ca,1,"",2015-11-03 
21:45:40 UTC,,0,LOG,0,"connection received: host=10.1.1.169 
port=36335",""
2015-11-03 21:45:40.427 
UTC,"postgres","pgmirror",5066,"10.1.1.169:36335",56392b04.13ca,2,"authentication",2015-11-03 21:45:40 UTC,5/149,0,LOG,0,"replication connection authorized: user=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)",""
2015-11-03 21:45:40.431 
UTC,"postgres","pgmirror",5066,"10.1.1.169:36335",56392b04.13ca,3,"idle",2015-11-03 21:45:40 UTC,5/0,0,LOG,0,"starting logical decoding for slot ""bdr_16387_6212727469166484615_1_16387__""","streaming transactions committing after 1/14FEDFE0, reading WAL from 0/187AFA0""bdr (6212727469166484615,1,16387,):receive"
2015-11-03 21:45:40.435 
UTC,"postgres","pgmirror",5066,"10.1.1.169:36335",56392b04.13ca,4,"idle",2015-11-03 21:45:40 UTC,5/0,0,LOG,0,"logical decoding found consistent point at 0/187AFA0","Logical decoding will begin using saved snapshot.""bdr (6212727469166484615,1,16387,):receive"

###

On the other node in the cluster (supposed to receive the data via BDR) 
I see this in the logs:


###
2015-11-03 21:45:31.885 UTC,,,3391,,56391e57.d3f,19,,2015-11-03 20:51:35 
UTC,,0,LOG,0,"checkpoint starting: xlog",""
2015-11-03 21:45:35.400 UTC,,,4773,,56392a26.12a5,1,,2015-11-03 21:41:58 
UTC,3/201,876,ERROR,XX000,"connection to other side has 
died","bdr (6212727469166484615,1,16387,): apply"
2015-11-03 21:45:35.408 UTC,,,3388,,56391e56.d3c,34,,2015-11-03 20:51:34 
UTC,,0,LOG,0,"worker process: bdr 
(6212727469166484615,1,16387,)->bdr (6212727476664052871,1, (PID 4773) 
exited with exit code 1",""
2015-11-03 21:45:40.412 UTC,,,3388,,56391e56.d3c,35,,2015-11-03 20:51:34 
UTC,,0,LOG,0,"starting background worker process ""bdr 
(6212727469166484615,1,16387,)->bdr 
(6212727476664052871,1,""",""

###


--
Florin Andrei
http://florin.myip.org/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: name conflict when joining a rebuilt node

[Florin Andrei]

Still having issues with this with BDR-0.9.3

This is how I join a new node to the cluster:


su - postgres
psql pgmirror

-- fire up BDR extensions
CREATE EXTENSION btree_gist;
CREATE EXTENSION bdr;

-- join BDR group via an existing node there
SELECT bdr.bdr_group_join(
local_node_name := 'pg12-prod-uswest2-aws',
node_external_dsn := 'host=pg12-prod-uswest2-aws dbname=pgmirror',
join_using_dsn := 'host=pg11-prod-uswest2-aws dbname=pgmirror'
);

SELECT bdr.bdr_node_join_wait_for_ready();


This is how I remove a node from the cluster:


# Log into any other node in the cluster (NOT the node you want to 
remove) and run:


su - postgres
psql pgmirror

SELECT bdr.bdr_part_by_node_names('{pg12-prod-uswest2-aws}');

# Log into the removed node and run:

su - postgres
psql pgmirror

BEGIN;
SET LOCAL bdr.permit_unsafe_ddl_commands = true;
SET LOCAL bdr.skip_ddl_locking = true;
SECURITY LABEL FOR 'bdr' ON DATABASE pgmirror IS '{"bdr": false}';
COMMIT;

# Now restart PostgreSQL.


Now let's say on the removed node I've dropped the pgmirror database, 
performed some maintenance, re-created the pgmirror DB (empty), and now 
I want to re-join the node to the cluster under the same name. I repeat 
the join new node procedure described at the top. It gets stuck in 
node_join_wait_for_ready().


On another node, the re-joined node is now listed twice in 
bdr.bdr_nodes, once with status k, and again with status i. The logs on 
the re-joined node show this:



2015-11-03 20:29:52.016 UTC,,,4916,,56391614.1334,219,,2015-11-03 
20:16:20 UTC,,0,LOG,0,"starting background worker process ""bdr db: 
pgmirror""",""
2015-11-03 20:29:52.047 UTC,,,7222,"",56391940.1c36,1,"",2015-11-03 
20:29:52 UTC,,0,LOG,0,"connection received: host=127.0.0.1 
port=21241",""
2015-11-03 20:29:52.050 
UTC,"postgres","pgmirror",7222,"127.0.0.1:21241",56391940.1c36,2,"authentication",2015-11-03 20:29:52 UTC,4/321,0,LOG,0,"replication connection authorized: user=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)",""
2015-11-03 20:29:52.052 UTC,,,7221,,56391940.1c35,1,,2015-11-03 20:29:52 
UTC,3/0,0,ERROR,55000,"System identification mismatch between connection 
and slot","Connection for bdr (6212727469166484615,1,16387,) resulted in 
slot on node bdr (6212727469166484615,1,17169,) instead of expected 
node""bdr (6212727469166484615,1,17169,): perdb"
2015-11-03 20:29:52.053 
UTC,"postgres","pgmirror",7222,"127.0.0.1:21241",56391940.1c36,3,"idle",2015-11-03 20:29:52 UTC,4/0,0,LOG,08006,"could not receive data from client: Connection reset by peer","bdr (6212727469166484615,1,17169,):mkslot"
2015-11-03 20:29:52.053 
UTC,"postgres","pgmirror",7222,"127.0.0.1:21241",56391940.1c36,4,"idle",2015-11-03 20:29:52 UTC,,0,LOG,0,"disconnection: session time: 0:00:00.006 user=postgres database=pgmirror host=127.0.0.1 port=21241","bdr (6212727469166484615,1,17169,):mkslot"
2015-11-03 20:29:52.053 UTC,,,4916,,56391614.1334,220,,2015-11-03 
20:16:20 UTC,,0,LOG,0,"worker process: bdr db: pgmirror (PID 7221) 
exited with exit code 1",""



OS is Ubuntu 14.04, with these packages installed:


ii  postgresql-bdr-9.4   9.4.4-1trusty
amd64object-relational SQL database, version 9.4 server
ii  postgresql-bdr-9.4-bdr-plugin0.9.3-1trusty
amd64BDR Plugin for PostgreSQL-BDR 9.4
ii  postgresql-bdr-client-9.49.4.4-1trusty
amd64front-end programs for PostgreSQL-BDR 9.4
ii  postgresql-bdr-contrib-9.4   9.4.4-1trusty
amd64additional facilities for PostgreSQL
ii  postgresql-bdr-server-dev-9.49.4.4-1trusty
amd64development files for PostgreSQL-BDR 9.4 server-side 
programming
ii  postgresql-client-common 169.pgdg14.04+1  
all  manager for multiple PostgreSQL client versions
ii  postgresql-common169.pgdg14.04+1  
all  PostgreSQL database-cluster manager



--
Florin Andrei
http://florin.myip.org/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR: name conflict when joining a rebuilt node

[Craig Ringer]

On 30 October 2015 at 08:24, Florin Andrei  wrote:

> The problem is, bdr_node_join_wait_for_ready() never returns, it just waits
> forever. If I go on pg11 and run SELECT * FROM bdr.bdr_nodes, I see pg12
> listed twice, with node_status k and i, respectively. On pg11 I see this in
> the logs:
>
> "System identification mismatch between connection and slot","Connection for
> bdr (6211167104388615363,1,16387,) resulted in slot on node bdr
> (6211167104388615363,1,17163,) instead of expected node""bdr
> (6211167104388615363,1,17163,): perdb"

This is a bug fixed in 0.9.3.

https://github.com/2ndQuadrant/bdr/issues/126


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR: name conflict when joining a rebuilt node

[Florin Andrei]

Let's say node pg12 in a cluster needs to be removed because it has 
serious problems. I remove it by running this command on another node in 
the cluster:


SELECT bdr.bdr_part_by_node_names('{pg12}');

On pg12, I run this:

BEGIN;
SET LOCAL bdr.permit_unsafe_ddl_commands = true;
SET LOCAL bdr.skip_ddl_locking = true;
SECURITY LABEL FOR 'bdr' ON DATABASE pgmirror IS '{"bdr": false}';
COMMIT;

I repair the broken node, drop the existing database, fix whatever is 
wrong with it, re-create the database (empty). It's basically like a new 
node. Then I try to re-join it to the cluster under the same old name:


SELECT bdr.bdr_group_join(
local_node_name := 'pg12',
node_external_dsn := 'host=pg12 dbname=pgmirror',
join_using_dsn := 'host=pg11 dbname=pgmirror'
);
SELECT bdr.bdr_node_join_wait_for_ready();

The problem is, bdr_node_join_wait_for_ready() never returns, it just 
waits forever. If I go on pg11 and run SELECT * FROM bdr.bdr_nodes, I 
see pg12 listed twice, with node_status k and i, respectively. On pg11 I 
see this in the logs:


"System identification mismatch between connection and slot","Connection 
for bdr (6211167104388615363,1,16387,) resulted in slot on node bdr 
(6211167104388615363,1,17163,) instead of expected node""bdr 
(6211167104388615363,1,17163,): perdb"


How can I re-join an old node to the cluster after rebuilding it from 
scratch, under the old name?


Do I have to change the name every time I re-join a node?

--
Florin Andrei
http://florin.myip.org/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] bdr-plugin packages repo not updated to 0.9.3?

[Florin Andrei]

http://packages.2ndquadrant.com/bdr/apt/pool/main/b/bdr-plugin/

Looks like the repo has not caught up to the latest 0.9.3 release. Would 
it be possible to push updated packages to the repo, please? It would be 
a great help with testing BDR.


Thanks,

--
Florin Andrei
http://florin.myip.org/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR-Plugin make install on RHEL7.1

[Frank Nagel]

On Thu, 2015-10-29 at 08:33 -0400, Will McCormick wrote:
> Trying to get the bdr-plugin to install make install on RHEL7.1. Having some 
> issues with make of the plugin.
> 
> 
>
> # make -j4 -s all make -s install
> make: *** No rule to make target `make'.  Stop.
> make: *** Waiting for unfinished jobs

Your last command should probably look more like this:

# make -j4 -s all && make -s install




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR-Plugin make install on RHEL7.1

[Will McCormick]

Trying to get the bdr-plugin to install make install on RHEL7.1.
Having some issues with make of the plugin.


Here is what I have run verbatim and the error is at the very bottom.
Any help is appreciated. Please let me know if there is another forum
I should be posting this to.


I'm assuming I'm just doing something silly with make but may not be the case.


# yum check-update
# yum groupinstall "Development Tools"
# yum install yum-utils openjade docbook-dtds docbook-style-dsssl
docbook-style-xsl
# yum install readline-devel
# cd /stage
# yum install pgdg-redhat94-9.4-2.noarch.rpm
# yum-builddep postgresql94


# cd /stage/gitrepo

# git clone -b bdr-pg/REL9_4_STABLE
https://github.com/2ndQuadrant/bdr.git postgresql-bdr
Cloning into 'postgresql-bdr'...
remote: Counting objects: 465808, done.
remote: Compressing objects: 100% (12/12), done.
remote: Total 465808 (delta 1), reused 0 (delta 0), pack-reused 465796
Receiving objects: 100% (465808/465808), 135.70 MiB | 1.91 MiB/s, done.
Resolving deltas: 100% (391503/391503), done.

# git clone -b bdr-plugin/REL0_9_STABLE
https://github.com/2ndQuadrant/bdr.git bdr-plugin
Cloning into 'bdr-plugin'...
remote: Counting objects: 465808, done.
remote: Compressing objects: 100% (12/12), done.
remote: Total 465808 (delta 1), reused 0 (delta 0), pack-reused 465796
Receiving objects: 100% (465808/465808), 135.70 MiB | 1.73 MiB/s, done.
Resolving deltas: 100% (391503/391503), done.

# ls -lart /stage/gitrepo
total 8
drwxr-xr-x 3 root root 57 Oct 28 17:19 ..
drwxr-xr-x 7 root root 4096 Oct 28 17:26 postgresql-bdr
drwxr-xr-x 4 root root 44 Oct 28 17:26 .
drwxr-xr-x 10 root root 4096 Oct 28 17:28 bdr-plugin


# ./configure --prefix=/database/postgres/product/9.4.4 --enable-debug
--with-openssl
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking which template to use... linux
checking whether to build with 64-bit integer date/time support... yes
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
checking for WAL segment size... 16MB
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking whether gcc supports -Wdeclaration-after-statement... yes
checking whether gcc supports -Wendif-labels... yes
checking whether gcc supports -Wmissing-format-attribute... yes
checking whether gcc supports -Wformat-security... yes
checking whether gcc supports -fno-strict-aliasing... yes
checking whether gcc supports -fwrapv... yes
checking whether gcc supports -fexcess-precision=standard... yes
checking whether gcc supports -funroll-loops... yes
checking whether gcc supports -ftree-vectorize... yes
checking whether gcc supports -Wunused-command-line-argument... no
checking whether the C compiler still works... yes
checking how to run the C preprocessor... gcc -E
checking allow thread-safe client libraries... yes
checking whether to build with Tcl... no
checking whether to build Perl modules... no
checking whether to build Python modules... no
checking whether to build with GSSAPI support... no
checking whether to build with PAM support... no
checking whether to build with LDAP support... no
checking whether to build with Bonjour support... no
checking whether to build with OpenSSL support... yes
checking whether to build with SELinux support... no
checking for grep that handles long lines and -e... /usr/bin/grep
checking for egrep... /usr/bin/grep -E
checking for ld used by GCC... /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking for ranlib... ranlib
checking for strip... strip
checking whether it is possible to strip libraries... yes
checking for ar... ar
checking for a BSD-compatible install... /usr/bin/install -c
checking for tar... /usr/bin/tar
checking whether ln -s works... yes
checking for gawk... gawk
checking for a thread-safe mkdir -p... /usr/bin/mkdir -p
checking for bison... /usr/bin/bison
configure: using bison (GNU Bison) 2.7
checking for flex... /usr/bin/flex
configure: using flex 2.5.37
checking for perl... /usr/bin/perl
configure: using perl 5.16.3
checking for main in -lm... yes
checking for library containing setproctitle... no
checking for library containing dlopen... -ldl
checking for library containing socket... none required
checking for library containing shl_load... no
checking for library containing getopt_long... none required
checking for library containing crypt... -lcrypt
checking for library containing shm_open... -lrt
checking for library containing shm_unlink... none required
checking for library 

Re: [GENERAL] BDR - DDL Locking

[Craig Ringer]

Will,

I saw after replying that there's more detail I missed in your mail,
so please see the more detailed reply inline below.

On 20 October 2015 at 23:31, Will McCormick  wrote:
> First time user here and new to PostgreSQL and BDR so I hope I have the
> right place.

You do.

> I attempted to issues a TRUNCATE TABLE without the cascade option on a
> Parent table that had a child FK constraint.

I've looked at your logs, and it looks like the TRUNCATE suceeded on
the node that was doing the DDL and it was queued for replication.
Then, when applying to another node, it failed because there was a
foreign key relationship referencing the target table.

This is odd, because the way BDR captures TRUNCATEs should prevent
that from happening. It uses triggers to capture TRUNCATES and
enqueues them for execution. However, I can see upon inspection that
the approach used just isn't sufficient to handle FK relationships,
and that the current test suite doesn't cover this.

I'm going to write a test to confirm what I think is going on, then follow up.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR - DDL Locking

[Will McCormick]

Hey Craig thank you very much for your response.

> When you say you "attempted to" - what was the outcome?

I tried a truncate without the cascade option. After that I tried it with
the cascade option. The session just hanged indefinitely at that point.
There was no rollback and I was testing on an empty table.

Replication was in a ready state on both nodes and both DDL and DML was
replicating.

0.9.2.0 BDR

When you say restarting the nodes. I did restart postgres and this didn't
help.


On Wed, Oct 21, 2015 at 8:31 AM, Craig Ringer  wrote:

> What's the *exact* BDR version?
>
> When you say you "attempted to" - what was the outcome? Presumably an
> ERROR from the TRUNCATE, right? That would roll back the transaction,
> and in the process abort the DDL lock acquisition attempt.
>
> Are you sure replication was working normally prior to this point,
> with no issues?
>
> The global DDL lock isn't a true lock in the sense that it appears in
> pg_locks, etc. If you roll back the transaction trying to acquire it,
> or terminate the PostgreSQL backend attempting to acquire it - such as
> your TRUNCATE - using pg_terminate_backend(...) then it will be
> removed automatically. If for any reason that is not the case (which
> it shouldn't be) then restarting the nodes will clear it.
>

Re: [GENERAL] BDR - DDL Locking

[Craig Ringer]

What's the *exact* BDR version?

When you say you "attempted to" - what was the outcome? Presumably an
ERROR from the TRUNCATE, right? That would roll back the transaction,
and in the process abort the DDL lock acquisition attempt.

Are you sure replication was working normally prior to this point,
with no issues?

The global DDL lock isn't a true lock in the sense that it appears in
pg_locks, etc. If you roll back the transaction trying to acquire it,
or terminate the PostgreSQL backend attempting to acquire it - such as
your TRUNCATE - using pg_terminate_backend(...) then it will be
removed automatically. If for any reason that is not the case (which
it shouldn't be) then restarting the nodes will clear it.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR: pg_stat_bdr: cache lookup failed

[Selim Tuvi]

Hi we are currently testing BDR 0.9.2 and I set up a two node cluster. From one 
node I can run:

select * from bdr.pg_stat_bdr

and it gives me the rows fine but on the other node I get the following error:

ERROR:  cache lookup failed for replication identifier id: 4

Any idea why?

The server log produces this:

2015-10-14 21:01:13.313 
UTC,"postgres","deliver",30783,"[local]",561ec296.783f,3,"SELECT",2015-10-14 
21:01:10 UTC,6/5,0,ERROR,XX000,"cache lookup failed for replication identifier 
id: 4",,"select * from bdr.pg_stat_bdr;",,,"psql"

Thanks
-Selim

Re: [GENERAL] BDR: no free replication state could be found

[Craig Ringer]

On 10 October 2015 at 02:53, Selim Tuvi  wrote:
> node: deliver_sing (the problem node):
>
> postgres=# SELECT * FROM pg_catalog.pg_replication_identifier;
>  riident | riname
> -+
>1 | bdr_6197393155020108291_1_47458_16385_
>2 | bdr_6199712740068695651_1_16385_16385_
>3 | bdr_6197393155020108291_1_47458_17167_
>4 | bdr_6199712740068695651_1_16385_17167_
>5 | bdr_6199712740068695651_1_18817_17951_
>6 | bdr_6197393155020108291_1_48609_17951_
>7 | bdr_6197393155020108291_1_48609_19685_
>8 | bdr_6199712740068695651_1_18817_19685_
> (8 rows)


> On 9 October 2015 at 06:54, Selim Tuvi  wrote:

>> "recovered replication state of node 6 to 0/59F35A8",""
>> "no free replication state could be found, increase
>> max_replication_slots",""

The number of supported replication identifiers (in bdr 9.4) is
controlled by max_replication_slots, hence the error message. This
should be documented; I'll amend the docs appropriately.

https://github.com/2ndQuadrant/bdr/issues/133

The identifiers aren't currently dropped during node part, which
should be changed. It hasn't come up to date because frequent node
addition and removal is something to be avoided, and because most
deployments configure room for more slots than needed to avoid future
restarts.

https://github.com/2ndQuadrant/bdr/issues/134

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR workers exiting?

[Craig Ringer]

BDR is currently memory-limited for extremely large transactions. At a
guess, I'd say one of your big tables is large enough that the logical
decoding facility BDR uses can't keep track of the transaction
properly.

There's no hard limit, it depends on details of the transaction and a
number of other variables, but "many tens or hundreds of GB" is
generally too much.

If I was to load such a big DB, I'd probably do it with ETL tools that
could split up the load and do it progressively.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR workers exiting?

[Steve Pribyl]

The process used to created this

Start with clean db
Create host A database with bdr
Join host B with dbr
Load database using psql < file.sql

I was able to get it work if I do the following.
Start with clean db
Create host A database
Load data on host A
Join host A to bdr.
Join host b to bdr.

Glad to have a work around but would like to get to understand the failure.

Steve Pribyl




From: Steve Pribyl
Sent: Monday, October 12, 2015 11:19 AM
To: Andres Freund
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR workers exiting?

Yup, there is a disconnect on other side.

This disconnect is preceded by this.
ERROR,XX000,"invalid memory alloc request size 1073741824","slot 
""bdr_16494_6204748238611542317_1_16494__"", output plugin ""bdr"", in the 
change callback, associated LSN 2/FD250E48""bdr 
(6204748238611542317,1,16494,):receive"

Steve Pribyl



From: Andres Freund 
Sent: Monday, October 12, 2015 11:08 AM
To: Steve Pribyl
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR workers exiting?

On 2015-10-12 14:37:07 +, Steve Pribyl wrote:
> I am loading up a 60G database into BDR database and these "ERRORS" are in my 
> logs.  Is not normal behavior or is something going bad.
>
> 2015-10-12 09:28:59.389 CDT,,,30371,,561bc17d.76a3,1,,2015-10-12 09:19:41 
> CDT,5/0,0,ERROR,XX000,"data stream ended","bdr 
> (6204748238611542317,1,16494,): apply"
> 2015-10-12 09:28:59.390 CDT,,,12693,,561bb1ae.3195,20,,2015-10-12 08:12:14 
> CDT,,0,LOG,0,"worker process: bdr (6204748238611542317,1,16494,)->bdr 
> (6204748255428234532,1, (PID 30371) exited with exit code 1",""
> 2015-10-12 09:29:04.395 CDT,,,12693,,561bb1ae.3195,21,,2015-10-12 08:12:14 
> CDT,,0,LOG,0,"starting background worker process ""bdr 
> (6204748238611542317,1,16494,)->bdr (6204748255428234532,1,""",""

There'll possibly be an error message on the other node about ending the
connection.

Do you use SSL? If so, try disabling renegotiation.

Regards,

Andres

 [http://www.akunacapital.com/images/akuna.png]
Steve Pribyl | Senior Systems Engineer
Akuna Capital LLC
36 S Wabash, Suite 310 Chicago IL 60603 USA | www.akunacapital.com 
<http://www.akunacapital.com>
p: +1 312 994 4646 | m:  | f: +1 312 750 1667 | steve.pri...@akunacapital.com

Please consider the environment, before printing this email.

This electronic message contains information from Akuna Capital LLC that may be 
confidential, legally privileged or otherwise protected from disclosure. This 
information is intended for the use of the addressee only and is not offered as 
investment advice to be relied upon for personal or professional use. 
Additionally, all electronic messages are recorded and stored in compliance 
pursuant to applicable SEC rules. If you are not the intended recipient, you 
are hereby notified that any disclosure, copying, distribution, printing or any 
other use of, or any action in reliance on, the contents of this electronic 
message is strictly prohibited. If you have received this communication in 
error, please notify us by telephone at (312)994-4640 and destroy the original 
message.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR workers exiting?

[Steve Pribyl]

Yup, there is a disconnect on other side.

This disconnect is preceded by this.
ERROR,XX000,"invalid memory alloc request size 1073741824","slot 
""bdr_16494_6204748238611542317_1_16494__"", output plugin ""bdr"", in the 
change callback, associated LSN 2/FD250E48""bdr 
(6204748238611542317,1,16494,):receive"

Steve Pribyl
Sr. Systems Engineer
steve.pri...@akunacapital.com
Desk: 312-994-4646



From: Andres Freund 
Sent: Monday, October 12, 2015 11:08 AM
To: Steve Pribyl
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR workers exiting?

On 2015-10-12 14:37:07 +, Steve Pribyl wrote:
> I am loading up a 60G database into BDR database and these "ERRORS" are in my 
> logs.  Is not normal behavior or is something going bad.
>
> 2015-10-12 09:28:59.389 CDT,,,30371,,561bc17d.76a3,1,,2015-10-12 09:19:41 
> CDT,5/0,0,ERROR,XX000,"data stream ended","bdr 
> (6204748238611542317,1,16494,): apply"
> 2015-10-12 09:28:59.390 CDT,,,12693,,561bb1ae.3195,20,,2015-10-12 08:12:14 
> CDT,,0,LOG,0,"worker process: bdr (6204748238611542317,1,16494,)->bdr 
> (6204748255428234532,1, (PID 30371) exited with exit code 1",""
> 2015-10-12 09:29:04.395 CDT,,,12693,,561bb1ae.3195,21,,2015-10-12 08:12:14 
> CDT,,0,LOG,0,"starting background worker process ""bdr 
> (6204748238611542317,1,16494,)->bdr (6204748255428234532,1,""",""

There'll possibly be an error message on the other node about ending the
connection.

Do you use SSL? If so, try disabling renegotiation.

Regards,

Andres

 [http://www.akunacapital.com/images/akuna.png]
Steve Pribyl | Senior Systems Engineer
Akuna Capital LLC
36 S Wabash, Suite 310 Chicago IL 60603 USA | www.akunacapital.com 
<http://www.akunacapital.com>
p: +1 312 994 4646 | m:  | f: +1 312 750 1667 | steve.pri...@akunacapital.com

Please consider the environment, before printing this email.

This electronic message contains information from Akuna Capital LLC that may be 
confidential, legally privileged or otherwise protected from disclosure. This 
information is intended for the use of the addressee only and is not offered as 
investment advice to be relied upon for personal or professional use. 
Additionally, all electronic messages are recorded and stored in compliance 
pursuant to applicable SEC rules. If you are not the intended recipient, you 
are hereby notified that any disclosure, copying, distribution, printing or any 
other use of, or any action in reliance on, the contents of this electronic 
message is strictly prohibited. If you have received this communication in 
error, please notify us by telephone at (312)994-4640 and destroy the original 
message.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR workers exiting?

[Andres Freund]

On 2015-10-12 14:37:07 +, Steve Pribyl wrote:
> I am loading up a 60G database into BDR database and these "ERRORS" are in my 
> logs.  Is not normal behavior or is something going bad.
> 
> 2015-10-12 09:28:59.389 CDT,,,30371,,561bc17d.76a3,1,,2015-10-12 09:19:41 
> CDT,5/0,0,ERROR,XX000,"data stream ended","bdr 
> (6204748238611542317,1,16494,): apply"
> 2015-10-12 09:28:59.390 CDT,,,12693,,561bb1ae.3195,20,,2015-10-12 08:12:14 
> CDT,,0,LOG,0,"worker process: bdr (6204748238611542317,1,16494,)->bdr 
> (6204748255428234532,1, (PID 30371) exited with exit code 1",""
> 2015-10-12 09:29:04.395 CDT,,,12693,,561bb1ae.3195,21,,2015-10-12 08:12:14 
> CDT,,0,LOG,0,"starting background worker process ""bdr 
> (6204748238611542317,1,16494,)->bdr (6204748255428234532,1,""",""

There'll possibly be an error message on the other node about ending the
connection.

Do you use SSL? If so, try disabling renegotiation.

Regards,

Andres


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR workers exiting?

[Jim Nasby]

On 10/12/15 10:14 AM, Jim Nasby wrote:

On 10/12/15 9:37 AM, Steve Pribyl wrote:

I am loading up a 60G database into BDR database and these "ERRORS"
are in my logs.  Is not normal behavior or is something going bad.

2015-10-12 09:28:59.389 CDT,,,30371,,561bc17d.76a3,1,,2015-10-12
09:19:41 CDT,5/0,0,ERROR,XX000,"data stream ended","bdr
(6204748238611542317,1,16494,): apply"
2015-10-12 09:28:59.390 CDT,,,12693,,561bb1ae.3195,20,,2015-10-12
08:12:14 CDT,,0,LOG,0,"worker process: bdr
(6204748238611542317,1,16494,)->bdr (6204748255428234532,1, (PID
30371) exited with exit code 1",""
2015-10-12 09:29:04.395 CDT,,,12693,,561bb1ae.3195,21,,2015-10-12
08:12:14 CDT,,0,LOG,0,"starting background worker process ""bdr
(6204748238611542317,1,16494,)->bdr (6204748255428234532,1,""",""


Looks like something's going bad, but you need to ask on the BDR mailing
list.


Nevermind, just discovered there is no separate list. Sorry for the noise.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR workers exiting?

[Jim Nasby]

On 10/12/15 9:37 AM, Steve Pribyl wrote:

I am loading up a 60G database into BDR database and these "ERRORS" are in my 
logs.  Is not normal behavior or is something going bad.

2015-10-12 09:28:59.389 CDT,,,30371,,561bc17d.76a3,1,,2015-10-12 09:19:41 
CDT,5/0,0,ERROR,XX000,"data stream ended","bdr 
(6204748238611542317,1,16494,): apply"
2015-10-12 09:28:59.390 CDT,,,12693,,561bb1ae.3195,20,,2015-10-12 08:12:14 
CDT,,0,LOG,0,"worker process: bdr (6204748238611542317,1,16494,)->bdr 
(6204748255428234532,1, (PID 30371) exited with exit code 1",""
2015-10-12 09:29:04.395 CDT,,,12693,,561bb1ae.3195,21,,2015-10-12 08:12:14 CDT,,0,LOG,0,"starting background 
worker process ""bdr (6204748238611542317,1,16494,)->bdr 
(6204748255428234532,1,""",""


Looks like something's going bad, but you need to ask on the BDR mailing 
list.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] BDR workers exiting?

[Steve Pribyl]

I am loading up a 60G database into BDR database and these "ERRORS" are in my 
logs.  Is not normal behavior or is something going bad.

2015-10-12 09:28:59.389 CDT,,,30371,,561bc17d.76a3,1,,2015-10-12 09:19:41 
CDT,5/0,0,ERROR,XX000,"data stream ended","bdr 
(6204748238611542317,1,16494,): apply"
2015-10-12 09:28:59.390 CDT,,,12693,,561bb1ae.3195,20,,2015-10-12 08:12:14 
CDT,,0,LOG,0,"worker process: bdr (6204748238611542317,1,16494,)->bdr 
(6204748255428234532,1, (PID 30371) exited with exit code 1",""
2015-10-12 09:29:04.395 CDT,,,12693,,561bb1ae.3195,21,,2015-10-12 08:12:14 
CDT,,0,LOG,0,"starting background worker process ""bdr 
(6204748238611542317,1,16494,)->bdr (6204748255428234532,1,""",""


Steve Pribyl
Thanks

 [http://www.akunacapital.com/images/akuna.png]
Steve Pribyl | Senior Systems Engineer
Akuna Capital LLC
36 S Wabash, Suite 310 Chicago IL 60603 USA | www.akunacapital.com 

p: +1 312 994 4646 | m:  | f: +1 312 750 1667 | steve.pri...@akunacapital.com

Please consider the environment, before printing this email.

This electronic message contains information from Akuna Capital LLC that may be 
confidential, legally privileged or otherwise protected from disclosure. This 
information is intended for the use of the addressee only and is not offered as 
investment advice to be relied upon for personal or professional use. 
Additionally, all electronic messages are recorded and stored in compliance 
pursuant to applicable SEC rules. If you are not the intended recipient, you 
are hereby notified that any disclosure, copying, distribution, printing or any 
other use of, or any action in reliance on, the contents of this electronic 
message is strictly prohibited. If you have received this communication in 
error, please notify us by telephone at (312)994-4640 and destroy the original 
message.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general