[Aurora PostgreSQL Setup Guide, Follow these instructions to replicate your Amazon Aurora PostgreSQL database to your destination using 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), NOTE: We do not support serverless Aurora., Choose a connection method, Decide whether to connect your Aurora PostgreSQL database directly or using an SSH tunnel. How you configure your security groups will differ depending on this decision., Connect directly, Fivetran connects directly to your database instance., 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 , these instructions, ” before proceeding to the next step. You must then configure your tunnel servers security group to allow Fivetran access and configure the database instances security to allow access from the tunnel.”, Create read replica (optional), “We recommend that you connect a read replica to Fivetran, but its not required. Using a read replica reduces the load of Fivetrans queries on your master database. (In practice, this load is negligible unless your database has a table with more than 100 million rows.)”, If you already have a read replica or want to connect Fivetran to your master database, skip to the , Enable access section, ., In your RDS Dashboard, select the Aurora PostgreSQL instance you want to replicate., Click , Instance actions, ., Click , Create aurora replica, ., Ensure that the read replica is accessible from outside your VPC., Specify the , DB instance class, for the read replica. It does not need to be as large as your master instance., Set a , DB instance identifier, ., Set a , DB Parameter group, and note the database port number. You will need the port number to configure Fivetran., Click , Create Aurora replica, ., “The replicas status should now be “, creating, ., It will take a few minutes for the read replica to finish being created. The status will change to , available, when it is done., Enable access, “Fivetrans data processing servers need access to your database server. If your instance is in a VPC, two mechanisms control access: VPC security groups and network access control lists (ACLs). If your instance is not in a VPC, then you only need to configure security groups.”, Configure security group, These instructions assume that your read replica is in a VPC. If it is not in a VPC, you can still use these instructions because configuring a non-VPC security group is an almost identical process., Click on your Aurora replica., Go to , Details, ., “Set your replicas “, Publicly accessible, value to YES, then click , Security groups, ., In the Security Group panel, go to the , Inbound, tab, then click , Edit, ., Click , Add Rule, to add a new Custom TCP rule., In the , Port Range, ” field, enter your replicas port number. If you created a new read replica for Fivetran, this is the port number that you found in “, Step 2, . (The default port number is , 5432, .), In the , Source, field, enter , “Fivetrans IP”, ., In the , Description, field, add a description to your rule (for example, “Fivetran”)., Click , Save, ., Configure network ACLs, “In your Aurora replicas Details page, click on your “, VPC, ., Select the , VPC, . , In the Summary tab, select , Network ACL, ., You will see tabs for Inbound Rules and Outbound Rules. You must edit both., Edit inbound rules, Select , Inbound Rules, ., If you have a default VPC that was automatically created by AWS, the settings already allow all incoming traffic. To verify that the settings allow incoming traffic, confirm that the Source value is , 0.0.0.0/0, and that the ALLOW entry is listed above the DENY entry., “If your inbound rules dont include “, ALL – 0.0.0.0/0 – ALLOW, entry, edit the rules to allow inbound traffic to all ports , 1024-65535, for , destination 0.0.0.0/0, ., Edit outbound rules, Select , Outbound Rules, ., “If your outbound rules dont include an “, ALL – 0.0.0.0/0 – ALLOW, entry, edit the rules to allow outbound traffic to all ports , 1024-65535, for , destination 0.0.0.0/0, . For additional help, see , “AWSs Network ACLs documentation”, ., Create user, Open a connection to your master Aurora PostgreSQL database., Create a user for Fivetran called , fivetran, by executing the following SQL command. Replace , some-password, with a password of your choice., “CREATE USER fivetran PASSWORD some-password;n”, Grant user permissions, Grant the , fivetran, ” user read-only access to all tables by running the following commands. To grant access to a schema other than PostgreSQLs default “, public, schema, replace , public, with the schema name., GRANT USAGE ON SCHEMA “public” TO fivetran;nGRANT SELECT ON ALL TABLES IN SCHEMA “public” TO fivetran;nALTER DEFAULT PRIVILEGES IN SCHEMA “public” GRANT SELECT ON TABLES TO fivetran;n, 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), “If you want to limit Fivetrans 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 fivetran;n, Revoke any previously granted permission to all tables in that schema., ALTER DEFAULT PRIVILEGES IN SCHEMA “some_schema” REVOKE SELECT ON TABLES FROM fivetran;nREVOKE SELECT ON ALL TABLES IN SCHEMA “some_schema” FROM fivetran;n, Repeat the following command for each table you want Fivetran to sync., GRANT SELECT ON “some_schema”.”some_table” TO fivetran;n, By default, any tables that you create in the future will be excluded from the , fivetran, ” users access. To grant access to new tables, run the following command.”, ALTER DEFAULT PRIVILEGES IN SCHEMA “some_schema” GRANT SELECT ON TABLES TO fivetran;n, Restrict access to columns (optional), 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 , xmin, for 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 fivetran;n, Grant permission to the specific columns you want to sync (for example some_column, and , other_column, )., GRANT SELECT (xmin, “some_column”, “other_column”) ON “some_schema”.”some_table” TO fivetran;n, 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 mechanism, 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 Aurora PostgreSQL versions 10.6 or later. For earlier versions, we only support the XMIN method. As a result, our Aurora PostgreSQL connector does NOT support replicating deleted data for versions before 10.6. , 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, ., Logical replication, Logical replication is based on , logical decoding, of the PostgreSQL write-ahead log (WAL). To enable logical replication, follow these steps:, IMPORTANT: You can only enable logical replication if your Aurora PostgreSQL version is 10.6 or later., 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 cant 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 Amazon Aurora UI, do the following:, i. Create a new parameter group (non-default group)., ii. Enable the , logical_replication, flag in that group by setting the value to , 1, ., iii. Set , wal_sender_timeout, to , 0, ., iv. Apply the parameter group to the database., v. Wait until the status changes to , pending-reboot then reboot the database to apply the new parameter group., Log into a PostgreSQL console as a superuser (one that has the , rds_superuser, role)., Run the following command to create a logical replication slot named , fivetran_replication_slot, for the database you wish to sync. You , must, use the output plugin , test_decoding, . , “SELECT pg_create_logical_replication_slot(fivetran_replication_slot, test_decoding);n”, Grant permission to the , fivetran, user for reading the replication slot. , GRANT rds_replication TO fivetran;n, The , fivetran, user does not need the , rds_superuser, role., Log in as the , fivetran, user., Verify that the , fivetran, user can read the replication slot by running the following command., “SELECT count(*) FROM pg_logical_slot_peek_changes(fivetran_replication_slot, null, null);n”, If the query succeeds, then permissions are sufficient., XMIN, You do not need to do any additional configuration for the XMIN method. You must use the XMIN method if your Aurora PostgreSQL version is earlier than 10.6., Get endpoint details, Go to the Aurora replica that you want to integrate., In the Connect section, make a note of the , Endpoint URL, and , Port number, . You will need them to configure Fivetran., Choose schema prefix, Each schema from your 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., Finish Fivetran setup, In your , connector setup form enter your preferred , Destination schema prefix, . , Enter the , Host, URL you found in , Step 7, ., Enter the , Port, number you found in , Step 7, ., Enter the , User, name you created in , Step 4, ., Enter the , Password, you created in , Step 4, ., Enter the , Database, name you want to replicate., Select your , Connection Method, . You can , Connect directly, or , Connect using a SSH tunnel, . See , Step 1, ., If you connect using a SSH tunnel, enter the following details:, SSH Host, SSH Port, SSH User, Public Key, (Optional) Enable the toggle , Require TLS through tunnel, if you want to use TLS., Click , Save and Test, . Your Aurora PostgreSQL database is now connected., Related articles, description, Connector Overview, , account_tree, Schema Information, , assignment, Release Notes, , settings, API Connector Configuration, , home, Documentation Home]