pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLogical Replication
31+ messages / 11 participants
[nested] [flat]
* Logical Replication
@ 2017-10-09 16:43 pinker <pinker@onet.eu>
0 siblings, 1 reply; 31+ messages in thread
From: pinker @ 2017-10-09 16:43 UTC (permalink / raw)
To: pgsql-admin
Hi,
I'm just trying out new feature - logical replication. It works great
between 10 and 10, but I would like to try between different major version.
Documentation says it's possible : /Replicating between different major
versions of PostgreSQL/, but I cannot find any post with setup examples
(yes, I did google search :).
Do you know maybe where can I find it?
If I would like to replicate 9.4 to 10 it means I need to install pglogical?
or it's happening through fdw for instance?
Another thing that puzzles me:
I have created 2 identical publications what resulted in the data
duplication. Is it intended to work this way? or maybe some checks will
appear later on?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Logical Replication
@ 2017-10-09 23:26 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: pinker <pinker@onet.eu>
0 siblings, 1 reply; 31+ messages in thread
From: Mark Kirkwood @ 2017-10-09 23:26 UTC (permalink / raw)
To: pgsql-admin
On 10/10/17 05:43, pinker wrote:
> Hi,
> I'm just trying out new feature - logical replication. It works great
> between 10 and 10, but I would like to try between different major version.
> Documentation says it's possible : /Replicating between different major
> versions of PostgreSQL/, but I cannot find any post with setup examples
> (yes, I did google search :).
> Do you know maybe where can I find it?
> If I would like to replicate 9.4 to 10 it means I need to install pglogical?
> or it's happening through fdw for instance?
I think the docs mean that logical replication works between different
versions of Postgres - provided they both have the logical replication
code! So you will be able to run it between version 10 and 11 for instance.
With respect to 9.4 - you probably need to discuss using pglogical with
the EDB guys (ISTM that it is slightly different from the logical
replication code in version 10).
>
> Another thing that puzzles me:
> I have created 2 identical publications what resulted in the data
> duplication. Is it intended to work this way? or maybe some checks will
> appear later on?
>
>
Probably worth raising this as a bug.
regards
Mark
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Logical Replication
@ 2017-10-11 21:10 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 31+ messages in thread
From: Mark Kirkwood @ 2017-10-11 21:10 UTC (permalink / raw)
To: pgsql-admin
On 10/10/17 12:26, Mark Kirkwood wrote:
>
>
> With respect to 9.4 - you probably need to discuss using pglogical
> with the EDB guys...
That should really say: ...with the *2nd Quadrant* guys (apologies to
you all...)!
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 31+ messages in thread
* logical Replication
@ 2018-07-06 07:11 Anjul Tyagi <anjul@ibosstech-us.com>
0 siblings, 2 replies; 31+ messages in thread
From: Anjul Tyagi @ 2018-07-06 07:11 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-07-06 14:40 Anjul Tyagi <anjul@ibosstech-us.com>
parent: Anjul Tyagi <anjul@ibosstech-us.com>
1 sibling, 0 replies; 31+ messages in thread
From: Anjul Tyagi @ 2018-07-06 14:40 UTC (permalink / raw)
To: pgsql-admin
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>
To: anjul@ibosstech-us.com
Cc: pgsql-admin@postgresql.org
Sent: 06-07-2018 13:02:09
Subject: Re: logical Replication
>Have you checked these restrictions?
>https://www.postgresql.org/docs/10/static/logical-replication-restrictions.html
>
>
>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
>>
>>
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-07-06 15:32 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Anjul Tyagi <anjul@ibosstech-us.com>
1 sibling, 1 reply; 31+ messages in thread
From: Shreeyansh Dba @ 2018-07-06 15:32 UTC (permalink / raw)
To: Anjul Tyagi <anjul@ibosstech-us.com>; +Cc: pgsql-admin
Hi Anjul,
Do you have a big table with a structure on subscription side which you are
trying to replicate?
<http://www.shreeyansh.com;
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*
>
>
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-07-09 07:03 Anjul Tyagi <anjul@ibosstech-us.com>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Anjul Tyagi @ 2018-07-09 07:03 UTC (permalink / raw)
To: Shreeyansh Dba <shreeyansh2014@gmail.com>; pgsql-admin
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?
>
>
>
><http://www.shreeyansh.com;
>
>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
>>
>>
>
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-14 05:29 pavan95 <pavan.postgresdba@gmail.com>
parent: Anjul Tyagi <anjul@ibosstech-us.com>
0 siblings, 1 reply; 31+ messages in thread
From: pavan95 @ 2018-08-14 05:29 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-14 12:07 Anjul Tyagi <anjul@ibosstech-us.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Anjul Tyagi @ 2018-08-14 12:07 UTC (permalink / raw)
To: pavan95 <pavan.postgresdba@gmail.com>; pgsql-admin
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>
To: pgsql-admin@postgresql.org
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
>
>
>
>--
>Sent from:
>http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 06:24 pavan95 <pavan.postgresdba@gmail.com>
parent: Anjul Tyagi <anjul@ibosstech-us.com>
0 siblings, 1 reply; 31+ messages in thread
From: pavan95 @ 2018-08-20 06:24 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 06:33 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Achilleas Mantzios @ 2018-08-20 06:33 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 06:42 pavan95 <pavan.postgresdba@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 31+ messages in thread
From: pavan95 @ 2018-08-20 06:42 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 06:54 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Achilleas Mantzios @ 2018-08-20 06:54 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 07:04 pavan95 <pavan.postgresdba@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 31+ messages in thread
From: pavan95 @ 2018-08-20 07:04 UTC (permalink / raw)
To: pgsql-admin
>> 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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 07:20 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Achilleas Mantzios @ 2018-08-20 07:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 07:40 pavan95 <pavan.postgresdba@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 31+ messages in thread
From: pavan95 @ 2018-08-20 07:40 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 07:44 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Achilleas Mantzios @ 2018-08-20 07:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 07:59 pavan95 <pavan.postgresdba@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 31+ messages in thread
From: pavan95 @ 2018-08-20 07:59 UTC (permalink / raw)
To: pgsql-admin
> 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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 08:27 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
1 sibling, 1 reply; 31+ messages in thread
From: Achilleas Mantzios @ 2018-08-20 08:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 LOG for 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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 08:58 pavan95 <pavan.postgresdba@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 31+ messages in thread
From: pavan95 @ 2018-08-20 08:58 UTC (permalink / raw)
To: pgsql-admin
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-td603464...
<http://www.postgresql-archive.org/Space-Related-Errors-in-Postgres-10-4-Logical-Replication-td603464...;
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 09:41 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Achilleas Mantzios @ 2018-08-20 09:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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-td603464...
> <http://www.postgresql-archive.org/Space-Related-Errors-in-Postgres-10-4-Logical-Replication-td603464...;
>
> 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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 10:07 pavan95 <pavan.postgresdba@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 31+ messages in thread
From: pavan95 @ 2018-08-20 10:07 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 10:29 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Achilleas Mantzios @ 2018-08-20 10:29 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 10:37 pavan95 <pavan.postgresdba@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 31+ messages in thread
From: pavan95 @ 2018-08-20 10:37 UTC (permalink / raw)
To: pgsql-admin
>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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-20 10:58 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 0 replies; 31+ messages in thread
From: Achilleas Mantzios @ 2018-08-20 10:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 should have 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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: logical Replication
@ 2018-08-22 21:14 Maletin von Oertzen <oertzen@gmail.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
1 sibling, 0 replies; 31+ messages in thread
From: Maletin von Oertzen @ 2018-08-22 21:14 UTC (permalink / raw)
To: pavan95 <pavan.postgresdba@gmail.com>; +Cc: pgsql-admin
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?
https://www.postgresql.org/docs/11/static/sql-altertable.html=
^ permalink raw reply [nested|flat] 31+ messages in thread
* Logical replication
@ 2020-11-19 20:38 Yambu <hyambu@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Yambu @ 2020-11-19 20:38 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello
Is it recommended to do logical replication on a table without a primary?
regards
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Logical replication
@ 2020-11-19 20:40 David G. Johnston <david.g.johnston@gmail.com>
parent: Yambu <hyambu@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: David G. Johnston @ 2020-11-19 20:40 UTC (permalink / raw)
To: Yambu <hyambu@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Nov 19, 2020 at 1:39 PM Yambu <hyambu@gmail.com> wrote:
> Is it recommended to do logical replication on a table without a primary?
>
>
primary what?
David J.
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Logical replication
@ 2020-11-19 20:46 Holger Jakobs <holger@jakobs.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Holger Jakobs @ 2020-11-19 20:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
I think he refers to a table without a PRIMARY KEY.
No, that's not a good idea, as it is never a good idea having a table
without a PRIMARY KEY.
PRIMARY KEYs are not an optional feature.
Am 19.11.20 um 21:40 schrieb David G. Johnston:
> On Thu, Nov 19, 2020 at 1:39 PM Yambu <hyambu@gmail.com
> <mailto:hyambu@gmail.com>> wrote:
>
> Is it recommended to do logical replication on a table without a
> primary?
>
>
> primary what?
>
> David J.
>
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-keys] OpenPGP_0x8A579C93B31961BA.asc (16.5K, ../../dc37e0ba-50de-a640-5da5-5e6591ff4e8b@jakobs.com/3-OpenPGP_0x8A579C93B31961BA.asc)
download
[application/pgp-signature] OpenPGP_signature (202B, ../../dc37e0ba-50de-a640-5da5-5e6591ff4e8b@jakobs.com/4-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Logical replication
@ 2020-11-19 21:14 Yambu <hyambu@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 31+ messages in thread
From: Yambu @ 2020-11-19 21:14 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Oh yes i meant primary key.
Thanks
On Thu, Nov 19, 2020 at 10:46 PM Holger Jakobs <holger@jakobs.com> wrote:
> I think he refers to a table without a PRIMARY KEY.
>
> No, that's not a good idea, as it is never a good idea having a table
> without a PRIMARY KEY.
>
> PRIMARY KEYs are not an optional feature.
> Am 19.11.20 um 21:40 schrieb David G. Johnston:
>
> On Thu, Nov 19, 2020 at 1:39 PM Yambu <hyambu@gmail.com> wrote:
>
>> Is it recommended to do logical replication on a table without a primary?
>>
>>
> primary what?
>
> David J.
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>
^ permalink raw reply [nested|flat] 31+ messages in thread
* Logical Replication
@ 2023-05-22 18:58 André Rodrigues <db.andre@gmail.com>
0 siblings, 0 replies; 31+ messages in thread
From: André Rodrigues @ 2023-05-22 18:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
Which parameter should I keep an eye on to set a reliable logical
replication ( just 10 tables ) ?
I ask it because I have getting a lot of time status lost replication.
So I ask to Gurus DBA Postgres
=# select version () ;
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine
10.2.1_pre1) 10.2.1 20201203, 64-bit
(1 row)
--
Atenciosamente,
*André *
^ permalink raw reply [nested|flat] 31+ messages in thread
end of thread, other threads:[~2023-05-22 18:58 UTC | newest]
Thread overview: 31+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-10-09 16:43 Logical Replication pinker <pinker@onet.eu>
2017-10-09 23:26 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-10-11 21:10 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-07-06 07:11 logical Replication Anjul Tyagi <anjul@ibosstech-us.com>
2018-07-06 14:40 ` Re: logical Replication Anjul Tyagi <anjul@ibosstech-us.com>
2018-07-06 15:32 ` Re: logical Replication Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-07-09 07:03 ` Re: logical Replication Anjul Tyagi <anjul@ibosstech-us.com>
2018-08-14 05:29 ` Re: logical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-08-14 12:07 ` Re: logical Replication Anjul Tyagi <anjul@ibosstech-us.com>
2018-08-20 06:24 ` Re: logical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 06:33 ` Re: logical Replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-08-20 06:42 ` Re: logical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 06:54 ` Re: logical Replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-08-20 07:04 ` Re: logical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 07:20 ` Re: logical Replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-08-20 07:40 ` Re: logical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 07:44 ` Re: logical Replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-08-20 07:59 ` Re: logical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 08:27 ` Re: logical Replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-08-20 08:58 ` Re: logical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 09:41 ` Re: logical Replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-08-20 10:07 ` Re: logical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 10:29 ` Re: logical Replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-08-20 10:37 ` Re: logical Replication pavan95 <pavan.postgresdba@gmail.com>
2018-08-20 10:58 ` Re: logical Replication Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-08-22 21:14 ` Re: logical Replication Maletin von Oertzen <oertzen@gmail.com>
2020-11-19 20:38 Logical replication Yambu <hyambu@gmail.com>
2020-11-19 20:40 ` Re: Logical replication David G. Johnston <david.g.johnston@gmail.com>
2020-11-19 20:46 ` Re: Logical replication Holger Jakobs <holger@jakobs.com>
2020-11-19 21:14 ` Re: Logical replication Yambu <hyambu@gmail.com>
2023-05-22 18:58 Logical Replication André Rodrigues <db.andre@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox