An error occurred when upgrading postgreSQL 9.6 to 14 in place

[chuckwang]

hi
I upgraded my database using console mode and received an error message.

"db=cloudsqladmin,user=cloudsqladmin ERROR: cannot remove dependency on schema pg_catalog because it is a system object"

I also tried upgrading to version 12 and got the same error.
What should I do next? Doesn't it affect table work.

thanks

[ms4446]

The error message "ERROR: cannot remove dependency on schema pg_catalog because it is a system object" is a common obstacle when upgrading PostgreSQL in Google Cloud SQL. It typically occurs when attempting an in-place upgrade, where you try to upgrade the existing database directly to a newer version. The error arises when custom objects within your database rely on the core pg_catalog schema.

The pg_catalog schema is a fundamental component of PostgreSQL, housing essential system objects and functions. It serves as the backbone of the database, providing the necessary infrastructure for its operation. Modifying this schema directly is not recommended and can lead to unexpected issues.

When faced with this error, the first step is to identify the specific custom objects that depend on pg_catalog. This can be achieved by running a SQL query against the pg_depend table, a system table that tracks dependencies between database objects. By analyzing the results of this query, you can pinpoint the custom objects (like functions, views, or triggers) that need attention before proceeding with the upgrade.

Resolving the Issue: Two Options

1. Drop and Recreate Custom Objects: If you have a relatively small number of dependencies, you can opt to drop (remove) the problematic custom objects before the upgrade. After successfully upgrading to the new PostgreSQL version, you can then recreate these objects from your backed-up definitions. While this method can be faster, it requires meticulousness to ensure that all dependencies are accounted for.

2. Dump and Restore (Recommended): This is the preferred and most reliable approach, particularly for larger or more complex databases. It involves creating a complete backup (or dump) of your PostgreSQL database using the pg_dump utility. Next, you create a new Cloud SQL instance running the desired PostgreSQL version (e.g., 14 or 12) and restore the dump to this new instance. This method guarantees a clean and safe transition to the upgraded version, minimizing the risk of missing dependencies.

Important Considerations

• Downtime: Both methods will temporarily take your database offline during the upgrade process. Plan for this downtime and schedule it during periods of low traffic or usage to minimize disruption.
• Backups: Before making any significant changes to your database, always create a complete backup. This ensures that you have a safe copy of your data to fall back on if something goes wrong.
• Testing: After upgrading, thoroughly test your applications against the new PostgreSQL environment. This helps identify and address any potential compatibility issues that might arise due to the upgrade.

If you encounter any challenges or have questions along the way, don't hesitate to reach out to Google Cloud Support for assistance.

[chuckwang]

I want to try "Drop and Recreate Custom Objects" but need  role: cloudsqladmin to do it without getting [42501] ERROR: permission denied for relation pg_depend.

So options 1 "Drop and Recreate Custom Objects " is actually impossible to achieve, right?

[ms4446]

Upgrading a PostgreSQL database in Google Cloud SQL can be challenging due to dependencies on system objects like the pg_catalog schema. A common error, "ERROR: cannot remove dependency on schema pg_catalog because it is a system object," arises during in-place upgrades when custom objects depend on pg_catalog. This dependency makes the "Drop and Recreate Custom Objects" approach impractical, as cloudsqladmin lacks the necessary permissions to modify system catalog tables.

The recommended solution is the dump and restore method, which avoids modifying system objects directly. Here’s a concise guide:

Create a Backup: Use pg_dump to create a complete backup of your database.

pg_dump -U myuser -h old-host -p old-port -Fc -f backup.dump mydatabase

Set Up a New Instance: Create a new Cloud SQL instance with the desired PostgreSQL version.

Restore the Backup: Use pg_restore to restore the backup to the new instance.

pg_restore -U myuser -h new-host -p new-port -d newdatabase -Fc backup.dump

Verify and Test: Ensure the integrity of the restored database and run tests to confirm application compatibility.

Switch Over: Update your applications to point to the new instance and monitor performance.

Important Considerations:

• Plan for downtime during the upgrade and notify stakeholders accordingly.
• Always maintain a complete backup and perform thorough testing.

The dump and restore method is reliable and effective for upgrading PostgreSQL in Google Cloud SQL, ensuring data integrity and minimizing disruption. If challenges arise, Google Cloud Support is available for assistance.