如何通过AWS DMS在PostgreSQL数据库间复制视图？

Great question! You’re absolutely correct that AWS DMS doesn’t natively replicate views between PostgreSQL databases by default—even though they’re homogeneous. But don’t worry, there are a few solid workarounds to get this done. Let’s walk through them step by step:

方案1：用DMS自定义SQL脚本同步初始视图定义 #

If you only need to replicate views once (or rarely update them), this is the simplest approach. AWS DMS lets you run custom SQL at key stages of your migration task:

1. First, generate the CREATE statements for all your source views using this query in your source PostgreSQL database:

SELECT 'CREATE OR REPLACE VIEW ' || table_schema || '.' || table_name || ' AS ' || view_definition || ';'
FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

2. Save the output as a SQL script file.
3. In your DMS migration task configuration, add this script to the Pre-migration custom SQL (runs before full load) and Pre-apply custom SQL (runs before CDC starts) sections.

⚠️ Note: This only syncs the initial view definitions. Any later changes to views in the source won’t automatically propagate to the target—you’d need to re-run the script manually or automate it separately.

方案2：实时同步视图定义（触发器+中间表） #

For scenarios where views change regularly and you need real-time sync, this method uses PostgreSQL triggers and a dedicated sync table:

Step 1: Set up the sync table in the source database #

Create a table to track view definitions:

CREATE TABLE public.view_definitions (
    schema_name text NOT NULL,
    view_name text NOT NULL,
    view_definition text NOT NULL,
    last_updated timestamp DEFAULT now(),
    PRIMARY KEY (schema_name, view_name)
);

Step 2: Create a trigger to track view changes #

Make a function that updates the sync table when views are created, altered, or deleted:

CREATE OR REPLACE FUNCTION sync_view_defs()
RETURNS trigger AS $$
BEGIN
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        INSERT INTO public.view_definitions (schema_name, view_name, view_definition)
        VALUES (NEW.table_schema, NEW.table_name, NEW.view_definition)
        ON CONFLICT (schema_name, view_name) DO UPDATE
        SET view_definition = NEW.view_definition, last_updated = now();
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM public.view_definitions
        WHERE schema_name = OLD.table_schema AND view_name = OLD.table_name;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Then attach this function to an event trigger that listens for view-related DDL events:

CREATE EVENT TRIGGER track_view_changes
ON ddl_command_end
WHEN TAG IN ('CREATE VIEW', 'ALTER VIEW', 'DROP VIEW')
EXECUTE FUNCTION sync_view_defs();

Step 3: Sync the table with DMS #

Add the view_definitions table to your DMS replication task—treat it like any other table you’re migrating.

Step 4: Apply changes to the target database #

Create a trigger on the target’s view_definitions table that automatically creates/updates/drops views:

CREATE OR REPLACE FUNCTION apply_view_changes()
RETURNS trigger AS $$
DECLARE
    v_sql text;
BEGIN
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        v_sql := 'CREATE OR REPLACE VIEW ' || NEW.schema_name || '.' || NEW.view_name || ' AS ' || NEW.view_definition;
        EXECUTE v_sql;
    ELSIF TG_OP = 'DELETE' THEN
        v_sql := 'DROP VIEW IF EXISTS ' || OLD.schema_name || '.' || OLD.view_name;
        EXECUTE v_sql;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_apply_view_changes
AFTER INSERT OR UPDATE OR DELETE ON public.view_definitions
FOR EACH ROW EXECUTE FUNCTION apply_view_changes();

This setup will keep your target views in lockstep with the source in real time.

方案3：定期同步（pg_dump + 自动化） #

If real-time sync isn’t critical, you can automate periodic view syncs using pg_dump and AWS services:

1. Write a bash script to export view definitions from the source:

pg_dump -h source-db-endpoint -U your-source-user -d source-db-name -s -t '*.view' > views_dump.sql

2. Add a step to import the dump into the target:

psql -h target-db-endpoint -U your-target-user -d target-db-name -f views_dump.sql

3. Deploy this script to AWS Lambda, and use CloudWatch Events to schedule it (e.g., daily or weekly).

Key Considerations for All Methods #

• Permissions: Ensure your DMS user and trigger functions have the right privileges (e.g., superuser access on the source for event triggers, CREATE VIEW access on the target).
• Dependencies: Make sure any tables, functions, or other objects your views depend on are already synced via DMS—otherwise, view creation will fail.
• Testing: Always validate these workflows in a staging environment first to avoid breaking your production migration.

内容的提问来源于stack exchange，提问作者Daniel Pereira