0

Within "PostgreSQL Source WAL Heartbeat https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReleaseNotes.html#CHAP_ReleaseNotes.DMS230", what does heartbeat frequency represents? The AWS docs doesn't explain what this unit of time is for exactly. At first glance, is it supposed to be the amount of time WAL files are held onto disk before released?

Not much documentation with how DMS updates the restart_lsn based on the heartbeat frequency parameter.

0

It sets how frequently the dummy query is executed. I have enabled the heartbeat with the default frequency of 5 minutes, and in my DMS task logs I see this line when the task starts:

[SOURCE_CAPTURE ]I: Heartbeat mechanism is set to 5 minutes at schema 'public' (postgres_endpoint_util.c:1502)

And then the following line every 5 minutes:

[SOURCE_CAPTURE ]I: Heartbeat was signaled successfully (postgres_endpoint_util.c:1566)

Please note that if the DMS task stops or fails, the heartbeat will stop as well, and disk usage on the Postgres instance will start growing quickly.

I'm not actually sure when the heartbeat is useful, because if the task is running I expect restart_lsn to be updated frequently with or without the heartbeat queries.

  • Thanks for the response! Any idea though what does a "dummy query" mean and how does that exactly move the restart_lsn? I've noticed when there's NO data inserts/modifications to postgres (source endpoint), this heartbeat will continue to move the restart_lsn. That seems weird to me that the restart_lsn is moving despite no real data changes (WAL?) happening on the surface – David Han Feb 14 at 19:10
  • updated my previous comment. – David Han Feb 14 at 19:10
  • To be honest, I'm quite confused myself. This example seems to indicate that whenever pg_logical_slot_get_changes is called, the value for restart_lsn is updated. I have no idea if the "dummy query" mentioned in the DMS documentation is just doing that, or something else. – dhinus Feb 16 at 12:44
0

It's infuriating that the only mention of this seemingly crucial piece of configuration is in the release notes from a major version ago...

Added a write-ahead log (WAL) heartbeat (that is, running dummy queries) for replication from a PostgreSQL source. This feature was added so that idle logical replication slots don't hold onto old WAL logs, which can result in storage full situations on the source. This heartbeat keeps restart_lsn moving and prevents storage full scenarios.

I'm using DMS to CDC data from RDS (postgres) to Redshift. The heartbeat interval is currently set to the default of 5 minutes.

The RDS instance is experiencing a heavy (for us) write load. As a result, we are accumulating transaction logs at an increased rate.

I'm noticing a sawtooth pattern for transaction log disk usage on RDS. Peculiarly, the sawtooth seems to be on a 5 minute interval (matching the interval?).

I had an RDS instance run out of disk when testing because it was sitting "idle", building replication log.

The heartbeat setting seemed to have resolved this problem when testing. However, in production, due to the 5 min jaggedness, I'm wondering if Postgres is holding onto WAL longer than it actually needs to as a result of the heartbeat...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.