Re: [HACKERS] logical replication and statistics

[Pavel Stehule]

Hi

I did recheck and now it looks like it is expected.

Probably I did some wrong

Sorry for noise

Regards

Pavel

Re: [HACKERS] logical replication and statistics

[Masahiko Sawada]

On Tue, Sep 26, 2017 at 6:57 PM, Pavel Stehule  wrote:
>
>
> 2017-09-26 11:56 GMT+02:00 Pavel Stehule :
>>
>>
>>
>> 2017-09-26 11:51 GMT+02:00 Masahiko Sawada :
>>>
>>> On Tue, Sep 26, 2017 at 2:50 AM, Pavel Stehule 
>>> wrote:
>>> >
>>> >
>>> > 2017-09-25 19:23 GMT+02:00 Petr Jelinek :
>>> >>
>>> >> On 25/09/17 19:19, Tom Lane wrote:
>>> >> > Pavel Stehule  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

Re: [HACKERS] logical replication and statistics

[Pavel Stehule]

2017-09-26 11:56 GMT+02:00 Pavel Stehule :

>
>
> 2017-09-26 11:51 GMT+02:00 Masahiko Sawada :
>
>> On Tue, Sep 26, 2017 at 2:50 AM, Pavel Stehule 
>> wrote:
>> >
>> >
>> > 2017-09-25 19:23 GMT+02:00 Petr Jelinek :
>> >>
>> >> On 25/09/17 19:19, Tom Lane wrote:
>> >> > Pavel Stehule  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

>
> Pavel
>
>>
>> Regards,
>>
>> --
>> Masahiko Sawada
>> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
>> NTT Open Source Software Center
>>
>
>

Re: [HACKERS] logical replication and statistics

[Pavel Stehule]

2017-09-26 11:51 GMT+02:00 Masahiko Sawada :

> On Tue, Sep 26, 2017 at 2:50 AM, Pavel Stehule 
> wrote:
> >
> >
> > 2017-09-25 19:23 GMT+02:00 Petr Jelinek :
> >>
> >> On 25/09/17 19:19, Tom Lane wrote:
> >> > Pavel Stehule  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

Pavel

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

Re: [HACKERS] logical replication and statistics

[Masahiko Sawada]

On Tue, Sep 26, 2017 at 2:50 AM, Pavel Stehule  wrote:
>
>
> 2017-09-25 19:23 GMT+02:00 Petr Jelinek :
>>
>> On 25/09/17 19:19, Tom Lane wrote:
>> > Pavel Stehule  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

Re: [HACKERS] logical replication and statistics

[Pavel Stehule]

2017-09-25 19:23 GMT+02:00 Petr Jelinek :

> On 25/09/17 19:19, Tom Lane wrote:
> > Pavel Stehule  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

Pavel

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

Re: [HACKERS] logical replication and statistics

[Petr Jelinek]

On 25/09/17 19:19, Tom Lane wrote:
> Pavel Stehule  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

Re: [HACKERS] logical replication and statistics

[Tom Lane]

Pavel Stehule  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

Re: [HACKERS] logical replication and statistics

[Pavel Stehule]

2017-09-25 18:30 GMT+02:00 Petr Jelinek :

> On 25/09/17 13:33, Pavel Stehule wrote:
> >
> >
> > 2017-09-25 13:12 GMT+02:00 Masahiko Sawada  > >:
> >
> > On Mon, Sep 25, 2017 at 12:58 AM, Pavel Stehule
> > > 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.
>

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



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

Re: [HACKERS] logical replication and statistics

[Petr Jelinek]

On 25/09/17 13:33, Pavel Stehule wrote:
> 
> 
> 2017-09-25 13:12 GMT+02:00 Masahiko Sawada  >:
> 
> On Mon, Sep 25, 2017 at 12:58 AM, Pavel Stehule
> > 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

Re: [HACKERS] logical replication and statistics

[Pavel Stehule]

2017-09-25 13:12 GMT+02:00 Masahiko Sawada :

> On Mon, Sep 25, 2017 at 12:58 AM, Pavel Stehule 
> 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.

Regards

Pavel


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

Re: [HACKERS] logical replication and statistics

[Masahiko Sawada]

On Mon, Sep 25, 2017 at 12:58 AM, Pavel Stehule  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

[HACKERS] logical replication and statistics

[Pavel Stehule]

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?

Regards

Pavel