Re: [Slony1-general] Slony website is down

[Steve Singer via Slony1-general]

On Mon, 3 Jun 2024, Sung Hsin Lei via Slony1-general wrote:

The site is currently up, but it does go down occasionally.

The sources would be available in git 
https://git.postgresql.org/gitweb/?p=slony1-engine.git;a=summary but that 
doesn't show the documentation in a easy to read format.


Something we could think about is creating a static site with the tar 
releases and HTML documentation (but no mailing lists or bugzilla).


Steve



Hi,
Since the site is down and the project is ending, is there a place where people 
with legacy development can go to get documentation such as,
for example, performing a switchover, performing a repair, adding tables to a 
set... etc.

Thanks



On Mon, May 13, 2024 at 9:26 AM Jan Wieck via Slony1-general 
 wrote:
  On 5/13/24 03:08, Devrim Gündüz via Slony1-general wrote:
  >
  > Hi,
  >
  > On Sun, 2024-05-12 at 18:09 +, Soni M via Slony1-general wrote:
  >> I found that slony website is down.
  >
  > https://www.slony.info/pipermail/slony1-general/2024-March/013559.html

  It responds (sluggish) to me.

  That said the Slony-I project is ending. We will not provide any new
  versions.


  Regards, Jan

  >
  > Regards,
  >
  >
  > ___
  > Slony1-general mailing list
  > Slony1-general@lists.slony.info
  > https://lists.slony.info/cgi-bin/mailman/listinfo/slony1-general

  ___
  Slony1-general mailing list
  Slony1-general@lists.slony.info
  https://lists.slony.info/cgi-bin/mailman/listinfo/slony1-general



___
Slony1-general mailing list
Slony1-general@lists.slony.info
https://lists.slony.info/cgi-bin/mailman/listinfo/slony1-general

[Slony1-general] Future of slony.info

[Steve Singer via Slony1-general]

A while back I was talking to Jan about what our plans should be for 
slony.info.


The user base and use-cases for slony been replaced with options built 
around the built in replication/decoding features and these are present in 
all non EOL versions of PG.


The only updates to slony in the past 4-6 years have been minimal/trivial.

Should we declare the project finished and work towards shutting down the 
mailing lists and website?


The source code is already hosted at 
http://git.postgresql.org/git/slony1-engine.git with mirrors on github


I would guess that slony is mostly used to help migrate off out of support 
versions of PG.


Unless someone objects we are thinking of shutting down the mailing lists 
and bugzilla, update the website to say this and that the project is moving to 
an archive phase.


Thoughts?

Steve

___
Slony1-general mailing list
Slony1-general@lists.slony.info
https://lists.slony.info/cgi-bin/mailman/listinfo/slony1-general

Re: [Slony1-general] sl_log_1 is no longer empty

[Steve Singer via Slony1-general]

On Mon, 27 Nov 2023, Sebastien Marchand via Slony1-general wrote:


Hello,
I have a problem that I've just noticed on several of my replications, 
my sl_log_1 table no longer empties and keeps incrementing. Where should 
I look to find the event that breaks the function that's supposed to 
manage the truncate?


The cleanupEvent handles cleaning up the sl_log tables which slon calls

Is replication to a node behind?
Do you have a subscriber/node that is out of date/abandoned/dropped
Do you have a long running transaction on the system?
Is there a problem getting the confirms floating back from the replica to 
the other nodes?




Steve




Thanks for your help.
Sincerely,
Sébastien Marchand

___
Slony1-general mailing list
Slony1-general@lists.slony.info
https://lists.slony.info/cgi-bin/mailman/listinfo/slony1-general

___
Slony1-general mailing list
Slony1-general@lists.slony.info
https://lists.slony.info/cgi-bin/mailman/listinfo/slony1-general

[Slony1-general] Slony 2.2.11 released

[Steve Singer via Slony1-general]

The Slony team is pleased to announce Slony 2.2.11 the next minor release
of the Slony 2.2.x series

Slony 2.2.11 includes the following changes

 - Add support for PG 15
 - Remove standard_conforming_strings autoconf check
 - Fix typo in admin guide


Slony 2.2.11 can be downloaded from the following URL

https://www.slony.info/downloads/2.2/source/slony1-2.2.11.tar.bz2

Steve

___
Slony1-general mailing list
Slony1-general@lists.slony.info
https://lists.slony.info/cgi-bin/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony 2.2.10 + Pg14 = 2.2.11 ?

[Steve Singer via Slony1-general]

On Wed, 19 Jan 2022, Tim Kane via Slony1-general wrote:

The REL_2_2_10 is now in the git repo.
I just skipped the step of pushing it.

I wouldn't wait for the warning to be removed.
The next time I do a release for an actual change/fix I'll update the 
warning versions then.


Steve



I notice that the most recent commit to the slony project is to explicitly 
remove the unsupported warning against
PG13 and denotes a bump to version 2.2.9

That commit is here:
https://git.postgresql.org/gitweb/?p=slony1-engine.git;a=commit;h=5b4dc7df19f3e0873d7daa00b76b8f048bec5185

The most recent tag in that repository is also 2_2_9 but this tag appears to 
predate the above commit, so it
looks like there is a discrepancy there.

The most recent release available is actually version 2.2.10 but there is no 
associated tag for REL_2_2_10


Anyway, these are just observations...
My actual question is...  should we expect to see a similar update for PG14 and 
a REL_2_2_11 ?

I appreciate this was discussed here but that discussion stopped short of any 
actual change to remove the warning
for PG14. Should I be holding out for such a change?

Thanks


___
Slony1-general mailing list
Slony1-general@lists.slony.info
https://mail.slony.info/cgi-bin/mailman/listinfo/slony1-general

Re: [Slony1-general] Postgres 14

[Steve Singer via Slony1-general]

On Mon, 6 Dec 2021, Andy Dossett via Slony1-general wrote:



Hi

ￜ

Is Slony 2.2.10 fully compatible with Postgres 14, or is there a 2.2.11 in the 
pipeline?


As far as I know it should work.
I ran the slony testsuite against PG14 during the beta period and found no 
issues.


If you encounter any problems please report them.

Steve




ￜ

Thanks



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Replication interruption

[Steve Singer via Slony1-general]

On Fri, 22 Oct 2021, Sung Hsin Lei via Slony1-general wrote:


This question may have been asked already. I have noticed that if it takes 10 
days for the initial data transfer to complete, if the transfer
is interrupted after 9 days, the transfer resumes from day 1. In other words, I 
need 10 days of uninterrupted connection between the master
and slave machine for replication to be established properly. Is this the case 
or did I mess up somewhere?
Another question. On a new replication(empty tables on the slave), I see the 
initial transfer stuck on TRUNCATE for a long time for big
tables. Sometimes, it truncates the same table several times. I remember 
getting an explanation for why it is so. Is there a way to make the
initial transfer faster(avoiding the truncate)?


If you already have at least one replica you might be able to use CLONE 
PREPARE / CLONE FINISH  and OMIT COPY to speed things up.


Truncates taking a long time might be because you have an open transaction 
with locks against the table.







Regards

Sung


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Replication cascade with 2 differents replications on the same table

[Steve Singer via Slony1-general]

On Wed, 26 May 2021, Sebastien Marchand via Slony1-general wrote:



Hi,

thank you, but i don't know how do this, my server A can't talk to my server 
C...


I might misunderstand what your trying to do but does

store path(server=1, client=2, conninfo='serverA.local');
store path(server=2, client=1, conninfo='serverB.local');

store path(server=2, client=3, conninfo='serverB.local');
store path(server=3, client=2, conninfo='serverC.local');

subscribe set(id=1, provider=1, receiver=2, forward=yes);
subscribe set(id=1, provider=2, receiver=3, forward=yes);


where node 1=serverA, and node 2=serverB, node 3=serverC
work?

Slonik would need to be able to talk to all nodes.

Slon for serverB would need to be able to talk to all three servers.

Slon for serverA only needs to talk to server A and server B
Slon for serverC only needs to talk to server C and server B


Steve





Le 25/05/2021 à 23:23, Richard Yen a écrit :


On Tue, May 25, 2021 at 3:11 AM Sebastien Marchand via Slony1-general 
 wrote:
  I do this :

  Server A -> Server  B -> Server C

  _replication_N1 for server A to B and _replication _N2 server B to C on
  the same table.

 
If you are doing two replication sets, then this won't work.  That's because 
when data on Server A gets replicated to
Server B, the replay on Server B is done with a replica identity that disables 
the triggers, which subsequently prevents
replication to Server C.

If you want to do this replication architecture of A->B->C, then you will need 
C to subscribe to _replication_N1 and
specify the source as Server B

Hope that helps!
--Richard



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] move_set question

[Steve Singer via Slony1-general]

On Thu, 21 Jan 2021, Richard Yen via Slony1-general wrote:




On Wed, Jan 20, 2021 at 8:55 PM 張 蔚捷（Zhang WeiJie） via Slony1-general 
 wrote:
  But I hove no idea how to change to the following mode.
  node 1->node2
  node 1->node3

  Any suggestions would be greatly appreciated.


You should be able to just do:

subscribe set ( id = 1, provider = 1, receiver = 3 );

But first you will need to make sure node1->node2 has "forward = yes" (it is set to 
"yes" by default, so it may not be a problem
for you)


Or

resubscribe node (origin=1, provider=1,receiver=3);

https://www.slony.info/documentation/2.2/stmtresubscribenode.html

If you have multiple sets I think you need to use resubscribe node so all 
sets get adjusted at the same time.



Steve



--Richard



  ___
  Slony1-general mailing list
  Slony1-general@lists.slony.info
  http://lists.slony.info/mailman/listinfo/slony1-general




___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

[Slony1-general] Slony 2.2.10 released

[Steve Singer via Slony1-general]

The Slony team is pleased to announce Slony 2.2.10 the next minor release
of the Slony 2.2.x series


Slony 2.2.10 includes the following changes


 - Remove unsupported warning on PG13

This change was supposed to be included in 2.2.9 but the warning was still 
present.



Slony 2.2.10 can be downloaded from the following URL

https://www.slony.info/downloads/2.2/source/slony1-2.2.10.tar.bz2

Steve

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony 2.2.9 released

[Steve Singer via Slony1-general]

On Sun, 29 Nov 2020, Steve Singer via Slony1-general wrote:

Just as a heads up.
The unsupported warning is still printed by 2.2.9 with PG13(but no longer 
with PG 12). It still should work.  We'll remove that warning on PG13 with 
2.2.10








The Slony team is pleased to announce Slony 2.2.9 the next minor release
of the Slony 2.2.x series


Slony 2.2.9 includes the following changes


 - Compile with -fno-common by default. Fix
   compile errors caused by defining variables in headers
 - Adjust slonik_build_env.pl so it works with PG11+
 - Remove unsupported warning on PG13



Slony 2.2.9 can be downloaded from the following URL

https://www.slony.info/downloads/2.2/source/slony1-2.2.9.tar.bz2


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

[Slony1-general] Slony 2.2.9 released

[Steve Singer via Slony1-general]

The Slony team is pleased to announce Slony 2.2.9 the next minor release
of the Slony 2.2.x series


Slony 2.2.9 includes the following changes


 - Compile with -fno-common by default. Fix
   compile errors caused by defining variables in headers
 - Adjust slonik_build_env.pl so it works with PG11+
 - Remove unsupported warning on PG13



Slony 2.2.9 can be downloaded from the following URL

https://www.slony.info/downloads/2.2/source/slony1-2.2.9.tar.bz2


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Switching Slave VM

[Steve Singer via Slony1-general]

On Thu, 23 Jul 2020, Sung Hsin Lei via Slony1-general wrote:


Hello,
I have a slave machine who's database I want to restore on a different machine
with a different ip. Since the backup process takes several hours, I disconnect 
the master,
backup the slave db and restore it on the new slave machine. Then I shutdown 
the slave
machine and reconnect the master to the new slave machine. Finally, I run the 
following 
script:

store path (server = 1, client = 2, conninfo = 'dbname = Securithor2 host = 
192.168.1.101 user = slonyuser password =
Ert3c5L221JKert  port = 5432');
store path (server = 2, client = 1, conninfo = 'dbname = Securithor2 host = 
192.168.1.102 user = slonyuser password =
Ert3c5L221JKert  port = 5432');

REPAIR CONFIG (
  SET ID = 1,
  EVENT NODE = 2
);

This procedure works but I would like the old slave to maintain replication 
while I perform 
the backup and restore. II would also like the new slave to catch up replication
after I close the old slave an switch the node to point to new slave.

Adding a node or changing node id is not an option for us.

My question is, do I have to stop replication to old slave while I setup new 
slave
or was I too paranoid and the procedure will also work if I do not interrupt 
replication
during backup and restore process.


If you let data replicate to the 'old node' while the 'new node' is 
restoring then change the paths to point at the 'new node' slony will think 
that all the data sent to the old node is already present on the 'new node'. 
So to answer your question, no that won't work if you can't assign a new 
node id.


From a slony point of view the 'old node' and 'new node' are the same node. 
Your original procedure works because Slony sees it as just one node with a 
changing IP/hostname.


Depending on your circumstances you could take a physical backup (ie with 
pg_basebackup) of the slony replica node restore it to the new server, stop 
the slons, replay the remaining WAL so the new server is in-sync with the 
slony replica then continue with the procedure you outlined above.


Steve








Thanks.


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] SLONY waiting por event tobe confirmed never end

[Steve Singer via Slony1-general]

On Mon, 8 Jun 2020, Fontana Daniel C (Desartec S.R.L.) via Slony1-general wrote:

Are the slon proceses running?
I think they get started as windows services (once you add those services)

Are they logging any errors (or anything else) to the windows event logs?




Hi i am trying to do my first sync with slony 2.2.6. and PostgreSQL version
10,windows 7 on both terminals.

Server IP = 192.168.1.229
Slave IP = 192.168.1.252

Just a basic table
CREATE TABLE dba.prueba
(
   id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START
1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
   descripcion character(100) COLLATE pg_catalog."default" NOT NULL,
   CONSTRAINT prueba_pkey PRIMARY KEY (id)
)
WITH (
   OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE dba.prueba
   OWNER to postgres;

On the master node script for slonik

cluster name = sync_articulos;
node 1 admin conninfo = 'dbname = 30711404879 host = 192.168.1.229 port =
5434 user = replicator password = 1234'; node 2 admin conninfo = 'dbname =
30711404879 host = 192.168.1.252 port = 5434 user = replicator password =
1234'; INIT CLUSTER ( ID = 1, COMMENT = 'nodo maestro' ); CREATE SET ( ID =
1, ORIGIN = 1, COMMENT = 'aqui van todas mis tablas' ); set add table (set
id=1, origin=1, id=1, fully qualified name = 'dba.prueba', comment = 'tabla
articulos'); store node (id = 2, comment = 'nodo esclavo', EVENT NODE = 1);
store path (server = 1, client = 2, conninfo = 'dbname = 30711404879 host =
192.168.1.229 user = replicator password = 1234'); store path (server = 2,
client = 1, conninfo = 'dbname = 30711404879 host = 192.168.1.252 user =
replicator password = 1234'); store listen (origin = 1, provider = 1,
receiver = 2); store listen (origin = 2, provider = 2, receiver = 1);

When i run

C:\Program Files\PostgreSQL\10\bin>slonik maestro.com
maestro.com:14: Possible unsupported PostgreSQL version (101300) 10.13,
defaulti ng to 8.4 support
maestro.com:34: Possible unsupported PostgreSQL version (101300) 10.13,
defaulti ng to 8.4 support
maestro.com:39: waiting for event (1,57) to be confirmed on node 2
maestro.com:39: waiting for event (1,57) to be confirmed on node 2
maestro.com:39: waiting for event (1,57) to be confirmed on node 2
and continues.

on the slave node script for slonik

cluster name = sync_articulos;
node 1 admin conninfo = 'dbname = 30711404879 host = 192.168.1.229 port =
5434 user = replicator password = 1234'; node 2 admin conninfo = 'dbname =
30711404879 host = 192.168.1.252 port = 5434 user = replicator password =
1234';
SUBSCRIBE SET ( ID = 1, PROVIDER = 1,   RECEIVER = 2,   FORWARD = YES);

When i run

c:\Program Files\PostgreSQL\10\bin>slonik esclavo.com waiting for events
(2,51) only at (2,0) to be confirmed on node 1 waiting for events
(2,51) only at (2,0) to be confirmed on node 1 waiting for events
(2,51) only at (2,0) to be confirmed on node 1 and continues.


!these processes never end!, what is the problem?


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] slonik replcation

[Steve Singer via Slony1-general]

On Wed, 8 Apr 2020, zayasj1 wrote:



The merge sets completed. We have slony running on both sides 
(production/failover). We're using PostgreSQL
9.4.

When I am looking in pgAdmin, and I go down to Slony Replication and start 
looking at my clusters, I can see
"Nodes" and "Replication sets".

The issue I am having is that the 'Replication set" called "temporary cluster" 
was not cleaned up and is
still lingering around and I can't do a drop/delete from the GUI. I am trying 
to rerun a new merge for
additional tables that need replication setup, but it keeps failing because I 
can't reuse that same temp
cluster and I really don't want to just create a new temp cluster with a new 
name. I'd just like to figure
out how to drop that temp cluster and then start over with my "make_merge" 
process.

Javier


The word 'cluster' and 'set' have specific meanings in slony.
THINK you are saying that you have

* 1 cluster called 'cluster1'
* You initially had 1 set in the cluster (set id=1)
* You then created a second set in this cluster (set id=2)
* Your slonik script failed, leaving set 2 in an unknown state.

If this is the case, you can drop set 2

with


cluster name = $CLUSTER;
​node 1 admin conninfo = 'dbname=$DB1 host=$H1 user=$U port=$P password=$W';
node 2 admin conninfo = 'dbname=$DB2 host=$H2 user=$U port=$P password=$W';
drop set(id=2,origin=1);


At this point your set (set 2) should no longer be part of the cluster.

You could then recreate set 2, add the tables, subscribe the set and merge 
the set as your script originally did.


If my above assumptions are incorrect and you actually have 2 clusters then 
something different would apply.


Steve



On Wednesday, April 8, 2020, 9:27:35 AM PDT, Steve Singer  
wrote:


On Wed, 8 Apr 2020, zayasj1 via Slony1-general wrote:

I want to confirm a few things first

Your are saying that you have two clusters, $CLUSTER was set to 'cluster1'
and 'cluster2'.

and when you do

select * from  _cluster1.sl_set;

you see your two sets

and

select * from _cluster2.sl_set;

you see some other sets.

Are you running slon daemons for both clusters, or just 1?

Has the subscription + mergeset completed for one of the clusters?
Has it completed for both?


Is your question how you can you drop cluster2 ? Or something else?



>
>      I am having an issue with Slony replication. I was doing a merge of new 
tables that needed to be
>      replicated and my connection died in mid-process (forgot to set in a 
screen session). Now I have
>      temporary clusters in both my primary and backup table. When I go into 
pgAdmin, I can't seem to
>      drop these temporary clusters. Does someone know of a way to do this in 
a shell script? I ran a
>      make-merge script to get the list of tables I wanted to update. Due to 
the size of some of those
>      tables, I was doing them one at a time to make sure I wasn't impacting 
anything. During this
>      process, the replication setup broke
>
> relevant code from my shell script to feed to slonik as follow:
>
> cluster name = $CLUSTER;
>
> ​node 1 admin conninfo = 'dbname=$DB1 host=$H1 user=$U port=$P password=$W';
> node 2 admin conninfo = 'dbname=$DB2 host=$H2 user=$U port=$P password=$W';
>
> ​create set (id=2, origin=1, comment='temporary cluster');
>
> ​set add table (set id=2, origin=1, id=363, fully qualified name = 
'public.table1');
> set add sequence (set id=2, origin=1, id=364, fully qualified name = 
'public.table1_id_seq');
>
>
> ​subscribe set(id=2, provider=1,receiver=2);
> merge set(id=1, add id=2,origin=1);
>
>
> I figured there has to be a way to run a similar type of script that would 
let me drop the temporary
> cluster based on the "set" details, but have never had the pgadmin GUI not 
work for me, so kinda stuck.
> Anyone else ever deal with this?
>
> Many thanks,
> Javier
>
>
>


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] slonik replcation

[Steve Singer via Slony1-general]

On Wed, 8 Apr 2020, zayasj1 via Slony1-general wrote:

I want to confirm a few things first

Your are saying that you have two clusters, $CLUSTER was set to 'cluster1' 
and 'cluster2'.


and when you do

select * from  _cluster1.sl_set;

you see your two sets

and

select * from _cluster2.sl_set;

you see some other sets.

Are you running slon daemons for both clusters, or just 1?

Has the subscription + mergeset completed for one of the clusters?
Has it completed for both?


Is your question how you can you drop cluster2 ? Or something else?





  I am having an issue with Slony replication. I was doing a merge of new 
tables that needed to be
  replicated and my connection died in mid-process (forgot to set in a 
screen session). Now I have
  temporary clusters in both my primary and backup table. When I go into 
pgAdmin, I can't seem to
  drop these temporary clusters. Does someone know of a way to do this in a 
shell script? I ran a
  make-merge script to get the list of tables I wanted to update. Due to 
the size of some of those
  tables, I was doing them one at a time to make sure I wasn't impacting 
anything. During this
  process, the replication setup broke

relevant code from my shell script to feed to slonik as follow:

cluster name = $CLUSTER;

​node 1 admin conninfo = 'dbname=$DB1 host=$H1 user=$U port=$P password=$W';
node 2 admin conninfo = 'dbname=$DB2 host=$H2 user=$U port=$P password=$W';

​create set (id=2, origin=1, comment='temporary cluster');

​set add table (set id=2, origin=1, id=363, fully qualified name = 
'public.table1');
set add sequence (set id=2, origin=1, id=364, fully qualified name = 
'public.table1_id_seq');


​subscribe set(id=2, provider=1,receiver=2);
merge set(id=1, add id=2,origin=1);


I figured there has to be a way to run a similar type of script that would let 
me drop the temporary
cluster based on the "set" details, but have never had the pgadmin GUI not work 
for me, so kinda stuck.
Anyone else ever deal with this?

Many thanks,
Javier



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Too much data update...

[Steve Singer via Slony1-general]

On Thu, 26 Mar 2020, Sebastien Marchand via Slony1-general wrote:


Hi,

I've got a replication fully fonctionnal, but after a big update of 
datas, the master doesn't succeed to sent all datas ( 7gB ) because 
their line is too bad ( 60kB/s UP ).


Can i set an option to sent less data in one time ?



Normal replication needs to send all the data from a given database 
transaction on the origin to a single transaction on the replica.  Maybe 
playing wtih the tcp keep-alive settings will help? 
https://www.slony.info/documentation/2.2/slon-config-connection.html


If not log shipping would let you send the data via files 
https://www.slony.info/documentation/2.2/logshipping.html



Steve



thanks in advance


Sébastien Marchand.

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Is slony 2.2.4 compatible with OSS 9.5.21?

[Steve Singer via Slony1-general]

On Wed, 25 Mar 2020, Rajni Baliyan via Slony1-general wrote:


Hello folks,

I need some pointers and recommendations regarding Slony and PostgreSQL 
minor/major version upgrade.




Also, I can't see any document which lists the changes from 2.2.4 to 2.2.8.


https://git.postgresql.org/gitweb/?p=slony1-engine.git;a=blob;f=RELEASE;h=7a572970389294a61502c6bb31f6cb4867af3098;hb=refs/heads/REL_2_2_STABLE





Looking forward for your recommendations.

Regards,
Saan




___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] NOTIFY/LISTEN for cache invalidation in clients of slony-i slaves

[Steve Singer via Slony1-general]

On 2/28/20 12:28 PM, John Muehlhausen via Slony1-general wrote:

Hello,

It appears that native streaming and logical replication in postgres 
disallows the use of NOTIFY on the master (fired by a trigger) 
propagating to a LISTENer on a slave.  (In the former case it is not 
implemented, and in the latter it is buggy with no signs of a fix.)


How does slony-i fare in this department?  I would hope that the same 
trigger would exist on the slave and that an update to a slave table 
would trigger it, causing the slave LISTENer to receive it.  Not sure 
why this is such a problem with native logical replication?


Also, how do I search the list archives?



Google with a site restriction probably works best for searching mailing 
list archives.


Ie a google search for

Notify site:lists.slony.info

will search for the word 'notify' in the mailing list archives.


Notify events fired on the master/origin won't propogate to a LISTEN on 
the replica with slony.


Steve





Thanks,
John

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] slony and postGIS

[Steve Singer]

On Tue, 21 Jan 2020, S.Bob wrote:

> Hi All;
>
>
> Does slony replicate postGIS geo columns? Anything special I need to do
> for geo columns?

It has been a few year since I've used postGIS but the last time I looked at 
it you could.

