PostgreSQL Integration

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.

Monitoring the PostgreSQL is crucial to ensure performance and availability. Xitoring provides a comprehensive monitoring solution for PostgreSQL that allows users to monitor performance, usage, and resource metrics easily.

Enable PostgreSQL Integration

TIP

PostgreSQL (Postgres) is supported on both Linux and Windows, on versions: 12,13,14,15,16

In order to enable PostgreSQL integration on Xitogent please follow this step-by-step guide:

1. Run following queries on your Postgres CLI or GUI:

    create user xitogent with password 'YOUR_PASSWORD';
    grant pg_monitor to xitogent;
    grant SELECT ON pg_stat_database to xitogent;
   

2. Verify that the permissions are correct by running the following command:

    psql -h localhost -U xitogent postgres -c \
    "select * from pg_stat_database LIMIT(1);" \
    && echo -e "\e[0;32mPostgres connection - OK\e[0m" \
    || echo -e "\e[0;31mCannot connect to Postgres\e[0m"
   

3. Now it's required to modify PostgreSQL configuration to enable performance and monitoring metrics. Please note that the configuration file path could vary based on OS and installation method.

   1. On Linux servers you can probably find the configuration file on /etc/postgresql/14/main/postgresql.conf
   2. On Windows servers you can probably find the configuration file on C:\Program Files\PostgreSQL\16\data\postgresql.conf

Required configurations to apply on the file:

track_activities = on
track_counts = on
track_io_timing = on
track_functions = all
shared_preload_libraries = 'pg_stat_statements'

4. Connect to your PostgreSQL again to run the following query: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

5. Now run the following query to verify the setup: SELECT * FROM pg_stat_statements; if you are getting a response, everything is fine.

6. Now run xitogent integrate and select PostgreSQL from the list, and provide host (localhost), port (5432), user, password, and SSL mode.

7. Xitogent will test the connection and set up the rest automatically. After a minute you can see graphs and data on your Server overview.

Setup Triggers for PostgreSQL integration

Xitogent also enables users to create Triggers for PostgreSQL integrations and set up incidents based on specific parameters. These parameters include:

CPU
Memory
Active connections
Idle connections
Cache hit
Total disk space
Database deadlocks
Active queries
Waiting queries
Committed transactions
Rollback transactions
Read disk blocks
Rows returned
Rows selected
Rows inserted
Rows updated
Rows deleted

For instance, users can create a Trigger for Total queries per second that generates an incident if the total queries exceed a specific threshold. On the Trigger page, users can view the current value for each parameter and configure their alerts accordingly.