Thread: Help diagnosing replication (copy) error

On 3/8/24 13:50, Steve Baldwin wrote:
> Hi,
> 
> I'm in the process of migrating a cluster from 15.3 to 16.2. We have a 
> 'zero downtime' requirement so I'm using logical replication to create 
> the new cluster and then perform the switch in the application.
> 
> I have a situation where all but one table have done their initial copy. 
> The remaining table is the largest (of course), and the replication slot 
> that is assigned for the copy 
> (pg_378075177_sync_60067_7343845372910323059) is showing as 
> 'active=false' if I select from pg_replication_slots on the publisher.

What are the rest of the values in pg_replication_slots?

Is there data in the subscriber side table?

What are the publisher and subscriber configurations?


> 
> I've checked the recent logs for both the publishing cluster and the 
> subscribing cluster but I can't see any replication errors. I guess I 
> could have missed them, but it doesn't seem like anything is being 
> 'retried' like I've seen in the past with replication errors.
> 
> I've used this mechanism for zero-downtime upgrades multiple times in 
> the past, and have recently used it to upgrade smaller clusters from 
> 15.x to 16.2 without issue.
> 
> The clusters are hosted on AWS RDS, so I have no access to the servers, 
> but if that's the only way to diagnose the issue, I can create a support 
> case.
> 
> Does anyone have any suggestions as to where I should look for the issue?
> 
> Thanks,
> 
> Steve

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/8/24 14:04, Steve Baldwin wrote:
> 
> 
> On Sat, Mar 9, 2024 at 8:56 AM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
> 
>     What are the rest of the values in pg_replication_slots?
> 
> b2bcreditonline=> select * from pg_replication_slots;
>                    slot_name                  |  plugin  | slot_type | 
> datoid |    database     | temporary | active | active_pid | xmin | 
> catalog_xmin |  restart_lsn  | confirmed_flush_lsn | wal_status | 
> safe_wal_size | two_phase
>
---------------------------------------------+----------+-----------+--------+-----------------+-----------+--------+------------+------+--------------+---------------+---------------------+------------+---------------+-----------
>   b2bcreditonline_prod_e_master               | pgoutput | logical   | 
>   16404 | b2bcreditonline | f         | t      |      13700 |      |   
>   456150233 | 2E2F/AC07B760 | 2E2F/AEEEA478       | reserved   |         
>        | f
>   b2bcreditonline_prod_sandbox_d_master       | pgoutput | logical   | 
>   16404 | b2bcreditonline | f         | t      |       9232 |      |   
>   456150233 | 2E2F/AC07B760 | 2E2F/AEEEA560       | reserved   |         
>        | f
>   b2bcreditonline_prod_e_master_only          | pgoutput | logical   | 
>   16404 | b2bcreditonline | f         | t      |      13710 |      |   
>   456150233 | 2E2F/AC07B760 | 2E2F/AEEEA560       | reserved   |         
>        | f
>   pg_378075177_sync_60067_7343845372910323059 | pgoutput | logical   | 
>   16404 | b2bcreditonline | f         | f      |            |      |   
>   455719618 | 2E2A/1C0972E0 | 2E2A/1C097318       | extended   |         
>        | f
>   b2bcreditonline_prod_e_shard                | pgoutput | logical   | 
>   16404 | b2bcreditonline | f         | t      |      13718 |      |   
>   456150233 | 2E2F/AC07B760 | 2E2F/AEEEA560       | reserved   |         
>        | f
> (5 rows)
> 
>     Is there data in the subscriber side table?
> 
> No there is not, although when I did a 'select count(*) from {table}' it 
> took several minutes to return 0 rows.
> 
>     What are the publisher and subscriber configurations?
> 
> Not sure which settings, but here's a few.


I should been clearer.

