pt-set-tablespace¶
Summary¶
Change at once the tablesapace of the index associated with the specified table.
Usage¶
pt-set-tablespace [option...] [tablespace]
Options¶
-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
-o, --owner=STRING
-n, --schema=STRING
-t, --table=STRING
-l, --list
--apply
--help
-h
, --host
Specifies the connecting PostgreSQL database server name or its IP address. If not specified, to set the value of PGHOST. localhost
will be used if no value has been defined in PGHOST.
-p
, --port
Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432
will be used if no value has been defined in PGPORT.
-U
, --username
Specifies the user name of the PosgtgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used if no value has been defined in PGUSER.
-d
, --dbname
Specifies the connecting database name. If not specified, to set the value of PGDATABASE. It connects to the database same as the database username if no value has been defined in PGDATABASE.
-o
, --owner
It applies only table that is owning user that matches the specified name. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/
)
-n
, --schema
It applies only table that is a schema that matches the specified name. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/
)
-t
, --table
It applies only table that matches the specified name. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/
)
--apply
The change of tablespace, it is actually reflected in the database.
-l
, --list
Displays the tablespace information.
-o
(or --owner
), -n
(or --schema
), -t
(or --table
), if specified the options at the same time, the only table that matches the conditions.
If it fails to move one or more tables, returned an exit code of 1
. If it’s successful the movement of all of the files, returned a “0”.
Output Items¶
-l
, --list
Items that are displayed in the options.
OID |
Object ID of tablespace |
OWNER |
Owner name of tablespace |
TABLESPACE |
Tablespace name |
LOCATION |
Path of the directory for tablespace |
USE% |
Disk usage of the partition for tablespace |
AVAIL |
Free space of the partition for tablespace |
Other output items are shown below.
Dry-run mode |
Free space of the partition for tablespace. | |
Applying ALTER TABLE/INDEX |
Acutually running the ALTER TABLE/INDEX statement | it will change tablespace of the index. |
X tables/indexes moved. Y failed. |
X of tables/indexes is moved successfully | Y failed to move. |
Examples¶
Displays list of tablespace that exist in the PostgreSQL instance. Gets the used area of the each partition, and displays as a list together.
$ pt-set-tablespace --list
+--------+----------+------------+---------------------------+------+-------+
| OID | OWNER | TABLESPACE | LOCATION | USE% | AVAIL |
+--------+----------+------------+---------------------------+------+-------+
| 1663 | postgres | pg_default | | | |
| 1664 | postgres | pg_global | | | |
| 121263 | postgres | hddspc2 | /disk/disk2/pgsql | 85% | 80G |
| 16818 | postgres | ssdspc1 | /disk/disk1/tblspc1 | 67% | 127G |
| 305242 | postgres | ssdspc2 | /disk/disk3/pgsql/ssdspc2 | 98% | 13G |
+--------+----------+------------+---------------------------+------+-------+
$
All orders
tables and the indexes that was created on orders
table in dbt3
database, it displays ALTER TABLE
and ALTER INDEX
statement for to move the tablesapace. (Does not actually move)
$ pt-set-tablespace -d dbt3 --table orders ssdspc1
[2015-04-29 17:35:24] INFO: Dry-run mode:
[2015-04-29 17:35:24] INFO: ALTER TABLE "public"."orders" SET TABLESPACE "ssdspc1";
[2015-04-29 17:35:24] INFO: ALTER INDEX "public"."pk_orders" SET TABLESPACE "ssdspc1";
[2015-04-29 17:35:24] INFO: ALTER INDEX "public"."i_o_orderdate" SET TABLESPACE "ssdspc1";
[2015-04-29 17:35:24] INFO: ALTER INDEX "public"."i_o_custkey" SET TABLESPACE "ssdspc1";
$
All orders
tables and the indexes that were created on orders
table in dbt3
database, to move to the ssdspc1
tablespace.
$ pt-set-tablespace -d dbt3 --table orders --apply ssdspc1
[2015-04-29 17:37:06] INFO: Applying ALTER TABLE/INDEX...
[2015-04-29 17:37:08] INFO: 4 tables/indexes moved. 0 failed.
$
All tables in the dbt3
schema and all indexes, to move to the ssdspc1
tablespace.
$ pt-set-tablespace --schema dbt3 --apply ssdspc1
[2015-04-29 17:38:39] INFO: Applying ALTER TABLE/INDEX...
[2015-04-29 17:38:57] INFO: 31 tables/indexes moved. 0 failed.
$