Azure PostgreSQL Database Setup Guide
Follow these instructions to replicate your Azure PostgreSQL database to your destination via Fivetran.
Prerequisites
To connect your PostgreSQL database to Fivetran, you need:
- PostgreSQL version 7.3 or above
- IP (e.g. 1.2.3.4) or host (your.server.com)
- Port (usually 5432)
Connection options
First, you'll need to decide whether to connect your Azure PostgreSQL database directly or via an SSH tunnel. Security group configuration in subsequent steps will differ depending on this decision.
If you connect directly, you will need to create a rule in security group that allows us access to your database instance.
If you connect via SSH, Fivetran will connect to a separate server in your network which provides an SSH tunnel to your database. This method is necessary if your database is in an inaccessible subnet. You will then configure your tunnel server's security group to allow us access and then configure the database instance's security to allow access from the tunnel.
If you have an SSH connection, please follow these instructions before proceeding to the next step.
Enable access
In the setup wizard, decide if you'd like to connect your database directly or via an SSH tunnel.
If you decide to connect directly to your database, you will need to create a firewall rule to allow access. This is the simplest and most secure method.
If you decide to connect via an SSH tunnel, Fivetran will connect to a separate server in your network which provides an SSH tunnel to your database. This method is necessary if your database is in an inaccessible subnet on a virtual network.
The Fivetran data processing servers will need access to your database server. You will need to configure the firewall.
Configure server firewall
Open the SQL database firewall settings in the Azure console
Select the Azure Database for Postgres resource that you want to replicate. In its settings, click "Connection security".
Add a new firewall rule
Set Fivetran's IP as both "Start IP" and "End IP" to define the range of that firewall rule.
Remember to Save!
Create Fivetran user in master database
Open a connection to your Postgres database and execute this SQL command to create a fivetran user:
CREATE USER fivetran PASSWORD 'some-password';
replacing some-password with a password of your choice.
Grant read-only access to Fivetran
Then grant the fivetran user read-only access to all tables:
GRANT USAGE ON SCHEMA "public" TO fivetran;
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO fivetran;
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO fivetran;
The last command makes sure that any future tables will be accessible to fivetran. To grant access to schemas other than the Postgres' default public schema, simply replace public with your other schema name. If you have several schemas and you wish to grant access to all of them, you will need to repeat these three commands for each schema.
Restricted permissions (optional)
You can limit Fivetran's access to any schemas, tables, or columns of your choice.
How to allow only a subset of tables
To grant access only to some tables in a schema, first make sure fivetran has access to the schema itself:
GRANT USAGE ON SCHEMA "some_schema" TO fivetran;
Make sure you have removed any previously given permission to all tables in that schema:
ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" REVOKE SELECT ON TABLES FROM fivetran;
REVOKE SELECT ON ALL TABLES IN SCHEMA "some_schema" FROM fivetran;
Then repeat this command for each table you wish to include:
GRANT SELECT ON "some_schema"."some_table" TO fivetran;
Any tables created in the future will be excluded from access by default. To include them, run:
ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" GRANT SELECT ON TABLES TO fivetran;
To grant access to all tables except a few, unfortunately there is no way to do that directly in Postgres. You will just have to positively include all the tables you do want. Note that it is not possible to achieve exclusion by granting access to all tables and then revoking access for a subset of tables.
How to allow only a subset of schemas or columns
To grant access only to some columns in a table, first make sure you have removed any previously given permission to read all columns in the table:
REVOKE SELECT ON "some_schema"."some_table" FROM fivetran;
Then give permission to the specific columns, e.g. some_column and other_column:
GRANT SELECT (xmin, "some_column", "other_column") ON "some_schema"."some_table" TO fivetran;
Access to the hidden system column xmin is required for incremental updates. Any new columns added to the table
in the future will not be accessible to fivetran unless you rerun this command with the new column included.
To grant access to all columns except one, you would have to positively grant access to all the other columns.
Incremental update Configuration
Note - Logical Replication is not supported by Azure PostgreSQL Database so we use XMIN system column for incremental updates. As a result, our Azure Postgres connection does NOT support replicating deleted data. If you would like this feature to be supported, please reach out to Microsoft to let them know.
Choose schema prefix
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.
Related articles
description Connector Overview
account_tree Schema Information
settings API Connector Configuration