- Notifications
You must be signed in to change notification settings - Fork67
Compatible tools
Here's a list of tools that have been tested with pg_pathman:
Important: If you use pg_pathman 1.4.7 and older, you should disable
pg_pathman.override_copybefore dumping a database. Otherwise produced dump file will contain duplicate rows.
# dump whole database 'db'pg_10/bin/pg_dump db -Fc> db_dump.bin# restore database 'db' to 'db_copy'pg_restore -d db_copy db_dump.bin
# backup whole cluster in tar formatpg_basebackup -D db_backup -Ft -z -PAdd a few lines topostgresql.conf
shared_preload_libraries ='online_analyze, pg_pathman'# adjust these settingsonline_analyze.enable = ononline_analyze.verbose = on# online_analyze.threshold = 50# online_analyze.min_interval = 1# online_analyze.scale_factor = 0.1# online_analyze.table_type = 'temporary'
Now, let's take a look at some INSERT statements:
createtablept (idintnot null);select create_hash_partitions('pt','id',4);insert into ptvalues (1);INFO: analyzing"public.pt"INFO:"pt": scanned0 of0 pages, containing0 live rowsand0 dead rows;0 rowsin sample,0 estimated total rowsINFO: analyzing"public.pt" inheritance treeINFO:"pt_2": scanned1 of1 pages, containing1 live rowsand0 dead rows;1 rowsin sample,1 estimated total rowsINFO: analyze"pt" took0.00 secondsINSERT01insert into ptselect generate_series(1,3);INFO: analyzing"public.pt"INFO:"pt": scanned0 of0 pages, containing0 live rowsand0 dead rows;0 rowsin sample,0 estimated total rowsINFO: analyzing"public.pt" inheritance treeINFO:"pt_2": scanned1 of1 pages, containing3 live rowsand0 dead rows;3 rowsin sample,3 estimated total rowsINFO:"pt_3": scanned1 of1 pages, containing1 live rowsand0 dead rows;1 rowsin sample,1 estimated total rowsINFO: analyze"pt" took0.00 secondsINSERT03
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.
To repack a whole database, use-C pg_pathman option:
create extension pg_pathman;create extension pg_repack;createtablea(idintprimary keynot null);select create_range_partitions('a','id',1,10,4);createtableb(idintprimary keynot null);select create_hash_partitions('b','id',3);
# repack all tables of database 'db'pg_repack db -C pg_pathmanINFO: repacking table"a"INFO: repacking table"a_1"INFO: repacking table"a_2"INFO: repacking table"a_3"INFO: repacking table"a_4"INFO: repacking table"b"INFO: repacking table"b_0"INFO: repacking table"b_1"INFO: repacking table"b_2"
Otherwise pg_repack will deadlock on tablepathman_config:
# repack all tables of database 'db'pg_repack dbINFO: repacking table"pathman_config".....# nothing happens at all
To repack a single partitioned table, use-I option:
create extension pg_pathman;create extension pg_repack;/* create a table partitioned by hash*/createtablept (idintprimary keynot null);select create_hash_partitions('pt','id',4);
# repack table 'pt' of database 'db'pg_repack db -I ptINFO: repacking table"pt"INFO: repacking table"pt_0"INFO: repacking table"pt_1"INFO: repacking table"pt_2"INFO: repacking table"pt_3"