Monitor PostgreSQL for Availability and Performance

 Are you using PostgreSQL database in your application? You can use CopperEgg to monitor your PostgreSQL database for availability and performance. As an illustration, you can configure and receive alerts for the following scenarios:

  • Performance: alert if the PostgreSQL performance is poor.
  • If the connections value is significantly above its normal range, that would imply that the PostgreSQL database is under heavy load.
  • deadlocks value is significantly above its normal range, that would imply that the PostgreSQL database is under unoptimized operations.

Prerequisites

CopperEgg offers a SAAS based out-of-the-box solution for monitoring PostgreSQL for performance and availability. If you are not familiar with the concept of custom metrics as used in CopperEgg, please go through Custom Metrics information once before continuing with this document.

  1. CopperEgg account.

If you don’t already have an account, you can sign up for a 14 day FREE TRIAL of CopperEgg (no Credit Card required).

  1. PostgreSQL up and running in your environment.
  2. Root access to server to install the PostgreSQL agent.

Monitor PostgreSQL using out-of-the-box custom metrics installer from CopperEgg

There are three steps that you need to do to start monitoring a PostgreSQL service:

  1. PostgreSQL Agent Installation
  2. Verify that your PostgreSQL Dashboard is up and running
  3. Configure alerts to be notified about PostgreSQL performance issues

1.PostgreSQL Agent Installation

image1.png

Figure 1: In your CopperEgg Account, navigate to Custom>Getting Started

Login to your CopperEgg account and navigate to Custom tab -> Getting Started. Click on PostgreSQL icon.  image3.png.

image2.png

Figure 2: The PostgreSQL agent installation script

Click on image5.png icon to copy the installer script to your clipboard. Paste the command into the terminal window on the server where you want to install the agent.  Script can be run on the server running PostgreSQL, or it can be a different server, but this server will need to be continually running in order to monitor PostgreSQL.

As soon as the script is run on a terminal with root access, it starts downloading the required PostgreSQL monitoring agent on your server. You will need to configure the following items in the agent installation process. Item specified in square brackets ( [ ] ) are the default.

image4.png

Figure 3: Executing the PostgreSQL agent installation script with user as copperegg, Group as copperegg and monitoring frequency as 60 seconds

  1. User and Group for running service as default values: [copperegg][copperegg]
  2. Monitoring frequency: 15, [60], 300, 900, 3600 seconds

image7.png

Figure 4: Executing the PostgreSQL agent installation script with group name as  “PostgreSQL”, group label as “PostgreSQL metrics” and  Dashboard  as “PostgreSQL”

  1. Group label, group name and dashboard name for custom metrics

[PostgreSQL][PostgreSQL Metrics][PostgreSQL]

image6.png

Figure 5: Executing the PostgreSQL agent installation script with unique id , server URL, port, ssl mode, database name, username and password. Only one PostgreSQL database is added for monitoring.

  1. Unique name for this db server. [<user>-PostgreSQL]
  2. Server details : URL and Port for service. [localhost][5437]
  3. Database Name : [postgres]
  4. Username and Password to login to database.
  5. After configuring one node, you’ll get an option to add more PostgreSQL databases and servers.

image9.png

Figure 6: Executing the PostgreSQL agent installation script with an upstart init file and the default value for log file name. The installation completes successfully!

Configure log file name and upstart job name (defaults present) [/usr/local/copperegg/log/PostgreSQL_metrics.log][/etc/init/revealmetrics_PostgreSQL.conf]

2.Verify that your PostgreSQL Dashboard is up and running

Navigate to Dashboard and you will see a new PostgreSQL Dashboards have been added automatically by the installer.

image8.png

Figure 7: A new Dashboard for PostgreSQL and PostgreSQL has been added

As the installed script executes, you will notice data samples appearing on the widgets for this Dashboard.

image12.png

Figure 8: Widget inside PostgreSQL Dashboard displaying count of commits.

image10.png

Figure 9: Widget inside PostgreSQL Dashboard displaying count of Rollbacks.

image11.png

Figure 10: Widget inside PostgreSQL Dashboard displaying count of Rows Fetched.

Monitored metrics

CopperEgg currently monitors 42 metrics which cover most of the important metrics for PostgreSQL from a performance and stability perspective. You can see these 42 metrics by navigating to the Custom Tab > Custom Objects and selecting Details on the Custom Metrics Group Label [PostgreSQL Metrics] that was given as installation input. For some of these metrics, alerts have been automatically created (see section 3 below). To create new alerts see section: Configuring your own Alerts for the PostgreSQL Database.

