triggers or else between 2 postgres databases hosted on a different datacenter

Question

I'm trying to create a trigger inside of a Postgres RDS database hosted in Frankfurt that we will call "Frankfurt". That trigger will poll tables in a distant Postgres RDS database hosted in Seoul, let's call that database "Seoul".

The Seoul database will never have delete nor update. It will only have insert.

The aim of that trigger is for each insert into the Seoul database to copy the rows from Seoul that are not in Frankfurt, to the Frankfurt database.

CREATE OR REPLACE FUNCTION my_dml_function() RETURNS TRIGGER AS $employees$
    BEGIN
        --
        --
        IF (TG_OP = 'INSERT') THEN
            perform dblink_connect('dbname=database_seoul user=postgres password=ExtraPassword'); 
            INSERT INTO all_employees
            SELECT *
            FROM dblink('foreign_server',$RDS$
                SELECT * 
                FROM employees 
                WHERE employee_id NOT IN  (SELECT employee_id FROM all_employees)   
            $RDS$) AS t1;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER my_dml_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW EXECUTE PROCEDURE my_dml_function();

I'm not sure if it is the best way.

Answer 1

I don't think this is a good idea, you should read the documentation on Streaming Synchronous Replication instead which guarantees consistency and is a ton easier to configure.

Even if you're not going to use that, you should consider postgres_fdw instead of the older dblink.