Movatterモバイル変換

pg_repack

pg_repack — utility andPostgres Pro Standard extension to reorganize tables

Synopsis

Description

pg_repack is aPostgres Pro Standard extension which lets you remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes. UnlikeCLUSTER andVACUUM FULL it works online, without holding an exclusive lock on the processed tables during processing.pg_repack is efficient to boot, with performance comparable to usingCLUSTER directly.

pg_repack is a fork of the previoushttps://github.com/reorg/pg_reorg project.

You can choose one of the following methods to reorganize data:

Installation

On Linux systems,pg_repack is provided together withPostgres Pro as a separate pre-built package and requires thepostgrespro-std-11-server package to be installed with all the dependencies. For the list of available packages and detailed installation instructions, seeChapter 16. On Windows systems,pg_repack is automatically installed as part ofPostgres Pro.

Once you havepg_repack installed, load thepg_repack extension in the database you want to process, as follows:

$ psql -c "CREATE EXTENSION pg_repack" -dyour_database

You can later removepg_repack from aPostgres Pro installation usingDROP EXTENSION pg_repack.

If you are upgrading from a previous version ofpg_repack, just drop the old version from the database as explained above and install the new version.

Options

Environment

This utility, like most otherPostgres Pro utilities, also uses the environment variables supported bylibpq (seeSection 32.14).

Examples

Perform an onlineCLUSTER of all the clustered tables in the databasetest, and perform an onlineVACUUM FULL of all the non-clustered tables:

$ pg_repack test

Perform an onlineVACUUM FULL on the tablesfoo andbar in the databasetest (an eventual cluster index is ignored):

$ pg_repack --no-order --table foo --table bar test

Move all indexes of tablefoo to tablespacetbs:

$ pg_repack -d test --table foo --only-indexes --tablespace tbs

Move the specified index to tablespacetbs:

$ pg_repack -d test --index idx --tablespace tbs

Diagnostics

Error messages are reported whenpg_repack fails. The following list shows the cause of errors.

You need to cleanup by hand after fatal errors. To cleanup, just removepg_repack from the database and install it again.

ForPostgres Pro or PostgreSQL 9.1 and newer execute:

DROP EXTENSION pg_repack CASCADE

in the database where the error occurred, followed by

CREATE EXTENSION pg_repack

For previous versions load the script$SHAREDIR/contrib/uninstall_pg_repack.sql into the database where the error occurred and then load$SHAREDIR/contrib/pg_repack.sql again.

INFO: database "db" skipped:pg_repack VER is not installed in the database:pg_repack is not installed in the database when the --all option is specified.

Create thepg_repack extension in the database.

ERROR: pg_repack VER is not installed in the database:pg_repack is not installed in the database specified by --dbname

Create thepg_repack extension in the database.

ERROR: program 'pg_repack V1' does not match database library 'pg_repack V2':There is a mismatch between the pg_repack binary and the database library(.so or .dll).

The mismatch could be due to the wrong binary in thePATH or the wrong database being addressed. Check the program directory and the database; if they are what expected you may need to repeatpg_repack installation.

ERROR: extension 'pg_repack V1' required, found 'pg_repack V2':The SQL extension found in the database does not match the version required by the pg_repack program.

You should drop the extension from the database and reload it as described inthe section called “Installation”.

ERROR: relation "table" must have a primary key or not-null unique keys:The target table doesn't have a PRIMARY KEY or any UNIQUE constraints defined.

Define a PRIMARY KEY or a UNIQUE constraint on the table.

ERROR: query failed: ERROR: column "col" does not exist:The target table doesn't have columns specified by --order-by option.

Specify existing columns.

WARNING: the table "tbl" already has a trigger called z_repack_trigger:The trigger was probably installed during a previous attemptto run pg_repack on the table which was interruptedand for some reason failed to clean up the temporary objects.

You can remove all the temporary objects by dropping and re-creating the extension: seethe section called “Installation” for the details.

WARNING: trigger "trg" conflicting on table "tbl":The target table has a trigger whose name follows z_repack_triggerin alphabetical order.

Thez_repack_trigger should be the last BEFORE trigger to fire. Please rename your trigger so that it sorts alphabetically beforepg_repack one; you can use:

ALTER TRIGGERzzz_my_trigger ONsometable RENAME TOyyy_my_trigger;

ERROR: Another pg_repack command may be running on the table. Please try again later.

There is a chance of deadlock when two concurrentpg_repack commands are run on the same table. So, try to run the command after some time.

WARNING: Cannot create index "schema"."index_xxxxx", already existsDETAIL: An invalid index may have been left behind by a previouspg_repack on the table which was interrupted. Please use DROP INDEX"schema"."index_xxxxx" to remove this index and try again.

A temporary index apparently created bypg_repack has been left behind, and we do not want to risk dropping this index ourselves. If the index was in fact created by an oldpg_repack job which didn't get cleaned up, you should just useDROP INDEX and try therepack command again.

Restrictions

pg_repack comes with the following restrictions.

See also