Re: [GENERAL] BDR replication and table triggers

[Sylvain Marechal]

Why not using the logical decoding feature:
https://www.postgresql.org/docs/9.4/static/logicaldecoding-example.html

On both sides, you would have a process that regularly decodes the stream
and emits notifications for event in tables you are insterested in.

Sylvain

2017-05-02 18:18 GMT+02:00 Alvaro Aguayo Garcia-Rada <
aagu...@opensysperu.com>:

> Hi.
>
> It's not like BDR is unable to replicate triggers across the cluster: BDR
> is not intended to do so.
>
> BDR replicates everything that happens inside a transaction; that includes
> both SQL run directly from the application, as well as changes made by
> triggers and extensions. As the changes are applied directly from the WAL,
> no trigger is re-run on the other nodes. If the trigger is re-run, that
> would lead to problems, such as duplicated rows.
>
> The only "problem", if it really is, is that BDR does not copy
> notifications across the databases. As this may be seen as a problem, I
> could also consider it as a chance to make the application more
> self-conscious of the distributed environment it is running in. So I would
> try one out of two alternatives:
>
> 1. Make the application listen to notifications on both databases, so it
> will get notified of changes no matter where they happen
>
> 2. Instead of using notify, create a notification table, which your app
> should scan periodically and act accordingly.
>
> Regards,
>
> Alvaro Aguayo
> Jefe de Operaciones
> Open Comb Systems E.I.R.L.
>
> Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51)
> 954183248
> Website: www.ocs.pe
>
> - Original Message -
> From: "jamesadams89" 
> To: "PostgreSql-general" 
> Sent: Wednesday, 26 April, 2017 07:48:03
> Subject: [GENERAL] BDR replication and table triggers
>
> Hi,
>
> I have some questions regarding how BDR interacts with triggers.
>
> I have two databases that are both joined to the same BDR group and
> correctly replicating between one another sharing a table created as:
>
> create table testtable(
> key varchar(16) NOT NULL PRIMARY KEY,
> data jsonb
> );
>
> With the following trigger defined:
>
> CREATE OR REPLACE FUNCTION test_table_notify()
> RETURNS TRIGGER AS
> $$
> BEGIN
> IF TG_OP='INSERT' OR TG_OP='UPDATE' THEN
> PERFORM pg_notify( 'TestTable', TG_OP || ' ' || NEW.key );
> ELSE
> PERFORM pg_notify( 'TestTable', TG_OP || ' ' || OLD.key );
> END IF;
> RETURN NULL;
> END;
> $$ LANGUAGE plpgsql;
>
>
> CREATE TRIGGER TestTableTrigger
> AFTER INSERT OR UPDATE OR DELETE
> on testtable
> FOR EACH ROW
> EXECUTE PROCEDURE test_table_notify();
>
> I then have a client application listening on the 'TestTable' Notify on one
> of the Databases:
>
> Client
>  ___
> | |
> | A  |
> |___|
>   /\
>|
>  _|_ ___
> |  | |   |
> |DB1|-|DB2|
> |_ __| ||
>
> If I perform any INSERT, UPDATE or DELETE operations directly on DB1 I see
> the trigger on the table being fired as expected and Client Application 'A'
> recieves the notify.  I also see the changes propagate to DB2 via BDR as
> expected.  However if I perform any INSERT, UPDATE or DELETE operations on
> DB2 and these changes propagate over to DB1 via BDR I do not see DB1 firing
> any triggers. Is this intended behavior?  My current understanding is that
> BDR is unable to invoke Postgres triggers as it operates on the rows
> directly, a layer below Postgres. Is this Correct? Is there any mechanism
> that exists that could provide notifications to a listening application
> when
> BDR makes changes to the underlying database?
>
> Apologies if this is all a bit elementary, this is my first foray into BDR
> and I was unable to find anything in the documentation that mentioned
> triggers.
>
> Thanks for any input
>
>
>
> --
> View this message in context: http://www.postgresql-archive.
> org/BDR-replication-and-table-triggers-tp5958463.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
>
>
> --
> 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] Best way to alter a foreign constraint

[Sylvain MARECHAL]

Le 19/03/2017 à 09:54, Sylvain Marechal a écrit :
2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian.kla...@aklaver.com 
<mailto:adrian.kla...@aklaver.com>>:


On 03/18/2017 12:05 PM, Sylvain Marechal wrote:

Hello all,

Some of my tables were badly designed and have 2 indexes, like the
following example (lots of tables have same problem):

<<<
postgres=# \d test1
 Table "public.test1"
 Column |  Type   | Modifiers
+-+---
 t1 | integer | not null
Indexes:
"test1_pkey" PRIMARY KEY, btree (t1)
"test1_t1_key" UNIQUE CONSTRAINT, btree (t1)
Referenced by:
TABLE "test2" CONSTRAINT "test2_t1_fkey" FOREIGN KEY (t1)
REFERENCES
test1(t1)

postgres=# \d test2
 Table "public.test2"
 Column |  Type   | Modifiers
+-+---
 t2 | integer | not null
 t1 | integer |
Indexes:
"test2_pkey" PRIMARY KEY, btree (t2)
Foreign-key constraints:
"test2_t1_fkey" FOREIGN KEY (t1) REFERENCES test1(t1)



It is not possible to remove the "test1_t1_key" constraint
because the
"test2_t1_fkey"  internally references it:
<<<
postgres=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key;
ERROR:  cannot drop constraint test1_t1_key on table test1
because other
objects depend on it
DETAIL:  constraint test2_t1_fkey on table test2 depends on index
test1_t1_key
HINT:  Use DROP ... CASCADE to drop the dependent objects too.



Why not CASCADE?:

In fact, CASCADE is not enough, because I don't know how the 
test2_t1_fkey is built : does it use the test1_pkey primary key or the 
test1_t1_key unique key?
I am sure this information can be found in system catalogs, but I find 
it safer to explicitely delete then recreate the foreign constraint.


Sylvain

Re: [GENERAL] Best way to alter a foreign constraint

[Sylvain Marechal]

2017-03-19 17:55 GMT+01:00 Adrian Klaver <adrian.kla...@aklaver.com>:

> On 03/19/2017 01:54 AM, Sylvain Marechal wrote:
>
>>
>>
>> 2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>:
>>
>> On 03/18/2017 12:05 PM, Sylvain Marechal wrote:
>>
>
>
>>
>> Why not CASCADE?:
>>
>> test=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key CASCADE;
>> NOTICE:  drop cascades to constraint test2_t1_fkey on table test2
>> ALTER TABLE
>>
>>
>>
>>
>> It is the same end result as the first two steps of what you are
>> doing below, just a different direction.
>>
>>
>> No special reason at all: I began with CASCADE, and as things went
>> wrong, I tried to split the process to better figure out the problem
>>
>>
>>
>> Is there a solution to" alter" the "test2_t1_fkey" constraint so
>> that it
>> uses the "primary key constraint", then to remove the
>> unnecessary unique
>> constraint on table test1
>>
>> The following solution works but causes me deadlocks problems
>> with BDR:
>>
>>
>> Is the below wrapped in a transaction?
>>
>>
>> Yes.
>> The goal is to wrap this upgrade process inside a transaction to be able
>> to abort it in case something was wrong.
>>
>> Problem is that some tables may be accessed during the upgrade process.
>> May be a solution is to avoid it by only allowing the upgrade backend
>> and bdr to access the tables, but I do not like the idea to make the
>> database readonly (UPDATE pg_database SET datallowconn = false WHERE pid
>> != upgrade_and_bdr ... ):
>>
>
> So is the above a BDR specific enhancement to pg_database or is pid !=
> upgrade_and_bdr just a placeholder for something else?
>

Sorry, forget all about BDR. In fact, I need to arrange the tables not to
be accessed during the upgrade phase, else this leads to deadlocks, and
there is no possible magic to avoid it as I was initially dreaming.
In other words, to solve my problem, I think I have 2 solutions :
1) do the necessary job so that only the upgrade process access the tables
during constraints changes; other processes will be stopped during the
upgrade
2) or in the upgrade process, terminate all processes except the one that
does the upgrade, and the bdr workers.
(the "upgrade_and_bdr" pseudo code was not clear, sorry for this)



> in case the upgrade process fails, this would
>> requiere require a manual intervention to solve it (upgrade is called if
>> needed by the application).
>>
>
> If I am following correctly then the changes to the tables are being done
> on a as needed basis based on some external criteria.
>
> In any case for each table it should be a one time operation, right?
> Also from a practical stand point the FK between test2 and test1 is not
> actually changing. So why not just change them ahead of time in a process
> you can monitor directly?
>

Yes, this is what I should do.


Thank you,

Sylvain

Re: [GENERAL] Best way to alter a foreign constraint

[Sylvain Marechal]

2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian.kla...@aklaver.com>:

> On 03/18/2017 12:05 PM, Sylvain Marechal wrote:
>
>> Hello all,
>>
>> Some of my tables were badly designed and have 2 indexes, like the
>> following example (lots of tables have same problem):
>>
>> <<<
>> postgres=# \d test1
>>  Table "public.test1"
>>  Column |  Type   | Modifiers
>> +-+---
>>  t1 | integer | not null
>> Indexes:
>> "test1_pkey" PRIMARY KEY, btree (t1)
>> "test1_t1_key" UNIQUE CONSTRAINT, btree (t1)
>> Referenced by:
>> TABLE "test2" CONSTRAINT "test2_t1_fkey" FOREIGN KEY (t1) REFERENCES
>> test1(t1)
>>
>> postgres=# \d test2
>>  Table "public.test2"
>>  Column |  Type   | Modifiers
>> +-+---
>>  t2 | integer | not null
>>  t1 | integer |
>> Indexes:
>> "test2_pkey" PRIMARY KEY, btree (t2)
>> Foreign-key constraints:
>> "test2_t1_fkey" FOREIGN KEY (t1) REFERENCES test1(t1)
>>
>>>
>>>>>
>> It is not possible to remove the "test1_t1_key" constraint because the
>> "test2_t1_fkey"  internally references it:
>> <<<
>> postgres=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key;
>> ERROR:  cannot drop constraint test1_t1_key on table test1 because other
>> objects depend on it
>> DETAIL:  constraint test2_t1_fkey on table test2 depends on index
>> test1_t1_key
>> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
>>
>>>
>>>>>
> Why not CASCADE?:
>
> test=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key CASCADE;
> NOTICE:  drop cascades to constraint test2_t1_fkey on table test2
> ALTER TABLE
>



> It is the same end result as the first two steps of what you are doing
> below, just a different direction.
>

No special reason at all: I began with CASCADE, and as things went wrong, I
tried to split the process to better figure out the problem


>
>> Is there a solution to" alter" the "test2_t1_fkey" constraint so that it
>> uses the "primary key constraint", then to remove the unnecessary unique
>> constraint on table test1
>>
>> The following solution works but causes me deadlocks problems with BDR:
>>
>
> Is the below wrapped in a transaction?


Yes.
The goal is to wrap this upgrade process inside a transaction to be able to
abort it in case something was wrong.

Problem is that some tables may be accessed during the upgrade process. May
be a solution is to avoid it by only allowing the upgrade backend and bdr
to access the tables, but I do not like the idea to make the database
readonly (UPDATE pg_database SET datallowconn = false WHERE pid !=
upgrade_and_bdr ... ): in case the upgrade process fails, this would
requiere require a manual intervention to solve it (upgrade is called if
needed by the application).




>
>
> <<<
>> ALTER TABLE test2 DROP CONSTRAINT test2_t1_fkey;
>> ALTER TABLE test1 DROP CONSTRAINT test1_t1_key;
>> ALTER TABLE test2 ADD CONSTRAINT test2_t1_fkey FOREIGN KEY (t1)
>> REFERENCES test1(t1);
>>
>>>
>>>>>
>> Thanks and regards,
>> Sylvain
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


Thanks,
Sylvain

[GENERAL] Best way to alter a foreign constraint

[Sylvain Marechal]

Hello all,

Some of my tables were badly designed and have 2 indexes, like the
following example (lots of tables have same problem):

<<<
postgres=# \d test1
 Table "public.test1"
 Column |  Type   | Modifiers
+-+---
 t1 | integer | not null
Indexes:
"test1_pkey" PRIMARY KEY, btree (t1)
"test1_t1_key" UNIQUE CONSTRAINT, btree (t1)
Referenced by:
TABLE "test2" CONSTRAINT "test2_t1_fkey" FOREIGN KEY (t1) REFERENCES
test1(t1)

postgres=# \d test2
 Table "public.test2"
 Column |  Type   | Modifiers
+-+---
 t2 | integer | not null
 t1 | integer |
Indexes:
"test2_pkey" PRIMARY KEY, btree (t2)
Foreign-key constraints:
"test2_t1_fkey" FOREIGN KEY (t1) REFERENCES test1(t1)
>>>

It is not possible to remove the "test1_t1_key" constraint because the
"test2_t1_fkey"  internally references it:
<<<
postgres=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key;
ERROR:  cannot drop constraint test1_t1_key on table test1 because other
objects depend on it
DETAIL:  constraint test2_t1_fkey on table test2 depends on index
test1_t1_key
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
>>>

Is there a solution to" alter" the "test2_t1_fkey" constraint so that it
uses the "primary key constraint", then to remove the unnecessary unique
constraint on table test1

The following solution works but causes me deadlocks problems with BDR:
<<<
ALTER TABLE test2 DROP CONSTRAINT test2_t1_fkey;
ALTER TABLE test1 DROP CONSTRAINT test1_t1_key;
ALTER TABLE test2 ADD CONSTRAINT test2_t1_fkey FOREIGN KEY (t1) REFERENCES
test1(t1);
>>>

Thanks and regards,
Sylvain

Re: [GENERAL] BDR problem rejoining node

[Sylvain Marechal]

2017-02-11 1:34 GMT+01:00 Tanner Kerr :

> I have two databases being replicated across three nodes with bdr. The
> third node filled up and crashed. I removed this node from the group
> successfully, but now I'm having trouble rejoining it. I'm able to re-join
> the one database no problem. However, trying to do a bdr join on the other
> causes the two active nodes to slow down considerably, and refuses to join.
> This database copies the data after a few minutes, however, the join fails
> and bdr.bdr_nodes on the third node shows only itself in the group with
> status 'i'. The active nodes don't show anything different in the
> bdr.bdr_nodes. All they can see is a new pg_replication_slot for that node.
> The logs say "previous init failed, manual cleanup is required" Even though
> there are no bdr_connections or broken bdr_nodes entries. Everytime I've
> tried, I've removed the pg_replication slots, the bdr_connections, and the
> bdr_nodes (bdr.remove_bdr_from_local_node()). I've also dropped the
> cluster and recreated it again each time with:
>
> pg_dropcluster 9.4 main --stop
> /usr/bin/pg_createcluster --start 9.4 -d /var/lib/postgresql/9.4/main main
>
> The database failing to copy is small only around 60M. The one being
> copied successfully, is around 1600M.
>
> The join command I've been using:
>
> select bdr.bdr_group_join(local_node_name := 'NODENAMEOFREBUILTNODE',
> node_external_dsn := 'host=IPOFREBUILTNODE port=5432 dbname=MYDB
> connect_timeout=50 keepalives_idle=5 keepalives_interval=1', join_using_dsn
> := 'host=ACTIVENODEIP  port=5432 dbname=MYDB connect_timeout=50
> keepalives_idle=5 keepalives_interval=1');
>
> I'm not sure why it works for one database and not the other.
>
> The log from the failed join attemp is below:
>
> 2017-02-10 14:49:24 PST [6981-10] LOG:  Creating replica with:
> /usr/lib/postgresql/9.4/bin/bdr_initial_load --snapshot 00C58720-1
> --source "host=ACTIVEIP  port=5432 dbname=MYDB connect_timeout=5
> 0 keepalives_idle=5 keepalives_interval=1" --target "host=REBUILDHOST
> port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5
> keepalives_interval=1" --tmp-directory "/tmp/postgres-bdr-00C58720-1
> .6981", --pg-dump-path "/usr/lib/postgresql/9.4/bin/bdr_dump",
> --pg-restore-path "/usr/lib/postgresql/9.4/bin/pg_restore"
> Dumping remote database "connect_timeout=30 keepalives=1
> keepalives_idle=20 keepalives_interval=20 keepalives_count=5
> host=ACTIVEIP  port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5
> keepalives_interval=1 fallback_application_name='bdr
> (6385577151748866557,1,16391,): init_replica dump'" with 1 concurrent
> workers to "/tmp/postgres-bdr-00C58720-1.6981"
> Restoring dump to local DB "host=REBUILDHOST port=5432 dbname=MYDB
> connect_timeout=50 keepalives_idle=5 keepalives_interval=1
> fallback_application_name='bdr (6385577151748866557,1,16391,):
> init_replica restore' options='-c bdr.do_not_replicate=on  -c
> bdr.permit_unsafe_ddl_commands=on -c bdr.skip_ddl_replication=on -c
> bdr.skip_ddl_locking=on -c session_replication_role=replica'" with 1
> concurrent workers from "/tmp/postgres-bdr-00C58720-1.6981"
> 2017-02-10 14:57:16 PST [7271-1] postgres@MYDB NOTICE:  extension
> "btree_gist" already exists, skipping
> 2017-02-10 14:57:16 PST [7271-2] postgres@MYDB NOTICE:  extension "bdr"
> already exists, skipping
> 2017-02-10 14:57:16 PST [7271-3] postgres@MYDB NOTICE:  extension
> "plpgsql" already exists, skipping
> 2017-02-10 14:57:16 PST [7271-4] postgres@MYDB NOTICE:  extension
> "pgcrypto" already exists, skipping
> 2017-02-10 14:57:16 PST [7271-5] postgres@MYDB NOTICE:  extension
> "uuid-ossp" already exists, skipping
>
> *alters tables and builds indexes*
>
> 2017-02-10 14:57:23 PST [6981-10] DEBUG:  syncing bdr_nodes and
> bdr_connections
> 2017-02-10 14:57:23 PST [6981-11] ERROR:  BEGIN or table locking on remote
> failed:
> 2017-02-10 14:57:23 PST [6884-11] LOG:  worker process: bdr db: MYDB (PID
> 6981) exited with exit code 1
> 2017-02-10 14:57:28 PST [6884-12] LOG:  starting background worker process
> "bdr db: MYDB"
> 2017-02-10 14:57:28 PST [7274-1] NOTICE:  version "1.0" of extension
> "btree_gist" is already installed
> 2017-02-10 14:57:28 PST [7274-2] NOTICE:  version "1.0.1.0" of extension
> "bdr" is already installed
> 2017-02-10 14:57:28 PST [7274-3] DEBUG:  per-db worker for node bdr
> (6385577151748866557,1,16391,) starting
> 2017-02-10 14:57:28 PST [7274-4] DEBUG:  init_replica init from remote
> host=ACTIVEIP  port=5432 dbname=MYDB connect_timeout=50 keepalives_idle=5
> keepalives_interval=1
> 2017-02-10 14:57:28 PST [7274-5] ERROR:  previous init failed, manual
> cleanup is required
> 2017-02-10 14:57:28 PST [7274-6] DETAIL:  Found bdr.bdr_nodes entry for
> bdr (6385577151748866557,1,16391,) with state=i in remote bdr.bdr_nodes
> 2017-02-10 14:57:28 PST [7274-7] HINT:  Remove all replication identifiers
> and slots corresponding to this node from the init target node then drop
> and 

Re: [GENERAL] BDR: changing dsn on a running node

[Sylvain MARECHAL]

Le 07/10/2016 à 23:54, Natan Abolafya a écrit :


Hi

Is it possible to change the dsn connection string of a node without 
leaving the group? I couldn’t find the related documentation 
unfortunately.


We’re using BDR in a dynamic environment where the hostname/ip of a 
node may be changed any time. Leaving and rejoining the BDR group 
seems to be a solution but is a bit tedious and a slow process.


Natan


Hi,

Just to say I did it with bdr 0.9.3 to change the password of the 
connection string.
I needed to manually modify the bdr.bdr_nodes and bdr.bdr_connections 
tables for all nodes but finally it works.

What I found strange is that the changes were not replicated automatically.
(Note there is a bdr.bdr_connections_changed() stored procedure, I am 
not sure this is needed, I did not use it)


My 2 cents,
Sylvain

Re: [GENERAL] Monitor pg_xlog size via SQL with postgres 9.4

[Sylvain Marechal]

Thank you all, I thought there may be a specific posgresql stored procedure
that did the work of enumerating the files of the pg_xlog directory to get
its full  size, or may be one that gave the full size of the server
databases including pg_xlog.
Nevermind, this does the work.

Regards,
Sylvain
​

2016-09-22 16:05 GMT+02:00 hubert depesz lubaczewski <dep...@depesz.com>:

> On Thu, Sep 22, 2016 at 02:23:20PM +0200, Sylvain Marechal wrote:
> > is there a way to monitor the size of the pg_xlog directory in SQL? The
>
> Assuming you have superuser privileges, it will most likely work:
>
> select sum( (f).size ) from (select pg_stat_file('pg_xlog/' || pg_ls_dir)
> as f from pg_ls_dir('pg_xlog/')) x where not (f).isdir;
>
> Best regards,
>
> depesz
>
>

[GENERAL] Monitor pg_xlog size via SQL with postgres 9.4

[Sylvain Marechal]

Hello all,

is there a way to monitor the size of the pg_xlog directory in SQL? The
goal is to monitor the pg_xlog file without ressorting to a 'du' like
solution that needs a direct access to the machine.

I know I can get the retained size for existing replication slots segment
in case there are some with the following query :
<<<
SELECT slot_name, database, active,
pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS
retained_bytes FROM pg_replication_slots  ;
>>>

but how could I monitor the real size of the pg_xlog directory and detect
it is growing (may be because the archive_command is becomed faulty)

I was thinking of using the last_archived_wal field of the pg_stat_archiver
table, but I am not sure it is accurate, and I do not find any postgres
stored procedure that converts the file name into an LSN (ie, a function
that does the opposite of pg_xlogfile_name())

Thanks and regards,
Sylvain

[GENERAL] BDR: Recover from "FATAL: mismatch in worker state" without restarting postgres

[Sylvain Marechal]

Hello all,

After uninstalling a BDR node, it becomes not possible to join it again.
The following log appears in loop:
<<<
2016-08-25 10:17:08 [ll101] postgres info [11709]: [14620-1] LOG:  starting
background worker process "bdr (6287997142852742670,1,19526,)->bdr
(6223672436788445259,2," #local4,support
2016-08-25 10:17:08 [ll101] postgres info [11709]: [14621-1] LOG:  starting
background worker process "bdr (6287997142852742670,1,18365,)->bdr
(6223672436788445259,2," #local4,support
2016-08-25 10:17:08 [ll101] postgres info [11709]: [14622-1] LOG:  starting
background worker process "bdr db: mydb" #local4,support
2016-08-25 10:17:08 [ll101] postgres error [6484]: [14621-1] FATAL:
 mismatch in worker state, got 0, expected 1 #error,local4,support
2016-08-25 10:17:08 [ll101] postgres error [6486]: [14622-1] FATAL:
 mismatch in worker state, got 0, expected 1 #error,local4,support

>>>
I can not tell how this appends: before removing the node, one of the node
was in the 'catchup' state and the lag of data between the 2 nodes was
growing, that is why I removed it (the idea was to clean the lagged node
and to reattach it again.)


Questions:
* is it possible to recover from this error without restarting postgres
* in case a restart is necessary, how to be sure the postgres restart will
work? my fear is that the restart fails, meaning the service will be
completely down.

Thanks and regards,
Sylvain

Re: [GENERAL] A simple extension immitating pg_notify

[Sylvain Marechal]

I understand that:
1) you like to use postgres as a "bus" to transfer messages between
connected clients;
2) only one database server is concerned (no redundancy at all);
3) it is the client code (perl, php ...) that send the notification (ie,
notifications are not sent by triggers for example)

 May be you could dedicate one of your database to do this; all clients
could listen messages on this database and some client code would decode
the payload and does its job.

Sylvain

2016-07-24 15:55 GMT+02:00 Melvin Davidson :

>
>
> On Sun, Jul 24, 2016 at 9:35 AM, Adrian Klaver 
> wrote:
>
>> On 07/24/2016 03:31 AM, Mehran Ziadloo wrote:
>>
>>> I'm sorry, you are right. Let me rephrase that.
>>>
>>> What I meant was that the notifications are received by the client
>>> connections
>>> as if it was generated within their corresponding database (let's name
>>> the
>>> database with a client connection listening to it; DB_C), then a
>>> notification
>>> generated by a NOTIFY command within DB_X like this:
>>>
>>> pg_notify_db('DB_C', some_channel, 'payload');
>>>
>>> can be heard by the client. Of course, the client needs to know which
>>> database
>>> has sent the notification but I think we can skip that part since we can
>>> easily
>>> include database name in the notification's payload if necessary.
>>>
>>> That was one of the two solutions. The other one is like this:
>>>
>>> pg_notify_all(some_channel, 'payload');
>>>
>>> And this one sends the notification to all of the client connections,
>>> regardless of which database they are connected/listening to.
>>>
>>> I hope it makes sense now.
>>>
>>>
>>>
>> As to how to start writing an extension:
>>
>> https://www.postgresql.org/docs/9.5/static/extend-extensions.html
>>
>> As to how NOTIFY/LISTEN works:
>>
>>
>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/async.c;h=beef574076c257db0a868b39a946565185f6c53e;hb=ba37ac217791dfdf2b327c4b75e7083b6b03a2f5
>>
>>
>> --
>> 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
>>
>
> I am not so sure another extension is needed. Would it not make more sense
> just to use the dblink extension?
> https://www.postgresql.org/docs/9.5/static/contrib-dblink-function.html
>
> Just make a table that contains the names of the other databases and
> connection info.
> Then the  same trigger that calls the function for NOTIFY or
> pg_notify(text, text) could just as
> easily call a function with dblink that determines which database needs
> the notify and raises it there.
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] BDR replication slots

