Monday, May 15, 2023

pg_repack address bloat on table in postgresql14

pg_repack can be used as online operation to address bloat on a table in postgresql14.
Advantages of using pg_repack
  • Releases storage from a table to disk/FS
  • Rebuild a table - reduces I/O
  • Release storage piled due to dead tuples as bloat & not cleared by Auto vacuum

Install pg_repack on server
 [root@linuxpg1 bin]# yum install pg_repack_14
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pg_repack_14.x86_64 0:1.4.8-1.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===========================================================================================================================================
 Package                            Arch                         Version                                Repository                    Size
===========================================================================================================================================
Installing:
 pg_repack_14                       x86_64                       1.4.8-1.rhel7                          pgdg14                       127 k
Transaction Summary
===========================================================================================================================================
Install  1 Package
Total download size: 127 k
Installed size: 311 k
Is this ok [y/d/N]: y
Downloading packages:
pg_repack_14-1.4.8-1.rhel7.x86_64.rpm                                                                               | 127 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pg_repack_14-1.4.8-1.rhel7.x86_64                                                                                       1/1
  Verifying  : pg_repack_14-1.4.8-1.rhel7.x86_64                                                                                       1/1
Installed:
  pg_repack_14.x86_64 0:1.4.8-1.rhel7
Complete!


[root@linuxpg1 bin]# pwd
/usr/pgsql-14/bin

[root@linuxpg1 bin]# ls
clusterdb   initdb             pg_config       pg_isready     pg_rewind        pg_waldump                  psql
createdb    pg_archivecleanup  pg_controldata  pg_receivewal  pg_test_fsync    postgres                    reindexdb
createuser  pg_basebackup      pg_ctl          pg_repack      pg_test_timing   postgresql-14-check-db-dir  vacuumdb
dropdb      pgbench            pg_dump         pg_resetwal    pg_upgrade       postgresql-14-setup
dropuser    pg_checksums       pg_dumpall      pg_restore     pg_verifybackup  postmaster

[root@linuxpg1 bin]#


switch to postgres user
-bash-4.2$ psql
psql (14.7)
Type "help" for help.

postgres=# set shared_preload_libraries = 'pg_repack';
ERROR:  parameter "shared_preload_libraries" cannot be changed without restarting the server

restart postgresql

[root@linuxpg1 bin]# systemctl restart postgresql-14
[root@linuxpg1 bin]# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2023-05-15 00:37:20 CDT; 19s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 7950 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 7959 (postmaster)
    Tasks: 8
   CGroup: /system.slice/postgresql-14.service
           ├─7959 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
           ├─7962 postgres: logger
           ├─7964 postgres: checkpointer
           ├─7965 postgres: background writer
           ├─7966 postgres: walwriter
           ├─7967 postgres: autovacuum launcher
           ├─7968 postgres: stats collector
           └─7969 postgres: logical replication launcher

May 15 00:37:20 linuxpg1 systemd[1]: Starting PostgreSQL 14 database server...
May 15 00:37:20 linuxpg1 postmaster[7959]: 2023-05-15 00:37:20.547 CDT [7959] LOG:  redirecting log output to logging collector process
May 15 00:37:20 linuxpg1 postmaster[7959]: 2023-05-15 00:37:20.547 CDT [7959] HINT:  Future log output will appear in directory "log".
May 15 00:37:20 linuxpg1 systemd[1]: Started PostgreSQL 14 database server.
[root@linuxpg1 bin]#

-bash-4.2$ psql
psql (14.7)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 db1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
           |          |          |             |             | app_ro=c/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

connect to postgres db
postgres=# \c postgres

create extension pack in postgres database

postgres=# create extension pg_repack;
CREATE EXTENSION
postgres=#





check location of pg_repack & add it to PATH varaible
-bash-4.2$ which pg_repack
/usr/pgsql-14/bin/pg_repack
-bash-4.2$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/usr/pgsql-14/bin
-bash-4.2$

Execute pg_repack
-bash-4.2$ pg_repack --dry-run -d postgres --table tab1
INFO: Dry run enabled, not executing repack
WARNING: relation "public.tab1" must have a primary key or not-null unique keys
-bash-4.2$

Add primary key

ALTER TABLE public.tab1 ADD CONSTRAINT tab1_pk PRIMARY KEY (a1);


Run pg_repack dry 
-bash-4.2$ pg_repack --dry-run -d postgres --table tab1
INFO: Dry run enabled, not executing repack
INFO: repacking table "public.tab1"
-bash-4.2$

Run pg_repack to address bloat
-bash-4.2$ pg_repack  -d postgres --table tab1
INFO: repacking table "public.tab1"
-bash-4.2$



No comments:

Post a Comment