Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Posts Tagged ‘postgres’

« Previous Entries

pg_cron : Probably the best way to schedule jobs within PostgreSQL database.

Posted by FatDBA on July 30, 2021

Hi Guys,

Many of databases like Oracle (DBMS_SCHEDULER), MySQL (event_scheduler), Micrsoft SQL Server (SQL Server Agent) have their own build-in job/command scheduling mechanisms, PostgreSQL still does’t have any native or in-build scheduling methods like those databases. There are few popular extensions like pg_cron, pgAgent etc. which can be used to get the scheduling functionality in your PostgreSQL database.

This post will be all about installing, configuring and doing some tests using pg_cron extension and see what all it has to offer. pg_cron which is a simple cron-based job scheduler for PostgreSQL ( >= 9.5) that runs inside the database as an extension. It uses the same grammatical arrangement as regular cron, but it allows you to schedule PostgreSQL commands directly. You can schedule your SQL Statements, stored procedures and tasks like VACUUM, VACUUM ANALYZE

Though there are few limitations with the approach

  • You can’t use pg_cron jobs to schedule a job in seconds interval. For example, you can’t schedule a cron job to run every 10 seconds. For such scenarios, it’s better to write a shell script that uses the sleep command. Still a good option to try!
  • pg_cron can run multiple jobs in parallel, but it runs at most one instance of a job at a time. If a second run is supposed to start before the first one finishes, then the second run is queued and started as soon as the first run completes.

but still a best bet …

Okay, let’s start with the installation. I am using YUM to install the package. I am doing this test on Open Source PostgreSQL 12.6.7

[root@canttowin ~]# yum install pg_cron_12.x86_64
Loaded plugins: langpacks, ulninfo

Resolving Dependencies
--> Running transaction check
---> Package pg_cron_12.x86_64 0:1.3.1-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
 Package                               Arch                              Version                                     Repository                         Size
=============================================================================================================================================================
Installing:
 pg_cron_12                            x86_64                            1.3.1-1.rhel7                               pgdg12                             90 k

Transaction Summary
=============================================================================================================================================================
Install  1 Package

Total download size: 90 k
Installed size: 178 k
Is this ok [y/d/N]: y
Downloading packages:
pg_cron_12-1.3.1-1.rhel7.x86_64.rpm                                                                                                   |  90 kB  00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pg_cron_12-1.3.1-1.rhel7.x86_64                                                                                                           1/1
  Verifying  : pg_cron_12-1.3.1-1.rhel7.x86_64                                                                                                           1/1

Installed:
  pg_cron_12.x86_64 0:1.3.1-1.rhel7

Complete!
You have new mail in /var/spool/mail/root
[root@canttowin ~]#

Next, I will be modifying the parameter ‘shared_preload_libraries‘ within postgresql.conf parameter file which is to specifies one or more shared libraries to be preloaded at server start. Will do a database reboot after that to make changes persistent.

[postgres@canttowin data]$
[postgres@canttowin data]$ more postgresql.conf |grep shared
#shared_preload_libraries = ''  # (change requires restart)


[postgres@canttowin data]$
[postgres@canttowin data]$ more postgresql.conf |grep shared
shared_preload_libraries = 'pg_cron'    # (change requires restart)


[postgres@canttowin bin]$
[postgres@canttowin bin]$ ./pg_ctl -D /var/lib/pgsql/12/data/ stop
waiting for server to shut down.... done
server stopped
[postgres@canttowin bin]$ ./pg_ctl -D /var/lib/pgsql/12/data/ start
waiting for server to start....2021-07-24 11:14:00.948 EDT [14074] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-07-24 11:14:00.948 EDT [14074] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-07-24 11:14:00.948 EDT [14074] LOG:  listening on IPv6 address "::", port 5432
2021-07-24 11:14:00.966 EDT [14074] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-07-24 11:14:00.982 EDT [14074] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-07-24 11:14:01.020 EDT [14074] LOG:  redirecting log output to logging collector process
2021-07-24 11:14:01.020 EDT [14074] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@canttowin bin]$

Next, if you want to use any specific database to create the extension, you have to an additional entry to configuration file with cron.database_name= ‘name of the database’

Now, you will see one more background process ‘pg_cron launcher‘ comes in to existence and can be seen running on the system.

Alright, all set. Now will create the extension on the database and will do some tests.

[postgres@canttowin bin]$
[postgres@canttowin bin]$ psql -p 5432
psql (12.6.7)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=#
postgres=#
postgres=#
postgres=# CREATE EXTENSION pg_cron;
CREATE EXTENSION
postgres=#
postgres=#
postgres=# GRANT USAGE ON SCHEMA cron TO postgres;
GRANT
postgres=#



-- How to check job lists.
postgres=# SELECT * FROM cron.job;
 jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+----------+---------+----------+----------+----------+----------+--------+---------
(0 rows)

-- To schedule a VACUUM ANALYZE on each day at 11:00 AM
postgres=# SELECT cron.schedule('0 11 * * *', 'VACUUM ANALYZE');
 schedule
----------
        1
(1 row)

postgres=# SELECT * FROM cron.job;
 jobid |  schedule  |    command     | nodename  | nodeport | database | username | active | jobname
-------+------------+----------------+-----------+----------+----------+----------+--------+---------
     1 | 0 11 * * * | VACUUM ANALYZE | localhost |     5432 | postgres | postgres | t      |
(1 row)

postgres=#

postgres=#
postgres=#
postgres=# table cron.job;

 jobid |  schedule  |    command     | nodename  | nodeport | database | username | active | jobname
-------+------------+----------------+-----------+----------+----------+----------+--------+---------
     1 | 0 11 * * * | VACUUM ANALYZE | localhost |     5432 | postgres | postgres | t      |
(1 row)


Lets do some more tests, will now create a table with some test records and will try to delete them using pg_cron.

