Monitoring and Auditing Your Scalingo for PostgreSQL® Addon
Each Scalingo for PostgreSQL® addon comes with several tools allowing for monitoring and auditing of your database. These tools give access to the database logs, to some metrics and to running queries figures. Query Statistics can also be enabled if needed.
All these tools are available from the database dashboard.
Inspecting Database Logs
Database logs are a very valuable resource when it comes to monitoring and troubleshooting problems, tracking performance and auditing the database activity.
By default, only the most recent logs are directly and immediately available from the different tools (see below). We call these logs Hot Logs.
Once the logs reach 50MiB, they are compressed and placed in an archive. These archives are Cold Logs. They are still available, but they require a bit more work (see below).
Using the Database Dashboard
- From your web browser, open your database dashboard
- Click the Logs tab
The default view allows to consult the latest hot logs in real-time. The Archives link at the upper-right corner of the page allows to download the cold logs. These have to be manually unarchived to be processed.
Using the Command Line
- Make sure you have correctly setup the Scalingo command line tool
- Run the following command:
- To access the hot logs of this addon:
scalingo --app my-app --addon postgresql logs --lines <number_of_lines>
- To access the cold logs of this addon:
scalingo --app my-app --addon postgresql logs-archives
- To access the hot logs of this addon:
Analyzing Database Metrics
Database metrics are very helpful to identify and track overall performance issues, making them key indicators to watch.
The provided metrics include:
- CPU usage, memory consumption, storage usage and disk input/output operations per second (IOPS) for all nodes composing your database cluster
- CPU usage and memory consumption for all gateway nodes.
Using the Database Dashboard
- From your web browser, open your database dashboard
- Click the Metrics tab
Watching Running Queries
Viewing running queries in real time is another feature we provide to help have a better understanding of what’s going on with your Scalingo for PostgreSQL®. They allow to quickly identify resource-consuming queries, or even stuck ones.
Using the Database Dashboard
- From your web browser, open your database dashboard
- Click the Running Queries tab
Exploring Query Statistics
Query statistics are another useful tool allowing to get precious insights about the database operations and performances over time. Analyzing these data can help identifying costful queries, thus allowing to optimize code or resources.
Query statistics are built by the pg_stat_statements
extension. Once
activated, this extension provides a pg_stat_statements
view that can be
queried as you wish. Detailed information about this view can be found in the
extension official documentation.
Enabling Query Statistics
Gathering query statistics can have a non negligible impact on your database overall performance. Consequently, they are not enabled by default and have to be activated manually.
Using the Database Dashboard
- From your web browser, open your database dashboard
- Click the Query Statistics tab
- Click the Enable Query Statistics button
Using the Command Line
- Access your database using the Interactive Remote Console
- From the PostgreSQL® console, run the following command:
CREATE extension pg_stat_statements;
The output should look like this:
CREATE EXTENSION my_app_4553=>
Using the Database Dashboard
Once enabled, query statistics can be consulted:
- From your web browser, open your database dashboard
- Click the Query Statistics tab