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>;