How to Upgrade AWS RDS PostgreSQL With Extensions(2)

In another post “how to upgrade aws rds Postgresql with extensions(1)” we have demonstrated how to upgrade AWS PostgreSQL RDS 9.5.23-R1 to AWS PostgreSQL RDS 9.6.20-R1 with PostGIS extension. According to AWS doc about PostGIS extension:

If a database uses the PostGIS extension, you can’t skip major versions for some source to target combinations. For these circumstances, upgrade in steps to the next major version one step at a time until you reach the desired target version.

So in this post, we will show how to upgrade AWS PostgreSQL RDS 9.6.20-R1 with PostGIS extension to AWS PostgreSQL RDS 10.15-R1.

Subscribe to get access

Read more of this content when you subscribe today.

Tasks of After Major Version Upgrade

  • Run the ANALYZE operation to refresh the pg_statistic table.
  • Optionally, use Amazon RDS to view two logs that the pg_upgrade utility produces. These are pg_upgrade_internal.log and pg_upgrade_server.log. Amazon RDS appends a timestamp to the file name for these logs. You can view these logs as you can any other log. For more information, see Amazon RDS database log files.You can also upload the upgrade logs to Amazon CloudWatch Logs. For more information, see Publishing PostgreSQL logs to CloudWatch Logs.
  • To verify that everything works as expected, test your application on the upgraded database with a similar workload. After the upgrade is verified, you can delete this test instance.

How to Upgrade AWS RDS PostgreSQL With Extensions(1)

One of the client just received notifications about end of support for AWS PostgreSQL RDS 9.5. They do have a couple of PostgreSQL RDS 9.5 in AWS. Some of them are using PostGIS extensions.

This post demonstrates step by step how to upgrade AWS PostgreSQL RDS 9.5.23-R1 to AWS PostgreSQL RDS 9.6.20-R1. Certainly the upgrading method in this post can be applied to other versions as well like AWS PostgreSQL RDS 10, 11 and 12, etc.

Subscribe to get access

Read more of this content when you subscribe today.

How to Configure SQL Developer to Connect to PostgreSQL

A client likes to use Oracle SQL Developer to connect to PostgreSQL databases. During the configuring, some errors occurred. This post shows how to resolve the configuration issues from Oracle SQL Developer and third party libraries .

The typical issue is you will get the following errors when connecting to PostgreSQL database by using non ‘postgres’ users.

Failure -FATAL: database "user1" does not exist

Subscribe to get access

Read more of this content when you subscribe today.

So it is not correct that we have to use ‘postgres’ username which is the same name as database name to connect to PostgreSQL databases. Instead, any user can be used to connect to a PostgreSQL database.

Code to Report Table Index Schema or Database Fragmentation in PostgreSQL

We are always asked by a client or our manager about how fragmented a table, an index, a schema or whole database is in PostgreSQL database. The following code will answer this question with the following information reported for a schema.

Toal 7415.00 MB, Free 1918.87 MB, Fragmentation 25.88 Percent

Please do NOT run the code during busy or peak hours, ideally run the code in after hours or off-peak hours.

Subscribe to get access

Read more of this content when you subscribe today.

How to Speed Up Index Creation in AWS PostgreSQL RDS

I was asked by a client about how to speed up index creation in a terabytes AWS PostgreSQL database. This post tries to explain all the details, and give some examples in real situation.

Subscribe to get access

Read more of this content when you subscribe today.