Thread: logical Replication

logical Replication

From
"Anjul Tyagi"
Date:
Team,

we are trying to setup an logical replication on the Postgres 10.4, we are able to replicate the small tables but facing issue with big tables, when table has around 60 columns and 30 million rows. 

While checking the log on publication server, it shows copy command but did not copy data to subscriber server. 

Postgres Version - 10.4
Linux - SUSE SLE-12.3

Following are the setting we have enabled on Primary server:

Primary Server:
wal_level = logical
shared_buffers = 6GB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3145kB
min_wal_size = 1GB
max_wal_size = 2GB
max_replication_slots = 25
max_wal_senders = 30
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

Secondary Server:
shared_buffers = 6GB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3145kB
max_replication_slots = 25 
max_logical_replication_workers = 30
max_worker_processes = 31

can you please help us to understand, if is there any issue on the configuration.
 
 

Regards,

Anjul TYAGI

 

ü Go Green


Re: logical Replication

From
"Anjul Tyagi"
Date:
Liu,

I believe they have restriction for large object like BLOB. When i read the other details, there are no limitation for large amount of data.

 
  
 
 

Regards,

Anjul TYAGI

 

ü Go Green


------ Original Message ------
From: "Liu Huarong" <liuhuarongynu@gmail.com>
Sent: 06-07-2018 13:02:09
Subject: Re: logical Replication


Anjul Tyagi <anjul@ibosstech-us.com> 于2018年7月6日周五 下午3:11写道:
Team,

we are trying to setup an logical replication on the Postgres 10.4, we are able to replicate the small tables but facing issue with big tables, when table has around 60 columns and 30 million rows. 

While checking the log on publication server, it shows copy command but did not copy data to subscriber server. 

Postgres Version - 10.4
Linux - SUSE SLE-12.3

Following are the setting we have enabled on Primary server:

Primary Server:
wal_level = logical
shared_buffers = 6GB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3145kB
min_wal_size = 1GB
max_wal_size = 2GB
max_replication_slots = 25
max_wal_senders = 30
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

Secondary Server:
shared_buffers = 6GB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3145kB
max_replication_slots = 25 
max_logical_replication_workers = 30
max_worker_processes = 31

can you please help us to understand, if is there any issue on the configuration.
 
 

Regards,

Anjul TYAGI

 

ü Go Green


Re: logical Replication

From
Shreeyansh Dba
Date:
Hi Anjul,

Do you have a big table with a structure on subscription side which you are trying to replicate?





On Fri, Jul 6, 2018 at 12:41 PM, Anjul Tyagi <anjul@ibosstech-us.com> wrote:
Team,

we are trying to setup an logical replication on the Postgres 10.4, we are able to replicate the small tables but facing issue with big tables, when table has around 60 columns and 30 million rows. 

While checking the log on publication server, it shows copy command but did not copy data to subscriber server. 

Postgres Version - 10.4
Linux - SUSE SLE-12.3

Following are the setting we have enabled on Primary server:

Primary Server:
wal_level = logical
shared_buffers = 6GB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3145kB
min_wal_size = 1GB
max_wal_size = 2GB
max_replication_slots = 25
max_wal_senders = 30
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

Secondary Server:
shared_buffers = 6GB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3145kB
max_replication_slots = 25 
max_logical_replication_workers = 30
max_worker_processes = 31

can you please help us to understand, if is there any issue on the configuration.
 
 

Regards,

Anjul TYAGI

 

ü Go Green



Re: logical Replication

From
"Anjul Tyagi"
Date:
Shreeyansh,

Yes.. we have same structure in the subscription side as well. 

 
 
 

Regards,

Anjul TYAGI

 

ü Go Green


------ Original Message ------
From: "Shreeyansh Dba" <shreeyansh2014@gmail.com>
To: "Anjul Tyagi" <anjul@ibosstech-us.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: 06-07-2018 21:02:33
Subject: Re: logical Replication

Hi Anjul,

Do you have a big table with a structure on subscription side which you are trying to replicate?





On Fri, Jul 6, 2018 at 12:41 PM, Anjul Tyagi <anjul@ibosstech-us.com> wrote:
Team,

we are trying to setup an logical replication on the Postgres 10.4, we are able to replicate the small tables but facing issue with big tables, when table has around 60 columns and 30 million rows. 

While checking the log on publication server, it shows copy command but did not copy data to subscriber server. 

