To check the table sizes of Puppet Enterprise (PE) databases you can either download and install the puppetlabs-support_tasks module and run task st0287, or you can enter commands manually.
Note: This solution will not work on a PostgreSQL instance that is not Puppet managed.
Version and installation information
PE version: All supported
Installation type: PE with a Puppet managed PostgreSQL instance
Solution
To use a task:
Use these steps to install the puppetlabs-support_tasks module and run task st0287 to check the table sizes of PE databases.
Complete the following steps
-
Install the puppetlabs-support_tasks module from the Forge. If you’re already using the module, please update it to the latest version.
-
Navigate to the console, click Tasks. Click Run a task. Under Task, select task
support_tasks::st0287_check_db_table_sizes. -
In the Parameter
dbnameselect the PE database you’d like information about as its Value. Use one of the following:pe-puppetdb,pe-postgres,pe-classifier,pe-rbac,pe-activity,pe-orchestrator,postgresorall -
Under Select targets, select PQL query and enter the following query:
resources[certname] { type = "Service" and title = "postgresqld" } -
Run the task. When it finishes, you will see the database indexes and their sizes along with their associated tables, followed by
-- ST#0287 Task ended: <Date/Time Stamp> --at the end of the output.For example:
List of relations Schema | Name | Type | Owner | Table | Size | Description --------+-------------------------------+-------+---------+--------------------+------------+------------- public | actions_pkey | index | pe-rbac | actions | 16 kB | public | directory_settings_pkey | index | pe-rbac | directory_settings | 16 kB | public | groupings_gid_idx | index | pe-rbac | groupings | 8192 bytes | public | groupings_pkey | index | pe-rbac | groupings | 8192 bytes | public | groupings_ruid_idx | index | pe-rbac | groupings | 8192 bytes | public | object_types_display_name_key | index | pe-rbac | object_types | 16 kB | public | object_types_pkey | index | pe-rbac | object_types | 16 kB | public | permissions_pkey | index | pe-rbac | permissions | 16 kB | public | permissions_triple | index | pe-rbac | permissions | 16 kB | public | roles_display_name_key | index | pe-rbac | roles | 16 kB | public | roles_permissions_pkey | index | pe-rbac | roles_permissions | 16 kB | public | roles_pkey | index | pe-rbac | roles | 16 kB | public | salt_pkey | index | pe-rbac | salt | 16 kB | public | schema_migrations_id_key | index | pe-rbac | schema_migrations | 16 kB | public | schema_migrations_pkey | index | pe-rbac | schema_migrations | 16 kB | public | subject_roles_sid_idx | index | pe-rbac | subject_roles | 16 kB | public | subjects_login_key | index | pe-rbac | subjects | 16 kB | public | subjects_pkey | index | pe-rbac | subjects | 16 kB | public | tokens_id_key | index | pe-rbac | tokens | 16 kB | public | tokens_pkey | index | pe-rbac | tokens | 16 kB | public | tokens_token_hash_unique | index | pe-rbac | tokens | 16 kB | public | tokens_user_label_unique_idx | index | pe-rbac | tokens | 16 kB | public | user_roles_pkey | index | pe-rbac | subject_roles | 16 kB | (23 rows)
If you mistype the PQL query and run the task on a node that is not running the
pe-postgresqlservice you get the output:Node not running pe-postgresql service, please select node which is.
To run the commands manually:
On the node running the pe-postgresql service:
-
Log in as the pe-postgres user:
su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql" -
Display a list of databases that you can connect to. Run:
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;For example:
datname | size -----------------+--------- postgres | 7725 kB template1 | 7725 kB template0 | 7585 kB pe-postgres | 7725 kB pe-rbac | 8757 kB pe-activity | 8525 kB pe-orchestrator | 9181 kB pe-inventory | 7997 kB pe-classifier | 9357 kB pe-puppetdb | 44 MB (10 rows)
-
Choose a database from the output list and connect to it. Run
\c <DATABASE NAME>For example,\c pe-puppetdb -
To display the database indexes and their sizes along with their associated tables, run:
\di+For example:
List of relations Schema | Name | Type | Owner | Table | Size | Description --------+-------------------------------+-------+---------+--------------------+------------+------------- public | actions_pkey | index | pe-rbac | actions | 16 kB | public | directory_settings_pkey | index | pe-rbac | directory_settings | 16 kB | public | groupings_gid_idx | index | pe-rbac | groupings | 8192 bytes | public | groupings_pkey | index | pe-rbac | groupings | 8192 bytes | public | groupings_ruid_idx | index | pe-rbac | groupings | 8192 bytes | public | object_types_display_name_key | index | pe-rbac | object_types | 16 kB | public | object_types_pkey | index | pe-rbac | object_types | 16 kB | public | permissions_pkey | index | pe-rbac | permissions | 16 kB | public | permissions_triple | index | pe-rbac | permissions | 16 kB | public | roles_display_name_key | index | pe-rbac | roles | 16 kB | public | roles_permissions_pkey | index | pe-rbac | roles_permissions | 16 kB | public | roles_pkey | index | pe-rbac | roles | 16 kB | public | salt_pkey | index | pe-rbac | salt | 16 kB | public | schema_migrations_id_key | index | pe-rbac | schema_migrations | 16 kB | public | schema_migrations_pkey | index | pe-rbac | schema_migrations | 16 kB | public | subject_roles_sid_idx | index | pe-rbac | subject_roles | 16 kB | public | subjects_login_key | index | pe-rbac | subjects | 16 kB | public | subjects_pkey | index | pe-rbac | subjects | 16 kB | public | tokens_id_key | index | pe-rbac | tokens | 16 kB | public | tokens_pkey | index | pe-rbac | tokens | 16 kB | public | tokens_token_hash_unique | index | pe-rbac | tokens | 16 kB | public | tokens_user_label_unique_idx | index | pe-rbac | tokens | 16 kB | public | user_roles_pkey | index | pe-rbac | subject_roles | 16 kB | (23 rows)
How can we improve this article?
0 comments
Please sign in to leave a comment.
Related articles