## 0. Read the summary of upgrade pg
https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html
## 1. Prepare Action
1.1. Choose Preferred Upgrade Targets from :
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html
1.2 Set the env variables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
export REGION=ap-southeast-1 export AWS_PROF=akProfile_mmp_global_vg export UPGRADE_INSTANCE_ID=pg-for-coolap-vg-restore-testupgrade export DB_CURRENT_VERSION=`aws --profile ${AWS_PROF} --region ${REGION} rds describe-db-instances --db-instance-identifier=${UPGRADE_INSTANCE_ID} |grep EngineVersion | awk '{print $2}' |awk -F "[\"\"]" '{print $2}'` export ENDPOINT=https://rds.${REGION}.amazonaws.com export READ_REPLICA_INSTANCE_ID=`aws --profile ${AWS_PROF} --region ${REGION} rds describe-db-instances --db-instance-identifier=${UPGRADE_INSTANCE_ID} --output text |grep READREPLICADBINSTANCEIDENTIFIERS |awk '{print $2}'` export DB_NEW_VERSION=<Version_From_Step_1.1> echo "REGION: ${REGION}" echo "AWS_PROF: ${AWS_PROF}" echo "UPGRADE_INSTANCE_ID: ${UPGRADE_INSTANCE_ID}" echo "DB_CURRENT_VERSION: ${DB_CURRENT_VERSION}" echo "DB_NEW_VERSION: ${DB_NEW_VERSION}" echo "ENDPOINT: ${ENDPOINT}" echo "READ_REPLICA_INSTANCE_ID: ${READ_REPLICA_INSTANCE_ID}" |
1.3. Check the current instance infomation,check if non-default option and parameter group
|
1 2 3 |
aws --profile ${AWS_PROF} --region ${REGION} rds describe-db-instances --db-instance-identifier=${UPGRADE_INSTANCE_ID} \ --query 'DBInstances[*].{ID:DBInstanceIdentifier,Name:DBName,EngineName:Engine,EngineVersion:EngineVersion,Public:PubliclyAccessible,Type:DBInstanceClass,OptionGroup:OptionGroupMemberships[*].OptionGroupName|[0],ParameterGroup:DBParameterGroups|[0].DBParameterGroupName, VpcId:DBSubnetGroup.VpcId, ReadReplica:ReadReplicaDBInstanceIdentifiers|[0]}' \ --output table |
1.4. Check any vailable target major version
|
1 |
aws --profile ${AWS_PROF} rds describe-db-engine-versions --engine postgres --region ${REGION} --endpoint $ENDPOINT --output table --query 'DBEngineVersions[*].ValidUpgradeTarget[?IsMajorVersionUpgrade==`true`].{EngineVersion:EngineVersion}' --engine-version ${DB_CURRENT_VERSION} |
Double check with the version which you choose in step 1.1
## 2. Upgrade Action
2.1. List all database in the instance
|
1 |
DB=> SELECT d.datname FROM pg_catalog.pg_database d WHERE d.datallowconn = true; |
2.2. Check if any REG data type, these data type should be handle(remove or change),before upgrade
|
1 2 3 4 5 6 7 8 9 10 11 |
DB=> SELECT count(*) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid IN ('pg_catalog.regproc'::pg_catalog.regtype, 'pg_catalog.regprocedure'::pg_catalog.regtype, 'pg_catalog.regoper'::pg_catalog.regtype, 'pg_catalog.regoperator'::pg_catalog.regtype, 'pg_catalog.regconfig'::pg_catalog.regtype, 'pg_catalog.regdictionary'::pg_catalog.regtype) AND c.relnamespace = n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema'); |
2.3 If upgrade from pg 9.3, check if any exist LINE data type,these data type should be handle(remove or change),before upgrade
|
1 2 3 4 5 6 7 8 |
DB=> SELECT count(*) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid = 'pg_catalog.line'::pg_catalog.regtype AND c.relnamespace = n.oid AND n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema'); |
2.4. check the extension support
|
1 2 |
DB=> \c <dbname> <username> DB=> \dx |
2.5. check if any prepare transaction, if any, please kill the session and wait for no prepared traction.
|
1 |
DB=> SELECT count(*) FROM pg_catalog.pg_prepared_xacts; |
2.6. If any Read Replica instance from , promote the read replica before upgrade.
|
1 2 |
aws --profile ${AWS_PROF} --region ${REGION} rds promote-read-replica \ --db-instance-identifier ${READ_REPLICA_INSTANCE_ID} |
2.7. Begin upgrade postgresql
|
1 2 3 4 5 |
aws --profile ${AWS_PROF} --region ${REGION} rds modify-db-instance \ --db-instance-identifier ${UPGRADE_INSTANCE_ID} \ --engine-version ${DB_NEW_VERSION} \ --no-allow-major-version-upgrade \ --apply-immediately |
2.8. monitor the instance upgrade status:
|
1 2 3 4 5 6 |
while true do echo "-----`date`------" aws --profile ${AWS_PROF} --region ${REGION} rds describe-db-instances --db-instance-identifier=${UPGRADE_INSTANCE_ID} |grep DBInstanceStatus sleep 5 done |
## 3. Post Action
3.1. Update the pg extension to new version, new version could be found in step 3.5
|
1 2 3 |
DB=> ALTER EXTENSION PostgreSQL-extension UPDATE TO 'new-version'; For example: DB=> ALTER EXTENSION pg_trgm update to '1.4'; |
3.2. Delete the read replica after you confirm it will not be used.
|
1 2 3 |
aws --profile ${AWS_PROF} --region ${REGION} rds delete-db-instance \ --db-instance-identifier ${READ_REPLICA_INSTANCE_ID} \ --final-db-snapshot-identifier ${READ_REPLICA_INSTANCE_ID}-FinalSnapshot-`date +%Y%m%d%H%M%S%N` |
3.3. Create new read replica for upgrade instance if necessary.
|
1 2 3 4 5 |
aws --profile ${AWS_PROF} --region ${REGION} rds create-db-instance-read-replica \ --db-instance-identifier ${READ_REPLICA_INSTANCE_ID} --no-multi-az \ --no-auto-minor-version-upgrade --no-deletion-protection --copy-tags-to-snapshot \ --no-publicly-accessible --storage-type gp2 \ --source-db-instance-identifier ${UPGRADE_INSTANCE_ID} |