# Cleanup Postgres

## Notes

Time to do Dump migration: 3:41.

## Preparation

### Install psql and pg_dump on instance
- [x] Run install_psql on production instance from control node
- `./run_playbook production playbooks/install_psql.yml`
- [x] Verify psql V 12 on production
- `psql --version`

### Setup Parameter Group for migration
- AWS console: RDS > Parameter Group > Create Parameter Group
- [x] Parameter Group: aurora-postgresql12
- [x] DB Cluster Parameter Group
- [x] Group name: migration-group
- Open the paramter
- [x] Search `rds.logical_replication`
- [x] `Edit parameter 0 -> 1`

- [x] Make sure Route53 endpoints point to CLUSTER endpoint and not INSTANCE endpoint

- [x] modify parameter group for fallback cluster
- `aws rds modify-db-cluster --db-cluster-identifier <fallback-cluster> --db-cluster-parameter-group-name migration-group`

### Cleanup Target

- [x] Connect to CDK postgres
- `PGPASSWORD=<PASSWORD> psql -h <CDK URL> --username momo`
- [x] Ensure 0 connections
- `select * from pg_stat_database;`

- [x] Connect toanother database
- `\c postgres`

- [x] Terminate all instances
- `select pg_terminate_backend(pid) from pg_stat_activity where datname='momo';`

- [x] Drop and re-create the main database
- `DROP DATABASE momo`
- `CREATE DATABASE momo`
- [x] Recreate the readonly user

  CREATE USER readonly WITH PASSWORD 'fNYhianbOzJQytD';

  REVOKE CREATE ON SCHEMA public FROM PUBLIC;

  GRANT CONNECT ON DATABASE momo to readonly;
  GRANT USAGE ON SCHEMA public to readonly;
  GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

### Setup Database Replication instance
- [x] Create replication instance
- Instance t3.medium
- Availability DEV
- VPC is app-production vpc
- Security group is data-postgres security group

### Create endpoint Source
- Source endpoint
- Selct RDS DB instance
- Select fallback write instance
- Acess to endpoint > Provide access information manually
- User name: momo
- Password: get from secrets
- database: momo
- Test endpoint
### Create endpoint Target
- Source endpoint
- Selct RDS DB instance
- Select CDK write instance
- Acess to endpoint > Provide access information manually
- User name: momo
- Password: get from secrets
- database: momo
- Test endpoint

### Create database migration task
#### Pre task creation
- Create Role called migrationAssistant
- Give S3 Full access Permission
- Give Trust relationship

        {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "",
            "Effect": "Allow",
            "Principal": {
                "Service": "dms.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

- [x] Create bucket for storing pre-migration assistant
#### DMS > Database Migration Task > Create task
- [x] Select instance you created
- [x] Select endpoints you created
- [x] Migration type: migrate existing data and replicate ongoing changes
- [x] Target table prepration Do Nothing
- [x] Dont' Stop after full load
- [x] LOB : limited LOB 32KB
- [x] Task Logs Turn On
- [x] Table Mappings Rule Mappings, add one wildcard rule include
- [x] Premigration assesment select bucket you create, select role you created
- [x] Leave out encryption
- [x] Manually later on migration

Wait for the pre-migration to finish.
LOB will fail but that is OK because We are running LOB in inline mode

## Migration steps
### Run the bulk migration
- [ ] In the production EC2 run.
`PGPASSWORD=PwWSdPMpPDubTeb pg_dump -h fallback-2-cluster-cluster.cluster-ccpncvfct7ob.eu-central-1.rds.amazonaws.com --username momo | psql -h postgres-production.cluster-ccpncvfct7ob.eu-central-1.rds.amazonaws.com --username momo`

### Apply parameter group

- [x] Re-boot the reader instance on the fallback cluster.
- Wait for reboot to complete
- [x] Failover Fallback cluster
- [x] Restart service, re-deploy fargate, make sure connections have migrated to new writer
- [x] Check rds.logical_replication is ON in the main writer instance now.
- `show rds.logical_replication`

- [X] From the console start the live migration

- [X] Wait until live migration is at 100 % and running

- [X] Migrate all view, triggers, functions, and sequences
`PGPASSWORD=<Password> pg_dump -s -h fallback-2-cluster-cluster.cluster-ccpncvfct7ob.eu-central-1.rds.amazonaws.com --username momo | PGPASSWORD=<Password> psql -h postgres-production.cluster-ccpncvfct7ob.eu-central-1.rds.amazonaws.com --username momo`
- [x] Check for the existence of views in target database
`\dv
\sv current_beds
\sv current_bedsense
\sv problems
\sv current_api_beds
select count(*) from current_beds`

- [ ] Update Route 53.

- [ ] Redploy fargate restart service

So the reason the postgres cleanup failed last attempt is because the views were not found in the target database.
While the views were created in the bulk migration that occured in the start. Due to the foreign key constraints that exists on the tables. We configured AWS DMS to drop tables on target when we ran the migration. This delted the views as their target tables were dropped and were not re-created. To fix this I have added another step in the process.
After the migration has entered replication mode, i.e. all tables have been compied and AWS DMS is just copying changes. We will run a Schema update on the whole database. Basically re apply the schema from the source to the target again. Moreover I will add a verification step for the existance of the views and trigger functions before migration.