The Postgis functions that add metadata would need to be run on each node 
(ie PopulateGeometryColumn) since the catalog changes won't get replicated.

Steve


>
>
> Thanks in advance
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony 2.2.6 and Postgres 10

[Steve Singer]

On Thu, 12 Sep 2019, Tignor, Tom wrote:

>
>   Hello Slony community,
>   Wanted to check on this slony 2.2.6 message. The doc and the web 
> site indicate that v2.2.6 supports postgres 10. We are now running QA with 
> postgres v10.7. We are seeing this message in our log, though. Everything 
> seems to be working in spite of it. Is there some action we should take 
> here?
>
>   Possible unsupported PostgreSQL version (100700) 10.7, defaulting to 
> 8.4 support

No action is needed. 2.2.6 should be fine for 10.

We released 2.2.6 while PG10 was still in beta but it should work with 10. 
There have been no PG version compatibility changes to slony since 2.2.6 
that should impact 10.

>
>   Tom(
>
>
>
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

[Slony1-general] Slony 2.2.8 released

[Steve Singer]

The Slony team is pleased to announce Slony 2.2.8 the next minor release
of the Slony 2.2.x series


Slony 2.2.8 includes the following changes

   - Fix pg 8.4 messagaging in slonik
   - Fix documented default config values for slon
   - Add PG12 support
   - Makefile changes to  enable vpath builds
   - The win32 makefiles no longer define HAVE_PGPORT. win32
 users of slonik should set the SLONY_SHARE_DIR environment variable


Slony 2.2.8 can be downloaded from the following URL

http://www.slony.info/downloads/2.2/source/slony1-2.2.8.tar.bz2

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] URGENT- Slave taking long time to Sync

[Steve Singer]

On Mon, 5 Aug 2019, Rajni Baliyan wrote:

> Thanks Steve.
>
> Vacuum did not work and lag is increasing.
> Will slave catch up from where it stop if I unsubscribe and subscribe again.

If you unsubscribe and subscribe again it will copy all the data back to the 
subscriber.  This might be faster than letting replication catch up 
depending on the root cause.

> What is the best solution here to get things back on track?


Without understanding why things are slow it is hard to say what the 
solution should be.


>
> Thanks in advance
> Saan
>
>
>
>> On 3 Aug 2019, at 12:30 pm, Steve Singer  wrote:
>>
>> On Fri, 2 Aug 2019, Klaus Darilion wrote:
>>
>>>>
>>>> Is there a way I can see what exactly slony is currently doing? As logs
>>>> doesn't says anything.
>>
>> Turn logging to DEBUG.
>> The slons should log many things.
>>
>>
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] URGENT- Slave taking long time to Sync

[Steve Singer]

On Fri, 2 Aug 2019, Klaus Darilion wrote:

>>
>> Is there a way I can see what exactly slony is currently doing? As logs
>> doesn't says anything.

Turn logging to DEBUG.
The slons should log many things.


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Table fails initial sync

[Steve Singer]

On Thu, 11 Jul 2019, David Fetter wrote:

> On Thu, Jul 11, 2019 at 01:10:43PM -0400, st...@ssinger.info 
> st...@ssinger.info wrote:
>> 14GB isn't very big.
>> Any idea what is happening?
>
> Nope, and that's why I wrote here.
>
>> Are there any locks on the table (on the origin or replica) that could be 
>> preventing this?
>
> Not that I can see.
>
>> What is in the slon log, for the slon for the replica.
>
> Nothing in sl_log_1 or sl_log_2 on the either the origin or the replica, 
> nothing in
> /var/log/slony1/ on either the origin or the replica.

'nothing' in the log makes me think that your looking at the wrong log file, 
or logging isn't working.

I would expect to see something like the following when it starts to copy a 
table

INFO   copy_set 1 - omit=f - bool=0

Just before the copy starts

EDT CONFIG remoteWorkerThread_1: Begin COPY of table ..
(for each table)

It should go through with one of this for each table in the set. This 
indicates copying of that table is starting.

You might also see some indications that the remoteWorker is connecting to 
the origin database.
Ie
CONFIG remoteWorkerThread_1: connected to provider DB

Prior to the subscribe set being processed I would expect to still see

INFO   remoteWorkerThread_1: SYNC 500037 done in 0.001 seconds

type events in the log


>
>> Did the COPY start?
>
> How would I tell?
>
>> Did the replica get some data and then things hung?
>
> It did not get data. I have several other clusters running (different
> DBs, same hosts) without incident. It's just this one that's failing
> to do the initial sync.
>
> Best,
> David.
> -- 
> David Fetter  http://fetter.org/
> Phone: +1 415 235 3778
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] slony backup during pg_dump

[Steve Singer]

On Tue, 11 Jun 2019, Tory M Blue wrote:




Ya will take a look, I also thought that bypassing the slon schema would solve 
it but apparently not, we
can have 2-10million count in the sl_log before it clears and it's apparent 
that the replication is not
happening, so I'll poke around some more. Glad to see folks are still around :)

Thanks
Tory 


The large number of rows in sl_log is expected. The pg_dump opens a 
transaction and the rows can't be cleaned up by the cleanupThread until all 
transactions that might use those rows are gone.


I am a bit surprised that replication stops.
I know many sites take pg_dump's from slon replicas.

Steve

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Creating triggers on replicated table

[Steve Singer]

On Mon, 15 Apr 2019, Sung Hsin Lei wrote:

> Hello,
> I'm able to create triggers on a non replicated DB. However, It does not seem 
> to work on
> a replicated DB. I have a table A that is replicated to table B on another 
> machine. I want
> to add a trigger in table B so that when a row is added in table B, it 
> notifies a program
> that is listening. Unfortunately, the triggers that I implemented does not 
> seem to be raised
> when the table is a replication of another. Is there any way around this?

See the "DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER" section of
https://www.postgresql.org/docs/11/sql-altertable.html

You want your triggers to fire ALWAYS or only on the replica.

Steve

> 
> 
> Thanks.
> 
> Sung Hsin
> 
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Connections increase when upgrading to slony 2.2.7

[Steve Singer]

On Tue, 5 Mar 2019, SAS wrote:


Thank you for your answer.

I could easily drop some paths, and then lower the number of established 
connections on some
nodes.

Could you point me to what has changed between 2.1 and 2.2 branches that causes 
this connections
increase? The paths were there before the upgrade.


It has to do with the changes to how DDL is handled.
In 2.1 and earlier DDL was treated as a special type of event and was 
replicated by this event in sl_event.  There were cases where DDL and data 
could be replicated in the wrong order.


In 2.2 there is now a table specifically for DDL changes, like sl_log.  The 
DDL from this table is pulled in the correct order by the listener threads 
through the listener connection.


DDL can be submitted on any node, and for nodes that aren't providing a 
subscription there isn't a clear cascaded network route though other nodes.


For larger node setups it might be nice to have a way of designating nodes 
as 'edge only' nodes, where they can't be origin's, providers or failover 
targets and they can't accept DDL submissions.  These nodes wouldn't have to 
generate any events and so other nodes wouldn't have to listen 
for their events.  We don't currently support this though.


Hope that helps.


Best,

--
Dr Stéphane Schildknecht
Contact régional PostgreSQL pour l'Europe francophone
+33 617 11 37 42



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Connections increase when upgrading to slony 2.2.7

[Steve Singer]

On Wed, 27 Feb 2019, SAS wrote:

If you don't have paths between two nodes then there won't be a connection 
between them.


If your not going to have a complete network of paths then you should 
consider failure cases in determining how many paths you really need.







Hello,

We have a slony cluster with 11 nodes, and 3 sets. All PostgreSQL servers are 
still running a
9.2.x version. The upgrade of Slony is part of the PostgreSQL nodes upgrades, 
as we have to run a
more recent version, so as to be able to insert PG 11 nodes in the cluster.

The replication topology is as follow:

Set 1 :

1 --- 2
\ --- 3 --- 4
    \ --- 5
    \ --- 6
    \ --- 7
    \ --- 8
    \ --- 9
    \ --- 10
    \ --- 11

Set 2:

11 --- 12
\ --- 3 --- 4
    \ --- 5
    \ --- 6
    \ --- 7
    \ --- 8
    \ --- 9
    \ --- 1
    \ --- 2

Set 3:

1 --- 2
\ --- 3 --- 11
    \ --- 12

All slon daemons run on node 3.

When running slony 2.1.3, we had at most 20 or 25 connections on every 
database. Once we upgraded
to slony 2.2.7, we ended up with 107 slony connections on every node.

Is it a normal behaviour? Is there a way to lower that number, as not every 
node needs to contact
every other node?

Best, 

--
Dr Stéphane Schildknecht
Contact régional PostgreSQL pour l'Europe francophone
+33 617 11 37 42



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Understanding resubscribe

[Steve Singer]

On Tue, 9 Oct 2018, Kacey Holston wrote:


Thank you for your reply, I want to make sure I am absolutely clear before 
using the call on a production
system so I have two questions.
1. Currently C is a receiver of A, do I still need to use 

RESUBSCRIBE NODE  (
   ORIGIN = A,
   PROVIDER = A,
   RECEIVER = C
);

Current:
A --—> B 
        \__> C ——>D

Desired:

A >C >D
   \_>B



If C is already a direct subscriber to A then you don't need the above 
command.


2. Calling resubscribe node as below will only change B’s provider on all sets 
and leave all other nodes as
they are, is that correct?

RESUBSCRIBE NODE  (
   ORIGIN = A,
   PROVIDER = C,
   RECEIVER = B
);



Yes this should only change B's provider.

I encourage you to test this out on a test cluster with the same set 
and node topology as you'll be doing before trying this on production.





Thank you,

Kacey

  On Oct 8, 2018, at 2:47 PM, Steve Singer  wrote:

On Mon, 8 Oct 2018, Kacey Holston wrote:

  I have not had to use the resubscribe command since migrating to 2.2 and 
I am not quite
  understanding the
  docs. I am hoping someone can clarify how to use the call for me. 
  I have 4 nodes
  A is replicating to B and C and C is replicating to D.
  I would like to resubscribe B so it is a receiver from C so A is the 
provider to C and C
  is the provider to
  B and D.
  Would I run:
  RESUBSCRIBE NODE  (
    ORIGIN = A,
    PROVIDER = C,
    RECEIVER = B
  );
  And it will resubscribe all the sets? What id I wanted to only 
resubscribe some sets?
  There does not seem to
  be an option for this like there was with using subscribe set(). I see 
the function for
  reshapesubscription
  which seems to be more in line with what I want but it does not seem to 
be a callable.


I think you would first need to do

RESUBSCRIBE NODE  (
   ORIGIN = A,
   PROVIDER = A,
   RECEIVER = C
);

So C is receiving the data from A directly

then you could do

RESUBSCRIBE NODE  (
   ORIGIN = A,
   PROVIDER = C,
   RECEIVER = B
);

to point B at C.

If all your sets have A as the origin then they must all take the same route
to each subscriber.  You can't have some sets from A reach C through B and 
other sets from A reach C
directly.



  Any guidance would be greatly appreciated.
  — Kacey

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general




___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Understanding resubscribe

[Steve Singer]

On Mon, 8 Oct 2018, Kacey Holston wrote:


I have not had to use the resubscribe command since migrating to 2.2 and I am 
not quite understanding the
docs. I am hoping someone can clarify how to use the call for me. 
I have 4 nodes

A is replicating to B and C and C is replicating to D.

I would like to resubscribe B so it is a receiver from C so A is the provider 
to C and C is the provider to
B and D.

Would I run:

RESUBSCRIBE NODE  (
   ORIGIN = A,
   PROVIDER = C,
   RECEIVER = B
);
And it will resubscribe all the sets? What id I wanted to only resubscribe some 
sets? There does not seem to
be an option for this like there was with using subscribe set(). I see the 
function for reshapesubscription
which seems to be more in line with what I want but it does not seem to be a 
callable.


I think you would first need to do

RESUBSCRIBE NODE  (
ORIGIN = A,
PROVIDER = A,
RECEIVER = C
 );

So C is receiving the data from A directly

then you could do

RESUBSCRIBE NODE  (
ORIGIN = A,
PROVIDER = C,
RECEIVER = B
 );

to point B at C.

If all your sets have A as the origin then they must all take the same route
to each subscriber.  You can't have some sets from A reach C through B and 
other sets from A reach C directly.






Any guidance would be greatly appreciated.

— Kacey


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] enable_version_check

[Steve Singer]

On Fri, 31 Aug 2018, Andy Dossett wrote:

I have carried out a basic test with the master at 2.2.7, with 
enable_version_check ‘off’, and a slave at 2.2.3. The slave still checks 
versions.


My conclusion is that this option only has any value when master and slave 
are at, or above, 2.2.7. The only way I can see it working for older 
versions is if the master holds the version number of the slave and ‘lies’ 
by reporting the stored version number when queried by the slave.


I think 'version number of the slave' is a bit misleading.

You have slonik, slon, and the stored functions.
Only the stored functions are directly tied to the version number 'of the 
slave'.


You could do something like the following.

Step 1) Upgrade all your slon processes to be 2.2.7 and set
enable_version_check=false.  Slon should start.

Step 2) Upgrade your slonik instance

Step 3) Upgrade each database and then run UPDATE FUNCTIONS on each

Set enable_version_check=true again

This of course should be tested.





Here’s some snippets from the logs;

2.2.7 (master)
2018-08-31 14:00:05 BST [20062] CONFIG main: slon version 2.2.7 starting up
2018-08-31 14:00:05 BST [20063] CONFIG main: Boolean option 
enable_version_check = 0

2.2.3 (slave)
2018-08-31 14:09:17 BST [10640] CONFIG version for "host=xxx.xxx.xxx.xxx 
dbname=posdb user=postgres port=5432 password=abcdefg" is 90603
2018-08-31 14:09:17 BST [10640] ERROR  Slony-I schema version is 2.2.7
2018-08-31 14:09:17 BST [10640] ERROR  please upgrade Slony-I schema to version 
2.2.3
2018-08-31 14:09:17 BST [10640] ERROR  Slony-I module version is 2.2.7
2018-08-31 14:09:17 BST [10640] ERROR  please upgrade Slony-I shared module to 
version 2.2.3
2018-08-31 14:09:17 BST [10640] ERROR  remoteListenThread_1: 
db_checkSchemaVersion() failed

A few other things from the log.

Both 2.2.3 and 2.2.7 warn that three config options are missing, yet complain 
that those same options are present! Note, no line break for the ‘not found’ 
warning.
2018-08-31 14:00:05 BST [0] WARN   conf option sync_max_rowsize not found2018-08-31 
14:00:05 BST [0] WARN   unrecognized configuration parameter "sync_max_rowsize"
2018-08-31 14:00:05 BST [0] WARN   conf option sync_max_largemem not found2018-08-31 
14:00:05 BST [0] WARN   unrecognized configuration parameter "sync_max_largemem"
2018-08-31 14:00:05 BST [0] WARN   conf option desired_sync_time not found2018-08-31 
14:00:05 BST [0] WARN   unrecognized configuration parameter "desired_sync_time"

The log says that enable_version_check is a boolean option – the documentation 
says it is integer.

Should the password in the reporting of the connection string be obfuscated?



-Original Message-
From: Steve Singer 
Sent: 27 August 2018 02:28
To: andy.doss...@btinternet.com
Cc: slony1-general@lists.slony.info
Subject: Re: [Slony1-general] enable_version_check

On Fri, 24 Aug 2018, andy.doss...@btinternet.com wrote:


Hi

We are currently running our master server on SuSE 10 with Postgres
9.3 and Slony 2.2.3. Our slaves are running on CentOS, also with Postgres 9.3 
and Slony 2.2.3.

We are running a project to upgrade the master to SuSE 12 and Postgres
9.6. Unfortunately Slony 2.2.3 would not install so we went up to
2.2.6. Unfortunately this means that we have to upgrade the slaves to 2.2.6 as 
well.

However, 2.2.7 has just been released which includes the enable_version_check 
feature.

If we went up to 2.2.7 on the master and turned version checking off,
would this enable master and slaves to replicate, or would the version checking 
in the slave prevent it?

If turning off version checking does allow replication, are 2.2.3 and 2.2.7 
compatible with each other?


You should test it but there is a good chance data replication will work 
between them.  Just looking at the release notes I don't see anything that 
would obviously break data replication. Configuration events, including 
failovers  and DDL replication did have some changes though.





Thanks

 Andy







___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] enable_version_check

[Steve Singer]

On Fri, 24 Aug 2018, andy.doss...@btinternet.com wrote:


Hi

We are currently running our master server on SuSE 10 with Postgres 9.3 and 
Slony 2.2.3. Our slaves are
running on CentOS, also with Postgres 9.3 and Slony 2.2.3.

We are running a project to upgrade the master to SuSE 12 and Postgres 9.6. 
Unfortunately Slony 2.2.3 would
not install so we went up to 2.2.6. Unfortunately this means that we have to 
upgrade the slaves to 2.2.6 as
well.

However, 2.2.7 has just been released which includes the enable_version_check 
feature.

If we went up to 2.2.7 on the master and turned version checking off, would 
this enable master and slaves
to replicate, or would the version checking in the slave prevent it?

If turning off version checking does allow replication, are 2.2.3 and 2.2.7 
compatible with each other?


You should test it but there is a good chance data replication will work 
between them.  Just looking at the release notes I don't see anything that 
would obviously break data replication. Configuration events, including 
failovers  and DDL replication did have some changes though.






Thanks

 Andy




___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

[Slony1-general] Slony 2.2.7 released

[Steve Singer]

The Slony team is pleased to announce Slony 2.2.7 the next minor release
of the Slony 2.2.x series


Slony 2.2.7 includes the following changes

   - Fix warning with flex 2.6+
   - Fix compile errors with PG11
   - Fix bug in with regex in 'set add sequence' when
 specifying the sequence as a regular expression.
 It was not being escaped properly
   - Add support to Slonik to specify the share direcotry
 using the environment variable SLONY_SHARE_DIR
   - Add slon config setting remote_listen_serializable_transactions
 to use read committed instead of read-only-serializable deferable
 transactions(default true)
   - Add slon config setting enable_version_check to disable
 the slony version check that ensures all nodes run the
 same slony version (default true, version check enabled)


Slony 2.2.7 can be downloaded from the following URL

http://www.slony.info/downloads/2.2/source/slony1-2.2.7.tar.bz2
___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Inconsistent regex handling between SET ADD TABLE and SEQUENCE

[Steve Singer]

On Wed, 6 Jun 2018, Steve Singer wrote:

> On Wed, 6 Jun 2018, Marek Becka wrote:
>
>> Hello,
>>
>>
>> since Slony-I 2.1 a POSIX regular expresions can be used for adding
>> tables and sequences to a replication set. I have tried to add all
>> tables using Slony 2.2.5 documentation example of command SET ADD TABLE:
>>
>> SET ADD TABLE (SET ID=1, TABLES='public\\.tracker*');
>>
>> In this case backslash must be escaped to get intended regular expression.
>>
>>
>> Regular expresions used with SET ADD SEQUENCE doesn't works correctly
>> with doubled backslash. To select all sequences from public schema plain
>> regular expression must be used:
>>
>> SET ADD SEQUENCE (SET ID=1, SEQUENCES='public\.*');
>>
>>
>> Shouldn't be both command handled consistently?
>
> Yes, it probably should. This looks like a bug
>

I have pushed a fix for this to master and REL_2_2_STABLE



>>
>>
>> ___
>> Slony1-general mailing list
>> Slony1-general@lists.slony.info
>> http://lists.slony.info/mailman/listinfo/slony1-general
>>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Problème with sl_listen and too many nodes

[Steve Singer]

On Fri, 20 Jul 2018, Sébastien Marchand wrote:

In fact I have only 2 servers in datacenter all the others (16) are in 
agency scattered in the country (low bandwidth). So no server will become 
master except one. I noticed quite quickly that past a dozen nodes servers 
consumes too much resource cpu, DD and network.


So if I want the sl_listen table to no longer be filled can I modify the 
function RebuildListenEntries ()?


Thx you for your time.


Yes you can modify the function. Will this break anything? I'm not sure, you 
would need to test.


I think you might find that SYNC events generated on the agency servers with 
don't get deleted/removed because those events never get processed or 
confirmed by the other nodes.


This might pose a problem with respect to sl_event growing. It would also 
pose a problem if slonik ever tried to perform a 'wait for event'.

I don't think it would cause a problem with normal data replication.

Steve






-Message d'origine-
De : Steve Singer [mailto:ssin...@afilias.info] 
Envoyé : jeudi 19 juillet 2018 15:33

À : Sébastien Marchand
Cc : slony1-general@lists.slony.info
Objet : Re: [Slony1-general] Problème with sl_listen and too many nodes

On 07/19/2018 04:05 AM, Sébastien Marchand wrote:

RebuildListenEntries() is the function that populates sl_listen.

With slony, any node can be the source of an event, possibly because 
that event was submitted to via slonik.


This means that node 2 must be listening for events with origin=node 2 
and origin=130.


When you perform your 'cleanup' you actually don't have a way for node 2 
to receive events from 130.


Node 2 could receive events directly from 130

origin;provider;receiver
130,130,2

or via node 1
130,1,2

but here must be at least one row for each origin,receiver pair.

If there is a sl_subscribe row connecting origin=130 to receiver=2 then 
that is what the sl_listen row should also look like.


However if there is no subscription between those nodes then the 
sl_listen is built with all possibilities.  I think my reasoning for 
this was because if nodes fail events can still propogate(in particular 
events used in the failover process).


Is there a particular problem the larger listen network is causing?





Sl_subscribe of my test :
sub_set;sub_provider;sub_receiver;sub_forward;sub_active
1;1;2;FALSE;TRUE
1;1;130;FALSE;TRUE

Sl_path :
pa_server;pa_client;pa_conninfo
1;2;dbname=DB host=192.168.0.29 port=5432 user=slony password=123;10
1;130;dbname=DB host=192.168.0.29 port=5432 user=slony password=123;10
2;1;dbname=DB host=192.168.0.3 port=5432 user=slony password=123;10
130;1;dbname=DB host=192.168.0.230 port=5432 user=slony password=123;10

Sub_forward true or false change nothing...

Thx for your help.

-Message d'origine-
De : Steve Singer [mailto:st...@ssinger.info]
Envoyé : jeudi 19 juillet 2018 05:42
À : Sébastien Marchand
Cc : slony1-general@lists.slony.info
Objet : Re: [Slony1-general] Problème with sl_listen and too many nodes

On Wed, 18 Jul 2018, Sébastien Marchand wrote:

What is sl_subscribe?

(I assume sl_path has paths between each node)






Hello,



I have a problem with the sl_listen table.

I have a replication that has been running for a very long time from a

master to X slaves (1 master

node for 18 nodes)

My concern comes from the sl_listen table which instead of just containing

what it needs it creates

all the possible combinations.

For example I have a test for 1 master and 2 slaves and I have 4 too many

lines that are useless

for me:



SL_LISTEN table

origin; provider; receiver

1, 1, 2

1; 1; 130

2; 1; 130

2; 2; 1

2; 130; 1

130, 1, 2

130, 2, 1

130; 130; 1



Here after cleaning what should be:



origin; provider; receiver

1, 1, 2

1; 1; 130

2; 2; 1

130; 130; 1



The problem is that with each add / delete of tables / nodes the table is

re-filled again and I

have to redo the cleaning.



My final question is: Is it normal for all nodes to talk to each other?



Thank you.





___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general




___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general
___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Problème with sl_listen and too many nodes

[Steve Singer]

On Wed, 18 Jul 2018, Sébastien Marchand wrote:

What is sl_subscribe?

(I assume sl_path has paths between each node)






Hello,

 

I have a problem with the sl_listen table.

I have a replication that has been running for a very long time from a master 
to X slaves (1 master
node for 18 nodes)

My concern comes from the sl_listen table which instead of just containing what 
it needs it creates
all the possible combinations.

For example I have a test for 1 master and 2 slaves and I have 4 too many lines 
that are useless
for me:

 

SL_LISTEN table

origin; provider; receiver

1, 1, 2

1; 1; 130

2; 1; 130

2; 2; 1

2; 130; 1

130, 1, 2

130, 2, 1

130; 130; 1

 

Here after cleaning what should be:

 

origin; provider; receiver

1, 1, 2

1; 1; 130

2; 2; 1

130; 130; 1

 

The problem is that with each add / delete of tables / nodes the table is 
re-filled again and I
have to redo the cleaning.

 

My final question is: Is it normal for all nodes to talk to each other?

 

Thank you.



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Inconsistent regex handling between SET ADD TABLE and SEQUENCE

[Steve Singer]

On Wed, 6 Jun 2018, Marek Becka wrote:

> Hello,
>
>
> since Slony-I 2.1 a POSIX regular expresions can be used for adding
> tables and sequences to a replication set. I have tried to add all
> tables using Slony 2.2.5 documentation example of command SET ADD TABLE:
>
> SET ADD TABLE (SET ID=1, TABLES='public\\.tracker*');
>
> In this case backslash must be escaped to get intended regular expression.
>
>
> Regular expresions used with SET ADD SEQUENCE doesn't works correctly
> with doubled backslash. To select all sequences from public schema plain
> regular expression must be used:
>
> SET ADD SEQUENCE (SET ID=1, SEQUENCES='public\.*');
>
>
> Shouldn't be both command handled consistently?

Yes, it probably should. This looks like a bug

>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Useless warning for pg 10 support

[Steve Singer]

On Mon, 19 Mar 2018, Jehan-Guillaume de Rorthais wrote:

Thanks.
I've pushed this change.


Hi all,

Sorry for writing on -general about a bug report, but -bugs seems spammed
according to the archives.

Despite support for PostgreSQL 10, while working with Slony 2.2.6 and
PostgreSQL v10.3, I hit the following (common and mainly painless) warning
message:

«
Possible unsupported PostgreSQL version (100300) 10.3, defaulting to 8.4
support 
»


It seems to me the check in slonik.c might be wrong:

https://git.postgresql.org/gitweb/?p=slony1-engine.git;a=blob;f=src/slonik/slonik.c;h=a30616129018c15f15eea60c73ffcb8c1489c4e2;hb=5aced469e8859174e84ccde031c28eaf7b30b127#l2079

I suppose:

 ((adminfo->pg_version >= 9) && (adminfo->pg_version < 10)) /* 10 */

Should be:

 ((adminfo->pg_version >= 9) && (adminfo->pg_version < 11)) /* 10 */

Or maybe (considering we will not have more than 99 minor release for 10):

 ((adminfo->pg_version >= 9) && (adminfo->pg_version < 100100)) /* 10 */

Indeed, 10 is 10 and 10.3 is 13, so (adminfo->pg_version < 10) is
always false for v10.x.

I was volunteering to make a pull request on github, but I couldn't find up to
date sources for 2.2.6. Last patch on REL_2_2_STABLE is almost 2 years ago:

https://github.com/ssinger/slony1-engine/blob/REL_2_2_STABLE/src/slonik/slonik.c

Cheers,
___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general
___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] slony1 drop node failure

[Steve Singer]

On Mon, 26 Feb 2018, Tignor, Tom wrote:



In the slony1 log of our primary host (the same one which later showed 
the bug) we had:

2018-02-21 19:19:49 UTC [22582] INFO   remoteWorkerThread_8: SYNC 5002075962 
done in 1.725 seconds
2018-02-21 19:19:50 UTC [22582] CONFIG disableNode: no_id=8

	The timestamp of the problem event was somewhat earlier: 2018-02-21 
19:19:41.
	To me it looks like there is a race condition and despite the order 
of the log events, the DROP NODE was processed first and the SYNC was 
accepted later, and thus created an sl_event record. Of course, that’s 
simply a guess.


This might be possible.

If the drop being droped is 8, and the event node from the drop is 4.
Some third node say 3 might have a sequence like this

remoteWorkerThread_8: Starts processing SYNC
remoteWorkerThread_4: process drop node. Updates runtime config
remoteWorkerThread_8: finishes processing sync COMMITS

I'd feel more comfortable if we had a way of reproducing this though.

One option might be to have the remoteWorkerThread recheck the runtime 
config before the COMMIT to make sure that the node is still active.







Tom(


On 2/26/18, 12:01 PM, "Steve Singer" <st...@ssinger.info> wrote:

   On Mon, 26 Feb 2018, Tignor, Tom wrote:

   >
   > Thanks. I see the deletes added for sl_seqlog and sl_log_script. 
The
   > constraint violation appearing in the errors was for sl_event. Do we
   > expect these changes fully remove all state, including sl_event? The
   > checkNodeDeleted function doesn’t look at sl_event.

   It could be that this is a different issue then.
   That function (the dropNode_int) should have removed the rows from sl_event.
   The question the becomes did it not remove them for some reason, or did they
   get added back later, and if so how?


   > Tom(
   >
   >
   > On 2/26/18, 11:03 AM, "Steve Singer" <st...@ssinger.info> wrote:
   >
   >On Mon, 26 Feb 2018, Tignor, Tom wrote:
   >
   >You can get it from the github branch (latest commit) at
   >https://github.com/ssinger/slony1-engine/tree/bug375
   >
   >
   >
   >
   >>
   >> Steve,
   >> The patch link actually goes to a page which says “Bugzilla 
is down for maintenance.” Is there a way to see the patch currently? Does it exist or 
is it scheduled in some Slony-I release?
   >>
   >> Tom(
   >>
   >>
   >> On 2/22/18, 6:06 PM, "Steve Singer" <st...@ssinger.info> wrote:
   >>
   >>On Thu, 22 Feb 2018, Tignor, Tom wrote:
   >>
   >>Looks like?
   >>
http://lists.slony.info/pipermail/slony1-general/2016-September/013331.html
   >>
   >>I can't remember if that was what prompted
   >>
http://lists.slony.info/pipermail/slony1-hackers/2016-December/000560.html
   >>
   >>
   >>https://github.com/ssinger/slony1-engine/tree/bug375
   >>
   >>I can't seem to find a reason why this wasn't committed.
   >>
   >>
   >>>
   >>>
   >>>
   >>> Hello slony1 community,
   >>>
   >>> We have a head scratcher here. It appears a DROP 
NODE command was not fully processed. The
   >>> command was issued and confirmed on all our nodes at 
approximately 2018-02-21 19:19:50 UTC. When we went to
   >>> restore it over two hours later, all replication stopped on an 
sl_event constraint violation. Investigation
   >>> showed a SYNC event for the dropped node with a timestamp of 
just a few seconds before the drop. I believe this
   >>> is a first for us. The DROP NODE command is supposed to remove 
all state for the dropped node. Is that right? Is
   >>> there a potential race condition somewhere which could leave 
behind state?
   >>>
   >>> Thanks in advance,
   >>>
   >>>
   >>>
   >>>  master log replication freeze error 
   >>>
   >>> 2018-02-21 21:38:52 UTC [5775] ERROR  remoteWorkerThread_8: "insert into 
"_ams_cluster".sl_event (ev_origin,
   >>> ev_seqno, ev_timestamp,  ev_snapshot, ev\
   >>>
   >>> _type ) values ('8', '5002075962', '2018-02-21 
19:19:41.958719+00', '87044110:87044110:', 'SYNC'); insert
   >>> into "_ams_cluster".sl_confirm   (con_origi\
   >>>
   >>> n, con_received, con_seqno

Re: [Slony1-general] slony1 drop node failure

[Steve Singer]

On Mon, 26 Feb 2018, Tignor, Tom wrote:



	Thanks. I see the deletes added for sl_seqlog and sl_log_script. The 
constraint violation appearing in the errors was for sl_event. Do we 
expect these changes fully remove all state, including sl_event? The 
checkNodeDeleted function doesn’t look at sl_event.


It could be that this is a different issue then.
That function (the dropNode_int) should have removed the rows from sl_event. 
The question the becomes did it not remove them for some reason, or did they 
get added back later, and if so how?




Tom(


On 2/26/18, 11:03 AM, "Steve Singer" <st...@ssinger.info> wrote:

   On Mon, 26 Feb 2018, Tignor, Tom wrote:

   You can get it from the github branch (latest commit) at
   https://github.com/ssinger/slony1-engine/tree/bug375




   >
   > Steve,
   > The patch link actually goes to a page which says “Bugzilla is 
down for maintenance.” Is there a way to see the patch currently? Does it exist or 
is it scheduled in some Slony-I release?
   >
   > Tom(
   >
   >
   > On 2/22/18, 6:06 PM, "Steve Singer" <st...@ssinger.info> wrote:
   >
   >On Thu, 22 Feb 2018, Tignor, Tom wrote:
   >
   >Looks like?
   >
http://lists.slony.info/pipermail/slony1-general/2016-September/013331.html
   >
   >I can't remember if that was what prompted
   >
http://lists.slony.info/pipermail/slony1-hackers/2016-December/000560.html
   >
   >
   >https://github.com/ssinger/slony1-engine/tree/bug375
   >
   >I can't seem to find a reason why this wasn't committed.
   >
   >
   >>
   >>
   >>
   >> Hello slony1 community,
   >>
   >> We have a head scratcher here. It appears a DROP NODE 
command was not fully processed. The
   >> command was issued and confirmed on all our nodes at approximately 
2018-02-21 19:19:50 UTC. When we went to
   >> restore it over two hours later, all replication stopped on an 
sl_event constraint violation. Investigation
   >> showed a SYNC event for the dropped node with a timestamp of just a 
few seconds before the drop. I believe this
   >> is a first for us. The DROP NODE command is supposed to remove all 
state for the dropped node. Is that right? Is
   >> there a potential race condition somewhere which could leave behind 
state?
   >>
   >> Thanks in advance,
   >>
   >>
   >>
   >>  master log replication freeze error 
   >>
   >> 2018-02-21 21:38:52 UTC [5775] ERROR  remoteWorkerThread_8: "insert into 
"_ams_cluster".sl_event (ev_origin,
   >> ev_seqno, ev_timestamp,  ev_snapshot, ev\
   >>
   >> _type ) values ('8', '5002075962', '2018-02-21 
19:19:41.958719+00', '87044110:87044110:', 'SYNC'); insert
   >> into "_ams_cluster".sl_confirm   (con_origi\
   >>
   >> n, con_received, con_seqno, con_timestamp)values (8, 1, 
'5002075962', now()); select
   >> "_ams_cluster".logApplySaveStats('_ams_cluster', 8, '0.139 s'::inter\
   >>
   >> val); commit transaction;" PGRES_FATAL_ERROR ERROR:  duplicate key 
value violates unique constraint
   >> "sl_event-pkey"
   >>
   >> DETAIL:  Key (ev_origin, ev_seqno)=(8, 5002075962) already exists.
   >>
   >> 2018-02-21 21:38:52 UTC [13649] CONFIG slon: child terminated signal: 
9; pid: 5775, current worker pid: 5775
   >>
   >> 2018-02-21 21:38:52 UTC [13649] CONFIG slon: restart of worker in 10 
seconds
   >>
   >>  master log replication freeze error 
   >>
   >>
   >>
   >>
   >>
   >>  master DB leftover event 
   >>
   >> a...@ams6.cmb.netmgmt:~$ psql -U akamai -d ams
   >>
   >> psql (9.1.24)
   >>
   >> Type "help" for help.
   >>
   >>
   >>
   >> ams=# select * from sl_event_bak;
   >>
   >>  ev_origin |  ev_seqno  | ev_timestamp  |
ev_snapshot | ev_type | ev_data1 | ev_data2 |
   >> ev_data3 | ev_data4 | ev_data5 | ev_data6 | ev_
   >>
   >> data7 | ev_data8
   >>
   >> 
---++---++-+--+--+-
   >> -+--+--+--+
   >>
   >> --+--
   >>
   >>  8 | 5002075962 | 2018-02-21 19:1

Re: [Slony1-general] slony1 drop node failure

[Steve Singer]

On Mon, 26 Feb 2018, Tignor, Tom wrote:

You can get it from the github branch (latest commit) at 
https://github.com/ssinger/slony1-engine/tree/bug375







Steve,
The patch link actually goes to a page which says “Bugzilla is down for 
maintenance.” Is there a way to see the patch currently? Does it exist or is it 
scheduled in some Slony-I release?

Tom(


On 2/22/18, 6:06 PM, "Steve Singer" <st...@ssinger.info> wrote:

   On Thu, 22 Feb 2018, Tignor, Tom wrote:

   Looks like?
   http://lists.slony.info/pipermail/slony1-general/2016-September/013331.html

   I can't remember if that was what prompted
   http://lists.slony.info/pipermail/slony1-hackers/2016-December/000560.html


   https://github.com/ssinger/slony1-engine/tree/bug375

   I can't seem to find a reason why this wasn't committed.


   >
   >
   >
   > Hello slony1 community,
   >
   > We have a head scratcher here. It appears a DROP NODE 
command was not fully processed. The
   > command was issued and confirmed on all our nodes at approximately 
2018-02-21 19:19:50 UTC. When we went to
   > restore it over two hours later, all replication stopped on an sl_event 
constraint violation. Investigation
   > showed a SYNC event for the dropped node with a timestamp of just a few 
seconds before the drop. I believe this
   > is a first for us. The DROP NODE command is supposed to remove all state 
for the dropped node. Is that right? Is
   > there a potential race condition somewhere which could leave behind state?
   >
   > Thanks in advance,
   >
   >
   >
   >  master log replication freeze error 
   >
   > 2018-02-21 21:38:52 UTC [5775] ERROR  remoteWorkerThread_8: "insert into 
"_ams_cluster".sl_event (ev_origin,
   > ev_seqno, ev_timestamp,  ev_snapshot, ev\
   >
   > _type ) values ('8', '5002075962', '2018-02-21 19:19:41.958719+00', 
'87044110:87044110:', 'SYNC'); insert
   > into "_ams_cluster".sl_confirm   (con_origi\
   >
   > n, con_received, con_seqno, con_timestamp)values (8, 1, '5002075962', 
now()); select
   > "_ams_cluster".logApplySaveStats('_ams_cluster', 8, '0.139 s'::inter\
   >
   > val); commit transaction;" PGRES_FATAL_ERROR ERROR:  duplicate key value 
violates unique constraint
   > "sl_event-pkey"
   >
   > DETAIL:  Key (ev_origin, ev_seqno)=(8, 5002075962) already exists.
   >
   > 2018-02-21 21:38:52 UTC [13649] CONFIG slon: child terminated signal: 9; 
pid: 5775, current worker pid: 5775
   >
   > 2018-02-21 21:38:52 UTC [13649] CONFIG slon: restart of worker in 10 
seconds
   >
   >  master log replication freeze error 
   >
   >
   >
   >
   >
   >  master DB leftover event 
   >
   > a...@ams6.cmb.netmgmt:~$ psql -U akamai -d ams
   >
   > psql (9.1.24)
   >
   > Type "help" for help.
   >
   >
   >
   > ams=# select * from sl_event_bak;
   >
   >  ev_origin |  ev_seqno  | ev_timestamp  |ev_snapshot   
  | ev_type | ev_data1 | ev_data2 |
   > ev_data3 | ev_data4 | ev_data5 | ev_data6 | ev_
   >
   > data7 | ev_data8
   >
   > 
---++---++-+--+--+-
   > -+--+--+--+
   >
   > --+--
   >
   >  8 | 5002075962 | 2018-02-21 19:19:41.958719+00 | 
87044110:87044110: | SYNC|  |  |
   > |  |  |  |
   >
   >   |
   >
   > (1 row)
   >
   >
   >
   > ams=#
   >
   >  master DB leftover event 
   >
   >
   >
   >  master log drop node record 
   >
   > 2018-02-21 19:19:50 UTC [22582] CONFIG disableNode: no_id=8
   >
   > 2018-02-21 19:19:50 UTC [22582] CONFIG storeListen: li_origin=4 
li_receiver=1 li_provider=4
   >
   > 2018-02-21 19:19:50 UTC [22582] CONFIG storeListen: li_origin=7 
li_receiver=1 li_provider=7
   >
   > 2018-02-21 19:19:50 UTC [22582] CONFIG storeListen: li_origin=3 
li_receiver=1 li_provider=3
   >
   > 2018-02-21 19:19:50 UTC [22582] CONFIG remoteWorkerThread_4: update 
provider configuration
   >
   > 2018-02-21 19:19:50 UTC [22582] CONFIG remoteWorkerThread_4: connection 
for provider 4 terminated
   >
   > 2018-02-21 19:19:50 UTC [22582] CONFIG remoteWorkerThread_4: disconnecting 
from data provider 4
   >
   > 2018-02-21 19:19:50 UTC [22582] CONFIG remoteWorkerThread_4: connection 
for provider 7 terminated
   >
   >  master log drop node record 
   >
   >
   >
   >  replica log drop node record 
   >
   > 2018-02-21 19:19:51 UTC [22

Re: [Slony1-general] slony1 drop node failure

[Steve Singer]

On Thu, 22 Feb 2018, Tignor, Tom wrote:

Looks like?
http://lists.slony.info/pipermail/slony1-general/2016-September/013331.html

I can't remember if that was what prompted 
http://lists.slony.info/pipermail/slony1-hackers/2016-December/000560.html



https://github.com/ssinger/slony1-engine/tree/bug375

I can't seem to find a reason why this wasn't committed.




 

    Hello slony1 community,

    We have a head scratcher here. It appears a DROP NODE command 
was not fully processed. The
command was issued and confirmed on all our nodes at approximately 2018-02-21 
19:19:50 UTC. When we went to
restore it over two hours later, all replication stopped on an sl_event 
constraint violation. Investigation
showed a SYNC event for the dropped node with a timestamp of just a few seconds 
before the drop. I believe this
is a first for us. The DROP NODE command is supposed to remove all state for 
the dropped node. Is that right? Is
there a potential race condition somewhere which could leave behind state?

    Thanks in advance,

 

 master log replication freeze error 

2018-02-21 21:38:52 UTC [5775] ERROR  remoteWorkerThread_8: "insert into 
"_ams_cluster".sl_event     (ev_origin,
ev_seqno, ev_timestamp,      ev_snapshot, ev\

_type     ) values ('8', '5002075962', '2018-02-21 19:19:41.958719+00', 
'87044110:87044110:', 'SYNC'); insert
into "_ams_cluster".sl_confirm       (con_origi\

n, con_received, con_seqno, con_timestamp)    values (8, 1, '5002075962', 
now()); select
"_ams_cluster".logApplySaveStats('_ams_cluster', 8, '0.139 s'::inter\

val); commit transaction;" PGRES_FATAL_ERROR ERROR:  duplicate key value 
violates unique constraint
"sl_event-pkey"

DETAIL:  Key (ev_origin, ev_seqno)=(8, 5002075962) already exists.

2018-02-21 21:38:52 UTC [13649] CONFIG slon: child terminated signal: 9; pid: 
5775, current worker pid: 5775

2018-02-21 21:38:52 UTC [13649] CONFIG slon: restart of worker in 10 seconds

 master log replication freeze error 

 

 

 master DB leftover event 

a...@ams6.cmb.netmgmt:~$ psql -U akamai -d ams

psql (9.1.24)

Type "help" for help.

 

ams=# select * from sl_event_bak;

 ev_origin |  ev_seqno  |         ev_timestamp          |    ev_snapshot     | 
ev_type | ev_data1 | ev_data2 |
ev_data3 | ev_data4 | ev_data5 | ev_data6 | ev_

data7 | ev_data8 

---++---++-+--+--+-
-+--+--+--+

--+--

         8 | 5002075962 | 2018-02-21 19:19:41.958719+00 | 87044110:87044110: | 
SYNC    |          |          | 
        |          |          |          |    

      | 

(1 row)

 

ams=# 

 master DB leftover event 

 

 master log drop node record 

2018-02-21 19:19:50 UTC [22582] CONFIG disableNode: no_id=8

2018-02-21 19:19:50 UTC [22582] CONFIG storeListen: li_origin=4 li_receiver=1 
li_provider=4

2018-02-21 19:19:50 UTC [22582] CONFIG storeListen: li_origin=7 li_receiver=1 
li_provider=7

2018-02-21 19:19:50 UTC [22582] CONFIG storeListen: li_origin=3 li_receiver=1 
li_provider=3

2018-02-21 19:19:50 UTC [22582] CONFIG remoteWorkerThread_4: update provider 
configuration

2018-02-21 19:19:50 UTC [22582] CONFIG remoteWorkerThread_4: connection for 
provider 4 terminated

2018-02-21 19:19:50 UTC [22582] CONFIG remoteWorkerThread_4: disconnecting from 
data provider 4

2018-02-21 19:19:50 UTC [22582] CONFIG remoteWorkerThread_4: connection for 
provider 7 terminated

 master log drop node record 

 

 replica log drop node record 

2018-02-21 19:19:51 UTC [22650] WARN   remoteWorkerThread_1: got DROP NODE for 
local node ID

NOTICE:  Slony-I: Please drop schema "_ams_cluster"

2018-02-21 19:19:53 UTC [22650] INFO   remoteWorkerThread_7: SYNC 5001868819 
done in 2.153 seconds

NOTICE:  drop cascades to 243 other objects

DETAIL:  drop cascades to table _ams_cluster.sl_node

drop cascades to table _ams_cluster.sl_nodelock

drop cascades to table _ams_cluster.sl_set

drop cascades to table _ams_cluster.sl_setsync

drop cascades to table _ams_cluster.sl_table

drop cascades to table _ams_cluster.sl_sequence

 replica log drop node record 

 

 

    Tom    ☺

 

 



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] 2.2.4 -> 2.2.6 upgrade

[Steve Singer]

On Mon, 13 Nov 2017, Tignor, Tom wrote:



 

    Hello Slony-I community,

    We’re working on a postgres upgrade now and considering a slony 
2.2.4 to 2.2.6
upgrade to perform at the same time. I’ve found the Slony-I Upgrade section in 
the doc. This
describes an all-at-once upgrade procedure which is difficult for us. For a 
2.2.4 to 2.2.6 upgrade,
what would be the barriers to upgrading each node in turn? Of course we’ve seen 
the schema check in
remote_listen.c. However, the 2.2.6 upgradeSchema function looks like it has 
little or no work to do.
Setting aside the schema check, are there any data format or other problems 
which would prevent 2.2.4
from replicating from 2.2.6 or vice versa?



Other than the checks in place to explicitly prevent this, I don't think 
there is anything in 2.2.5 or 2.2.6 that would stop this from working.


This is from memory and glancing at the release notes, Obviously you would 
need to do your own testing (and disable version checks)






   

    Tom    ☺

 

 



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

[Slony1-general] Slony 2.2.6 released

[Steve Singer]

The Slony team is pleased to announce Slony 2.2.6 the next minor release
of the Slony 2.2.x series

Slony 2.2.6 includes the following changes

 - slonik_build_env can now accept multiple -schema options on the 
command line
- Support for PG10. This involved changes to PG version detection
- Disallow createEvent and data changes in the same transaction.
  This also fixes some issues when the logApply trigger invokes the
  data change trigger by inserting into a table with a trigger that
  in turns inserts into another replicated table.
- Fix some failover issues when doing a multi-node failover
  with a cascaded node.
- Bug 341 - suppress log trigger/deny when running in 'local' mode
- Fix issue when receiving DDL from non origin nodes


Slony 2.2.6 can be downloaded from the following URL

http://www.slony.info/downloads/2.2/source/slony1-2.2.6.tar.bz2
___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony 2.2.5 on Postgres-9.6 64-bit

[Steve Singer]

On Tue, 22 Aug 2017, S. Lui wrote:


I will try to find my old VS2010 to test. I'm able to compile Slony 2.2.5 on 
postgres-9.3 with VS2012
though. However, I really need postgres-9.6.
May I ask how you got around the issue when you hit the error?



I didn't really work around it.
Since I was just checking to see if I introduced any compile errors with the 
slony 2.2.6 changes I stopped when I hit the error.


Ideally we can figure out what the issue is but I haven't yet been able to 
setup VC 2010 environment to check it out.






Regards.


On Mon, Aug 21, 2017 at 9:55 PM, Steve Singer <st...@ssinger.info> wrote:
  On Mon, 21 Aug 2017, S. Lui wrote:



Hello,
Is Slony 2.2.5 compilable with Postgres-9.6 64-bit binaries on 
Windows OS? I
successfully compiled with
Postgres-9.3 64-bit binaries on Windows OS.

With Postgres-9.6 64-bit binaries, it gives me the following error 
after typing
"nmake /f win32.mak
slon.exe" in the command prompt:

libpgport.lib(snprintf.obj) : error LNK2019: unresolved external 
symbol __imp__d
class referenced in function fmtfloat
slon.exe : fatal error LNK1120: 1 unresolved externals
NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft 
Visual Studio 11.0
\VC\BIN\amd64\link.exe"' : return code '0x460'
Stop.


I tried with Windows SDK 7.1, VS2012 x64 Native Tools Command 
Prompt and VS2013
x64 Native Tools Command
Prompt.



  I THINK you need to be using the same version of visual studio that was 
used to build the
  postgres you were using.

  Perhaps it was VS2010?


  I've hit the same error at times. When I was doing the pre release 
testing for 2.2.6 I hit
  that, but I didn't have the time to download + setup/try VS2010.

  If you try different versions of visual studio please let the list know 
what works or
  doesn't.





Thanks for the help.





___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony 2.2.5 on Postgres-9.6 64-bit

[Steve Singer]

On Mon, 21 Aug 2017, S. Lui wrote:




Hello,
Is Slony 2.2.5 compilable with Postgres-9.6 64-bit binaries on Windows OS? I 
successfully compiled with
Postgres-9.3 64-bit binaries on Windows OS.

With Postgres-9.6 64-bit binaries, it gives me the following error after typing 
"nmake /f win32.mak
slon.exe" in the command prompt:

libpgport.lib(snprintf.obj) : error LNK2019: unresolved external symbol __imp__d
class referenced in function fmtfloat
slon.exe : fatal error LNK1120: 1 unresolved externals
NMAKE : fatal error U1077: '"C:\Program Files (x86)\Microsoft Visual Studio 11.0
\VC\BIN\amd64\link.exe"' : return code '0x460'
Stop.


I tried with Windows SDK 7.1, VS2012 x64 Native Tools Command Prompt and VS2013 
x64 Native Tools Command
Prompt.



I THINK you need to be using the same version of visual studio that was used 
to build the postgres you were using.


Perhaps it was VS2010?


I've hit the same error at times. When I was doing the pre release testing 
for 2.2.6 I hit that, but I didn't have the time to download + setup/try 
VS2010.


If you try different versions of visual studio please let the list know what 
works or doesn't.







Thanks for the help.


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony 2.2.6 release plans

[Steve Singer]

On Sun, 30 Jul 2017, Steve Singer wrote:

In my pre-release testing I hit a bug where occasionally when running DDL 
submitted to a non set origin/non provider node the DDL is replicating twice 
to some other nodes.

The regression test testddl.js will occasionally hit this case and fail.

I don't think this is a new issue with 2.2.6.

I can

A) Release 2.2.6 anyway and we we will have another release when we fix the 
DDL issue.

B) Hold off on 2.2.6 until we have a better idea on a timeline for the fix.

Unless anyone is aching for a 2.2.6 release (speak up if so) I am inclined 
to go with B.

Steve


>
> I am thinking of releasing slony 2.2.6 later this week or early next week.
> Changes are checked into git on the REL_2_2_STABLE branch.
>
> Our version detection code doesn't work with the PG10+ version numbering. I
> wasn't planning on backporting these changes to 2.1 or earlier but someone
> could if they really wanted to.
>
>
> The following are the changes I am planning on including in 2.2.6
>
>   - slonik_build_env can now accept multiple -schema options on the command
> line
>- Support for PG10. This involved changes to PG version detection
>- Disallow createEvent and data changes in the same transaction.
>  This also fixes some issues when the logApply trigger invokes the
>  data change trigger by inserting into a table with a trigger that
>  in turns inserts into another replicated table.
>- Fix some failover issues when doing a multi-node failover
>  with a cascaded node.
>- Bug 341 - suppress log trigger/deny when running in 'local' mode
>
>
>
> If I don't hear any objections, or requests for more time to test I work
> through the release process when I have a chance, likely Monday.
>
> Steve
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony 2.2.6 release plans

[Steve Singer]

On Thu, 3 Aug 2017, Tignor, Tom wrote:



Thanks Steve. I should mention, the dependence on indirect subscribers 
for a successful failover may provide a scalability limitation for us. We’re 
required to complete failover reliably in just a few minutes. Getting 
acknowledgements from all the multiple geographically distributed nodes in the 
allotted timeframe has sometimes been challenging. Would this be a worthwhile 
Slony-I feature? I believe I could find time in my schedule to do the dev work 
myself, if that would be helpful.

Tom(



If you remove the edge nodes from the admin conninfo section does this solve 
your issue? Does it introduce any other issues?


The trick is being able to figure out which nodes it actually needs to wait 
for and which ones don't.  Part of the problem is to think about how the 
edge nodes will catch up with the events they haven't yet processed if they 
then get the FAILNODE command earlier.








On 8/2/17, 5:51 PM, "Steve Singer" <st...@ssinger.info> wrote:

   On Mon, 31 Jul 2017, Tignor, Tom wrote:


   I THINK, and I am not 100% sure of this, but looking at the code it appears
   to do this is

   that the failover process will wait for each of the non-failed nodes to
   receive/confirm the FAILOVER event before finishng the failover process.


   >
   > Hi Steve,
   > A question on one item:
   >
   >- Fix some failover issues when doing a multi-node failover
   >  with a cascaded node.
   >
   > In cascaded node failover, is it necessary to sync with every 
receiver node for a failed over set? Or is it sufficient to sync only with nodes 
directly subscribing to the failed over node? Hoping for the latter!
   > Thanks,
   >
   >     Tom    (
   >
   >
   > On 7/30/17, 10:15 PM, "Steve Singer" <st...@ssinger.info> wrote:
   >
   >
   >I am thinking of releasing slony 2.2.6 later this week or early next 
week.
   >Changes are checked into git on the REL_2_2_STABLE branch.
   >
   >Our version detection code doesn't work with the PG10+ version 
numbering. I
   >wasn't planning on backporting these changes to 2.1 or earlier but 
someone
   >could if they really wanted to.
   >
   >
   >The following are the changes I am planning on including in 2.2.6
   >
   >   - slonik_build_env can now accept multiple -schema options on the 
command
   >line
   >- Support for PG10. This involved changes to PG version detection
   >- Disallow createEvent and data changes in the same transaction.
   >  This also fixes some issues when the logApply trigger invokes the
   >  data change trigger by inserting into a table with a trigger that
   >  in turns inserts into another replicated table.
   >- Fix some failover issues when doing a multi-node failover
   >  with a cascaded node.
   >- Bug 341 - suppress log trigger/deny when running in 'local' mode
   >
   >
   >
   >If I don't hear any objections, or requests for more time to test I work
   >through the release process when I have a chance, likely Monday.
   >
   >Steve
   >
   >___
   >Slony1-general mailing list
   >Slony1-general@lists.slony.info
   >http://lists.slony.info/mailman/listinfo/slony1-general
   >
   >
   >




___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony 2.2.6 release plans

[Steve Singer]

On Mon, 31 Jul 2017, Tignor, Tom wrote:


I THINK, and I am not 100% sure of this, but looking at the code it appears 
to do this is

that the failover process will wait for each of the non-failed nodes to 
receive/confirm the FAILOVER event before finishng the failover process.


>
>   Hi Steve,
>   A question on one item:
>
>- Fix some failover issues when doing a multi-node failover
>  with a cascaded node.
>
>   In cascaded node failover, is it necessary to sync with every receiver 
> node for a failed over set? Or is it sufficient to sync only with nodes 
> directly subscribing to the failed over node? Hoping for the latter!
>   Thanks,
>
>   Tom    (
>
>
> On 7/30/17, 10:15 PM, "Steve Singer" <st...@ssinger.info> wrote:
>
>
>I am thinking of releasing slony 2.2.6 later this week or early next week.
>Changes are checked into git on the REL_2_2_STABLE branch.
>
>Our version detection code doesn't work with the PG10+ version numbering. I
>wasn't planning on backporting these changes to 2.1 or earlier but someone
>could if they really wanted to.
>
>
>The following are the changes I am planning on including in 2.2.6
>
>   - slonik_build_env can now accept multiple -schema options on the 
> command
>line
>- Support for PG10. This involved changes to PG version detection
>- Disallow createEvent and data changes in the same transaction.
>  This also fixes some issues when the logApply trigger invokes the
>  data change trigger by inserting into a table with a trigger that
>  in turns inserts into another replicated table.
>- Fix some failover issues when doing a multi-node failover
>  with a cascaded node.
>- Bug 341 - suppress log trigger/deny when running in 'local' mode
>
>
>
>If I don't hear any objections, or requests for more time to test I work
>through the release process when I have a chance, likely Monday.
>
>Steve
>
>___
>Slony1-general mailing list
>Slony1-general@lists.slony.info
>http://lists.slony.info/mailman/listinfo/slony1-general
>
>
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

[Slony1-general] Slony 2.2.6 release plans

[Steve Singer]

I am thinking of releasing slony 2.2.6 later this week or early next week.
Changes are checked into git on the REL_2_2_STABLE branch.

Our version detection code doesn't work with the PG10+ version numbering. I 
wasn't planning on backporting these changes to 2.1 or earlier but someone 
could if they really wanted to.


The following are the changes I am planning on including in 2.2.6

   - slonik_build_env can now accept multiple -schema options on the command 
line
- Support for PG10. This involved changes to PG version detection
- Disallow createEvent and data changes in the same transaction.
  This also fixes some issues when the logApply trigger invokes the
  data change trigger by inserting into a table with a trigger that
  in turns inserts into another replicated table.
- Fix some failover issues when doing a multi-node failover
  with a cascaded node.
- Bug 341 - suppress log trigger/deny when running in 'local' mode



If I don't hear any objections, or requests for more time to test I work 
through the release process when I have a chance, likely Monday.

Steve

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] more missing paths, and

[Steve Singer]

On Sat, 22 Jul 2017, Tignor, Tom wrote:



Hi Steve,
Thanks for the store path desc. That’s what I surmised generally. I 
should note: when problems arise with subscribers, we have a utility to drop 
and re-store the node, and then re-store paths to all other nodes.
To answer your questions: node 4 has all expected state, 7*6=42 
connections, i.e.

Sl_path server = 1, client = 3
Sl_path server = 1, client = 4
Sl_path server = 1, client = 6
Sl_path server = 1, client = 7
Sl_path server = 1, client = 8
Sl_path server = 1, client = 9
Sl_path server = 3, client = 1
Sl_path server = 3, client = 4
Sl_path server = 3, client = 6
Sl_path server = 3, client = 7
Sl_path server = 3, client = 8
Sl_path server = 3, client = 9
…


All the other nodes have 37 connections. The following are missing in 
each DB:

Sl_path server = 3, client = 4
Sl_path server = 6, client = 4
Sl_path server = 7, client = 4
Sl_path server = 8, client = 4
Sl_path server = 9, client = 4


I don't see any sl_path entires with

server=4 client=[3,6,7,8,9]
What if you re-issue the store path command with

server=4,client = everything






Moreover, the Sl_path server = 1, client = 4 path shows the conninfo as 
.
Just a guess: is there possibly some sl_event table entry which, if 
deleted, will allow the node-4-client store path ops to get processed?

Tom(


On 7/21/17, 9:53 PM, "Steve Singer" <st...@ssinger.info> wrote:

   On Fri, 21 Jul 2017, Tignor, Tom wrote:

   >
   >
   >
   > Hello again, Slony-I community,
   >
   > After our last missing path issue, we’ve taken a new 
interest in keeping all our path/conninfo
   > data up to date. We have a cluster running with 7 nodes. Each has conninfo 
to all the others, so we expect N=7;
   > N*(N-1) = 42 paths. We’re having persistent problems with our paths for 
node 4. Node 4 itself has fully accurate
   > path data. However, all the other nodes have missing or inaccurate data 
for node-4-client conninfo. Specifically:
   > node 1 shows:
   >
   >
   >
   >  1 | 4 | |  
 10
   >
   >
   >
   > For the other five nodes, the node-4-client conninfo is 
just missing. In other words, there are no
   > pa_server=X, pa_client=4 rows in sl_path for these nodes. Again, the node 
4 DB itself shows all the paths we
   > expect.
   >
   > Does anyone have thoughts on how this is caused and how it 
could be fixed? Repeated “store path”
   > operations all complete without errors but do not change state. Service 
restarts haven’t worked either.

   When you issue a store path command with line client=4 server=X

   slonik connects to db4 and
   A) updates sl_path
   B) creates an event in sl_event of ev_type=STORE_PATH with ev_origin=4

   This event then needs to propogate to the other nodes in the network.

   When this event propogates to the other nodes then the remoteWorkerThread_4
   in each of the other nodes will process this STORE_PATH entry, and you
   should see a
   CONFIG storePath: pa_server=X pa_client=4

   message in each of the other slons.

   If this happens you should see the actual path in sl_path.  Since your not I
   assume that this isn't happening.

   Where on the chain of events are things breaking down?

   Do you have other paths from other nodes with  client=[X,Y,Z] server=4


   Steve



   >
   > Thanks in advance,
   >
   >
   >
   > Tom☺
   >
   >
   >
   >
   >
   >
   >



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] more missing paths, and

[Steve Singer]

On Fri, 21 Jul 2017, Tignor, Tom wrote:



 

    Hello again, Slony-I community,

    After our last missing path issue, we’ve taken a new interest 
in keeping all our path/conninfo
data up to date. We have a cluster running with 7 nodes. Each has conninfo to 
all the others, so we expect N=7;
N*(N-1) = 42 paths. We’re having persistent problems with our paths for node 4. 
Node 4 itself has fully accurate
path data. However, all the other nodes have missing or inaccurate data for 
node-4-client conninfo. Specifically:
node 1 shows:

 

             1 |         4 |             |           
10

 

    For the other five nodes, the node-4-client conninfo is just 
missing. In other words, there are no
pa_server=X, pa_client=4 rows in sl_path for these nodes. Again, the node 4 DB 
itself shows all the paths we
expect.

    Does anyone have thoughts on how this is caused and how it 
could be fixed? Repeated “store path”
operations all complete without errors but do not change state. Service 
restarts haven’t worked either.


When you issue a store path command with line client=4 server=X

slonik connects to db4 and
A) updates sl_path
B) creates an event in sl_event of ev_type=STORE_PATH with ev_origin=4

This event then needs to propogate to the other nodes in the network.

When this event propogates to the other nodes then the remoteWorkerThread_4 
in each of the other nodes will process this STORE_PATH entry, and you 
should see a

CONFIG storePath: pa_server=X pa_client=4

message in each of the other slons.

If this happens you should see the actual path in sl_path.  Since your not I 
assume that this isn't happening.


Where on the chain of events are things breaking down?

Do you have other paths from other nodes with  client=[X,Y,Z] server=4


Steve





    Thanks in advance,

 

    Tom    ☺

 

 



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] failover failure and mysterious missing paths

[Steve Singer]

On Wed, 5 Jul 2017, Tignor, Tom wrote:



Interesting. Of course the behavior evident on inspection indicated 
something like this must be happening.
It seems the doc could be improved on the subject of required paths. I 
recall some sections indicate it is not harmful to have a path from each node 
to each other node. What seems not to be spelled out is that for the service to 
be highly available, to have the ability to failover, each node is *required* 
to have a path to each other node.
On a related point, it would be a lot more convenient if we could give 
each node a default path instead of re-specifying the same IP for each new 
subscriber, and a new line of conninfo for every slonik script.
Would either of these items be worth writing up in bug tracking and/or 
providing the solution? If so, could I get that link?


You don't need a path to EVERY other node, you just need a path to nodes 
that might be the providers as part of the failover.


For example

1-->2-->3
|
V
4

If that is the direction of the replication flow, and the paths (plus back 
paths).  Node 2 is the only viable failover candidate for node 1.  There 
isn't a reason why node 3 and 4 need to have paths between each other.


However

1--->2
|\
V \
3  4

means that any of the nodes 2,3,4 might be failover candidates and if node 3 
becomes the new origin then there would need to be paths between 3 and 2,4


I tried to capture a lot of these rules in the sl_failover_targets view.


We had to take the slony bugzilla instance offline because of excesive spam.


Steve



Tom(


On 7/2/17, 9:30 PM, "Steve Singer" <st...@ssinger.info> wrote:

   On Wed, 28 Jun 2017, Tignor, Tom wrote:

   >
   > Hi Steve,
   > Thanks for the info. I was able to repro this problem in testing 
and saw as soon as I added the missing path back the still-in-process failover op 
continued on and completed successfully.
   > We do issue DROP NODEs in the event we need to restore a replica 
from scratch, which did occur. However, the restore workflow also should issue 
store paths to/from the new replica node and every other node. Still investigating 
this.
   > What still confuses me is the recurring “remoteWorkerThread_X: 
SYNC” output, despite the fact of not having a configured path. If the path is 
missing, how does slon continue to get SYNC events?

   Slon can get events including SYNC from nodes other than the event origin if
   it has a path to that node.   However a slon can only replicate the data
   from a node it has a path to.


   Steve



   >
   > Tom    (
   >
   >
   > On 6/27/17, 5:04 PM, "Steve Singer" <st...@ssinger.info> wrote:
   >
   >On 06/27/2017 11:59 AM, Tignor, Tom wrote:
   >
   >
   >The disableNode() in the makes it look like someone did a DROP NODE
   >
   >If the only issue is that your missing active paths in sl_path you can
   >add/update the paths with slonik.
   >
   >
   >
   >
   >> **
   >>
   >> **Hello Slony-I community,
   >>
   >>  Hoping someone can advise on a strange and serious 
problem.
   >> We performed a slony service failover yesterday. For the first time
   >> ever, our slony service FAILOVER op errored out. We recently expanded
   >> our cluster to 7 consumers from a single provider. There are no load
   >> issues during normal operations. As the error output below shows,
   >> though, our node 4 and node 5 consumers never got the events they
   >> needed. Here’s where it gets weird: closer inspection has shown that
   >> node 2->4 and node 2->5 path data went missing out of the service at
   >> some point. It seems clear that’s the main issue, but in spite of 
that,
   >> both node 4 and node 5 continued to find and process node 2 SYNC 
events
   >> for a full week! The logs show this happened in spite of multiple 
restarts.
   >>
   >> How can this happen? If missing path data stymies the failover, 
wouldn’t
   >> it also prevent normal SYNC processing?
   >>
   >> In the case where a failover is begun with inadequate path data, 
what’s
   >> the best resolution? Can path data be quickly applied to allow 
failover
   >> to succeed?
   >>
   >>  Thanks in advance for any insights.
   >>
   >>  failover error 
   >>
   >> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: 
NOTICE:
   >> calling restart node 1
   >>
   >> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:55:
   >> 2017-06-26 18:33:02
   >

Re: [Slony1-general] failover failure and mysterious missing paths

[Steve Singer]

On Wed, 28 Jun 2017, Tignor, Tom wrote:



Hi Steve,
Thanks for the info. I was able to repro this problem in testing and 
saw as soon as I added the missing path back the still-in-process failover op 
continued on and completed successfully.
We do issue DROP NODEs in the event we need to restore a replica from 
scratch, which did occur. However, the restore workflow also should issue store 
paths to/from the new replica node and every other node. Still investigating 
this.
What still confuses me is the recurring “remoteWorkerThread_X: SYNC” 
output, despite the fact of not having a configured path. If the path is 
missing, how does slon continue to get SYNC events?


Slon can get events including SYNC from nodes other than the event origin if 
it has a path to that node.   However a slon can only replicate the data 
from a node it has a path to.



Steve





Tom(


On 6/27/17, 5:04 PM, "Steve Singer" <st...@ssinger.info> wrote:

   On 06/27/2017 11:59 AM, Tignor, Tom wrote:


   The disableNode() in the makes it look like someone did a DROP NODE

   If the only issue is that your missing active paths in sl_path you can
   add/update the paths with slonik.




   > **
   >
   > **Hello Slony-I community,
   >
   >  Hoping someone can advise on a strange and serious problem.
   > We performed a slony service failover yesterday. For the first time
   > ever, our slony service FAILOVER op errored out. We recently expanded
   > our cluster to 7 consumers from a single provider. There are no load
   > issues during normal operations. As the error output below shows,
   > though, our node 4 and node 5 consumers never got the events they
   > needed. Here’s where it gets weird: closer inspection has shown that
   > node 2->4 and node 2->5 path data went missing out of the service at
   > some point. It seems clear that’s the main issue, but in spite of that,
   > both node 4 and node 5 continued to find and process node 2 SYNC events
   > for a full week! The logs show this happened in spite of multiple restarts.
   >
   > How can this happen? If missing path data stymies the failover, wouldn’t
   > it also prevent normal SYNC processing?
   >
   > In the case where a failover is begun with inadequate path data, what’s
   > the best resolution? Can path data be quickly applied to allow failover
   > to succeed?
   >
   >  Thanks in advance for any insights.
   >
   >  failover error 
   >
   > /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: NOTICE:
   > calling restart node 1
   >
   > /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:55:
   > 2017-06-26 18:33:02
   >
   > executing preFailover(1,1) on 2
   >
   > executing preFailover(1,1) on 3
   >
   > executing preFailover(1,1) on 4
   >
   > executing preFailover(1,1) on 5
   >
   > executing preFailover(1,1) on 6
   >
   > executing preFailover(1,1) on 7
   >
   > executing preFailover(1,1) on 8
   >
   > NOTICE: executing "_ams_cluster".failedNode2 on node 2
   >
   > /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
   > for event (2,561664).  node 8 only on event 561654, node 4 only
   > on event 561654, node 5 only on event 561655, node 3 only on
   > event 561662, node 6\
   >
   >   only on event 561654, node 7 only on event 561656
   >
   > /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
   > for event (2,561664).  node 4 only on event 561657, node 5 only
   > on event 561663, node 3 only on event 561663, node 6 only on
   > event 561663
   >
   > /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
   > for event (2,561664).  node 4 only on event 561663, node 5 only
   > on event 561663, node 6 only on event 561663
   >
   > /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
   > for event (2,561664).  node 4 only on event 561663, node 5 only
   > on event 561663
   >
   > /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
   > for event (2,561664).  node 4 only on event 561663, node 5 only
   > on event 561663
   >
   > /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
   > for event (2,561664).  node 4 only on event 561663, node 5 only
   > on event 561663
   >
   > /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
   > for event (2,561664).  node 4 only on event 561663, node 5 only
   > on event 561663
   >
   > /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
   > for event (2,5

Re: [Slony1-general] failover failure and mysterious missing paths

[Steve Singer]

On 06/27/2017 11:59 AM, Tignor, Tom wrote:


The disableNode() in the makes it look like someone did a DROP NODE

If the only issue is that your missing active paths in sl_path you can 
add/update the paths with slonik.




> **
>
> **Hello Slony-I community,
>
>  Hoping someone can advise on a strange and serious problem.
> We performed a slony service failover yesterday. For the first time
> ever, our slony service FAILOVER op errored out. We recently expanded
> our cluster to 7 consumers from a single provider. There are no load
> issues during normal operations. As the error output below shows,
> though, our node 4 and node 5 consumers never got the events they
> needed. Here’s where it gets weird: closer inspection has shown that
> node 2->4 and node 2->5 path data went missing out of the service at
> some point. It seems clear that’s the main issue, but in spite of that,
> both node 4 and node 5 continued to find and process node 2 SYNC events
> for a full week! The logs show this happened in spite of multiple restarts.
>
> How can this happen? If missing path data stymies the failover, wouldn’t
> it also prevent normal SYNC processing?
>
> In the case where a failover is begun with inadequate path data, what’s
> the best resolution? Can path data be quickly applied to allow failover
> to succeed?
>
>  Thanks in advance for any insights.
>
>  failover error 
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: NOTICE:
> calling restart node 1
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:55:
> 2017-06-26 18:33:02
>
> executing preFailover(1,1) on 2
>
> executing preFailover(1,1) on 3
>
> executing preFailover(1,1) on 4
>
> executing preFailover(1,1) on 5
>
> executing preFailover(1,1) on 6
>
> executing preFailover(1,1) on 7
>
> executing preFailover(1,1) on 8
>
> NOTICE: executing "_ams_cluster".failedNode2 on node 2
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 8 only on event 561654, node 4 only
> on event 561654, node 5 only on event 561655, node 3 only on
> event 561662, node 6\
>
>   only on event 561654, node 7 only on event 561656
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561657, node 5 only
> on event 561663, node 3 only on event 561663, node 6 only on
> event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663, node 6 only on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
> /tmp/ams-tool/ams-slony1-fastfailover-1-FR_80.67.75.105.slk:56: waiting
> for event (2,561664).  node 4 only on event 561663, node 5 only
> on event 561663
>
>  node 4 log archive 
>
> bos-mpt5c:odin-9353 ttignor$ egrep 'disableNode: no_id=2|storePath:
> pa_server=2 pa_client=4|restart notification' prod4/node4-pathconfig.out
>
> 2017-06-15 15:14:00 UTC [5688] INFO   localListenThread: got restart
> notification
>
> 2017-06-15 15:14:10 UTC [8431] CONFIG storePath: pa_server=2 pa_client=4
> pa_conninfo="dbname=ams
>
> 2017-06-15 15:53:00 UTC [8431] INFO   

Re: [Slony1-general] Slony - ntdll.dll?

[Steve Singer]

On 05/15/2017 03:16 PM, Sung Hsin Lei wrote:
> Hello,
>
> Thanks for the response. Attached in the data in the command prompt when
> I run slon from the command prompt. The last things that slon seems to
> be doing is:
>
> 2017-05-14 00:37:31 Eastern Daylight Time INFO   remoteWorkerThread_2:
> SYNC 508808 done in 0.000 seconds
> 2017-05-14 00:37:49 Eastern Daylight Time INFO   remoteWorkerThread_2:
> SYNC 508809 done in 0.000 seconds
> 2017-05-14 00:37:51 Eastern Daylight Time INFO   remoteWorkerThread_2:
> SYNC 508810 done in 0.000 seconds
> 2017-05-14 00:38:09 Eastern Daylight Time INFO   remoteWorkerThread_2:
> SYNC 508811 done in 0.000 seconds
> 2017-05-14 00:38:11 Eastern Daylight Time INFO   remoteWorkerThread_2:
> SYNC 508812 done in 0.000 seconds
> 2017-05-14 00:38:29 Eastern Daylight Time INFO   remoteWorkerThread_2:
> SYNC 508813 done in 0.000 seconds
> 2017-05-14 00:38:31 Eastern Daylight Time INFO   remoteWorkerThread_2:
> SYNC 508814 done in 0.000 seconds
> 2017-05-14 00:38:49 Eastern Daylight Time INFO   remoteWorkerThread_2:
> SYNC 508815 done in 0.000 seconds
> 2017-05-14 00:38:51 Eastern Daylight Time INFO   remoteWorkerThread_2:
> SYNC 508816 done in 0.000 seconds
> NOTICE:  Slony-I: log switch to sl_log_2 complete - truncate sl_log_1
> CONTEXT:  PL/pgSQL function _slony_securithor2.cleanupevent(interval)
> line 95 at assignment
> 2017-05-14 00:39:09 Eastern Daylight Time INFO   cleanupThread:0.060
> seconds for cleanupEvent()
> 2017-05-14 00:39:09 Eastern Daylight Time INFO   cleanupThread:0.010
> seconds for vacuuming
> 2017-05-14 00:39:09 Eastern Daylight Time INFO   remoteWorkerThread_2:
> SYNC 508817 done in 0.000 seconds
>
>
> Does Slony has any other types of logs? If yes, how do I access it?
>
>

No that is the type of stuff slon logs. You can turn the debug level up 
to debug and it will log a bit more at the same place.
Can you tell how close the timestamp of the crash/error message is to 
those messages?

Those messages are all normal and don't show any sign of a problem



> Thanks.
>
>
>
>
>
>
> On Mon, May 15, 2017 at 2:35 PM, Steve Singer <ssin...@ca.afilias.info
> <mailto:ssin...@ca.afilias.info>> wrote:
>
> On 05/14/2017 12:22 AM, Sung Hsin Lei wrote:
>
> Hello,
>
> I was wondering if someone had a similar error or know what this
> error
> means?
>
> I compiled my Slony 2.2.5 64-bit with Postgres 9.3. On one of my
> VM's,
> replication would run fine for about 2 days and then it would
> just stop.
> When it stops, I need to restart the slon service for
> replication to resume.
>
> looking at the windows logs, I get the following:
>
>
> Faulting application name: slon.exe, version: 0.0.0.0, time stamp:
> 0x57be7ff1
> Faulting module name: ntdll.dll, version: 6.1.7601.23796, time
> stamp:
> 0x590296ce
> Exception code: 0xc374
> Fault offset: 0x000bf3e2
> Faulting process id: 0x1464
> Faulting application start time: 0x01d2cc57301577a0
> Faulting application path: C:\Program
> Files\PostgreSQL\9.3\bin\slon.exe
> Faulting module path: C:\Windows\SYSTEM32\ntdll.dll
> Report Id: 9fcc9980-384e-11e7-9cd3-7ab625288ae1
>
>
> Any idea what slon was doing at this time? Did slon log anything
> about its activities?
>
>
> Has anyone experience such problem or know what this means?
>
>
> Thank you.
>
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> <mailto:Slony1-general@lists.slony.info>
> http://lists.slony.info/mailman/listinfo/slony1-general
> <http://lists.slony.info/mailman/listinfo/slony1-general>
>
>
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony - ntdll.dll?

[Steve Singer]

On 05/14/2017 12:22 AM, Sung Hsin Lei wrote:
> Hello,
>
> I was wondering if someone had a similar error or know what this error
> means?
>
> I compiled my Slony 2.2.5 64-bit with Postgres 9.3. On one of my VM's,
> replication would run fine for about 2 days and then it would just stop.
> When it stops, I need to restart the slon service for replication to resume.
>
> looking at the windows logs, I get the following:
>
>
> Faulting application name: slon.exe, version: 0.0.0.0, time stamp:
> 0x57be7ff1
> Faulting module name: ntdll.dll, version: 6.1.7601.23796, time stamp:
> 0x590296ce
> Exception code: 0xc374
> Fault offset: 0x000bf3e2
> Faulting process id: 0x1464
> Faulting application start time: 0x01d2cc57301577a0
> Faulting application path: C:\Program Files\PostgreSQL\9.3\bin\slon.exe
> Faulting module path: C:\Windows\SYSTEM32\ntdll.dll
> Report Id: 9fcc9980-384e-11e7-9cd3-7ab625288ae1
>

Any idea what slon was doing at this time? Did slon log anything about 
its activities?

>
> Has anyone experience such problem or know what this means?
>
>
> Thank you.
>
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Looks like someone's spam tool is buggy

[Steve Singer]

On 01/04/2017 02:04 PM, Jan Wieck wrote:
> I am right now seeing up to 6 bugs opened per minute. I suggest we shut
> bugzilla down until we can figure this out.
>

or switch the new accounts creation to be moderated and delete all the 
spammy accounts

Someone with bugzilla admin access will need to do this (or do something 
else to stop the spam)
(not having bugzilla admin access I can't do this)




> On Wed, Jan 4, 2017 at 1:04 PM, Christopher Browne  > wrote:
>
> Oh dear.  I closed several hundred of them yesterday, with the faint
> hope that it was a one-off situation; apparently something is running
> on an ongoing basis, generating bugs consisting of "material of spammy
> consistency" on the Slony Bugzilla instance.
>
> Joshua Drake has been enquring as to whether or not we should see
> about updating some things (he's been thinking of the OS and
> PostgreSQL) on slony.info ; I suspect we need to
> do something regarding
> the version of Bugzilla, with the dose of wishful thinking that
> perhaps a later version would be more resistant to this spamulous
> activity.
>
> I suggest weighing in on Joshua's comments; we should muse a bit on
> what to do about all this.
>
>
>
>
> --
> Jan Wieck
> Senior Postgres Architect
> http://pgblog.wi3ck.info
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Setting up replicating database to receive log-shipping files - how to do?

[Steve Singer]

On Wed, 19 Oct 2016, Andrew Edson wrote:



I am working on setting up a secondary machine, at another location, with 
roughly the same structure as
our primary machine here.   The current machine is running Slony 2.0 (I think 
2.0.7 but cannot recall for
certain) against Postgres 8.4.13, the newer one is running Slony 2.1.4 against 
Postgres 9.2.15.

 

The main machine uses slony to generate log-shipping files, which are then sent 
out to other servers we
maintain to feed the local database copies on them.  The new remote machine is 
intended as a backup to
this system, eventually to become a replacement for the current machine.  To 
this end, I was asked to get
the new remote machine set up to both create log files, and to receive the log 
files we’re sending to the
other machines we maintain.

 

I have managed to install Slony 2.1.4 on the new machine, and I have verified 
that I can replicate
changes input manually on the new machine.  Changes from the log files being 
generated by the old
machine, however, don’t replicate on the new system.  I believe, from what I’ve 
found online, that the
cause of this is the line ‘set session_replication_role to replica;’ found near 
the beginning of the log
files.  If I’m understanding correctly, that is forcing the system to bypass 
the triggers that Slony
needs to run the replication setup.

 

Is there some setting I can change in the Slony instance on the log-shipping 
generating machine to
prevent the log files from being sent out with that line, or something I can 
change on the receiving
machine that will allow it to ignore the line in question?  Or something else, 
perhaps, which can work to
resolve this?  What options do I have for getting this setup to work? 



Generally people don't generate logshipping files in a cascaded fashion. 
Can't you just send the sames files to multiple locations.


Also your missing something in your setup. You need at least 3 nodes wiht 
log shipping, the origin, a replica that slon is generating the log shipping 
files(B, which is a proper replica with a slon) and node C the machine 
receiving your logshipping files.


A-->slon->B->logshipping files--->C


You can just then also ship the files to nodes D and E

If you stripped the session replication role from the .sql files (which you 
could do with a script I guess) you could then do something like


A-->slon->B->log shipping files->C--->slon--->D

where you are actually running 2 slony clusters one with nodes A,B and one 
with nodes C,D (I guess this is what your trying to do?)


The set session_replication_role is added to the .sql files by slon in 
remote_worker.c (see archive_open) and can't be disabled without changing 
the code





 

Thank you,

Andrew Edson

Application Software, Inc.

 



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] switchover between primary and secondary slave. question + masternode

[Steve Singer]

On Sun, 9 Oct 2016, Tory M Blue wrote:



I currently have a setup like this

Node:
1  primary
2  secondary
3 query 
4 query
5 query
11 secondary
12 secondary
13 query
14 query
15 query

Replication is like this

Masternode=1

1 -> 2
2 -> 11 3 4 5
11 -> 12
12 -> 13 14 15

I'm going to switchover between 1 and 11

So switchover would mean node 1 becomes secondary and node 11 becomes primary.  
However as noted above
currently 2 is talking to 11, 1 is not replicating directly to 11 as 2 is doing 
the wide area replication
to 11..

So do I have to subscribe 11 to node 1 prior to trying this switchover? How is 
switchover handled if I
don't , since data in 2 could not be 100% up to date (1000ms delay) when the 
command is triggered, so 1
would have to finish it's replication to 2 and 2 to 11, before 1 gave the sets 
to 11?


I recommend running move set with a preamble containing admin conninfo to
all your nodes. That way auto wait for can do its thing.

Most of your questions are answered in
http://www.slony.info/documentation/2.2/stmtmoveset.html

Move set will reverse the subscription chain between 2 and 11, so 11->2
11->1

If after your move set completes you can make further changes to your
subscription network with
http://www.slony.info/documentation/2.2/stmtresubscribenode.html




Also when I tear down the primary site (completely) how do I move, promote, 
migrate the masternode (right
now the config sees the masternode as 1, but when I drop 1-5 at some point from 
11/12, I don't have a
masternode 1 anymore, so how do I configure the system to treat 11 as 
masternode?


Once your move set completes node 11 is your master node(for those tables) 
that is exactly what move set does, it makes node 11 the origin(master).


Slony does not support renaming node, don't try to make some other node in 
the cluster be renamed to node 1 after this.


I encourage you to setup a test/staging cluster in your configuration and 
try various scenarios out before executing the commands on your 
production cluster.


Steve


Thanks
Tory


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] SET ADD TABLE argument TABLES basically useless on even slightly busy systems.

[Steve Singer]

On Fri, 16 Sep 2016, Scott Marlowe wrote:

> So I tried to subscribe a set with SET ADD TABLES using the regular
> expression with TABLES and every single time it would error out with a
> deadlock detected.
>
> It's a cool idea, but the way it takes out locks seems to make it not
> really useful.
>
> Are there any plans to have it operate similar to creating a long list
> of tables and running a single table add for each one, so it doesn't
> get deadlocks?
>
> Just wondering, it's not a world-stopper, just a slight annoyance.

I don't think anyone has raised this before.

The issue with having 'set add table' use an individual transaction for each 
table would be that if the command failed in the middle some tables would be 
added and some woudln't be.

I guess we could have an option to to give this behaviour if people wanted 
but I wouldn't make it the default.

Steve


>
> -- 
> To understand recursion, one must first understand recursion.
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] sync performance

[Steve Singer]

On 09/12/2016 11:39 AM, Tignor, Tom wrote:
>  Seems I have an additional data point: the sync test
> always takes longer (> 20 secs) if I include conninfo for all cluster
> nodes instead of just the local node. I had previously thought conninfo
> data was only used when needed. Is this not the case?

What if you do

  sync(id=2);

  wait for event (origin=2, confirmed=5, wait on=2, timeout=30);

  sync(id=2);

  wait for event (origin=2, confirmed=5, wait on=2, timeout=30);
  sync(id=2);

  wait for event (origin=2, confirmed=5, wait on=2, timeout=30);


3 times (or more) in a row, does it still take about the same amount of 
time as 1 sync ?

When slonik starts up it contacts all the nodes it has admin conninfo 
for to get the current state/last event from each node.  Maybe your time 
is spent establishing all those connections over SSL





>
>  Tom J
>
> *From: *Tom Tignor 
> *Date: *Monday, September 12, 2016 at 10:52 AM
> *To: *"slony1-general@lists.slony.info" 
> *Subject: *sync performance
>
>  Hello slony1 community,
>
>  We’ve recently been testing communication reliability
> between our cluster nodes. Our config is a simple setup with one
> provider producing a modest volume of changes (measured in KB/s)
> consumed by 5 direct subscribers, though these are geographically
> distributed. The test is just a sync event followed by a wait on the
> sync originator. Example:
>
> cluster name = ams_cluster;
>
> node 5 admin
>
>conninfo='dbname=ams
>
>host=23.79.242.182
>
>user=ams_slony
>
>sslmode=verify-ca
>
>sslcert=/usr/local/akamai/.ams_certs/complete-ams_slony.crt
>
>sslkey=/usr/local/akamai/.ams_certs/ams_slony.private_key
>
>sslrootcert=/usr/local/akamai/etc/ssl_ca/canonical_ca_roots.pem';
>
> node 2 admin conninfo = 'dbname=ams user=ams_slony';
>
> sync(id=2);
>
> wait for event (origin=2, confirmed=5, wait on=2, timeout=30);
>
>  Tests show the script takes 10-20 secs to run on
> different nodes.
>
>  Can anyone explain what’s happening internally during
> this time, and why it takes so long? On a healthy, lightly loaded
> system, we might have hoped for a sync response in just a couple
> seconds. Our slon daemons are running with mostly default startup options.
>
>  Thanks in advance,
>
>  Tom J
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Need some help again, keep alives, wide area replication , set failure STILL

[Steve Singer]

On 09/12/2016 02:33 PM, Tory M Blue wrote:
>

>
> Okay keepalives didn't work, but maybe I configured the slon.conf wrong,
> there does not appear to be any real examples
>
> I used:
>
> tcp_keepalive_time = 5
>
> tcp_keepalive_probes = 24
>
> tcp_keepalive_intvl = 5

tcp_keepalive_idle=5
tcp_keepalive_count=24
tcp_keepalive_interval=5


http://www.slony.info/documentation/2.2/slon-config-connection.html


>
> While my kernel is set at, maybe I need to adjust the kernel as well?
>
> net.ipv4.tcp_keepalive_time = 7200
>
> net.ipv4.tcp_keepalive_probes = 9
>
> net.ipv4.tcp_keepalive_intvl = 75
>
>
> 2016-09-12 09:27:48 PDT CONFIG remoteWorkerThread_1: 1869.486 seconds to
> copy table "torque"."impressions_daily"
>
> 2016-09-12 09:27:48 PDT CONFIG remoteWorkerThread_1: copy table
> "torque"."impressions"
>
> 2016-09-12 09:27:48 PDT CONFIG remoteWorkerThread_1: Begin COPY of table
> "torque"."impressions"
>
> NOTICE:  truncate of "torque"."impressions" succeeded
>
> 2016-09-12 10:31:09 PDT CONFIG remoteWorkerThread_1: 77048102322 bytes
> copied for table "torque"."adimpressions"
>
> 2016-09-12 11:02:56 PDT CONFIG remoteWorkerThread_1: 5708.515 seconds to
> copy table "torque"."impressions"
>
> 2016-09-12 11:02:56 PDT CONFIG remoteWorkerThread_1: copy table
> "torque"."impressions_archive"
>
> 2016-09-12 11:02:56 PDT CONFIG remoteWorkerThread_1: Begin COPY of table
> "torque"."impressions_archive"
>
> 2016-09-12 11:02:56 PDT ERROR  remoteWorkerThread_1: "select
> "_cls".copyFields(237);"
>
> 2016-09-12 11:02:56 PDT WARN   remoteWorkerThread_1: data copy for set 2
> failed 1 times - sleep 15 seconds
>
> There are no indexes, so I don't know what Slon is doing for the 31
> minutes between when the data is finished copied and it attempts to
> start the next table.
>
> More suggestions? I know I'm being needy but I'm spinning my wheels it seems
>
> Thanks
> Tory
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] value violates unique constraint "sl_event-pkey"

[Steve Singer]

On Sat, 10 Sep 2016, Tory M Blue wrote:



Running into an issue, working with a cluster on another network, so I added 
node 11 (insert secondary in site2) from
node 2 (which is the insert secondary of site 1). Set 2 kept failing, so I went 
in and dropped node 11 from the
cluster config, but node 2, which is the seecondary insert server continues 
with this error.. I've not inserted
anything into node 2, nor can you, I'm really confused how my primary cluster 
got in this state..


Any assistance to help clean it up? Dropping node 2 and re-adding is a 24 hour 
ordeal.

2016-09-10 17:41:50 PDT ERROR  remoteWorkerThread_11: "insert into 
"_cls".sl_event     (ev_origin, ev_seqno,
ev_timestamp,      ev_snapshot, ev_type     ) values ('11', '505698', 
'2016-09-10 08:34:48.691123-07',
'6422065:6530984:6422065', 'SYNC'); insert into "_cls".sl_confirm (con_origin, 
con_received, con_seqno,
con_timestamp)    values (11, 2, '505698', now()); select 
"_cls".logApplySaveStats('_cls', 11, '0.047
s'::interval); commit transaction;" PGRES_FATAL_ERROR ERROR:  duplicate key 
value violates unique constraint
"sl_event-pkey"

DETAIL:  Key (ev_origin, ev_seqno)=(11, 505698) already exists.



So when slon 2 (I assume this is slon 2) is stopped, is/was there a row in 
sl_event with ev_origin=11 and ev_seqno=505698


?

You also didn't you say when things were failing before dropping the node.




thanks

Tory



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] drop node error

[Steve Singer]

On Mon, 18 Jul 2016, Tignor, Tom wrote:

One thing that I stress is that it is a good idea (and maybe very important) 
is that all nodes be caught up (or at least with respect to configuration 
events) before you issue the drop node command.  The automatic waitfor 
support in slonik is supposed to ensure this before submitting the drop 
node but maybe it isn't working as I expect or you might be doing something 
to subvert this.


Your error message looked like the remoteWorker_$droppedNode was trying to 
connect to the dropped node and pull data.  I THINK the remoteWorker would 
only be trying to pull data from the $dropped node if the local node thought 
that the dropped node was a set origin.  This makes me think that the 
failover wasn't confirmed by all nodes before issuing the drop node.  If 
your drop node is in the same slonik script as the failover then slonik 
'should' be doing this, but maybe there is a bug, or maybe your doing 
something to make the drop node get submitted too early.


Without more details it is hard to say.




Hi Steve,
	Thanks for looking into this. The context in which this occurs is in 
an effort to move a node. The specifics are somewhat involved, but the key 
points are that a node is failed over, then dropped and a new node with 
the same node ID created elsewhere (another DB on another host). The 
recent work makes a best effort to automatically reverse the steps if a 
failure is encountered, so failover and drop the new node, then recreate 
the original. There are certainly ‘wait for event’s along the way, but it 
seems likely all nodes aren’t fully caught up before an error. If you’re 
trying to reproduce, recurringly dropping and recreating a node with the 
same node ID could help.
	For the specific problem of the race, though, it might be simpler to 
just ‘kill –STOP’ the slon daemons on node A and then wait for node B to 
drop its schema (then resume with ‘kill –CONT’ to node A.)


Tom☺


On 7/17/16, 2:16 PM, "Steve Singer" <st...@ssinger.info> wrote:


On 07/12/2016 08:23 AM, Steve Singer wrote:

On 07/08/2016 03:27 PM, Tignor, Tom wrote:

  Hello slony group,

  I’m testing now with slony1-2.2.4. I have just recently
produced an error which effectively stops slon processing on some node A
due to some node B being dropped. The event reproduces only
infrequently. As some will know, a slon daemon for a given node which
becomes aware its node has been dropped will respond by dropping its
cluster schema. There appears to be a race condition between the node B
schema drop and the (surviving) node A receipt of the disableNode (drop
node) event. If the former occurs before the latter, all the remote
worker threads on node A enter an error state. See the log samples
below. I resolved this the first time by deleting all the recent
non-SYNC events from the sl_event tables, and more recently with a
simple node A slon restart.

  Please advise if there is any ticket I should provide
this info to, or if I should create a new one. Thanks.



The Slony bug tracker is at
http://bugs.slony.info/bugzilla/


I assume your saying that when the slon restart it keeps hitting this
error and keeps restarting.



Any more hints on how you reproduce this would be helpful.
I've been trying to reproduce this with no luck.

At the time you issue the drop node, are all other nodes caught up to
the drop'd node (and the event node) with respect to configuration events?

Ie if you do
sync(id=$NODE_ABOUT_TO_DROP);
wait for event(wait on=$NODE_ABOUT_TO_DROP, origin=$NODE_ABOUT_TO_DROP,
confirmed=all);

sync(id=$EVENT_NODE_FOR_DROP);
wait for event(wait on=$EVENT_NODE_FOR_DROP,
origin=$EVENT_NODE_FOR_DROP, confirmed=all);


(notice that I am NOT passing any timeout to the wait for).









 node 1 log 

2016-07-08 18:06:31 UTC [30382] INFO   remoteWorkerThread_99: SYNC
58 done in 0.002 seconds

2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_99: SYNC
59 done in 0.002 seconds

2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_2: SYNC
517869 done in 0.002 seconds

2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_3: SYNC
518148 done in 0.004 seconds

2016-07-08 18:06:45 UTC [30382] CONFIG remoteWorkerThread_2: update
provider configuration

2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_3: "select
last_value from "_ams_cluster".sl_log_status" PGRES_FATAL_ERROR ERROR:
schema "_ams_clu\

ster" does not exist

LINE 1: select last_value from "_ams_cluster".sl_log_status

 ^

2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_3: SYNC aborted

2016-07-08 18:06:45 UTC [30382] CONFIG version for "dbname=ams

host=198.18.102.45

user=ams_slony

sslmode=verify-ca

sslcert=/usr/local/akamai/.ams_certs/complete-ams_slony.crt

Re: [Slony1-general] drop node error

[Steve Singer]

On 07/12/2016 08:23 AM, Steve Singer wrote:
> On 07/08/2016 03:27 PM, Tignor, Tom wrote:
>>   Hello slony group,
>>
>>   I’m testing now with slony1-2.2.4. I have just recently
>> produced an error which effectively stops slon processing on some node A
>> due to some node B being dropped. The event reproduces only
>> infrequently. As some will know, a slon daemon for a given node which
>> becomes aware its node has been dropped will respond by dropping its
>> cluster schema. There appears to be a race condition between the node B
>> schema drop and the (surviving) node A receipt of the disableNode (drop
>> node) event. If the former occurs before the latter, all the remote
>> worker threads on node A enter an error state. See the log samples
>> below. I resolved this the first time by deleting all the recent
>> non-SYNC events from the sl_event tables, and more recently with a
>> simple node A slon restart.
>>
>>   Please advise if there is any ticket I should provide
>> this info to, or if I should create a new one. Thanks.
>>
>
> The Slony bug tracker is at
> http://bugs.slony.info/bugzilla/
>
>
> I assume your saying that when the slon restart it keeps hitting this
> error and keeps restarting.
>

Any more hints on how you reproduce this would be helpful.
I've been trying to reproduce this with no luck.

At the time you issue the drop node, are all other nodes caught up to 
the drop'd node (and the event node) with respect to configuration events?

Ie if you do
sync(id=$NODE_ABOUT_TO_DROP);
wait for event(wait on=$NODE_ABOUT_TO_DROP, origin=$NODE_ABOUT_TO_DROP, 
confirmed=all);

sync(id=$EVENT_NODE_FOR_DROP);
wait for event(wait on=$EVENT_NODE_FOR_DROP, 
origin=$EVENT_NODE_FOR_DROP, confirmed=all);


(notice that I am NOT passing any timeout to the wait for).





>
>
>>  node 1 log 
>>
>> 2016-07-08 18:06:31 UTC [30382] INFO   remoteWorkerThread_99: SYNC
>> 58 done in 0.002 seconds
>>
>> 2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_99: SYNC
>> 59 done in 0.002 seconds
>>
>> 2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_2: SYNC
>> 517869 done in 0.002 seconds
>>
>> 2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_3: SYNC
>> 518148 done in 0.004 seconds
>>
>> 2016-07-08 18:06:45 UTC [30382] CONFIG remoteWorkerThread_2: update
>> provider configuration
>>
>> 2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_3: "select
>> last_value from "_ams_cluster".sl_log_status" PGRES_FATAL_ERROR ERROR:
>> schema "_ams_clu\
>>
>> ster" does not exist
>>
>> LINE 1: select last_value from "_ams_cluster".sl_log_status
>>
>>  ^
>>
>> 2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_3: SYNC aborted
>>
>> 2016-07-08 18:06:45 UTC [30382] CONFIG version for "dbname=ams
>>
>> host=198.18.102.45
>>
>> user=ams_slony
>>
>> sslmode=verify-ca
>>
>> sslcert=/usr/local/akamai/.ams_certs/complete-ams_slony.crt
>>
>> sslkey=/usr/local/akamai/.ams_certs/ams_slony.private_key
>>
>> sslrootcert=/usr/local/akamai/etc/ssl_ca/canonical_ca_roots.pem"
>> is 90119
>>
>> 2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_2: "select
>> last_value from "_ams_cluster".sl_log_status" PGRES_FATAL_ERROR ERROR:
>> schema "_ams_clu\
>>
>> ster" does not exist
>>
>> LINE 1: select last_value from "_ams_cluster".sl_log_status
>>
>>  ^
>>
>> 2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_2: SYNC aborted
>>
>> 2016-07-08 18:06:45 UTC [30382] ERROR  remoteListenThread_99:
>> "select ev_origin, ev_seqno, ev_timestamp,ev_snapshot,
>> "pg_catalog".txid_sna\
>>
>> pshot_xmin(ev_snapshot),
>> "pg_catalog".txid_snapshot_xmax(ev_snapshot),ev_type,
>>ev_data1, ev_data2,ev_data3, ev_data4,ev\
>>
>> _data5, ev_data6,ev_data7, ev_data8 from "_ams_cluster".sl_event
>> e where (e.ev_origin = '99' and e.ev_seqno > '59') or
>> (e.ev_origin = '2'\
>>
>> and e.ev_seqno > '517870') or (e.ev_origin = '3' and e.ev_seqno >
>> '518151') order by e.ev_origin, e.ev_seqno limit 40" - ERROR:
>> schema "_ams_cluste\
>>
>> r" does n

Re: [Slony1-general] drop node error

[Steve Singer]

On 07/08/2016 03:27 PM, Tignor, Tom wrote:
>  Hello slony group,
>
>  I’m testing now with slony1-2.2.4. I have just recently
> produced an error which effectively stops slon processing on some node A
> due to some node B being dropped. The event reproduces only
> infrequently. As some will know, a slon daemon for a given node which
> becomes aware its node has been dropped will respond by dropping its
> cluster schema. There appears to be a race condition between the node B
> schema drop and the (surviving) node A receipt of the disableNode (drop
> node) event. If the former occurs before the latter, all the remote
> worker threads on node A enter an error state. See the log samples
> below. I resolved this the first time by deleting all the recent
> non-SYNC events from the sl_event tables, and more recently with a
> simple node A slon restart.
>
>  Please advise if there is any ticket I should provide
> this info to, or if I should create a new one. Thanks.
>

The Slony bug tracker is at
http://bugs.slony.info/bugzilla/


I assume your saying that when the slon restart it keeps hitting this 
error and keeps restarting.



>  node 1 log 
>
> 2016-07-08 18:06:31 UTC [30382] INFO   remoteWorkerThread_99: SYNC
> 58 done in 0.002 seconds
>
> 2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_99: SYNC
> 59 done in 0.002 seconds
>
> 2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_2: SYNC
> 517869 done in 0.002 seconds
>
> 2016-07-08 18:06:33 UTC [30382] INFO   remoteWorkerThread_3: SYNC
> 518148 done in 0.004 seconds
>
> 2016-07-08 18:06:45 UTC [30382] CONFIG remoteWorkerThread_2: update
> provider configuration
>
> 2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_3: "select
> last_value from "_ams_cluster".sl_log_status" PGRES_FATAL_ERROR ERROR:
> schema "_ams_clu\
>
> ster" does not exist
>
> LINE 1: select last_value from "_ams_cluster".sl_log_status
>
> ^
>
> 2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_3: SYNC aborted
>
> 2016-07-08 18:06:45 UTC [30382] CONFIG version for "dbname=ams
>
>host=198.18.102.45
>
>user=ams_slony
>
>sslmode=verify-ca
>
>sslcert=/usr/local/akamai/.ams_certs/complete-ams_slony.crt
>
>sslkey=/usr/local/akamai/.ams_certs/ams_slony.private_key
>
>sslrootcert=/usr/local/akamai/etc/ssl_ca/canonical_ca_roots.pem"
> is 90119
>
> 2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_2: "select
> last_value from "_ams_cluster".sl_log_status" PGRES_FATAL_ERROR ERROR:
> schema "_ams_clu\
>
> ster" does not exist
>
> LINE 1: select last_value from "_ams_cluster".sl_log_status
>
> ^
>
> 2016-07-08 18:06:45 UTC [30382] ERROR  remoteWorkerThread_2: SYNC aborted
>
> 2016-07-08 18:06:45 UTC [30382] ERROR  remoteListenThread_99:
> "select ev_origin, ev_seqno, ev_timestamp,ev_snapshot,
> "pg_catalog".txid_sna\
>
> pshot_xmin(ev_snapshot),
> "pg_catalog".txid_snapshot_xmax(ev_snapshot),ev_type,
>   ev_data1, ev_data2,ev_data3, ev_data4,ev\
>
> _data5, ev_data6,ev_data7, ev_data8 from "_ams_cluster".sl_event
> e where (e.ev_origin = '99' and e.ev_seqno > '59') or
> (e.ev_origin = '2'\
>
> and e.ev_seqno > '517870') or (e.ev_origin = '3' and e.ev_seqno >
> '518151') order by e.ev_origin, e.ev_seqno limit 40" - ERROR:
> schema "_ams_cluste\
>
> r" does not exist
>
> LINE 1: ...v_data5, ev_data6,ev_data7, ev_data8 from "_ams_clus...
>
>   ^
>
> 2016-07-08 18:06:55 UTC [30382] ERROR  remoteWorkerThread_3: "start
> transaction; set enable_seqscan = off; set enable_indexscan = on; "
> PGRES_FATAL_ERROR ERR\
>
> OR:  current transaction is aborted, commands ignored until end of
> transaction block
>
> 2016-07-08 18:06:55 UTC [30382] ERROR  remoteWorkerThread_3: SYNC aborted
>
> 2016-07-08 18:06:55 UTC [30382] ERROR  remoteWorkerThread_2: "start
> transaction; set enable_seqscan = off; set enable_indexscan = on; "
> PGRES_FATAL_ERROR ERR\
>
> OR:  current transaction is aborted, commands ignored until end of
> transaction block
>
> 2016-07-08 18:06:55 UTC [30382] ERROR  remoteWorkerThread_2: SYNC aborted
>
> 
>
>  node 99 log 
>
> 2016-07-08 18:06:44 UTC [558] INFO   remoteWorkerThread_1: SYNC
> 581216 done in 0.004 seconds
>
> 2016-07-08 18:06:44 UTC [558] INFO   remoteWorkerThread_2: SYNC
> 517870 done in 0.004 seconds
>
> 2016-07-08 18:06:44 UTC [558] INFO   remoteWorkerThread_3: SYNC
> 518150 done in 0.004 seconds
>
> 2016-07-08 18:06:44 UTC [558] INFO   remoteWorkerThread_1: SYNC
> 581217 done in 0.003 seconds
>
> 2016-07-08 18:06:44 UTC [558] WARN   remoteWorkerThread_3: got DROP NODE
> for local node ID
>
> NOTICE:  Slony-I: Please drop schema "_ams_cluster"
>
> NOTICE:  drop cascades to 171 other 

Re: [Slony1-general] cannot safely MOVE SET, DROP NODE

[Steve Singer]

On Tue, 28 Jun 2016, Tignor, Tom wrote:



 

    Hello slony community,

    I’m working now on some slony1 failover automation 
(slony1-2.2.4) and I’m having a lot of trouble
getting slony1 to honor MOVE SET commands. Below are the commands I’m using, to 
my mind pretty simple instructions to
move a set and confirm everyone gets the message (wait for event, confirmed by 
all) before moving on. Shortly after
moving on, I’m dropping the former set origin node. I suspect that’s why one of 
subscribers enters the perpetual
“MOVE_SET not received yet” loop, which I know isn’t a new slony1 problem. Is 
there any reason this shouldn’t work?
The FAILOVER command never does this. In the scenario I’m working on, I would 
have liked to avoid the heavy hammer,
but do I need to use FAILOVER to make this happen reliably?



Don't drop the old origin until after all the nodes have processed the 
ACCEPT SET.  Did that slonik script finish?  I would have expected that wait 
for wouldn't have finished until all the nodes had received the MOVE SET







    Thanks in advance,

 

 

--- slonik commands 

lock set (id=1, origin=1);

move set (id=1, old origin=1, new origin=3);

wait for event (origin=3, confirmed=all,

    wait on=3, timeout=60);



 

 remote subscriber log 

2016-06-28 12:23:26 UTC [19800] INFO   start processing ACCEPT_SET

2016-06-28 12:23:26 UTC [19800] INFO   ACCEPT: set=1

2016-06-28 12:23:26 UTC [19800] INFO   ACCEPT: old origin=1

2016-06-28 12:23:26 UTC [19800] INFO   ACCEPT: new origin=3

2016-06-28 12:23:26 UTC [19800] INFO   ACCEPT: move set seq=58

2016-06-28 12:23:26 UTC [19800] INFO   got parms ACCEPT_SET

2016-06-28 12:23:26 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet - 
sleep

2016-06-28 12:23:36 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet - 
sleep

2016-06-28 12:23:46 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet - 
sleep

2016-06-28 12:23:56 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet - 
sleep

2016-06-28 12:24:06 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet - 
sleep

2016-06-28 12:24:16 UTC [19800] INFO   ACCEPT_SET - MOVE_SET not received yet – 
sleep



 

 

    Tom    J

 

 



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

[Slony1-general] Slony 2.2.5 released

[Steve Singer]

The Slony team is pleased to announce Slony 2.2.5 the next minor release 
of the Slony 2.2.x series

Slony 2.2.5 includes the following changes

   - PG 9.5 makefile fix for win32
   - PG 9.6 header file fix
   - Bug 359 :: Additional parameter to GetConfigOptionByName() in HEAD
   - Remove unsupported warning for PG 9.5

Slony 2.2.5 can be downloaded from the following URL

http://www.slony.info/downloads/2.2/source/slony1-2.2.5.tar.bz2


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony with postgresql replication?

[Steve Singer]

On 05/21/2016 05:56 PM, Tory M Blue wrote:
> This maybe an odd request and let me preface this by saying, we have
> used Slony for over 10 years, it works great, we have had no real
> major issues.
>
> However my data sets, index creation times are starting to mean that a
> drop/add of my master or a full replicated slave, takes about 15 hours
> to come back on line (only takes about 2 hours to copy the data, but
> some indexes take 7 hours on their own).. So any maintenance that
> requires a drop/add (upgrades), requires a herculean effort, even with
> dropping indexes and adding them at the end manually and concurrently
> (as much as possible)
>
> So I was thinking with a master-master setup that Postgres provides,
> could I run Postgres replication between the master  servers
> (master/slave), and run Slony to create our read only db's (these take
> a bit less than 30 minutes to create)?

I am not sure what you mean by 'master-master'.  Postgresql core doesn't 
currently contain any 'master-master' solutions, there are numerous 
extensions or third party projects that claim some form of 
'master-master' solution each with a definition of 'master-master' and 
different limitations. It is really hard to say if slony is compatible 
with one without understanding which one you are talking about.

Postgresql core does have built in master / standby replication 
(streaming replication).

You can do a streaming replica for failover purposes and a slony replica 
for your reporting server.

If your streaming replica is an asynchronous replica then there is a 
risk that following a failover it is possible that the slony replica saw 
transactions that never made it to the streaming replica. The solution 
to this is to rebuild your slony replica following a failover (as a 
matter of procedure/practice) OR use a synchronous replica





>
> I've been unsuccessful in determining why the index creation takes so
> long (and right, it's not Slony, it's Postgresql!!)
>
> I would love to keep Slony for the read only query DB's and like I
> said considering moving to Postrgresql replication for the
> master/master. My query DB's only need about 5% of the data that is in
> the insert master/slave, so using Postgresql replication and forcing
> the same huge size db on each smaller query node, doesn't sound very
> appetizing.
>
> The other issue is any heavy process causes Slony to backup, major
> deletes back up, and it's just getting worse.
>
> Is this silly, is it either/or and I should now this?
>
> Thanks
> Tory
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] removing old slony triggers

[Steve Singer]

On 05/17/2016 02:59 PM, Mike James wrote:
> Hi, all. Due to some changes in our slony schema over the years, in our
> database I now see three slony schemas. Our current replication set only
> uses the most recent one. (slony 2.2.1 and PG 9.3)
>
> In the database when I "select * from pg_trigger; " I see some triggers
> from the older, unused schemas, particularly _logtrigger,
> _truncatetrigger have tgenabled = O.
>
> _denyaccess and _truncatedeny are disabled (tgenabled = D)
>
> How do I safely remove these triggers and delete the unused slony
> schemas? This maybe a simple question - but I'd rather not take the
> chance with the production database. Thanks,

What version of slony is are the old schema's based on?
Depending on the version this makes a difference on how you would remove 
them.

Steve


>
> Mike
> Clutch Holdings, LLC   Mike James | Manager of
> Infrastructure
> 267.419.6400, ext 204 | mike.ja...@clutch.com 
> 201 S Maple St. | Suite 250 | Ambler, PA 19002
> Clutch.com  | Twitter
>  | LinkedIn
>  | YouTube
>  | Clutch Support Center
> 
>
> The only end to end consumer management platform that empowers
> consumer-focused businesses to identify, target, message, and engage
> their best customers.
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Replication Lag?

[Steve Singer]

On 03/25/2016 11:13 AM, Rob Brucks wrote:
> I did not receive any other responses on this.
>
> Can I get this logged as a bug?
>
> And if so, how do I go about doing that.
>

The bugtracker is at http://bugs.slony.info/bugzilla/

A reproducable test case makes bugs a lot easier to fix.

Something else you might want to try,

1. Create a dummy table on your subscriber with some data
2. Create a second replication set with the subscriber as the origin
3. Subscribe the master to the second replication set

Does the subscription go through? Does the data replicate to the master? 
and if so at this point do the missing confirms also now show up?



> Thanks,
> Rob
>
>
>
>
> On 2/29/16, 10:28 AM, "slony1-general-boun...@lists.slony.info on behalf of 
> Rob Brucks" <slony1-general-boun...@lists.slony.info on behalf of 
> rob.bru...@rackspace.com> wrote:
>
>> Only errors connecting to the node I intentionally brought down as part of 
>> the test.
>>
>> No errors in the origin daemon connecting to the subscriber that is still up 
>> and running.  If I check pg_stat_activity I see connections from both 
>> daemons on both DB instances.
>>
>> Thanks,
>> Rob
>>
>>
>>
>>
>> On 2/29/16, 10:25 AM, "Jan Wieck" <j...@wi3ck.info> wrote:
>>
>>> On 02/29/2016 10:49 AM, Rob Brucks wrote:
>>>> 1. Yes, the sl_confirm data is showing up on the subscriber.
>>>>
>>>> 2. No, the origin node is not getting back the sl_confirm data from the 
>>>> active subscriber.
>>>
>>> Does the origin node log any errors that it cannot connect to that
>>> subscriber node?
>>>
>>>
>>> Jan
>>>
>>>
>>>
>>>>
>>>>
>>>> Thanks,
>>>> Rob
>>>>
>>>>
>>>>
>>>> On 2/26/16, 8:38 PM, "Steve Singer" <st...@ssinger.info> wrote:
>>>>
>>>>> On Fri, 26 Feb 2016, Rob Brucks wrote:
>>>>>
>>>>>>
>>>>>> But, if I insert some test data into the master DB, I see the data show 
>>>>>> up
>>>>>> on the remaining active slave.  So replication to the remaining slave DB
>>>>>> is obviously working.
>>>>>
>>>>> Replication with slony has two parts
>>>>>
>>>>> 1. Does the data replicate from the origin to the subscriber. When this
>>>>> happens a row is added to the subscriber's sl_event table with
>>>>> ev_origin=$origin_node and a confirm is added to the subscribers 
>>>>> sl_confirm
>>>>> table with con_origin=$origin_node and con_received=$subscriber_node
>>>>>
>>>>> 2. The sl_confirm row mentioned above needs to then get picked up by the
>>>>> slon for the origin node and brought back from the subscriber to the 
>>>>> origin.
>>>>>
>>>>> Are your confirms making it back?
>>>>>
>>>>>
>>>>>>
>>>>>> We use sl_status to monitor replication so we need it to accurately 
>>>>>> report lag if there's an issue.  The Slony 1.2 version we used before 
>>>>>> did not behave this way, it accurately reported which slave was not 
>>>>>> replicating.
>>>>>>
>>>>>> Why does sl_status report lag on the active slave even though 
>>>>>> replication appears to be working fine?
>>>>>>
>>>>>> Do I have a misconfiguration somewhere?
>>>>>>
>>>>>> Thanks,
>>>>>> Rob
>>>>>>
>>>>>>
>>>>>> Here's my slony config:
>>>>>>
>>>>>>
>>>>>>   CLUSTER NAME = slony;
>>>>>>   NODE 1 ADMIN CONNINFO = 'dbname=test_db host=/tmp port=5432 
>>>>>> user=slony';
>>>>>>   NODE 2 ADMIN CONNINFO = 'dbname=test_db host=/tmp port=5433 
>>>>>> user=slony';
>>>>>>   NODE 3 ADMIN CONNINFO = 'dbname=test_db host=/tmp port=5434 
>>>>>> user=slony';
>>>>>>
>>>>>>  CLUSTERS
>>>>>>
>>>>>>   INIT CLUSTER (ID = 1, COMMENT = 'Master');
>>>>>>
>>>>>>
>>>>>>  NODES
>>>>>>
>>>>>>   STORE NODE (ID = 2, COMMENT

Re: [Slony1-general] Replication Lag?

[Steve Singer]

On Fri, 26 Feb 2016, Rob Brucks wrote:

>
> But, if I insert some test data into the master DB, I see the data show up 
> on the remaining active slave.  So replication to the remaining slave DB 
> is obviously working.

Replication with slony has two parts

1. Does the data replicate from the origin to the subscriber. When this 
happens a row is added to the subscriber's sl_event table with 
ev_origin=$origin_node and a confirm is added to the subscribers sl_confirm 
table with con_origin=$origin_node and con_received=$subscriber_node

2. The sl_confirm row mentioned above needs to then get picked up by the 
slon for the origin node and brought back from the subscriber to the origin.

Are your confirms making it back?


>
> We use sl_status to monitor replication so we need it to accurately report 
> lag if there's an issue.  The Slony 1.2 version we used before did not behave 
> this way, it accurately reported which slave was not replicating.
>
> Why does sl_status report lag on the active slave even though replication 
> appears to be working fine?
>
> Do I have a misconfiguration somewhere?
>
> Thanks,
> Rob
>
>
> Here's my slony config:
>
>
>  CLUSTER NAME = slony;
>  NODE 1 ADMIN CONNINFO = 'dbname=test_db host=/tmp port=5432 user=slony';
>  NODE 2 ADMIN CONNINFO = 'dbname=test_db host=/tmp port=5433 user=slony';
>  NODE 3 ADMIN CONNINFO = 'dbname=test_db host=/tmp port=5434 user=slony';
>
>  CLUSTERS
>
>  INIT CLUSTER (ID = 1, COMMENT = 'Master');
>
>
>  NODES
>
>  STORE NODE (ID = 2, COMMENT = 'Slave1', EVENT NODE = 1);
>  STORE NODE (ID = 3, COMMENT = 'Slave2', EVENT NODE = 1);
>
>
>  PATHS
>
>  STORE PATH (SERVER = 1, CLIENT = 2, CONNINFO = 'dbname=test_db host=/tmp 
> port=5432 user=slony');
>  STORE PATH (SERVER = 1, CLIENT = 3, CONNINFO = 'dbname=test_db host=/tmp 
> port=5432 user=slony');
>  STORE PATH (SERVER = 2, CLIENT = 1, CONNINFO = 'dbname=test_db host=/tmp 
> port=5433 user=slony');
>  STORE PATH (SERVER = 2, CLIENT = 3, CONNINFO = 'dbname=test_db host=/tmp 
> port=5433 user=slony');
>  STORE PATH (SERVER = 3, CLIENT = 1, CONNINFO = 'dbname=test_db host=/tmp 
> port=5434 user=slony');
>  STORE PATH (SERVER = 3, CLIENT = 2, CONNINFO = 'dbname=test_db host=/tmp 
> port=5434 user=slony');
>
>
>  SETS
>
>  CREATE SET (ID = 1, ORIGIN = 1, COMMENT = 'TEST Set 1');
>
>  SEQUENCES
>
>  SET ADD SEQUENCE (SET ID = 1, ORIGIN = 1, ID = 1, FULLY QUALIFIED NAME = 
> '"public"."test_seq"');
>
>  TABLES
>
>  SET ADD TABLE (SET ID = 1, ORIGIN = 1, ID = 2, FULLY QUALIFIED NAME = 
> '"public"."test"');
>
>  SUBSCRIPTIONS
>
>  SUBSCRIBE SET (ID = 1, PROVIDER = 1, RECEIVER = 2, FORWARD = YES);
>  SUBSCRIBE SET (ID = 1, PROVIDER = 1, RECEIVER = 3, FORWARD = YES);
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Is slony.info down?

[Steve Singer]

On Tue, 9 Feb 2016, Asad Shah wrote:

> I cannot get to the slony site right now?
> Is it down?

Working for me right now, but it could have been down.

> 
> Regards,
> Asad
> 
> 
> 
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] duplicate key value violates unique constraint?

[Steve Singer]

On 01/21/2016 10:42 AM, Frank McGeough wrote:
> I’m having intermittent issues that result in the message "duplicate key 
> value violates unique constraint” in the slony logs. Here is my configuration 
> :
>
> 1 master
> 2 slaves
> postgresql 9.3.5 on all servers
> slony2.2.2
>
> All the servers are in the same network and running the same Linux o/s with 
> the same file system. The issues just seem to happen randomly. They happen 
> primarily on one table but have occurred on two different tables up to this 
> point (the tables are logically related in the database - one is populated by 
> events on the other one, the one that has most of the issues is the one with 
> the trigger on it that populates the other table). When the error occurs it 
> may occur on one slave or the other. Once on both.
>
> These incidents have happened 6 times in the past week. Prior to this issue 
> we’ve been running the same setup for many months without incident.
>
> What I do to correct this is to actually remove the entry that is causing the 
> duplicate key issue from the sl_log table. Replication picks back up again. 
> When I compare the rows on all servers I see that they are the same. In all 
> cases when the servers are back in sync the row is actually gone from all 
> servers.
>
> There is no obvious corruption issue on the slave boxes. I’m not sure what 
> else to check at this point. Are there suggestions from the slony developers 
> on how to investigate what is actually going on to cause these issues? Any 
> help is appreciated at this point.

Above you say 'the one with the trigger on it that populates the other 
table'

You sort of imply you have triggers on tables but don't provide any details.

Do these triggers fire on replicas, the origin, everywhere?

Do the triggers insert data into a replicated table?

The conflicting row, you see the second insert for it in sl_log but do 
you know where the first instance comes from?  Is the row recently 
added? (if so you do you see the first insert in sl_log also) or has the 
row been there a long time.

Is the conflicting row otherwise identical to the existing row or is it 
only identical with respet to the primary key (ie the last_updated 
times, do they match?)




>
> sample error :
>
> 2016-01-21 01:19:01 UTC ERROR  remoteWorkerThread_1_1: error at end of COPY 
> IN: ERROR:  duplicate key value violates unique constraint 
> "device_properties_pkey"
> DETAIL:  Key (device_guid, property_guid, data_index)=(26464008, 39, 0) 
> already exists.
> CONTEXT:  SQL statement "INSERT INTO "device"."device_properties" 
> ("device_guid", "property_guid", "data_index", "property_value", "tran_id", 
> "dt_last_updated", "last_updated_by_userid", "version_id") VALUES ($1, $2, 
> $3, $4, $5, $6, $7, $8);"
> COPY sl_log_1, line 36: "1  6117236903  298 7797340846  
> device  device_properties   I   0   
> {device_guid,26464008,property_guid,39,data…"
>
> thanks,
> Frank
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] log insert/update query executed on subscriber

[Steve Singer]

On Thu, 14 Jan 2016, Krzysztof Jakowczyk wrote:

> Our java aplication have bug and from time to time it's trying to insert
> row on subscriber node (which shouldn't happend). My developers wants to
> have query with parametrs which is executed on subscriber node. Table
> audit.slon_audit is created only on subscriber node to catch "bad"
> INSERT/UPDATE queries. I know that this queries shoud executed only on
> origin. I want to find the cause of this behavior of our aplication.

Even if the audit trigger fires first the denyAccess trigger will abort the 
transaction causing any changes added to the audit table to be rolled back.



>
>
> On 13.01.2016 23:37, Steve Singer wrote:
>> On 01/13/2016 01:52 AM, Krzysztof Jakowczyk wrote:
>>> No, it doesn't. Take a look:
>>>
>>> # \d emp
>>>Table "public.emp"
>>>   Column  |  Type   | Modifiers
>>> -+-+---
>>>   empname | text| not null
>>>   salary  | integer |
>>> Triggers:
>>>  _b24v2_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON emp FOR EACH
>>> ROW EXECUTE PROCEDURE _b24v2.denyaccess('_b24v2')
>>> Triggers firing always:
>>>  __audit BEFORE INSERT OR DELETE OR UPDATE ON emp FOR EACH ROW
>>> EXECUTE PROCEDURE save_query()
>>>
>>>
>>> -- with disabled trigger denyaccess
>>> # insert into emp values ('TEST dasdasdas',123123);
>>> INSERT 0 1
>>> # select * from audit.slon_audit;
>>>   operation |   stamp|  userid
>>> |   query
>>> ---++--+---
>>>
>>>   I | 2016-01-13 06:46:15.417799 | postgres | insert into emp
>>> values ('TEST dasdasdas',123123);
>>> (1 row)
>>>
>>> -- enabling trigger denyaccess
>>> # alter table emp enable trigger _b24v2_denyaccess;
>>> ALTER TABLE
>>> # insert into emp values ('test2',31337);
>>> ERROR:  Slony-I: Table emp is replicated and cannot be modified on a
>>> subscriber node - role=0
>>
>> I guess I don't understand what it is your trying to do.
>>
>> What I thought you were asking was this
>>
>> You have the table "emp" which has node1 as the origin.
>>
>> You replicate the table "emp" to node 2.
>>
>> You have the audit.slon_audit table on both node1 and node2. You want
>> the trigger to fire on both node1 and node2 instead of replicating
>> audit.slon_audit
>>
>> If that's the case why are you trying your insert on node2?
>> You want to perform the insert on node1,and then the audit trigger
>> should run (assuming you configure it as an always trigger) on both
>> node1 and on node2 inserting data into your audit table
>>
>>
>>
>>
>>
>>> # select * from audit.slon_audit;
>>>   operation |   stamp|  userid
>>> |   query
>>> ---++--+---
>>>
>>>   I | 2016-01-13 06:46:15.417799 | postgres | insert into emp
>>> values ('TEST dasdasdas',123123);
>>> (1 row)
>>>
>>>
>>> Nothing happend. Any other ideas?
>>>
>>
>> .
>>
>
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slon blocked by an index on a non replicated table?? 2.2.3

[Steve Singer]

On 01/13/2016 09:38 PM, Tory M Blue wrote:
>
>
> On Wed, Jan 13, 2016 at 6:30 PM, Steve Singer <st...@ssinger.info
> <mailto:st...@ssinger.info>> wrote:
>
> On Wed, 13 Jan 2016, Tory M Blue wrote:
>
> Tory,
>
> You talk about slon 'initializing'.  When subscriptions start it
> needs to wait until all in progress transactions are committed
> before starting the copy. Once your cluster is subscribed a create
> index shouldn't block things.
>
>
> Ya 2 different issues, sorry. but the Initialization part, even if the
> table being indexed is not part of the set? That rings weird and I
> really wish i could find the other thread that had a discussion on this,
> as it has the correct error etc.
>
> But an index on a table that is not part of any replication set, blocks
> slony from starting the copy?  We are talking table based replication
> here right, so we are not looking at the db level, which I could sort of
> understand. Since slony is replicating tables, if this table is not part
> of any set and thus is not being replicated, why does that hold true?
>

ANY in progress transaction on the master blocks the initial copy even 
if it hasn't yet touched a replicated table.

The comment in the code explains the reason for this and is as follows


/*
 * Begin a serialized transaction and verify that the event's snapshot
 * xxid is less than the present snapshot. This ensures that all
 * transactions that have been in progress when the subscription got
 * enabled (which is after the triggers on the tables have been 
defined),
 * have finished. Otherwise a long running open transaction would not 
have
 * the trigger definitions yet, and an insert would not get logged. But 
if
 * it still runs when we start to copy the set, then we don't see the 
row
 * either and it would get lost.



> And while I'm fully replicated now, if I try to index these tables, slon
> backs up and if I kill the index, the replications set happen
> immediately. So something is happening with these tables. These are
> archive tables, nothing is accessing them, they are here purely for
> historical purposes.  So I'm at a loss and I don't expect anyone to have
> an immediate answer, but it seems weird and would love to provide any
> necessary information to help frame the question/issue better, if
> someone can help me do that :)
>
> Thanks again Steve!
>
> Tory
>
>
>
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Make all slon options actually settable from the command line?

[Steve Singer]

On 01/13/2016 06:59 PM, David Fetter wrote:
>
> I'm happy to add in some form of getopt_long, which I generally prefer
> for self-documentation purposes.  Do we need to do something special
> the way PostgreSQL does?  I presume simply mandating GNU wouldn't
> work.

If your hoping to have something that we could add for a 2.2.x then 
there I wouldn't want to introduce or change any dependencies.

In terms of requiring GNU getopt for 2.3.x I guess it depends on how 
common that is on other platforms:the bsd's, solaris and AIX.  I don't 
think slon uses command line options on win32 (but I could be wrong). I 
do expect slony to build on those platforms without requiring a lot more 
effort than what would be required to build PG.

My take is adding additional command line options is fine for a dot 
release as long as we don't change the names of any existing options. If 
anyone disagrees they should speak up.




>
>> If you're keen on patching in long-ish option names for everything,
>> I don't see a big reason to struggle against that.
>
> I'd be delighted to improve the clarity here.
>
> Cheers,
> David.
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] log insert/update query executed on subscriber

[Steve Singer]

On 01/13/2016 01:52 AM, Krzysztof Jakowczyk wrote:
> No, it doesn't. Take a look:
>
> # \d emp
>Table "public.emp"
>   Column  |  Type   | Modifiers
> -+-+---
>   empname | text| not null
>   salary  | integer |
> Triggers:
>  _b24v2_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON emp FOR EACH
> ROW EXECUTE PROCEDURE _b24v2.denyaccess('_b24v2')
> Triggers firing always:
>  __audit BEFORE INSERT OR DELETE OR UPDATE ON emp FOR EACH ROW
> EXECUTE PROCEDURE save_query()
>
>
> -- with disabled trigger denyaccess
> # insert into emp values ('TEST dasdasdas',123123);
> INSERT 0 1
> # select * from audit.slon_audit;
>   operation |   stamp|  userid
> |   query
> ---++--+---
>   I | 2016-01-13 06:46:15.417799 | postgres | insert into emp
> values ('TEST dasdasdas',123123);
> (1 row)
>
> -- enabling trigger denyaccess
> # alter table emp enable trigger _b24v2_denyaccess;
> ALTER TABLE
> # insert into emp values ('test2',31337);
> ERROR:  Slony-I: Table emp is replicated and cannot be modified on a
> subscriber node - role=0

I guess I don't understand what it is your trying to do.

What I thought you were asking was this

You have the table "emp" which has node1 as the origin.

You replicate the table "emp" to node 2.

You have the audit.slon_audit table on both node1 and node2. You want 
the trigger to fire on both node1 and node2 instead of replicating 
audit.slon_audit

If that's the case why are you trying your insert on node2?
You want to perform the insert on node1,and then the audit trigger 
should run (assuming you configure it as an always trigger) on both 
node1 and on node2 inserting data into your audit table





> # select * from audit.slon_audit;
>   operation |   stamp|  userid
> |   query
> ---++--+---
>   I | 2016-01-13 06:46:15.417799 | postgres | insert into emp
> values ('TEST dasdasdas',123123);
> (1 row)
>
>
> Nothing happend. Any other ideas?
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] log insert/update query executed on subscriber

[Steve Singer]

On Tue, 12 Jan 2016, Krzysztof Jakowczyk wrote:


Hello,

Is it possible to log query with parameters executed on subscriber node?
I've tried to add __audit trigger before insert or update, but when
denyaccess trigger is enabled, nothing is logged. Below is my try for
table emp:





CREATE TRIGGER __audit
BEFORE INSERT OR UPDATE OR DELETE ON emp
   FOR EACH ROW EXECUTE PROCEDURE save_query();


Setting the trigger to fire always should work.

ALTER TABLE foo ENABLE ALWAYS TRIGGER __audit;

http://www.postgresql.org/docs/9.1/interactive/sql-altertable.html





--
Pozdrawiam,

Krzysztof Jakowczyk
Administrator Systemów Unix

Grupa Unity | ul. Przedmiejska 6-10, 54-201 Wrocław
ul. Conrada 55B, 31-357 Kraków | ul. Złota 59, 00-120 Warszawa



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general
___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony-I: log switch to sl_log_2 still in progress - sl_log_1 not truncated

[Steve Singer]

On Thu, 7 Jan 2016, Tory M Blue wrote:



So I'm backing up in a big way. I know what started it, "adding a new insert 
slave which took 13 hours to complete (indexes
etc)".. But now it doesn't appear I am able to catch up. I see the slave doing 
what it's suppose to, get a bunch of data,
truncate the sl_log files move on. But the master is having a hard time.

Postgres 9.4.5 and Slony 2.2.3

All other nodes don't have any errors or issues.

this is Node 1 (the master)
node 2 is a slave
node 3-5 are query slaves with only 1 of 3 sets being replicated too.

I have interval at 5 minutes and sync_group_maxsize=50

Any suggestions on where to thump it. At some point this will cause issues on 
my master and when I see that starting, I'll
have to drop node 2 again, and when i add it, it will take 13+ hours and I'll 
be back in the same position :)


Bump sync_group_maxsize to be much bigger, I'm not saying that will solve 
the problem but it might help(max allowed is 10,000). I'm also suspect when 
you say your have a sync_interval of 5 minutes, since I thought 60 seconds was the largest 
allowed.






Thanks
Tory



Node:  Old Transactions Kept Open

Old Transaction still running with age 01:48:00 > 01:30:00

Query: autovacuum: VACUUM


Node: 0 threads seem stuck

Slony-I components have not reported into sl_components in interval 00:05:00

Perhaps slon is not running properly?

Query:
     select co_actor, co_pid, co_node, co_connection_pid, co_activity, 
co_starttime, now() - co_starttime, co_event,
co_eventtype
     from "_admissioncls".sl_components
     where  (now() - co_starttime) > '00:05:00'::interval
     order by co_starttime;
  


Node: 1 sl_log_1 tuples = 219700 > 20

Number of tuples in Slony-I table sl_log_1 is 219700 which
exceeds 20.

You may wish to investigate whether or not a node is down, or perhaps
if sl_confirm entries have not been propagating properly.


Node: 1 sl_log_2 tuples = 1.74558e+07 > 20

Number of tuples in Slony-I table sl_log_2 is 1.74558e+07 which
exceeds 20.

You may wish to investigate whether or not a node is down, or perhaps
if sl_confirm entries have not been propagating properly.


Node: 2 sl_log_2 tuples = 440152 > 20

Number of tuples in Slony-I table sl_log_2 is 440152 which
exceeds 20.

You may wish to investigate whether or not a node is down, or perhaps
if sl_confirm entries have not been propagating properly.



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony-I: log switch to sl_log_2 still in progress - sl_log_1 not truncated

[Steve Singer]

On Thu, 7 Jan 2016, Tory M Blue wrote:


  Bump sync_group_maxsize to be much bigger, I'm not saying that will solve 
the problem but it might help(max
  allowed is 10,000). I'm also suspect when you say your have a 
sync_interval of 5 minutes, since I thought 60
  seconds was the largest allowed.

My apologies

cleanup_interval="5 minutes"  

my interval is 1000ms

And sync, group cites 100 is the max

# Range:  [0,100], default: 6

sync_group_maxsize=50


Where does that come from?
http://www.slony.info/documentation/2.2/slon-config-interval.html

says the max is 10,000 and the code looks like it agrees.  Try it and see if 
you start to catch up.  Also an analyze on your sl_log_1 and sl_log_2 can't 
hurt.


With a sync_group_size of 20 slon will select the data for at most 20 SYNC's 
at once and apply them (using a select from sl_log_1 ... union select from 
sl_log_2 ...)


With a sync_group_size of 10,000 it can in theory select 10,000 sync's at 
once (but I think it takes a while to work up to that point).


If your bottleneck is the master then it is possible that the selecting from 
sl_log is causing the problem.


Slon can't truncate a log until all the data in that log has been 
replicated and there are only 2 logs.











___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony Process Repeatedly Killed

[Steve Singer]

slon has a parent pid and a child process.  On all sorts of errors the child worker will ask the parent to kill it. Chances are slon is issuing the kill signal.   The question is what earlier error is causing this to happen.  Yesterday or maybe the day before someone else from consistent state was asking about a similiar setup with utf8 encoding errors. Maybe this is the same or a related issue? in anycase you need to find the earlier error in the slon logsteveGrant Schaefer wrote:Hello - I'm deploying Slony1-2.2.4 from a PG 9.2 instance to 9.4.  All data within subscribed tables is UTF8, the subscribed node contains a firewall so that the slon daemon is not killed during its initial sync and connectivity is reliable.  But I still see a repeated "server closed the connection error" during Slony's initial sync.  This seems to happen randomly after an hour or two of running through the initial sync.  The target database is 140 GB.  I'm not exceeding SHMMAX settings.  This error: "CONFIG slon: child terminated signal:
 9; pid: 300, current worker pid: 300" has me wondering what is issuing kill -9 to slon daemons.  Is it postgres?  I know that Slony uses OS TCP keepalive settings and those are set to allow the daemon to run indefinitely.Any thoughts on this?Thank you.
Sent Using Firefox OS
___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] SLONY initial sync (PostgreSQL 9.2 master, PostgreSQL 9.4 slave) producing out of memory and invalid byte sequence errors in the postgres log

[Steve Singer]

On 12/22/2015 02:25 PM, CS DBA wrote:
> SLONY version 2.2.4
>
>

Does setting the client encoding for both connections (explicitly) to 
utf8 make any difference?

Are you able to determine what row/value is causing the problem?

What does the table look like? Just integers and text columns or 
anything more fancy or particularly large?

Are you able to restore a pg_dump of the database on 9.4 (to rule out 
there being some tightening of utf8 conversion in the newer version or 
something)




> On 12/22/2015 12:10 PM, Steve Singer wrote:
>> On 12/22/2015 02:03 PM, CS DBA wrote:
>>
>> You forgot to tell us your slony version
>>
>>> Hi All;
>>>
>>> We are replicating from a PostgreSQL 9.2 database (our Master) to a
>>> PostgreSQL 9.4 database (our slave) - server encoding is UTF8
>>>
>>> The slon process for the slave has this entry:
>>>
>>> 2015-12-22 11:28:31 CST ERROR  remoteWorkerThread_1: PQputCopyData() -
>>> server closed the connection unexpectedly
>>>   This probably means the server terminated abnormally
>>>   before or while processing the request.
>>> 2015-12-22 11:28:31 CST WARN   remoteWorkerThread_1: data copy for set 1
>>> failed 1 times - sleep 15 seconds
>>> FATAL:  invalid frontend message type 166
>>> 2015-12-22 11:28:31 CST ERROR  remoteWorkerThread_1: "rollback
>>> transaction" PGRES_FATAL_ERROR
>>> 2015-12-22 11:28:31 CST CONFIG slon: child terminated signal: 9; pid:
>>> 12450, current worker pid: 12450
>>> 2015-12-22 11:28:31 CST CONFIG slon: restart of worker in 10 seconds
>>>
>>> The process then begins to copy seemingly successful initial table
>>> sync's
>>> however the postgresql log file has entries like this:
>>>
>>>
>>> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ ERROR:  out of
>>> memory
>>> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ DETAIL: Cannot
>>> enlarge string buffer containing 0 bytes by 1572761385 more bytes.
>>> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ CONTEXT: COPY
>>> /*[table name] */, line 2332808
>>> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ STATEMENT: select
>>> "_/*[schema name]*/".prepareTableForCopy(11); copy "public"."/*[table
>>> name]*/" /*...*/  from stdin;
>>>
>>>
>>>
>>> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ LOG:  could not
>>> send data to client: Broken pipe
>>> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ FATAL: connection
>>> to client lost
>>> 2015-12-22 14:56:10 UTC /*[db name] [cluster name]*/ LOG:  could not
>>> send data to client: Broken pipe
>>> 2015-12-22 14:56:10 UTC /*[db name] [cluster name]*/ FATAL: connection
>>> to client lost
>>> 2015-12-22 17:28:50 UTC /*[db name] [cluster name]*/ ERROR: invalid
>>> byte sequence for encoding "UTF8": 0x9d
>>> 2015-12-22 17:28:50 UTC /*[db name] [cluster name]*/ CONTEXT: COPY
>>> transcript_indices, line 35227547
>>> 2015-12-22 17:28:50 UTC /*[db name] [cluster name]*/ STATEMENT: select
>>> "_/*[schema name]*/".prepareTableForCopy(7); copy "public"."/*[table
>>> name]*/" /*...*/ from stdin;
>>> 2015-12-22 17:28:54 UTC /*[db name] [cluster name]*/ FATAL: invalid
>>> frontend message type 166
>>>
>>>
>>>
>>> Thoughts?
>>>
>>> Thanks in advance...
>>>
>>>
>>>
>>>
>>> ___
>>> Slony1-general mailing list
>>> Slony1-general@lists.slony.info
>>> http://lists.slony.info/mailman/listinfo/slony1-general
>>>
>>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] SLONY initial sync (PostgreSQL 9.2 master, PostgreSQL 9.4 slave) producing out of memory and invalid byte sequence errors in the postgres log

[Steve Singer]

On 12/22/2015 02:03 PM, CS DBA wrote:

You forgot to tell us your slony version

> Hi All;
>
> We are replicating from a PostgreSQL 9.2 database (our Master) to a
> PostgreSQL 9.4 database (our slave) - server encoding is UTF8
>
> The slon process for the slave has this entry:
>
> 2015-12-22 11:28:31 CST ERROR  remoteWorkerThread_1: PQputCopyData() -
> server closed the connection unexpectedly
>  This probably means the server terminated abnormally
>  before or while processing the request.
> 2015-12-22 11:28:31 CST WARN   remoteWorkerThread_1: data copy for set 1
> failed 1 times - sleep 15 seconds
> FATAL:  invalid frontend message type 166
> 2015-12-22 11:28:31 CST ERROR  remoteWorkerThread_1: "rollback
> transaction" PGRES_FATAL_ERROR
> 2015-12-22 11:28:31 CST CONFIG slon: child terminated signal: 9; pid:
> 12450, current worker pid: 12450
> 2015-12-22 11:28:31 CST CONFIG slon: restart of worker in 10 seconds
>
> The process then begins to copy seemingly successful initial table sync's
> however the postgresql log file has entries like this:
>
>
> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ ERROR:  out of memory
> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ DETAIL:  Cannot
> enlarge string buffer containing 0 bytes by 1572761385 more bytes.
> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ CONTEXT:  COPY
> /*[table name] */, line 2332808
> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ STATEMENT:  select
> "_/*[schema name]*/".prepareTableForCopy(11); copy "public"."/*[table
> name]*/" /*...*/  from stdin;
>
>
>
> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ LOG:  could not
> send data to client: Broken pipe
> 2015-12-22 14:56:05 UTC /*[db name] [cluster name]*/ FATAL:  connection
> to client lost
> 2015-12-22 14:56:10 UTC /*[db name] [cluster name]*/ LOG:  could not
> send data to client: Broken pipe
> 2015-12-22 14:56:10 UTC /*[db name] [cluster name]*/ FATAL:  connection
> to client lost
> 2015-12-22 17:28:50 UTC /*[db name] [cluster name]*/ ERROR:  invalid
> byte sequence for encoding "UTF8": 0x9d
> 2015-12-22 17:28:50 UTC /*[db name] [cluster name]*/ CONTEXT:  COPY
> transcript_indices, line 35227547
> 2015-12-22 17:28:50 UTC /*[db name] [cluster name]*/ STATEMENT:  select
> "_/*[schema name]*/".prepareTableForCopy(7); copy "public"."/*[table
> name]*/" /*...*/ from stdin;
> 2015-12-22 17:28:54 UTC /*[db name] [cluster name]*/ FATAL:  invalid
> frontend message type 166
>
>
>
> Thoughts?
>
> Thanks in advance...
>
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Data in sl_log table that was not replicated,

[Steve Singer]

On 12/17/2015 03:21 PM, Tory M Blue wrote:
> I've done a switchover and as part of a migration to another center, but
> there was data that didn't replicate, we are unclear why but will be
> investigating.
>
> The question at hand, is there any way to dump the sl_log file and
> somehow get it into the new cluster (that new cluster does not have this
> data). It's been a couple of days so I can't just dump and load the data
> (more data would be lost), so it's really about retrieving what is in
> sl_log and getting that into the new cluster.
>
> If not, no big deal, but figured I would ask if there is a way.
>

Assuming your on slony 2.2 I *THINK* you can copy (ie COPY FROM ..  | 
COPY TO) the contents from sl_log_1/2 on the origin to sl_log_1/2 on the 
replica, that should cause the logApply trigger to fire and insert the rows.

I've never actually tried doing this, you would want to test it well to 
determine if it actually works without side-effects. The fact that the 
switchover missed data is a huge redflag that makes me think something 
else is going on.





> Thanks
> Tory
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] prepare clone failure

[Steve Singer]

On 12/07/2015 09:25 PM, Josh Berkus wrote:
> On 12/07/2015 11:32 AM, Josh Berkus wrote:
>> On 12/07/2015 10:56 AM, Josh Berkus wrote:
>>> So, the prepare clone method above worked perfectly twice.  But then we
>>> tried to bring up a new node as a prepared clone from node 11 and things
>>> went to hell.
>>
>> One thing I just realized was different between the first two,
>> successful, runs and the failed runs:  the first two times, we didn't
>> have pg_hba.conf configured, so when we brought up slony on the new node
>> it couldn't connect until we fixed that.
>>
>> So I'm wondering if there's a timing issue here somewhere.
>
> So, this problem was less interesting than I thought.  As it turns out,
> the sysadmin was handling "make sure slony doesn't start on the server"
> by letting it autostart, then shutting it down.  In the couple minutes
> it was running, though, it did enough to prevent finish clone from working.
>


I wonder if there is more going on here


In remoteWorker_event

We have

if (node->last_event >= ev_seqno)
{
rtcfg_unlock();
slon_log(SLON_DEBUG2,
 "remoteWorker_event: event %d," INT64_FORMAT
 " ignored - duplicate\n",
 ev_origin, ev_seqno);
return;
}

/*
 * We lock the worker threads message queue before bumping the nodes 
last
 * known event sequence to avoid that another listener queues a later
 * message before we can insert this one.
 */
pthread_mutex_lock(&(node->message_lock));
node->last_event = ev_seqno;
rtcfg_unlock();


It seems strange to me that we are obtaining the mutex lock after 
checking node->last_event.
Does the rtcfg_lock prevent the race condition making the direct 
message_lock redundent? If not do we need to obtain the 
node->message_lock before we do the comparision?


The CLONE_NODE handler in remote_worker sets last_event by calling 
rtcfg_getNodeLastEvent which obtains the rtcfg_lock but not the message 
lock.

The clone node handler in remote_worker seems to do this
1. call rtcfg_storeNode (which obtains then releases the config lock)
2. calls cloneNodePrepare_int()
3. queries the last event id
4. calls rtcfg_getNodeLastEvent() which would re-obtain then release the 
config lock

I wonder if sometime after step 1 but before step 4 a remote listener 
queries events from the new node and adds them into the queue because 
the last_event hasn't yet been set.

Maybe cloneNodePrepare needs to obtain the message queue lock at step 1 
and hold it until step 4 and then remoteWorker_event needs to obtain 
that lock a bit earlier



___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] remote listener serializability

[Steve Singer]

On 11/19/2015 01:09 PM, Tignor, Tom wrote:
>   A general question for the group: if we would consider a change like 
> this
> (as a runtime option or otherwise), what’s the correct way to move it
> forward? Should I file a bug? Are there specific tests or analysis which
> should be performed?

I would use bug 336 for this.
My gut is to make this a runtime option since I am not confident that
downgrading the isolation level won't impact someones workload, having
said that I am open to being convinced otherwise.  I would also be
inclined to leave the default value unchanged for 2.2.x and then maybe
consider changing the default in 2.3.   If we actually any concrete
plans for a 2.3 version I would say just leave the change for 2.3 but I
don't see a 2.3 release happening soon and I don't want to change the
default behaviour in a minor release.


Generally when we make a change like this I try to do lots of runs
through the disorder/clustertest suite that doesn't cover all types of
workloads.  Have you tried this change on your workload? Does it
actually help things?

It would be great if Jan and Chris were to comment on this thread

> 
>   Tom:-)
> 
> 
> On 11/18/15, 10:35 AM, "Greg Sabino Mullane"  wrote:
> 
>> On Wed, Nov 18, 2015 at 02:26:15PM +, Tom Tignor wrote:
>> ...
>>> Sorry for the delay getting back. Inspired by your questions, I¹ve been
>>> reading up on SSI, the Cahill paper and slony1 and postgres code.
>> ...
>>
>> It should be pointed out that 9.1 goes EOL (End of Life) in less than
>> a year (Sep 2016), and transaction handling has changed a *lot* since
>> then,
>> so any changes that core Slony makes may not even work for you.
>>
>> (FWIW, I think dropping the isolation level in this particular
>> instance seems safe, however.)
>>
>> -- 
>> Greg Sabino Mullane g...@endpoint.com
>> End Point Corporation
>> PGP Key: 0x14964AC8
> 
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
> 

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] remote listener serializability

[Steve Singer]

On 11/20/2015 09:56 AM, Jan Wieck wrote:
> Without taking SYNC snapshots in a SERIALIZABLE transaction I believe
> that a Slony-I replica could suffer the same inconsistency dangers that
> a pg_dump without --serializable-deferrable can suffer. Namely a replica
> would not be usable as a source for reporting. From the 9.4 pg_dump docs:

I was wondering if this is actually possible or not.

The remote slon only selects from  sl_event and sl_log_*.  The remote
worker is only going to see rows that are covered by a snapshot range in
the SYNC in sl_event.  Rows in sl_log_* might be visible from a
transaction point of view but they won't be captured by the where
conditions for pulling from sl_log.

The snapshot for the event in sl_log is done by the local sync
connection which is a read-write connection not by a slon remote
read-only connection.

(I'm ignoring copy_set from the above analysis).


> 
> "This option is not beneficial for a dump which is intended only for
> disaster recovery. It could be useful for a dump used to load a copy of
> the database for reporting or other read-only load sharing while the
> original database continues to be updated. Without it the dump may
> reflect a state which is not consistent with any serial execution of the
> transactions eventually committed. For example, if batch processing
> techniques are used, a batch may show as closed in the dump without all
> of the items which are in the batch appearing."
> 
> Changing the default isolation levels(s) may therefore change, what a
> replica can safely be used for and I believe that creating reports is
> one of the major use cases. Using options with big, bold, red, flashing
> warnings in the documentation would be the only way to go.
> 
> 
> Regards, Jan
> 
> 
> 
>>
>>>
>>> Tom:-)
>>>
>>>
>>> On 11/18/15, 10:35 AM, "Greg Sabino Mullane"  wrote:
>>>
 On Wed, Nov 18, 2015 at 02:26:15PM +, Tom Tignor wrote:
 ...
> Sorry for the delay getting back. Inspired by your questions, I¹ve been
> reading up on SSI, the Cahill paper and slony1 and postgres code.
 ...

 It should be pointed out that 9.1 goes EOL (End of Life) in less than
 a year (Sep 2016), and transaction handling has changed a *lot* since
 then,
 so any changes that core Slony makes may not even work for you.

 (FWIW, I think dropping the isolation level in this particular
 instance seems safe, however.)

 --
 Greg Sabino Mullane g...@endpoint.com
 End Point Corporation
 PGP Key: 0x14964AC8
>>>
>>> ___
>>> Slony1-general mailing list
>>> Slony1-general@lists.slony.info
>>> http://lists.slony.info/mailman/listinfo/slony1-general
>>>
>>
>> ___
>> Slony1-general mailing list
>> Slony1-general@lists.slony.info
>> http://lists.slony.info/mailman/listinfo/slony1-general
>>
> 
> 

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] remote listener serializability

[Steve Singer]

On 11/16/2015 08:52 AM, Tignor, Tom wrote:
>
> Hello slony1 community,
> I’m part of a team at Akamai working on a notification service based on
> postgres. (We call it an Alert Management System.) We’re at the point
> where we need to scale past the single instance DB and so have been
> working with slony1-2.2.4 (and postgresql-9.1.18) to make that happen.
> Most tests in the past few months have been great, but in recent tests
> the reassuring SYNC-event-output-per-two-seconds suddenly disappeared.
> Throughout the day, it returns for a few minutes (normally less than 5,
> never 10) and then re-enters limbo. Vigorous debugging ensued, and the
> problem was proven to be the serializable isolation level set in
> slon/remote_listen.c. Our recent test environment doesn’t have a
> tremendous write rate (measured in KB/s), but it does have 200-400
> clients at any one time, which may be a factor. Below is the stack shown
> in gdb of the postgres server proc (identified via pg_stat_activity)
> while slon is in limbo.

> What are the thoughts on possible changes to the remote listener
> isolation level and their impact? I’ve tested changes using repeatable
> read instead, and also with serializable but dropping the deferrable
> option. The latter offers little improvement if any, but the former
> seems to return us to healthy replication. In searching around, I found
> Jan W filed Bug 336 last year (link below) which suggests we could relax
> the isolation level here and elsewhere. If it was helpful, I could
> verify an agreed solution and submit it back as a patch. (Not really in
> the slony community yet, just looking at the process now.)
> Thanks in advance,

The last time we had a change to isolation levels was in response to 
this thread


http://lists.slony.info/pipermail/slony1-general/2011-November/011939.html

Also know as bug #255 (http://www.slony.info/bugzilla/show_bug.cgi?id=255)

I can't recall if anyone figured out if we could reduce the remote 
listener isolation level to read committed - read only or not.

One concern at the back of my mind is if a read only repeatable read 
transactions would result in more pivot conflicts than a read only 
serializable deferrable transaction where the conflicts are with a 
serializable transaction running on the origin by some application 
transaction.






>
> http://www.slony.info/bugzilla/show_bug.cgi?id=336
>
>
> (gdb) thread apply all bt
>
>
> Thread 1 (process 13052):
>
> #0  0xe430 in __kernel_vsyscall ()
>
> #1  0xf76d2c0f in semop () from /lib32/libc.so.6
>
> #2  0x08275a26 in PGSemaphoreLock (sema=0xf69d6784, interruptOK=1
> '\001') at pg_sema.c:424
>
> #3  0x082b52cb in ProcWaitForSignal () at proc.c:1443
>
> #4  0x082bb57a in GetSafeSnapshot (origSnapshot=) at
> predicate.c:1520
>
> #5  RegisterSerializableTransaction (snapshot=0x88105a0) at predicate.c:1580
>
> #6  0x083b3f35 in GetTransactionSnapshot () at snapmgr.c:138
>
> #7  0x082c460a in exec_simple_query (
>
>  query_string=0xa87d248 "select ev_origin, ev_seqno, ev_timestamp,
>ev_snapshot,
> \"pg_catalog\".txid_snapshot_xmin(ev_snapshot),
> \"pg_catalog\".txid_snapshot_xmax(ev_snapshot),ev_type,
> ev_data1,"...)
>
>  at postgres.c:948
>
> #8  PostgresMain (argc=1, argv=0xa7cd1e0, dbname=0xa7cd1d0 "ams",
> username=0xa7cd1b8 "ams_slony") at postgres.c:4021
>
> #9  0x08284a58 in BackendRun (port=0xa808118) at postmaster.c:3657
>
> #10 BackendStartup (port=0xa808118) at postmaster.c:3330
>
> #11 ServerLoop () at postmaster.c:1483
>
> #12 0x082854d8 in PostmasterMain (argc=3, argv=0xa7ccb58) at
> postmaster.c:1144
>
> #13 0x080cb430 in main (argc=3, argv=0xa7ccb58) at main.c:210
>
> (gdb)
>
>
>
> Tom:-)
>
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] slony newbie setup trouble

