work on autovacuum postgreSQL parameter

 In This blog, we are discussing the auto vacuum parameter on a small scale. we will understand the below parameters and will see how to modify them on the cluster level. 

PARAMETER DETAILS:

AUTOVACUUM_MAX_WORKERS : This parameter will show you how many Autovacuum process is working on cluster level.f there are four databases with autovacuum_max_workers set to 3, 

then, the 4th database has to wait until one of the existing worker process gets free.

AUTOVACUUM_VACUUM_THRESHOLD : The autovacuum_vacuum_threshold parameter controls the minimum number oftuple update or delete operations that must occur on a table before autovacuum vacuums it.

AUTOVACUUM_VACUUM_SCALE_FACTOR: The autovacuum_vacuum_scale_factor configuration parameter controls how aggressive theautovacuum process should be when vacuuming a table.

AUTOVACUUM_ANALYZE_THRESHOLD :  The autovacuum_analyze_scale_factor parameter controls how aggressive the autovacuum process should be when analyzing table.

AUTOVACUUM_VACUUM_COST_LIMIT: The autovacuum_vacuum_cost_limit parameter controls the amount of CPU and I/O resources that an autovacuum worker can consume.

how to work on parameter modification on cluster 

postgres=# show autovacuum_max_workers;

 autovacuum_max_workers

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

 3

(1 row)

postgres=# show autovacuum_vacuum_threshold;

 autovacuum_vacuum_threshold

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

 50

(1 row)

postgres=# show autovacuum_vacuum_scale_factor;

 autovacuum_vacuum_scale_factor

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

 0.2

(1 row)

postgres=# show autovacuum_analyze_threshold;

 autovacuum_analyze_threshold

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

 50

(1 row)

postgres=# show autovacuum_vacuum_cost_limit;

 autovacuum_vacuum_cost_limit

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

 -1

(1 row)

postgres=#

[postgres@PG-V16 ~]$ cat  reset_the_par_values.sql

alter system set autovacuum_max_workers = 6;

alter system set autovacuum_vacuum_threshold = 100;

alter system set autovacuum_vacuum_scale_factor = 0.3;

alter system set autovacuum_analyze_threshold = 100;

alter system set autovacuum_vacuum_cost_limit = 100;

[postgres@PG-V16 ~]$

[postgres@PG-V16 ~]$ psql -p 5433 -d postgres -U postgres -f reset_the_par_values.sql

ALTER SYSTEM

ALTER SYSTEM

ALTER SYSTEM

ALTER SYSTEM

ALTER SYSTEM

[postgres@PG-V16 ~]$ cat validate_vacuum_par_values.sql

show autovacuum_max_workers;

show autovacuum_vacuum_threshold ;

show autovacuum_vacuum_scale_factor ;

show autovacuum_analyze_threshold ;

show autovacuum_vacuum_cost_limit ;

[postgres@PG-V16 ~]$ psql -p 5433 -d postgres -U postgres -f validate_vacuum_par_values.sql

 autovacuum_max_workers

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

 6

(1 row)

 autovacuum_vacuum_threshold

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

 100

(1 row)

 autovacuum_vacuum_scale_factor

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

 0.3

(1 row)

 autovacuum_analyze_threshold

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

 100

(1 row)

 autovacuum_vacuum_cost_limit

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

 100

(1 row)

[postgres@PG-V16 ~]$

ERROR: trailing junk after numeric literal at or near

 

Error : ERROR:  trailing junk after numeric literal at or near

Solution: Enter/specify the parameter value in "" 

Example: 

postgres=# show work_mem;

 work_mem

----------

 4MB

(1 row)

postgres=# set work_mem = 8MB;

ERROR:  trailing junk after numeric literal at or near "8M"

LINE 1: set work_mem = 8MB;

                       ^

postgres=# set work_mem = "8MB";

SET

postgres=# show work_mem;

 work_mem

----------

 8MB

(1 row)

postgres=#

Create a new cluster in Postgres V16

 

Once the PostgreSQL installation is done. You will get the default cluster with data location /var/lib and the port number default one 5432. After that, you can configure the new cluster as per your requirements like specific port number and storage location. In this section, we are deploying the new cluster. We will use port 5433 and storage location /u01 in our new Postgres cluster configuration.

Deployment diagram:

Infrastructure Details:

HOSTNAME: PG_V16.example.com
OS version	Red Hat Enterprise Linux release 8.6 (Ootpa)
PG Version	PostgreSQL 16.1
Port number	5433
Cluster location	/u01/app/postgres/product/v16

 

In this QuickStart, we learn how to:

 

