Aliyun RDS for PostgreSQL

TapData Cloud offers you cloud services that are suitable for scenarios requiring rapid deployment and low initial investment, helping you focus more on business development rather than infrastructure management. Free trial with TapData Cloud.TapData Enterprise can be deployed in your local data center, making it suitable for scenarios with strict requirements on data sensitivity or network isolation. It can serve to build real-time data warehouses, enable real-time data exchange, data migration, and more.TapData Community is an open-source data integration platform that provides basic data synchronization and transformation capabilities. This helps you quickly explore and implement data integration projects. As your project or business grows, you can seamlessly upgrade to TapData Cloud or TapData Enterprise to access more advanced features and service support.

Follow the instructions below to successfully add and use PostgreSQL database in TapData Cloud.

Supported Versions

PostgreSQL 9.4, 9.5, 9.6, 10.x, 11.x, 12

Permission Settings

As a Data Source

Initialization

GRANT SELECT ON ALL TABLES IN SCHEMA <schemaname> TO <username>;

If you need to use the incremental log feature, you also need to set replication login permission:

CREATE ROLE <rolename> REPLICATION LOGIN;
CREATE USER <username> ROLE <rolename> PASSWORD '<password>';
// or
CREATE USER <username> WITH REPLICATION LOGIN PASSWORD '<password>';

If you need to perform incremental synchronization using the last update timestamp, you also need to perform the following steps:

• Create Public Function In the database, execute the following command

CREATE OR REPLACE FUNCTION <schema>.update_lastmodified_column()
  RETURNS TRIGGER language plpgsql AS $$
  BEGIN
      NEW.last_update = now();
      RETURN NEW;
  END;
$$;

• Create Column and Trigger

Note: The following operations need to be executed once for each table. Let's assume the table name that needs to add the last update column is mytable.

• Create last_update Column

ALTER TABLE <schema>.mytable ADD COLUMN last_update TIMESTAMP DEFAULT now();

• Create Trigger

CREATE TRIGGER trg_uptime BEFORE UPDATE ON <schema>.mytable FOR EACH ROW EXECUTE PROCEDURE update_lastmodified_column();

As a Data Target

Grant user permissions:

GRANT INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA <schemaname> TO <username>;