On 02/14/14 15:26, Tory M Blue wrote:
>
>
>
> On Fri, Feb 14, 2014 at 10:35 AM, Vick Khera <vivek at khera.org
> <mailto:vivek at khera.org>> wrote:
>
>
>     On Thu, Feb 13, 2014 at 5:15 PM, Tory M Blue <tmblue at gmail.com
>     <mailto:tmblue at gmail.com>> wrote:
>
>         so I'm wondering how do I change my scripts to create the
>         index's at the end? I obviously can't drop the index on the
>         master or everything will go to heck in a hand basket, but is
>         there an instruction set when I'm adding a node and adding sets
>         to that node, to tell it to ignore the indexes?
>
>
>     I'm pretty sure slony disables indexes while copying, then re-builds
>     them after it is done.
>
>
>
> Actually I believe this is beyond slony. The schema tells postgres what
> to do, so if it's creating indexes when the table is created, seems like
> everything pauses / waits for the index to be done, before moving on to
> the next table copy.
>
> So I think editing the schema to do the indexes at the end or not at all
> (make it manual), one should be able to use that schema on a remote slon
> host, that host will not pause to create indexes, and allow slony to
> push the data without a significant pause (some of our indexes have
> taken 4+ hours to create).

If you do a SUBSCRIBE SET without specifying OMIT COPY = yes, then Slony 
is doing:

     truncate the table
     turn off indexes (mucking with system catalog here)
     copy table
     turn indexes back on
     reindex table

All this is done within one transaction for all tables in the set, so in 
this case "mucking with system catalog" is safe, because it will never 
be visible to any concurrent session.

You could drop all indexes (and the primary key) from all the tables on 
the subscriber, do the subscribe, then kill the slon (because without 
the PK it won't be able to do much in terms of catching up), recreate PK 
and indexes, then let it catch up.

What I would rather suggest is that you enable TCP keepalives and 
configure them in the slon config so that your firewall/NAT-gateway or 
whatever is reaping the idle connections is kept from doing so.

>
> I'm going to test this anyways, I don't see anything particular to slon
> to stop this behaviour

TCP keepalives should do that, as a side effect.


Regards,
Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info