Обсуждение: [HACKERS] logical replication and statistics

On Mon, Sep 25, 2017 at 12:58 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hi
>
> I did trivial example of logical replication (one table, one publication,
> one subscription)
>
> I am little bit surprised so after some work - the replication is working,
> the statistics are empty
>
> #master
> postgres=# select * from pg_stat_replication ;
> (0 rows)
>
> #slave
> postgres=# select * from pg_stat_subscription ;
> -[ RECORD 1 ]---------+---------
> subid                 | 16472
> subname               | test_sub
> pid                   |
> relid                 |
> received_lsn          |
> last_msg_send_time    |
> last_msg_receipt_time |
> latest_end_lsn        |
> latest_end_time       |
>
> Should be some enabled?
>

If the subscription is disabled, the statistics of subscription is
empty and no wal sender processes launch. The test_sub can start the
replication by ALTER SUBSCRIPTION test_sub ENABLE.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 25/09/17 13:33, Pavel Stehule wrote:
> 
> 
> 2017-09-25 13:12 GMT+02:00 Masahiko Sawada <sawada.mshk@gmail.com
> <mailto:sawada.mshk@gmail.com>>:
> 
>     On Mon, Sep 25, 2017 at 12:58 AM, Pavel Stehule
>     <pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>> wrote:
>     > Hi
>     >
>     > I did trivial example of logical replication (one table, one publication,
>     > one subscription)
>     >
>     > I am little bit surprised so after some work - the replication is working,
>     > the statistics are empty
>     >
>     > #master
>     > postgres=# select * from pg_stat_replication ;
>     > (0 rows)
>     >
>     > #slave
>     > postgres=# select * from pg_stat_subscription ;
>     > -[ RECORD 1 ]---------+---------
>     > subid                 | 16472
>     > subname               | test_sub
>     > pid                   |
>     > relid                 |
>     > received_lsn          |
>     > last_msg_send_time    |
>     > last_msg_receipt_time |
>     > latest_end_lsn        |
>     > latest_end_time       |
>     >
>     > Should be some enabled?
>     >
> 
>     If the subscription is disabled, the statistics of subscription is
>     empty and no wal sender processes launch. The test_sub can start the
>     replication by ALTER SUBSCRIPTION test_sub ENABLE.
> 
> 
> I used this subscriptions for and it was warking.


If there is no pid, the worker is not running. And if there is nothing
in pg_stat_replication on master, the walsender is not running either,
so it seems like it's not actually working.

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Pavel Stehule <pavel.stehule@gmail.com> writes:
> I had two instances on one server with different port. I am sure, so
> replication was functional. Only one issue is statistics

> Master:

> CREATE TABLE foo(id int primary key, a int);
> CREATE PUBLICATION test_pub FOR TABLE foo;
> INSERT INTO foo VALUES(1, 200);

> slave

> CREATE TABLE foo(id int primary key, a int);
> CREATE SUBSCRIPTION test_sub CONNECTION 'port=5432' PUBLICATION test_pub;

> That was all

In this example, nothing's been done yet by the actual replication
apply process, only by the initial table sync.  Maybe that accounts
for your not seeing stats?
        regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 25/09/17 19:19, Tom Lane wrote:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> I had two instances on one server with different port. I am sure, so
>> replication was functional. Only one issue is statistics
> 
>> Master:
> 
>> CREATE TABLE foo(id int primary key, a int);
>> CREATE PUBLICATION test_pub FOR TABLE foo;
>> INSERT INTO foo VALUES(1, 200);
> 
>> slave
> 
>> CREATE TABLE foo(id int primary key, a int);
>> CREATE SUBSCRIPTION test_sub CONNECTION 'port=5432' PUBLICATION test_pub;
> 
>> That was all
> 
> In this example, nothing's been done yet by the actual replication
> apply process, only by the initial table sync.  Maybe that accounts
> for your not seeing stats?
> 

