master to multiple slaves replication system with cascading and failover

headers Mikko Partio | 1 Aug 2007 06:05

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

Benezra, Eric a écrit :
>
> I am currently trying to use Slony on Windows XP. It is installed and
> scripts have been created and modified appropriately. When I try to
> run the Slony configuration script (slonik slonyconfigure.txt), I get
> the following error messages:
>
> slonyconfigure.txt:7 could not open file
> /usr/local/pgsql/share/xxid.v81.sql
> slonyconfigure.txt:7 ERROR: no admin conninfo for node 4017728
>
Is the file readable ?
What's more, I don't think windows can understand such a path.

My 2 cents

SAS

"Mikko Partio" <mpartio@...> writes:
> On 7/29/07, Jan Wieck <[[JanWieck@...]]> wrote:
>
>           On 7/24/2007 12:29 AM, Mikko Partio wrote:
>      >
>      >     What I meant is that it'd be good to get enough detail information about
>      >     the specific actions that lead to this problem in order to create a
>      >     standalone test that can reproduce it.
>      >
>      >
>      > Ok here comes:
>      
>      This one played quite a bit hide and seek with me. But I think I found
>      and fixed the bug.
>      
>      The problem occurred when some action (like STORE_SET) caused the
>      subscriber slon to perform an internal restart. During that, the current
>      setsync tracking in the pset structure is reinitialized. However, since
>      the sl_setsync table is not updated on events other than SYNC, this
>      would lead to the wrong (too low) old sync expected if the last event(s)
>      processed from that node where no SYNC events.
>      
>      Thanks for the detailed example.
>      
>
>
> Great that you got it fixed! Will the patch be included in the 1.2.11 release?
> Regards
> MP

Yes, it's in what will be 1.2.11.

I have not been able to get the log shipping test in the
tests/testlogship directory in the code base to exercise the problem.
Now, the patch is applied in CVS, so that either a checkout of the 1.2
branch, or application of the patch to a copy of source that you may
have would allow you to deploy it for testing, at least.

I'd be more than happy to make a tarball available for you, if that
would help you run a test to verify that this fixes the problem for
you.
--

-- 
output = reverse("ofni.sesabatadxunil" " <at> " "enworbbc")
http://linuxdatabases.info/info/wp.html
"Consistency requires you to be as  ignorant today as  you were a year
ago."  -- Bernard Berenson

Hi,

We are using Slony on a production environment and are very pleased by it.

Our cluster is made of 1 master, 4 slaves that needs to be replicated
fast, and 2 slaves for which the replication speed isn't a problem.

Here's our issue: In the sl_status view I notice that the st_lag_time is
always between 1 and many seconds: it goes up to 10 seconds regularly,
and approximatively one time a day, there is always a slave reaching 1
min, for example while vacuuming.

I tried playing with the folllowing options:
     -s <milliseconds>     SYNC check interval (default 10000)
     -t <milliseconds>     SYNC interval timeout (default 60000)
     -o <milliseconds>     desired subscriber SYNC processing time
     -g <num>              maximum SYNC group size (default 6)

Now on the master I have:
-s 1000 -g 50
On the fast slaves I have:
-s 1000
And on the slow slaves:
-s 10000 -g 10

I tried lowering the SYNC check interval to 500ms with no real effect,
and the master is already loaded enough anyway ;)

Is there an effective way to shorten the replication lag time ?

A Slony noob.

--

-- 
Laurent Raufaste
JFG Networks
<http://www.over-blog.com/>

_______________________________________________
Slony1-general mailing list
Slony1-general@...
http://gborg.postgresql.org/mailman/listinfo/slony1-general

Hi folks,

In an effort to upgrade a cluster from slony1-1.2.6 and postgresql-8.1.8, I 
dumped the DB, upgraded to postgresql-8.2.4, comiled and installed 
slony1-1.2.10 and then tried to restore the DB on the master.  The restore 
crashed the backend every time when trying to restore our slony_test table. So 
I opted to go a different route and installed 8.1.9, moved the old data 
directory in, dropped the slony schema cascade and dumped the db.  Then I 
restored that into 8.2.4 successfully.  After upgrading the slave to 8.2.4 and 
1.2.10, I tried to subscribe the slave, but the slonik command errored out and 
left the following in the postgresql log:

Jul 29 15:48:49 testdb1 postgres[1173]: [1-1] ERROR:  could not access file
"$libdir/xxid": No such file or directory
Jul 29 15:48:49 testdb1 postgres[1173]: [1-2] STATEMENT:  load
'$libdir/xxid';

This is what slonik complained about:

<stdin>:17: PGRES_FATAL_ERROR load '$libdir/xxid'; - ERROR: incompatible 
library "/usr/lib64/pgsql/xxid.so": missing magic block

Except, I don't know why it's looking in /usr/lib64/pgsql because this is a 32 
bit CentOS 4.3 machine.  I double checked that the i686 versions of postgres 
are installed, slony was compiled from source against /usr/bin/pg_config which 
correctly outputs that libdir is /usr/lib/pgsql:
PKGLIBDIR = /usr/lib/pgsql

What am I missing?  Is there a special procedure for using Postgresql-8.2.4?

It yielded the same results with slony 1.2.8 and 1.2.9.  But, 1.2.10 is working 
fine with postgresql-8.1.9 which we reverted to.

--

-- 
Jeff Frost, Owner 	<jeff@...>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954

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

I have run what-soon-should-be-1.2.11 against PostgreSQL 7.4, 8.0, 8.1, 
8.2, and a (sadly, aging!) 8.3 (probably a few weeks old ).  All 
looking good, so far.

I'll see about getting a tarball put together to generate a "pre-1.2.11" 
so that Mikko Partio and others can do any further testing that is 
desired before the release.

Laurent Raufaste wrote:
> Hi,
>
> We are using Slony on a production environment and are very pleased by 
> it.
>
> Our cluster is made of 1 master, 4 slaves that needs to be replicated
> fast, and 2 slaves for which the replication speed isn't a problem.
>
> Here's our issue: In the sl_status view I notice that the st_lag_time is
> always between 1 and many seconds: it goes up to 10 seconds regularly,
> and approximatively one time a day, there is always a slave reaching 1
> min, for example while vacuuming.
>
If the problem is essentially that the master is overloaded, then there 
isn't any configuration change likely to help.  There is no guaranteeing 
that subscribers will be Right Up To Date.
> I tried playing with the folllowing options:
>     -s <milliseconds>     SYNC check interval (default 10000)
>     -t <milliseconds>     SYNC interval timeout (default 60000)
>     -o <milliseconds>     desired subscriber SYNC processing time
>     -g <num>              maximum SYNC group size (default 6)
>
> Now on the master I have:
> -s 1000 -g 50
> On the fast slaves I have:
> -s 1000
> And on the slow slaves:
> -s 10000 -g 10
>
> I tried lowering the SYNC check interval to 500ms with no real effect,
> and the master is already loaded enough anyway ;)
There are seeming misconceptions in how you're configuring it...

- On the origin node, the "-g" option is pretty much irrelevant.  -g 
affects how *subscribers* group together SYNCs into groups; since the 
origin does not apply any SYNCs coming from subscribers, it is 
irrelevant to do any grouping there.

