Have you ever wondered whether is there an easier way to collect data from the Postgres database running in a CP4AIOps namespace?
Let's say you have a need to obtain row count from all tables in the 3 databases that CP4AIOps uses.
OR
Perhaps you have to extract some data from a certain few tables filtered based on a regular expression.
OR
A critical situation that requires you to run a complex and lengthy custom SQL script on the database.
Alright...let's imagine the steps in our head for a moment...
(1) Obtain the CP4AIOps namespace
(2) Identify the secret to extract the username, password, hostname and port.
(3) Oh wait! There are 3 databases (lifecycle, connector/edge, and topology)! Each of them has their own secret!
(4) With details extracted from the secrets, let's construct the Postgres database connection string.
(5) From there, we can connect to a Postgres pod and establish the connection.
(6) Once connected, game on...
From the looks of it, there are 6 steps.
The fact is the knowledge and details needed to achieve all the 6 steps are not trivial and straightforward at all.
Some would create their own script, but how about product version handling? When the name of the secret changed in newer product version? When the port changed? or when there are many repetitive SQL statements or tasks you use regularly?
Now, let's explore how you can save some time by using the CP4AIOps MustGather...
[TEST ENVIRONMENT]
OCP Server = 4.16.11
OC Client = 4.16.11
ODF Version = 4.16.2
CP4AIOps MustGather = 1.25.1
CP4AIOps = 4.7.0 |
To download CP4AIOps MustGather:
https://www.ibm.com/support/pages/node/7022441
To upgrade CP4AIOps MustGather to the latest:
./waiops-mustgather.sh -UD
=====================================================================================================================
First of all, let's explore what CP4AIOps MustGather got to offer...
(1) View modules related to Postgres (-V option of the MustGather)
./waiops-mustgather.sh -V .listviews | grep postgres
product postgres-conn-template A Postgres connection script template
product postgres-tables To display record count for all Postgres tables
(2) Postgres product plugin (-P option of the MustGather)
./waiops-mustgather.sh -P .listparams
...
[POSTGRES]
GET_PSQL_DATA = Collect Postgres data [0 = off / 1 = on] - default = 1
AIMGR_PSQL_DATA_DUMP = Dump Postgres data to file [0 = off / 1 = on] - default = 0
AIMGR_PSQL_ENTRY_ORDER = To set Postgres query order [any valid ORDER BY statement] - default = 1 desc
AIMGR_PSQL_ENTRY_LIMIT = To set Postgres query size [any positive integer value / -1 for all records] - default = 50
AIMGR_PSQL_CUSTOM_SQLFILE = Custom PSQL input file (<database>:<sqlfile> where <database> is optional - if <database> is empty, <sqlfile> is applied to all databases) - default = <blank>
AIMGR_PSQL_CUSTOM_SQLOPT = Custom PSQL command options - default = <blank>
AIMGR_PSQL_TABLE_REGEX = Regex to filter for target table (<database>:<table-regex> where <database> is optional - if <database> is empty, <table-regex> is applied to all databases) - default = <blank>
AIMGR_PSQL_TABLE_COUNT = Perform row count of all Postres tables [true / false] - default = true
...
(3) Various task scripts related to Postgres (-C option of the MustGather)
./waiops-mustgather.sh -C .listcmd
...
postgres-edb.sh Script to collect Postgres EDB data -C postgres-edb.sh
postgres-rebuild.sh Script to delete current Postgres cluster and rebuild it -C postgres-rebuild.sh
...
=====================================================================================================================
Since we are clear now, what are the offerings, let's go through a few use cases...
(A) Quick way to list row count of all Postgres tables (across all 3 databases - lifecycle, connector/edge, and topology)
./waiops-mustgather.sh -V postgres-tables
(B) A basic analaysis on aiops_irb.alerts table
./waiops-mustgather.sh -DRMP aimanager -Z 'COLLECT_ONLY=psql##AIMGR_PSQL_CUSTOM_SQLFILE=aiops_irb:alerts-metrics.psql##AIMGR_PSQL_TABLE_COUNT=false'
(C) Row count and extraction of all rows from a selected few tables based on regular expression (regex)
./waiops-mustgather.sh -DRMP aimanager -Z 'COLLECT_ONLY=psql##DATA_DUMP=1##AIMGR_PSQL_TABLE_REGEX=aiops_irb:^(alerts|relation)##AIMGR_PSQL_ENTRY_LIMIT=-1'
(D) Postgres - PVC utilization
./waiops-mustgather.sh -RMXz pvcusage
(E) How if I only want to get connected to the Postgres databases and do my own stuff?
./waiops-mustgather.sh -V postgres-conn-template
Eg.
(F) Can I write my own Postgres SQL script and have the MustGather executes it?
Of course, you can!
Notice how the output is in HTML format, because we have set AIMGR_PSQL_CUSTOM_SQLOPT with "--html".
./waiops-mustgather.sh -DRMP aimanager -Z 'COLLECT_ONLY=psql##AIMGR_PSQL_CUSTOM_SQLFILE=aiops_irb:/tmp/my.psql##AIMGR_PSQL_CUSTOM_SQLOPT="--html -q"##AIMGR_PSQL_TABLE_COUNT=false'
This is how the output would look like on a browser:
(G) Can we collect data similar to EDB Postgres MustGather?
Referring to the technote of EDB Postgres MustGather, some of the key data collection tasks involve CNP plugin:
https://www.ibm.com/support/pages/mustgather-collecting-data-diagnose-issues-edb-postgres-cloudnativepg
CP4AIOps MustGather user can collect all the needed data by executing:
./waiops-mustgather.sh -C postgres-edb.sh
NOTE: By default, the codes will try to pull the latest CNP plugin from the Internet. For airgap environment, the codes will fallback to using the CNP plugin packaged in CP4AIOps MustGather.
=====================================================================================================================
CP4AIOps MustGather offers an easy yet flexible way to interact with Postgres that runs on CP4AIOps.
Instead of spending time on getting connected to Postgres or remembering the Postgres-related changes to a particular CP4AIOps version, user can now focus more on getting the work done.
More PSQL scripts will be integrated into CP4AIOps MustGather in the future to automate some command data collection and troubleshooting tasks.
We hope you will stay with us in this journey...