datname: The name of the database the connection is currently connected to.

(There are others, however their likelihood is rare, consult the documentation for more information). Active means it’s currently executing a query, ‘idle’ means it’s waiting further input from the client, ‘idle in transaction’ means it’s waiting for further input from the client while holding an open transaction. state: The state of the connection to the database.

Backend_start represents when the connection to the database was established, xact_start is when the current transaction started, and query_start is when the current (or last) query started.

backend_start, xact_start, query_start: These three provide a timestamp of when each started respectively.

If empty (or Null), it originated from localhost.

client_addr: The IP address for which this connection and query originated from.

This can help identify what query / queries a developer may be reporting are running slowly.

query: a text column showing the query that’s currently being executed, waiting to be executed, or was last executed (depending on the state).

Understanding of the columns themselves will help build queries dynamically as needed in the future. NOTE: pg_stat_activity has been known to change structure over time, refining the data it presents. There is a handful of useful information in this view that can help. The pg_stat_activity view (documentation for every column in this view can be found here) contains a row for every server process / connection to the database from a client. The request may come in many different forms, but if “slowness” is the general issue, checking pg_stat_activity is the first step to understand just what’s going on. The more information they give the easier it will be to find the issue. Get as much information as possible from whoever says the database seems slow specific queries, applications connected, timeframes of the performance slowness, etc. Limited users will either be denied or have data omitted. NOTE: The queries to be executed should be done as a superuser, such as ‘postgres’ or any database user granted the superuser permissions. A developer may know how the application acts and responds, but the Database Administrator knows how the database acts and responds to the application, and together, the issue can be found. But it’s rather a set of initial steps that can be taken to help find the common offenders quickly, as well as gain new insight as to what the issue may be.

This is by no extent an exhaustive guide, nor do the steps need to be done in any specific order. Whether it’s a panicked email, or an open ticket for “the database feels slow”, this common task can generally be followed with a few steps to check whether or not there is a problem with PostgreSQL, and what that problem may be. As a PostgreSQL Database Administrator, there are the everyday expectations to check on backups, apply DDL changes, make sure the logs don’t have any game breaking ERROR’s, and answer panicked calls from developers who’s reports are running twice as long as normal and they have a meeting in ten minutes.Įven with a good understanding of the health of managed databases, there will always be new cases and new issues popping up relating to performance and how the database “feels”.

Restart postgresql mac