[PostgreSQL RDS Setup Guide, Follow these instructions to replicate your Amazon RDS 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), 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., 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. You will configure your incremental update mechanism in later steps., Logical replication, NOTE: Because of AWS limitations, you can only use logical replication if you connect Fivetran to your PostgreSQL RDS master database., Logical replication is based on , logical decoding, of the PostgreSQL write-ahead log (WAL). Fivetran reads the WAL to detect incrementally changed data. We recommend using logical replication whenever possible because it is faster than XMIN replication., Learn more in our , logical replication documentation, ., XMIN, The XMIN method is based on the hidden , xmin, ” system column that is present in all PostgreSQL tables. With XMIN, Fivetran must scan every table in full to detect updated data. XMIN replication is slower than logical replication and doesnt allow Fivetran to detect deleted rows, so we do not recommend XMIN for near real-time data needs. “, You can use XMIN for any PostgreSQL deployment, including a read replica., Learn more in our , XMIN documentation, ., Choose a connection method, Decide whether to connect your 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 a read replica (XMIN only), Creating a read replica allows Fivetran to integrate your data without putting unnecessary load on or interrupting the queries running on your master server. You can only use a read replica if you chose XMIN as your incremental update mechanism., If you chose logical replication as your incremental update mechanism, want to connect Fivetran to your master database, or already have a read replica, skip to the , Enable access section, ., In your RDS Dashboard, select the PostgreSQL instance you want to replicate., Click , Instance actions, ., Click , Create read replica, ., Specify the instance type for the read replica. It does not need to be as large as your master instance., Set an instance ID., Ensure that the read replica is accessible from outside your VPC., Click , Create read replica, ., Create read 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 your read replica is not in a VPC, you can still use these instructions because configuring a non-VPC security group is an almost identical process., In your RDS Dashboard, click on your database instance., Go to , Details, ., “Find the databases port number and make a note of it. You will need the port number to configure Fivetran.”, “Click the link to the database instances “, Security Group, ., In the Security Group panel, go to the , Inbound, tab., Click , Edit, ., Click , Add Rule, ., Fill in the new Custom TCP Rule., In the , Port Range, ” field, enter your read replicas port number. If you created a new read replica for Fivetran, this is the port number that you found in “, step 3 of this section, . (The port will be , 5432, for direct connections, unless you changed the default.), What you enter in the , Custom IP, ” field depends on whether youre connecting directly or using an SSH tunnel.”, “If youre connecting directly, enter “, “Fivetrans IP”, ., “If youre connecting using an SSH tunnel, enter “, {your-ssh-tunnel-server-ip-address}/32, ., Click , Save, ., Configure network ACLs, Return to the RDS dashboard. , Expand the view on your database instance., “Click the link to the instances “, VPC, ., Select the , VPC, . , In the Summary tab, click the , Network ACL, link., 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 the , Source, to access the port number of your database instance. (The port will be , 5432, for direct connections, unless you changed the default.) For additional help, see , “AWSs Network ACLs documentation”, ., “If youre connecting directly, enter “, “Fivetrans IP”, ., “If youre connecting using an SSH tunnel, enter “, {your-ssh-tunnel-server-ip-address}/32, ., 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 the following , Destination, :, “If youre connecting directly, enter “, “Fivetrans IP”, ., “If youre connecting using an SSH tunnel, enter “, {your-ssh-tunnel-server-ip-address}/32, ., Create user, Open a connection to your PostgreSQL database., Create a user for Fivetran by executing the following SQL command. Replace , some-password, with a password of your choice., “CREATE USER fivetran PASSWORD some-password;n”, Grant read-only access, 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 permissions 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., Configure incremental updates, Follow the steps below to configure your chosen incremental update mechanism., For logical replication, proceed to the , next section, ., For XMIN, skip ahead to the , XMIN section, ., Logical replication, To enable logical replication, follow these steps:, IMPORTANT: You can only enable logical replication if your PostgreSQL server version is 9.4.15+, 9.5.10+, 9.6.6+, or 10.1+. Prior minor versions have breaking bugs., Go to your RDS 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.”, Set the , rds.logical_replication, parameter to , 1, by following these steps: , ni. Create a new parameter group (non-default group)., nii. Enable the , logical_replication, flag in that group by setting the value to , 1, ., niii. Set , wal_sender_timeout, to , 0, ., niv. Apply the parameter group to the database., nv. 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 (the one with a , rds_superuser, role)., Create a logical replication slot named , fivetran_replication_slot, for the database you wish to sync by running the following command. If you use a custom slot name instead, it cannot start with a number. 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; Note: 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., Important: You must periodically tune the , checkpoint_timeout, and , max_wal_size, parameters based on your PostgreSQL database operations. If you do not, you may experience replication failures. To learn how to tune, read , this tuning checkpoints documentation, . , XMIN, You do not need to do any additional configuration for the XMIN method., 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, , assignment, Release Notes, , settings, API Connector Configuration, , home, Documentation Home]