3

I am trying to use the AWS Database Migration Service to migrate data from an RDS Postgres instance to an Aurora MySQL instance.

Database Migration Service requires the wal_level to be set as logical in the source Postgres DB. But when done, Postgres started consuming a lot of disk space to the point where the instance became unusable.

How long are the WAL logs retained? Are there other settings that can be enabled so disk space use is limited?

3

This is an AWS DMS issue.

DMS has recently added a feature WAL heartbeat [1] (run dummy queries) for replication from a PostgreSQL source so idle logical replication slots do not hold on to old WAL logs which may result in storage full situations on the source. This heartbeat keeps restart_lsn moving and prevents storage full scenarios.

  1. Login to DMS Console.
  2. Stop all the tasks related to the source endpoint
  3. Click on "Endpoints" from left side selection pane.
  4. Select the source Endpoint which you are using, as per the task.
  5. Click on "Modify" from the top of the screen.
  6. Expand "Advanced".
  7. Under extra connection attribute, please add this:

    heartbeatenable=Y;heartbeatFrequency=1

HeartbeatEnable – set to true (default is false) HeartbeatSchema – schema for heartbeat artifacts (default is public) HeartbeatFrequency – heartbeat frequency in minutes (default is 5 and the minimum value is 1)

  1. Click on "Modify". You will be automatically redirected to the Endpoints screen.
  2. Select the endpoint again and click on "Test Connection".
  3. It should automatically start the testing. Wait for it to show "Successful".

Stopping the task alone will not clear replication slot, so the storage usage will still increase when the task is in the stopped state, you will need to delete task to clear the slot.

To clear up the replication slots use the following commands -

SELECT * FROM pg_replication_slots ; --finds all replication slots
select pg_drop_replication_slot('NameOfSlot'); -- you will get the name of the slot from the first command

References: [1] PostgreSQL Source WAL Heartbeat https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReleaseNotes.html#CHAP_ReleaseNotes.DMS230

  • Are the heartbeat setting case sensitive? The release notes says to set the value to true not Y, is it the same? This usage is not documented anywhere apart from in the release notes and this exact SO question. – Davos Aug 31 '18 at 6:56
  • 1
    They are the same. – Aravindan R Aug 31 '18 at 21:47

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.