search

airbytehq / airbyte

The leading data integration platform for ETL / ELT data pipelines from APIs, databases & files to data warehouses, data lakes & data lakehouses. Both self-hosted and Cloud-hosted.
https://airbyte.com
Other
15.42k stars 3.98k forks source link

[source-postgres] Transaction logs not being cleaned up #42917

Closed joeybenamy closed 1 month ago

joeybenamy commented 1 month ago

Connector Name

source-postgres

Connector Version

3.6.11

What step the error happened?

During the sync

Relevant information

When using Airbyte to sync Postgres 16 to Snowflake in CDC incremental mode, we are observing that our transaction logs in Postgres are not cleaning up after each sync as much as we expect. Another user reports that is happening for them with syncing Postgres 15 to Redshift. Both of us have also observed seemingly random full refreshes of the data. For us, our Postgres replication slot does not appear to advance.

Feedback from AWS support:

I have been inspecting and indeed I can see that there an increase and drops in FreeStorageSpace and OldestReplicationSlotLag that seem to correlate. However, indeed there seem to be WAL data being held.

Normally when the WAL data is consumed by the consumer the space should be released. However, if the replication slot become inactive (consumer disconnected for example) WAL data will remains till is not longer necessary by the slot. Please, be aware that I have not visibility over your consumer configuration but it does seem that might become inactive and the WAL data keep being accumulated. Therefore, could you verify on the replication slot status, under "active" should be T > active F >not active.

If the slot is inactive I would suggest to inspect on the consumer side as when become inactive WAL data will remains.

Inspection of replication slot:

SELECT SLOT_NAME
     , PG_CURRENT_WAL_LSN()
     , REDO_LSN
     , RESTART_LSN
     , PG_SIZE_PRETTY(PG_WAL_LSN_DIFF(PG_CURRENT_WAL_LSN(), RESTART_LSN)) AS LAG
     , ACTIVE
FROM PG_CONTROL_CHECKPOINT(), PG_REPLICATION_SLOTS;

+------------+------------------+-------------+-------------+------+------+
|slot_name   |pg_current_wal_lsn|redo_lsn     |restart_lsn  |lag   |active|
+------------+------------------+-------------+-------------+------+------+
|airbyte_slot|1DE0/894564C8     |1DE0/75FDF240|1D8D/55E49CA0|333 GB|false |
+------------+------------------+-------------+-------------+------+------

Screenshots of transaction log and replication slot lag metrics: image (2) image (1) image

Platform version: 0.63.10 Destination: Snowflake Destination connector version: 3.11.4

Slack conversation with another user experiencing this issue and Marcos from Airbyte: https://airbytehq.slack.com/archives/C021JANJ6TY/p1722433610435219

Relevant log output

Contribute

evantahler commented 1 month ago

To confirm, the Inspection of replication slot query only shows one slot. Is there only one replication slot on the host - the one that airbyte uses, or could there be others?

joeybenamy commented 1 month ago

To confirm, the Inspection of replication slot query only shows one slot. Is there only one replication slot on the host - the one that airbyte uses, or could there be others?

Good question. In our case, we only have one replication slot in total, and it is the one that Airbyte uses.

rodireich commented 1 month ago

Hi @joeybenamy Thanks for confirming a single replication slot. I have a bunch of other questions - trying to understand this behavior you're experiencing.

Can you please share information about how log retention is configured. If I can ask for the output from 

select *
from pg_settings

I'm looking mainly for min_wal_size and max_wal_size.

Another question I have - is this a standalone server or is it replicated to/from another node. If yes could you please share how often it happens?

Also, is this plain RDS or an AWS Aurora server (or some other type?)

thanks

rodireich commented 1 month ago

If you can also share logs of an airbyte sync either here or on slack (or a more secure mean - I can help with that)

joeybenamy commented 1 month ago

Hi @joeybenamy Thanks for confirming a single replication slot. I have a bunch of other questions - trying to understand this behavior you're experiencing.

Can you please share information about how log retention is configured. If I can ask for the output from

select *
from pg_settings

I'm looking mainly for min_wal_size and max_wal_size.

Another question I have - is this a standalone server or is it replicated to/from another node. If yes could you please share how often it happens?

Also, is this plain RDS or an AWS Aurora server (or some other type?)

thanks

Thanks for looking into this. Happy to provide anything you need. Here are the pg_settings related to WAL:

+-----------------------------+---------+----+--------------------------------------------------+----------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------+-------+------------------+-------+----------+-----------------------------------------+---------+---------+---------------------------------+----------+---------------+
|name                         |setting  |unit|category                                          |short_desc                                                                                                |extra_desc                                                                                                                                 |context   |vartype|source            |min_val|max_val   |enumvals                                 |boot_val |reset_val|sourcefile                       |sourceline|pending_restart|
+-----------------------------+---------+----+--------------------------------------------------+----------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------+-------+------------------+-------+----------+-----------------------------------------+---------+---------+---------------------------------+----------+---------------+
|max_slot_wal_keep_size       |-1       |MB  |Replication / Sending Servers                     |Sets the maximum WAL size that can be reserved by replication slots.                                      |Replication slots will be marked as failed, and segments released for deletion or recycling, if this much space is occupied by WAL on disk.|sighup    |integer|default           |-1     |2147483647|null                                     |-1       |-1       |null                             |null      |false          |
|max_wal_senders              |35       |null|Replication / Sending Servers                     |Sets the maximum number of simultaneously running WAL sender processes.                                   |null                                                                                                                                       |postmaster|integer|configuration file|0      |262143    |null                                     |10       |35       |/rdsdbdata/config/postgresql.conf|117       |false          |
|max_wal_size                 |6144     |MB  |Write-Ahead Log / Checkpoints                     |Sets the WAL size that triggers a checkpoint.                                                             |null                                                                                                                                       |sighup    |integer|configuration file|2      |2147483647|null                                     |1024     |6144     |/rdsdbdata/config/postgresql.conf|114       |false          |
|min_wal_size                 |192      |MB  |Write-Ahead Log / Checkpoints                     |Sets the minimum size to shrink the WAL to.                                                               |null                                                                                                                                       |sighup    |integer|configuration file|2      |2147483647|null                                     |80       |192      |/rdsdbdata/config/postgresql.conf|56        |false          |
|track_wal_io_timing          |on       |null|Statistics / Cumulative Query and Index Statistics|Collects timing statistics for WAL I/O activity.                                                          |null                                                                                                                                       |superuser |bool   |configuration file|null   |null      |null                                     |off      |on       |/rdsdbdata/config/postgresql.conf|98        |false          |
|wal_block_size               |8192     |null|Preset Options                                    |Shows the block size in the write ahead log.                                                              |null                                                                                                                                       |internal  |integer|default           |8192   |8192      |null                                     |8192     |8192     |null                             |null      |false          |
|wal_buffers                  |8192     |8kB |Write-Ahead Log / Settings                        |Sets the number of disk-page buffers in shared memory for WAL.                                            |null                                                                                                                                       |postmaster|integer|default           |-1     |262143    |null                                     |-1       |8192     |null                             |null      |false          |
|wal_compression              |zstd     |null|Write-Ahead Log / Settings                        |Compresses full-page writes written in WAL file with specified method.                                    |null                                                                                                                                       |superuser |enum   |configuration file|null   |null      |{pglz,lz4,zstd,on,off}                   |off      |zstd     |/rdsdbdata/config/postgresql.conf|107       |false          |
|wal_consistency_checking     |         |null|Developer Options                                 |Sets the WAL resource managers for which WAL consistency checks are done.                                 |Full-page images will be logged for all data blocks and cross-checked against the results of WAL replay.                                   |superuser |string |default           |null   |null      |null                                     |         |         |null                             |null      |false          |
|wal_decode_buffer_size       |524288   |B   |Write-Ahead Log / Recovery                        |Buffer size for reading ahead in the WAL during recovery.                                                 |Maximum distance to read ahead in the WAL to prefetch referenced data blocks.                                                              |postmaster|integer|default           |65536  |1073741823|null                                     |524288   |524288   |null                             |null      |false          |
|wal_init_zero                |on       |null|Write-Ahead Log / Settings                        |Writes zeroes to new WAL files before first use.                                                          |null                                                                                                                                       |superuser |bool   |default           |null   |null      |null                                     |on       |on       |null                             |null      |false          |
|wal_keep_size                |2048     |MB  |Replication / Sending Servers                     |Sets the size of WAL files held for standby servers.                                                      |null                                                                                                                                       |sighup    |integer|configuration file|0      |2147483647|null                                     |0        |2048     |/rdsdbdata/config/postgresql.conf|108       |false          |
|wal_level                    |logical  |null|Write-Ahead Log / Settings                        |Sets the level of information written to the WAL.                                                         |null                                                                                                                                       |postmaster|enum   |configuration file|null   |null      |{minimal,replica,logical}                |replica  |logical  |/rdsdbdata/config/postgresql.conf|120       |false          |
|wal_log_hints                |off      |null|Write-Ahead Log / Settings                        |Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modification.    |null                                                                                                                                       |postmaster|bool   |default           |null   |null      |null                                     |off      |off      |null                             |null      |false          |
|wal_receiver_create_temp_slot|off      |null|Replication / Standby Servers                     |Sets whether a WAL receiver should create a temporary replication slot if no permanent slot is configured.|null                                                                                                                                       |sighup    |bool   |configuration file|null   |null      |null                                     |off      |off      |/rdsdbdata/config/postgresql.conf|109       |false          |
|wal_receiver_status_interval |10       |s   |Replication / Standby Servers                     |Sets the maximum interval between WAL receiver status reports to the sending server.                      |null                                                                                                                                       |sighup    |integer|default           |0      |2147483   |null                                     |10       |10       |null                             |null      |false          |
|wal_receiver_timeout         |30000    |ms  |Replication / Standby Servers                     |Sets the maximum wait time to receive data from the sending server.                                       |null                                                                                                                                       |sighup    |integer|configuration file|0      |2147483647|null                                     |60000    |30000    |/rdsdbdata/config/postgresql.conf|110       |false          |
|wal_recycle                  |on       |null|Write-Ahead Log / Settings                        |Recycles WAL files by renaming them.                                                                      |null                                                                                                                                       |superuser |bool   |default           |null   |null      |null                                     |on       |on       |null                             |null      |false          |
|wal_retrieve_retry_interval  |5000     |ms  |Replication / Standby Servers                     |Sets the time to wait before retrying to retrieve WAL after a failed attempt.                             |null                                                                                                                                       |sighup    |integer|default           |1      |2147483647|null                                     |5000     |5000     |null                             |null      |false          |
|wal_segment_size             |67108864 |B   |Preset Options                                    |Shows the size of write ahead log segments.                                                               |null                                                                                                                                       |internal  |integer|default           |1048576|1073741824|null                                     |16777216 |67108864 |null                             |null      |false          |
|wal_sender_timeout           |0        |ms  |Replication / Sending Servers                     |Sets the maximum time to wait for WAL replication.                                                        |null                                                                                                                                       |user      |integer|configuration file|0      |2147483647|null                                     |60000    |0        |/rdsdbdata/config/postgresql.conf|111       |false          |
|wal_skip_threshold           |2048     |kB  |Write-Ahead Log / Settings                        |Minimum size of new file to fsync instead of writing WAL.                                                 |null                                                                                                                                       |user      |integer|default           |0      |2147483647|null                                     |2048     |2048     |null                             |null      |false          |
|wal_sync_method              |fdatasync|null|Write-Ahead Log / Settings                        |Selects the method used for forcing WAL updates to disk.                                                  |null                                                                                                                                       |sighup    |enum   |default           |null   |null      |{fsync,fdatasync,open_sync,open_datasync}|fdatasync|fdatasync|null                             |null      |false          |
|wal_writer_delay             |200      |ms  |Write-Ahead Log / Settings                        |Time between WAL flushes performed in the WAL writer.                                                     |null                                                                                                                                       |sighup    |integer|default           |1      |10000     |null                                     |200      |200      |null                             |null      |false          |
|wal_writer_flush_after       |128      |8kB |Write-Ahead Log / Settings                        |Amount of WAL written out by WAL writer that triggers a flush.                                            |null                                                                                                                                       |sighup    |integer|default           |0      |2147483647|null                                     |128      |128      |null                             |null      |false          |
+-----------------------------+---------+----+--------------------------------------------------+----------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------+-------+------------------+-------+----------+-----------------------------------------+---------+---------+---------------------------------+----------+---------------+

This is an RDS instance, not Aurora, and we don't have any replication to another RDS instance. Airbyte is the only replication slot.

rodireich commented 1 month ago

max_slot_wal_keep_size currently at -1 allows for unlimited accumulation of files under the replication slot directory.

rodireich commented 1 month ago

https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE

rodireich commented 1 month ago

@joeybenamy let us know whether the suggested change works for you and did it help with removing stale WAL files. Thanks

ashraf-gb commented 1 month ago

@rodireich I'm sorry but I believe our problem here is actually that the WAL files aren't being consumed and flushed by Airbyte.

We can limit the max_slot_wal_keep_size to avoid running our DB out of storage. But this still doesn't fix the problem with Airbyte, where syncs aren't actually consuming the WAL.

joeybenamy commented 1 month ago

@rodireich I'm sorry but I believe our problem here is actually that the WAL files aren't being consumed and flushed by Airbyte.

We can limit the max_slot_wal_keep_size to avoid running our DB out of storage. But this still doesn't fix the problem with Airbyte, where syncs aren't actually consuming the WAL.

This is what we are wondering as well.

joeybenamy commented 1 month ago

@rodireich I'm sorry but I believe our problem here is actually that the WAL files aren't being consumed and flushed by Airbyte.

We can limit the max_slot_wal_keep_size to avoid running our DB out of storage. But this still doesn't fix the problem with Airbyte, where syncs aren't actually consuming the WAL.

We've been working on this with Airbyte and this explanation aligns with what we observed:

One point I want to stress is that when a sync ends we save an updated offset in an airbyte state, but only on the next sync will we acknowledge that to the DB. This has to do with the way sources and destinations communicate over the airbyte protocol, and is meant to ensure that we only acknowledge to postgres after destination has confirmed the data reached its final destination successfully. That means in your case that log records that we read and uploaded to your warehouse will only be released on postgres on the following day.

joeybenamy commented 1 month ago

Due to the above explanation, we are no longer considering this issue to be a bug, but rather expected behavior for our use case.