What are the CREATE PUBLICATION and CREATE SUBSCRIPTION statements?

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/8/24 14:04, Steve Baldwin wrote:
> 
> 
> On Sat, Mar 9, 2024 at 8:56 AM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
> 
>     What are the rest of the values in pg_replication_slots?
> 
> b2bcreditonline=> select * from pg_replication_slots;
>                    slot_name                  |  plugin  | slot_type | 
> datoid |    database     | temporary | active | active_pid | xmin | 
> catalog_xmin |  restart_lsn  | confirmed_flush_lsn | wal_status | 
> safe_wal_size | two_phase
>
---------------------------------------------+----------+-----------+--------+-----------------+-----------+--------+------------+------+--------------+---------------+---------------------+------------+---------------+-----------
>   b2bcreditonline_prod_e_master               | pgoutput | logical   | 
>   16404 | b2bcreditonline | f         | t      |      13700 |      |   
>   456150233 | 2E2F/AC07B760 | 2E2F/AEEEA478       | reserved   |         
>        | f
>   b2bcreditonline_prod_sandbox_d_master       | pgoutput | logical   | 
>   16404 | b2bcreditonline | f         | t      |       9232 |      |   
>   456150233 | 2E2F/AC07B760 | 2E2F/AEEEA560       | reserved   |         
>        | f
>   b2bcreditonline_prod_e_master_only          | pgoutput | logical   | 
>   16404 | b2bcreditonline | f         | t      |      13710 |      |   
>   456150233 | 2E2F/AC07B760 | 2E2F/AEEEA560       | reserved   |         
>        | f
>   pg_378075177_sync_60067_7343845372910323059 | pgoutput | logical   | 
>   16404 | b2bcreditonline | f         | f      |            |      |   
>   455719618 | 2E2A/1C0972E0 | 2E2A/1C097318       | extended   |         
>        | f
>   b2bcreditonline_prod_e_shard                | pgoutput | logical   | 
>   16404 | b2bcreditonline | f         | t      |      13718 |      |   
>   456150233 | 2E2F/AC07B760 | 2E2F/AEEEA560       | reserved   |         
>        | f
> (5 rows)
> 

For future reference the above would be easier to follow if you did \x 
before the select * from pg_replication_slots;

Someone with more experience will need to comment further but to me:

restart_lsn     confirmed_flush_lsn
2E2A/1C0972E0    2E2A/1C097318

does not look like enough data was transferred before the slot stopped 
transmitting.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/8/24 14:50, Steve Baldwin wrote:

> Hi,
>
> I'm in the process of migrating a cluster from 15.3 to 16.2. We have a 
> 'zero downtime' requirement so I'm using logical replication to create 
> the new cluster and then perform the switch in the application.
>
> I have a situation where all but one table have done their initial 
> copy. The remaining table is the largest (of course), and the 
> replication slot that is assigned for the copy 
> (pg_378075177_sync_60067_7343845372910323059) is showing as 
> 'active=false' if I select from pg_replication_slots on the publisher.
>
> I've checked the recent logs for both the publishing cluster and the 
> subscribing cluster but I can't see any replication errors. I guess I 
> could have missed them, but it doesn't seem like anything is being 
> 'retried' like I've seen in the past with replication errors.
>
> I've used this mechanism for zero-downtime upgrades multiple times in 
> the past, and have recently used it to upgrade smaller clusters from 
> 15.x to 16.2 without issue.
>
> The clusters are hosted on AWS RDS, so I have no access to the 
> servers, but if that's the only way to diagnose the issue, I can 
> create a support case.
>
> Does anyone have any suggestions as to where I should look for the issue?
>
> Thanks,
>
> Steve

In our setup we're logically replicating a 450G database hosted on real 
hardware to an RDS instance.

Multiple times we've had replication simply stop and we could never find 
any reason for that on either publisher or subscriber.

The *only* solution that ever worked in these cases was dropping the 
subscription in RDS and re-creating it with (copy_data = false).

At that point replication picks right up again for new transactions 
*but* at the expense of losing all of the WAL that should have been 
replicated during the outage.  I wrote a python based "logical 
replication fixer" to fill in those gaps.

Given that the subscriber is the one that initiates the connection to 
the publisher and that as soon as the subscription is dropped and 
restarted replication resumes my hunch is that this is squarely on RDS.  
With both publisher and subscriber on RDS as in your case YMMV.

RDS is a black box--who knows what's really going on there?  It would be 
interesting to see what the response is after you open a support case.  
I hope you'll be able to share that with the list.

Jeff

On 3/8/24 22:26, Steve Baldwin wrote:
> 
> 
> On Sat, Mar 9, 2024 at 11:06 AM Jeff Ross <jross@openvistas.net 
> <mailto:jross@openvistas.net>> wrote:
> 
> 
>     RDS is a black box--who knows what's really going on there?  It
>     would be
>     interesting to see what the response is after you open a support case.
>     I hope you'll be able to share that with the list.
> 
> This is very mysterious. I logged the case, and before it had been 
> picked up by an analyst, the issue somehow resolved itself without me 
> doing anything.

Is the case still active?
Can you get information from them about what they saw?

I am not big believer in coincidences, that you reported a problem and 
then the problem disappeared.

> 
> I now have 418M+ rows in the table that it got stuck on.
> 
> :shrug:
> 
> Thanks Adrian and Jeff for responding.
> 
> Steve
> 
>     Jeff
> 
> 
> 
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com