[Steve Singer]

On 11/16/2015 10:32 PM, Armand Pirvu (home) wrote:
> Hi
>
> Please pardon the length of this email but I felt it is best to give a global 
> view
>
> I was looking at slonik for a project.
> All the setup info, tutorials etc etc seems to vary
>
> http://raghavt.blogspot.com/2012/07/simple-slony-i-replication-setup.html
> and
> http://www.linuxjournal.com/article/7834
>

>
>
> /usr/pgsql-9.4/bin/slon_start -c /etc/slony1-94/slon_tools.conf 1  &
> fails to start
>

I think the config file should be to slon config file not a slon_tools.conf

ie the format is

cluster_name=myrep
pid_file='/opt/PostgreSQL/9.1/data/master_slon.pid'
conn_info='host=localhost dbname=master user=postgres port=5432'

http://www.slony.info/documentation/2.2/runtime-config.html

Or see  slon_node.conf.sample which I think is included with the perl tools



> 2015-11-16 20:59:50 CST ERROR  syntax error in file 
> "/etc/slony1-94/slon_tools.conf" line 13, near end of line
> 2015-11-16 20:59:50 CST CONFIG main: slon version 2.2.4 starting up
> usage: /usr/pgsql-9.4/bin/slon [options] clustername conninfo
>
> Options:
>  -hprint usage message and exit
>  -vprint version and exit
>  -dverbosity of logging (1..4)
>  -s  SYNC check interval (default 1)
>  -t  SYNC interval timeout (default 6)
>  -o  desired subscriber SYNC processing time
>  -g   maximum SYNC group size (default 6)
>  -c   how often to vacuum in cleanup cycles
>  -p  slon pid file
>  -f  slon configuration file
>  -a directory to store SYNC archive files
>  -x   program to run after writing archive file
>  -q   Terminate when this node reaches # of SYNCs
>  -r   # of syncs for -q option
>  -l  this node should lag providers by this interval
>
>
> So what gives ? How do I debug, fix , move forward ? Aside the doc which 
> maybe I missed , has an example which yes it works but it not going in all 
> these details, any other examples and so on ?
>
>
> Many thanks
> Armand
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Network connection from slaves to the master

