pg_analyse

Description

Tools to gather useful information from PostgreSQL

This package can function both as Python module and as a command line utility. Command line interface can show gathered information in the form of tables or JSON.

Use it to gather information manually or in Continuous Integration.

Can give you some information on:

Index health (bloat, duplicates, unused, etc.);
Tables missing PKs and indexes;
Slowest queries.

Requirements

Python 3.6+
psycopg 2

Installation

; If you do not have psycopg2 yet, install it as `psycopg2` or `psycopg2-binary`.
; You may also want to install `envbox` to get PG connection settings from .env files.
; Your distribution may require issuing `pip3` command instead of plain `pip`.
$ pip install psycopg2-binary envbox

; If you want to use it just as Python module:
$ pip install pg_analyse

; If you want to use it from command line:
$ pip install pg_analyse[cli]

Usage

Hint

One can set PG_ANALYSE_DSN environment variable instead of explicitly passing DSN to connect to PostgreSQL. If envbox is installed this variable can be defined in .env file .

Python module

from pg_analyse.toolbox import Analyser, analyse_and_format

analyser = Analyser(dsn='user=test')

inspections = analyser.run()
inspection = inspections[0]

print(inspection.alias)
print(inspection.result)

# Shortcut function is available:
out = analyse_and_format()

CLI

; Show known inspections and descriptions:
$ pg_analyse inspections

; Use DSN from the environment variable (see hint above),
; print out complex values (e.g. sizes) in human-friendly way:
$ pg_analyse run --human

; Run certain inspections, override default params.
; Use "common" keyword to pass params common for all inspections.
$ pg_analyse run --one idx_unused --one idx_bloat --args "idx_bloat:schema=my,bloat_min=20;common:schema=my"

; Use explicitly passed DSN:
$ pg_analyse run --dsn "host=myhost.net port=6432 user=test password=xxx sslmode=verify-full sslrootcert=/home/my.pem"
; Local connection as `postgres` user with password:
$ pg_analyse run --dsn "host=127.0.0.1 user=postgres password=yourpass"

; Output analysis result as json (instead of tables):
$ pg_analyse run --fmt json

Adding Inspections

To add a new inspection to pg_analyse:

Compose SQL for inspection and put it into a file under sql/ directory.
Add a subclass of Inspection into inspections/bundled.py. Fill in alias, sql_name attributes (see docstrings in Inspection).

Metadata

Release files for pg_analyse 0.5.0

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for pg_analyse 0.5.0
File	Size	Uploaded
pg_analyse-0.5.0.tar.gz	20.7 kB	Apr 28, 2020	Details

Built distribution (wheel)

Table of built distributions (wheels) for pg_analyse 0.5.0
File	Interpreter	ABI	Platform	Reset
pg_analyse-0.5.0-py2.py3-none-any.whl 27.5 kB Apr 28, 2020	Python 3, Python 2	none	any	Details

Total release size: 48.2 kB

Release files / pg_analyse-0.5.0.tar.gz

Download URL	pg_analyse-0.5.0.tar.gz
Size	20.7 kB
Tags	Source
SHA-256 checksum How to use checksums	`3c0ecc346ba7e790d8ddbee265a3b0d6a945e53e64142ec891ced2ce667f2125`
BLAKE2b-256 checksum How to use checksums	`f2893bc743f24ce1f766c43146228b71feca3d73fb99ab3a3d03aaddab2f83d8`
Upload date	Apr 28, 2020
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`Python-urllib/3.6`

Release files / pg_analyse-0.5.0-py2.py3-none-any.whl

Download URL	pg_analyse-0.5.0-py2.py3-none-any.whl
Size	27.5 kB
Tags	Python 2 Python 3
SHA-256 checksum How to use checksums	`9191ff8a651549a3f32a7b81fe00aea63ccdec735e8c0aea32404fc85210a1bc`
BLAKE2b-256 checksum How to use checksums	`249a1f0492ce468c3b2497a2bce58fb1f3c87d33aeae137b2351a6271157cc64`
Upload date	Apr 28, 2020
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`Python-urllib/3.6`