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

2017-09-25 13:12 GMT+02:00 Masahiko Sawada <sawada.mshk@gmail.com>:

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.

I used this subscriptions for and it was warking.

Regards

Pavel

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

2017-09-25 18:30 GMT+02:00 Petr Jelinek <petr.jelinek@2ndquadrant.com>:

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.

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

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

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

Pavel

--

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

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

Pavel

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

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

Hi

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

Probably I did some wrong

Sorry for noise

Regards

Pavel