The main replication worker should still be running though. The output
of pg_stat_replication should only be empty if there is nothing running.

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Sep 26, 2017 at 2:50 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
> 2017-09-25 19:23 GMT+02:00 Petr Jelinek <petr.jelinek@2ndquadrant.com>:
>>
>> On 25/09/17 19:19, Tom Lane wrote:
>> > Pavel Stehule <pavel.stehule@gmail.com> writes:
>> >> I had two instances on one server with different port. I am sure, so
>> >> replication was functional. Only one issue is statistics
>> >
>> >> Master:
>> >
>> >> CREATE TABLE foo(id int primary key, a int);
>> >> CREATE PUBLICATION test_pub FOR TABLE foo;
>> >> INSERT INTO foo VALUES(1, 200);
>> >
>> >> slave
>> >
>> >> CREATE TABLE foo(id int primary key, a int);
>> >> CREATE SUBSCRIPTION test_sub CONNECTION 'port=5432' PUBLICATION
>> >> test_pub;
>> >
>> >> That was all
>> >
>> > In this example, nothing's been done yet by the actual replication
>> > apply process, only by the initial table sync.  Maybe that accounts
>> > for your not seeing stats?
>> >
>>
>> The main replication worker should still be running though. The output
>> of pg_stat_replication should only be empty if there is nothing running.
>>
>
> I did some inserts, updates, ..
>
> I can recheck it - it was done on 10 RC

I guess CREATE SUBSCRIPTION failed for whatever reason (e.g, wal_level
< logical on the master). Didn't you get errors from CREATE
SUBSCRIPTION?

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Sep 26, 2017 at 6:57 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
> 2017-09-26 11:56 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:
>>
>>
>>
>> 2017-09-26 11:51 GMT+02:00 Masahiko Sawada <sawada.mshk@gmail.com>:
>>>
>>> On Tue, Sep 26, 2017 at 2:50 AM, Pavel Stehule <pavel.stehule@gmail.com>
>>> wrote:
>>> >
>>> >
>>> > 2017-09-25 19:23 GMT+02:00 Petr Jelinek <petr.jelinek@2ndquadrant.com>:
>>> >>
>>> >> On 25/09/17 19:19, Tom Lane wrote:
>>> >> > Pavel Stehule <pavel.stehule@gmail.com> writes:
>>> >> >> I had two instances on one server with different port. I am sure,
>>> >> >> so
>>> >> >> replication was functional. Only one issue is statistics
>>> >> >
>>> >> >> Master:
>>> >> >
>>> >> >> CREATE TABLE foo(id int primary key, a int);
>>> >> >> CREATE PUBLICATION test_pub FOR TABLE foo;
>>> >> >> INSERT INTO foo VALUES(1, 200);
>>> >> >
>>> >> >> slave
>>> >> >
>>> >> >> CREATE TABLE foo(id int primary key, a int);
>>> >> >> CREATE SUBSCRIPTION test_sub CONNECTION 'port=5432' PUBLICATION
>>> >> >> test_pub;
>>> >> >
>>> >> >> That was all
>>> >> >
>>> >> > In this example, nothing's been done yet by the actual replication
>>> >> > apply process, only by the initial table sync.  Maybe that accounts
>>> >> > for your not seeing stats?
>>> >> >
>>> >>
>>> >> The main replication worker should still be running though. The output
>>> >> of pg_stat_replication should only be empty if there is nothing
>>> >> running.
>>> >>
>>> >
>>> > I did some inserts, updates, ..
>>> >
>>> > I can recheck it - it was done on 10 RC
>>>
>>> I guess CREATE SUBSCRIPTION failed for whatever reason (e.g, wal_level
>>> < logical on the master). Didn't you get errors from CREATE
>>> SUBSCRIPTION?
>>
>>
>> sorry I had wal_level = logical
>
>
> but if I remember - maybe I had this level only on master
>>
>>

Hmm, has a logical replication slot created on the master?

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers