table replication through PostgreSQL databases

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

CREATE VIEW v_constraints AS
SELECT
   c.conname AS constraint_name
  ,t.relname AS table_name
  ,ta.attname AS column_name
  ,ft.relname AS foreign_table_name
  ,fta.attname AS foreign_column_name
  , c.contype AS constraint_type
  ,c.confmatchtype AS match_type
  ,c.confupdtype AS on_update
  ,c.confdeltype AS on_delete
  ,format('ALTER TABLE ONLY %s DROP CONSTRAINT %s', t.relname, c.conname) AS constraint_drop
  ,format('ALTER TABLE %s ADD CONSTRAINT %s %s (%s) REFERENCES %s (%s) MATCH %s ON UPDATE %s ON DELETE %s', t.relname, c.conname
      ,(CASE c.contype WHEN 'c' THEN 'CHECK' WHEN 'f' THEN 'FOREIGN KEY' WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' WHEN 't' THEN 'TRIGGER' WHEN 'x' THEN 'EXCLUSION' END)
      ,ta.attname, ft.relname, fta.attname
      ,(CASE c.confmatchtype WHEN 'f' THEN 'FULL' WHEN 'p' THEN 'PARTIAL' WHEN 'u' THEN 'SIMPLE' ELSE 'SIMPLE' END)
      ,(CASE c.confupdtype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END)
      ,(CASE c.confupdtype WHEN 'a' THEN 'NO ACTION' WHEN 'r' THEN 'RESTRICT' WHEN 'c' THEN 'CASCADE' WHEN 'n' THEN 'SET NULL' WHEN 'd' THEN 'SET DEFAULT' END)
  ) AS constraint_add
FROM
  pg_constraint c
  LEFT JOIN pg_class t  ON (c.conrelid  = t.oid)
  LEFT JOIN pg_class ft ON (c.confrelid = ft.oid)
  LEFT JOIN pg_attribute ta ON (t.oid = ta.attrelid AND ta.attnum = ANY(c.conkey))
  LEFT JOIN pg_attribute fta ON (ft.oid = fta.attrelid AND fta.attnum = ANY(c.confkey))
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

CREATE OR REPLACE FUNCTION create_table_like(source text, newtable text) RETURNS void AS $$
DECLARE
    _query text;
BEGIN
    EXECUTE format('create table %s (like %s including all)', newtable, source);
    for _query in SELECT format('alter table %s add constraint %s %s',
                                newtable,
                                replace(conname, source, newtable),
                                pg_get_constraintdef(oid))
                  FROM pg_constraint
                  WHERE contype = 'f' and conrelid = source::regclass
    LOOP
        EXECUTE _query;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

1
2
3
4
5
6
7

CREATE VIEW v_sync_data AS
SELECT * FROM dblink('dbname=origin_db', 'select * from tbl_data')
AS t (
    f1  integer,
    f2  boolean,
    f3  text
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE FUNCTION update_table_from(source text, dest text) RETURNS void AS $$
DECLARE
    cstr     RECORD;
BEGIN

    CREATE TEMP TABLE cstrs AS SELECT constraint_drop, constraint_add FROM v_constraints
    WHERE constraint_type='f' AND foreign_table_name=dest;

    DROP TABLE IF EXISTS new_table;
    EXECUTE format($x$select adm_create_table_like('%s', 'new_table')$x$, dest);
    EXECUTE format('insert into new_table (select * from %s)', source);

    FOR cstr IN SELECT * FROM cstrs LOOP
        EXECUTE cstr.constraint_drop;
    END LOOP;

    EXECUTE format('DROP TABLE IF EXISTS %s_old', dest);
    EXECUTE format('ALTER TABLE %s RENAME TO %s_old', dest, dest);
    EXECUTE format('ALTER TABLE new_table RENAME TO %s', dest);

    FOR cstr IN SELECT * FROM cstrs LOOP
        EXECUTE cstr.constraint_add;
    END LOOP;

    DROP TABLE cstrs;
END;
$$ LANGUAGE plpgsql;