Contents

• Requirements
• Download
• Installation
• Usage
• Environment
• Examples
• Diagnostics
• Restrictions
• Details
• Releases
• See Also

Requirements

PostgreSQL versions

PostgreSQL 8.3, 8.4, 9.0, 9.1, 9.2, 9.3, 9.4

Disks

Performing a full-table repack requires free disk space about twice as large as the target table(s) and its indexes. For example, if the total size of the tables and indexes to be reorganized is 1GB, an additional 2GB of disk space is required.

Download

You can download pg_repack from the PGXN website. Unpack the archive and follow the installation instructions.

Alternatively you can use the PGXN Client to download, compile and install the package; use:

$ pgxn install pg_repack

Check the pgxn install documentation for the options available.

Installation

pg_repack can be built with make on UNIX or Linux. The PGXS build framework is used automatically. Before building, you might need to install the PostgreSQL development packages (postgresql-devel, etc.) and add the directory containing pg_config to your $PATH. Then you can run:

$ cd pg_repack
$ make
$ sudo make install

You can also use Microsoft Visual C++ 2010 to build the program on Windows. There are project files in the msvc folder.

After installation, load the pg_repack extension in the database you want to process. On PostgreSQL 9.1 and following pg_repack is packaged as an extension, so you can execute:

$ psql -c "CREATE EXTENSION pg_repack" -d your_database

For previous PostgreSQL versions you should load the script $SHAREDIR/contrib/pg_repack.sql in the database to process; you can get $SHAREDIR using pg_config --sharedir, e.g.

$ psql -f "$(pg_config --sharedir)/contrib/pg_repack.sql" -d your_database

You can remove pg_repack from a PostgreSQL 9.1 and following database using DROP EXTENSION pg_repack. For previous Postgresql versions load the $SHAREDIR/contrib/uninstall_pg_repack.sql script or just drop the repack schema.

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

Usage

pg_repack [OPTION]... [DBNAME]

The following options can be specified in OPTIONS.

Options:

-a, --all	repack all databases
-t, --table=TABLE
	repack specific table only
-c, --schema=SCHEMA
	repack tables in specific schema only
-s, --tablespace=TBLSPC
	move repacked tables to a new tablespace
-S, --moveidx	move repacked indexes to TBLSPC too
-o, --order-by=COLUMNS
	order by columns instead of cluster keys
-n, --no-order	do vacuum full instead of cluster
-N, --dry-run	print what would have been repacked and exit
-j, --jobs=NUM	Use this many parallel jobs for each table
-i, --index=INDEX
	move only the specified index
-x, --only-indexes
	move only indexes of the specified table
-T, --wait-timeout=SECS
	timeout to cancel other backends on conflict
-Z, --no-analyze
	don't analyze at end

Connection options:

-d, --dbname=DBNAME
	database to connect
-h, --host=HOSTNAME
	database server host or socket directory
-p, --port=PORT
	database server port
-U, --username=USERNAME
	user name to connect as
-w, --no-password
	never prompt for password
-W, --password	force password prompt

Generic options:

-e, --echo	echo queries
-E, --elevel=LEVEL
	set output message level
--help	show this help, then exit
--version	output version information, then exit

Environment

PGDATABASE, PGHOST, PGPORT, PGUSER

Default connection parameters

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Environment Variables).

Examples

Perform an online CLUSTER of all the clustered tables in the database test, and perform an online VACUUM FULL of all the non-clustered tables:

$ pg_repack test

Perform an online VACUUM FULL on the tables foo and bar in the database test (an eventual cluster index is ignored):

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

Move all indexes of table foo to tablespace tbs:

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

Move the specified index to tablespace tbs:

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

Diagnostics

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

You need to cleanup by hand after fatal errors. To cleanup, just remove pg_repack from the database and install it again: for PostgreSQL 9.1 and following execute DROP EXTENSION pg_repack CASCADE in the database where the error occurred, followed by CREATE EXTENSION pg_repack; for previous version load the script $SHAREDIR/contrib/uninstall_pg_repack.sql into the database where the error occured 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 the pg_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 the pg_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 the $PATH or the wrong database being addressed. Check the program directory and the database; if they are what expected you may need to repeat pg_repack installation.

ERROR: extension 'pg_repack V1' required, found extension '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 in the installation section.

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 attempt to run pg_repack on the table which was interrupted and for some reason failed to clean up the temporary objects.

You can remove all the temporary objects by dropping and re-creating the extension: see the installation section for the details.

WARNING: trigger "trg" conflicting on table "tbl"

The target table has a trigger whose name follows z_repack_trigger in alphabetical order.

The z_repack_trigger should be the last BEFORE trigger to fire. Please rename your trigger so that it sorts alphabetically before pg_repack's one; you can use:

ALTER TRIGGER zzz_my_trigger ON sometable RENAME TO yyy_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 concurrent pg_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 exists DETAIL: An invalid index may have been left behind by a previous pg_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 by pg_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 old pg_repack job which didn't get cleaned up, you should just use DROP INDEX and try the repack command again.

Restrictions

pg_repack comes with the following restrictions.

Details

To perform full table repacks, pg_repack creates a work table in the "repack" schema and sorts the rows in this table. Then, it updates the system catalogs directly to swap the work table and the original one.

To perform index only repacks, pg_repack creates its work index on the target table and then updates the system catalogs directly to swap the work index and the original index.

Releases

• pg_repack 1.3.1
  • Added support for PostgreSQL 9.4.
• pg_repack 1.3
  • Added --schema to repack only the specified schema (issue #20).
  • Added --dry-run to do a dry run (issue #21).
  • Fixed advisory locking for >2B OID values (issue #30).
  • Avoid possible deadlock when other sessions lock a to-be-repacked table (issue #32).
  • Performance improvement for performing sql_pop DELETEs many-at-a-time.
  • Attempt to avoid pg_repack taking forever when dealing with a constant heavy stream of changes to a table.
• pg_repack 1.2
  • Support PostgreSQL 9.3.
  • Added --tablespace and --moveidx options to perform online SET TABLESPACE.
  • Added --index to repack the specified index only.
  • Added --only-indexes to repack only the indexes of the specified table
  • Added --jobs option for parallel operation.
  • Don't require --no-order to perform a VACUUM FULL on non-clustered tables (pg_repack issue #6).
  • Don't wait for locks held in other databases (pg_repack issue #11).
  • Bugfix: correctly handle key indexes with options such as DESC, NULL FIRST/LAST, COLLATE (pg_repack issue #3).
  • Fixed data corruption bug on delete (pg_repack issue #23).
  • More helpful program output and error messages.
• pg_repack 1.1.8
  • Added support for PostgreSQL 9.2.
  • Added support for CREATE EXTENSION on PostgreSQL 9.1 and following.
  • Give user feedback while waiting for transactions to finish (pg_reorg issue #5).
  • Bugfix: Allow running on newly promoted streaming replication slaves (pg_reorg issue #1).
  • Bugfix: Fix interaction between pg_repack and Slony 2.0/2.1 (pg_reorg issue #4)
  • Bugfix: Properly escape column names (pg_reorg issue #6).
  • Bugfix: Avoid recreating invalid indexes, or choosing them as key (pg_reorg issue #9).
  • Bugfix: Never choose a partial index as primary key (pg_reorg issue #22).
• pg_reorg 1.1.7 (2011-08-07)
  • Bugfix: VIEWs and FUNCTIONs could be corrupted that used a reorganized table which has a dropped column.
  • Supports PostgreSQL 9.1 and 9.2dev. (but EXTENSION is not yet)

See Also

• clusterdb
• vacuumdb