server crash on slave: slon-start to do catch-up VS re-subscribe

Hi – we are running postgres 8.3.11 and two weeks ago started slony 2.0.5 on
a VM slave, and a standalone
Master.  The database approaches 200 GB in size and the subscription process
took about 16 hours
Success!  Unforturately the NEXT day the underlying server on the vm slave
experienced a 
Hardware memory crash and we lost connectivity. 

We now have the hardware crash resolved and wish 
to restart slony this weekend. 
I have verified that there have been no schema changes.
 Should I:
  1. issue a ./slon_start and allow slony to catch-up two weeks worth of 
     updates
        Or
  2. Punt, start from scratch, redefine everything and rerun the 16 hour
     subscription process?

Master config:

Linux ar-db-psql-1desq 2.6.18-194.11.3.el5 #1 SMP Mon Aug 30 16:19:16 EDT
2010 x86_64 x86_64 x86_64 GNU/Linux

Slave config:

Linux ar-db3-phys 2.6.18-194.26.1.el5 #1 SMP Tue Nov 9 12:54:20 EST 2010
x86_64 x86_64 x86_64 GNU/Linux

Thank you,

Re: server crash on slave: slon-start to do catch-up VS re-subscribe

On Wed, Dec 1, 2010 at 9:33 AM, Mark Steben <msteben@...> wrote:
>  Should I:
>  1. issue a ./slon_start and allow slony to catch-up two weeks worth of
>     updates
>        Or
>  2. Punt, start from scratch, redefine everything and rerun the 16 hour
>     subscription process?
>

It all depends on the rate of change in your database. Do you do a lot
of insert/update/deletes to the DB?  Take a look at the number of rows
in the sl_log_1 and sl_log_2 tables in your replication schema on the
master.  If the number is very very high, say in the tens or hundreds
of milliions, then perhaps restarting from scratch may be helpful.  If
it is in the low tens of millions, I'd venture to day you could
recover by just restarting slony.  One optimization may be to drop all
indexes (except the PK index) on the replica until it is caught up.
This will reduce the I/O it needs to apply the changes.

Purging Slony

Hello There,

What is an official way for purging Slony
schema/columns/functions/whatever from a database?

Can I use slonik_* scripts for that task? What script order should be?
slonik_unsubscribe_set, slonik_drop_set, slonik_uninstall_nodes, drop
schema _replication?

My best regards,
Pawel Tecza

Re: Purging Slony

you want to use the uninstall node slonik function.  i usually do drop
node for the replicas first.  not sure if that is necessary unless you
want o preserve those nodes.

Re: Purging Slony

On Wed, 2010-12-01 at 16:27 +0100, Paweł Tęcza wrote:
> 
> What is an official way for purging Slony
> schema/columns/functions/whatever from a database?
> 
> Can I use slonik_* scripts for that task? What script order should be?
> slonik_unsubscribe_set, slonik_drop_set, slonik_uninstall_nodes, drop
> schema _replication? 

After killing slons, slonik_uninstall_nodes should be sufficient.
--

-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

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

Re: Purging Slony

Dnia 2010-12-01, śro o godzinie 17:30 +0200, Devrim GÜNDÜZ pisze: 
> On Wed, 2010-12-01 at 16:27 +0100, Paweł Tęcza wrote:
> > 
> > What is an official way for purging Slony
> > schema/columns/functions/whatever from a database?
> > 
> > Can I use slonik_* scripts for that task? What script order should be?
> > slonik_unsubscribe_set, slonik_drop_set, slonik_uninstall_nodes, drop
> > schema _replication? 
> 
> After killing slons, slonik_uninstall_nodes should be sufficient.

Vick and Devrim,

Thanks a lot for your rapid reply! :D

Before sending my post here I was trying to purge Slony in a following
way:

myDB=# drop schema _replication cascade;
NOTICE:  drop cascades to 274 other objects
DETAIL:  drop cascades to type _replication.xxid
[...]
drop cascades to function _replication.denyaccess()
drop cascades to function _replication.lockedset()
and 174 other objects (see server log for list)
DROP SCHEMA

# slonik_unsubscribe_set 1 10 |slonik
<stdin>:13: unsubscribed node 10 from set 1

Re: Purging Slony

On 12/1/2010 11:03 AM, Paweł Tęcza wrote:
> Dnia 2010-12-01, śro o godzinie 17:30 +0200, Devrim GÜNDÜZ pisze:
>>  On Wed, 2010-12-01 at 16:27 +0100, Paweł Tęcza wrote:
>>  >
>>  >  What is an official way for purging Slony
>>  >  schema/columns/functions/whatever from a database?
>>  >
>>  >  Can I use slonik_* scripts for that task? What script order should be?
>>  >  slonik_unsubscribe_set, slonik_drop_set, slonik_uninstall_nodes, drop
>>  >  schema _replication?
>>
>>  After killing slons, slonik_uninstall_nodes should be sufficient.
>
> Vick and Devrim,
>
> Thanks a lot for your rapid reply! :D
>
> Before sending my post here I was trying to purge Slony in a following
> way:
>
> myDB=# drop schema _replication cascade;
> NOTICE:  drop cascades to 274 other objects
> DETAIL:  drop cascades to type _replication.xxid
> [...]
> drop cascades to function _replication.denyaccess()
> drop cascades to function _replication.lockedset()
> and 174 other objects (see server log for list)
> DROP SCHEMA
>
> # slonik_unsubscribe_set 1 10 |slonik

Re: server crash on slave: slon-start to do catch-up VS re-subscribe

Vick Khera <vivek <at> khera.org> writes:
> On Wed, Dec 1, 2010 at 9:33 AM, Mark Steben <msteben <at> autorevenue.com> wrote:
>>  Should I:
>>  1. issue a ./slon_start and allow slony to catch-up two weeks worth of
>>     updates
>>        Or
>>  2. Punt, start from scratch, redefine everything and rerun the 16 hour
>>     subscription process?
>
> It all depends on the rate of change in your database. Do you do a lot
> of insert/update/deletes to the DB?  Take a look at the number of rows
> in the sl_log_1 and sl_log_2 tables in your replication schema on the
> master.  If the number is very very high, say in the tens or hundreds
> of milliions, then perhaps restarting from scratch may be helpful.  If
> it is in the low tens of millions, I'd venture to day you could
> recover by just restarting slony.  One optimization may be to drop all
> indexes (except the PK index) on the replica until it is caught up.
> This will reduce the I/O it needs to apply the changes.

It's uncertain what the bottleneck will be; that may well depend on
local characteristics, such that it may be a mistaken assumption to
assume that saving on index writes is material.

I think something would be learned by simply letting Slony catch up.
There are some interesting open questions as to the pathologies when
there's truly a lot of data in sl_log_1/2.

I don't imagine it would take too terribly long to figure out if things
are catching up, between:

Re: Purging Slony

Dnia 2010-12-01, śro o godzinie 13:13 -0500, Jan Wieck pisze: 
> On 12/1/2010 11:03 AM, Paweł Tęcza wrote:

> > Probably it was my fault that I dropped the schema before dropping set
> > "set1".
> 
> That was definitely a mistake. Never do that unless you are on Slony 2.0.x.
> 
> For subscribers under version < 2.0 there is the possibility to be left 
> with a corrupted system catalog that is rather tricky (and risky) to 
> recover from. This will be the case if your schema has any user defined 
> triggers or foreign keys.
> 
> The master database should be fine regardless.

Hello Jan,

Thanks for your comment!

I use Slony 1.2.20 under Ubuntu 10.04 LTS. Next time I'll try to use
only slonik_uninstall_nodes script, according to the advice of Vick and
Devrim :)

Best regards,
Pawel

_______________________________________________
Slony1-general mailing list
Slony1-general <at> lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general

"multi step" replication?

hi,

is it possible for a node to be subscriber and origin for the same set 
at the same time so it "forwards" changes to its subscribers?

in 
http://scanningpages.wordpress.com/2010/10/09/9-0-streaming-replication-vs-slony/ 
it says slony is usable when "I want to replicate from A==>B and then 
have B replicate to C and D?". but does this mean "at the same time"? i 
couldn't find this in the documentation

i'm looking for a solution like this to reduce the number of slon 
daemons (currently 11) on my origin server. are there other solutions 
when you have "a lot of" nodes?

i'm using slony 1.2.15

thx
matthias