聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

How to upgrade aws rds postgresql

2020-09-30 23:33 浏览: 1209 次 我要评论(0 条) 字号:

## 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.

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

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

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

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

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

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

DB=> c <dbname> <username>
DB=> dx

https://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions

2.5. check if any prepare transaction, if any, please kill the session and wait for no prepared traction.

DB=> SELECT count(*) FROM pg_catalog.pg_prepared_xacts;

2.6. If any Read Replica instance from , promote the read replica before upgrade.

aws --profile ${AWS_PROF} --region ${REGION} rds promote-read-replica 
    --db-instance-identifier ${READ_REPLICA_INSTANCE_ID}

2.7. Begin upgrade postgresql

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:

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

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.

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.

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}



网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复