Postgres Version - 10.4
Linux - SUSE SLE-12.3

Following are the setting we have enabled on Primary server:

Primary Server:
wal_level = logical
shared_buffers = 6GB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3145kB
min_wal_size = 1GB
max_wal_size = 2GB
max_replication_slots = 25
max_wal_senders = 30
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

Secondary Server:
shared_buffers = 6GB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3145kB
max_replication_slots = 25 
max_logical_replication_workers = 30
max_worker_processes = 31

can you please help us to understand, if is there any issue on the configuration.
 
 

Regards,

Anjul TYAGI

 

ü Go Green



Re: logical Replication

From
pavan95
Date:
Hi Anjul,

Did your query got resolved? If yes, could you say me how you got it sorted
out?  I wanted to know if I can monitor Logical Replication in PG10.4.

And want to have a clear picture of Replication Lag.  Looking forward to
hear from you!!


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: logical Replication

From
"Anjul Tyagi"
Date:
Pavan,

I have update my PostgreSQL from 10.2 to 10.4, it resolve my issue.

 
 
 

Regards,

Anjul TYAGI

 

ü Go Green


------ Original Message ------
From: "pavan95" <pavan.postgresdba@gmail.com>
Sent: 14-08-2018 10:59:40
Subject: Re: logical Replication

Hi Anjul,
 
Did your query got resolved? If yes, could you say me how you got it sorted
out? I wanted to know if I can monitor Logical Replication in PG10.4.
 
And want to have a clear picture of Replication Lag. Looking forward to
hear from you!!
 
 
Regards,
Pavan
 
 
 
--
 

Re: logical Replication

From
pavan95
Date:
Hi Anjul,

I am on Postgres 10.4. But I'm still facing Replication Lag(don't know why).
I tried refreshing the publication  with data from the subscription side.
But still the data didn't got replicated.

But I tried to drop and recreate the subscription and then the data got
replicated within a fly. I really don't know what is the cause of this lag?

Am I running behind core concepts in Logical Replication?

Looking forward to hear from you!!


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: logical Replication

From
Achilleas Mantzios
Date:
On 20/08/2018 09:24, pavan95 wrote:
> Hi Anjul,
>
> I am on Postgres 10.4. But I'm still facing Replication Lag(don't know why).
> I tried refreshing the publication  with data from the subscription side.
> But still the data didn't got replicated.
>
> But I tried to drop and recreate the subscription and then the data got
> replicated within a fly. I really don't know what is the cause of this lag?
How do you monitor ? Do you check for ERROR in the logs?
>
> Am I running behind core concepts in Logical Replication?
>
> Looking forward to hear from you!!
>
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: logical Replication

From
pavan95
Date:
Hi Achilleas,

>> But still the data didn't got replicated. 
>> 
>> But I tried to drop and recreate the subscription and then the data got 
>> replicated within a fly. I really don't know what is the cause of this
>> lag? 

  >How do you monitor ? Do you check for ERROR in the logs?

I do monitor the ERRORS from the log file itself. 

But When I insert data in the publisher, And perform a check on the
subscriber, it is not getting replicated(most of the times) until I drop and
recreate my SUBSCRIPTION.

But later that, it is replicating very fast. Hope I specified the answer for
your question. 

Thanks in Advance

Regards,
Pavan
 



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: logical Replication

From
Achilleas Mantzios
Date:
On 20/08/2018 09:42, pavan95 wrote:
> Hi Achilleas,
>
>>> But still the data didn't got replicated.
>>>
>>> But I tried to drop and recreate the subscription and then the data got
>>> replicated within a fly. I really don't know what is the cause of this
>>> lag?
>    >How do you monitor ? Do you check for ERROR in the logs?
>
> I do monitor the ERRORS from the log file itself.
>
> But When I insert data in the publisher, And perform a check on the
> subscriber, it is not getting replicated(most of the times) until I drop and
> recreate my SUBSCRIPTION.

OS? (Linux, BSD, etc)
PostgreSQL Version?
Also give the current settings of wal_receiver_timeout , wal_sender_timeout , increasing those to '5 min' solved my
issues.



>
> But later that, it is replicating very fast. Hope I specified the answer for
> your question.
>
> Thanks in Advance
>
> Regards,
> Pavan
>   
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: logical Replication