postgres=#
postgres=# CREATE TABLE article (
postgres(#     article_id bigserial primary key,
postgres(#     article_name varchar(20) NOT NULL,
postgres(#     article_desc text NOT NULL,
postgres(#     date_added timestamp default NULL
postgres(# );
CREATE TABLE
postgres=#


postgres=# insert into article (article_id, article_name, article_desc, date_added) values (1010,'vinyl','Beatles 1980 Vinyl',current_timestamp);
postgres=# insert into article (article_id, article_name, article_desc, date_added) values (1011,'Tape','Deftones 2015 Vinyl',current_timestamp);
postgres=# insert into article (article_id, article_name, article_desc, date_added) values (1019,'Tape','Deftones 2015 Vinyl',current_timestamp);



postgres=#
postgres=# select * from article;
 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:03:30.514886
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:03:40.870081
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:03:52.046054
(3 rows)

postgres=#


postgres=# \! date
Sat Jul 24 12:20:40 EDT 2021
postgres=#



-- To delete entries those are older than 1 minute
postgres=# SELECT cron.schedule('23 12 * * *', $$DELETE FROM article WHERE date_added<now()- interval '1 Mins'$$);
 schedule
----------
       2
(1 row)

postgres=#
postgres=# SELECT * FROM cron.job;
 jobid |  schedule   |                            command                            | nodename  | nodeport | database | username | active | jobname
-------+-------------+---------------------------------------------------------------+-----------+----------+----------+----------+--------+---------
     1 | */5 * * * * | VACUUM                                                        | localhost |     5432 | postgres | postgres | t      |
     2 | 23 12 * * * | DELETE FROM article WHERE date_added<now()- interval '1 Mins' | localhost |     5432 | postgres | postgres | t      |
(2 rows)


-- This is what it is before pg_cron executing the schedule.
postgres=#
postgres=#
postgres=# select * from article;
 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:16:00.507391
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:05.762869
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:09.960464
(3 rows)


-- Will do a watch on table records to see if pg_cron comes and delete records on specified time.
postgres=#
postgres=# \watch 5
                  Sat 24 Jul 2021 12:22:43 PM EDT (every 5s)

 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:16:00.507391
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:05.762869
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:09.960464
(3 rows)

                  Sat 24 Jul 2021 12:22:48 PM EDT (every 5s)

 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:16:00.507391
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:05.762869
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:09.960464
(3 rows)

                  Sat 24 Jul 2021 12:22:53 PM EDT (every 5s)

 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:16:00.507391
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:05.762869
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:09.960464
(3 rows)

                  Sat 24 Jul 2021 12:22:58 PM EDT (every 5s)

 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------
       1010 | vinyl        | Beatles 1980 Vinyl  | 2021-07-24 12:16:00.507391
       1011 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:05.762869
       1019 | Tape         | Deftones 2015 Vinyl | 2021-07-24 12:16:09.960464
(3 rows)

                  Sat 24 Jul 2021 12:23:03 PM EDT (every 5s)

 article_id | article_name |    article_desc     |         date_added
------------+--------------+---------------------+----------------------------

Yes, it did pretty smoothly on specified time! Let’s do some more fun.

postgres=#
postgres=# CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT)
postgres-# AS $$ BEGIN
postgres$# RAISE NOTICE 'Procedure Parameter: %', msg ;
postgres$# END ;
postgres$# $$
postgres-# LANGUAGE plpgsql ;
CREATE PROCEDURE
postgres=#
postgres=# call display_message('This is my test case');
NOTICE:  Procedure Parameter: This is my test case
         msg
----------------------
 This is my test case
(1 row)



postgres=# SELECT cron.schedule('display_message', '* * * * *', 'CALL display_message()');
 schedule
----------
       14
(1 row)

postgres=# SELECT * FROM cron.job;
 jobid |  schedule   |                            command                            | nodename  | nodeport | database | username | active |     jobname
-------+-------------+---------------------------------------------------------------+-----------+----------+----------+----------+--------+-----------------
     9 | */5 * * * * | VACUUM                                                        | localhost |     5432 | postgres | postgres | t      |
    12 | 20 12 * * * | DELETE FROM article WHERE date_added<now()- interval '1 Mins' | localhost |     5432 | postgres | postgres | t      |
    13 | 0 3 * * *   | VACUUM article                                                | localhost |     5432 | postgres | postgres | t      |
    14 | * * * * *   | CALL display_message()                                        | localhost |     5432 | postgres | postgres | t      | display_message
(4 rows)

Now, how to delete a single schedule or all/multiple.

postgres=#  SELECT cron.unschedule(2);
 unschedule
------------
 t
(1 row)


postgres=#
postgres=# SELECT cron.unschedule(jobid) FROM cron.job;
 unschedule
------------
 t
 t

(2 rows)

postgres=#
postgres=# SELECT * FROM cron.job;
 jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+----------+---------+----------+----------+----------+----------+--------+---------
(0 rows)

postgres=#

You can check database log files (postgresql-<day of the week>.log) to see if all okay with the jobs. If any of the scheduled job failed to execute, it will log more details to the log file, example, see below screenshot where one of the job with ID 14 trying to start but failed due to ‘connection refused‘ error. So, here we have got a hint to check pg_hba file and accordingly take the action to mitigate the problem.

After we applied the change …

Hope It Helped!
Prashant Dixit

Advertisement

Posted in Advanced | Tagged: , | 2 Comments »

How to fix/handle Wraparound problem in PostgreSQL ?

Posted by FatDBA on July 21, 2021

Hi Guys,

In continuation from where I left in my last post on simulating wraparounds in PostgreSQL, this post is all about managing the mess that we spilled 🙂 Though this was a synthetic/artificial test and as soon you close or exit SESSION 1 where you’ve opened a BEGIN block/transaction and ran select txid_current() to block autovacuum, it (autovacuum) will automatically kick-in and perform a quick full vacuum on all of the databases in the cluster. You have to remember that even if you have autovacuum turned off, it will still start in emergency mode.

So, this is what we got earlier at the time we hit by the wraparound problem when we tried to execute a DML and it says something about stopping postmaster and vacuum databases in single-user mode; What is that ? – We will see that next ….

enterprisedb=#
enterprisedb=# SELECT datname
, age(datfrozenxid)
, current_setting('autovacuum_freeze_max_age')
FROM pg_database
 ORDER BY 2 DESC;
 
   datname    |    age     | current_setting
--------------+------------+-----------------
 postgres     | 2146483647 | 200000000
 edb          | 2146483647 | 200000000
 template1    | 2146483647 | 200000000
 template0    | 2146483647 | 200000000
 immortal     | 2146483647 | 200000000
 darkthrone   | 2146483647 | 200000000
 immortal1    | 2146483647 | 200000000
 mayhem       | 2146483647 | 200000000
 murduk       | 2146483647 | 200000000
 burzum       | 2146483647 | 200000000
 gorgoroth    | 2146483647 | 200000000
 repmgr       | 2146483647 | 200000000
 enterprisedb | 2146483647 | 200000000
(13 rows)



enterprisedb=# update pgbench_history set aid=65555 where aid=64444;
ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
enterprisedb=#
enterprisedb=#

Okay, coming back to the point, how to handle the situation now when you’ve already hit the wraparound. So, first thing you have to manually shut down the database cluster, if it’s still up.

-bash-4.2$ pwd
/usr/edb/as11/bin

-bash-4.2$ /usr/edb/as11/bin/pg_ctl stop -D /var/lib/edb/as11/data
waiting for server to shut down.... done
server stopped
-bash-4.2$

Next, you need to run VACUUM FULL for each of the database (one-by-one using single mode) through stand-alone backend mode. For that you have to call postgres (if you’re on open source) or edb-postgres utility. I am on EDB PostgreSQL 11 so will be calling edb-postgres and execute VACUUM FULL.

-bash-4.2$ pwd
/usr/edb/as11/bin

-bash-4.2$
-bash-4.2$ ls *postgre*
edb-postgres
-bash-4.2$

-bash-4.2$ ./edb-postgres --single -D /var/lib/edb/as11/data enterprisedb

PostgreSQL stand-alone backend 11.11.20
backend> vacuum full;
2021-07-20 11:07:49 IST LOG:  statement: vacuum full;

backend>

It’s done for ‘enterprisedb’ database. Next will do it for ‘postgresql’ database, but this time I will use some stand-alone backend related command to assure we are connected with the right database and to enable runtime stats or verbose.

-bash-4.2$ ./edb-postgres --single -D /var/lib/edb/as11/data postgres

PostgreSQL stand-alone backend 11.11.20
backend>
backend> select current_database();
2021-07-20 13:45:25 IST LOG:  statement: select current_database();

         1: current_database    (typeid = 19, len = 64, typmod = -1, byval = f)
        ----
         1: current_database = "postgres"       (typeid = 19, len = 64, typmod = -1, byval = f)
        ----
backend> 


backend> SELECT set_config('log_statement_stats', 'on', false);
2021-07-20 13:48:55 IST LOG:  statement: SELECT set_config('log_statement_stats', 'on', false);

         1: set_config  (typeid = 25, len = -1, typmod = -1, byval = f)
        ----
         1: set_config = "on"   (typeid = 25, len = -1, typmod = -1, byval = f)
        ----
backend>

-- Now when we have enabled logging of stats, let's execute the VACUUM to see runtime stats
backend> vacuum full;
2021-07-20 13:49:29 IST LOG:  statement: vacuum full;

2021-07-20 13:49:30 IST LOG:  QUERY STATISTICS
2021-07-20 13:49:30 IST DETAIL:  ! system usage stats:
        !       0.557302 s user, 0.426659 s system, 120.357278 s elapsed
        !       [0.583998 s user, 0.455579 s system total]
        !       21756 kB max resident size
        !       224/402352 [240/402824] filesystem blocks in/out
        !       0/2558 [0/6201151] page faults/reclaims, 0 [0] swaps
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
        !       982/2 [1005/3] voluntary/involuntary context switches
2021-07-20 13:58:39 IST STATEMENT:  vacuum full;

backend>

Okay, same way we have to run VACUUM FULL on all of the databases and restart PostgreSQL cluster.
Once all of the databases are done and after restart, connect with the database and you will see the TXID or datfrozenid has been reset and you are all good.

enterprisedb=# SELECT datname
, age(datfrozenxid)
, current_setting('autovacuum_freeze_max_age')
FROM pg_database
 ORDER BY 2 DESC;

    datname    | age | current_setting
--------------+-----+-----------------
postgres     | 1369 | 200000000
edb          | 1272 | 200000000
template1    | 1170 | 200000000
template0    | 1071 | 200000000
immortal     |  974 | 200000000
darkthrone   |  877 | 200000000
immortal1    |  780 | 200000000
mayhem       |  585 | 200000000
murduk       |  487 | 200000000
burzum       |  388 | 200000000
gorgoroth    |  291 | 200000000
repmgr       |  194 | 200000000
enterprisedb |   97 | 200000000

You’re all set!

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , , | Leave a Comment »

How to simulate the deadly, infamous, misunderstood & complex ‘Transaction Wraparound Problem’ in PostgreSQL …

Posted by FatDBA on July 20, 2021

Hi Guys,

Last week one of my good friend asked me an interesting question – ‘How to replicate the transaction wraparound problem’ in PostgreSQL ?

He has a demo/POC that he needs to do for one of his customer who is interested to see how it looks like in reality and how ‘deadly’ is the problem and how to fix this. He scanned almost everything on the Google, but unfortunately found nothing available or pre-cooked …

So, this might be the only post available on the internet explaining how to exactly simulate the transaction wraparound in the PostgreSQL Database. This one was really interesting and I have never tried this before as I thought I will never see transaction ID wraparound error 🙂 . So, before I jump in to the demo, we need to take care of few things. Use a system with good resources, I have performed this demo on an EDB PostgreSQL 11.11.20 database with 8 CPUs, 31 GB RAM, and it took me around 9 hours to exhaust all transactionIDs and reach the limit of 2,000,000,000.

Would like to thank Nikolay Samokhvalov for his help! 🙂

Before I start, let’s do this one more time – What is a transaction ID and why it wrapsaround ?
The PostgreSQL transaction control mechanism assigns a transaction ID to every row that is modified in the database; these IDs control the visibility of that row to other concurrent transactions. Txid wraparound is a problem due to MVCC. MVCC relies on being able to take the txids of two transactions and determine which of the transactions came first.
A PostgreSQL database can have two billion ‘in-flight’ unvacuumed transactions before PostgreSQL takes dramatic action to avoid data loss. If the number of unvacuumed transactions reaches (2^31 – 10,000,000), the log starts warning that vacuuming is needed. If the number of unvacuumed transactions reaches 2,000,000,000, PostgreSQL sets the database to read-only mode and requires an offline, single-user, standalone vacuum.

Note: You have to carefully monitor your database pg_log, archiving locations as this activity will generate huge WALs. At the same time, keep an eye on the size of your log directory, specially if you are capturing all statements.

We will open multiple parallel sessions to simulate the workload and monitor the progress.

SESSION 1:

In the first session, we will first connect with the database and set idle_in_transaction_session_timeout to value 0 and start a BEGIN block and execute select txid_current(); this is to block autovacuum, as simply turning it off wouldn’t help because emegency mode kick in even with `autovacuum = off.

The idle_in_transaction_session_timeout parameter terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. So, we will have to set it to value 0 that will disable this feature.

-bash-4.2$ psql -p 5444
psql.bin (11.11.20)
Type "help" for help.

enterprisedb=# SET idle_in_transaction_session_timeout = 0;
SET
enterprisedb=#  BEGIN;
BEGIN
enterprisedb=# select txid_current();
 txid_current
--------------
         1266

enterprisedb=#
enterprisedb=#

SESSION 2:

Now, I will create a file with current transaction ID written in to it and will call the pgbench. I am doing something like below to call pgbench, below are the explanations on flags used and why I used them ..

pgbench -U enterprisedb enterprisedb -f wl.sql -c8 -j8 -T100000 -rn -P60

-f: to read from a file
-c8: 8 clients
-j8: 8 threads/jobs (adjust it as per your CPU count)
-T100000: seconds to run the test for this many seconds, rather than a fixed number of transactions per client
-rn: where r is to report the average per-statement latency (execution time from the perspective of the client) of each command after the benchmark finishes. Whereas n is to perform no vacuuming before running the test. This option is necessary if you are running a custom test scenario that does not include the standard tables pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers.
-P60: is to show progress report every sec seconds

-bash-4.2$ 
-bash-4.2$ echo 'select txid_current();' > wl.sql
-bash-4.2$ 
-bash-4.2$
-bash-4.2$ /usr/edb/as11/bin/pgbench -U enterprisedb enterprisedb -f wl.sql -c8 -j8 -T100000 -rn -P60
progress: 60.0 s, 51097.6 tps, lat 0.157 ms stddev 0.262
progress: 120.0 s, 51171.1 tps, lat 0.156 ms stddev 0.252
progress: 180.0 s, 51622.8 tps, lat 0.155 ms stddev 0.250
progress: 240.0 s, 51328.9 tps, lat 0.156 ms stddev 0.260
progress: 300.0 s, 50976.6 tps, lat 0.157 ms stddev 0.256
progress: 360.0 s, 51130.3 tps, lat 0.156 ms stddev 0.265
progress: 420.0 s, 50792.6 tps, lat 0.158 ms stddev 0.254
progress: 480.0 s, 51207.2 tps, lat 0.156 ms stddev 0.250
progress: 540.0 s, 51162.6 tps, lat 0.156 ms stddev 0.251
progress: 600.0 s, 51246.0 tps, lat 0.156 ms stddev 0.256
progress: 660.0 s, 51047.3 tps, lat 0.157 ms stddev 0.253
progress: 720.0 s, 51197.3 tps, lat 0.156 ms stddev 0.252
progress: 780.0 s, 51119.2 tps, lat 0.156 ms stddev 0.281
progress: 840.0 s, 50938.8 tps, lat 0.157 ms stddev 0.252
progress: 900.0 s, 51252.8 tps, lat 0.156 ms stddev 0.251
progress: 960.0 s, 51054.0 tps, lat 0.157 ms stddev 0.253
progress: 1020.0 s, 51159.5 tps, lat 0.156 ms stddev 0.254
progress: 1080.0 s, 51116.0 tps, lat 0.157 ms stddev 0.253
progress: 1140.0 s, 50947.6 tps, lat 0.157 ms stddev 0.251
progress: 1200.0 s, 51289.7 tps, lat 0.156 ms stddev 0.254
progress: 1260.0 s, 50338.7 tps, lat 0.159 ms stddev 0.254
progress: 1320.0 s, 50121.1 tps, lat 0.160 ms stddev 0.260
progress: 1380.0 s, 49936.6 tps, lat 0.160 ms stddev 0.291
progress: 1440.0 s, 50451.1 tps, lat 0.159 ms stddev 0.251
progress: 1500.0 s, 51107.4 tps, lat 0.157 ms stddev 0.248
progress: 1560.0 s, 50907.8 tps, lat 0.157 ms stddev 0.321
progress: 1620.0 s, 51033.7 tps, lat 0.157 ms stddev 0.251
progress: 1680.0 s, 51013.6 tps, lat 0.157 ms stddev 0.254
progress: 1740.0 s, 51119.4 tps, lat 0.156 ms stddev 0.252
progress: 1800.0 s, 51411.9 tps, lat 0.156 ms stddev 0.249
progress: 1860.0 s, 51099.1 tps, lat 0.157 ms stddev 0.249
progress: 1920.0 s, 51527.5 tps, lat 0.155 ms stddev 0.256
progress: 1980.0 s, 51204.3 tps, lat 0.156 ms stddev 0.267
progress: 2040.0 s, 51363.8 tps, lat 0.156 ms stddev 0.249
progress: 2100.0 s, 51563.1 tps, lat 0.155 ms stddev 0.250
progress: 2160.0 s, 51329.3 tps, lat 0.156 ms stddev 0.246
progress: 2220.0 s, 51073.4 tps, lat 0.157 ms stddev 0.276
progress: 2280.0 s, 51402.0 tps, lat 0.156 ms stddev 0.248
progress: 2340.0 s, 51364.4 tps, lat 0.156 ms stddev 0.253
progress: 2400.0 s, 50890.0 tps, lat 0.157 ms stddev 0.251
progress: 2460.0 s, 50239.6 tps, lat 0.159 ms stddev 0.296
progress: 2520.0 s, 50684.8 tps, lat 0.158 ms stddev 0.283
progress: 2580.0 s, 49826.2 tps, lat 0.161 ms stddev 0.271
progress: 2640.0 s, 51076.7 tps, lat 0.157 ms stddev 0.250
progress: 2700.0 s, 51349.6 tps, lat 0.156 ms stddev 0.252
progress: 2760.0 s, 51076.6 tps, lat 0.157 ms stddev 0.253
progress: 2820.0 s, 50854.5 tps, lat 0.157 ms stddev 0.256
progress: 2880.0 s, 49864.8 tps, lat 0.160 ms stddev 0.260
progress: 2940.0 s, 50450.1 tps, lat 0.159 ms stddev 0.264
progress: 3000.0 s, 50334.7 tps, lat 0.159 ms stddev 0.264
progress: 3060.0 s, 50514.3 tps, lat 0.158 ms stddev 0.299
progress: 3120.0 s, 50508.7 tps, lat 0.158 ms stddev 0.284
progress: 3180.0 s, 50691.3 tps, lat 0.158 ms stddev 0.288
progress: 3240.0 s, 50601.6 tps, lat 0.158 ms stddev 0.256
progress: 3300.0 s, 50280.2 tps, lat 0.159 ms stddev 0.264
progress: 3360.0 s, 50457.6 tps, lat 0.159 ms stddev 0.265
progress: 3420.0 s, 50178.3 tps, lat 0.159 ms stddev 0.390
progress: 3480.0 s, 50353.3 tps, lat 0.159 ms stddev 0.264
progress: 3540.0 s, 50133.0 tps, lat 0.160 ms stddev 0.259
progress: 3600.0 s, 50217.6 tps, lat 0.159 ms stddev 0.282
progress: 3660.0 s, 50147.8 tps, lat 0.160 ms stddev 0.279
progress: 3720.0 s, 50578.0 tps, lat 0.158 ms stddev 0.261
progress: 3780.0 s, 50523.9 tps, lat 0.158 ms stddev 0.261
progress: 3840.0 s, 50349.7 tps, lat 0.159 ms stddev 0.269
progress: 3900.0 s, 50437.5 tps, lat 0.159 ms stddev 0.287
progress: 3960.0 s, 49569.1 tps, lat 0.161 ms stddev 0.311
progress: 4020.0 s, 50337.3 tps, lat 0.159 ms stddev 0.286
progress: 4080.0 s, 50065.2 tps, lat 0.160 ms stddev 0.260
progress: 4140.0 s, 49791.4 tps, lat 0.161 ms stddev 0.264
progress: 4200.0 s, 49681.3 tps, lat 0.161 ms stddev 0.326
progress: 4260.0 s, 48034.2 tps, lat 0.167 ms stddev 0.340
progress: 4320.0 s, 50460.9 tps, lat 0.159 ms stddev 0.290
progress: 4380.0 s, 49731.0 tps, lat 0.161 ms stddev 0.300
progress: 4440.0 s, 50441.1 tps, lat 0.159 ms stddev 0.266
progress: 4500.0 s, 50259.1 tps, lat 0.159 ms stddev 0.267
progress: 4560.0 s, 50197.0 tps, lat 0.159 ms stddev 0.259
progress: 4620.0 s, 49832.1 tps, lat 0.161 ms stddev 0.271
progress: 4680.0 s, 50160.3 tps, lat 0.159 ms stddev 0.268
progress: 4740.0 s, 50578.5 tps, lat 0.158 ms stddev 0.262
progress: 4800.0 s, 50260.5 tps, lat 0.159 ms stddev 0.270
progress: 4860.0 s, 50545.1 tps, lat 0.158 ms stddev 0.256
progress: 4920.0 s, 49961.8 tps, lat 0.160 ms stddev 0.285
progress: 4980.0 s, 50450.7 tps, lat 0.159 ms stddev 0.267
progress: 5040.0 s, 50008.7 tps, lat 0.160 ms stddev 0.257
progress: 5100.0 s, 49723.2 tps, lat 0.161 ms stddev 0.428
progress: 5160.0 s, 50211.1 tps, lat 0.159 ms stddev 0.378
progress: 5220.0 s, 49791.0 tps, lat 0.161 ms stddev 0.262
progress: 5280.0 s, 50091.5 tps, lat 0.160 ms stddev 0.265
progress: 5340.0 s, 50280.3 tps, lat 0.159 ms stddev 0.266
progress: 5400.0 s, 49957.1 tps, lat 0.160 ms stddev 0.402
progress: 5460.0 s, 50101.7 tps, lat 0.160 ms stddev 0.293
progress: 5520.0 s, 50387.3 tps, lat 0.159 ms stddev 0.261
progress: 5580.0 s, 50173.2 tps, lat 0.159 ms stddev 0.278
progress: 5640.0 s, 50225.1 tps, lat 0.159 ms stddev 0.266
progress: 5700.0 s, 50186.7 tps, lat 0.159 ms stddev 0.264
progress: 5760.0 s, 49120.1 tps, lat 0.163 ms stddev 0.264
progress: 5820.0 s, 49445.3 tps, lat 0.162 ms stddev 0.262
progress: 5880.0 s, 49547.2 tps, lat 0.161 ms stddev 0.260
progress: 5940.0 s, 49730.2 tps, lat 0.161 ms stddev 0.267
progress: 6000.0 s, 50543.3 tps, lat 0.158 ms stddev 0.258
progress: 6060.0 s, 50569.1 tps, lat 0.158 ms stddev 0.258
progress: 6120.0 s, 50560.4 tps, lat 0.158 ms stddev 0.250
progress: 6180.0 s, 50572.1 tps, lat 0.158 ms stddev 0.255
progress: 6240.0 s, 50551.6 tps, lat 0.158 ms stddev 0.253
progress: 6300.0 s, 50993.8 tps, lat 0.157 ms stddev 0.254
progress: 6360.0 s, 51114.6 tps, lat 0.157 ms stddev 0.257
progress: 6420.0 s, 50762.6 tps, lat 0.158 ms stddev 0.261
progress: 6480.0 s, 50814.2 tps, lat 0.157 ms stddev 0.250
progress: 6540.0 s, 50735.7 tps, lat 0.158 ms stddev 0.257
progress: 6600.0 s, 50521.9 tps, lat 0.158 ms stddev 0.263
progress: 6660.0 s, 50829.8 tps, lat 0.157 ms stddev 0.252
progress: 6720.0 s, 50933.7 tps, lat 0.157 ms stddev 0.255
progress: 6780.0 s, 51152.9 tps, lat 0.156 ms stddev 0.252
progress: 6840.0 s, 50457.1 tps, lat 0.159 ms stddev 0.255
progress: 6900.0 s, 50542.5 tps, lat 0.158 ms stddev 0.251
progress: 6960.0 s, 50802.5 tps, lat 0.157 ms stddev 0.282
progress: 7020.0 s, 51066.1 tps, lat 0.157 ms stddev 0.270
progress: 7080.0 s, 50729.0 tps, lat 0.158 ms stddev 0.258
progress: 7140.0 s, 51242.7 tps, lat 0.156 ms stddev 0.255
progress: 7200.0 s, 50714.2 tps, lat 0.158 ms stddev 0.253
progress: 7260.0 s, 50704.7 tps, lat 0.158 ms stddev 0.323
progress: 7320.0 s, 50951.4 tps, lat 0.157 ms stddev 0.250
progress: 7380.0 s, 50604.8 tps, lat 0.158 ms stddev 0.340
progress: 7440.0 s, 51217.0 tps, lat 0.156 ms stddev 0.251
progress: 7500.0 s, 50832.4 tps, lat 0.157 ms stddev 0.251
progress: 7560.0 s, 50815.7 tps, lat 0.157 ms stddev 0.256
progress: 7620.0 s, 49936.3 tps, lat 0.160 ms stddev 0.463
progress: 7680.0 s, 50556.4 tps, lat 0.158 ms stddev 0.251
progress: 7740.0 s, 50699.8 tps, lat 0.158 ms stddev 0.255
progress: 7800.0 s, 50474.0 tps, lat 0.158 ms stddev 0.256
progress: 7860.0 s, 50401.9 tps, lat 0.159 ms stddev 0.321
progress: 7920.0 s, 51385.5 tps, lat 0.156 ms stddev 0.251
progress: 7980.0 s, 50671.9 tps, lat 0.158 ms stddev 0.248
progress: 8040.0 s, 50631.3 tps, lat 0.158 ms stddev 0.252
progress: 8100.0 s, 50741.3 tps, lat 0.158 ms stddev 0.255
progress: 8160.0 s, 50465.5 tps, lat 0.159 ms stddev 0.257
progress: 8220.0 s, 51384.9 tps, lat 0.156 ms stddev 0.212
progress: 8280.0 s, 51676.8 tps, lat 0.155 ms stddev 0.233
progress: 8340.0 s, 52049.3 tps, lat 0.154 ms stddev 0.179
progress: 8400.0 s, 52394.6 tps, lat 0.153 ms stddev 0.178
progress: 8460.0 s, 51872.3 tps, lat 0.154 ms stddev 0.192
progress: 8520.0 s, 51896.9 tps, lat 0.154 ms stddev 0.178
progress: 8580.0 s, 52187.5 tps, lat 0.153 ms stddev 0.178
progress: 8640.0 s, 51773.8 tps, lat 0.155 ms stddev 0.185
progress: 8700.0 s, 51862.7 tps, lat 0.154 ms stddev 0.184
progress: 8760.0 s, 51906.7 tps, lat 0.154 ms stddev 0.189
progress: 8820.0 s, 52038.6 tps, lat 0.154 ms stddev 0.189
progress: 8880.0 s, 52370.9 tps, lat 0.153 ms stddev 0.180
progress: 8940.0 s, 52155.2 tps, lat 0.153 ms stddev 0.185
progress: 9000.0 s, 52162.2 tps, lat 0.153 ms stddev 0.219
progress: 9060.0 s, 51900.7 tps, lat 0.154 ms stddev 0.189
progress: 9120.0 s, 51794.5 tps, lat 0.154 ms stddev 0.178
progress: 9180.0 s, 51885.1 tps, lat 0.154 ms stddev 0.180
progress: 9240.0 s, 52007.4 tps, lat 0.154 ms stddev 0.177
progress: 9300.0 s, 52168.3 tps, lat 0.153 ms stddev 0.189
progress: 9360.0 s, 51801.3 tps, lat 0.154 ms stddev 0.182
progress: 9420.0 s, 51899.8 tps, lat 0.154 ms stddev 0.173
progress: 9480.0 s, 52215.3 tps, lat 0.153 ms stddev 0.177
progress: 9540.0 s, 52129.9 tps, lat 0.153 ms stddev 0.227
progress: 9600.0 s, 51929.9 tps, lat 0.154 ms stddev 0.176
progress: 9660.0 s, 51776.6 tps, lat 0.155 ms stddev 0.181
progress: 9720.0 s, 51542.8 tps, lat 0.155 ms stddev 0.268
progress: 9780.0 s, 51352.5 tps, lat 0.156 ms stddev 0.310
progress: 9840.0 s, 51661.0 tps, lat 0.155 ms stddev 0.177
progress: 9900.0 s, 51219.8 tps, lat 0.156 ms stddev 0.260
progress: 9960.0 s, 52235.8 tps, lat 0.153 ms stddev 0.184
progress: 10020.0 s, 51904.5 tps, lat 0.154 ms stddev 0.186
progress: 10080.0 s, 52066.8 tps, lat 0.154 ms stddev 0.183
progress: 10140.0 s, 52212.5 tps, lat 0.153 ms stddev 0.209
progress: 10200.0 s, 52402.8 tps, lat 0.153 ms stddev 0.186
progress: 10260.0 s, 52194.8 tps, lat 0.153 ms stddev 0.178
progress: 10320.0 s, 52117.3 tps, lat 0.153 ms stddev 0.179
progress: 10380.0 s, 52060.5 tps, lat 0.154 ms stddev 0.181
progress: 10440.0 s, 52043.1 tps, lat 0.154 ms stddev 0.183
progress: 10500.0 s, 52358.5 tps, lat 0.153 ms stddev 0.178
progress: 10560.0 s, 51361.2 tps, lat 0.156 ms stddev 0.179
progress: 10620.0 s, 51655.7 tps, lat 0.155 ms stddev 0.184
progress: 10680.0 s, 51501.1 tps, lat 0.155 ms stddev 0.176
progress: 10740.0 s, 51315.4 tps, lat 0.156 ms stddev 0.182
progress: 10800.0 s, 52344.5 tps, lat 0.153 ms stddev 0.181
progress: 10860.0 s, 51687.0 tps, lat 0.155 ms stddev 0.182
progress: 10920.0 s, 51150.2 tps, lat 0.156 ms stddev 0.179
progress: 10980.0 s, 51341.9 tps, lat 0.156 ms stddev 0.181
progress: 11040.0 s, 51074.0 tps, lat 0.157 ms stddev 0.184
progress: 11100.0 s, 51645.4 tps, lat 0.155 ms stddev 0.277
progress: 11160.0 s, 51984.6 tps, lat 0.154 ms stddev 0.182
progress: 11220.0 s, 51993.4 tps, lat 0.154 ms stddev 0.181
progress: 11280.0 s, 51894.9 tps, lat 0.154 ms stddev 0.183
progress: 11340.0 s, 52007.2 tps, lat 0.154 ms stddev 0.186
progress: 11400.0 s, 52255.9 tps, lat 0.153 ms stddev 0.188
progress: 11460.0 s, 52221.2 tps, lat 0.153 ms stddev 0.183
progress: 11520.0 s, 51990.5 tps, lat 0.154 ms stddev 0.181
progress: 11580.0 s, 52138.3 tps, lat 0.153 ms stddev 0.178
progress: 11640.0 s, 51258.2 tps, lat 0.156 ms stddev 0.178
progress: 11700.0 s, 52268.0 tps, lat 0.153 ms stddev 0.179
progress: 11760.0 s, 51838.4 tps, lat 0.154 ms stddev 0.180
progress: 11820.0 s, 52079.2 tps, lat 0.154 ms stddev 0.185
progress: 11880.0 s, 52531.4 tps, lat 0.152 ms stddev 0.175
progress: 11940.0 s, 51711.7 tps, lat 0.155 ms stddev 0.260
progress: 12000.0 s, 52020.7 tps, lat 0.154 ms stddev 0.180
progress: 12060.0 s, 51990.1 tps, lat 0.154 ms stddev 0.241
progress: 12120.0 s, 51367.6 tps, lat 0.156 ms stddev 0.193
progress: 12180.0 s, 51316.0 tps, lat 0.156 ms stddev 0.187
progress: 12240.0 s, 50819.0 tps, lat 0.157 ms stddev 0.190
progress: 12300.0 s, 51614.6 tps, lat 0.155 ms stddev 0.182
progress: 12360.0 s, 51500.6 tps, lat 0.155 ms stddev 0.187
progress: 12420.0 s, 51715.8 tps, lat 0.155 ms stddev 0.259
progress: 12480.0 s, 51805.0 tps, lat 0.154 ms stddev 0.183
progress: 12540.0 s, 51846.2 tps, lat 0.154 ms stddev 0.183
progress: 12600.0 s, 51829.6 tps, lat 0.154 ms stddev 0.179
progress: 12660.0 s, 51411.1 tps, lat 0.156 ms stddev 0.221
progress: 12720.0 s, 51728.5 tps, lat 0.155 ms stddev 0.187
progress: 12780.0 s, 52170.5 tps, lat 0.153 ms stddev 0.181
progress: 12840.0 s, 51985.8 tps, lat 0.154 ms stddev 0.182
progress: 12900.0 s, 51723.3 tps, lat 0.155 ms stddev 0.181
progress: 12960.0 s, 52324.2 tps, lat 0.153 ms stddev 0.184
progress: 13020.0 s, 51795.5 tps, lat 0.154 ms stddev 0.189
progress: 13080.0 s, 51547.3 tps, lat 0.155 ms stddev 0.373
progress: 13140.0 s, 51592.4 tps, lat 0.155 ms stddev 0.231
progress: 13200.0 s, 52004.8 tps, lat 0.154 ms stddev 0.223
progress: 13260.0 s, 52118.3 tps, lat 0.153 ms stddev 0.184
progress: 13320.0 s, 51781.1 tps, lat 0.154 ms stddev 0.183
progress: 13380.0 s, 50621.8 tps, lat 0.158 ms stddev 0.196
progress: 13440.0 s, 50671.8 tps, lat 0.158 ms stddev 0.321
progress: 13500.0 s, 51389.4 tps, lat 0.156 ms stddev 0.181
progress: 13560.0 s, 51653.2 tps, lat 0.155 ms stddev 0.186
progress: 13620.0 s, 51491.7 tps, lat 0.155 ms stddev 0.181
progress: 13680.0 s, 51639.4 tps, lat 0.155 ms stddev 0.183
progress: 13740.0 s, 51385.3 tps, lat 0.156 ms stddev 0.188
progress: 13800.0 s, 51640.2 tps, lat 0.155 ms stddev 0.177
progress: 13860.0 s, 51820.7 tps, lat 0.154 ms stddev 0.176
progress: 13920.0 s, 52041.9 tps, lat 0.154 ms stddev 0.179
progress: 13980.0 s, 52001.0 tps, lat 0.154 ms stddev 0.178
progress: 14040.0 s, 52189.3 tps, lat 0.153 ms stddev 0.176
progress: 14100.0 s, 51897.9 tps, lat 0.154 ms stddev 0.179
progress: 14160.0 s, 52066.4 tps, lat 0.154 ms stddev 0.180
progress: 14220.0 s, 52257.9 tps, lat 0.153 ms stddev 0.178
progress: 14280.0 s, 51850.1 tps, lat 0.154 ms stddev 0.210
......
.........
...........

SESSION 3:

Now in this third session we will monitor the database to see what’s going on. You can do something like below to check where we are going.

Below SQL will show you the ‘datfrozenxid‘ which is a value for all transaction IDs before this one have been replaced with a permanent (“frozen”) transaction ID in this database. This is used to track whether the database needs to be vacuumed in order to prevent transaction ID wraparound. And to see ‘autovacuum_freeze_max_age‘ that is age at which to autovacuum a table to prevent transaction ID wraparound.

Do a watch (\watch 10) on the command to see the progress and the play live 🙂 

SELECT datname
    , age(datfrozenxid)
    , current_setting('autovacuum_freeze_max_age') 
FROM pg_database 
ORDER BY 2 DESC;


   datname    |   age   | current_setting
--------------+---------+-----------------
 postgres     | 3058531 | 200000000
 edb          | 3058531 | 200000000
 template1    | 3058531 | 200000000
 template0    | 3058531 | 200000000
 immortal     | 3058531 | 200000000
 darkthrone   | 3058531 | 200000000
 immortal1    | 3058531 | 200000000
 mayhem       | 3058531 | 200000000
 marduk       | 3058531 | 200000000
 burzum       | 3058531 | 200000000
 gorgoroth    | 3058531 | 200000000
 repmgr       | 3058531 | 200000000
 enterprisedb | 3058531 | 200000000
(13 rows)

I have used below query to check where we are going, current XIDs, how close we are to our target of wraparound ..

enterprisedb=# WITH max_age AS ( 
    SELECT 2000000000 as max_old_xid
        , setting AS autovacuum_freeze_max_age 
        FROM pg_catalog.pg_settings 
        WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS ( 
    SELECT datname
        , m.max_old_xid::int
        , m.autovacuum_freeze_max_age::int
        , age(d.datfrozenxid) AS oldest_current_xid 
    FROM pg_catalog.pg_database d 
    JOIN max_age m ON (true) 
    WHERE d.datallowconn ) 
SELECT max(oldest_current_xid) AS oldest_current_xid
    , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
    , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac 
FROM per_database_stats


\watch 10

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          289007513 |                         14 |                               145
(1 row)



 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          290077770 |                         15 |                               145
(1 row)


 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          292467983 |                         15 |                               146
(1 row)


                     Mon 19 Jul 2021 08:38:01 PM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          342714029 |                         17 |                               171
(1 row)

                     Mon 19 Jul 2021 08:38:11 PM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          343223530 |                         17 |                               172
(1 row)


                     Mon 19 Jul 2021 09:17:45 PM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          406185619 |                         20 |                               203
(1 row)


 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          421779698 |                         21 |                               211
(1 row)

                     Mon 19 Jul 2021 09:28:10 PM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          438446236 |                         22 |                               219
(1 row)



                     Mon 19 Jul 2021 11:03:03 PM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          733479765 |                         37 |                               367
(1 row)



 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          763852636 |                         38 |                               382
(1 row)


                     Mon 19 Jul 2021 11:33:03 PM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          826485794 |                         41 |                               413
(1 row)


                     Tue 20 Jul 2021 12:18:07 AM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
          966692697 |                         48 |                               483
(1 row)


 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
         1107823196 |                         55 |                               554
 

                     Tue 20 Jul 2021 01:09:18 AM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
         1125393734 |                         56 |                               563
(1 row)


                     Tue 20 Jul 2021 01:43:11 AM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
         1230984982 |                         62 |                               615
(1 row)





-- LETS SEE WHAT'S HAPPENING ON THE DATABASE 
enterprisedb=# \x
Expanded display is on.
enterprisedb=#
enterprisedb=# select * from pg_stat_activity;


-[ RECORD 4 ]----+---------------------------------
datid            | 16384
datname          | enterprisedb
pid              | 24056
usesysid         | 10
usename          | enterprisedb
application_name | psql.bin
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 20-JUL-21 09:45:44.833016 +05:30
xact_start       | 20-JUL-21 09:45:57.749892 +05:30
query_start      | 20-JUL-21 09:46:01.042977 +05:30
state_change     | 20-JUL-21 09:46:01.047161 +05:30
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 1195
backend_xmin     |
query            | select txid_current();
backend_type     | client backend
-[ RECORD 5 ]----+---------------------------------
datid            | 16384
datname          | enterprisedb
pid              | 25884
usesysid         | 10
usename          | enterprisedb
application_name | psql.bin
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 20-JUL-21 09:47:36.766729 +05:30
xact_start       | 20-JUL-21 09:52:18.941776 +05:30
query_start      | 20-JUL-21 09:52:18.941776 +05:30
state_change     | 20-JUL-21 09:52:18.941778 +05:30
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 1195
query            | select * from pg_stat_activity;
backend_type     | client backend
-[ RECORD 6 ]----+---------------------------------
datid            | 16384
datname          | enterprisedb
pid              | 27150
usesysid         | 10
usename          | enterprisedb
application_name | pgbench
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 20-JUL-21 09:49:56.70284 +05:30
xact_start       | 20-JUL-21 09:52:18.941429 +05:30
query_start      | 20-JUL-21 09:52:18.941429 +05:30
state_change     | 20-JUL-21 09:52:18.94143 +05:30
wait_event_type  | IPC
wait_event       | ClogGroupUpdate
state            | active
backend_xid      | 4526420
backend_xmin     |
query            | select txid_current();
backend_type     | client backend
-[ RECORD 7 ]----+---------------------------------
datid            | 16384
datname          | enterprisedb
pid              | 27151
usesysid         | 10
usename          | enterprisedb
application_name | pgbench
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 20-JUL-21 09:49:56.704062 +05:30
xact_start       |
query_start      | 20-JUL-21 09:52:18.943293 +05:30
state_change     | 20-JUL-21 09:52:18.943411 +05:30
wait_event_type  | Client
wait_event       | ClientRead
state            | idle
backend_xid      |
backend_xmin     |
query            | select txid_current();
backend_type     | client backend
-[ RECORD 8 ]----+---------------------------------
datid            | 16384
datname          | enterprisedb
pid              | 27153
usesysid         | 10
usename          | enterprisedb
application_name | pgbench
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 20-JUL-21 09:49:56.7067 +05:30
xact_start       |
query_start      | 20-JUL-21 09:52:18.943218 +05:30
state_change     | 20-JUL-21 09:52:18.943334 +05:30
wait_event_type  |
wait_event       |
state            | idle
backend_xid      |
backend_xmin     |
query            | select txid_current();
backend_type     | client backend
-[ RECORD 9 ]----+---------------------------------
datid            | 16384
datname          | enterprisedb
pid              | 27154
usesysid         | 10
usename          | enterprisedb
application_name | pgbench
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 20-JUL-21 09:49:56.707948 +05:30
xact_start       | 20-JUL-21 09:52:18.941116 +05:30
query_start      | 20-JUL-21 09:52:18.941116 +05:30
state_change     | 20-JUL-21 09:52:18.941117 +05:30
wait_event_type  | IPC
wait_event       | ClogGroupUpdate
state            | active
backend_xid      | 4526414
backend_xmin     |
query            | select txid_current();
backend_type     | client backend
-[ RECORD 10 ]---+---------------------------------
datid            | 16384
datname          | enterprisedb
pid              | 27152
usesysid         | 10
usename          | enterprisedb
application_name | pgbench
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 20-JUL-21 09:49:56.710155 +05:30
xact_start       | 20-JUL-21 09:52:18.943403 +05:30
query_start      | 20-JUL-21 09:52:18.943403 +05:30
state_change     | 20-JUL-21 09:52:18.943405 +05:30
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     |
query            | select txid_current();
backend_type     | client backend
-[ RECORD 11 ]---+---------------------------------
datid            | 16384
datname          | enterprisedb
pid              | 27155
usesysid         | 10
usename          | enterprisedb
application_name | pgbench
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 20-JUL-21 09:49:56.710785 +05:30
xact_start       | 20-JUL-21 09:52:18.941493 +05:30
query_start      | 20-JUL-21 09:52:18.941493 +05:30
state_change     | 20-JUL-21 09:52:18.941494 +05:30
wait_event_type  | IPC
wait_event       | ClogGroupUpdate
state            | active
backend_xid      | 4526421
backend_xmin     |
query            | select txid_current();
backend_type     | client backend
-[ RECORD 12 ]---+---------------------------------
datid            | 16384
datname          | enterprisedb
pid              | 27156
usesysid         | 10
usename          | enterprisedb
application_name | pgbench
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 20-JUL-21 09:49:56.711762 +05:30
xact_start       |
query_start      | 20-JUL-21 09:52:18.942582 +05:30
state_change     | 20-JUL-21 09:52:18.942704 +05:30
wait_event_type  | Client
wait_event       | ClientRead
state            | idle
backend_xid      |
backend_xmin     |
query            | select txid_current();
backend_type     | client backend
-[ RECORD 13 ]---+---------------------------------
datid            | 16384
datname          | enterprisedb
pid              | 27157
usesysid         | 10
usename          | enterprisedb
application_name | pgbench
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 20-JUL-21 09:49:56.711944 +05:30
xact_start       | 20-JUL-21 09:52:18.943345 +05:30
query_start      | 20-JUL-21 09:52:18.943345 +05:30
state_change     | 20-JUL-21 09:52:18.943346 +05:30
wait_event_type  | Client
wait_event       | ClientRead
state            | active
backend_xid      | 4526447
backend_xmin     | 1195
query            | select txid_current();
backend_type     | client backend
-[ RECORD 14 ]---+---------------------------------

SESSION 4:

Open another session to check log files, if they are reporting any hints, warnings or errors
After ~1 hour of test run, I have started seeing below warnings in the log files.

2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST WARNING:  oldest xmin is far in the past
2021-07-19 20:15:26 IST HINT:  Close open transactions soon to avoid wraparound problems.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST WARNING:  oldest xmin is far in the past
2021-07-19 20:15:26 IST HINT:  Close open transactions soon to avoid wraparound problems.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();
2021-07-19 20:15:26 IST LOG:  statement: select txid_current();

After ~ 3 hours of test run, the warnings becoming more prominent.

-bash-4.2$ grep -Rinw /var/lib/edb/as11/data/log/ -e 'wraparound'
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:17711:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:20705:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:21160:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:21650:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:22179:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:22694:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:23229:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:23776:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:24316:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:24840:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:25391:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:25927:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:27533:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:28135:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:32986:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:33581:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:17711:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:20705:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:21160:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:21650:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:22179:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:22694:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:23229:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:23776:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:24316:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:24840:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:25391:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:25927:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:27533:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:28135:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:32986:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.
/var/lib/edb/as11/data/log/edb-2021-07-19_195300.log:33581:2021-07-19 19:53:01 IST HINT:  Close open transactions soon to avoid wraparound problems.

Now, let’s monitor system stats too to see overall load, processes their commands etc.

top - 00:19:39 up 159 days,  7:11,  6 users,  load average: 10.31, 9.97, 9.95
Tasks: 399 total,   9 running, 390 sleeping,   0 stopped,   0 zombie
%Cpu(s): 60.0 us, 38.4 sy,  0.0 ni,  1.6 id,  0.1 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 32780168 total, 14568928 free,  1259976 used, 16951264 buff/cache
KiB Swap: 16777212 total, 16685044 free,    92168 used. 23083820 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
21831 enterpr+  20   0  591764   5228   2152 S 16.8  0.0 417:57.53 /usr/edb/as11/bin/pgbench -U enterprisedb enterprisedb -f wl.sql -c8 -j8 -T100000 -rn -P+
21845 enterpr+  20   0 8667644  24604  22256 R  9.7  0.1 209:53.07 postgres: enterprisedb enterprisedb [local] SELECT
21840 enterpr+  20   0 8667644  24584  22244 R  9.4  0.1 210:36.23 postgres: enterprisedb enterprisedb [local] idle
21844 enterpr+  20   0 8667644  24876  22528 R  9.4  0.1 209:38.54 postgres: enterprisedb enterprisedb [local] idle
21841 enterpr+  20   0 8667644  24836  22496 R  8.8  0.1 209:15.88 postgres: enterprisedb enterprisedb [local] SELECT
21847 enterpr+  20   0 8667644  24600  22252 S  8.8  0.1 206:16.87 postgres: enterprisedb enterprisedb [local] idle
21846 enterpr+  20   0 8667644  24604  22256 S  8.5  0.1 210:41.59 postgres: enterprisedb enterprisedb [local] idle
20421 enterpr+  20   0  259688   2124    632 R  7.1  0.0 146:27.66 postgres: logger
21843 enterpr+  20   0 8667644  24588  22248 R  6.6  0.1 210:05.38 postgres: enterprisedb enterprisedb [local] idle
21842 enterpr+  20   0 8667644  24600  22260 R  6.6  0.1 209:48.66 postgres: enterprisedb enterprisedb [local] idle
20426 enterpr+  20   0 8666684  18736  17208 R  4.0  0.1 102:43.53 postgres: walwriter
 6858 polkitd   20   0  620236  18044   4428 S  0.9  0.1  15940:23 /usr/lib/polkit-1/polkitd --no-debug

SESSION 2:

To my surprise, after ~8 hours of test run, the session screen where I have pgbench running, started throwing some WARNINGs to perform vacuum within specified number of transactions to avoid database shutdown. It kept on throwing those direct warnings for almost an hour before it stopped processing pgbench requests and started aborting workers with a message ‘ERROR: database is not accepting commands to avoid wraparound data loss in database “postgres”.

In the end it will present you the overall pgbench stats where it fired 2146483646 statements in total.

WARNING:  database "postgres" must be vacuumed within 5092920 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING:  database "postgres" must be vacuumed within 5092920 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING:  database "postgres" must be vacuumed within 5092917 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
....
......
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING:  database "postgres" must be vacuumed within 3178805 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING:  database "postgres" must be vacuumed within 3178808 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING:  database "postgres" must be vacuumed within 3178804 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING:  database "postgres" must be vacuumed within 3178803 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING:  database "postgres" must be vacuumed within 3178801 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING:  database "postgres" must be vacuumed within 3178799 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
......
........
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING:  database "postgres" must be vacuumed within 1000002 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
WARNING:  database "postgres" must be vacuumed within 1000002 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
client 0 aborted in command 0 (SQL) of script 0; ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

client 1 aborted in command 0 (SQL) of script 0; ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

client 7 aborted in command 0 (SQL) of script 0; ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

client 6 aborted in command 0 (SQL) of script 0; ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

client 2 aborted in command 0 (SQL) of script 0; ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

client 4 aborted in command 0 (SQL) of script 0; ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

WARNING:  database "postgres" must be vacuumed within 1000005 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
client 3 aborted in command 0 (SQL) of script 0; ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

client 5 aborted in command 0 (SQL) of script 0; ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

transaction type: wl.sql
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 8
duration: 100000 s
number of transactions actually processed: 2146483646
latency average = 0.159 ms
latency stddev = 1.102 ms
tps = 50437.366776 (including connections establishing)
tps = 50437.386645 (excluding connections establishing)
statement latencies in milliseconds:
         0.171  select txid_current();
You have new mail in /var/spool/mail/enterprisedb
-bash-4.2$

SESSION 3:

Lets check our screen 3 to see where we are in terms on oldest current XID and percent towards wraparound, and I see XID numbers are not increasing and we have reached 107% towards wraparound (strange, why it goes beyond 100%).

enterprisedb=# WITH max_age AS ( 
    SELECT 2000000000 as max_old_xid
        , setting AS autovacuum_freeze_max_age 
        FROM pg_catalog.pg_settings 
        WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS ( 
    SELECT datname
        , m.max_old_xid::int
        , m.autovacuum_freeze_max_age::int
        , age(d.datfrozenxid) AS oldest_current_xid 
    FROM pg_catalog.pg_database d 
    JOIN max_age m ON (true) 
    WHERE d.datallowconn ) 
SELECT max(oldest_current_xid) AS oldest_current_xid
    , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
    , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac 
FROM per_database_stats;

(1 row)
                     Tue 20 Jul 2021 06:57:51 AM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
         2146483647 |                        107 |                              1073
(1 row)


                     Tue 20 Jul 2021 06:58:01 AM IST (every 10s)

 oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac
--------------------+----------------------------+-----------------------------------
         2146483647 |                        107 |                              1073
(1 row)




enterprisedb=# SELECT datname
, age(datfrozenxid)
, current_setting('autovacuum_freeze_max_age')
FROM pg_database
 ORDER BY 2 DESC;

   datname    |    age     | current_setting
--------------+------------+-----------------
 postgres     | 2146483647 | 200000000
 edb          | 2146483647 | 200000000
 template1    | 2146483647 | 200000000
 template0    | 2146483647 | 200000000
 immortal     | 2146483647 | 200000000
 darkthrone   | 2146483647 | 200000000
 immortal1    | 2146483647 | 200000000
 mayhem       | 2146483647 | 200000000
 murduk       | 2146483647 | 200000000
 burzum       | 2146483647 | 200000000
 gorgoroth    | 2146483647 | 200000000
 repmgr       | 2146483647 | 200000000
 enterprisedb | 2146483647 | 200000000
(13 rows)

Let’s connect with the database and see what happens when we try to do both SELECT & UPDATE statements …

enterprisedb=# \dt+
                            List of relations
 Schema |       Name       | Type  |    Owner     |  Size   | Description
--------+------------------+-------+--------------+---------+-------------
 public | pgbench_accounts | table | enterprisedb | 13 MB   |
 public | pgbench_branches | table | enterprisedb | 40 kB   |
 public | pgbench_history  | table | enterprisedb | 40 kB   |
 public | pgbench_tellers  | table | enterprisedb | 40 kB   |
 public | scale_data       | table | enterprisedb | 6720 MB |
(5 rows)

enterprisedb=# select * from pgbench_history;
 tid | bid |  aid  | delta |           mtime           | filler
-----+-----+-------+-------+---------------------------+--------
   2 |   1 | 64444 | -1096 | 19-JUL-21 18:41:15.655738 |
   3 |   1 | 18460 | -4688 | 19-JUL-21 18:41:15.668699 |
   2 |   1 | 92762 | -4669 | 19-JUL-21 18:41:15.670741 |
   2 |   1 | 55081 |  1817 | 19-JUL-21 18:41:15.672719 |
   5 |   1 | 47743 |   803 | 19-JUL-21 18:41:15.674666 |
   5 |   1 | 23899 | -3910 | 19-JUL-21 18:41:15.67664  |
  10 |   1 | 33384 |  4005 | 19-JUL-21 18:41:15.678631 |
   6 |   1 | 88383 | -4257 | 19-JUL-21 18:41:15.680629 |
   8 |   1 | 87562 |   200 | 19-JUL-21 18:41:15.682612 |
   5 |   1 |  3957 | -2407 | 19-JUL-21 18:41:15.684595 |
(10 rows)

enterprisedb=#
enterprisedb=# update pgbench_history set aid=65555 where aid=64444;
ERROR:  database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
enterprisedb=#
enterprisedb=#
enterprisedb=#

Alright, so with transaction wraparound is already there, we only be able to run SELECT queries, but DMLS will fail with a message printed to stop the database and vacuum it in single-user mode.

So, what we have learned

  • Database will keep on running and accepting READ ONLY (SELECT) requests, error will be thrown in case if DMLs tried.
  • 2,000,000,000 seems a huge number, but in case of busy transactional database, it’s just a matter of few hours.
  • AUTOVACUUMING is good thing, at least for this scenario, let’s not discuss about its bugs and other problems.
  • Add VACUUMING a routine activity else you will soon find yourself in a big mess.

The easiest way to get the highest TXID age value back down is to force a vacuum on the entire database cluster. And the best way to do this cluster-wide vacuum is the vacuumdb binary utility that comes with PostgreSQL. I will cover it in my next post as this post is all about simulating wraparounds.

Hope It Helped!
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , | 6 Comments »

pgCenter, a TOP like utility to monitor PostgreSQL databases

Posted by FatDBA on July 12, 2021

Hi All,

I was recently asked by one of my team mate to recommend any performance monitoring tool which is free, text based, light weight and is quick to run to check what all is running on the PostgreSQL database. He wanted to use it for one of the performance drive he was leading for one production database setup. The guy was coming from Oracle background, and was looking something like Linux TOP utility or like oratop (like top for oracle database) …

After carefully understanding his requirements, I recommended him to use pgCenter. This is an open source project by Lesovsky Alexey (the man behind pgstats.dev and Weaponry) and is a command-line admin tool for observing and troubleshooting Postgres database. I would have recommended pg_top to him, but pgCenter is one step ahead and better as it provide more details as compared to the later.

pgCenter’s main goal is to help Postgres DBA working with statistics and provide a convenient way to observe Postgres in runtime. Tool is capable to provide replication, table level, Index, activity, table size, database (overall), and function statistics. It also shows details on VACCUM, NIC & Disk stats along with OS level stats of CPU, Memory, load averages, connection and server details.

It’s pretty easy to call or install, please refer below project GitHub link to download and follow install actions. https://github.com/lesovsky/pgcenter

So, let’s see what all it monitors and catches.

[enterprisedb@fatdba ~]$
[enterprisedb@fatdba ~]$ pgcenter top -d perf -h 10.0.0.130 -U enterprisedb  -p 5444

A GIF (from project page on github) to show it in real-time, what all you can do with the tool.

Stats it supports …

PostgreSQL Stats it supports:

  • summary activity – a compilation/selection of metrics from different sources – postgres uptime, version, recovery status, number of clients grouped by their states, number of (auto)vacuums, statements per second, age of the longest transaction and the longest vacuum;
  • pg_stat_activity – activity of connected clients and background processes.
  • pg_stat_database – database-wide and sessions statistics, such as number of commits/rollbacks, processed tuples, deadlocks, temporary files, etc.
  • pg_stat_replication – replication statistics, like connected standbys, their activity and replication lag.
  • pg_stat_user_tables, pg_statio_user_tables – statistics on accesses (including IO) to tables.
  • pg_stat_user_indexes, pg_statio_user_indexes – statistics on accesses (including IO) to indexes.
  • pg_stat_user_functions – statistics on execution of functions.
  • pg_stat_wal – WAL usage statistics.
  • pg_stat_statements – statistics on SQL statements executed including time and resources usage.
  • statistics on tables sizes based on pg_relation_size() and pg_total_relation_size() functions;
  • pg_stat_progress_vacuum – progress of (auto)vacuums operations.
  • pg_stat_progress_cluster – progress of CLUSTER and VACUUM FULL operations.
  • pg_stat_progress_create_index – progress of CREATE INDEX and REINDEX operations.
  • pg_stat_progress_analyze – progress of ANALYZE operations.
  • pg_stat_progress_basebackup – progress of basebackup operations.
  • pg_stat_progress_copy – progress of COPY operations.

System Statistics it supports :

pgCenter top also provides system usage information based on statistics from procfs filesystem:

  • load average and CPU usage time (user, system, nice, idle, iowait, software, and hardware interrupts, steal);
  • memory and swap usage, amount of cached and dirty memory, writeback activity;
  • storage devices statistics: IOPS, throughput, latencies, average queue and requests size, devices utilization;
  • network interfaces statistics: throughput in bytes and packets, different kind of errors, saturation and utilization.
  • mounted filesystems’ usage statistics: total size, amount of free/used/reserved space and inodes.

Hope It Helps!
Prashant Dixit

Posted in troubleshooting | Tagged: , , , | Leave a Comment »

New names to RecoveryWalAll and RecoveryWalStream wait events in PostgreSQL 13

Posted by FatDBA on July 9, 2021

Hi Guys,

Recently while working on PostgreSQL Version 13 I have identified few new changes related with the wait events, particularly related with recovery in order to make the names and what they are more consistent.

For all who are new to events, they can tell you a lot about query and system performance, specially scenarios like .. why a query isn’t making progress ? – One example of a wait event may be IO / DataFileRead, which points that a process is currently waiting for data to be read from disk or the page cache.

So, talking about those waits, first one is event ‘RecoveryWalAll‘ which tells recovery is waiting for WAL from a stream, that could be from any kind of source i.e. local, archive or stream, is now changed to ‘RecoveryWalStream’.

Another one is ‘RecoveryWalStream‘ which can help you to make sure that each standby does not have more WAL to replay or helps to identify what is waiting for WAL from a stream at recovery, is now changed/renamed to RecoveryRetrieveRetryInterval. I have also noticed a change in event class or category of RecoveryRetrieveRetryInterval to Timeout from Activity.

Hope It Helps
Prashant Dixit

Posted in Advanced, troubleshooting | Tagged: , , | Leave a Comment »

Migration assessment report, possible with ora2pg ?

Posted by FatDBA on July 3, 2021

Hi Guys,

Last week someone asked if me if it’s possible to generate the migration assessment report (of any sort) if using ora2pg for migration ? – The entire team was coming from EDB PostgreSQL migration where they used their migration toolkit, and there you can generate something similar, but with their current project, ora2pg was finalized as a migration tool for Oracle to vanilla PostgreSQL migration.

And the answer to their question was YES, it’s possible to generate the migration assessment report with ora2pg too, which is very useful as it performs an assessment for the potential migration problems or difficulties. It allocates a cost per object types and also provides other details like invalid object counts, total number of object types, comments and more description about objects etc.

It inspects all database objects , functions, stored procedures to detect if there are some objects or PL/SQL code that cannot be automatically converted by the tool. At the end of the report it calculates the overall cost (sum of all costs) and assigns an approximate number of days or time to perform the migration.

Default time per “cost evaluation unit” is 5 minutes, and outputs can be generated in text (default), html or csv formats.

Let’s generate a sample report, but first let me show you the version of ora2pg, and next will call the report for Oracle database running on 18c for a particular schema.

$
$ ora2pg -t SHOW_VERSION -c config/ora2pg.conf
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0


-- Here we used 'estimate_cost' is to activate the migration cost evaluation with SHOW_REPORT

$ ora2pg -t show_report  --estimate_cost -c config/ora2pg.conf --dump_as_html > /tmp/orapgtestmigr/ora2pgreport.html

-------------------------------------------------------------------------------
Ora2Pg v20.0 - Database Migration Report
-------------------------------------------------------------------------------
Version  Oracle Database 18c Enterprise Edition Release 18.0.0.0.0
Schema   DIXITSCH
Size     88.19 MB

Below is the screen snip of the report head.

Please click below link to view the full report.

ora2pg-database-migration-reportDownload

Hope It Helped!
Prashant Dixit

Posted in Advanced | Tagged: , , , | Leave a Comment »

PgBackRest: A reliable backup and recovery solution to your PostgreSQL clusters …

Posted by FatDBA on April 8, 2021

Hi Everyone,

Recently while working for one of the customer, I was asked to propose a reliable backup and recovery solution for the database clusters. The customer was using both EDB and open source PostgreSQL. The ask is to take all major types of backups i.e. Full, Incremental and Differential, and last two types is to cover their anticipation for ways to decrease amount of time and disk space usage to take a full backup. After considering all their prerequisites and necessities, I came up with the idea of using PgBackRest which I have tested in some of my previous assignments too. PgBackRest is an open source backup tool that creates physical backups with some improvements compared to the classic pg_basebackup tool.

It comes up with lot of cool features which otherwise isn’t possible with pg_basebackup and few of them not even with other backup tools. Features like parallel backups, encryption, differential and incremental backups, backup integrity checks, archive expiration policies, local and remote operations, backup resume etc.

This post is all about this popular backup tool PgBackRest, how to configure and how to perform backup and restore operations using the tool. I will be doing few test cases here on my personal lab where I have RHEL and have PostgreSQL 12 installed.

I have already installed the tool using PostgreSQL YUM repository. It’s pretty straight forward, do ‘yum install pgbackrest‘ and that’s it!

-bash-4.2$ which pgbackrest
/usr/bin/pgbackrest

Let’s checked the version.

-bash-4.2$ pgbackrest version
pgBackRest 2.32

Now when the tool is installed and working fine, time to configure its core property file or config (pgbackrest.conf) file. I will be first creating a new directory to house this core confgiuration file for the tool.

[root@canttowin ~]# mkdir /etc/pgbackrest
[root@canttowin ~]# vi /etc/pgbackrest/pgbackrest.conf

Lets add global and local database details to the configuration file. Here I am setting full backup retention of 2 days (repo1-retention-full=2), I am only passing required set of params to it, else there is a huge list of them which you can use and defined under config file.

[root@canttowin ~]# more /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2

[openpgsql]
pg1-path=/var/lib/pgsql/12/data/
pg1-port=5432

If you have noticed, all operations above I have performed with root account/user, and this should not be the case, ownership should be passed to PostgreSQL database owner, that is ‘postgres’ user in my case. So, let’s fix permissions first before we do our first backup.

[root@canttowin ~]# chmod 0750 /var/lib/pgbackrest
[root@canttowin ~]# chown -R postgres:postgres /var/lib/pgbackrest
[root@canttowin ~]# ls -ll /var/log/pgbackrest/
total 8
-rw-r-----. 1 root root 0 Apr 4 04:23 all-start.log
-rw-r----- 1 root root 185 Mar 27 05:37 all-start.log.1.gz
-rw-r----- 1 postgres postgres 450 Apr 6 00:54 openpgsql-stanza-create.log
[root@canttowin ~]#
[root@canttowin ~]# chown -R postgres:postgres /var/log/pgbackrest
[root@canttowin ~]#
[root@canttowin ~]# ls -ll /var/log/pgbackrest/
total 8
-rw-r-----. 1 postgres postgres 0 Apr 4 04:23 all-start.log
-rw-r----- 1 postgres postgres 185 Mar 27 05:37 all-start.log.1.gz
-rw-r----- 1 postgres postgres 450 Apr 6 00:54 openpgsql-stanza-create.log

All set with the permissions, now next is to set few of the parameters within postgresql.conf file to make pgbackrest handle WAL segments, pushing them immediately to archive.

[postgres@canttowin data]$
[postgres@canttowin data]$ more /var/lib/pgsql/12/data/postgresql.conf |grep archive
archive_mode = on
archive_command = 'pgbackrest --stanza=openpgsql archive-push %p'

Above changes to database configuration requires a restart of the database, so, let’s do it.

[postgres@canttowin bin]$ ./pg_ctl -D /var/lib/pgsql/12/data stop
waiting for server to shut down…. done
server stopped
[postgres@canttowin bin]$ ./pg_ctl -D /var/lib/pgsql/12/data start
waiting for server to start….2021-04-06 01:03:45.837 EDT [28770] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-06 01:03:45.838 EDT [28770] LOG: listening on IPv6 address "::1", port 5432
2021-04-06 01:03:45.838 EDT [28770] LOG: listening on IPv4 address "127.0.0.1", port 5432
2021-04-06 01:03:45.861 EDT [28770] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-04-06 01:03:45.911 EDT [28770] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-04-06 01:03:45.983 EDT [28770] LOG: redirecting log output to logging collector process
2021-04-06 01:03:45.983 EDT [28770] HINT: Future log output will appear in directory "log".
done
server started

Next is to create the ‘STANZA‘, A stanza defines the backup configuration for a specific PostgreSQL database cluster.

[postgres@canttowin ~]$ pgbackrest stanza-create --stanza=openpgsql --log-level-console=info
2021-04-06 00:54:31.731 P00 INFO: stanza-create command begin 2.32: --exec-id=24839-da2916aa --log-level-console=info --pg1-path=/var/lib/pgsql/12/data --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --stanza=openpgsql
2021-04-06 00:54:32.361 P00 INFO: stanza-create for stanza 'openpgsql' on repo1
2021-04-06 00:54:32.400 P00 INFO: stanza-create command end: completed successfully (672ms)

Next, we will check if everything is okay, this ‘check‘ command will check the cluster and validates archive_command and other related settings, if no error, then it’s all good.
[postgres@canttowin bin]$ pgbackrest --stanza=openpgsql check --log-level-console=info
2021-04-06 01:07:18.941 P00 INFO: check command begin 2.32: --exec-id=30501-dbf76c75 --log-level-console=info --pg1-path=/var/lib/pgsql/12/data --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --stanza=openpgsql
2021-04-06 01:07:19.553 P00 INFO: check repo1 configuration (primary)
2021-04-06 01:07:19.778 P00 INFO: check repo1 archive for WAL (primary)
2021-04-06 01:07:20.196 P00 INFO: WAL segment 000000010000000000000057 successfully archived to '/var/lib/pgbackrest/archive/openpgsql/12-1/0000000100000000/000000010000000000000057-dd44b724c7a9e257512f5c9d3ecf5d87f7ae9f67.gz' on repo1
2021-04-06 01:07:20.197 P00 INFO: check command end: completed successfully (1258ms)

All good, time to take our first backup, we have to use ‘type‘ argument with values full, incr, diff for all three types of backups. As this is our first backup, if you go and try for diff and incr backups, they will still go for the full backup as they requires a base backup to consider.
Below are the runtime logs, I have used option ‘log-level-console=info‘ which prints log information, warnings and errors, other possible values with this parameter are off, error, warn, detail, debug and trace.

[postgres@canttowin bin]$ pgbackrest --stanza=openpgsql --type=full backup --log-level-console=info
2021-04-06 01:07:49.917 P00 INFO: backup command begin 2.32: --exec-id=30602-14142f51 --log-level-console=info --pg1-path=/var/lib/pgsql/12/data --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=openpgsql --type=full
2021-04-06 01:07:50.646 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes
2021-04-06 01:07:51.362 P00 INFO: backup start archive = 000000010000000000000059, lsn = 0/59000060
2021-04-06 01:07:53.028 P01 INFO: backup file /var/lib/pgsql/12/data/base/14188/16415 (13.5MB, 22%) checksum d8deb3703748d22554be2fb29c0ed105bab9658c
2021-04-06 01:07:53.782 P01 INFO: backup file /var/lib/pgsql/12/data/base/14188/16426 (5MB, 30%) checksum 29a07de6e53a110380ef984d3effca334a07d6e6
2021-04-06 01:07:54.176 P01 INFO: backup file /var/lib/pgsql/12/data/base/14188/16423 (2.2MB, 33%) checksum 5184ac361b2bef0df25a34e91636a085fc526930
2021-04-06 01:07:54.222 P01 INFO: backup file /var/lib/pgsql/12/data/base/16385/1255 (632KB, 34%) checksum edd483d42330ae26a455b3ee40e5c2b41cb298d5
2021-04-06 01:07:54.334 P01 INFO: backup file /var/lib/pgsql/12/data/base/16384/1255 (632KB, 35%) checksum edd483d42330ae26a455b3ee40e5c2b41cb298d5
2021-04-06 01:07:54.434 P01 INFO: backup file /var/lib/pgsql/12/data/base/14188/1255 (632KB, 36%) checksum edd483d42330ae26a455b3ee40e5c2b41cb298d5
….
……
2021-04-06 01:08:05.364 P01 INFO: backup file /var/lib/pgsql/12/data/PG_VERSION (3B, 100%) checksum ad552e6dc057d1d825bf49df79d6b98eba846ebe
2021-04-06 01:08:05.369 P01 INFO: backup file /var/lib/pgsql/12/data/global/6100 (0B, 100%)
2021-04-06 01:08:05.372 P01 INFO: backup file /var/lib/pgsql/12/data/global/6000 (0B, 100%)
2021-04-06 01:08:05.376 P01 INFO: backup file /var/lib/pgsql/12/data/global/4185 (0B, 100%)
2021-04-06 01:08:05.379 P01 INFO: backup file /var/lib/pgsql/12/data/global/4183 (0B, 100%)
2021-04-06 01:08:05.390 P01 INFO: backup file /var/lib/pgsql/12/data/global/4181 (0B, 100%)
….
…..
2021-04-06 01:08:06.735 P01 INFO: backup file /var/lib/pgsql/12/data/base/1/14040 (0B, 100%)
2021-04-06 01:08:06.738 P01 INFO: backup file /var/lib/pgsql/12/data/base/1/14035 (0B, 100%)
2021-04-06 01:08:06.743 P01 INFO: backup file /var/lib/pgsql/12/data/base/1/14030 (0B, 100%)
2021-04-06 01:08:06.847 P01 INFO: backup file /var/lib/pgsql/12/data/base/1/14025 (0B, 100%)
2021-04-06 01:08:06.848 P00 INFO: full backup size = 61MB
2021-04-06 01:08:06.848 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-04-06 01:08:07.068 P00 INFO: backup stop archive = 000000010000000000000059, lsn = 0/59000170
2021-04-06 01:08:07.107 P00 INFO: check archive for segment(s) 000000010000000000000059:000000010000000000000059
2021-04-06 01:08:07.354 P00 INFO: new backup label = 20210406-010750F
2021-04-06 01:08:07.489 P00 INFO: backup command end: completed successfully (17575ms)
2021-04-06 01:08:07.489 P00 INFO: expire command begin 2.32: --exec-id=30602-14142f51 --log-level-console=info --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=openpgsql
2021-04-06 01:08:07.500 P00 INFO: expire command end: completed successfully (11ms)
[postgres@canttowin bin]$

So, our first backup is done. Now, let’s check it’s detail (size, timings, WAL details etc.).

[postgres@canttowin bin]$ pgbackrest info
stanza: openpgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (12): 000000010000000000000056/000000010000000000000059

        full backup: 20210406-010650F
            timestamp start/stop: 2021-04-06 01:06:50 / 2021-04-06 01:07:12
            wal start/stop: 000000010000000000000056 / 000000010000000000000056
            database size: 61MB, database backup size: 61MB
            repo1: backup set size: 8.0MB, backup size: 8.0MB

When we have our first full backup ready, let’s take the differential backup.

[postgres@canttowin ~]$ pgbackrest --stanza=openpgsql --type=diff --log-level-console=info backup
2021-04-06 14:40:34.145 P00 INFO: backup command begin 2.32: --exec-id=54680-0dd25993 --log-level-console=info --pg1-path=/var/lib/pgsql/12/data --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=openpgsql --type=diff
2021-04-06 14:40:34.892 P00 INFO: last backup label = 20210406-143757F, version = 2.32
2021-04-06 14:40:34.892 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes
2021-04-06 14:40:35.405 P00 INFO: backup start archive = 00000001000000000000005F, lsn = 0/5F000028
2021-04-06 14:40:36.252 P01 INFO: backup file /var/lib/pgsql/12/data/global/pg_control (8KB, 99%) checksum 962d11b5c25154c5c8141095be417a7f5d699419
2021-04-06 14:40:36.354 P01 INFO: backup file /var/lib/pgsql/12/data/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2021-04-06 14:40:36.355 P00 INFO: diff backup size = 8KB
2021-04-06 14:40:36.355 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-04-06 14:40:36.568 P00 INFO: backup stop archive = 00000001000000000000005F, lsn = 0/5F000100
2021-04-06 14:40:36.573 P00 INFO: check archive for segment(s) 00000001000000000000005F:00000001000000000000005F
2021-04-06 14:40:36.615 P00 INFO: new backup label = 20210406-143757F_20210406-144034D
2021-04-06 14:40:36.672 P00 INFO: backup command end: completed successfully (2528ms)
2021-04-06 14:40:36.672 P00 INFO: expire command begin 2.32: --exec-id=54680-0dd25993 --log-level-console=info --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=openpgsql
2021-04-06 14:40:36.678 P00 INFO: expire command end: completed successfully (6ms)

[postgres@canttowin ~]$ pgbackrest info
stanza: openpgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (12): 00000001000000000000005B/00000001000000000000005F

        full backup: 20210406-143652F
            timestamp start/stop: 2021-04-06 14:36:52 / 2021-04-06 14:37:10
            wal start/stop: 00000001000000000000005B / 00000001000000000000005B
            database size: 61MB, database backup size: 61MB
            repo1: backup set size: 8.0MB, backup size: 8.0MB

        diff backup: 20210406-143757F_20210406-144034D
            timestamp start/stop: 2021-04-06 14:40:34 / 2021-04-06 14:40:36
            wal start/stop: 00000001000000000000005F / 00000001000000000000005F
            database size: 61MB, database backup size: 8.3KB
            repo1: backup set size: 8.0MB, backup size: 431B
            backup reference list: 20210406-143757F

The ‘info’ command output can be printed in JSON format too, just like below.

[postgres@canttowin ~]$ pgbackrest info --output=json
[{"archive":[{"database":{"id":1,"repo-key":1},"id":"12-1","max":"00000001000000000000005F","min":"00000001000000000000005B"}],"backup":[{"archive":{"start":"00000001000000000000005B","stop":"00000001000000000000005B"},"backrest":{"format":5,"version":"2.32"},"database":{"id":1,"repo-key":1},"info":{"delta":64047301,"repository":{"delta":8380156,"size":8380156},"size":64047301},"label":"20210406-143652F","prior":null,"reference":null,"timestamp":{"start":1617734212,"stop":1617734230},"type":"full"},{"archive":{"start":"00000001000000000000005D","stop":"00000001000000000000005D"},"backrest":{"format":5,"version":"2.32"},"database":{"id":1,"repo-key":1},"info":{"delta":64047301,"repository":{"delta":8380155,"size":8380155},"size":64047301},"label":"20210406-143757F","prior":null,"reference":null,"timestamp":{"start":1617734277,"stop":1617734285},"type":"full"},{"archive":{"start":"00000001000000000000005F","stop":"00000001000000000000005F"},"backrest":{"format":5,"version":"2.32"},"database":{"id":1,"repo-key":1},"info":{"delta":8459,"repository":{"delta":431,"size":8380156},"size":64047301},"label":"20210406-143757F_20210406-144034D","prior":"20210406-143757F","reference":["20210406-143757F"],"timestamp":{"start":1617734434,"stop":1617734436},"type":"diff"}],"cipher":"none","db":[{"id":1,"repo-key":1,"system-id":6941966298907810297,"version":"12"}],"name":"openpgsql","repo":[{"cipher":"none","key":1,"status":{"code":0,"message":"ok"}}],"status":{"code":0,"lock":{"backup":{"held":false}},"message":"ok"}}][postgres@canttowin ~]$
[postgres@canttowin ~]$

Now next comes the incremental backup, let’s do it!

[postgres@canttowin ~]$ pgbackrest --stanza=openpgsql --type=incr --log-level-console=info backup
2021-04-06 14:43:26.193 P00 INFO: backup command begin 2.32: --exec-id=55204-d310aa59 --log-level-console=info --pg1-path=/var/lib/pgsql/12/data --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=openpgsql --type=incr
2021-04-06 14:43:26.976 P00 INFO: last backup label = 20210406-143757F_20210406-144034D, version = 2.32
2021-04-06 14:43:26.976 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes
2021-04-06 14:43:27.495 P00 INFO: backup start archive = 000000010000000000000061, lsn = 0/61000028
2021-04-06 14:43:28.266 P01 INFO: backup file /var/lib/pgsql/12/data/global/pg_control (8KB, 99%) checksum 92143d43c90ed770f99f722d734bec62d9413d2a
2021-04-06 14:43:28.369 P01 INFO: backup file /var/lib/pgsql/12/data/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2021-04-06 14:43:28.369 P00 INFO: incr backup size = 8KB
2021-04-06 14:43:28.369 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-04-06 14:43:28.872 P00 INFO: backup stop archive = 000000010000000000000061, lsn = 0/61000100
2021-04-06 14:43:28.874 P00 INFO: check archive for segment(s) 000000010000000000000061:000000010000000000000061
2021-04-06 14:43:28.915 P00 INFO: new backup label = 20210406-143757F_20210406-144326I
2021-04-06 14:43:28.977 P00 INFO: backup command end: completed successfully (2785ms)
2021-04-06 14:43:28.977 P00 INFO: expire command begin 2.32: --exec-id=55204-d310aa59 --log-level-console=info --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=openpgsql
2021-04-06 14:43:28.981 P00 INFO: expire command end: completed successfully (4ms)
[postgres@canttowin ~]$
[postgres@canttowin ~]$

[postgres@canttowin ~]$ pgbackrest info
stanza: openpgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (12): 00000001000000000000005B/000000010000000000000061

        full backup: 20210406-143652F
            timestamp start/stop: 2021-04-06 14:36:52 / 2021-04-06 14:37:10
            wal start/stop: 00000001000000000000005B / 00000001000000000000005B
            database size: 61MB, database backup size: 61MB
            repo1: backup set size: 8.0MB, backup size: 8.0MB

        diff backup: 20210406-143757F_20210406-144034D
            timestamp start/stop: 2021-04-06 14:40:34 / 2021-04-06 14:40:36
            wal start/stop: 00000001000000000000005F / 00000001000000000000005F
            database size: 61MB, database backup size: 8.3KB
            repo1: backup set size: 8.0MB, backup size: 431B
            backup reference list: 20210406-143757F

        incr backup: 20210406-143757F_20210406-144326I
            timestamp start/stop: 2021-04-06 14:43:26 / 2021-04-06 14:43:28
            wal start/stop: 000000010000000000000061 / 000000010000000000000061
            database size: 61MB, database backup size: 8.3KB
            repo1: backup set size: 8.0MB, backup size: 430B
            backup reference list: 20210406-143757F

So, that’s how you can take all three types of backup using the tool, if you want to scchedule it, you can use CRON and do entries something like below.

[postgres@canttowin ~]$ crontab -l
#m h   dom mon dow   command
30 06  *   *   0     pgbackrest --type=full --stanza=openpgsql backup
30 06  *   *   1-6   pgbackrest --type=diff --stanza=openpgsql backup

There are some other cool options which you can use with your backup command directly or even mention them in configuration file. There is a long list of parameters which you can use, click here to know about them. Few of them which are very useful are discussed below.

start-fast : Force a checkpoint to start backup quickly.
compress: Use file compression
compress-level: To declare compression levels
repo1-retention-diff: For differential backup retention.

Now, let’s create a recovery scenario. I am going to delete the entire DATA directory from PG HOME and will restore it using backups that we have. This being a brand new cluster, let me create some sample data.

dixit=#
dixit=# CREATE TABLE COMPANY(
dixit(# ID INT PRIMARY KEY NOT NULL,
dixit(# NAME TEXT NOT NULL,
dixit(# AGE INT NOT NULL,
dixit(# ADDRESS CHAR(50),
dixit(# SALARY REAL,
dixit(# JOIN_DATE DATE
dixit(# );
CREATE TABLE
dixit=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT 0 1
dixit=#
dixit=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');
INSERT 0 1
dixit=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );
INSERT 0 1
dixit=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
INSERT 0 2
dixit=#
dixit=# select * from COMPANY;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
1 | Paul | 32 | California | 20000 | 2001-07-13
2 | Allen | 25 | Texas | | 2007-12-13
3 | Teddy | 23 | Norway | 20000 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
(5 rows)

And now when we have made above changes and we have the full database backup taken before than that, we have to take an incremental backup to cover new changes.

[postgres@canttowin ~]$ pgbackrest --stanza=openpgsql --type=incr backup --log-level-console=info
2021-04-06 23:12:18.008 P00 INFO: backup command begin 2.32: --exec-id=80088-57a7eed8 --log-level-console=info --pg1-path=/var/lib/pgsql/12/data --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --repo1-retention-diff=2 -–repo1-retention-full=1 --stanza=openpgsql --start-fast --type=incr
2021-04-06 23:12:18.744 P00 INFO: last backup label = 20210406-225743F_20210406-231110I, version = 2.32
2021-04-06 23:12:18.744 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-04-06 23:12:19.256 P00 INFO: backup start archive = 00000001000000000000006C, lsn = 0/6C000028
2021-04-06 23:12:20.245 P01 INFO: backup file /var/lib/pgsql/12/data/base/16384/1249 (440KB, 90%) checksum b85efa460cab148bf9d7db5a3e78dba71cc5b0b2
2021-04-06 23:12:20.247 P01 INFO: backup file /var/lib/pgsql/12/data/base/16384/2610 (32KB, 96%) checksum c6331e9df78c639a6b04aed46ecc96bd09f170f6
2021-04-06 23:12:20.250 P01 INFO: backup file /var/lib/pgsql/12/data/global/pg_control (8KB, 98%) checksum e75e69d389d82b2bc9bee88aea6353d3d889c28e
2021-04-06 23:12:20.252 P01 INFO: backup file /var/lib/pgsql/12/data/base/16384/2606 (8KB, 99%) checksum 59284824f0a0cd49006d5c220941248b13c2b286
2021-04-06 23:12:20.355 P01 INFO: backup file /var/lib/pgsql/12/data/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2021-04-06 23:12:20.355 P00 INFO: incr backup size = 488KB
2021-04-06 23:12:20.355 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-04-06 23:12:20.558 P00 INFO: backup stop archive = 00000001000000000000006C, lsn = 0/6C000138
2021-04-06 23:12:20.561 P00 INFO: check archive for segment(s) 00000001000000000000006C:00000001000000000000006C
2021-04-06 23:12:20.591 P00 INFO: new backup label = 20210406-225743F_20210406-231218I
2021-04-06 23:12:20.643 P00 INFO: backup command end: completed successfully (2636ms)
2021-04-06 23:12:20.643 P00 INFO: expire command begin 2.32: --exec-id=80088-57a7eed8 --log-level-console=info --repo1-path=/var/lib/pgbackrest --repo1-retention-diff=2 --repo1-retention-full=1 --stanza=openpgsql
2021-04-06 23:12:20.649 P00 INFO: expire command end: completed successfully (6ms)
[postgres@canttowin ~]$

[postgres@canttowin ~]$ pgbackrest info
stanza: openpgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (12): 000000010000000000000068/00000001000000000000006C

        full backup: 20210406-225743F
            timestamp start/stop: 2021-04-06 22:57:43 / 2021-04-06 22:58:01
            wal start/stop: 000000010000000000000068 / 000000010000000000000068
            database size: 61MB, database backup size: 61MB
            repo1: backup set size: 8.0MB, backup size: 8.0MB

        incr backup: 20210406-225743F_20210406-231110I
            timestamp start/stop: 2021-04-06 23:11:10 / 2021-04-06 23:11:12
            wal start/stop: 00000001000000000000006A / 00000001000000000000006A
            database size: 61.2MB, database backup size: 2.4MB
            repo1: backup set size: 8.0MB, backup size: 239.5KB
            backup reference list: 20210406-225743F

To know more about any of the database backup, we can use option ‘set‘ where we use backup name with ‘info‘ command, just like below.

[postgres@canttowin ~]$ pgbackrest --stanza=openpgsql --set=20210406-225743F_20210406-231110I info
stanza: openpgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (12): 000000010000000000000068/00000001000000000000006C

        incr backup: 20210406-225743F_20210406-231110I
            timestamp start/stop: 2021-04-06 23:11:10 / 2021-04-06 23:11:12
            wal start/stop: 00000001000000000000006A / 00000001000000000000006A
            database size: 61.2MB, database backup size: 2.4MB
            repo1: backup set size: 8.0MB, backup size: 239.5KB
            backup reference list: 20210406-225743F
            database list: dixit (16384), kartikey (16385), postgres (14188)

I have removed (did rm -rf *) all files that exists in PG_HOME/base directory, lets restore.

[postgres@canttowin data]$ pgbackrest --stanza=openpgsql --db-include=dixit --type=immediate --target-action=promote restore --log-level-console=detail
2021-04-06 23:19:12.641 P00 INFO: restore command begin 2.32: --db-include=dixit --exec-id=82229-9187cb59 --log-level-console=detail --pg1-path=/var/lib/pgsql/12/data --repo1-path=/var/lib/pgbackrest --stanza=openpgsql --target-action=promote --type=immediate
2021-04-06 23:19:12.676 P00 INFO: restore backup set 20210406-225743F_20210406-231218I
2021-04-06 23:19:12.677 P00 DETAIL: databases found for selective restore (1, 14187, 14188, 16384, 16385)
2021-04-06 23:19:12.677 P00 DETAIL: check '/var/lib/pgsql/12/data' exists
2021-04-06 23:19:12.678 P00 DETAIL: create path '/var/lib/pgsql/12/data/base'
2021-04-06 23:19:12.678 P00 DETAIL: create path '/var/lib/pgsql/12/data/base/1'
2021-04-06 23:19:12.678 P00 DETAIL: create path '/var/lib/pgsql/12/data/base/14187'
2021-04-06 23:19:12.678 P00 DETAIL: create path '/var/lib/pgsql/12/data/base/14188'
2021-04-06 23:19:12.679 P00 DETAIL: create path '/var/lib/pgsql/12/data/base/16384'
2021-04-06 23:19:12.679 P00 DETAIL: create path '/var/lib/pgsql/12/data/base/16385'
2021-04-06 23:19:12.679 P00 DETAIL: create path '/var/lib/pgsql/12/data/global'
2021-04-06 23:19:12.679 P00 DETAIL: create path '/var/lib/pgsql/12/data/log'
2021-04-06 23:19:12.679 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_commit_ts'
2021-04-06 23:19:12.679 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_dynshmem'
2021-04-06 23:19:12.679 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_logical'
2021-04-06 23:19:12.683 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_logical/mappings'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_logical/snapshots'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_multixact'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_multixact/members'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_multixact/offsets'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_notify'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_replslot'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_serial'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_snapshots'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_stat'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_stat_tmp'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_subtrans'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_tblspc'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_twophase'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_wal'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_wal/archive_status'
2021-04-06 23:19:12.684 P00 DETAIL: create path '/var/lib/pgsql/12/data/pg_xact'
2021-04-06 23:19:12.879 P01 INFO: restore file /var/lib/pgsql/12/data/base/14188/16415 (13.5MB, 22%) checksum d8deb3703748d22554be2fb29c0ed105bab9658c
2021-04-06 23:19:12.957 P01 INFO: restore file /var/lib/pgsql/12/data/base/14188/16426 (5MB, 30%) checksum 29a07de6e53a110380ef984d3effca334a07d6e6
2021-04-06 23:19:12.999 P01 INFO: restore file /var/lib/pgsql/12/data/base/14188/16423 (2.2MB, 33%) checksum 5184ac361b2bef0df25a34e91636a085fc526930
2021-04-06 23:19:13.000 P01 DETAIL: restore zeroed file /var/lib/pgsql/12/data/base/16385/1255 (632KB, 34%)
2021-04-06 23:19:13.057 P01 INFO: restore file /var/lib/pgsql/12/data/base/16384/1255 (632KB, 35%) checksum edd483d42330ae26a455b3ee40e5c2b41cb298d5
2021-04-06 23:19:13.065 P01 INFO: restore file /var/lib/pgsql/12/data/base/14188/1255 (632KB, 36%) checksum fc3c70ab83b8c87e056594f20b2186689d3c4678
2021-04-06 23:19:13.101 P01 INFO: restore file /var/lib/pgsql/12/data/base/14187/1255 (632KB, 37%) checksum edd483d42330ae26a455b3ee40e5c2b41cb298d5
2021-04-06 23:19:13.118 P01 INFO: restore file /var/lib/pgsql/12/data/base/1/1255 (632KB, 38%) checksum edd483d42330ae26a455b3ee40e5c2b41cb298d5
2021-04-06 23:19:13.119 P01 DETAIL: restore zeroed file /var/lib/pgsql/12/data/base/16385/2838 (456KB, 39%)
2021-04-06 23:19:13.127 P01 INFO: restore file /var/lib/pgsql/12/data/base/16384/2838 (456KB, 40%) checksum c41dbf11801f153c9bd0493eb6deadd1a3f22333
2021-04-06 23:19:13.133 P01 INFO: restore file /var/lib/pgsql/12/data/base/16384/2608 (456KB, 41%) checksum 9de1966f80ac1c0bfa530fa3379e55bfea5936e0
…
…..
2021-04-06 23:19:14.941 P01 DETAIL: restore zeroed file /var/lib/pgsql/12/data/base/16385/14043_fsm (24KB, 78%)
2021-04-06 23:19:14.942 P01 DETAIL: restore zeroed file /var/lib/pgsql/12/data/base/16385/14038_fsm (24KB, 78%)
2021-04-06 23:19:14.943 P01 DETAIL: restore zeroed file /var/lib/pgsql/12/data/base/16385/14033_fsm (24KB, 78%)
2021-04-06 23:19:14.943 P01 DETAIL: restore zeroed file /var/lib/pgsql/12/data/base/16385/14028_fsm (24KB, 78%)
2021-04-06 23:19:14.944 P01 DETAIL: restore zeroed file /var/lib/pgsql/12/data/base/16385/14023_fsm (24KB, 78%)
….
……
2021-04-06 23:19:17.879 P00 DETAIL: sync path '/var/lib/pgsql/12/data/pg_stat_tmp'
2021-04-06 23:19:17.879 P00 DETAIL: sync path '/var/lib/pgsql/12/data/pg_subtrans'
2021-04-06 23:19:17.879 P00 DETAIL: sync path '/var/lib/pgsql/12/data/pg_tblspc'
2021-04-06 23:19:17.879 P00 DETAIL: sync path '/var/lib/pgsql/12/data/pg_twophase'
2021-04-06 23:19:17.879 P00 DETAIL: sync path '/var/lib/pgsql/12/data/pg_wal'
2021-04-06 23:19:17.879 P00 DETAIL: sync path '/var/lib/pgsql/12/data/pg_wal/archive_status'
2021-04-06 23:19:17.879 P00 DETAIL: sync path '/var/lib/pgsql/12/data/pg_xact'
2021-04-06 23:19:17.879 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2021-04-06 23:19:17.879 P00 DETAIL: sync path '/var/lib/pgsql/12/data/global'
2021-04-06 23:19:17.883 P00 INFO: restore command end: completed successfully (5243ms)
[postgres@canttowin data]$

Perfect, the restore is completed. Let’s start the database cluster.

[postgres@canttowin data]$ cd /usr/pgsql-12/bin/
[postgres@canttowin bin]$
[postgres@canttowin bin]$ /usr/pgsql-12/bin/pg_ctl^C
[postgres@canttowin bin]$
[postgres@canttowin bin]$ ./pg_ctl -D /var/lib/pgsql/12/data start
waiting for server to start….2021-04-06 23:19:55.212 EDT [82343] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-06 23:19:55.212 EDT [82343] LOG: listening on IPv6 address "::1", port 5432
2021-04-06 23:19:55.212 EDT [82343] LOG: listening on IPv4 address "127.0.0.1", port 5432
2021-04-06 23:19:55.213 EDT [82343] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-04-06 23:19:55.216 EDT [82343] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-04-06 23:19:55.226 EDT [82343] LOG: redirecting log output to logging collector process
2021-04-06 23:19:55.226 EDT [82343] HINT: Future log output will appear in directory "log".
done
server started

Now let’s connect with the database and see if we still see those records which we inserted.

[postgres@canttowin ~]$ psql -p 5432
psql (12.6.7)
Type "help" for help.

postgres=# SELECT datname FROM pg_database WHERE datistemplate = false;
 datname
----------
 postgres
 dixit
 kartikey
(3 rows)

postgres=# \c dixit
You are now connected to database "dixit" as user "postgres".

dixit=# \d
           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

dixit=# select * from company;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |        | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 |
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(5 rows)

dixit=#
dixit=#

Perfect! they are there.

Similarly you can do PITR (point-in-time) backups and restores and even backup and restore any specific database.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | 1 Comment »

How to register remote PEM agents to the PEM Server ?

Posted by FatDBA on April 3, 2021

Hi Guys,

During that quiesce period when I was away from blogging, I worked on lot of stuff, hence lot of contents to share 🙂 …. So here goes my another post. This one is about registering PEM agents with the PEM server as each PEM agent must be registered with the PEM server.

I have this PEM Server already configured (steps for configuring PEM server) and I have this new EDB AS 12 standby server which I would like to add to the PEM monitoring console. let’s get started!

192.168.20.128: PEM Server Host (canttowin.ontadomain)
192.168.20.129: Standby host (canttowinsec.quebecdomain)

I have already installed PEM agent (edb-pem-agent-8.0.1-1.rhel7.x86_64) on this remote standby host, let me show you that.

[root@canttowinsec ~]# yum install edb-pem-agent
Loaded plugins: langpacks, ulninfo
epel/x86_64/metalink | 7.0 kB 00:00:00
local | 2.9 kB 00:00:00
ol7_UEKR6 | 2.5 kB 00:00:00
ol7_latest | 2.7 kB 00:00:00
percona-release-noarch | 2.9 kB 00:00:00
percona-release-x86_64 | 2.9 kB 00:00:00
prel-release-noarch | 2.9 kB 00:00:00
Package edb-pem-agent-8.0.1-1.rhel7.x86_64 already installed and latest version
Nothing to do

Let’s go to the agent home directory and call the configuration utility called ‘pemworker’.

[root@canttowinsec bin]# pwd
/usr/edb/pem/agent/bin

[root@canttowinsec bin]# ls
pemagent pemworker pkgLauncher

Here we have to use few of the configuration options with their preferred values.
–pem-server : IP Address of the PEM backend database server.
–pem-port : Port of the PEM backend database server, default is 5432, but you have to check what port you have used.
-–pem-user : name of the Database user (having superuser privileges) of the PEM backend database server. This is a mandatory option.
–allow_server_restart: Enable the allow-server_restart parameter to allow PEM to restart the monitored server. TRUE is default.
–allow-batch-probes: Enable the allow-batch-probes parameter to allow PEM to run batch probes on this agent. FALSE is default.
-–batch-script-user: operating system user that should be used for executing the batch/shell scripts. NONE is default.

[root@canttowinsec bin]# ./pemworker --register-agent --pem-server 192.168.20.128 --pem-port 5444 --pem-user enterprisedb --allow_server_restart true --allow-batch-probes true --batch-script-user enterprisedb
Postgres Enterprise Manager Agent registered successfully!

Okay, so the agent is successfully registered with the PEM Server. Next we need to add the configuration to the agent.cfg file.

[root@canttowinsec etc]# pwd
/usr/edb/pem/agent/etc
[root@canttowinsec etc]# ls
agent.cfg

I am setting allow_streaming_replication to TRUE as this makes user to configure streaming replication, and next parameter is to provide path of CA certificates.
[root@canttowinsec etc]# echo "allow_streaming_replication=true" >> /usr/edb/pem/agent/etc/agent.cfg
[root@canttowinsec etc]# echo "ca_file=/usr/libexec/libcurl-pem/share/certs/ca-bundle.crt" >> /usr/edb/pem/agent/etc/agent.cfg

So, now my agent configuration file will look like below.

[root@canttowinsec etc]# more agent.cfg
[PEM/agent]
pem_host=192.168.20.128
pem_port=5444
agent_id=2
agent_ssl_key=/root/.pem/agent2.key
agent_ssl_crt=/root/.pem/agent2.crt
log_level=warning
log_location=/var/log/pem/worker.log
agent_log_location=/var/log/pem/agent.log
long_wait=30
short_wait=10
alert_threads=0
enable_smtp=false
enable_snmp=false
enable_webhook=false
max_webhook_retries=3
allow_server_restart=true
max_connections=0
connect_timeout=10
connection_lifetime=0
allow_batch_probes=true
heartbeat_connection=false
enable_nagios=false
batch_script_user=enterprisedb
allow_streaming_replication=true
ca_file=/usr/libexec/libcurl-pem/share/certs/ca-bundle.crt

Now you will see your PEM agent already added to the PEM agents list under PEM console.

Next you can add your standby database to the list of managed server. Here you need to follow same steps what I have discussed in my last post about PEM configuration, please click here to directly go to that post. The only difference is that you need to select the bounded agent from the drop down list, here you see your new agent coming under drop list, rest all is same!

Once it’s added successfully, you will see the new server under the list, here I have named the connection ‘EDBAS12_Sby‘.

Now here is how the main landing page will look like, new agent and database with its status.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | Leave a Comment »

How to install EDB-AS without it’s software repositories ?

Posted by FatDBA on April 3, 2021

Hi Everyone,

Many of you might be thinking after reading the title – Why to write about such a simple or rudimentary task, what is so tricky about installing EDB PostgreSQL software ? I know it’s quite easy and straight forward, but only if you are able to add the EDB repository to your server or register it, if you fail to add or register it, then it will be very difficult and becomes a tedious and a time consuming activity to install all of the software’s and their long list of dependencies. This post is all about how to deal with such situation, how to download the source RPMs and install them on the server if you are not able to add the EDB repository.

First step is to download the complete EDB’s tarball, I am downloading the complete tarball here as I don’t want to miss any dependent packages which are needed by the core components. This tarball is close to 1.8 GBs in size, you can download the file using below wget command, here you need to use your EDB credentials.

wget https://prashant.dixit:password@yum.enterprisedb.com/edb/redhat/edb_redhat_rhel-7-x86_64.tar.gz

Now, once the tarball is installed, we can go and create the YUM local repository, though to create YUM repository is optional as you can also install RPMs directly, but will make your work lot easier otherwise you have to look out for dependencies manually. So, I have deceided to create the local repository here.

Once the above file is downloaded, unzip it. You will see list of all core and dependent packages/rpm, just like below.

….
….
edb-pgpool40-4.0.8-1.rhel7.x86_64.rpm sslutils_96-1.3-2.rhel7.x86_64.rpm
edb-pgpool40-4.0.9-1.rhel7.x86_64.rpm wxjson-1.2.1-1.rhel7.x86_64.rpm
edb-pgpool40-devel-4.0.6-1.rhel7.x86_64.rpm wxjson-1.2.1-2.rhel7.x86_64.rpm
edb-pgpool40-devel-4.0.8-1.rhel7.x86_64.rpm wxjson-devel-1.2.1-1.rhel7.x86_64.rpm
edb-pgpool40-devel-4.0.9-1.rhel7.x86_64.rpm wxjson-devel-1.2.1-2.rhel7.x86_64.rpm
…
…

Next I will create a directory which will be used as a repository container.
[root@canttowin edb]# mkdir -p /home/user/repo

move all unzipped files/rpms to this new directory.
[root@canttowin edb]# mv * /home/user/repo

change permissions of the directory.
[root@canttowin edb]# chown -R root.root /home/user/repo
[root@canttowin edb]# chmod -R o-w+r /home/user/repo

Now we can go and create the repository, for that we will use ‘createrepo‘ command.
[root@canttowin edb]# createrepo /home/user/repo
Spawning worker 0 with 1151 pkgs
Workers Finished
Saving Primary metadata
Saving file lists metadata
Saving other metadata
Generating sqlite DBs
Sqlite DBs complete

Now let’s create the YUM repository entry under /etc/yum.repos.d
[root@canttowin edb]# more /etc/yum.repos.d/myrepo.repo
[local]
name=Prashant Local EDB Repo
baseurl=file:///home/user/repo
enabled=1
gpgcheck=0
[root@canttowin edb]#

All set! let’s try to look for any EDB’s package using this new local repository

[root@canttowin ~]# yum search edb-as12-server
Loaded plugins: langpacks, ulninfo
=============================================================== N/S matched: edb-as12-server ================================================================
edb-as12-server.x86_64 : EnterpriseDB Advanced Server Client and Server Components
edb-as12-server-client.x86_64 : The client software required to access EDBAS server.
edb-as12-server-cloneschema.x86_64 : cloneschema is a module for EnterpriseDB Advanced Server
edb-as12-server-contrib.x86_64 : Contributed source and binaries distributed with EDBAS
edb-as12-server-core.x86_64 : The core programs needed to create and run a EnterpriseDB Advanced Server
edb-as12-server-devel.x86_64 : EDBAS development header files and libraries
edb-as12-server-docs.x86_64 : Extra documentation for EDBAS
edb-as12-server-edb-modules.x86_64 : EDB-Modules for EnterpriseDB Advanced Server
edb-as12-server-indexadvisor.x86_64 : Index Advisor for EnterpriseDB Advanced Server
edb-as12-server-libs.x86_64 : The shared libraries required for any EDBAS clients
edb-as12-server-llvmjit.x86_64 : Just-In-Time compilation support for EDBAS
edb-as12-server-parallel-clone.x86_64 : parallel_clone is a module for EnterpriseDB Advanced Server
edb-as12-server-pldebugger.x86_64 : PL/pgSQL debugger server-side code
edb-as12-server-plperl.x86_64 : The Perl procedural language for EDBAS
edb-as12-server-plpython.x86_64 : The Python procedural language for EDBAS
edb-as12-server-plpython3.x86_64 : The Python3 procedural language for EDBAS
edb-as12-server-pltcl.x86_64 : The Tcl procedural language for EDBAS
edb-as12-server-sqlprofiler.x86_64 : SQL profiler for EnterpriseDB Advanced Server
edb-as12-server-sqlprotect.x86_64 : SQL Protect for EnterpriseDB Advanced Server

Great, so we are now able to look and install all our EDB packages through YUM, it’s lot easier than manually fixing dependencies and install core packages.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

How to monitor your PostgreSQL databases using EDB PEM – Setup, Config, benchmarking and much more …

Posted by FatDBA on March 26, 2021

Hi Everyone,

Today’s post will be all about monitoring your PostgreSQL database clusters using EDB PostgreSQL Enterprise Manager (PEM). Postgres Enterprise Manager is a comprehensive, customizable solution providing an interface to control and optimize your PostgreSQL deployment.

I will be doing the installation, configuration, adding servers to the console and will perform a live monitoring of the database while I will be generating some synthetic load on the database host. I am doing this on a standalone RHEL 7 64 Bit server which I will be using it both as a PEM server and local instance. Alright, so without further ado, lets start. So, first you need to download EDB’s official repository and install following package.

Below is a complete list of packages available with name ‘edb-pem’, you need to install version: edb-pem-8.0.1-1.rhel7.x86_64

[root@canttowin repo]# yum search edb-pem
Loaded plugins: langpacks, ulninfo

=================================================================== N/S matched: edb-pem ====================================================================
edb-pem-debuginfo.x86_64 : Debug information for package edb-pem
edb-pem.x86_64 : PostgreSQL Enterprise Manager
edb-pem-agent.x86_64 : Postgres Enterprise Manager Agent
edb-pem-docs.x86_64 : Documentation for Postgres Enterprise Manager
edb-pem-server.x86_64 : PEM Server Components

Once installation is completed, go to the default installation directory, it’s /usr/edb in my case, and go to pem/bin folder.

[root@canttowin ~]# cd /usr/edb/
[root@canttowin edb]# ls
as12 bart efm-4.1 jdbc migrationtoolkit pem pgbouncer1.15 pgpool4.2
[root@canttowin ~]# cd /usr/edb/pem/bin/
[root@canttowin bin]# ls
configure-pem-server.sh configure-selinux.sh

We see two configuration shell scripts are present, we will be using the configuration script – configure-pem-server.sh
Here I will be choosing option 1 which means I will be installing web services and databases all on one host, next you need to input installation path (/usr/edb/as12 in my case), followed by super user name, port numbers and IP Address of the server.

Before I call the config script, let me quickly reset the default superuser’s password.

postgres=# alter user postgres with password 'dixit';
ALTER ROLE

Now, let’s call the configuration scipt and pass all discussed values.

[root@canttowin bin]# ./configure-pem-server.sh

 -----------------------------------------------------
 EDB Postgres Enterprise Manager
 -----------------------------------------------------
Install type: 1:Web Services and Database, 2:Web Services 3: Database [ ] :1
Enter local database server installation path (i.e. /usr/edb/as12 , or /usr/pgsql-12, etc.) [ ] :/usr/edb/as12
Enter database super user name [ ] :enterprisedb
Enter database server port number [ ] :5444
Enter database super user password [ ] :
Please enter CIDR formatted network address range that agents will connect to the server from, to be added to the server's pg_hba.conf file. For example, 192.168.1.0/24 [ 0.0.0.0/0 ] :10.0.0.153/32
Enter database systemd unit file or init script name (i.e. edb-as-12 or postgresql-12, etc.) [ ] :edb-as-12
Please specify agent certificate path (Script will attempt to create this directory, if it does not exists) [ ~/.pem/ ] :
CREATE EXTENSION
[Info] Configuring database server.
CREATE DATABASE
CREATE ROLE
CREATE ...
..
..
..
CREATE EXTENSION
-->  [Info] -->  [Info] Configuring database server.
-->  [Info] -->  [Info] creating role pem
-->  [Info] -->  [Info] Generating certificates
-->  [Info] -->  [Info] Executing systemctl stop edb-as-12
-->  [Info] -->  [Info] Skipping - configurations for /var/lib/edb/as12/data/pg_hba.conf and /var/lib/edb/as12/data/postgresql.conf file
-->  [Info] -->  [Info] Executing systemctl start edb-as-12
-->  [Info] -->  [Info] Enable pemagent service.
-->  [Info] -->  [Info] Executing systemctl enable pemagent
-->  [Info] -->  [Info] Stop pemagent service
-->  [Info] -->  [Info] Executing systemctl stop pemagent
-->  [Info] -->  [Info] Start pemagent service.
-->  [Info] -->  [Info] Executing systemctl start pemagent
-->  [Info] -->  [Info] Configuring httpd server
-->  [Info] -->  [Info] Executing systemctl stop httpd
-->  [Info] -->  [Info] Taking backup of /usr/edb/pem/web/pem.wsgi
-->  [Info] -->  [Info] Creating /usr/edb/pem/web/pem.wsgi
-->  [Info] -->  [Info] Taking backup of /usr/edb/pem/web/config_local.py.
-->  [Info] -->  [Info] Generating PEM Cookie Name.
-->  [Info] -->  [Info] Creating /usr/edb/pem/web/config_local.py
-->  [Info] -->  [Info] Taking backup of /etc/httpd/conf.d/edb-pem.conf
-->  [Info] -->  [Info] Creating /etc/httpd/conf.d/edb-pem.conf
-->  [Info] -->  [Info] Configuring httpd server sslconf
-->  [Info] -->  [Info] Taking backup of /etc/httpd/conf.d/edb-ssl-pem.conf
-->  [Info] -->  [Info] Taking backup of /etc/httpd/conf.d/edb-ssl-pem.conf
-->  [Info] -->  [Info] Executing /usr/edb/pem/web/setup.py
Postgres Enterprise Manager - Application Initialisation
========================================================
-->  [Info] -->  [Info] Check and Configure SELinux security policy for PEM
 getenforce found, now executing 'getenforce' command
 Configure the httpd to work with the SELinux
 Allow the httpd to connect the database (httpd_can_network_connect_db = on)
 Allow the httpd to connect the network (httpd_can_network_connect = on)
 Allow the httpd to work with cgi (httpd_enable_cgi = on)
 Allow to read & write permission on the 'pem' user home directory
 SELinux policy is configured for PEM
-->  [Info] -->  [Info] Executing systemctl start httpd
-->  [Info] -->  [Info] Configured the webservice for EDB Postgres Enterprise Manager (PEM) Server on port '8443'.
-->  [Info] -->  [Info] PEM server can be accessed at https://127.0.0.1:8443/pem at your browser

It’s completed, and at the very end it has provided URL to access the PEM GUI.

Now next step is to install PEM Agents to the server, you need to install it on all servers which you want to monitor, I am leaving the PEMAgents configuration that you do in agent.cfg file.

[root@canttowin bin]# yum install edb-pem-agent

Let’s check the PEM GUI now.

Here on the left panel you will notice there’s already one database present under ‘PEM Server Directory’ folder, this is the same database which we have configured/used PEM server, hence it will be automatically added to the server list. We will manually add one more database cluster to explain how to do it explicitly.

Let’s check the dashboard for the same (PEM Server) database for session, TPS, IO related details.

Now, let’s add another database to the monitoring console. I will be adding a community PostgreSQL 12 database to it. Go to ‘PEM Server Directory’ folder right click on it, choose option create-> server.

Next, fill connection wizard with all details i.e, username, password, IP, port and security related details for the new database and click save at the end.

And you are done!

Now, let’s see the default landing page of PEM GUI and here you see details of all added hosts and agents with their status.

Next I will create some new databases to see how that data reflects in PEM GUI.
postgres=#
postgres=# create database dixit;
CREATE DATABASE
postgres=# create database kartikey;
CREATE DATABASE

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size
-----------+----------+----------+-------------+-------------+-----------------------+--------
dixit | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8049 kB
kartikey | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8049 kB
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8193 kB

(3 rows)

All good! now let’s do some performance test to see how useful PEM can be in case of performance issues. In order to mimic or simulate the situation, I will generating some synthetic load using PostgreSQL’s default utility Pgbench.

Reference:
-c number of clients
-j 2 number of threads
-t amount of transactions

These values are 10000 transactions per client. So : 10 x 10000 = 100,000 transactions

[postgres@canttowin bin]$ ./pgbench -U postgres -p 5432 -c 10 -j 2 -t 10000 postgres
starting vacuum…end.

Let’s see how the changes are captured and presented in PEM.

Okay, we can see the peaks are recorded and presented.

The load is still running and we can clearly see that from the below graph.

[postgres@canttowin bin]$ ./pgbench -U postgres -p 5432 -c 10 -j 2 -t 10000 postgres
starting vacuum…end.transaction type:
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 2
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 18.217 ms
tps = 548.940142 (including connections establishing)
tps = 548.970173 (excluding connections establishing)

Alright, so the load run has ended, let see how the graph now looks like.

So to conclude, PEM is a great tool which can fulfil all your monitoring needs, it has got some cool features too i.e. performance dashboards, tuning wizards, advisories and other graphs.

Hope It Helped
Prashant Dixit

Posted in Uncategorized | Tagged: , , , , | 1 Comment »

« Previous Entries
 
%d bloggers like this: