PostgreSQL Tablespaces Test

Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.

Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

Tablespaces should be adequately sized. If not, the tablespaces may not be able to accomodate many critical database objects, thereby causing the performance of the database to suffer. Continuous monitoring of tablespace size and usage is hence important. The PostgreSQL Tablespaces test does just that. This test auto-discovers tablespaces managed by this PostgreSQL server and reports how well the tablespace has been utilized.

Target of the test : PostgreSQL server

Agent deploying the test: An internal/remote agent

Outputs of the test : One set of results for every tablespace in every database that is configured for monitoring in the target PostgreSQL server

Configurable parameters for the test
Parameter Description

Test period

How often should the test be executed

Host

The IP address of the host for which this test is to be configured.

Port

The port on which the server is listening. The default port is 5432.

Username

In order to monitor a PostgreSQL server, you need to manually create a special database user account in every PostgreSQL database instance that requires monitoring. When doing so, ensure that this user is vested with the superuser privileges. The sample script we recommend for user creation for eG monitoring is:

CREATE ROLE eguser LOGIN

ENCRYPTED PASSWORD {‘eguser password’}

SUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

Specify the name of this user in the Username text box.

Password

The password associated with the above Username (can be ‘NULL’). Here, ‘NULL’ means that the user does not have any password.

Confirm Password

Confirm the Password (if any) by retyping it here.

DB Name

The name of the database to connect to. The default is “postgres”.

Include DB

Specify a comma-separated list of databases that you wish to monitor in the Include DB text box.

Exclude DB

Specify a comma-separated list of databases that need to be excluded from monitoring in the Exclude DB text box. By default, this is set to rdsadmin.

Note:

If you are monitoring a PostgreSQL server hosted on AWS cloud, then make sure that you do not remove 'rdsadmin' from the Exclude DB list.

SSL

If the PostgreSQL server being monitored is an SSL-enabled server, then set the SSL flag to Yes. If not, then set the SSL flag to No.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Tablespace size:

Indicates the amount of space currently used in this tablespace.

MB

A high value of this measure indicates that the table consumes a large chunk of space in the tablespace which may cause serious performance issues ranging from slowdown to shutdowns of this database.