Follow these instructions to replicate your Azure PostgreSQL database to your destination using Fivetran.
To connect your PostgreSQL database to Fivetran, you need:
- PostgreSQL version 8.4 - 12.4
- Your database host’s IP (e.g.,
22.214.171.124) or domain (e.g.,
- Your database’s port (usually
- TLS enabled on your database
Choose a connection method (TLS required)link
IMPORTANT: You must have TLS enabled on your database to connect to Fivetran.
Decide whether to connect your Azure PostgreSQL database directly or using an SSH tunnel. How you configure your security groups will differ depending on this decision.
Fivetran connects directly to your database instance. This is the simplest and most secure method.
If you connect directly, you must create a rule in a security group that allows Fivetran access to your database instance.
Connect using SSH
Fivetran connects to a separate server in your network that provides an SSH tunnel to your database. You must connect through SSH if your database is in an inaccessible subnet.
If you connect using SSH, you must follow our SSH tunnel instructions before proceeding to the next step. You must then configure your tunnel server’s security group to allow Fivetran access and configure the database instance’s security to allow access from the tunnel.
You must configure the firewall to grant Fivetran’s data processing servers access to your database server.
In the Azure console, open the SQL database firewall settings.
Select the Azure PostgreSQL database that you want to connect to Fivetran.
In Settings, click Connection security.
Add a new firewall rule. Enter Fivetran’s IP in both the Start IP and End IP fields to define the firewall rule’s range.
Open a connection to your master PostgreSQL database.
Create a user for Fivetran by executing the following SQL command. Replace
some-passwordwith a username and password of your choice.
CREATE USER <username> PASSWORD 'some-password';
Grant read-only accesslink
Grant the Fivetran user read-only access to all tables by running the following commands. To grant access to a schema other than PostgreSQL’s default
public schema, replace
public with the schema name.
GRANT USAGE ON SCHEMA "public" TO <username>; GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO <username>; ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO <username>;
NOTE: The last command makes sure that any future tables will be accessible to Fivetran.
If you want to grant access to multiple schemas, you must run these three commands for each schema.
Restrict access to tables (optional)link
If you want to limit Fivetran’s access to your data, grant the Fivetran user access to only the tables that you would like to sync. You need to individually grant access for each table that you want to sync. It is not possible to achieve exclusion by granting access to all tables and then revoking access for a subset of tables.
Ensure that the Fivetran user has access to the schema that contains your table(s).
GRANT USAGE ON SCHEMA "some_schema" TO <username>;
Revoke any previously granted permission to all tables in that schema.
ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" REVOKE SELECT ON TABLES FROM <username>; REVOKE SELECT ON ALL TABLES IN SCHEMA "some_schema" FROM <username>;
Repeat the following command for each table you want Fivetran to sync.
GRANT SELECT ON "some_schema"."some_table" TO <username>;
By default, any tables that you create in the future will be excluded from the Fivetran user’s access. To grant access to new tables, run the following command.
ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" GRANT SELECT ON TABLES TO <username>;
Restrict access to columns (optional)link
You can also grant the Fivetran user access to only certain columns within a table. You need to individually grant access for each column that you want to sync.
NOTE: We need access to the hidden system column
xminfor incremental updates.
Ensure that you have revoked any previously granted permission to read all columns in the table.
REVOKE SELECT ON "some_schema"."some_table" FROM <username>;
Grant permission to the specific columns you want to sync (for example,
GRANT SELECT (xmin, "some_column", "other_column") ON "some_schema"."some_table" TO <username>;
Once you restrict access to columns within a table, the Fivetran user will not have access to any new columns added to that table in the future. To grant access to new columns, you must rerun the command above.
Choose incremental update mechanismlink
To keep your data up to date after the initial sync, we use one of two incremental update methods: logical replication and XMIN. Both methods keep a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync. We support logical replication and the XMIN method on Azure PostgreSQL versions 9.5 or later. For earlier versions, we only support the XMIN method. As a result, our Azure PostgreSQL connector does not support replicating deleted data for versions before 9.5.
TIP: We recommend using logical replication as your incremental update mechanism. Learn more in our Updating data documentation.
Choose either logical replication or XMIN as your incremental update mechanism.
- To enable logical replication, proceed to the next section.
- To enable XMIN, skip ahead to the XMIN section.
IMPORTANT: You can only enable logical replication if your Azure PostgreSQL version is 10 or later.
Logical replication is based on logical decoding of the PostgreSQL write-ahead log (WAL). To enable logical replication, follow these steps:
Connect to your master database. You cannot enable logical replication on a read replica.
Ensure that your server has ample free space for the logs. Logs that Fivetran has already processed are released. However, logs are not released if replication stops (for example, if we lose access). In this case, logs may accumulate on your server and consume additional storage. The amount of additional disk space consumed by these logs is proportional to the amount of changes committed on the server. If a lost connection can’t be resumed quickly enough, you can drop the replication slot, which releases the storage of unconsumed logs. You would then need to do a full re-sync of your connector to reset the cursor in the replication slot.
In your Azure portal, do the following:
i. Set Azure replication support to logical, then click Save.
ii. Click Yes to restart the server to apply the change.
iii. If you are running Postgres 9.5 or 9.6 and use public network access, add a firewall rule to include the public IP address of the client used to run the logical replication. The firewall rule name must include
_replrule. For example,
fivetran_replrule. Add the rule and click Save.
Log into a PostgreSQL console as a superuser (one that has the
Run the following command to create a logical replication slot named
fivetran_replication_slotfor the database you wish to sync. You must use the output plugin
SELECT pg_create_logical_replication_slot('fivetran_replication_slot', 'test_decoding');
Grant permission to the Fivetran user for reading the replication slot.
ALTER ROLE fivetran WITH REPLICATION;
Log in as the Fivetran user.
Verify that the Fivtran user can read the replication slot by running the following command.
SELECT count(*) FROM pg_logical_slot_peek_changes('fivetran_replication_slot', null, null);
If the query succeeds, then permissions are sufficient.
You do not need to do any additional configuration for the XMIN method. You must use the XMIN method if your Azure PostgreSQL version is earlier than 10.
Enter user, password, and database in setup formlink
In your Fivetran setup form, enter your user, password, and database name.
- For the User, enter
<servername>is part of your Azure host URL:
- For the Password, enter the password you set when you created the Fivetran user.
- For the Database, enter the database you want to replicate from.
Choose schema prefixlink
This is the last step of the integration. Each schema from the source database will be mapped to a schema in the destination by adding a prefix to the original schema name. For example, if your original database contains schemas “foo” and “bar” and if you choose the prefix “pre”, then you will get schemas “pre_foo” and “pre_bar” in the output.