·         Prerequisite Steps

-          Validate cluster is up and running and current port number

-          Create the directory to store new cluster configuration and database files and folder

-          Provide appropriate permission to Postgres user on newly created folder

·         Create and initialize the cluster

·         Validation

 

Step-1 Prerequisite Steps

 

-          Validate cluster is up and running and current port number

 

[postgres@PG-V16 ~]$ pg_ctl status

pg_ctl: server is running (PID: 766)

/usr/pgsql-16/bin/postgres "-D" "/var/lib/pgsql/16/data/"

[postgres@PG-V16 ~]$

 

[postgres@PG-V16 ~]$ psql

psql (16.1)

Type "help" for help.

 

postgres=# show port;

 port

------

 5432

(1 row)

 

postgres=#

 

-          Create the directory to store new cluster configuration and database files and folder

 

[root@PG-V16 ~]# mkdir -p /u01

 

-          Provide appropriate permission to Postgres user on the newly created folder

 

[root@PG-V16 ~]# chown postgres:postgres /u01

[root@PG-V16 ~]# chmod 775 /u01

[root@PG-V16 ~]# su – postgres

[postgres@PG-V16 ~]$ mkdir -p /u01/app/postgres/product/v16

[postgres@PG-V16 ~]$

 

Step- 2 Create and initialize the cluster

 

[postgres@PG-V16 ~]$ initdb -D /u01/app/postgres/product/v16 -W

 

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

 

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

 

Data page checksums are disabled.

 

Enter new superuser password:

Enter it again:

 

fixing permissions on existing directory /u01/app/postgres/product/v16 ... ok

creating subdirectories ... ok

selecting dynamic shared memory implementation ... posix

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting default time zone ... America/New_York

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

 

initdb: warning: enabling "trust" authentication for local connections

initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

 

Success. You can now start the database server using:

 

 pg_ctl -D /u01/app/postgres/product/v16 -l logfile start

 

[postgres@PG-V16 ~]$

 

-          Make some changes in Postgresql.conf file like port which is not in use. In this deployment I choose 5433 port.

-           

[postgres@PG-V16 app]$ cd /u01/app/postgres/product/v16

[postgres@PG-V16 v16]$ ls -ltr postgresql.conf

-rw-------. 1 postgres postgres 29705 Dec 31 17:16 postgresql.conf

[postgres@PG-V16 v16]$

 

[postgres@PG-V16 v16]$ vi postgresql.conf

[postgres@PG-V16 v16]$ cat postgresql.conf | grep -w 'port'

port = 5433                             # (change requires restart)

                                        #   %r = remote host and port

[postgres@PG-V16 v16]$

 

-          Check the current cluster status and start it if it is not running

 

[postgres@PG-V16 v16]$ pg_ctl -D /u01/app/postgres/product/v16  start

waiting for server to start....2023-12-31 17:27:25.202 EST [1766] LOG:  redirecting log output to logging collector process

2023-12-31 17:27:25.202 EST [1766] HINT:  Future log output will appear in directory "log".

 done

server started

[postgres@PG-V16 v16]$ pg_ctl -D /u01/app/postgres/product/v16  status

pg_ctl: server is running (PID: 1766)

/usr/pgsql-16/bin/postgres "-D" "/u01/app/postgres/product/v16"

[postgres@PG-V16 v16]$

 

Step-3 Validation

 

[postgres@PG-V16 v16]$ psql -p 5433 -d postgres -U postgres

psql (16.1)

Type "help" for help.

 

postgres=# SELECT pg_reload_conf();

 pg_reload_conf

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

 t

(1 row)

 

postgres=#

INFORMATION: pg_reload_conf() method shows you the reload configuration status.

 

 

 

 

 

Install and Configure PostgreSQL v16 on Linux

 

In this section of installation and configuration, we are going to see how to set up PostgreSQL DB on Linux and after installation of it. We will set OS-level variables using user bash-profile which helps us to access the PostgreSQL cluster easily. Below are the details of the infrastructure details which we use to configure the PostgreSQL environment.

 

Deployment Diagram:

Infrastructure Details:

HOST NAME: PG_V16.example.com
Linux Version	Red Hat Enterprise Linux release 8.6 (Ootpa)
PG Version	PostgreSQL 16.1

 

In this QuickStart, we learn how to:

 

·         Prerequisite Steps

·         Installation of PostgreSQL v16 using Yum 

·         Validation

 

Step-1 Prerequisite Steps

-          Go to the PostgreSQL installation page, read it carefully select the correct option as per your current infrastructure copy the installation script, and execute step by step on your Linux machine

