HomeMogDBMogDB StackUqbar
v3.1

Documentation:v3.1

Supported Versions:

Other Versions:

pg_repack

pg_repack Overview

The pg_repack extension is used to clear the table space online, thereby effectively solving the problem of table expansion caused by the operation that a large amount of data in a table is updated. pg_repack does not need to acquire an exclusive lock, which is more lightweight than CLUSTER or VACUUM FULL.


Install pg_repack

  1. Access Download page of the MogDB official website, download the pg_repack extension for the version you need.

  2. Unpack the package, for example:

    tar -xzvf pg_repack-1.4.6-3.1.0-01-CentOS-x86_64.tar.gz
  3. Go to the directory where the extension is located and execute the make install command.

    cd pg_repack/
    make install
  4. Connect to the database and execute the create extension pg_repack; to use it.

    MogDB=# create extension pg_repack;
    CREATE EXTENSION

Use pg_repack

  1. Connect to the database and create a user.

    gsql postgres -r -p 5001
    create user test password 'Test123456' login sysadmin;
  2. In the database, create extension pg_repack.

    create extension pg_repack;
  3. Create the pg_repack table which must contain a primary key.

    create table repack_test(id int primary key, name name);
  4. Run \dx to check whether pg_repack is successfully created.

    \dx
  5. Insert 2,000,000 rows of data into the table repack_test.

    insert into repack_test select generate_series(1,2000000),'a';
  6. Check the size of the table repack_test.

    select pg_size_pretty(pg_relation_size('repack_test'));
  7. Delete some data and then check the table size.

    Delete from repack_test where id>1000000;

    The figure shows that the table size does not change after 1,000,000 rows of data is deleted. In this case, pg_repack needs to be used.

  8. Use the pg_repack tool.

    pg_repack -d postgres -t test.repack_test -h 127.0.0.1 -U test -p 5001

    Parameters:

    • -d dbname

      Specifies the database name.

    • -t test.repack_test -h hostip

      Specifies the server IP address.

    • -U user

      Specifies the username.

    • -p port

      Specifies the port.

    • -e

      Outputs all SQL statements executed during repack.

    • -E DEBUG

      Sets the log level to DEBUG.

    • -a, --all

      Specifies all tables and indexes that are to be cleared.

    • -t, --table=TABLE

      Specifies a specified table to be cleared.

    • -I, --parent-table=TABLE repack

      Specifies the parent table and all sub-tables that inherit the parent table.

    • -c, --schema=SCHEMA repack

      Specifies all tables and indexes in a specified schema.

    • -s, --tablespace=TBLSPC

      Specifies the tablespace to which a single or multiple tables that are cleared is to be migrated.

    • -S, --moveidx

      Specifies the tablespace to which the index that is cleared is to be migrated.

    • -o, --order-by=COLUMNS

      Replaces cluster keys with order by columns.

    • -n, --no-order

      Performs vacuum full to replace cluster.

    • -N, --dry-run

      Outputs all content that is cleared by pg_repack.

    • -j, --jobs=NUM

      Specifies the number of concurrent jobs. If indexes are processed, pg_repack chooses the smaller value between the number of indexes and number of concurrent jobs specified by the parameter -j as the actual number of concurrent jobs.

    • -i, --index=INDEX

      Processes the specified index.

    • -x, --only-indexes

      Processes all indexes of a specified table.

    • -T, --wait-timeout=SECS

      Sets the life cycle of a backend conflict. If there is a conflict, the program is directly ended upon timeout (s).

    • -D, --no-kill-backend

      Specifies that other backed processes and threads are not killed when the extension times out.

    • -Z, --no-analyze

      Specifies that the analyze table operation is not performed after the clearing operation.

    • -k, --no-superuser-check

      Skips the super user check on the client.

    • -C, --exclude-extension

      Skips tables that belong to a specified extension.

    • -e, --echo

      Outputs all SQL statements executed by pg_repack.

    • -E, --elevel=LEVEL

      Outputs the log level, including debug and warning.

  9. Check the size of the table repack_test. The table space is released successfully.

    select pg_size_pretty(pg_relation_size('repack_test'));
Copyright © 2011-2024 www.enmotech.com All rights reserved.