Just a quick question: what would be the most efficient way to transfer data (say: a table) between 2 PostgreSQL databases both hosted on Aiven? Transfer should happen on a regular basis, automated.
Thanks,
Stephan
Hi Stephan!
I would say that there are two ways to achieve the goal of transferring the data between 2 PG instances:
- A scheduled batch oriented solution: Using pg_cron extension to schedule a refresh with a query using the postgres-fdw to connect from the secondary to the primary
- A near real-time streaming solution, based on Apache Kafka, the Debezium source Connector to PostgreSQL and the JDBC sink to PostgreSQL.
The former is simpler since it requires only PG and a couple of extensions but has the drawback of classic batch oriented replication. The latter has the benefit of providing updates in near real time via change data capture using PostgreSQL WAL logs.
1 Like
One efficient way to transfer data between two PostgreSQL databases hosted on Aiven is to use the built-in replication features like Logical or Streaming Replication. Set up replication between the source and destination databases, ensuring real-time or selective data transfer based on your requirements. Automate the data transfer process using cron jobs or a scheduler service, triggering a script that extracts data from the source and inserts it into the destination database.
Best Regard
Danish Hafeez | QA Assistant
ICTInnovations
1 Like
I’d second the built-in replication if you can make it work as it’s likely to be best-supported. Physical replication is simplest, and may avoid load in some cases, but PostgreSQL’s got some very powerful logical replication options which may be even better (and which are more likely to work in a locked-down environment). Usually this requires access you don’t have, but the aiven_extras extension can be used to access this functionality on Aiven.