PostgreSQL Live Migration from 9.2 to 14 with Bucardo

version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.21 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

1. Create the Bucardo Role
    `CREATE ROLE bucardo WITH LOGIN SUPERUSER PASSWORD 'password';`
2. Modify the `pg_hba.conf` file to allow connection from the Bucardo role.
    `host    replication     bucardo         10.0.0.0/16            md5`
3. Export PG roles and schema (from 9.2):
    ```


    pg_dump --schema-only --schema=public foo > /data-bucardo/foo_schemas.sql
    pg_dumpall --roles-only --database=foo > /data/foo_roles.sql


    ```
4. Import the roles and the schema into the new PG 14 DB:
    ```


    psql foo -f /data-bucardo/foo_roles.sql
    psql foo -f /data-bucardo/foo_schemas.sql


    ```
5. Create all the extensions needed in PG 14
6. Disable Triggers and FKs:
    ```


    create table if not exists foo_triggers (
        seq bigserial primary key,
        sql text
    );

    -- disable triggers
    do $$ declare t record;
    begin
        for t in select trigger_schema || '.' || event_object_table as table_name, trigger_name from information_schema.triggers
                where trigger_catalog = 'foo'
                and trigger_schema in ('public')
            loop

            insert into foo_triggers (sql) values (
                format('alter table %s disable trigger %s',
                    t.table_name, t.trigger_name));

            execute format('alter table %s disable trigger %s', t.table_name, t.trigger_name);

        end loop;
    end $$;

    create table if not exists dropped_foreign_keys_public (
            seq bigserial primary key,
            sql text
    );

    -- public schema
    do $$ declare t record;
    begin
        for t in select conrelid::regclass::varchar table_name, conname constraint_name,
                pg_catalog.pg_get_constraintdef(r.oid, true) constraint_definition
                from pg_catalog.pg_constraint r
                where r.contype = 'f'
                -- current schema only:
                and r.connamespace = (select n.oid from pg_namespace n where n.nspname = 'public')
            loop

            insert into dropped_foreign_keys_public (sql) values (
                format('alter table %s add constraint %s %s',
                    quote_ident(t.table_name), quote_ident(t.constraint_name), t.constraint_definition));

            execute format('alter table %s drop constraint %s', quote_ident(t.table_name), quote_ident(t.constraint_name));

        end loop;
    end $$;


    ```
7. Add tables and sequences to Bucardo:
    ```


    sudo bucardo add db source_db dbname=foo host=pgsql-aws-05 user=bucardouser pass='password'
    sudo bucardo add db target_db dbname=foo host=pgsql-aws-pg14 user=bucardo pass='password'

    sudo bucardo add table public.* db=source_db relgroup=foo_db_group
    sudo bucardo add sequence public.* db=source_db relgroup=foo_db_group

    sudo bucardo add dbgroup foo_db_group source_db:source target_db:target

    sudo bucardo add sync foo_sync relgroup=foo_db_group dbs=foo_db_group autokick=0

    sudo bucardo validate foo_sync


    ```
7. Export the data from PG 9.2:
    `pg_dump -Fc --data-only --disable-triggers --verbose foo > /data/foo_database_data.sql`
8. Import the data into PG 14:
    `pg_restore -d foo /data/foo_database_data.sql --disable-triggers --exit-on-error -v --jobs=15`
9. Re-enable Triggers and FKs:
    ```


    -- enable FKs
    do $$ declare t record;
            begin
                -- order by seq for easier troubleshooting when data does not satisfy FKs
                for t in select * from dropped_foreign_keys_public order by seq loop
                    execute t.sql;
                end loop;
            end $$;
    -- enable triggers
    do $$ declare t record;
    begin
        for t in select trigger_schema || '.' || event_object_table as table_name, trigger_name from information_schema.triggers
                where trigger_catalog = 'foo'
                and trigger_schema in ('public')
            loop

            execute format('alter table %s enable trigger %s', t.table_name, t.trigger_name);

        end loop;
    end $$;


    ```
10. Start Bucardo Replication:
    `sudo bucardo update sync foo_sync autokick=1`
    `sudo bucardo start`

Launch an Application Load Balancer
2. Redirect all customers to the new ALB
3. From the ALB, redirect all customers to a maintenance page using `aws_lb_listener_rule`
4. Whitelist some of our IP addresses to access the application (This is for allowing few members of our team to actually access the application for testing purposes)
    ```


    resource "aws_lb_listener_rule" "https_whitelist_ip_addresses_to_app" {
    count        = terraform.workspace == "prod" ? 1 : 0
    listener_arn = aws_alb_listener.old_alb_listener_https_prod[count.index].arn

    action {
        target_group_arn = "arn:aws:elasticloadbalancing:ap-southeast-2:2____________0:targetgroup/bau-prod-elb/6_____e"
        type             = "forward"
    }

    condition {
        host_header {
            values = ["*.foo.com", "foo.com"]
        }
    }

    condition {
        source_ip {
            values = [
                "100.100.100.101/32", # Staff 1
                "100.100.100.102/32", # Staff 2
                "100.100.100.103/32", # Staff 3
                "100.100.100.104/32", # Staff 4
                ]
        }
    }}


    ```
5. Stop Bucardo
6. Modify Route53 records and point those records to the new DB cluster (Our application connects to the database using a specific endpoint)
7. Testing, testing and more testing!
8. Modify the ALB and remove the redirect rules to allow customers to access the application