Slony1 PostgreSQL Replication - Slony upgrade + admin advice needed (retry)

Author

Slony upgrade + admin advice needed (retry)

John Sidney-Woollett

2005-08-11, 1:28 pm

Apologies for sending this again, but I don't think that it made it to
the list first time as I wasn't subscribed...

I'm using Postgres 7.4.6 and slony 1.0.5, and I want to upgrade to a
newer slony - should I be using 1.0.6 or 1.1.0?

What are the differences between them? We've had a few issues with
switching over using 1.0.5 and I'd like to use a new version if
possible. Also, defining listening paths with more than two nodes is a
total pain... I think this problem goes away in 1.1.1?

Here's a little background...

This morning our main database (master node, #1) ran out of disk space.
We have two slaves (nodes #2 and #3) in the same cluster.

We restarted the pg db at node #1 (pg had shut itself down), and issued
a move set command to make node #2 the new master - the move looked like
it worked OK. We had all clients disconnected before we did this. When
we restarted our application against node #2, *some* (not sure how many)
tables were still locked (most were not). We got this error, for example
in our webapp logs:

org.postgresql.util.PSQLException: ERROR: Slony-I: Table wsexchrate is
replicated and cannot be modified on a subscriber node

Because we were being rushed to get node #2 online, we used a slonik
uninstall script to remove the replication cluster altogether. This
seems to have worked and released all the tables in node 2, which has
become our live database.

Now that things are a little calmer, we want to recreate a clean (schema
only) node #1, define a new relication cluster and start relicating from
node #2 (master) to node #1 (slave) to rebuild the data there.

Once that is done, we want to make node #1 the master again, and node #2
the slave. We are retiring node #3 so that's not a factor anymore.

We have lots of cron scripts that run against the master database and we
want to get node #1 up as the master as soon as poss, rather than
recreating the same scripts on node #2.

Questions:

1) We want the lastest version of Slony - should we use 1.0.6 or 1.1.0
for pg 7.4.x?

2) If we can use either, which is easier to administer and setup?

3) After we restart the replication how can we tell whether node #1 has
caught up with node #2?

4) What do we need to do to get a clean switch over using move set when
making node #1 the master again? Does our web application have to be
disconnected from the db?

Thanks for any help, anyone.

John

Brad Nicholson

2005-08-11, 8:25 pm

John Sidney-Woollett wrote:

> Questions:
>
> 1) We want the lastest version of Slony - should we use 1.0.6 or 1.1.0
> for pg 7.4.x?
>
1.0.6 contains some bug fixes, you can get details here:
http://developer.postgresql.org/~wi...HISTORY-1.0.txt

1.1 adds new features, such as log shipping.

> 2) If we can use either, which is easier to administer and setup?
>
1.1 is easier. The altperl admin scripts have been cleaned up a lot,
slonik supports define/include, and the listen network is automatically
generated for you.

> 3) After we restart the replication how can we tell whether node #1 has
> caught up with node #2?
>
Have a look at _clustername.sl_status on node #1

> 4) What do we need to do to get a clean switch over using move set when
> making node #1 the master again? Does our web application have to be
> disconnected from the db?
>

Move set will lock the set, which involves exclusive locks on all tables
in the set - it may prove difficult to lock the set on a busy site. I
recommend doing this during an outage. Have a look at the MOVE SET
section in the admin guide:
http://linuxfinances.info/info/stmtmoveset.html

Also, important to note - one of the bugs in 1.0.5 that was fixed in
1.0.6 relates to potential data loss with move set.

> Thanks for any help, anyone.
>
> John
> ____________________
____________________
_______
> Slony1-general mailing list
> Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
> http://gborg.postgresql.org/mailman.../slony1-general

--
Brad Nicholson 416-673-4106 bnichols-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org
Database Administrator, Afilias Canada Corp.

John Sidney-Woollett

2005-08-11, 8:25 pm

Brad

Thanks for the reply and the information.

I'm going to have a try using 1.0.6 since that should be similar to our
1.0.5 config, and at the moment, we need to get the cluster up and running.

When we have more time later I'll take a look at 1.1.

John

Brad Nicholson wrote:
> John Sidney-Woollett wrote:
>
> 1.0.6 contains some bug fixes, you can get details here:
> http://developer.postgresql.org/~wi...HISTORY-1.0.txt
>
>
> 1.1 adds new features, such as log shipping.
>
> 1.1 is easier. The altperl admin scripts have been cleaned up a lot,
> slonik supports define/include, and the listen network is automatically
> generated for you.
>
> Have a look at _clustername.sl_status on node #1
>
>
> Move set will lock the set, which involves exclusive locks on all tables
> in the set - it may prove difficult to lock the set on a busy site. I
> recommend doing this during an outage. Have a look at the MOVE SET
> section in the admin guide:
> http://linuxfinances.info/info/stmtmoveset.html
>
> Also, important to note - one of the bugs in 1.0.5 that was fixed in
> 1.0.6 relates to potential data loss with move set.
>
>
>
>
>

Sponsored Links