Slony1 PostgreSQL Replication

Author

Slony Question

Antonio Escudero

2006-02-17, 7:26 am

1. I know now that slony is working, but i still want to know on how longwill it take to finish replicating a large database?
2. I used ps -aux to look at processing on my system.
then i got this

USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 24723 0.0 0.9 899824 59564 ? S Feb14 0:12 postgres: postgres database_name 66.94.234.13(47439) idle
postgres 24728 5.7 13.6 903412 837340 ? D Feb14 169:39 postgres: postgres database_name 66.94.234.13(47440) COPY
postgres 24729 0.0 0.9 899016 55732 ? S Feb14 0:25 postgres: postgres database_name 66.94.234.13(47441) idle
postgres 24730 0.0 4.5 900136 277144 ? S Feb14 0:36 postgres: postgres database_name 66.94.234.13(47442) idle

does this means that it is still replicating the database?
what does Feb14 169:39 postgres: postgres database_name 66.94.234.13(47440) COPY means?

3. I tried to query some tables in the slave database. Unfortunately, the tables are locked. How will i know that the data are being copied or appended to the slave database?

4. Does this query tell that slony is appending the slave db (db_name)?
postgres=# select * from pg_stat_database;
datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit
-----------+-----------+-------------+-------------+---------------+-----------+----------
1 | template1 | 0 | 100 | 12 | 0 | 0
17229 | template0 | 0 | 0 | 0 | 0 | 0
675239438 | db_name | 300 | 40965 | 14011 | 0 | 0
(3 rows)

5 In my slony log file, i got the following now:

DEBUG2 remoteListenThread_1
: queue event 1,1471 SYNC
DEBUG2 remoteListenThread_1
: queue event 1,1472 SYNC
DEBUG2 syncThread: new sl_action_seq 1 - SYNC 222
DEBUG2 remoteListenThread_1
: queue event 1,1473 SYNC
DEBUG2 localListenThread: Received event 2,222 SYNC
DEBUG2 remoteListenThread_1
: queue event 1,1474 SYNC
DEBUG2 remoteListenThread_1
: queue event 1,1475 SYNC
DEBUG2 remoteListenThread_1
: queue event 1,1476 SYNC
Tue Feb 14 14:11:01 PST 2006
DEBUG2 remoteListenThread_1
: queue event 1,1477 SYNC

Thanks for the help

---------------------------------
Yahoo! Autos. Looking for a sweet ride? Get pricing, reviews, & more on new and used cars.

Ujwal S. Setlur

2006-02-17, 7:26 am

--- Antonio Escudero <aescudero- ur4TIblo6goN+BqQ9rBE
Ug@public.gmane.org> wrote:

>
> 1. I know now that slony is working, but i still
> want to know on how long will it take to finish
> replicating a large database?
>

Check the _<cluster_name>.sl_status view on the origin
of the subscribed set. I generally look at the
num_lag_events field. Once the subscriber has caught
up, that number should be close to zero.

Ujwal

____________________
____________________
__________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

lkv

2006-02-17, 7:26 am

Ujwal S. Setlur wrote:
>
> --- Antonio Escudero <aescudero- ur4TIblo6goN+BqQ9rBE
Ug@public.gmane.org> wrote:
>
>
> Check the _<cluster_name>.sl_status view on the origin
> of the subscribed set. I generally look at the
> num_lag_events field. Once the subscriber has caught
> up, that number should be close to zero.

being on topic, is there a way to verify that replication is
consistent on 2 nodes. i had a problem today with inconsistencies in a
replica - there were no errors in the log files, but certain records
were missing on one of the slaves. afaik there was no network
disconnection.

cheers,
l

Christopher Browne

2006-02-17, 11:24 am

Antonio Escudero <aescudero- ur4TIblo6goN+BqQ9rBE
Ug@public.gmane.org> writes:

> 1. I know now that slony is working, but i still want to know on how long=
will it take to finish replicating a large database?
> 2. I used ps -aux to look at processing on my system.
> then i got this
> USER=A0=A0=A0=A0=A0=
A0 PID %CPU %MEM=A0=A0 VSZ=A0 RSS TTY=A0=A0=A0=A0=A0 =
STAT START=A0=A0 TIME COMMAND
> postgres 24723=A0 0.0=A0 0.9 899824 59564 ?=A0=A0=A0=A0=A0 S=A0=A0=A0 Feb=
14=A0=A0 0:12 postgres: postgres database_name 66.94.234.13(47439) idle=A0=
=A0=A0=A0=A0 =

> postgres 24728=A0 5.7 13.6 903412 837340 ?=A0=A0=A0=A0 D=A0=A0=A0 Feb14 1=
69:39 postgres: postgres database_name 66.94.234.13(47440) COPY=A0=A0=A0=A0=
=A0 =

> postgres 24729=A0 0.0=A0 0.9 899016 55732 ?=A0=A0=A0=A0=A0 S=A0=A0=A0 Feb=
14=A0=A0 0:25 postgres: postgres database_name 66.94.234.13(47441) idle=A0=
=A0=A0=A0=A0 =

> postgres 24730=A0 0.0=A0 4.5 900136 277144 ?=A0=A0=A0=A0 S=A0=A0=A0 Feb14=
=A0=A0 0:36 postgres: postgres database_name 66.94.234.13(47442) idle=A0=A0=
=A0=A0=A0 =

> does this means that it is still replicating the database? =

That would seem pretty probable.

> what does Feb14 169:39 postgres: postgres database_name 66.94.234.13(4744=
0) COPY=A0 means? =

That means that there is a connection busy copying all the data in the
replicated tables. You can expect to see this twice:

1. On the server providing the data, there is a "COPY some_table to
stdout;" operation running;

2. On the server subscribing to the data, there is a "COPY some_table from
stdin;" operation running;

I'm not sure which server you were checking; there should be two of
them "tangoing" at this point...

> 3.=A0 I tried to query some tables in the slave database.=A0
> Unfortunately, the tables are locked.=A0 How will i know that the data
> are being copied or appended to the slave database?

It'll be done when it's done.

In Slony-I version 1.2, the locks will actually be more express; there
are cases, now, where the system can get into a deadlock because the
slon grabs tables one at a time, and so a deadlock could lead to a big
waste of time because some data (possibly lots of it) could get copied
and then roll back due to the deadlock. In 1.2, we'll completely lock
the tables on the subscriber up front, which eliminates this
particular risk...

> 4.=A0 Does this query tell that slony is appending the slave db (db_name)?
> =A0postgres=3D# select * from pg_stat_database;
> =A0=A0 datid=A0=A0 |=A0 datname=A0 | numbackends | xact_commit | xact_rol=
lback | blks_read | blks_hit =

> -----------+-----------+-------------+-------------+---------------+-----=
------+----------
> =A0=A0=A0=A0=A0=A0=A
0=A0 1 | template1 |=A0=A0=A0=A0=A0=A0=
A0=A0=A0=A0 0 =
|=A0=A0=A0=A0=A0=A0=
A0=A0 100 |=A0=A0=A0=A0=A0=A0=
A0=A0=A0=A0=A0 12 |=A0=A0=
=A0=A0=A0=A0=A0=A0 0 |=A0=A0=A0=A0=A0=A0=
A0 0
> =A0=A0=A0=A0 17229 | template0 |=A0=A0=A0=A0=A0=A0=
A0=A0=A0=A0 0 |=A0=A0=
=A0=A0=A0=A0=A0=A0=A
0=A0 0 |=A0=A0=A0=A0=A0=A0=
A0=A0=A0=A0=A0=A0 0 |=A0=A0=
=A0=A0=A0=A0=A0=A0 0 |=A0=A0=A0=A0=A0=A0=
A0 0
> =A0675239438 | db_name =A0=A0 |=A0=A0=A0=A0=A0=A0=
A0=A0 300 |=A0=A0=A0=A0=
=A0=A0 40965 |=A0=A0=A0=A0=A0=A0=
A0=A0 140 11 |=A0=A0=A0=A0=A0=A0=
A0=A0 0 |=
=A0=A0=A0=A0=A0=A0=A
0 0
> (3 rows)

I don't think there's anything much of interest to be seen from this
query.

> 5=A0 In my slony log file, i got the following now:
> DEBUG2 remoteListenThread_1
: queue event 1,1471 SYNC
> DEBUG2 remoteListenThread_1
: queue event 1,1472 SYNC
> DEBUG2 syncThread: new sl_action_seq 1 - SYNC 222
> DEBUG2 remoteListenThread_1
: queue event 1,1473 SYNC
> DEBUG2 localListenThread: Received event 2,222 SYNC
> DEBUG2 remoteListenThread_1
: queue event 1,1474 SYNC
> DEBUG2 remoteListenThread_1
: queue event 1,1475 SYNC
> DEBUG2 remoteListenThread_1
: queue event 1,1476 SYNC
> Tue Feb 14 14:11:01 PST 2006
> DEBUG2 remoteListenThread_1
: queue event 1,1477 SYNC

Is this the log for the provider node? Or the subscriber?

If it's the subscriber, you'll probably see a bunch of SYNCs being
queued while the subscription is taking place. Once the data is all
copied, those SYNCs will be processed. If there are tables that are
Real Big, it may take as much as hours to copy the tables, so there
may be a lot of SYNCs in the interim. =

If you grep for the word "copy" in the log, that may be more useful to
you...
-- =

"cbbrowne","@","ca.afilias.info"
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Christopher Browne

2006-02-17, 1:24 pm

"Ujwal S. Setlur" <uvsetlur-/ E1597aS9LQAvxtiuMwx3
w@public.gmane.org> writes:
> --- Antonio Escudero <aescudero- ur4TIblo6goN+BqQ9rBE
Ug@public.gmane.org> wrote:
>
> Check the _<cluster_name>.sl_status view on the origin
> of the subscribed set. I generally look at the
> num_lag_events field. Once the subscriber has caught
> up, that number should be close to zero.

Well, while it's processing the SUBSCRIBE_SET event, that lag will
steadily grow until it actually finishes copying all the data.

sl_status won't be of much interest during that time...
--
"cbbrowne","@","ca.afilias.info"
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Ujwal S. Setlur

2006-02-17, 1:24 pm

--- Christopher Browne <cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org>
wrote:

> "Ujwal S. Setlur" <uvsetlur-/ E1597aS9LQAvxtiuMwx3
w@public.gmane.org> writes:
> wrote:
> origin
> caught
>
> Well, while it's processing the SUBSCRIBE_SET event,
> that lag will
> steadily grow until it actually finishes copying all
> the data.
>
> sl_status won't be of much interest during that
> time...

Yup, understood. But after the subscription, it should
start giving some real feedback.

____________________
____________________
__________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Sponsored Links