Glyn Astill | 20 Nov 17:00 2015

Slony replication lag with moderate quantity of deletes

Using Slony 2.2.4, Pg 9.4.5 I infrequently see replication lag when deleting a moderately large quantity
of records.

I've seen this very infrequently (i.e 3 or so times in a year), but came to a dead end investigating the cause
last time (previous thread here  

I'm hoping someone might be able to nudge me in the right direction this time.

The cause seems to be chunks of deletes on 2 specific tables (within a transaction), although I can't see a
reason why these specific tables cause issues other than the tables having pretty poor primary keys and 
indexes (Table definitions at the bottom).  

Changes to each table are logged by a table_log trigger
(, which we admittedly need to move away from, but I
can't see a reason for this to be an issue here either.

There is activity from other tables, but I'm pretty certain it's something to do with deletes from these
specific tables, although I can't reliably reproduce the behaviour.

In this case deleting around 100,000 records from each table on the origin (taking around 68 seconds),
causes the remote worker thread on all the subscribers to take approximately 90 minutes copying into it's
sl_log table. E.g:

2015-11-20 12:28:21 GMT [9843]: [5-1]
app=slon.remoteWorkerThread_7,user=slony,db=db,host= LOG:  duration: 6096341.085
ms  statement: COPY "_replication"."sl_log_2" ( log_origin,
log_txid,log_tableid,log_actionseq,log_tablenspname, log_tablerelname, log_cmdtype,
log_cmdupdncols,log_cmdargs) FROM STDIN

(Continue reading)

Armand Pirvu (home | 17 Nov 04:32 2015

slony newbie setup trouble


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

Just to name two sources of study

So I used two approaches

1 - Passing commands to slonik directly. All ok 
2 - Using the provided perl scripts . Not ok and this is where I would like some tips , guidance

master node:
master db: primdb

slave node:
slave db: bkdb

I use .pgpass 


I stripped out all comments to keep things to a minimum

(Continue reading)

Tignor, Tom | 16 Nov 14:52 2015

remote listener serializability

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,

(gdb) thread apply all bt

Thread 1 (process 13052):

#0  0xffffe430 in __kernel_vsyscall ()

#1  0xf76d2c0f in semop () from /lib32/

#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=<optimized out>) 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


Tom    :-)

Slony1-general mailing list
TOINEL, Ludovic | 10 Nov 10:51 2015

Network connection from slaves to the master



I would like to use Slony without any network connection from the slaves to the master.

The network allows only flows from master to slaves.


Is there any option that I missed to do that ?


Thank-you in advance.




Ludovic Toinel

Slony1-general mailing list
CS DBA | 28 Oct 16:28 2015

Error when adding a table to replication

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 
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 
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_00000000000001552202.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_00000000000001552202.sql.tmp
-rw-r--r-- 1 postgres postgres 504 2015-10-27 13:18 


Thanks in advance
Daniel Kahn Gillmor | 11 Oct 21:58 2015

adding a replication node instructions update?

hey slony people--


> 3.1.4. Adding a Replication Node
> To add a node to the replication cluster you should
>     Create a database for the node and install your application schema in it.
>     createdb -h $NEWSLAVE_HOST $SLAVEDB
>     pg_dump -h $MASTER_HOST -s $MASTERDB | psql -h $NEWSLAVE_HOST $SLAVEDB

however, this step seems like it's not quite right.

in particular, this step appears to copy the master's replication
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?


Sung Hsin Lei | 10 Oct 05:34 2015

Slony Replication Startup Speed


I have a 10gig database on the Master which I backed up and restored on the Slave. Database activity is low. When I setup Slony replication from the Master to the Slave, it would take hours before new information from the Master would be updated into the Slave. Postgres would be also VERY slow for the first few hours. However, after the initial wait period, the Slave would update within a second or so and Postgres speed would return to normal. I'm assuming that Slony is taking time to verify the initial database integrity between the Master and the Slave. Am i right? I would like to know if there's a way to have the updates start within the first few minutes of the original setup.

Slony1-general mailing list
Ger Timmens | 30 Sep 16:02 2015

slony and bdr


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 ?

Kind Regards,

P.S. Using postgresql 0.9.4/bdr 0.9.2/slony 2.2.4
Mark Steben | 29 Sep 23:23 2015

upgrade slony master / slave using pg_upgrade

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 and didn't find any reference to pg_upgrade.

Thanks for your time and insights

Mark Steben
 Database Administrator
<at> utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089

t: 413.327-3045
f: 413.383-9567

Slony1-general mailing list
Mike James | 24 Aug 15:19 2015

Slony replication failed invalid memory alloc request size

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.

  Mike James | Manager of Infrastructure
267.419.6400, ext 204 | mike.james-8f6iZ/
201 S Maple St. | Suite 250 | Ambler, PA 19002 | 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
Dave Cramer | 23 Aug 23:56 2015

Dropping a node when the node is gone ?

Drop node always wants to connect to the node ??

Dave Cramer
Slony1-general mailing list