docs/integrations/sources/postgres/postgres-troubleshooting.md
import Tabs from '@theme/Tabs'; import TabItem from '@theme/TabItem';
There are some notable shortcomings associated with the Xmin replication method:
v0.58.0 or newerStandard as Replication Method.DELETE/INSERT/UPDATE. For example, changes made using TRUNCATE/ALTER will not appear in logs and therefore in your destination.DELETE statements only contain primary keys. All other data fields are unset.TIMESTAMPTIMESTAMP_WITH_TIMEZONETIMETIME_WITH_TIMEZONEDATEBITBOOLEANTINYINT/SMALLINTINTEGERBIGINTFLOAT/DOUBLEREALNUMERIC/DECIMALCHAR/NCHAR/NVARCHAR/VARCHAR/LONGVARCHARBINARY/BLOB:::warning
Not all implementations or deployments of a database will be the same. This section lists specific limitations and known issues with the connector based on how or where it is deployed.
:::
AWS Aurora implements a CDC caching layer that is incompatible with Airbyte's CDC implementation. To use Airbyte with AWS Aurora, disable the CDC caching layer. Disable CDC caching by setting the rds.logical_wal_cache parameter to 0 in the AWS Aurora parameter group.
In addition, if you are seeing timeout errors, set apg_write_forward.idle_session_timeout to 1200000 (20 minutes) in the AWS Aurora parameter group.
While postgres compatible, TimescaleDB does not support CDC replication.
In some cases with a highly-compressed database, you may receive an error like transparent decompression only supports tableoid system column. This error indicates that the cursor column chosen for the sync is compressed and cannot be used. To resolve this issue, you can change the cursor column to a non-compressed column.
When using CDC with Azure PG Flex, if a failover to a new leader happens, your CDC replication slot will not be re-created automatically. You will need to manually re-create the replication slot on the new leader, and initiate a reset of the connection in Airbyte.
When the connector is reading from a Postgres replica that is configured as a Hot Standby, any update from the primary server will terminate queries on the replica after a certain amount of time, default to 30 seconds. This default waiting time is not enough to sync any meaning amount of data. See the Handling Query Conflicts section in the Postgres documentation for detailed explanation.
Here is the typical exception:
Caused by: org.postgresql.util.PSQLException: FATAL: terminating connection due to conflict with recovery
Detail: User query might have needed to see row versions that must be removed.
Hint: In a moment you should be able to reconnect to the database and repeat your command.
Possible solutions include:
hot_standby_feedback to true on the replica server. This parameter will prevent the primary server from deleting the write-ahead logs when the replica is busy serving user queries. However, the downside is that the write-ahead log will increase in size.max_standby_archive_delay and max_standby_streaming_delay to be larger than the amount of time needed to complete the data sync. However, it is usually hard to tell how much time it will take to sync all the data. This approach is not very practical.Normally under the CDC mode, the Postgres source will first run a full refresh sync to read the snapshot of all the existing data, and all subsequent runs will only be incremental syncs reading from the write-ahead logs (WAL). However, occasionally, you may see full refresh syncs after the initial run. When this happens, you will see the following log:
Saved offset is before Replication slot's confirmed_flush_lsn, Airbyte will trigger sync from scratch
The root causes is that the WALs needed for the incremental sync has been removed by Postgres. This can occur under the following scenarios:
pg_wal directory, Postgres will purge or archive the WAL files. This scenario is preventable. Possible solutions include:
wal_keep_size. If no unit is provided, it is in megabytes, and the default is 0. See detailed documentation here. The downside of this approach is that more disk space will be needed.Some larger tables may encounter an error related to the temporary file size limit such as temporary file size exceeds temp_file_limit. To correct this error increase the temp_file_limit.
When a database experiences Xmin wraparound, the replication performance will be degraded. Furthermore, data that has already been synced may be resynced again. When setting up a Postgres source connector or at the beginning of the sync, the connector will check if an Xmin wraparound exists. If so, the connector returns a config error, reminding the user to switch to the CDC replication method.
To customize the JDBC connection beyond common options, specify additional supported JDBC URL parameters as key-value pairs separated by the symbol & in the JDBC URL Parameters (Advanced) field.
Example: key1=value1&key2=value2&key3=value3
These parameters will be added at the end of the JDBC URL that Airbyte will use to connect to your Postgres database.
The connector now supports connectTimeout and defaults to 60 seconds. Setting connectTimeout to 0 seconds will set the timeout to the longest time available.
Note: Do not use the following keys in JDBC URL Params field as they will be overwritten by Airbyte:
currentSchema, user, password, ssl, and sslmode.
The Postgres connector may need some time to start processing the data in the CDC mode in the following scenarios:
The connector waits for the default initial wait time of 20 minutes (1200 seconds). Setting the parameter to a longer duration will result in slower syncs, while setting it to a shorter duration may cause the connector to not have enough time to create the initial snapshot or read through the change logs. The valid range is 120 seconds to 2400 seconds.
If you know there are database changes to be synced, but the connector cannot read those changes, the root cause may be insufficient waiting time. In that case, you can increase the waiting time (example: set to 600 seconds) to test if it is indeed the root cause. On the other hand, if you know there are no database changes, you can decrease the wait time to speed up the zero record syncs.
When using the Read Changes using Write-Ahead Log (CDC) update method, you might encounter a situation where your initial sync is successful, but further syncs fail. You may also notice that the confirmed_flush_lsn column of the server's pg_replication_slots view doesn't advance as expected.
This is a general issue that affects databases, schemas, and tables with small transaction volumes. There are complexities in the way PostgreSQL disk space can be consumed by WAL files, and these can cause issues for the connector when dealing with low transaction volumes. Airbyte's connector depends on Debezium. These complexities are outlined in their documentation, if you want to learn more.
The easiest way to fix this issue is to add one or more tables with high transaction volumes to the Airbyte publication. You do not need to actually sync these tables, but adding them to the publication will advance the log sequence number (LSN), ensuring the sync can succeed without you giving Airbyte write access to the database. However, this may greatly increase disk consumption.
ALTER PUBLICATION <publicationName> ADD TABLE <high_volume_table>;
If you do not want to increase disk consumption, use the following solutions, which require write access.
To fix the issue when reading against primary or standalone, artificially add events to a heartbeat table the Airbyte user can write to.
Create an airbyte_heartbeat table in the database and schema being tracked.
CREATE TABLE airbyte_heartbeat (
id SERIAL PRIMARY KEY,
timestamp TIMESTAMP NOT NULL DEFAULT current_timestamp,
text TEXT
);
Add this table to the airbyte publication.
ALTER PUBLICATION <publicationName> ADD TABLE airbyte_heartbeat;
In the Postgres source connector in Airbyte, configure the Debezium heartbeat query property. For example:
INSERT INTO airbyte_heartbeat (text) VALUES ('heartbeat')
Airbyte periodically executes this query on the airbyte_heartbeat table.
To fix the issue when reading against a read replica:
pg_cron is a cron-based job scheduler for PostgreSQL that runs inside the database as an extension so you can schedule PostgreSQL commands directly from the database.
<Tabs> <TabItem value="Google Cloud" label="Google Cloud" default>Ensure your PostgreSQL instance is version 10 or higher. Version 10 is the minimum version that supports pg_cron.
SELECT version();
Configure your database flags to enable pg_cron. For help with this, see Google Cloud's docs.
Set the cloudsql.enable_pg_cron flag to on. For more information, see Google Cloud's docs.
Set the shared_preload_libraries flag to include pg_cron.
shared_preload_libraries = 'pg_cron'
If you already have other libraries in this parameter, add pg_cron to the list, separating each library with a comma.
shared_preload_libraries = 'pg_cron,pg_stat_statements'
Restart your PostgreSQL instance. For help with this, see Google Cloud's docs.
PostgreSQL now preloads the pg_cron extension when the instance starts.
Ensure your RDS for PostgreSQL instance is version 12.5 or later. pg_cron requires version 12.5 and later.
SELECT version();
Modify the parameter group associated with your PostgreSQL database instance to enable pg_cron. For help modifying parameter groups, see the AWS docs.
If your RDS for PostgreSQL database instance uses the rds.allowed_extensions parameter to spcify which extensions can be installed, add pg_cron to that list.
Edit the custom parameter group associated with your PostgreSQL DB instance. Modify the shared_preload_libraries parameter to include the value pg_cron.
Reboot your PostgreSQL database instance. For help, see the AWS docs.
PostgreSQL now preloads the pg_cron extension when the instance starts.
Verify pg_cron was successfully added to shared_preload_libraries.
show shared_preload_libraries
Enable the pg_cron extension, create a periodic_log (heartbeat) table, and schedule a cron job.
CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE TABLE periodic_log (
log_id SERIAL PRIMARY KEY,
log_time TIMESTAMP DEFAULT current_timestamp
);
SELECT cron.schedule(
'periodic_logger', -- job name
'*/1 * * * *', -- cron expression (every minute)
$$INSERT INTO periodic_log DEFAULT VALUES$$ -- the SQL statement to run
);
Verify the scheduled job.
SELECT * FROM cron.job;
Verify the periodic update.
SELECT * FROM periodic_log ORDER BY log_time DESC;
Alter the publication to include this table on the primary.
ALTER PUBLICATION airbyte_publication ADD TABLE periodic_log;
Sync normally from the primary to the replica.
If you need to stop syncing later, unschedule the cron job.
SELECT cron.unschedule('periodic_logger');