From
pavan95
Date:
>> But When I insert data in the publisher, And perform a check on the 
>> subscriber, it is not getting replicated(most of the times) until I drop
>> and 
>> recreate my SUBSCRIPTION.

>OS? (Linux, BSD, etc) 
*Ubuntu 16.04.5 LTS*
>PostgreSQL Version? 
*PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit*
>Also give the current settings of wal_receiver_timeout , wal_sender_timeout
, increasing those to '5 min' solved my issues. 
*1) wal_receiver_timeout: 1min
2) wal_sender_timeout:   1min*

How increasing above params related to the replication lag? 

Looking forward hearing from you!!!


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: logical Replication

From
Achilleas Mantzios
Date:
On 20/08/2018 10:04, pavan95 wrote:
>>> But When I insert data in the publisher, And perform a check on the
>>> subscriber, it is not getting replicated(most of the times) until I drop
>>> and
>>> recreate my SUBSCRIPTION.
>> OS? (Linux, BSD, etc)
> *Ubuntu 16.04.5 LTS*
>> PostgreSQL Version?
> *PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit*
>> Also give the current settings of wal_receiver_timeout , wal_sender_timeout
> , increasing those to '5 min' solved my issues.
> *1) wal_receiver_timeout: 1min
> 2) wal_sender_timeout:   1min*
>
> How increasing above params related to the replication lag?
It helps with the ERRORs you get (e.g.
worker process: logical replication worker for subscription 33650 sync 20258 (PID 32898) exited with exit code 1
or
worker process: logical replication worker for subscription 185231525 (PID 78166) exited with exit code 1

Now on the publisher side what does :
select * from pg_stat_replication ;
tell you?

>
> Looking forward hearing from you!!!
>
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: logical Replication

From
pavan95
Date:
Hi Achilleas,

>Now on the publisher side what does : 
>select * from pg_stat_replication ; 
>tell you? 
Please find the below output:
>*select * from pg_stat_replication ;*
   pid  | usesysid | usename  | application_name | client_addr  |
client_hostname | client_port |          backend_start           |
backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn
| write_lag | flush_lag | replay_lag | sync_priority | sync_state

-------+----------+----------+------------------+--------------+-----------------+-------------+----------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
 32515 |    78225 |    user | appn               | xxx.xxx.xx.xxx |                
|       411111 | 2018-08-20 11:32:09.636622+05:30 |              | streaming
| 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 |           |          
|            |             0 | async






--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: logical Replication

From
Achilleas Mantzios
Date:
On 20/08/2018 10:40, pavan95 wrote:
> Hi Achilleas,
>
>> Now on the publisher side what does :
>> select * from pg_stat_replication ;
>> tell you?
> Please find the below output:
>> *select * from pg_stat_replication ;*
>     pid  | usesysid | usename  | application_name | client_addr  |
> client_hostname | client_port |          backend_start           |
> backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  | replay_lsn
> | write_lag | flush_lag | replay_lag | sync_priority | sync_state
>
-------+----------+----------+------------------+--------------+-----------------+-------------+----------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
>   32515 |    78225 |    user | appn               | xxx.xxx.xx.xxx |
> |       411111 | 2018-08-20 11:32:09.636622+05:30 |              | streaming
> | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 |           |
> |            |             0 | async
>
So here you have zero lag. How do you experience the lag? What do you exactly measure?
>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: logical Replication

From
pavan95
Date:
> Hi Achilleas, 
> 
>> Now on the publisher side what does : 
>> select * from pg_stat_replication ; 
>> tell you? 
> Please find the below output: 
>> *select * from pg_stat_replication ;* 
>     pid  | usesysid | usename  | application_name | client_addr  | 
> client_hostname | client_port |          backend_start           | 
> backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  |
> replay_lsn 
> | write_lag | flush_lag | replay_lag | sync_priority | sync_state 
>
-------+----------+----------+------------------+--------------+-----------------+-------------+----------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------

>   32515 |    78225 |    user | appn               | xxx.xxx.xx.xxx | 
> |       411111 | 2018-08-20 11:32:09.636622+05:30 |              |
> streaming 
> | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 |           | 
> |            |             0 | async 
>
>>So here you have zero lag. How do you experience the lag? What do you
exactly measure? 

Actually there are no running transactions in the database. When I insert
the data suppose 100 records in a table and after commit connect to the
subscriber database and issue row count from that particular table, I am
finding that the data didn't got replicated.

Later which I will proceed with :
ALTER SUBSCRIPTION my_sub_name WITH REFRESH PUBLICATION WITH( COPY_DATA)

Even then I can't see the data replicated in the subscriber side.

Then I will go with dropping and recreating the SUBSCRIPTION on the
subscriber side where I will see that inserted 100 records in the subscriber
side. 

Again will try inserting another 1000 records which will get replicated
within microsecond(I guess). 

This is what I will consider as lag. Hope I answered your question.

Looking forward to hear from you.

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: logical Replication

From
Achilleas Mantzios
Date:
On 20/08/2018 10:59, pavan95 wrote:
>> Hi Achilleas,
>>
>>> Now on the publisher side what does :
>>> select * from pg_stat_replication ;
>>> tell you?
>> Please find the below output:
>>> *select * from pg_stat_replication ;*
>>      pid  | usesysid | usename  | application_name | client_addr  |
>> client_hostname | client_port |          backend_start           |
>> backend_xmin |   state   |  sent_lsn  | write_lsn  | flush_lsn  |
>> replay_lsn
>> | write_lag | flush_lag | replay_lag | sync_priority | sync_state
>>
-------+----------+----------+------------------+--------------+-----------------+-------------+----------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
>>    32515 |    78225 |    user | appn               | xxx.xxx.xx.xxx |
>> |       411111 | 2018-08-20 11:32:09.636622+05:30 |              |
>> streaming
>> | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 | 0/69BE27D0 |           |
>> |            |             0 | async
>>
>>> So here you have zero lag. How do you experience the lag? What do you
> exactly measure?
>
> Actually there are no running transactions in the database. When I insert
> the data suppose 100 records in a table and after commit connect to the
> subscriber database and issue row count from that particular table, I am
> finding that the data didn't got replicated.
>
> Later which I will proceed with :
> ALTER SUBSCRIPTION my_sub_name WITH REFRESH PUBLICATION WITH( COPY_DATA)
>
> Even then I can't see the data replicated in the subscriber side.
>
> Then I will go with dropping and recreating the SUBSCRIPTION on the
> subscriber side where I will see that inserted 100 records in the subscriber
> side.
>
> Again will try inserting another 1000 records which will get replicated
> within microsecond(I guess).
This is far from normal. This isn't working for some reason.
Now do that :
a) increase the timeouts
b) restart both servers
c) drop / recreate the subscription
d) wait till all data are synced
e) start with a simple insert (1 row), and monitor the data on the subscriber. Then insert 100 rows. Always watch the
LOGfor ERRORs and also pg_stat_replication (publisher), pg_stat_subscription 
 