- On the other hand, the main node where the "-s" (and -t) options have 
meaningful effect is on an origin node, as that is the option which 
controls how often SYNCs are generated.  (SYNCs get generated on 
non-origin nodes, but those SYNCs don't lead to any replication work 
being done, so they're very uninteresting.)  Thus, setting "-s 1000" 
versus "-s 10000" on subscriber nodes is pretty much irrelevant.
> Is there an effective way to shorten the replication lag time ?
>
> A Slony noob.
>
Speed is an "emergent property," falling from how much work is being 
thrown at the system (e.g. - what are you doing to overload it) and how 
much hardware there is to do replication work.

Tuning the DBMS, tuning OS, tuning hardware, and such, will have some 
effect.  In the long run, though, the main way is to get faster network 
and disk hardware...

On 8/1/07, Jeff Frost <jeff@...> wrote:
> <stdin>:17: PGRES_FATAL_ERROR load '$libdir/xxid'; - ERROR: incompatible
> library "/usr/lib64/pgsql/xxid.so": missing magic block
>
> Except, I don't know why it's looking in /usr/lib64/pgsql because this is a 32
> bit CentOS 4.3 machine.  I double checked that the i686 versions of postgres
> are installed, slony was compiled from source against /usr/bin/pg_config which
> correctly outputs that libdir is /usr/lib/pgsql:
> PKGLIBDIR = /usr/lib/pgsql
>
> What am I missing?  Is there a special procedure for using Postgresql-8.2.4?
>
> It yielded the same results with slony 1.2.8 and 1.2.9.  But, 1.2.10 is working
> fine with postgresql-8.1.9 which we reverted to.

Weird. I haven't seen that before, but I haven't been using slony for
that long, either. I have multiple servers running a combination of
CentOS 4.5 (32bit & 64bit), CentOS 5 and Fedora Core 6 using
PostgreSQL 8.2.4 and Slony 1.2.10.

I've also upgraded PostgreSQL from a previous 8.2.x version as well as
Slony from a previous 1.2.x version on them without any issues.

When you upgrade, are you recompiling/reinstalling both Postgres and
also Slony against the upgraded Postgres? I suspect that you may be
missing the Slony modules in your Postgres install.

When I upgrade I put Postgres / Slony into a unique directory for each
build (for example /usr/local/pgsql-8.2.4-2007080101 and
/usr/local/slony-1.2.10-2007080101) and then symlink that folder to
the same folder name without the version so I don't have to bother
updating paths and startup scripts.

When updating a minor version of pgsql I haven't had to
recompile/reinstall slony as long as I remember to copy the slony
modules over to the new pgsql directory.

I also notice that you're not fully updated with CentOS, is there a
reason you're not fully updated to CentOS 4.5? I doubt this has
anything to do with your issue, but it can't hurt.

-Dave

Mon	Tue	Wed	Thu	Fri	Sat	Sun

		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

12	May 2013
13	April 2013
17	March 2013
12	February 2013
3	January 2013
25	December 2012
56	November 2012
35	October 2012
35	September 2012
19	August 2012
13	July 2012
67	June 2012
27	May 2012
29	April 2012
46	March 2012
58	February 2012
29	January 2012
41	December 2011
63	November 2011
28	October 2011
53	September 2011
50	August 2011
43	July 2011
43	June 2011
48	May 2011
64	April 2011
25	March 2011
51	February 2011
61	January 2011
60	December 2010
153	November 2010
61	October 2010
66	September 2010
94	August 2010
72	July 2010
130	June 2010
156	May 2010
138	April 2010
80	March 2010
75	February 2010
60	January 2010
78	December 2009
63	November 2009
77	October 2009
136	September 2009
177	August 2009
203	July 2009
74	June 2009
69	May 2009
101	April 2009
61	March 2009
49	February 2009
92	January 2009
65	December 2008
70	November 2008
103	October 2008
180	September 2008
173	August 2008
140	July 2008
221	June 2008
140	May 2008
123	April 2008
115	March 2008
289	February 2008
150	January 2008
190	December 2007
146	November 2007
145	October 2007
205	September 2007
189	August 2007
177	July 2007
221	June 2007
130	May 2007
141	April 2007
222	March 2007
191	February 2007
156	January 2007
167	December 2006
219	November 2006
198	October 2006
218	September 2006
103	August 2006
152	July 2006
148	June 2006
132	May 2006
144	April 2006
214	March 2006
166	February 2006
168	January 2006
167	December 2005
153	November 2005
189	October 2005
240	September 2005
182	August 2005
215	July 2005
203	June 2005
191	May 2005
111	April 2005
71	March 2005
2	January 2005

Re: Re: log shipping gone wrong

Re: Help with Slony- please read!

Re: Re: log shipping gone wrong

Slony lag times

(no subject)

problems with slony1-1.2.10 and postgresql-8.2.4

Re: Re: log shipping gone wrong

Testing on 1.2.11 - So Far So Good

Re: Slony lag times

Re: problems with slony1-1.2.10 and postgresql-8.2.4