Versions: 1.1 1.2 1.3 1.4 1.5 master
Languages: en jp
Contents
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.
pg_repack is a fork of the previous pg_reorg project. Please check the project page for bug report and development information.
You can choose one of the following methods to reorganize:
NOTICE:
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.
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 |
-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 |
-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 |
-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 connect to servers. You cannot use --all and --dbname or --table together.
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.
Default connection parameters
This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Environment Variables).
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
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 is not installed in the database when the --all option is specified.
Create the pg_repack extension in the database.
pg_repack is not installed in the database specified by --dbname.
Create the pg_repack extension in the database.
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.
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.
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.
The target table doesn't have columns specified by --order-by option.
Specify existing columns.
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.
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;
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.
pg_repack comes with the following restrictions.
pg_repack cannot reorganize temp tables.
pg_repack cannot reorganize tables using GiST indexes.
You will not be able to perform DDL commands of the target table(s) except VACUUM or ANALYZE while pg_repack is working. pg_repack will hold an ACCESS SHARE lock on the target table during a full-table repack, to enforce this restriction.
If you are using version 1.1.8 or earlier, you must not attempt to perform any DDL commands on the target table(s) while pg_repack is running. In many cases pg_repack would fail and rollback correctly, but there were some cases in these earlier versions which could result in data corruption.
To perform a full-table repack, pg_repack will:
pg_repack will only hold an ACCESS EXCLUSIVE lock for a short period during initial setup (steps 1 and 2 above) and during the final swap-and-drop phase (steps 6 and 7). For the rest of its time, pg_repack only needs to hold an ACCESS SHARE lock on the original table, meaning INSERTs, UPDATEs, and DELETEs may proceed as usual.
To perform an index-only repack, pg_repack will:
Creating indexes concurrently comes with a few caveats, please see the documentation for details.