Please note that this script is not by any means an official script released by Google Cloud, it can only be used for education and guidance purposes. Anyone who would like to use this automation method in their production environment must first customize the script and own it from a development and support perspective.
Many PostgreSQL database administrators prefer to dynamically maintain time series partitions by adding new partitions and / or dropping partitions with data beyond the retention period. The retention period is usually defined by an organization-level policy that mandates the length of time where the data must be stored. After the data reaches the end of its retention period, it usually makes sense to either move it to a cheaper storage or drop it completely, which helps reduce storage and management cost. In some cases, it also enhances the performance of the database.
To implement this, It’s highly recommended – especially in operational database environments – to detach the partition concurrently to minimize the transaction locking issue and impact on the transactions. One problem is that you can’t use the “concurrently” clause inside a transaction block, including procedures and functions. When you try you will get:
ERROR: ALTER TABLE … DETACH CONCURRENTLY cannot be executed from a function
PostgreSQL extension PG_PARTMAN does a good job in automating adding and dropping partitions; however the problem is that it detaches the partitions without the “concurrently” clause. Another workaround is to use PG_BACKGROUND to run the ‘detach .. concurrently’ command in a background worker and bypass the restriction of running the command inside a transaction block. However, in the Cloud SQL and AlloyDB, PG_BACKGROUND is not a supported extension at the moment.
1. To demonstrate the solution we will create a partitioned table as follows:
Now let’s assume that our retention policy is one year and we need to drop all partitions with older data.
2. Create the following table and function as follows:
3. In order to be able to call psql commands inside a shell script, I will set the password in an environment variable. This is for the case of simplicity but of course not secure, for more secure approaches you have the following options:
4. Create a shell script file with the following commands on the client machine and make it executable. The first command will populate the table with the commands, the second will generate a SQL file and the third will run these commands.
5. Optionally schedule this script to run periodically using cron or any other scheduling tool.
This blog showed how to control data growth within the PostgreSQL Database within Cloud SQL or AlloyDB in an automated manner using the script provided and scheduled to run at periodic intervals.
For more such database related information, please have a look at our resources under Cloud Architecture Center.
Cloud BlogRead More