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

0
votes

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.

1
postgresqlgoogle-cloud-sql
Yes, you will need to do something if you want that data preserved without exporting the 'postgres' db. There is no built-in limitation to PG about the 'postgres' DB, that is specific to Cloud SQL, but I don't know enough about the service to meaningfully speculate about what's at work (my guess is that it's actually a limitation around restoring the 'postgres' db in Cloud SQL, but the consequence is the same). - AdamKG

1 Answers

0
votes

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.