1

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.

3
  • What's the criteria for being "best"? What's your question, actually?
    – mustaccio
    Jun 21, 2017 at 13:51
  • hi @mustaccio my question is for every line written in the seoul db, I need it to be copied in the Frankfurt db. Best, I'm not sure. Let me change that word.
    – Andy K
    Jun 21, 2017 at 13:53
  • I'm not sure which database you're creating the trigger "I'm trying to create a trigger inside of a Postgres RDS database hosted in Frankfurt"... "each insert into the Seoul database" Jun 26, 2017 at 19:56

1 Answer 1

3

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.

10
  • hi @evan-carroll, will streaming synchronous rep and postgres_fdw work on amazon rds?
    – Andy K
    Jun 21, 2017 at 16:28
  • do you know a decent source of documentation on postgres?
    – Andy K
    Jun 21, 2017 at 16:29
  • postgres_fdw is certainly on rds aws.amazon.com/blogs/aws/rds-postgres-read-replicas, but no idea on the rest. You'll have to search for it. Jun 21, 2017 at 16:31
  • 3
    @AndyK: "a decent source of documentation on postgres" - the manual:
    – user1822
    Jun 21, 2017 at 16:40
  • 1
    @a_horse_with_no_name #rotfl
    – Andy K
    Jun 21, 2017 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.