Re-create a table within postgres without downtime

recreate_table_without_downtime.sql

-- This is a general process for recreating tables in postgres without downtime
-- it can help you recover from data corruption bugs like one seen in Postgres 9.3

-- The table in question should have no foreign key contraints
-- in this example I migrate a table called bibliography_entries to a table called bib_entries

-- create the new table
CREATE TABLE bib_entries (LIKE bibliography_entries INCLUDING ALL);
-- create a temporary table to hold the changes while the bulk of the table is migrated
CREATE TABLE bib_entries_tmp (LIKE bibliography_entries INCLUDING ALL);

-- create trigger to insert into the temporary table
CREATE OR REPLACE FUNCTION copy_to_bib_entries_tmp() RETURNS trigger
AS $body$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        DELETE FROM bib_entries_tmp WHERE bib_entries_tmp.id = NEW.id; -- deleting is ok because there arent FK constraints
        INSERT INTO bib_entries_tmp VALUES(NEW.*);
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO bib_entries_tmp VALUES(NEW.*);
    END IF;
    -- Always return the row
    RETURN NEW;
END $body$
LANGUAGE plpgsql
SECURITY DEFINER;


CREATE TRIGGER copy_to_bib_entries_tmp AFTER INSERT OR UPDATE ON "bibliography_entries"
FOR EACH ROW EXECUTE PROCEDURE copy_to_bib_entries_tmp();

-- create trigger to check for duplicate primary keys

CREATE OR REPLACE FUNCTION bib_entries_insert() RETURNS trigger
AS $body$
BEGIN
    BEGIN
        INSERT INTO bib_entries VALUES(NEW.*);
    EXCEPTION  WHEN unique_violation THEN
    END;
    --ignore and return null
    RETURN NULL;
END $body$
LANGUAGE plpgsql
SECURITY DEFINER;

CREATE TRIGGER bib_entries_insert BEFORE INSERT ON "bib_entries"
FOR EACH ROW
WHEN (pg_trigger_depth() = 0) -- otherwise we have infinite recursion
EXECUTE PROCEDURE bib_entries_insert();

-- binary dump of table

COPY bibliography_entries TO '/mnt/backup/bibliography_entries/dump' WITH (FORMAT binary);

-- load binary dump into new table

COPY bib_entries FROM '/mnt/backup/bibliography_entries/dump' WITH (FORMAT binary);

-- remove trigger that prevents inserts from failing

DROP TRIGGER bib_entries_insert ON bib_entries;
DROP FUNCTION bib_entries_insert();

-- create a trigger to copy data inserts / updates

CREATE OR REPLACE FUNCTION copy_to_bib_entries() RETURNS trigger
AS $body$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        DELETE FROM bib_entries WHERE bib_entries.id = NEW.id; -- deleting is ok because there arent FK constraints
        INSERT INTO bib_entries VALUES(NEW.*);
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO bib_entries VALUES(NEW.*);
    END IF;
    -- Always return the row
    RETURN NEW;
END $body$
LANGUAGE plpgsql
SECURITY DEFINER;

CREATE TRIGGER copy_to_bib_entries AFTER INSERT OR UPDATE ON "bibliography_entries"
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE copy_to_bib_entries();

-- remove temporary trigger

DROP TRIGGER copy_to_bib_entries_tmp ON bibliography_entries;
DROP FUNCTION copy_to_bib_entries_tmp();

-- Copy rows from the temporary table that dont exist in the new table

DELETE FROM bib_entries_tmp WHERE EXISTS (SELECT id FROM bib_entries WHERE id = bib_entries_tmp.id);
INSERT INTO bib_entries SELECT * FROM bib_entries_tmp WHERE NOT EXISTS (SELECT id FROM bib_entries WHERE id = bib_entries_tmp.id);

-- switch over app to use new table, then drop the trigger duplicating writes

DROP TRIGGER copy_to_bib_entries ON bibliography_entries;
DROP FUNCTION copy_to_bib_entries();