Project

General

Profile

User story #14450

Document using pg_repack to prevent large disk usage

Added by Nicolas CHARLES 2 months ago. Updated 9 days ago.

Status:
Released
Priority:
N/A
Category:
Performance and scalability
Target version:
Suggestion strength:
User visibility:
Effort required:

Description

First, find postgresql version

select version();

On debian like

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt-get update
apt-get install postgresql-PGVERSION-repack

On redhat like
Install repo from https://yum.postgresql.org/

https://download.postgresql.org/pub/repos/yum/PGVERSION/redhat/rhel-7-x86_64/
yum install pg_repackPGVERSION(without.dot)

Edit postgresql.conf file (typically /etc/postgresql/PGVERSION/main/postgresql.conf
to set shared_preload_libraries = 'pg_repack'

restart service postgresql
install extension

# su postgres
$ psql
psql (9.6.10)
Type "help" for help.

postgres=# \c rudder
You are now connected to database "rudder" as user "postgres".
CREATE EXTENSION pg_repack;

ALTER FUNCTION repack.version() OWNER TO postgres;
ALTER FUNCTION repack.version_sql() OWNER TO postgres;
ALTER FUNCTION repack.array_accum(anyelement) OWNER TO postgres;
ALTER FUNCTION repack.oid2text(oid) OWNER TO postgres;
ALTER FUNCTION repack.get_index_columns(oid,text) OWNER TO postgres;
ALTER FUNCTION repack.get_order_by(oid,oid) OWNER TO postgres;
ALTER FUNCTION repack.get_create_index_type(oid,name) OWNER TO postgres;
ALTER FUNCTION repack.get_create_trigger(oid,oid) OWNER TO postgres;
ALTER FUNCTION repack.get_enable_trigger(oid) OWNER TO postgres;
ALTER FUNCTION repack.get_assign(oid,text) OWNER TO postgres;
ALTER FUNCTION repack.get_compare_pkey(oid,text) OWNER TO postgres;
ALTER FUNCTION repack.get_columns_for_create_as(oid) OWNER TO postgres;
ALTER FUNCTION repack.get_drop_columns(oid,text) OWNER TO postgres;
ALTER FUNCTION repack.get_storage_param(oid) OWNER TO postgres;
ALTER FUNCTION repack.get_alter_col_storage(oid) OWNER TO postgres;
ALTER FUNCTION repack.repack_indexdef(oid,oid,name,boolean) OWNER TO postgres;
ALTER FUNCTION repack.repack_trigger() OWNER TO postgres;
ALTER FUNCTION repack.conflicted_triggers(oid) OWNER TO postgres;
ALTER FUNCTION repack.disable_autovacuum(regclass) OWNER TO postgres;
ALTER FUNCTION repack.repack_apply(cstring,cstring,cstring,cstring,cstring,integer) OWNER TO postgres;
ALTER FUNCTION repack.repack_swap(oid) OWNER TO postgres;
ALTER FUNCTION repack.repack_drop(oid,integer) OWNER TO postgres;
ALTER FUNCTION repack.repack_index_swap(oid) OWNER TO postgres;
ALTER FUNCTION repack.get_table_and_inheritors(regclass) OWNER TO postgres;

Add cronjob to repack regularly

/etc/cron.d/repack_db

# Compress inline the database
PATH=/usr/bin

# 2:20: compress ruddersysevents
20 2 * * * postgres pg_repack -d rudder -t ruddersysevents > /dev/null

# 3:40: compress ruddersysevents
40 3 * * * postgres pg_repack -d rudder -t archivedruddersysevents > /dev/null

# 4:30: compress  nodecompliancelevels
30 2 * * * postgres pg_repack -d rudder -t nodecompliancelevels > /dev/null

Associated revisions

Revision 2bf60970 (diff)
Added by Nicolas CHARLES 16 days ago

Fixes #14450: Document using pg_repack to prevent large disk usage

History

#1

Updated by Nicolas CHARLES 2 months ago

  • Status changed from New to In progress
  • Assignee set to Nicolas CHARLES
#2

Updated by Nicolas CHARLES 2 months ago

  • Status changed from In progress to Pending technical review
  • Assignee changed from Nicolas CHARLES to Alexis MOUSSET
  • Pull Request set to https://github.com/Normation/rudder-doc/pull/571
#3

Updated by Vincent MEMBRÉ about 1 month ago

  • Target version changed from 5.0.9 to 5.0.10
#4

Updated by Nicolas CHARLES 16 days ago

  • Status changed from Pending technical review to Pending release
#5

Updated by Vincent MEMBRÉ 9 days ago

  • Status changed from Pending release to Released

This bug has been fixed in Rudder 5.0.10 which was released today.

Also available in: Atom PDF