[Steve Singer]

On 11/10/2015 08:49 AM, TOINEL, Ludovic wrote:
> Is there a way to have subscribers with no direct SQL access to the provider ?
>
> The provider write the data on the subscribers directly.
>

To repeat what Stephane said

The slon daemon doesn't need to run on the same server as the replica 
database.

You can put the slon dameons for both the replicas and the orign on the 
origin node (or some other node that can access all nodes).  That way 
the replica database server doesn't need to open network connections 
anywhere.

The slon for the replica (which you are running on the originr or 
somewhere similar) can connect to both the replica and origin database 
servers.




> -Message d'origine-
> De : slony1-general-boun...@lists.slony.info 
> [mailto:slony1-general-boun...@lists.slony.info] De la part de Stéphane 
> Schildknecht
> Envoyé : mardi 10 novembre 2015 14:45
> À : slony1-general@lists.slony.info
> Objet : Re: [Slony1-general] Network connection from slaves to the master
>
> On 10/11/2015 14:03, TOINEL, Ludovic wrote:
>> Thanks Andrew,
>>
>> We are not allowed to have network connection from the slaves to the master 
>> (for security constraints).
>
> You really should think about a VPN between nodes. It would simplify your 
> architecture.
>
> But, in theory, subscriber nodes could be on a DMZ. They can be accessed by 
> daemons, but you don't need them to access providers.
> Your daemons would run on a node that can access every other node.
>
>
> BTW, there are no real master and slaves in Slony. There are nodes, which can 
> be subscribers (receiving modifications readonly), and providers (read/write).
> And you can have a subscriber of a set that is provider of another.
>
>   Only master can communicate with slaves.
>> We need database on slaves with mix replicates tables and read/write tables.
>>
>> The solution could be maybe that solution using a slony master has an Hot 
>> standby of a master protected somewhere ?
>>
>> [slony slaves] <-> [slony master - Standby node] <(log
>> shipping)--|firewall|-- [master protected somewhere]
>>
>> Do you think this solution can work with slony ?
>>
>> Regards,
>>
>> Ludovic Toinel
>>
>> -Message d'origine-
>> De : slony1-general-boun...@lists.slony.info
>> [mailto:slony1-general-boun...@lists.slony.info] De la part de Andrew
>> Sullivan Envoyé : mardi 10 novembre 2015 12:26 À :
>> slony1-general@lists.slony.info Objet : Re: [Slony1-general] Network
>> connection from slaves to the master
>>
>> On Tue, Nov 10, 2015 at 09:51:29AM +, TOINEL, Ludovic wrote:
>>> The network allows only flows from master to slaves.
>>>
>>> Is there any option that I missed to do that ?
>>
>> Not really.  In principle you could do this with the log shipping mode, but 
>> I don't recall whether doing that on the master was not possible or just a 
>> really bad idea.  (You could do this with the built-in standby mechanisms of 
>> Postgres, though.
>>
>> I do wonder why you have it set up this way, however.  Why do you control 
>> the flows this way?
>>
>> A
>>
>> --
>> Andrew Sullivan
>> a...@crankycanuck.ca
>
>
> --
> Stéphane Schildknecht
> Contact régional PostgreSQL pour l'Europe francophone Loxodata - Conseil, 
> expertise et formations
> 06.17.11.37.42
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] adding a replication node instructions update?

[Steve Singer]

On 10/29/2015 03:58 PM, Jaime Casanova wrote:
\
>> namespace into the new replication database.  This causes the subsequent
>> STORE NODE command to fail with a complaint that the _$CLUSTER_NAME
>> schema is already present.
>>
>> I think if you add -N _$CLUSTERNAME to the pg_dump side of the pipeline,
>> then the rest of the directions should complete OK.
>>
>> Is this a bug in the documentation, or have i misunderstood something?
>>
>
> looks like a bug in documentation, yes
>

I've committed a change to the documentation to include a 
--exclude-schema on the pg_dump command in that sample

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Error when adding a table to replication

[Steve Singer]

On 10/28/2015 11:28 AM, CS DBA wrote:

That error seems to be generated when it tries to write data to the 
archive file but when no the file pointer to the archive is null.

It isn't obvious to me looking at the code how this can happen at least 
in the current version of slony.

Which version of slony are you using?

I assume if you restart the slon you get this right away?

The log message 'does not require Slony-I serial key' also looks suspect.




> We're seeing this in the slon logs (we are doing slon archiving as well,
> the slon -a flag):
>
> 2015-10-28 07:54:56 PDTDEBUG1 remoteWorkerThread_1: connected to provider DB
> 2015-10-28 07:54:56 PDTDEBUG2 remoteWorkerThread_1: prepare to copy
> table "abc"."data_set_720"
> 2015-10-28 07:54:56 PDTDEBUG3 remoteWorkerThread_1: table
> "sch1"."data_set_720" does not require Slony-I serial key
> 2015-10-28 07:54:56 PDTDEBUG2 remoteWorkerThread_1: all tables for set
> 14 found on subscriber
> 2015-10-28 07:54:56 PDTDEBUG2 remoteWorkerThread_1: copy table
> "sch1"."data_set_720"
> 2015-10-28 07:54:56 PDTDEBUG3 remoteWorkerThread_1: table
> "sch1"."data_set_720" does not require Slony-I serial key
> 2015-10-28 07:54:56 PDTDEBUG2 remoteWorkerThread_1: Begin COPY of table
> "sch1"."data_set_720"
> 2015-10-28 07:54:56 PDTDEBUG2 remoteWorkerThread_1:  nodeon73 is 0
> NOTICE:  truncate of "sch1"."data_set_720" succeeded
> 2015-10-28 07:54:56 PDTERROR  remoteWorkerThread_1: Cannot write to
> archive file
> /usr/local/pgsql/slon_logs/slony1_log_2_01552202.sql.tmp -
> not open
> 2015-10-28 07:54:56 PDTWARN   remoteWorkerThread_1: data copy for set 14
> failed 1103 times - sleep 60 seconds
> 2015-10-28 07:55:04 PDTDEBUG2 remoteListenThread_1: queue event
> 1,1587129 SYNC
>
>
> The archive dir is writable by postgres and the file perms are:
> ls -l /usr/local/pgsql/slon_logs/slony1_log_2_01552202.sql.tmp
> -rw-r--r-- 1 postgres postgres 504 2015-10-27 13:18
> /usr/local/pgsql/slon_logs/slony1_log_2_01552202.sql.tmp
>
>
> Thoughts?
>
> Thanks in advance
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Error when adding a table to replication