https://www.postgresql.org/download/linux/redhat/

Step-2 Installation of PostgreSQL v16 using Yum 

 

Execute the below command one-by-one

 

#sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

#sudo dnf -qy module disable postgresql

#sudo dnf install -y postgresql16-server

#sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

#sudo systemctl enable postgresql-16

#sudo systemctl start postgresql-16

 

Step-3 Validation

-          Connect the Postgres user once it is present then reset its password to your future convenience

[root@PG-V16 ~]# su - postgres

[postgres@PG-V16 ~]$

[postgres@PG-V16 ~]$

[postgres@PG-V16 ~]$ exit

logout

[root@PG-V16 ~]# passwd postgres

Changing password for user postgres.

New password:

BAD PASSWORD: The password is shorter than 8 characters

Retype new password:

passwd: all authentication tokens updated successfully.

[root@PG-V16 ~]#

 

-          Set the Bash_profile

[root@PG-V16 ~]# su - postgres

[postgres@PG-V16 ~]$ cat .bash_profile

[ -f /etc/profile ] && source /etc/profile

PGDATA=/var/lib/pgsql/16/data

export PGDATA

# If you want to customize your settings,

# Use the file below. This is not overridden

# by the RPMS.

[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

[postgres@PG-V16 ~]$

 

INFORMATION: instead of changing bash_profile lets change the .pgsql_profile file

[postgres@PG-V16 ~]$ cat .bash_profile

[ -f /etc/profile ] && source /etc/profile

PGDATA=/var/lib/pgsql/16/data

export PGDATA

# If you want to customize your settings,

# Use the file below. This is not overridden

# by the RPMS.

[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

 

[postgres@PG-V16 ~]$ cat /var/lib/pgsql/.pgsql_profile

export PGDATA=/var/lib/pgsql/16/data

export PGPORT=5432

export PATH=/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/usr/pgsql-16/bin

 

[postgres@PG-V16 ~]$ env | grep PG

HOSTNAME=PG-V16.example.com

PGDATA=/var/lib/pgsql/16/data

PGPORT=5432

[postgres@PG-V16 ~]$

 

-          Connect to psql and check the PostgreSQL Version

[postgres@PG-V16 ~]$ psql

psql (16.1)

Type "help" for help.

 

postgres=# select version();

                                                 version

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

 PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

(1 row)

 

postgres=#

 

[postgres@PG-V16 ~]$ psql --version

psql (PostgreSQL) 16.1

[postgres@PG-V16 ~]$

 

 

 

 

 

 

 

 

 

 

 

After installation and configuration of the Postgres cluster. Let's see how we can connect to the PG database using the developer tool. In this section, we are going to see how we can connect to the PG database using Azure Data Studio. For this deployment, we already installed the Azure data studio in our local Windows system. you can download the Azure Data Studio from the Microsoft url it is free software.

Deployment diagram:

Infrastructure Details:

HOST NAME: PG_V16.example.com
Client Machine	Red Hat Enterprise Linux release 8.6 (Ootpa)
Azure Data Studio Version	1.47.0 (User Setup)

 

1] Already PG database 5432 port is already enabled on the database server so it will access the database remotely using the same port.

2] let's change the setting in postgresql.conf file

 

[root@PG-V16 data]# pwd

/var/lib/pgsql/16/data

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

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

[root@PG-V16 data]#

 

3] let's change the below parameter to a file pg_hba.conf. Specify the remote server IP/hostname address. If you know the network CDRS of remote users then you can add them so all users from the remote network can access the PG database

[root@PG-V16 data]# cat pg_hba.conf | grep -i host

# "host" records.  In that case you will also need to make PostgreSQL

host    all             all             127.0.0.1/32            scram-sha-256

host    all             all             ::1/128                 scram-sha-256

# Allow replication connections from localhost, by a user with the

host    replication     all             127.0.0.1/32            scram-sha-256

host    all             all             192.168.*.*/0           md5

host    replication     all             ::1/128                 scram-sha-256

 

3] let's try to connect the PG database using Azure Data Studio

 

Click on the connection and specify the database connection details.

ora-01153::undefined an incompatible media recovery is active

 

Information:  If you are seeing below error while starting MRP process means currently your MRP process is running. check MRP process status using below sql.

SQL> select process, status, sequence# from v$managed_standby where process in ('MRP0','RFS');

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG         18
RFS       IDLE                 18
RFS       IDLE                  0
RFS       IDLE                  0

Note: if you want  start it again  then cancel it and start it again using below sql 

SQL> alter database recover managed standby database cancel;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.