Cloud SQL for PostgreSQL
Airbyte's certified Postgres connector offers the following features:
- Multiple methods of keeping your data fresh, including Change Data Capture (CDC) and replication using the xmin system column.
- All available sync modes, providing flexibility in how data is delivered to your destination.
- Reliable replication at any table size with checkpointing and chunking of database reads.
Quick Start
Here is an outline of the minimum required steps to configure a connection to Postgres on Google Cloud SQL:
- Create a dedicated read-only Postgres user with permissions for replicating data
- Create a new Postgres source in the Airbyte UI using
xmin
system column - (Airbyte Cloud Only) Allow inbound traffic from Airbyte IPs
Once this is complete, you will be able to select Postgres as a source for replicating data.
Step 1: Create a dedicated read-only Postgres user
These steps create a dedicated read-only user for replicating data. Alternatively, you can use an existing Postgres user in your database. To create a user, first connect to your database. If you are getting started, you can use Cloud Shell to connect directly from the UI.
The following commands will create a new user:
CREATE USER <user_name> PASSWORD 'your_password_here';
Now, provide this user with read-only access to relevant schemas and tables. Re-run this command for each schema you expect to replicate data from (e.g. public
):
GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <user_name>;
Step 2: Create a new Postgres source in Airbyte UI
From your Airbyte Cloud or Airbyte Open Source account, select Sources
from the left navigation bar, search for Postgres
, then create a new Postgres source.
To fill out the required information:
- Enter the hostname, port number, and name for your Postgres database.
- You may optionally opt to list each of the schemas you want to sync. These are case-sensitive, and multiple schemas may be entered. By default,
public
is the only selected schema. - Enter the username and password you created in Step 1.
- Select an SSL mode. You will most frequently choose
require
orverify-ca
. Both of these always require encryption.verify-ca
also requires certificates from your Postgres database. See here to learn about other SSL modes and SSH tunneling. - Select
Standard (xmin)
from available replication methods. This uses the xmin system column to reliably replicate data from your database.- If your database is particularly large (> 500 GB), you will benefit from configuring your Postgres source using logical replication (CDC).
Step 3: (Airbyte Cloud Only) Allow inbound traffic from Airbyte IPs.
If you are on Airbyte Cloud, you will always need to modify your database configuration to allow inbound traffic from Airbyte IPs. To allowlist IPs in Cloud SQL:
- In your Google Cloud SQL database dashboard, select
Connections
from the left menu. Then, selectAdd Network
under theConnectivity
section.
- Add a new network, and enter the Airbyte's IPs, which you can find in our Airbyte Security documentation.
Now, click Set up source
in the Airbyte UI. Airbyte will now test connecting to your database. Once this succeeds, you've configured an Airbyte Postgres source!