(subscriber), pg_replication_slots (publisher), pg_replication_origin_status (subscriber)
>
> This is what I will consider as lag. Hope I answered your question.
>
> Looking forward to hear from you.
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: logical Replication

From
pavan95
Date:
Hi Achilleas,

Will monitor and get back to you on this. I have concern about one more
error for which I have raised a question on this on the following link:
http://www.postgresql-archive.org/Space-Related-Errors-in-Postgres-10-4-Logical-Replication-td6034649.html
<http://www.postgresql-archive.org/Space-Related-Errors-in-Postgres-10-4-Logical-Replication-td6034649.html>  

Do you have any idea on the below error:

2018-08-20 10:34:55.801 IST [24955] user@db ERROR:  could not create 
directory "pg_replslot/mysub_14211111_sync_111111.tmp": No space left on 
device 
2018-08-20 10:34:56.036 IST [34222] user@db ERROR:  could not create file 
"pg_replslot/mysub/state.tmp": No space left on device
2018-08-20 10:34:56.053 IST [23444] user@db WARNING:  could not create 
relation-cache initialization file "global/pg_internal.init.11331": No space 
left on device
2018-08-20 10:34:56.053 IST [23444] user@db DETAIL:  Continuing anyway, but 
there's something wrong.


Looking forward to hear from you regarding the lifecycle of snapshots in the
publisher side..

Thanks in advance!!

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: logical Replication

