Hey community,
I am proud to announce PgToolkit v1.0.2 today.
https://github.com/grayhemp/pgtoolkit/tree/v1.0.2
# PgToolkit - tools for PostgreSQL maintenance
Currently the package contains the only tool `pgcompact`, we are
planning to add much more in the future. Stay tuned.
## Changes
### 2014-02-22 - PgToolkit v1.0.2
- Fixed the non working statement timeout in the reindexing process
- Made it use `DROP INDEX CONCURRENTLY` if version is `>=9.2`
- Fixed the randomness of the SET statements order in database adapters
- Made it to process TOAST tables and indexes providing bloat
information and rebuilding instructions
- Set an additional protection against the "incorrect result of cleaning" error
## pgcompact
A tool to reduce bloat for tables and indexes without heavy locks and
full table rebuilding.
If [pgstattuple] is installed `pgcompact` uses it to get a better
statistics. It is highly recommended to be for `TOAST`ed tables and
indexes.
### Usage examples
Shows user manual.
pgcompact --man
Compacts all the bloated tables in all the databases in the cluster
plus their bloated indexes. Prints additional progress information.
pgcompact --all --reindex --verbosity info
Compacts all the bloated tables in the billing database and their
bloated indexes excepts ones that are in the `pgq` schema.
pgcompact --dbname billing --exclude-schema pgq --reindex
### Features
- Requires no dependencies except `Perl >=5.8.8`, so it can just be
copied to server and run
- Works via `DBD::Pg`, `DBD::PgPP` or even `psql` if there are no
former ones, detects and chooses the best option automatically
- Processes either whole cluster or specified tables, schemes, databases only
- Has an ability to exclude tables, schemes or databases from processing
- Performs bloat analysis and processes those tables that have it
only. We recommend to install [pgstattuple] for more precise
estimations.
- Uses non blocking reindex techniques
- Performs indexes bloat analysis and processes only the required ones
- Analyses and rebuilds bloated unique constraints and primary keys
where possible
- Provides TOAST tables and their indexes bloat information and
rebuilding instructions
- Incremental processing, in other words one can stop the process and
continue it at any time later
- Dynamically adjusts behavior for current load of database to not
affect its performance
- Can be run in several parallel sessions on the same instance to
process the tables faster
- Instructs administrators, supplying them with ready to use DDL, to
manually rebuild database objects that can not be rebuilt
automatically
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray.ru@gmail.com