| Name | pg-statviz JSON |
| Version |
0.7
JSON |
| download |
| home_page | None |
| Summary | A minimalist extension and utility pair for time series analysis and visualization of PostgreSQL internal statistics. |
| upload_time | 2024-07-21 23:57:59 |
| maintainer | None |
| docs_url | None |
| author | None |
| requires_python | >=3.9 |
| license | pg_statviz Copyright (c) 2024, Jimmy Angelakos Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. IN NO EVENT SHALL JIMMY ANGELAKOS BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF JIMMY ANGELAKOS HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. JIMMY ANGELAKOS SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND JIMMY ANGELAKOS HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. |
| keywords |
open-source
postgres
opensource
database
dataviz
time-series
postgresql
data-visualization
database-management
database-administration
performance-analysis
postgresql-database
postgresql-extension
time-series-analysis
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
No coveralls.
|
# pg_statviz
`pg_statviz` is a minimalist extension and utility pair for time series analysis and visualization
of PostgreSQL internal statistics.
Created for snapshotting PostgreSQL's cumulative and dynamic statistics and performing time series
analysis on them. The accompanying utility can produce visualizations for selected time ranges on
the stored stats snapshots, enabling the user to track PostgreSQL performance over time and
potentially perform tuning or troubleshooting.
## Design Philosophy
Designed with the [K.I.S.S.](https://en.wikipedia.org/wiki/KISS_principle) and
[UNIX](https://en.wikipedia.org/wiki/Unix_philosophy) philosophies in mind, `pg_statviz` aims to be
a modular, minimal and unobtrusive tool that does only what it's meant for: create snapshots
of PostgreSQL statistics for visualization and analysis. To this end, a utility is provided for
retrieving the stored snapshots and creating with them simple visualizations using
[pandas](https://pandas.pydata.org/) and [Matplotlib](https://github.com/matplotlib/matplotlib).
## Installing the extension
### Red Hat Enterprise Linux (v8.0+) / Fedora (37+)
1. Configure the PostgreSQL Yum repository for your Linux distribution, as
[explained here](https://www.postgresql.org/download/linux/redhat).
2. Use `dnf` or `yum` to install the extension for your PostgreSQL version:
sudo dnf install pg_statviz_extension-<pg_version>
OR
sudo yum install pg_statviz_extension-<pg_version>
### PGXN (PostgreSQL Extension Network)
The extension is available on [PGXN](https://pgxn.org/dist/pg_statviz/).
To install from PGXN, either download the zip file and install manually or use the
[PGXN Client](https://pgxn.github.io/pgxnclient/) to install:
pgxn install pg_statviz
### Manual installation
To install manually, clone this repository locally:
git clone https://github.com/vyruss/pg_statviz.git
This will install the extension in the appropriate location for your system (`$SHAREDIR/extension`):
cd pg_statviz
sudo make install
### Enabling the extension
The extension can now be enabled inside the appropriate database like this, e.g. from `psql`:
\c mydatabase
CREATE EXTENSION pg_statviz;
This will create the needed tables and functions under schema `pgstatviz` (note the lack of
underscore in the schema name).
## Installing the utility
The visualization utility can be installed from [PyPi](https://pypi.org/project/pg_statviz/):
pip install pg_statviz
The utility is also available in the
[PostgreSQL Yum Repository](https://www.postgresql.org/download/linux/redhat/) and can be installed
using `dnf` or `yum`:
sudo dnf install pg_statviz
OR
sudo yum install pg_statviz
### Requirements
Python 3.9+ is required for the visualization utility.
## Usage
The extension can be used by superusers, or any user that has `pg_monitor` role privileges. To take
a snapshot, e.g. from `psql`:
SELECT pgstatviz.snapshot();
[comment]::
NOTICE: created pg_statviz snapshot
snapshot
-------------------------------
2024-06-27 11:04:58.055453+00
(1 row)
Older snapshots and their associated data can be removed using any time expression. For example, to
remove data more than 90 days old:
DELETE FROM pgstatviz.snapshots
WHERE snapshot_tstamp < CURRENT_DATE - 90;
Or all snapshots can be removed like this:
SELECT pgstatviz.delete_snapshots();
[comment]::
NOTICE: truncating table "snapshots"
NOTICE: truncate cascades to table "buf"
NOTICE: truncate cascades to table "conf"
NOTICE: truncate cascades to table "conn"
NOTICE: truncate cascades to table "lock"
NOTICE: truncate cascades to table "io"
NOTICE: truncate cascades to table "wait"
NOTICE: truncate cascades to table "wal"
NOTICE: truncate cascades to table "db"
delete_snapshots
------------------
(1 row)
The `pg_monitor` role can be assigned to any user:
GRANT pg_monitor TO myuser;
## Scheduling
Periodic snapshots can be set up with any job scheduler. For example with `cron`:
crontab -e -u postgres
Inside the `postgres` user's crontab, add this line to take a snapshot every 15 minutes:
*/15 * * * * psql -c -d mydatabase "SELECT pgstatviz.snapshot()" >/dev/null 2>&1
## Visualization
Potentially very large numbers of data points can be visualized with the aid of pandas resampling,
displaying the mean value over 100 plot points as a default.
The visualization utility can be called like a PostgreSQL command line tool:
pg_statviz --help
[comment]::
usage: pg_statviz [--help] [--version] [--dbname DBNAME] [-h HOSTNAME] [--port PORT]
[-u USERNAME] [--password] [--daterange FROM TO] [-o OUTPUTDIR]
{analyze,buf,cache,checkp,conn, io,lock,tuple,wait,wal,xact} ...
run all analysis modules
positional arguments:
{analyze,buf,cache,checkp,conn,io,lock,tuple,wait,wal,xact}
analyze run all analysis modules
buf run buffers written analysis module
cache run cache hit ratio analysis module
checkp run checkpoint analysis module
conn run connection count analysis module
io run I/O analysis module
lock run locks analysis module
tuple run tuple count analysis module
wait run wait events analysis module
wal run WAL generation analysis module
xact run transaction count analysis module
options:
--help
--version show program's version number and exit
-d DBNAME, --dbname DBNAME
database name to analyze (default: 'myuser')
-h HOSTNAME, --host HOSTNAME
database server host or socket directory (default: '/var/run/postgresql')
-p PORT, --port PORT database server port (default: '5432')
-U USERNAME, --username USERNAME
database user name (default: 'myuser')
-W, --password force password prompt (should happen automatically) (default: False)
-D FROM TO, --daterange FROM TO
date range to be analyzed in ISO 8601 format e.g. 2024-01-01T00:00
2024-01-01T23:59 (default: [])
-O OUTPUTDIR, --outputdir OUTPUTDIR
output directory (default: -)
### Specific module usage
pg_statviz conn --help
[comment]::
usage: pg_statviz conn [-h] [-d DBNAME] [--host HOSTNAME] [-p PORT] [-U USERNAME] [-W]
[-D FROM TO] [-O OUTPUTDIR] [-u [USERS ...]]
run connection count analysis module
options:
-h, --help show this help message and exit
-d DBNAME, --dbname DBNAME
database name to analyze (default: 'myuser')
--host HOSTNAME database server host or socket directory (default: '/var/run/postgresql')
-p PORT, --port PORT database server port (default: '5432')
-U USERNAME, --username USERNAME
database user name (default: 'myuser')
-W, --password force password prompt (should happen automatically) (default: False)
-D FROM TO, --daterange FROM TO
date range to be analyzed in ISO 8601 format e.g. 2024-01-01T00:00
2024-01-01T23:59 (default: [])
-O OUTPUTDIR, --outputdir OUTPUTDIR
output directory (default: -)
-u [USERS ...], --users [USERS ...]
user name(s) to plot in analysis (default: [])
### Example:
pg_statviz buf --host localhost -d postgres -U postgres -D 2024-06-24T23:00 2024-06-26
### Produces:
[comment]::
## Schema
The `pg_statviz` extension stores its data in the following tables:
Table | Description
--- | ---
`pgstatviz.snapshots` | Timestamped snapshots
`pgstatviz.buf` | Buffer, checkpointer and background writer data
`pgstatviz.conf` | PostgreSQL server configuration data
`pgstatviz.conn` | Connection data
`pgstatviz.db` | PostgreSQL server and database statistics
`pgstatviz.io` | I/O stats data
`pgstatviz.lock` | Locks data
`pgstatviz.wait` | Wait events data
`pgstatviz.wal` | WAL generation data
## Export data
To dump the captured data, e.g. for analysis on a different machine, run:
pg_dump -d <dbname> -a -O -t pgstatviz.* > pg_statviz_data.dump
Load it like this on the target database (which should have `pg_statviz` installed) :
psql -d <other_dbname> -f pg_statviz_data.dump
Alternatively, `pg_statviz` internal tables can also be exported to a tab separated values (TSV) file
for use by other tools:
psql -d <dbname> -c "COPY pgstatviz.conn TO STDOUT CSV HEADER DELIMITER E'\t'" > conn.tsv
These can then be loaded into another database like this, provided the tables exist (installing the extension will create them):
psql -d <other_dbname> -c "COPY pgstatviz.conn FROM STDIN CSV HEADER DELIMITER E'\t'" < conn.tsv
Raw data
{
"_id": null,
"home_page": null,
"name": "pg-statviz",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.9",
"maintainer_email": null,
"keywords": "open-source, postgres, opensource, database, dataviz, time-series, postgresql, data-visualization, database-management, database-administration, performance-analysis, postgresql-database, postgresql-extension, time-series-analysis",
"author": null,
"author_email": "Jimmy Angelakos <vyruss@hellug.gr>",
"download_url": "https://files.pythonhosted.org/packages/eb/1a/af099ac0d113c30205b0eba6343d2e0424657eaf633e494d728be96253dc/pg_statviz-0.7.tar.gz",
"platform": null,
"description": "# pg_statviz\n\n`pg_statviz` is a minimalist extension and utility pair for time series analysis and visualization\nof PostgreSQL internal statistics.\n\nCreated for snapshotting PostgreSQL's cumulative and dynamic statistics and performing time series\nanalysis on them. The accompanying utility can produce visualizations for selected time ranges on\nthe stored stats snapshots, enabling the user to track PostgreSQL performance over time and\npotentially perform tuning or troubleshooting.\n\n## Design Philosophy\n\nDesigned with the [K.I.S.S.](https://en.wikipedia.org/wiki/KISS_principle) and\n[UNIX](https://en.wikipedia.org/wiki/Unix_philosophy) philosophies in mind, `pg_statviz` aims to be\na modular, minimal and unobtrusive tool that does only what it's meant for: create snapshots\nof PostgreSQL statistics for visualization and analysis. To this end, a utility is provided for\nretrieving the stored snapshots and creating with them simple visualizations using\n[pandas](https://pandas.pydata.org/) and [Matplotlib](https://github.com/matplotlib/matplotlib).\n\n## Installing the extension\n\n### Red Hat Enterprise Linux (v8.0+) / Fedora (37+)\n\n1. Configure the PostgreSQL Yum repository for your Linux distribution, as\n[explained here](https://www.postgresql.org/download/linux/redhat).\n2. Use `dnf` or `yum` to install the extension for your PostgreSQL version:\n\n sudo dnf install pg_statviz_extension-<pg_version>\n OR\n sudo yum install pg_statviz_extension-<pg_version>\n\n### PGXN (PostgreSQL Extension Network)\n\nThe extension is available on [PGXN](https://pgxn.org/dist/pg_statviz/).\n\nTo install from PGXN, either download the zip file and install manually or use the\n[PGXN Client](https://pgxn.github.io/pgxnclient/) to install:\n\n pgxn install pg_statviz\n\n### Manual installation\n\nTo install manually, clone this repository locally:\n\n git clone https://github.com/vyruss/pg_statviz.git\n\nThis will install the extension in the appropriate location for your system (`$SHAREDIR/extension`):\n\n cd pg_statviz\n sudo make install\n\n### Enabling the extension\n\nThe extension can now be enabled inside the appropriate database like this, e.g. from `psql`:\n\n \\c mydatabase\n CREATE EXTENSION pg_statviz;\n\nThis will create the needed tables and functions under schema `pgstatviz` (note the lack of\nunderscore in the schema name).\n\n## Installing the utility\n\nThe visualization utility can be installed from [PyPi](https://pypi.org/project/pg_statviz/):\n\n pip install pg_statviz\n\nThe utility is also available in the\n[PostgreSQL Yum Repository](https://www.postgresql.org/download/linux/redhat/) and can be installed\nusing `dnf` or `yum`:\n\n sudo dnf install pg_statviz\n OR\n sudo yum install pg_statviz\n\n### Requirements\n\nPython 3.9+ is required for the visualization utility.\n\n## Usage\n\nThe extension can be used by superusers, or any user that has `pg_monitor` role privileges. To take\na snapshot, e.g. from `psql`:\n\n SELECT pgstatviz.snapshot();\n\n[comment]::\n\n NOTICE: created pg_statviz snapshot\n snapshot\n -------------------------------\n\n 2024-06-27 11:04:58.055453+00\n\n (1 row)\n\nOlder snapshots and their associated data can be removed using any time expression. For example, to\nremove data more than 90 days old:\n\n DELETE FROM pgstatviz.snapshots\n WHERE snapshot_tstamp < CURRENT_DATE - 90;\n\nOr all snapshots can be removed like this:\n\n SELECT pgstatviz.delete_snapshots();\n\n[comment]::\n\n NOTICE: truncating table \"snapshots\"\n NOTICE: truncate cascades to table \"buf\"\n NOTICE: truncate cascades to table \"conf\"\n NOTICE: truncate cascades to table \"conn\"\n NOTICE: truncate cascades to table \"lock\"\n NOTICE: truncate cascades to table \"io\"\n NOTICE: truncate cascades to table \"wait\"\n NOTICE: truncate cascades to table \"wal\"\n NOTICE: truncate cascades to table \"db\"\n delete_snapshots\n ------------------\n\n (1 row)\n\nThe `pg_monitor` role can be assigned to any user:\n\n GRANT pg_monitor TO myuser;\n\n## Scheduling\n\nPeriodic snapshots can be set up with any job scheduler. For example with `cron`:\n\n crontab -e -u postgres\n\nInside the `postgres` user's crontab, add this line to take a snapshot every 15 minutes:\n\n */15 * * * * psql -c -d mydatabase \"SELECT pgstatviz.snapshot()\" >/dev/null 2>&1\n\n## Visualization\n\nPotentially very large numbers of data points can be visualized with the aid of pandas resampling,\ndisplaying the mean value over 100 plot points as a default.\n\nThe visualization utility can be called like a PostgreSQL command line tool:\n\n pg_statviz --help\n\n[comment]::\n\n usage: pg_statviz [--help] [--version] [--dbname DBNAME] [-h HOSTNAME] [--port PORT]\n [-u USERNAME] [--password] [--daterange FROM TO] [-o OUTPUTDIR]\n {analyze,buf,cache,checkp,conn, io,lock,tuple,wait,wal,xact} ...\n\n run all analysis modules\n\n positional arguments:\n {analyze,buf,cache,checkp,conn,io,lock,tuple,wait,wal,xact}\n analyze run all analysis modules\n buf run buffers written analysis module\n cache run cache hit ratio analysis module\n checkp run checkpoint analysis module\n conn run connection count analysis module\n io run I/O analysis module\n lock run locks analysis module\n tuple run tuple count analysis module\n wait run wait events analysis module\n wal run WAL generation analysis module\n xact run transaction count analysis module\n\n options:\n --help\n --version show program's version number and exit\n -d DBNAME, --dbname DBNAME\n database name to analyze (default: 'myuser')\n -h HOSTNAME, --host HOSTNAME\n database server host or socket directory (default: '/var/run/postgresql')\n -p PORT, --port PORT database server port (default: '5432')\n -U USERNAME, --username USERNAME\n database user name (default: 'myuser')\n -W, --password force password prompt (should happen automatically) (default: False)\n -D FROM TO, --daterange FROM TO\n date range to be analyzed in ISO 8601 format e.g. 2024-01-01T00:00\n 2024-01-01T23:59 (default: [])\n -O OUTPUTDIR, --outputdir OUTPUTDIR\n output directory (default: -)\n\n### Specific module usage\n\n pg_statviz conn --help\n\n[comment]::\n\n usage: pg_statviz conn [-h] [-d DBNAME] [--host HOSTNAME] [-p PORT] [-U USERNAME] [-W]\n [-D FROM TO] [-O OUTPUTDIR] [-u [USERS ...]]\n\n run connection count analysis module\n\n options:\n -h, --help show this help message and exit\n -d DBNAME, --dbname DBNAME\n database name to analyze (default: 'myuser')\n --host HOSTNAME database server host or socket directory (default: '/var/run/postgresql')\n -p PORT, --port PORT database server port (default: '5432')\n -U USERNAME, --username USERNAME\n database user name (default: 'myuser')\n -W, --password force password prompt (should happen automatically) (default: False)\n -D FROM TO, --daterange FROM TO\n date range to be analyzed in ISO 8601 format e.g. 2024-01-01T00:00\n 2024-01-01T23:59 (default: [])\n -O OUTPUTDIR, --outputdir OUTPUTDIR\n output directory (default: -)\n -u [USERS ...], --users [USERS ...]\n user name(s) to plot in analysis (default: [])\n\n### Example:\n\n pg_statviz buf --host localhost -d postgres -U postgres -D 2024-06-24T23:00 2024-06-26\n\n### Produces:\n\n\n[comment]::\n\n\n\n\n## Schema\n\nThe `pg_statviz` extension stores its data in the following tables:\n\nTable | Description\n--- | ---\n`pgstatviz.snapshots` | Timestamped snapshots\n`pgstatviz.buf` | Buffer, checkpointer and background writer data\n`pgstatviz.conf` | PostgreSQL server configuration data\n`pgstatviz.conn` | Connection data\n`pgstatviz.db` | PostgreSQL server and database statistics\n`pgstatviz.io` | I/O stats data\n`pgstatviz.lock` | Locks data\n`pgstatviz.wait` | Wait events data\n`pgstatviz.wal` | WAL generation data\n\n## Export data\n\nTo dump the captured data, e.g. for analysis on a different machine, run:\n\n pg_dump -d <dbname> -a -O -t pgstatviz.* > pg_statviz_data.dump\n\nLoad it like this on the target database (which should have `pg_statviz` installed) :\n\n psql -d <other_dbname> -f pg_statviz_data.dump\n\nAlternatively, `pg_statviz` internal tables can also be exported to a tab separated values (TSV) file\nfor use by other tools:\n\n psql -d <dbname> -c \"COPY pgstatviz.conn TO STDOUT CSV HEADER DELIMITER E'\\t'\" > conn.tsv\n\nThese can then be loaded into another database like this, provided the tables exist (installing the extension will create them):\n\n psql -d <other_dbname> -c \"COPY pgstatviz.conn FROM STDIN CSV HEADER DELIMITER E'\\t'\" < conn.tsv\n\n",
"bugtrack_url": null,
"license": "pg_statviz Copyright (c) 2024, Jimmy Angelakos Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. IN NO EVENT SHALL JIMMY ANGELAKOS BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF JIMMY ANGELAKOS HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. JIMMY ANGELAKOS SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN \"AS IS\" BASIS, AND JIMMY ANGELAKOS HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. ",
"summary": "A minimalist extension and utility pair for time series analysis and visualization of PostgreSQL internal statistics.",
"version": "0.7",
"project_urls": {
"Homepage": "https://github.com/vyruss/pg_statviz"
},
"split_keywords": [
"open-source",
" postgres",
" opensource",
" database",
" dataviz",
" time-series",
" postgresql",
" data-visualization",
" database-management",
" database-administration",
" performance-analysis",
" postgresql-database",
" postgresql-extension",
" time-series-analysis"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "eb1aaf099ac0d113c30205b0eba6343d2e0424657eaf633e494d728be96253dc",
"md5": "6d0eb77e3db038e8c4fc6ef13e6d8dbb",
"sha256": "ed3800a0fbd0f6d8df35596627ffe0fd13da75f110cc227f5d0586868a8d4408"
},
"downloads": -1,
"filename": "pg_statviz-0.7.tar.gz",
"has_sig": false,
"md5_digest": "6d0eb77e3db038e8c4fc6ef13e6d8dbb",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9",
"size": 861679,
"upload_time": "2024-07-21T23:57:59",
"upload_time_iso_8601": "2024-07-21T23:57:59.313528Z",
"url": "https://files.pythonhosted.org/packages/eb/1a/af099ac0d113c30205b0eba6343d2e0424657eaf633e494d728be96253dc/pg_statviz-0.7.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-07-21 23:57:59",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "vyruss",
"github_project": "pg_statviz",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [],
"lcname": "pg-statviz"
}