From
Achilleas Mantzios
Date:
On 20/08/2018 11:58, pavan95 wrote:
> Hi Achilleas,
>
> Will monitor and get back to you on this. I have concern about one more
> error for which I have raised a question on this on the following link:
> http://www.postgresql-archive.org/Space-Related-Errors-in-Postgres-10-4-Logical-Replication-td6034649.html
> <http://www.postgresql-archive.org/Space-Related-Errors-in-Postgres-10-4-Logical-Replication-td6034649.html>
>
> Do you have any idea on the below error:
>
> 2018-08-20 10:34:55.801 IST [24955] user@db ERROR:  could not create
> directory "pg_replslot/mysub_14211111_sync_111111.tmp": No space left on
> device
> 2018-08-20 10:34:56.036 IST [34222] user@db ERROR:  could not create file
> "pg_replslot/mysub/state.tmp": No space left on device
> 2018-08-20 10:34:56.053 IST [23444] user@db WARNING:  could not create
> relation-cache initialization file "global/pg_internal.init.11331": No space
> left on device
> 2018-08-20 10:34:56.053 IST [23444] user@db DETAIL:  Continuing anyway, but
> there's something wrong.
Man, your system's state is one step from disaster.

Increase the space ASAP.

>
> Looking forward to hear from you regarding the lifecycle of snapshots in the
> publisher side..
>
> Thanks in advance!!
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: logical Replication

From
pavan95
Date:
Hi Achilleas,

In order to be more specific, today morning I faced this issue. At that
particular point of time, pg_logical directory is containing snaps worth
23GB. 

Suddenly after sometime exactly 5 mins after my check all the snaps got
deleted and the logical replication continued to function as earlier..


Can we infer something(logical kind of concept) from this??


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: logical Replication

From
Achilleas Mantzios
Date:
On 20/08/2018 13:07, pavan95 wrote:
> Hi Achilleas,
>
> In order to be more specific, today morning I faced this issue. At that
> particular point of time, pg_logical directory is containing snaps worth
> 23GB.
>
> Suddenly after sometime exactly 5 mins after my check all the snaps got
> deleted and the logical replication continued to function as earlier..
>

If you are marginal on space available, the slightest delay can cause the replication slot to run out of space.
So increase your space.

> Can we infer something(logical kind of concept) from this??
>
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: logical Replication

From
pavan95
Date:
>If you are marginal on space available, the slightest delay can cause the
replication slot to run out of space. 
>So increase your space. 

How can I judge myself that marginal space is left available. But the fact
is I do still have 50 GB of additional disk space available(on the mount
point where the postgres 10.4 data directory runs) when I issued "df -kh" .

So I need to have a valid justification in order to ask the systems team for
space getting added. But still I'm unable to find any reason from our
conversation. 

But the point I wonder is the snaps are getting automatically deleted and
the error stops producing for a period of time. Looking forward to touch the
pain area(if at all the server feels).

Looking forward to hear from you. Thanks in Advance.

Regards,
Pavan





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: logical Replication

From
Achilleas Mantzios
Date:
On 20/08/2018 13:37, pavan95 wrote:
>> If you are marginal on space available, the slightest delay can cause the
> replication slot to run out of space.
>> So increase your space.
> How can I judge myself that marginal space is left available. But the fact
> is I do still have 50 GB of additional disk space available(on the mount
> point where the postgres 10.4 data directory runs) when I issued "df -kh" .
>
> So I need to have a valid justification in order to ask the systems team for
> space getting added. But still I'm unable to find any reason from our
> conversation.
"No space left on device" is a big reason. This is alarming. Your sysadms should have had monitoring in place and
shouldhave addressed and resolved this already.
 
>
> But the point I wonder is the snaps are getting automatically deleted and
> the error stops producing for a period of time. Looking forward to touch the
> pain area(if at all the server feels).
>
> Looking forward to hear from you. Thanks in Advance.
>
> Regards,
> Pavan
>
>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: logical Replication

From
Maletin von Oertzen
Date:
At 2018-08-20 09:59 pavan95 wrote:

Actually there are no running transactions in the database. When I insert
the data suppose 100 records in a table and after commit connect to the
subscriber database and issue row count from that particular table, I am
finding that the data didn't got replicated.

Later which I will proceed with :
ALTER SUBSCRIPTION my_sub_name WITH REFRESH PUBLICATION WITH( COPY_DATA)

Even then I can't see the data replicated in the subscriber side.

Then I will go with dropping and recreating the SUBSCRIPTION on the
subscriber side where I will see that inserted 100 records in the subscriber
side.

Again will try inserting another 1000 records which will get replicated
within microsecond(I guess).

Is it possible, that there is a "alter table replica identity"-statement missing?