The metrics are:

  • commits : Commits
  • rollbacks : Rollbacks
  • disk_reads : Disk Reads
  • buffer_hits : Buffer Hits
  • rows_returned : Rows Returned
  • rows_fetched : Rows Fetched
  • rows_inserted : Rows Inserted
  • rows_updated : Rows Updated
  • rows_deleted : Rows Deleted
  • sequential_scans : Sequential Scans
  • live_rows_fetched_by_seqscan : Live Rows Fetched by Sequential Scans
  • index_scans : Index Scans
  • live_rows_fetched_by_idxscan : Live Rows Fetched by Index Scans
  • rows_hot_updated : Rows HOT Updated
  • live_rows : Live Rows
  • dead_rows : Dead Rows
  • deadlocks : Deadlocks (alert automatically created)
  • temp_bytes : Temp Bytes
  • temp_files : Temp Files
  • checkpoints_scheduled : Checkpoints Scheduled
  • checkpoints_requested : Checkpoints Requested
  • buf_written_in_checkpoints : Buffers Written During Checkpoints
  • buf_written_by_bgwriter : Buffers Written by Background Writer
  • buf_written_by_backend : Buffers Written by Backend
  • buf_allocated : Buffers Allocated
  • fsync_calls_executed : fsync Calls Executed
  • checkpoint_writing_time : Checkpoint Processing – Writing Time
  • checkpoint_sync_time : Checkpoint Processing – Synchronizing Time
  • db_size : Database Size (alert automatically created)
  • locks : Locks
  • connections : Connections
  • max_connections : Max Connections
  • percent_used_connections : Percentage Used Connections (alert automatically created)
  • heap_blocks_read : Heap Blocks Read
  • heap_blocks_hit : Heap Blocks Hit
  • index_blocks_read : Index Blocks Read
  • index_blocks_hit : Index Blocks Hit
  • toast_block_read : Toast Block Read
  • toast_blocks_hit : Toast Block Hit
  • Toast_index_block_read : Toast Index Blocks Read
  • Toast_index_block_hit : Toast Index Blocks Hit
  • Index_row_read : Index Row Read

3.Configure Alerts to be notified about Postgres performance issues

Default Pre-Configured Alerts for the MySQL Database

As soon as your PostgreSQL Database Monitoring Dashboard comes up, you will notice that some alert configurations have been automatically created by our Installation Script (under Alerts > Configure Alerts). These pre-configured alerts for PostgreSQL Database include:

Sr. No. Metric Name Definition Alert Definitions Duration
1 Deadlocks The number of deadlocks detected in this database Alert will be triggered for each deadlock situation. 3 Min
2 DB Size The disk space used by this database
  1. Warning Alert will be triggered when size becomes 60% of memory.
  2. Alert will be triggered when size exceeds 70% of memory.
3 Min
3 Percent Used Connections The number of connections to this database as a fraction of the maximum number of allowed connections Alert will be triggered when used connections reach to 80%. 3 Min

If the alerting condition is met for these pre-configured alerts, some alert notifications may also be triggered.

Configuring your own Alerts for the PostgreSQL Database

image13.png

Figure 11: A new alert being added that will be triggered when Connections metric exceeds a threshold of 2000 within a time period of 1 min

Alerts can also be configured against any of these 42 monitored metrics for PostgreSQL.

You can configure new alerts that will be triggered when performance issues with PostgreSQL arise. You can also configure the notification mechanisms for a triggered alert.

  1. Go to Alerts Tab > Configure alerts and click on “New Alerts” button
  2. Provide values for these fields in the New Alert page:
  • Description: A description of the alert that will be easily recognized by you and your team if the alert is triggered
  • Alert me when: Select the metric of interest and the condition upon which the alert is triggered. In the Alert me when dropdown, you can prefill “Custom: <your PostgreSQL Metric Group>” to get only the list of monitored metrics for your specific PostgreSQL database. From this list, you can then select the specific metric for which you want to configure an alert. As an illustration, in Figure 11, the chosen metric of interest in ‘Alert me when’ is ‘Custom: PostgreSQL Metrics1: connections’ from the PostgreSQL Metrics group and the condition for triggering the alert is if the average value is more than 2000.
  • For at least: The duration for which the alert condition must be valid for the alert to be triggered
  • Matching tags: By default, (match everything) is chosen. If needed, you can attach tags to a particular metric object and select those tags here, which would cause only alerts on that metric object to be triggered.
  • Excluding tags: By default, (exclude nothing) is chosen. If needed, you can attach tags to a particular metric object and select those tags here, which would cause alerts on that metric object to be skipped.
  • Annotate: When enabled, an annotation is automatically created when the alert is triggered. Annotations will be visible in the custom metrics dashboard where the data stream is displayed.
  • Automatic Clear: When enabled, the alert issue is automatically cleared if the triggering condition is no longer true
  • Notify on clear: When enabled, notifications are also sent when the alert issue is cleared. Please do note that notifications are always sent when the alert issue is triggered.
  • Send Notifications To: Here you can configure the notification mechanisms by which the alert is communicated to you and others in your team.

CopperEgg supports notifying different sets of users with differing notification mechanisms for each alert type.

Notification mechanisms include:

  • Email
  • SMS
  • PagerDuty
  • Twitter
  • HipChat
  • Campfire
  • Slack
  • OpsGenie

Webhooks are also exposed for clients to configure custom notification mechanisms.

More about setting up website probe alerts can be found here.

CopperEgg Free Trial! Sign up today!

Sign up for a 14 day  of CopperEgg (no Credit Card required).

You can find more information about CopperEgg pricing options here.

We charge only $0.08 per month per metric!

Sign up today!