PGAdmin GUI Server Group Connections List Repository

A client opens PGAdmin GUI with the following messages:

{"success":0,"errormsg":"(sqlite3.OperationalError) no such table: user\n[SQL: SELECT anon_1.user_id AS anon_1_user_id, anon_1.user_email AS anon_1_user_email, anon_1.user_username AS anon_1_user_username, anon_1.user_password AS anon_1_user_password, anon_1.user_active AS anon_1_user_active, anon_1.user_confirmed_at AS anon_1_user_confirmed_at, anon_1.user_masterpass_check AS anon_1_user_masterpass_check, anon_1.user_auth_source AS anon_1_user_auth_source, role_1.id AS role_1_id, role_1.name AS role_1_name, role_1.description AS role_1_description \nFROM (SELECT user.id AS user_id, user.email AS user_email, user.username AS user_username, user.password AS user_password, user.active AS user_active, user.confirmed_at AS user_confirmed_at, user.masterpass_check AS user_masterpass_check, user.auth_source AS user_auth_source \nFROM user \nWHERE user.id = ?\n LIMIT ? OFFSET ?) AS anon_1 LEFT OUTER JOIN (roles_users AS roles_users_1 JOIN role AS role_1 ON role_1.id = roles_users_1.role_id) ON anon_1.user_id = roles_users_1.user_id]\n[parameters: ('1', 1, 0)]\n(Background on this error at: http://sqlalche.me/e/13/e3q8)","info":"","result":null,"data":null}

This message means the repository of PostgreSQL database connections details including credentials of Server Group is unavailable for PGAdmin.

The repository of PostgreSQL database connections details including credentials of Server Group is stored in SQLLite 3 format under “%UserProfile%\AppData\Roaming\pgAdmin\pgadmin4.db”.

It is strongly suggested to backup this file frequently if you have a long list of Server Group connections.

How to Upgrade AWS RDS PostgreSQL With Extensions(5)

In another four posts, we have upgraded from AWS PostgreSQL RDS 9.5.23-R1 to 9.6.20-R1, 9.6.20-R1 to 10.15, 10.15 to 11.10 and 11.10 to 12.5 respectively.

how to Upgrade AWS RDS PostgreSQL with Extensions(1) from 9.5 to 9.6
How to Upgrade AWS RDS PostgreSQL with Extensions(2) from 9.6 to 10.15
How to Upgrade AWS RDS PostgreSQL with Extensions(3) from 10.15 to 11.10
How to Upgrade AWS RDS PostgreSQL with Extensions(4) from 11.10 to 12.5

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 12.5 with PostGIS extension to AWS PostgreSQL RDS 13.1.

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(4)

In another three posts, we have upgraded from AWS PostgreSQL RDS 9.5 to 9.6, 9.6 to 10.15, and 10.15 to 11.10 respectively.

how to Upgrade AWS RDS PostgreSQL with Extensions(1) from 9.5 to 9.6
How to Upgrade AWS RDS PostgreSQL with Extensions(2) from 9.6 to 10.15
How to Upgrade AWS RDS PostgreSQL with Extensions(3) from 10.15 to 11.10

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 11.10 with PostGIS extension to AWS PostgreSQL RDS 12.5.

Subscribe to get access

Read more of this content when you subscribe today.

ERROR: column c.relhasoids does not exist

In PostgreSQL 12, the following errors occur when query some pg_ tables as below:

=> \d pg_class
ERROR:  column c.relhasoids does not exist
LINE 1: ...riggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoi...
                                                             ^
=> select * from pg_class;
ERROR:  syntax error at or near "ERROR"
LINE 1: ERROR:  column c.relhasoids does not exist
        ^

Subscribe to get access

Read more of this content when you subscribe today.

How to Upgrade AWS RDS PostgreSQL With Extensions(3)

In another two posts, we have upgraded from AWS PostgreSQL RDS 9.5.23-R1 to 9.6.20-R1, and 9.6.20-R1 to 10.15-R1 respectively.

how to Upgrade AWS RDS PostgreSQL with Extensions(1) from 9.5 to 9.6
How to Upgrade AWS RDS PostgreSQL with Extensions(2) from 9.6 to 10.15

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 10.15-R1 with PostGIS extension to AWS PostgreSQL RDS 11.10-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.