Solved: Postgres database updating using hardlink

k3ys · 07-03-2024 11:24 PM

I want to upgrade my cloudsql postgres instances from 14 to 15, but the regular method takes around 15 minutes to do the complete migration. After researching a bit I found that if I use hardlink ie `--link` flag with `pgupgrade`, it would take significantly less time to migrate. I can't find an option to use this flag, is it possible to do this using Cloud SQL for the PostgreSQL in-place upgrade operation?

ms4446

Cloud SQL does not currently support using the --link flag for in-place upgrades. This limitation is due to the robust processes in place to ensure data integrity and minimize the risk of data loss or corruption during the upgrade.

Despite this limitation, there are several best practices that can help reduce downtime and optimize the upgrade process. First, scheduling the upgrade during a maintenance window when database activity is minimal can help mitigate the impact on applications and users. This strategic timing allows for the upgrade to proceed with minimal interference and lower risk of performance issues.

Another approach is to use read replica promotion. By creating a read replica of the current instance, upgrading the replica, and then promoting it to the primary instance, downtime can be reduced. Although there will still be a brief period of read-only operations during the switch, this method can significantly decrease the overall impact on database availability.

Optimizing data and configuration before the upgrade is also beneficial. This can involve reducing the size of large tables and indexes, which may help speed up the upgrade process. Additionally, taking a database snapshot before initiating the upgrade ensures that the previous state can be quickly restored if any issues arise.

View solution in original post

ms4446

Cloud SQL does not currently support using the --link flag for in-place upgrades. This limitation is due to the robust processes in place to ensure data integrity and minimize the risk of data loss or corruption during the upgrade.

Despite this limitation, there are several best practices that can help reduce downtime and optimize the upgrade process. First, scheduling the upgrade during a maintenance window when database activity is minimal can help mitigate the impact on applications and users. This strategic timing allows for the upgrade to proceed with minimal interference and lower risk of performance issues.

Another approach is to use read replica promotion. By creating a read replica of the current instance, upgrading the replica, and then promoting it to the primary instance, downtime can be reduced. Although there will still be a brief period of read-only operations during the switch, this method can significantly decrease the overall impact on database availability.

Optimizing data and configuration before the upgrade is also beneficial. This can involve reducing the size of large tables and indexes, which may help speed up the upgrade process. Additionally, taking a database snapshot before initiating the upgrade ensures that the previous state can be quickly restored if any issues arise.