Postgresql: Table Replication Through Databases

SYNOPSYS

I want to replicate a few tables from one database to another.
I don’t need syncronised replication, hot swap or clustering capabilities.
I don’t want to add triggers to log events and then replay them periodicaly.
What I will do is :

  • create a new table, exact copy of the considered one including foreign constraints.
  • feed the new table from the distant one using dblink(…).
  • disable the foreign constraints referring the table.
  • swap the tables.
  • reactivate the foreign constraints.

You’ll find here an simpler solution to this.

PROCEDURE

show foreign constraints and build SQL to DROP/ADD them
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))
;
create an empty table, duplicate of another, including foreign keys
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;
view to access distant table using dblink(…)
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
);
function to do the dirty job
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;

ISSUES

  • when you use ‘ALTER TABLE foo RENAME TO bar’, views based on ‘foo’ will be updated too.
  • create_table_like() does not copy granted access, so they will be lost when switching the tables.