[Steve Singer]

On 10/29/2015 04:01 PM, CS DBA wrote:
> Its an older version, I'll need to check with the client to be sure, if
> we restart the slon daemons then everything works fine, until we add
> another table
>

If you can reproduce this in a 2.2.x version I'll try to see why it 
happens (looking at the code nothing jumps out at me)

If you can only reproduce this in 1.2.x then there is a good chance that 
it has already been fixed.


>
> On 10/29/2015 08:03 AM, Steve Singer wrote:
>> On 10/28/2015 11:28 AM, CS DBA wrote:
>>
>> That error seems to be generated when it tries to write data to the
>> archive file but when no the file pointer to the archive is null.
>>
>> It isn't obvious to me looking at the code how this can happen at
>> least in the current version of slony.
>>
>> Which version of slony are you using?
>>
>> I assume if you restart the slon you get this right away?
>>
>> The log message 'does not require Slony-I serial key' also looks suspect.
>>
>>
>>
>>
>>> We're seeing this in the slon logs (we are doing slon archiving as well,
>>> the slon -a flag):
>>>
>>> 2015-10-28 07:54:56 PDTDEBUG1 remoteWorkerThread_1: connected to
>>> provider DB
>>> 2015-10-28 07:54:56 PDTDEBUG2 remoteWorkerThread_1: prepare to copy
>>> table "abc"."data_set_720"
>>> 2015-10-28 07:54:56 PDTDEBUG3 remoteWorkerThread_1: table
>>> "sch1"."data_set_720" does not require Slony-I serial key
>>> 2015-10-28 07:54:56 PDTDEBUG2 remoteWorkerThread_1: all tables for set
>>> 14 found on subscriber
>>> 2015-10-28 07:54:56 PDTDEBUG2 remoteWorkerThread_1: copy table
>>> "sch1"."data_set_720"
>>> 2015-10-28 07:54:56 PDTDEBUG3 remoteWorkerThread_1: table
>>> "sch1"."data_set_720" does not require Slony-I serial key
>>> 2015-10-28 07:54:56 PDTDEBUG2 remoteWorkerThread_1: Begin COPY of table
>>> "sch1"."data_set_720"
>>> 2015-10-28 07:54:56 PDTDEBUG2 remoteWorkerThread_1:  nodeon73 is 0
>>> NOTICE:  truncate of "sch1"."data_set_720" succeeded
>>> 2015-10-28 07:54:56 PDTERROR  remoteWorkerThread_1: Cannot write to
>>> archive file
>>> /usr/local/pgsql/slon_logs/slony1_log_2_01552202.sql.tmp -
>>> not open
>>> 2015-10-28 07:54:56 PDTWARN   remoteWorkerThread_1: data copy for set 14
>>> failed 1103 times - sleep 60 seconds
>>> 2015-10-28 07:55:04 PDTDEBUG2 remoteListenThread_1: queue event
>>> 1,1587129 SYNC
>>>
>>>
>>> The archive dir is writable by postgres and the file perms are:
>>> ls -l
>>> /usr/local/pgsql/slon_logs/slony1_log_2_01552202.sql.tmp
>>> -rw-r--r-- 1 postgres postgres 504 2015-10-27 13:18
>>> /usr/local/pgsql/slon_logs/slony1_log_2_01552202.sql.tmp
>>>
>>>
>>> Thoughts?
>>>
>>> Thanks in advance
>>>
>>>
>>>
>>> ___
>>> Slony1-general mailing list
>>> Slony1-general@lists.slony.info
>>> http://lists.slony.info/mailman/listinfo/slony1-general
>>>
>>
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] slony and bdr

[Steve Singer]

On 09/30/2015 10:02 AM, Ger Timmens wrote:
> Hi,
>
> To get slony replicating again when using postgresql bdr we'll need
> primary keys added to the slony tables. At least:
>
> ALTER TABLE _slony_cluster.sl_apply_stats ADD PRIMARY KEY (as_origin);
>
> But there are maybe more. Can we add those PK's to the next patch ?
>

sl_confirm
sl_setsync
sl_log_1
sl_log_2
sl_log_script
sl_archive_counter
sl_event_lock
sl_components

are all missing primary keys.


Can anyone think of a reason why these tables shouldn't have a primary key?



> Kind Regards,
>
> P.S. Using postgresql 0.9.4/bdr 0.9.2/slony 2.2.4
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] upgrade slony master / slave using pg_upgrade

[Steve Singer]

On 09/29/2015 05:23 PM, Mark Steben wrote:

As I recall trying to pg_upgrade a slony node encounters two issues

1) Slony uses a catalog datatype (NAME or something like that) in some 
of the slony columns and this caused pg_upgrade to gripe

2) pg_upgrade resets (or at least used to) the xid rollover counter and 
this causes slony to get confused because the full 64bit xids slony uses 
appear to travel backwards.

In theory one could work around both of those issues, I can't say for 
sure if there are other issues.


> Good evening
>
> Fairly simple question (I think)
> Can I use the pg_upgrade contrib module to upgrade from postgres 9.2 to
> 9.4 without having to
> re-define and re-subscribe all of the slony replicated data?  My initial
> pg_upgrade tests are yelling at me that some slony libraries are not
> found in the new 9.4 binaries:
>
> Could not load library "$libdir/slony1_funcs"
> ERROR:  could not access file "$libdir/slony1_funcs": No such file or
> directory
>
> Hopefully it is just and issue of copying the slony binaries from 9.2 to
> the same libraries in 9.4 on master and slave, or is a re-creation
> necessary?  (We are running slony 2.2.3)
>
> I did check on www.slony.info  and didn't find
> any reference to pg_upgrade.
>
> Thanks for your time and insights
>
> --
> *Mark Steben*
>   Database Administrator
> @utoRevenue  | Autobase
> 
> CRM division of Dominion Dealer Solutions
> 95D Ashley Ave.
> West Springfield, MA 01089
> t: 413.327-3045
> f: 413.383-9567
>
> www.fb.com/DominionDealerSolutions
> 
> www.twitter.com/DominionDealer 
> www.drivedominion.com 
>
> 
>
>
>
>
>
> ___
> Slony1-general mailing list
> Slony1-general@lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>

___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Slony replication failed invalid memory alloc request size

[Steve Singer]

On 08/24/2015 09:19 AM, Mike James wrote:
 We have a slony slave in AWS. During an Amazon maintenance, the instance
 was rebooted and now the daemon won't start. The log file has this error
 message:


 2015-08-24 06:50:03 UTC INFO   remoteWorkerThread_1: syncing set 1 with
 102 table(s) from provider 1
 2015-08-24 06:50:33 UTC ERROR  remoteWorkerThread_1_1: error at end of
 COPY IN: ERROR:  invalid memory alloc request size 1970234207
 CONTEXT:  COPY sl_log_1, line 97033:

 I'm not sure whether this is a Postgres error or a slony error. We're
 running Postgres 9.3.5 and slony 2.2.1. Any help much appreciated.


Try upgrading to slony 2.2.4

This MIGHT be bug http://www.slony.info/bugzilla/show_bug.cgi?id=327

Memory corruption bugs sometimes manifest themselves with slightly 
different symptoms.



 Mike
 Clutch Holdings, LLC http://www.clutch.com  Mike James | Manager of
 Infrastructure
 267.419.6400, ext 204 | mike.ja...@clutch.com mailto:mike.ja...@clutch.com
 201 S Maple St. | Suite 250 | Ambler, PA 19002
 Clutch.com http://www.clutch.com | Twitter
 https://twitter.com/clutchsuccess | LinkedIn
 https://www.linkedin.com/company/2837209 | YouTube
 https://www.youtube.com/user/clutchsuccess | Clutch Support Center
 http://clientsupport.clutch.com/

 The only end to end consumer management platform that empowers
 consumer-focused businesses to identify, target, message, and engage
 their best customers.



 ___
 Slony1-general mailing list
 Slony1-general@lists.slony.info
 http://lists.slony.info/mailman/listinfo/slony1-general


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

Re: [Slony1-general] Dropping a node when the node is gone ?

[Steve Singer]

On 08/24/2015 02:20 PM, Dave Cramer wrote:
 Yes, but the node is no longer physically there ??
 Even worse it is a provider for another node


I think you can leave out the admin conninfo for the node being dropped 
and slonik will skip the uinstall step.

(This is from memory,so I might be confused)

You can use the resubscribe command to point the other nodes at a new 
provider.


 Dave Cramer

 On 24 August 2015 at 14:19, Greg Sabino Mullane g...@endpoint.com
 mailto:g...@endpoint.com wrote:

 On Sun, Aug 23, 2015 at 05:56:34PM -0400, Dave Cramer wrote:
  Drop node always wants to connect to the node ??

 Yes: it needs to clean up all the Slony pieces (especially triggers)
 on the tables in the node in question. This is done by having drop
 node call uninstall node.

 --
 Greg Sabino Mullane g...@endpoint.com mailto:g...@endpoint.com
 End Point Corporation
 PGP Key: 0x14964AC8




 ___
 Slony1-general mailing list
 Slony1-general@lists.slony.info
 http://lists.slony.info/mailman/listinfo/slony1-general


___
Slony1-general mailing list
Slony1-general@lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general