[Sylvain Marechal]

2016-04-19 6:51 GMT+02:00 Nikhil :

> Hello,
>
> I have a 2 node BDR group and replication is happening properly. if i
> bring down one of the node's interface, after sometime the replication
> slots are becoming inactive (pg_replication_slots view). Then if i bring
> back interface slots are not turning active automatically and replication
> stops. Any idea why automatically its not re-established ?
>

May be postgres does not detect the link is down.
You could try modifying the connection strings, so that postgres detects
earlier the problem, like described in this post :
https://github.com/2ndQuadrant/bdr/issues/173 :

<<<
[...]
Example of dsn_connection field:
host=192.168.1.140 port=5432 dbname=safewalk-server connect_timeout=10
keepalives_idle=5 keepalives_interval=1
>>>


Hope this helps,

Sylvain

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

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

[Sylvain MARECHAL]

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.




Answering to myself, I guess no magic SQL command exists for this, I 
have to cancel the request with pg_cancel_backend() (in fact, that what 
the does says, I was guessing if something could detect this 
automatically and abort the request).


If using a blocking API, this means one should fork the task and monitor 
it to decide whether it should be canceled or not if it takes to much 
time (check if one of the node is down, then cancel the request and 
retry it later when the node will be up again).


--
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] 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

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

[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 <st...@ilm.com 
<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

[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
>
>

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 truncate and replication sets

[Sylvain MARECHAL]

Le 15/09/2015 18:56, Alvaro Herrera a écrit :

Sylvain MARECHAL wrote:

[...] The exception is with TRUNCATE: In case it is called, data is removed on
both nodes.

Is it a feature or a bug?

I think it's an oversight.  Replication sets were added later than the
TRUNCATE trigger, so the design for the latter does not consider the
former as far as I know.

Ok. May I fill a bug report?

[...] Is there a workaround?
(deleting the truncate trigger seems to work, but I am sure it is safe to do
it)

Well, the truncate trigger is there to replicate the truncate to other
servers.  If you don't want truncate to be propagated, dropping the
trigger is one way to achieve that effect.


Ok. That is what I will do.
In fact, having TRUNCATE do its job on all nodes is sometimes useful, 
sometimes unwanted. So this workaround is perfect for me for the moment.


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

[GENERAL] BDR truncate and replication sets

[Sylvain MARECHAL]

Hello all,

To avoid replication of some tables, I use a specific replication set.
For example, with 2 nodes 'node1' and 'node2' and a table 'test' which 
content shall not be replicated, I do the following:


mydb=# CREATE TABLE test (i INT PRIMARY KEY NOT NULL);
mydb=# SELECT bdr.table_set_replication_sets('test', ARRAY['test_rep_set']);

After that, adding (INSERT) or removing (DELETE) data in 'node1' or 
'node2' can be done independently.
The exception is with TRUNCATE: In case it is called, data is removed on 
both nodes.


Is it a feature or a bug?
(I am not sure it is related with 
https://github.com/2ndQuadrant/bdr/issues/93)


Is there a workaround?
(deleting the truncate trigger seems to work, but I am sure it is safe 
to do it)


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

Re: [GENERAL] BDR setup - bdr_node_join_wait_for_ready()

[Sylvain MARECHAL]

Hello all,

Le 31/08/2015 02:25, Martín Marqués a écrit :

BTW, why don't you the physical join procedure with bdr_init_copy to
join nodes?


Are there known problems in the SQL interface ?

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

[GENERAL] Getting the value of the old_tuple using the test_decoding extension and BDR

[Sylvain MARECHAL]

Hello all,

I am playing with the test_decoding extension to be notified on database
changes in a couple of databases replicated with BDR.

What I can see is that new values are present, and also old key when the
modification concerns the primary key. But the other old values do not seem
to be accessible.

Is it a limitation of the test_decoding plugin, or old values are simply
not available?

Thanks,
Sylvain

[GENERAL] Re: Getting the value of the old_tuple using the test_decoding extension and BDR

[Sylvain MARECHAL]

2015-07-02 16:11 GMT+02:00 Sylvain MARECHAL marechal.sylva...@gmail.com:

 Hello all,

 [...]

 What I can see is that new values are present, and also old key when the
 modification concerns the primary key. But the other old values do not seem
 to be accessible.

 Is it a limitation of the test_decoding plugin, or old values are simply
 not available?



I guess I simply should use the REPLICA IDENTITY (
http://www.postgresql.org/docs/9.4/static/sql-altertable.html)


ALTER TABLE
[...]
REPLICA IDENTITY
[...]
FULL records the old values of all columns in the row.


Sylvain

Re: [GENERAL] BDR: Can a node live alone after being detached

[Sylvain MARECHAL]

Le 26/06/2015 03:26, Craig Ringer a écrit :

[...]

Sorry to bother again about that, but what about the detached node cleanup
best practice?

Suppose I have 2 nodes, 'node1' and 'node2'. So I am OK to call
bdr_part_by_node_names('node2') on node1 to detach node2. But then, is it
safe to call bdr_part_by_node_names('node1') to do the cleanup on node2, or
should I better call pg_drop_replication_slot('...') instead?

BDR 0.9.1 drops the slot on remaining nodes correctly on node part, so
no further action is required there. Just part_by_node_names the node
you are removing, running the query from one of the nodes you wish to
retain. (0.9.1 enforces this).

If you want to retain the node you're removing as a standalone, it
should be sufficient to drop any slots on that node, truncate
bdr.bdr_nodes and bdr.bdr_connections, and remove the bdr security
label with:

 SECURITY LABEL FOR bdr ON DATABASE thedb IS NULL;

then restart the DB. You may also wish to remove 'bdr' from
'shared_preload_libraries'.


Thanks a lot,
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: Can a node live alone after being detached

[Sylvain MARECHAL]

  You shouldn't part a node from its self. The next revision will
  prevent this with an error.
 
 
  Ok, this was not clear for me.

 Or anyone else, hence the coming docs and code changes.

 Sorry to bother again about that, but what about the detached node
cleanup best practice?

Suppose I have 2 nodes, 'node1' and 'node2'. So I am OK to call
bdr_part_by_node_names('node2') on node1 to detach node2. But then, is it
safe to call bdr_part_by_node_names('node1') to do the cleanup on node2, or
should I better call pg_drop_replication_slot('...') instead?

Thanks and Regards,
Sylvain

Re: [GENERAL] BDR: Can a node live alone after being detached

[Sylvain MARECHAL]

Hi Craig,

Thanks for your response.

Le 16/06/2015 03:58, Craig Ringer a écrit :

On 15 June 2015 at 17:19, Sylvain MARECHAL marechal.sylva...@gmail.com wrote:

Is it possible to completely detach a node so that it can live alone

Yes. On a different node to the one you want to remove,
bdr.bdr_part_by_node_names(ARRAY['the_node_to_remove'])  to safely
remove it without disrupting the other nodes.


in particular that DDL are again possible on that node?

DDL is possible on a BDR node anyway, just with some limitations.


I tried with a simple node without success:

postgres=# create database test template template0;
CREATE DATABASE
postgres=# \c test
You are now connected to database test as user dbadmin.
test=# CREATE EXTENSION btree_gist;
CREATE EXTENSION
test=# CREATE EXTENSION bdr;
CREATE EXTENSION

-- DDL are still possible
test=# create table before_node_creation (i int primary key not null);
CREATE TABLE

-- Now create the group. After that, DDL are now forbidden
test=# SELECT bdr.bdr_group_create(local_node_name :=
'node1',node_external_dsn := 'host=172.27.118.64 port=5432 dbname=test');

test=# create table after_node_creation (i int primary key not null);
ERROR:  No peer nodes or peer node count unknown, cannot acquire DDL lock
HINT:  BDR is probably still starting up, wait a while

Well, DDL is only forbidden because it hasn't successfully joined yet.
Check the PostgreSQL logs to see what it's doing.


Nothing special in the logs here. What I would like to emphasize is that 
a single node can not live alone.





-- Now detach the group, in the hope to create some table
-- But this does not work. DDL are still forbidden
test=# select bdr.bdr_part_by_node_names('{node1}');

You shouldn't part a node from its self. The next revision will
prevent this with an error.


Ok, this was not clear for me.




test=# create table after_node_creation (i int primary key not null);
ERROR:  No peer nodes or peer node count unknown, cannot acquire DDL lock
HINT:  BDR is probably still starting up, wait a while

Currently, once detached, the BDR extension isn't disabled on the node.

Support for that is possible, but not yet implemented. We need to add
a two-phase part protocol where the node confirms it has left the
system and disables BDR. At present removed nodes aren't really a
focus; it's expected that you're removing the node because you're
going to retire it and will be deleting the cluster or dropping the
database.

In the mean time you can remove the security label on the database to
disable BDR once the node has been parted, so that BDR no longer
activates on that node. The command filter prevents this so you'll
have to do this with the command filter off.

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


Ok, I will do this as a workaround.
But having a function doing the detach() properly would be really nice.



Out of interest, why do you want to detach a node and keep using it as
a standalone DB?




This is related to the design of our application: We would like to 
provide to our users a flexible way to add and remove nodes, for 
failover (2 nodes) and scalability reasons (possibly more than 2 nodes 
with load balancing).
If for some reason (long time maintenance for one of the machine, 
testing the application ...) the user wants to remove the failover, we 
would like him to be able to keep at least one DB without the need of 
dropping/recreating it to minimize the down time.
Of course, in a real production scenario, the user won't probably change 
often its configuration, but I think this is really useful to have this 
kind of flexibility when taking control of the application.


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: Can a node live alone after being detached

[Sylvain MARECHAL]

Hello all,

Is it possible to completely detach a node so that it can live alone, in 
particular that DDL are again possible on that node?


I tried with a simple node without success:

postgres=# create database test template template0;
CREATE DATABASE
postgres=# \c test
You are now connected to database test as user dbadmin.
test=# CREATE EXTENSION btree_gist;
CREATE EXTENSION
test=# CREATE EXTENSION bdr;
CREATE EXTENSION

-- DDL are still possible
test=# create table before_node_creation (i int primary key not null);
CREATE TABLE

-- Now create the group. After that, DDL are now forbidden
test=# SELECT bdr.bdr_group_create(local_node_name := 
'node1',node_external_dsn := 'host=172.27.118.64 port=5432 dbname=test');


test=# create table after_node_creation (i int primary key not null);
ERROR:  No peer nodes or peer node count unknown, cannot acquire DDL lock
HINT:  BDR is probably still starting up, wait a while


-- Now detach the group, in the hope to create some table
-- But this does not work. DDL are still forbidden
test=# select bdr.bdr_part_by_node_names('{node1}');

test=# create table after_node_creation (i int primary key not null);
ERROR:  No peer nodes or peer node count unknown, cannot acquire DDL lock
HINT:  BDR is probably still starting up, wait a while
test=#


Thanks and Regards,
Sylvain
NB: This post was part of a bigger - may be too dense - post: BDR: Node 
join and leave questions



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