Installing and upgrading whpg-diskquota

Install the whpg-diskquota package on each host in your WarehousePG cluster, as it doesn't ship as part of the WarehousePG server package.

Supported platforms

whpg-diskquota platform support differs by WarehousePG version:

whpg-diskquota 2.4.0 is the first release built for the ppc64le architecture.

WarehousePG 6 doesn't support database or cluster quotas, which require whpg-diskquota 2.4.0 and WarehousePG 7. See the release notes for the WarehousePG version and platforms each whpg-diskquota release targets.

Installing the package

Refer to Downloading and installing an extension for repository setup instructions. The package name is edb-whpg6-diskquota for WarehousePG 6, or edb-whpg7-diskquota for WarehousePG 7.

Registering whpg-diskquota for first use

After you install the package on every host, perform the following steps to configure whpg-diskquota for first use:

1. From the coordinator, create the diskquota database. The launcher uses this database to store the list of monitored databases and, since whpg-diskquota 2.4, the cluster quota.

   → WrapCopy

   createdb diskquota

2. Check for existing shared libraries:

   → WrapCopy

   gpconfig -s shared_preload_libraries

3. Use the output of the previous command to enable diskquota, along with any other shared libraries, and restart WarehousePG:

   • WHPG 7
   • WHPG 6

   → WrapCopy

   gpconfig -c shared_preload_libraries -v '<other_libraries>, diskquota-2.4'
   gpstop -ar

   → WrapCopy

   gpconfig -c shared_preload_libraries -v '<other_libraries>, diskquota-2.3'
   gpstop -ar

4. Register the diskquota extension in each database where you want to enforce disk usage quotas. By default, whpg-diskquota can monitor up to 50 databases. Raise that limit with diskquota.max_monitored_databases before you start the server, if you need to monitor more.

   → WrapCopy

   CREATE EXTENSION diskquota;

5. If the database already has tables, run diskquota.init_table_size_table() to record their current sizes. Until you do, whpg-diskquota stays inactive in that database. It reports the database's usage as 0 and doesn't enforce its quotas. In a database with many files, sizing can take some time. In an empty database, you can skip this step.

   → WrapCopy

   SELECT diskquota.init_table_size_table();

   Run it again if WarehousePG restarts after a crash, or after an operation such as ALTER TABLESPACE or TRUNCATE that changes a table's relfilenode, since whpg-diskquota doesn't lock a relation while computing its size and can record an incorrect size in either situation. In most cases you can ignore the difference, since whpg-diskquota updates the size the next time data is written to the table. To correct it right away, run init_table_size_table() again, then restart WarehousePG, since the running worker only reloads table sizes at startup.

Verifying the installation

Confirm that diskquota is registered in the database, and check its status:

\dx

                           List of installed extensions
   Name    | Version | Schema |         Description
-----------+---------+--------+------------------------------
 diskquota | 2.4     | public | Disk Quota Main Program

SELECT * FROM diskquota.status();

          name          | status
------------------------+---------
 soft limits            | on
 hard limits            | off
 current binary version | 2.4.0
 current schema version | 2.4
(4 rows)

Upgrading whpg-diskquota

First, upgrade the whpg-diskquota package on every host. Refer to Upgrading an extension for downloading and distributing the new package. The package name is edb-whpg6-diskquota for WarehousePG 6, or edb-whpg7-diskquota for WarehousePG 7.

Then replace the diskquota-<version> shared library in shared_preload_libraries, restart WarehousePG, and update the extension in every database where you registered it:

1. Check for existing shared libraries:

   → WrapCopy

   gpconfig -s shared_preload_libraries

2. Use the output of the previous command to replace the old diskquota library with the new one, keeping any other shared libraries, and restart WarehousePG. For example, to upgrade from 2.3 to 2.4:

   → WrapCopy

   gpconfig -c shared_preload_libraries -v '<other_libraries>, diskquota-2.4'
   gpstop -ar

3. Update the extension in every database where you registered diskquota:

   → WrapCopy

   ALTER EXTENSION diskquota UPDATE TO '2.4';

whpg-diskquota pauses during the upgrade and resumes automatically once it completes. Your existing schema, role, and tablespace quotas continue to be enforced after the upgrade, and you can define database and cluster quotas once every database is on 2.4.

Downgrading from 2.4

Before downgrading to 2.3, remove the quotas that are new in 2.4, replace the shared library, and update the extension in every database where you registered it:

1. In every monitored database, delete the 2.4-only quota rows and clear the cluster quota, since a 2.3 worker can't read them and behaves unpredictably on refresh if any remain:

   → WrapCopy

   SELECT diskquota.prepare_downgrade();

   Don't run prepare_downgrade() inside an explicit transaction block, since it commits on its own and refuses to run in one. To cancel a prepared downgrade, run SELECT diskquota.init_table_size_table();.

2. Point shared_preload_libraries back at diskquota-2.3 and restart WarehousePG:

   → WrapCopy

   gpconfig -c shared_preload_libraries -v '<other_libraries>, diskquota-2.3'
   gpstop -ar

3. Update the extension in every database where you registered diskquota:

   → WrapCopy

   ALTER EXTENSION diskquota UPDATE TO '2.3';

   In each database, the command fails and rolls back if you didn't run prepare_downgrade() there first, so an in-place downgrade can't leave 2.4-only quota rows behind by accident.

   Note

   If you plan to restore a pg_dump backup of a 2.4 database into a cluster running whpg-diskquota 2.3, run prepare_downgrade() in the source database before you take the backup. Restoring an unprepared backup bypasses the ALTER EXTENSION check, and the 2.3 worker can't read the 2.4-only quota rows the backup carries. prepare_downgrade() also removes the source database's quota and the source cluster's cluster quota, so after the backup, run init_table_size_table() in the source database and set those quotas again.

Uninstalling whpg-diskquota

Uninstall whpg-diskquota, whether from one database or from every database in the cluster.

1. If you're removing whpg-diskquota from every database, remove the cluster quota first, from any one of them. It's stored once, in the diskquota database, so it outlives DROP EXTENSION and would otherwise apply again the next time you create the extension:

   → WrapCopy

   SELECT diskquota.set_cluster_quota('-1');

2. Pause whpg-diskquota before you drop it from a database, to avoid a chance of deadlock:

   → WrapCopy

   SELECT diskquota.pause();
   DROP EXTENSION diskquota;

Dropping a monitored database

Drop the diskquota extension first from a database you need to drop. The database's whpg-diskquota worker keeps a connection open to it, so DROP DATABASE otherwise fails with database "sales" is being accessed by other users.

1. Pause and drop the extension in the database you want to remove. For example:

   → WrapCopy

   \c sales
   SELECT diskquota.pause();
   DROP EXTENSION diskquota;

2. Connect to a different database and drop it:

   → WrapCopy

   \c postgres
   DROP DATABASE sales;