Reindexing to reduce bloat - postgresql

postgres=# create table demo1 as select * from generate_series(1,50000) as id;

SELECT 50000

postgres=# create index demo1_idx on demo1(id);

CREATE INDEX

postgres=# \dt

         List of relations

 Schema | Name  | Type  |  Owner

--------+-------+-------+----------

 public | demo1 | table | postgres

 public | ridx1 | table | postgres

(2 rows)

postgres=# \dt+

                                    List of relations

 Schema | Name  | Type  |  Owner   | Persistence | Access method |  Size   | Description

--------+-------+-------+----------+-------------+---------------+---------+-------------

 public | demo1 | table | postgres | permanent   | heap          | 1808 kB |

 public | ridx1 | table | postgres | permanent   | heap          | 43 MB   |

(2 rows)

postgres=# \di+

                                           List of relations

 Schema |    Name     | Type  |  Owner   | Table | Persistence | Access method |  Size   | Description

--------+-------------+-------+----------+-------+-------------+---------------+---------+-------------

 public | demo1_idx   | index | postgres | demo1 | permanent   | btree         | 1112 kB |

 public | ridx1_pk    | index | postgres | ridx1 | permanent   | btree         | 14 MB   |

 public | ridx1_x_idx | index | postgres | ridx1 | permanent   | btree         | 14 MB   |

(3 rows)

postgres=# delete from demo1 where id % 2 = 0;

DELETE 25000

postgres=# select pg_size_pretty(pg_relation_size('demo1')) as table_size, pg_size_pretty(pg_relation_size('demo1_idx')) as index_size, 100 - (pgstatindex('demo1_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size | bloat_ratio

------------+------------+-------------

 1776 kB    | 1112 kB    |       54.94

(1 row)

postgres=# reindex index demo1_idx;

REINDEX

postgres=# select pg_size_pretty(pg_relation_size('demo1')) as table_size, pg_size_pretty(pg_relation_size('demo1_idx')) as index_size, 100 - (pgstatindex('demo1_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size |    bloat_ratio

------------+------------+--------------------

 1776 kB    | 568 kB     | 10.819999999999993

(1 row)

pgstattuple - for tuple-level statistics & removing bloat by pg_repack

 pgstattuple module/extension provides various functions to obtain tuple-level statistics, however its not installed by default.

postgres=#  select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;

ERROR:  function pgstattuple(unknown) does not exist

LINE 1: ...(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattupl...

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

To create extension, you need to install 

[root@linuxpg1 ~]# yum install postgresql14-contrib

Loaded plugins: langpacks, ulninfo

ol7_UEKR5                                                | 3.0 kB     00:00

ol7_latest                                               | 3.6 kB     00:00

pgdg-common/7Server/x86_64/signature                     |  198 B     00:00

pgdg-common/7Server/x86_64/signature                     | 2.9 kB     00:00 !!!

pgdg11/7Server/x86_64/signature                          |  198 B     00:00

pgdg11/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg12/7Server/x86_64/signature                          |  198 B     00:00

pgdg12/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg13/7Server/x86_64/signature                          |  198 B     00:00

pgdg13/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg14/7Server/x86_64/signature                          |  198 B     00:00

pgdg14/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

pgdg15/7Server/x86_64/signature                          |  198 B     00:00

pgdg15/7Server/x86_64/signature                          | 3.6 kB     00:00 !!!

(1/6): pgdg13/7Server/x86_64/primary_db                    | 287 kB   00:03

(2/6): pgdg-common/7Server/x86_64/primary_db               | 178 kB   00:03

(3/6): pgdg15/7Server/x86_64/primary_db                    | 106 kB   00:00

(4/6): pgdg14/7Server/x86_64/primary_db                    | 193 kB   00:04

(5/6): pgdg12/7Server/x86_64/primary_db                    | 373 kB   00:05

(6/6): pgdg11/7Server/x86_64/primary_db                    | 487 kB   00:11

Resolving Dependencies

--> Running transaction check

---> Package postgresql14-contrib.x86_64 0:14.8-1PGDG.rhel7 will be installed

--> Processing Dependency: postgresql14-server(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64

--> Processing Dependency: postgresql14-libs(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64

--> Processing Dependency: postgresql14(x86-64) = 14.8-1PGDG.rhel7 for package: postgresql14-contrib-14.8-1PGDG.rhel7.x86_64

--> Processing Dependency: libpython3.6m.so.1.0()(64bit) for package: postgresql 14-contrib-14.8-1PGDG.rhel7.x86_64

--> Running transaction check

---> Package postgresql14.x86_64 0:14.7-1PGDG.rhel7 will be updated

---> Package postgresql14.x86_64 0:14.8-1PGDG.rhel7 will be an update

---> Package postgresql14-libs.x86_64 0:14.7-1PGDG.rhel7 will be updated

---> Package postgresql14-libs.x86_64 0:14.8-1PGDG.rhel7 will be an update

---> Package postgresql14-server.x86_64 0:14.7-1PGDG.rhel7 will be updated

---> Package postgresql14-server.x86_64 0:14.8-1PGDG.rhel7 will be an update

---> Package python3-libs.x86_64 0:3.6.8-18.0.5.el7 will be installed

--> Processing Dependency: python(abi) = 3.6 for package: python3-libs-3.6.8-18.0.5.el7.x86_64

--> Running transaction check

---> Package python3.x86_64 0:3.6.8-18.0.5.el7 will be installed

--> Processing Dependency: python3-pip for package: python3-3.6.8-18.0.5.el7.x86_64

--> Processing Dependency: python3-setuptools for package: python3-3.6.8-18.0.5.el7.x86_64

--> Running transaction check

---> Package python3-pip.noarch 0:9.0.3-8.0.3.el7 will be installed

---> Package python3-setuptools.noarch 0:39.2.0-10.0.1.el7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

================================================================================

Package                  Arch       Version               Repository      Size

================================================================================

Installing:

 postgresql14-contrib     x86_64     14.8-1PGDG.rhel7      pgdg14         688 k

Installing for dependencies:

 python3                  x86_64     3.6.8-18.0.5.el7      ol7_latest      70 k

 python3-libs             x86_64     3.6.8-18.0.5.el7      ol7_latest     6.9 M

 python3-pip              noarch     9.0.3-8.0.3.el7       ol7_latest     1.6 M

 python3-setuptools       noarch     39.2.0-10.0.1.el7     ol7_latest     628 k

Updating for dependencies:

 postgresql14             x86_64     14.8-1PGDG.rhel7      pgdg14         1.5 M

 postgresql14-libs        x86_64     14.8-1PGDG.rhel7      pgdg14         271 k

 postgresql14-server      x86_64     14.8-1PGDG.rhel7      pgdg14         5.4 M

Transaction Summary

================================================================================

Install  1 Package  (+4 Dependent packages)

Upgrade             ( 3 Dependent packages)

Total size: 17 M

Total download size: 9.9 M

Is this ok [y/d/N]: y

Downloading packages:

(1/5): python3-3.6.8-18.0.5.el7.x86_64.rpm                 |  70 kB   00:01

(2/5): python3-pip-9.0.3-8.0.3.el7.noarch.rpm              | 1.6 MB   00:00

(3/5): python3-setuptools-39.2.0-10.0.1.el7.noarch.rpm     | 628 kB   00:00

(4/5): python3-libs-3.6.8-18.0.5.el7.x86_64.rpm            | 6.9 MB   00:06

(5/5): postgresql14-contrib-14.8-1PGDG.rhel7.x86_64.rpm    | 688 kB   00:08

--------------------------------------------------------------------------------

Total                                              1.1 MB/s | 9.9 MB  00:08

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : python3-libs-3.6.8-18.0.5.el7.x86_64                        1/11

  Installing : python3-pip-9.0.3-8.0.3.el7.noarch                          2/11

  Installing : python3-setuptools-39.2.0-10.0.1.el7.noarch                 3/11

  Installing : python3-3.6.8-18.0.5.el7.x86_64                             4/11

  Updating   : postgresql14-libs-14.8-1PGDG.rhel7.x86_64                   5/11

  Updating   : postgresql14-14.8-1PGDG.rhel7.x86_64                        6/11

  Updating   : postgresql14-server-14.8-1PGDG.rhel7.x86_64                 7/11

  Installing : postgresql14-contrib-14.8-1PGDG.rhel7.x86_64                8/11

  Cleanup    : postgresql14-server-14.7-1PGDG.rhel7.x86_64                 9/11

  Cleanup    : postgresql14-14.7-1PGDG.rhel7.x86_64                       10/11

  Cleanup    : postgresql14-libs-14.7-1PGDG.rhel7.x86_64                  11/11

  Verifying  : python3-3.6.8-18.0.5.el7.x86_64                             1/11

  Verifying  : postgresql14-libs-14.8-1PGDG.rhel7.x86_64                   2/11

  Verifying  : python3-pip-9.0.3-8.0.3.el7.noarch                          3/11

  Verifying  : python3-libs-3.6.8-18.0.5.el7.x86_64                        4/11

  Verifying  : postgresql14-14.8-1PGDG.rhel7.x86_64                        5/11

  Verifying  : python3-setuptools-39.2.0-10.0.1.el7.noarch                 6/11

  Verifying  : postgresql14-contrib-14.8-1PGDG.rhel7.x86_64                7/11

  Verifying  : postgresql14-server-14.8-1PGDG.rhel7.x86_64                 8/11

  Verifying  : postgresql14-server-14.7-1PGDG.rhel7.x86_64                 9/11

  Verifying  : postgresql14-14.7-1PGDG.rhel7.x86_64                       10/11

  Verifying  : postgresql14-libs-14.7-1PGDG.rhel7.x86_64                  11/11

Installed:

  postgresql14-contrib.x86_64 0:14.8-1PGDG.rhel7

Dependency Installed:

  python3.x86_64 0:3.6.8-18.0.5.el7

  python3-libs.x86_64 0:3.6.8-18.0.5.el7

  python3-pip.noarch 0:9.0.3-8.0.3.el7

  python3-setuptools.noarch 0:39.2.0-10.0.1.el7

Dependency Updated:

  postgresql14.x86_64 0:14.8-1PGDG.rhel7

  postgresql14-libs.x86_64 0:14.8-1PGDG.rhel7

  postgresql14-server.x86_64 0:14.8-1PGDG.rhel7

Complete!

[root@linuxpg1 ~]# su - postgres

Last login: Sat May 27 17:22:44 CDT 2023 on pts/1

ps-bash-4.2$ psql

psql (14.8)

Type "help" for help.

postgres=# create extension pgstattuple;

CREATE EXTENSION

postgres=#

Now you can use pgstattuple to query bloat (dead_tuple_percent)

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;

 table_size | index_size | dead_tuple_percent

------------+------------+--------------------

 65 MB      | 21 MB      |                  0

(1 row)

postgres=# delete from ridx1 where x % 3 = 0;

DELETE 333333

postgres=# analyze ridx1;

ANALYZE

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, (pgstattuple('ridx1')).dead_tuple_percent;

 table_size | index_size | dead_tuple_percent

------------+------------+--------------------

 65 MB      | 21 MB      |              29.78

(1 row)

Index bloat

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size | bloat_ratio

------------+------------+-------------

 65 MB      | 21 MB      |       39.86

(1 row)

 postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1

INFO: Dry run enabled, not executing repack

ERROR: pg_repack failed with error: pg_repack 1.4.8 is not installed in the database

-bash-4.2$ psql

psql (14.8)

Type "help" for help.

postgres=# select * from pg_extension;

  oid  |   extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition

-------+-------------+----------+--------------+----------------+------------+-----------+--------------

 14473 | plpgsql     |       10 |           11 | f              | 1.0        |           |

 33437 | pgstattuple |       10 |         2200 | t              | 1.5        |           |

(2 rows)

postgres=# create extension pg_repack;

CREATE EXTENSION

postgres=# select * from pg_extension;

  oid  |   extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition

-------+-------------+----------+--------------+----------------+------------+-----------+--------------

 14473 | plpgsql     |       10 |           11 | f              | 1.0        |           |

 33437 | pgstattuple |       10 |         2200 | t              | 1.5        |           |

 33448 | pg_repack   |       10 |         2200 | f              | 1.4.8      |           |

(3 rows)

postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1

INFO: Dry run enabled, not executing repack

WARNING: relation "public.ridx1" must have a primary key or not-null unique keys

-bash-4.2$ psql

psql (14.8)

Type "help" for help.

postgres=# alter table ridx1 add constraint ridx1_pk primary key (x);

ALTER TABLE

postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack --dry-run -d postgres --table ridx1

INFO: Dry run enabled, not executing repack

INFO: repacking table "public.ridx1"

-bash-4.2$ psql

psql (14.8)

Type "help" for help.

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size | bloat_ratio

------------+------------+-------------

 65 MB      | 21 MB      |       39.86

(1 row)

postgres=# \q

-bash-4.2$ /usr/pgsql-14/bin/pg_repack -d postgres --table ridx1

INFO: repacking table "public.ridx1"

-bash-4.2$ psql

psql (14.8)

Type "help" for help.

postgres=# select pg_size_pretty(pg_relation_size('ridx1')) as table_size, pg_size_pretty(pg_relation_size('ridx1_x_idx')) as index_size, 100 - (pgstatindex('ridx1_x_idx')).avg_leaf_density as bloat_ratio;

 table_size | index_size |    bloat_ratio

------------+------------+-------------------

 43 MB      | 14 MB      | 9.939999999999998

(1 row)

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$

PostgreSQL DBA helpful stuff!

PostgreSQL commands

# to get help on psql 

psql --help

# Examples of psql connection to DB using various switches/flags

psql postgres postgres -h 192.168.0.25 -p 5432 (-h flag to specify hostname)
psql -U postgres -h 192.168.0.25 -p 5432 -W  (-U flag to specify db user)

psql -U postgres -d db1 -h 192.168.0.25 -p 5432 (-d flag to specify db)

#to know current user

select current_user;

#to get current date

select current_date;

#to know user details

select * from pg_user;

#to know about databases

select * from pg_database;

#to know about tables

select * from pg_class;

#to know about roles

select * from pg_roles;

 

# to query parameters

SELECT name as "Parameter name", setting as value, short_desc FROM pg_settings WHERE name LIKE '%ssss%'; 

#to know relative file path

select pg_relation_filepath('course');

#get details of user connections

select state, count(*) from pg_stat_activity group by state;

select * from pg_stat_activity where state='idle';

SELECT pg_terminate_backend(pid);

#shortcut commands on psql

postgres=# \l -- list db

postgres=#\c dbname -- to connect to a particular db

# to know dead tuples in a table

postgres=#select relname, n_dead_tup from pg_stat_user_tables;

#to vacuum manually

postgres=# vacuum (verbose, analyze);

#to know size of tables

select relname as "tableName", pg_size_pretty(pg_table_size(pgc.oid)) as "spaceused"

from pg_class as pgc left join pg_namespace as pgns on (pgns.oid = pgc.relnamespace)

where nspname not in ('pg_catalog', 'information_schema') and nspname !='pg_toast' and relkind in ('r')

order by pg_table_size(pgc.oid) desc;

#Query to check is SSL is being used by sessions

SELECT datname as "Database name", usename as "User name", ssl, client_addr, application_name, backend_type
FROM pg_stat_ssl JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid ORDER BY ssl;

#Indexes Info

postgres=# \di

postgres=# \di+

postgres=# \dis

# to get index, table its on & columns its created on

select row_number() over (order by c.relname), c.relname as index, t.relname as table, array_to_string(array_agg(a.attname), ', ') as column_names from pg_class c join pg_index i on c.oid = i.indexrelid and c.relkind='i' and c.relname not like 'pg_%' join pg_class t on t.oid= i.indrelid left join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(i.indkey) group by t.relname, c.relname order by c.relname;

#when last auto vacuum happened

select relname, last_vacuum, last_autovacuum from pg_stat_user_tables;

#to vacuum table

vacuum full tab1; -- tab1 is table name

#to know details about Archiving

postgres=# select name, setting, unit from pg_settings where name in

('wal_level', 'wal_keep_size', 'wal_keep_segments', 'wal_segment_size', 'archive_mode', 'archive_command', 'archive_timeout');

#to Check number of connections, super user connections, max connections

select * from (select count(*) used from pg_stat_activity) q1, (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) q2, (select setting::int max_conn from pg_settings where name=$$max_connections$$) q3;

#to take online backups

#can take backups as postgres user or create user with replication role

pg_basebackup -U username -D /location/wheretoplacebackups

pg_basebackup -v -U username -D /location/wheretoplacebackups

pg_basebackup -U username -D /location/wheretoplacebackups -h localhost

pg_basebackup -U username -D /location/wheretoplacebackups -Ft -z -P (-Ft - format tar file, z for compression P for progress)

#for upgrade

pg_upgrade

--old-datadir '/pg/9.x/data' --new-datadir '/pg/10.x/data'

--old-bindir '/pg/9.x/bin' --new-bindir '/pg/10.x/bin'

pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin - B newCluster/bin

or you can export

$export PGDATAOLD = oldCluster/data

$export PGDATANEW = newCluster/data

$export PGBINOLD = oldCluster/bin

$export PGNEWOLD = newCluster/bin

$pg_upgrade

#to create db user

create user user1;

# to know the process tree for postgresql processes

pstree -p 7080 (where 7080 is postmaster pid)

PG Background Process

logger -- writes messages to error logs

checkpointer -- flushes dirty buffer to files (periodically)

background writer -- writes dirty buffer to files

wal writer -- writes wal buffer to wal file

autovaccum launcher -- performs vacuum operations

archiver-- for archiving

stats collector -- DBMS usage stats (session info, table usage stats)..etc.

PG Memory pools

shared memory -- db caching & transaction log caching

shared buffer -- frequently used blocks cached

wal buffer -- changes to db

postmaster process -- first to start, initialize memory, run background process, helps in recovery, creates background process for a client connection

maintenance_work_mem - for vacuum & create index, joins

temp_buffers -- temp tables area

Connect to Postgresql Database via DBeaver running on RHEL in a guest VM on Oracle virtualbox

Pre-requisites:

• Setup includes oracle virtualbox
• guest VM -- running on RHEL 7.x

[root@linuxpg1 selinux]#  cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.8 (Maipo)

• Postgresql installed & running

Redirecting to /bin/systemctl status postgresql-14.service

● postgresql-14.service - PostgreSQL 14 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)

   Active: active (running) since Wed 2023-05-10 00:17:57 CDT; 13h ago

     Docs: https://www.postgresql.org/docs/14/static/

 Main PID: 7080 (postmaster)

   CGroup: /system.slice/postgresql-14.service

           ├─ 7080 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/

           ├─ 7083 postgres: logger

           ├─ 7085 postgres: checkpointer

           ├─ 7086 postgres: background writer

           ├─ 7087 postgres: walwriter

           ├─ 7088 postgres: autovacuum launcher

           ├─ 7089 postgres: stats collector

           ├─ 7090 postgres: logical replication launcher

           ├─15197 postgres: postgres postgres 192.168.0.19(54850) idle

           ├─15198 postgres: postgres postgres 192.168.0.19(54851) idle

           └─15200 postgres: postgres postgres 192.168.0.19(54862) idle

May 10 00:17:57 linuxpg1 systemd[1]: Stopped PostgreSQL 14 database server.

May 10 00:17:57 linuxpg1 systemd[1]: Starting PostgreSQL 14 database server...

May 10 00:17:57 linuxpg1 postmaster[7080]: 2023-05-10 00:17:57.126 CDT [7080] LOG:  redirecting log output to loggi...roces

May 10 00:17:57 linuxpg1 postmaster[7080]: 2023-05-10 00:17:57.126 CDT [7080] HINT:  Future log output will appear ..."log"

May 10 00:17:57 linuxpg1 systemd[1]: Started PostgreSQL 14 database server.

Hint: Some lines were ellipsized, use -l to show in full.

• Update postgresql.conf 

[root@linuxpg1 data]# pwd

/var/lib/pgsql/14/data

[root@linuxpg1 data]# ls -ltrh postgresql.conf

-rw-------. 1 postgres postgres 29K May 10 00:09 postgresql.conf

[root@linuxpg1 data]# cat postgresql.conf | grep -i listen

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

listen_addresses = '*'          # what IP address(es) to listen on;

[root@linuxpg1 data]#

• Update pg_hba.conf

[root@linuxpg1 data]# pwd

/var/lib/pgsql/14/data

[root@linuxpg1 data]# ls -ltrh pg_hba.conf

-rw-------. 1 postgres postgres 4.6K May 10 00:16 pg_hba.conf

[root@linuxpg1 data]# cat pg_hba.conf | grep password

# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",

# Note that "password" sends passwords in clear text; "md5" or

# "scram-sha-256" are preferred since they send encrypted passwords.

host    all     all     0.0.0.0/0 password

[root@linuxpg1 data]#

• stop firewall

systemctl status firewalld

systemctl stop firewalld

systemctl disable firewalld

from your laptop (host) 

use telnet to check connectivity on port 5432

telnet 192.168.0.25 5432