'Is it possible to upgrade Cloud SQL postresql version with data in the 'postgres' database

We are planning to upgrade our Cloud SQL postgresql instances to V12. According to the instance upgrade guide, we should export data from the current instance and import it to a new instance. However, we noticed this sentence regarding exporting:

"Do not export the postgres system database".

Unfortunately, we have user data stored in the 'postgres' database. Does it mean we will have to take additional steps and move the data to another database first?

By the way, is this restriction to exporting the 'postgres' database specific to Cloud SQL? If so, could you explain a little bit why?

Thanks a lot.

postgresqlgoogle-cloud-sql


Solution 1:[1]

There's no restriction to export the postgres system database because this is the default database from every version of PostgresSQL. However, it is not recommended to import because if you are upgrading the version you will use a new version of postgres system database.

Therefore, the postgres system database is not the best practices or not advisable to store your data for your application. The instance upgrade guide is now updated:

Do not export the postgres system database. The postgres database is the default database you connect to before you have created any other databases. Once you create another database, switch to it in order to create tables and insert data. Don't use the postgres database for your application's data.

Solution 2:[2]

There is another option - In-place major version upgrade (Preview): https://cloud.google.com/sql/docs/postgres/upgrade-major-db-version-inplace

You can try whether it works for you on a cloned instance.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JM Gelilio
Solution 2 Sergei Namniasov

Related Questions