pg_repack -- Reorganize tables in PostgreSQL databases with minimal locks
pg_repack is a PostgreSQL extension which lets you remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes. Unlike CLUSTER and VACUUM 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 using CLUSTER directly.
You can choose one of the following methods to reorganize:
- Online CLUSTER (ordered by cluster index)
- Ordered by specified columns
- Online VACUUM FULL (packing rows only)
- Only superusers can use the utility.
- Target table must have a PRIMARY KEY, or at least a UNIQUE total index on a NOT NULL column.
- PostgreSQL versions
- PostgreSQL 8.2, 8.3, 8.4, 9.0, 9.1, 9.2
- RHEL 5.2, Windows XP SP3
- Requires free disk space twice as large as the target table(s) and 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.
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.
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.
pg_repack [OPTION]... [DBNAME]
The following options can be specified in OPTIONS.
-a, --all repack all databases -n, --no-order do vacuum full instead of cluster -o, --order-by=COLUMNS order by columns instead of cluster keys -t, --table=TABLE repack specific table only -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
Options to order rows. If not specified, pg_repack performs an online CLUSTER using cluster indexes. Only one option can be specified. You may also specify target tables or databases.
- -n, --no-order
- Perform an online VACUUM FULL.
- -o COLUMNS [,...], --order-by=COLUMNS [,...]
- Perform an online CLUSTER ordered by the specified columns.
- -t TABLE, --table=TABLE
- Reorganize the specified table only. By default, all eligible tables in the target databases are reorganized.
- -T SECS, --wait-timeout=SECS
- pg_repack needs to take an exclusive lock at the end of the reorganization. This setting controls how many seconds pg_repack will wait to acquire this lock. If the lock cannot be taken after this duration, pg_repack will forcibly cancel the conflicting queries. If you are using PostgreSQL version 8.4 or newer, pg_repack will fall back to using pg_terminate_backend() to disconnect any remaining backends after twice this timeout has passed. The default is 60 seconds.
- -Z, --no-analyze
- Disable ANALYZE after the reorganization. If not specified, run ANALYZE after the reorganization.
Options to connect to servers. You cannot use --all and --dbname or --table together.
- -a, --all
- Reorganize all databases.
- -d DBNAME, --dbname=DBNAME
- Specifies the name of the database to be reorganized. If this is not specified and -a (or --all) is not used, the database name is read from the environment variable PGDATABASE. If that is not set, the user name specified for the connection is used.
- -h HOSTNAME, --host=HOSTNAME
- Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.
- -p PORT, --port=PORT
- Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.
- -U USERNAME, --username=USERNAME
- User name to connect as.
- -w, --no-password
- Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.
- -W, --password
Force the program to prompt for a password before connecting to a database.
This option is never essential, since the program will automatically prompt for a password if the server demands password authentication. However, pg_repack will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.
- -e, --echo
- Echo commands sent to server.
- -E LEVEL, --elevel=LEVEL
- Choose the output message level from DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. The default is INFO.
- Show usage of the program.
- Show the version number of the program.
- 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).
Execute the following command to perform an online CLUSTER of all tables in database test:
$ pg_repack test
Execute the following command to perform an online VACUUM FULL of table foo in database test:
$ pg_repack --no-order --table foo -d test
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.
- pg_repack: repack database "template1" ... skipped: pg_repack is not installed in the database
pg_repack is not installed in the database when --all option is specified.
Create the pg_repack extension in the database.
- ERROR: pg_repack is not installed
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: relation "table" has no cluster key
The target table doesn't have CLUSTER KEY.
Define a CLUSTER KEY on the table, via ALTER TABLE CLUSTER ON, or use one of the --no-order or --order-by modes.
- pg_repack: query failed: ERROR: column "col" does not exist
The target table doesn't have columns specified by --order-by option.
Specify existing columns.
- ERROR: permission denied for schema repack
pg_repack must be executed by a superuser.
- pg_repack: query failed: ERROR: trigger "z_repack_trigger" for relation "tbl" already exists
The target table has already a trigger named z_repack_trigger. This is probably caused by a previous failed attempt to run pg_repack on the table, which for some reason failed to clean up the temporary object.
You can remove all the temporary objects by dropping and re-creating the extension: see the installation section for the details.
- pg_repack: trigger conflicted for 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 to that it sorts alphabetically before pg_repack's one.
pg_repack has the following restrictions. Be careful to avoid data corruptions.
pg_repack cannot reorganize temp tables.
pg_repack cannot reorganize tables using GiST indexes.
You cannot perform DDL commands of the target table(s) except VACUUM and ANALYZE during pg_repack. In many cases pg_repack will fail and rollback correctly, but there are some cases which may result in data corruption.
- TRUNCATE is lost. Deleted rows still exist after pg_repack.
- CREATE INDEX
- It causes index corruption.
- ALTER TABLE ... ADD COLUMN
- It causes loss of data. Newly added columns are initialized with NULLs.
- ALTER TABLE ... ALTER COLUMN TYPE
- It causes data corruption.
- ALTER TABLE ... SET TABLESPACE
- It causes data corruption by wrong relfilenode.
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.
- 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)