Tales From A Lazy Fat DBA

Fan of Oracle DB & Performance, PostgreSQL, Cassandra & much more … \,,/

  • Likes

    • 269,218
  • Archives

  • Categories

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Lets spread some Chaos – Gremlin Chaos Engineering Practitioner Certification

Posted by FatDBA on June 17, 2021

Chaos Engineering is a disciplined approach of identifying potential failures before they become outages, and its engineering practices focused on (and built on) Kubernetes environments, applications, microservices, and infrastructure (including Databases, storage or networking).

Gremlin provides a ‘failure-as-a-service’ testing platform or a toolset built to make systems more reliable. It turns failure into resilience by offering engineers a fully hosted solution to safely experiment on complex systems, in order to identify weaknesses before they impact customers and cause revenue loss. It can be easily tested on any of the infrastructure components to avoid single point of failures and to remove any FPs make system more HA and failsafe.

So, this you can recommend to any of your customer before they go live, should test the infra by generating intentional chaos to test respective zones, services, software  component, Storage (Disk space etc.), Databases (Cluster, replicas, standbys), Applications (Kubernetes etc.) and Networks.

About exam – This certification tests your knowledge on Chaos Engineering concepts like Gremlin platform, GameDay, MoD (Master of disaster) and other similar experiments and techniques.

Exam is free of cost and if you have any prior knowledge you can directly go and give the exam, but its good to attend their free prep session. There is not any time limit, and you have to answer 20 questions in total. Passing percentage required is 80% and you have 2 attempts in total to do that.

Direct link to certification: https://www.gremlin.com/blog/announcing-the-gremlin-chaos-engineering-practitioner-certificate-program/

Link for free prep session registration: https://www.gremlin.com/webinars/gremlin-certificate-prep-session/

Hope It Helped!
Prashant Dixit

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

Something cool was introduced in Oracle Golden Gate 12.2 …

Posted by FatDBA on June 16, 2021

Hi Guys,

Last week someone from my team contacted me for a problem that he was facing with the Golden Gate, he was getting an error message with replicat process saying ‘Error mapping TEST.TABLE1 to TEST.TABLE1’ and the replicat process is abending at the target end. He was using Golden Gate version 12.1.2.0. Now during the initial investigation we found the table structure both at the source and target is same except column order/positions, just like below.

Source
-----------------------
    TEST.TABLE1
ID Varchar2(10)
Name Varchar2(10)
Codes Numeric(10)

Target:
------------------------
    TEST.TABLE1
ID Varchar2(10)
Codes Numeric(10)
Name Varchar2(10)

As GG works with the column order and not explicitly with the column names, it lead to that error at the target while doing the INSERTS in to the table. We knew that this is now auto handled by the GG, but not sure on the version when it got fixed, so immediately, we started looking for newer releases and their documentations, and luckily we found release notes for Golden Gate 12.2 which clearly explained that now there isn’t any need to generate the DEFGEN files, or to use ASSUMETARGETDEFS or SOURCEDEFS parameter (in some cases).

Starting from Golden Gate version 12.2 (released in year 2016), the trail files now contains the metadata (TDR or table definition records), so GG knows table details like column order/positions, data type, column length etc. before applying the changes to the table.

So, about the solution, we had two options to choose from – Upgrade GG from 12.1 to 12.2 or else use FORMAT RELEASE 12.2 parameter, with that, there is no need to use any of the above mentioned parameters. We choose to upgraded GG version to 12.2 and that fixed the problem!

Hope It Helped
Prashant Dixit

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

My favorite 5 SQLcl Features ….

Posted by FatDBA on June 13, 2021

Hi Guys,

Todays post is about one of the tool that I have been using from last few years now and which I really like when it comes to working on Oracle databases. This is called SQLcl or SQL Developer Command Line, we can call it as an advance version of SQL Plus as it comes with lot of cool and handy features/commands those are not available with default SQL Plus command line interface or SQL prompt. So, this post is about ‘TOP 5 features of SQLcl which I like the most’ ….

Few of you who don’t know what this SQLcl is ? – This is one of tool developed by Oracle’s SQL Developer team, lead by Jeff Smith (Thanks Jeff for that!) and it’s something that makes DBAs & Developers job easy with its range of commands and features that makes it very powerful and gives upper hand over SQL Plus.

So lets get started with top 5 features of SQLcl that I liked the most.

Note: All of the below tests I have performed on SQLcl version 21.1.1.0 build: 21.1.1.113.1704

[oracle@canttowin bin]$ ./sql

SQLcl: Release 21.1 Production on Sat Jun 12 23:29:31 2021

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Username? (''?) system
Password? (**********?) ********
Last Successful login time: Sat Jun 12 2021 23:29:39 -04:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.1.1.0 build: 21.1.1.113.1704

Feature 1: ‘REPEAT’ command.
This is pretty useful command if you want to execute any specific query for a number of times to see results. I mean it’s just like what ‘watch’ is on Linux OS.
It simply executes the SQL command and provides output for number of times at a particular interval.

I.e. Below I have executed an SQL for 5 times with a gap of 5 seconds.

SQL> select count(*) from v$session where status='ACTIVE';

   COUNT(*)
___________
         149

SQL> repeat 5 5
Running 1 of 5  @ 11:38:25.243 with a delay of 5s

   COUNT(*)
___________
89
Running 2 of 5  @ 11:38:30.251 with a delay of 5s

   COUNT(*)
___________
109
Running 3 of 5  @ 11:38:35.254 with a delay of 5s

   COUNT(*)
___________
199
Running 4 of 5  @ 11:38:40.258 with a delay of 5s

   COUNT(*)
___________
230
Running 5 of 5  @ 11:38:45.263 with a delay of 5s

   COUNT(*)
___________
409

SQL>

Feature 2: ‘Quick DDL Generation’
Now with SQLcl you don’t have to run DBMS_METADATA.get_ddl to get the definition of your Table or Index or anything. You can simply use the DDL command with syntax DDL and you will have your complete object DDL.

SQL>
SQL>
SQL> ddl system.bigtab table

  CREATE TABLE "SYSTEM"."BIGTAB"
   (    "ID" NUMBER,
        "WEIGHT" NUMBER,
        "ADATE" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
SQL>

Feature 3: ‘Collect Object Information’
Now no need to query dynamic views and DESC commands to get your table stats (rows, analyzed date, in memory status, comments and sample size) and table descriptions. This you can get using a single command of INFORMATION or INFO.

If you want to get more details about histograms on your table, then you have INFO+ command which presents more details to you.

SQL> information system.bigtab
TABLE: BIGTAB
         LAST ANALYZED:2021-06-12 23:45:33.0
         ROWS         :67310
         SAMPLE SIZE  :67310
         INMEMORY     :DISABLED
         COMMENTS     :This is a table for testing purposes

Columns
NAME         DATA TYPE   NULL  DEFAULT    COMMENTS
 ID          NUMBER      Yes
 WEIGHT      NUMBER      Yes
 ADATE       DATE        Yes

SQL>


SQL> info+ system.bigtab
TABLE: BIGTAB
         LAST ANALYZED:2021-06-20 13:41:19.0
         ROWS         :67310
         SAMPLE SIZE  :67310
         INMEMORY     :DISABLED
         COMMENTS     :This is a table for testing purposes

Columns
NAME         DATA TYPE   NULL  DEFAULT    LOW_VALUE             HIGH_VALUE            NUM_DISTINCT   HISTOGRAM
 ID          NUMBER      Yes                  22                     198                   1000          FREQUENCY
 WEIGHT      NUMBER      Yes                  -2147337077           2147453933            67310          NONE
 ADATE       DATE        Yes                  2018.09.16.23.34.01   2021.06.12.23.24.14   66224          NONE

Feature 4: ‘CTAS easy and quick’
CTAS is very useful command and is quite frequently used because of its simplicity and purpose, now with SQLcl you don’t have to type the complete command or syntax to create a new tables using existing via CTAS.

SQL> ctas bigtab bigtable666

  CREATE TABLE "SYSTEM"."BIGTABLE666"
   (    "ID",
        "WEIGHT",
        "ADATE",
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
 as
select * from BIGTAB
SQL>

Feature 5: ‘Extended and more descriptive AUTOTRACING’.
This is by far one of the best feature of SQLcl in my opinion and specially for someone who have to tune and tweak databases every now and then. This provides more advance level details or statistics (overall 37 different stats) for any SQL statement where you set the AUTOTRACING feature ON. I am big fan of this feature!

SQL>
SQL> set autotrace on
Autotrace Enabled
Shows the execution plan as well as statistics of the statement.
SQL>

SQL> select * from system.bigtab where ID =588;
...
.....

67 rows selected.

Explain Plan
-----------------------------------------------------------
                                                              PLAN_TABLE_OUTPUT
_______________________________________________________________________________
Plan hash value: 441133017

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    76 |  2660 |    63   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BIGTAB |    76 |  2660 |    63   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=588)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Statistics
-----------------------------------------------------------
               1  CPU used by this session
               1  CPU used when call started
               3  DB time
              47  Requests to/from client
              46  SQL*Net roundtrips to/from client
               4  buffer is not pinned count
            1329  bytes received via SQL*Net from client
           91534  bytes sent via SQL*Net to client
               5  calls to get snapshot scn: kcmgss
              11  calls to kcmgcs
             302  consistent gets
             302  consistent gets from cache
             302  consistent gets pin
             302  consistent gets pin (fastpath)
               2  enqueue releases
               2  enqueue requests
               3  execute count
         2473984  logical read bytes from cache
             293  no work - consistent read gets
              49  non-idle wait count
               3  opened cursors cumulative
               1  opened cursors current
               2  parse count (hard)
               3  parse count (total)
               1  parse time cpu
               2  parse time elapsed
              12  process last non-idle time
               5  recursive calls
               1  recursive cpu usage
             302  session logical reads
               1  sorts (memory)
            2010  sorts (rows)
             293  table scan blocks gotten
           86905  table scan disk non-IMC rows gotten
           86905  table scan rows gotten
               2  table scans (short tables)
              47  user calls

Hope It Helped!
Prashant Dixit

Posted in Advanced, Basics | 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: , , , | Leave a 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 »

How to monitor your PostgreSQL database using Grafana, Prometheus & postgres_exporter.

Posted by FatDBA on March 24, 2021

Hi Everyone,

I am back with the another post, this time it’s for monitoring PostgreSQL database using one of the popular interactive visualization platform Grafana. I have recently implemented Grafana + Prometheus and created few really cool performance charts and database metric dashboards using one of the popular PostgreSQL metric exporter ‘postgres_exporter‘ to monitor a EDB 12 PostgreSQL database cluster.

I have divided everything in to three parts – Grafana Installation & configuration, Prometheus Installation and Configuration and final postgres_exporter installation and configuration.

let’s first start with Grafana installation and configuration on Linux server (this is EL 7).

Grafana Installation:

1. Disable SELinux

Change SELINUX=enforcing to SELINUX=disabled and Reboot.

vi /etc/sysconfig/selinux

2. Now we need to create Grafana YUM repository

vi /etc/yum.repos.d/grafana.repo

and add following lines to it

[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt

3. Install Grafana now using YUM

yum install grafana

This will install all binaries to location /usr/sbin/grafana-server, copies init.d file to /etc/init.d/grafana-server and default log location would be /var/log/grafana/grafana.log.

4. Install additional font packages

yum install fontconfig
yum install freetype*
yum install urw-fonts

5. Now, enable grafana service

Enable Grafana service on system boot
systemctl enable grafana-server.service

Start it using the following command:
systemctl start grafana-server

6. Check Grafana web interface

http://IPADDRESSorHOSTNAME:3000/

Note: Default username and password is ‘admin‘, and once you login, it will prompt you to set a new password.

Add PostgreSQL as Data Source:

Next we now going to add PostgreSQL as a data source in Grafana.

1. Login to Grafana and go to ‘configuration‘ tab on the left of the console.

2. Add PostgreSQL as a data source, populate all details like hostname, port number, database name, DB user and password, PostgreSQL version and some optional entries like connection limits, SSL details etc. Once done, click on button with name save and test to see if all details are fine.

3. Now you will start seeing PostgreSQL as a data source under data source tab.

Prometheus Installation & Config

All good, now next is to install and configure ‘Prometheus’, this is a time-series database that is optimized for storing and serving time series through associated pairs of time(s) and value(s). You can also use InfluxDB or Graphite as a time series database for Grafana.

1. Download and untar the Prometheus file for your respective OS.
Example:
curl -LO url -LO https://github.com/prometheus/prometheus/releases/download/v2.22.0/prometheus-2.22.0.linux-amd64.tar.gz
tar -xvf prometheus-2.22.0.linux-amd64.tar.gz
mv prometheus-2.22.0.linux-amd64 prometheus-files

2. Create prometheus user, directories and make that account owner for all the files and folders.

sudo useradd --no-create-home --shell /bin/false prometheus
sudo mkdir /etc/prometheus
sudo mkdir /var/lib/prometheus
sudo chown prometheus:prometheus /etc/prometheus
sudo chown prometheus:prometheus /var/lib/prometheus

3. Copy prometheus and promtool binaries from prometheus-files to /usr/local/bin and change ownership

sudo cp prometheus-files/prometheus /usr/local/bin/
sudo cp prometheus-files/promtool /usr/local/bin/
sudo chown prometheus:prometheus /usr/local/bin/prometheus
sudo chown prometheus:prometheus /usr/local/bin/promtool

4. Move consoles and console_libraries directories from prometheus-files to /etc/prometheus folder and change ownership

sudo cp -r prometheus-files/consoles /etc/prometheus
sudo cp -r prometheus-files/console_libraries /etc/prometheus
sudo chown -R prometheus:prometheus /etc/prometheus/consoles
sudo chown -R prometheus:prometheus /etc/prometheus/console_libraries

5. Now time to configure. Create prometheus.yml file

vi /etc/prometheus/prometheus.yml

copy below entries to the YAML file

global:
  scrape_interval: 10s

scrape_configs:
  - job_name: 'prometheus'
    scrape_interval: 5s
    static_configs:
      - targets: ['10.0.0.153:9090']

6. Change ownership of this file

sudo chown prometheus:prometheus /etc/prometheus/prometheus.yml

7. Create prometheus service file

vi /etc/systemd/system/prometheus.service

copy below entries to the file.

[Unit]
Description=Prometheus
Wants=network-online.target
After=network-online.target

[Service]
User=prometheus
Group=prometheus
Type=simple
ExecStart=/usr/local/bin/prometheus \
    --config.file /etc/prometheus/prometheus.yml \
    --storage.tsdb.path /var/lib/prometheus/ \
    --web.console.templates=/etc/prometheus/consoles \
    --web.console.libraries=/etc/prometheus/console_libraries

[Install]
WantedBy=multi-user.target

8. Register systemd service to register prometheus service and start it.

sudo systemctl daemon-reload
sudo systemctl enable prometheus
sudo systemctl start prometheus

and check the status of the service

sudo systemctl status prometheus

[root@canttowin edb]# systemctl status prometheus
● prometheus.service - Prometheus
   Loaded: loaded (/etc/systemd/system/prometheus.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-03-24 05:55:53 EDT; 4s ago
 Main PID: 17641 (prometheus)
    Tasks: 7
   CGroup: /system.slice/prometheus.service
           └─17641 /usr/local/bin/prometheus --config.file /etc/prometheus/prometheus.yml --storage.tsdb.path /var/lib/prometheus/ --web.console.templates...

Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.172Z caller=head.go:714 component=tsdb msg="WAL segment ...gment=10
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.358Z caller=head.go:714 component=tsdb msg="WAL segment ...gment=10
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.455Z caller=head.go:714 component=tsdb msg="WAL segment ...gment=10
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.456Z caller=head.go:714 component=tsdb msg="WAL segment ...gment=10
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.456Z caller=head.go:719 component=tsdb msg="WAL replay c...173255ms
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.478Z caller=main.go:732 fs_type=XFS_SUPER_MAGIC
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.478Z caller=main.go:735 msg="TSDB started"
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.478Z caller=main.go:861 msg="Loading configuration file"...heus.yml
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.481Z caller=main.go:892 msg="Completed loading of configuration …µs
Mar 24 05:55:54 canttowin.ontadomain prometheus[17641]: level=info ts=2021-03-24T09:55:54.481Z caller=main.go:684 msg="Server is ready to receive ...quests."
Hint: Some lines were ellipsized, use -l to show in full.

9. If no issues till here, you are all good, time to check Prometheus WEB GUI, you can access its using below URL.

http://10.0.0.153:9090/graph

10. You can check other stats and other available metrics via GUI console.

11. Now you will start seeing ‘Prometheus’ in the list of data sources along with PostgreSQL which we have added at the first step.

Postgres_Exporter Installation and configuration

Now, when Grafana and Prometheus is all set, time to install and configure postgres_exporter. Postgres_exporter is a popular PostgreSQL metric exporter for Prometheus.

1. Lets create few required directories first.

mkdir /opt/postgres_exporter
cd /opt/postgres_exporter

2. Download and untar the file.

wget https://github.com/wrouesnel/postgres_exporter/releases/download/v0.5.1/postgres_exporter_v0.5.1_linux-amd64.tar.gz
tar -xzvf postgres_exporter_v0.5.1_linux-amd64.tar.gz
cd postgres_exporter_v0.5.1_linux-amd64

3. Copy core file ‘postgres_exporter’ file to /usr/local/bin directory

cp postgres_exporter /usr/local/bin

4. Next, lets create the configuration file for postres_exporter

cd /opt/postgres_exporter
sudo vi postgres_exporter.env

I want to visualize my EDB 12 PostgreSQL stats, so will use below data source details. This is for all database, you can also monitor any specific database.

[root@canttowin edb]# more /opt/postgres_exporter/postgres_exporter.env
DATA_SOURCE_NAME="postgresql://enterprisedb:oracle@10.0.0.153:5444/?sslmode=disable"

5. Next, create the service for postgres_exporter

vi /etc/systemd/system/postgres_exporter.service

put below lines to the service file

[Unit]
Description=Prometheus exporter for Postgresql
Wants=network-online.target
After=network-online.target
[Service]
User=postgres
Group=postgres
WorkingDirectory=/opt/postgres_exporter
EnvironmentFile=/opt/postgres_exporter/postgres_exporter.env
ExecStart=/usr/local/bin/postgres_exporter
Restart=always
[Install]
WantedBy=multi-user.target

6. Next enable service and check status

sudo systemctl daemon-reload
sudo systemctl start postgres_exporter
sudo systemctl enable postgres_exporter

[root@canttowin edb]# systemctl status postgres_exporter
● postgres_exporter.service - Prometheus exporter for Postgresql
   Loaded: loaded (/etc/systemd/system/postgres_exporter.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-03-24 05:52:17 EDT; 2s ago
 Main PID: 16984 (postgres_export)
    Tasks: 3
   CGroup: /system.slice/postgres_exporter.service
           └─16984 /usr/local/bin/postgres_exporter --web.listen-address=:9187 --web.telemetry-path=/metrics

Mar 24 05:52:17 canttowin.ontadomain systemd[1]: Started Prometheus exporter for Postgresql.
Mar 24 05:52:17 canttowin.ontadomain postgres_exporter[16984]: time="2021-03-24T05:52:17-04:00" level=info msg="Established new database connection...go:777"
Mar 24 05:52:17 canttowin.ontadomain postgres_exporter[16984]: time="2021-03-24T05:52:17-04:00" level=info msg="Semantic Version Changed on \"10.0....o:1229"
Mar 24 05:52:18 canttowin.ontadomain postgres_exporter[16984]: time="2021-03-24T05:52:18-04:00" level=info msg="Starting Server: :9187" source="pos...o:1437"
Hint: Some lines were ellipsized, use -l to show in full.

7. Now we can check status if postgres_exporter (as a target) state information and other details. This we can check it through Prometheus web GUI.

As we have configured postgres_exporter and mapped it with Prometheus, we can also look for many of the expressions that it has created. This you can access on ‘graph’ page under Prometheus GUI console.

You can check it’s immediate results in both graphical or in console itself.

Alright, we have installed and configured Grafana and have added PostgreSQL and Prometheus as a data source and have configured postgres_exporter metric collector too. Time to generate some graphs. Well, you can do it by creating manual queries under new dashboards or else you can use any of prebuild Grafana dashboard templates for PostgreSQL i.e. 6742 or even 9628 (there are many more on Grafana labs repo).

1. Lets Import dashboard with ID 6742. Go to option with + sign on the left panel and choose last option ‘import’.

2. In next screen, pass dashboard ID 6742 and press button Load. You can also use JSON file entries instead of ID, I will use ID here in this sample configuration. Here select ‘Prometheus’ from the drop down list and click ‘Import’ and it’s all set!

3. Now this will bring the final dashboard showing lot’s of PostgreSQL metrics and their current values.

Hope It Helped!
Prashant Dixit

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

Display SQLID for a statement, no need to use Dynamic Views …

Posted by FatDBA on March 22, 2021

Hi Guys,

I have been busy lately, so couldn’t write much, but I have few things which I will be soon sharing in the form of blog posts, so stay tuned! Alright, so this one is about one of the feature which was though introduced in Oracle 18c, but I first tried it on 19c while doing a recent migration (few stories from that episode too!) – This is to display the SQLID for the currently executed SQL/PLSQL’s.

It’s pretty simple and a really cool feature, so, now you need not to query V$SQL, V$SQLAREA or even V$SESSION to get the SQLID of the last SQL. You only need to set the ‘SET FEEDBACK ON SQL_ID‘ and it’s done, it will first execute your statement and will display the SQLID at the end.

Let me show how to get that, I am doing this test on Oracle 19.3.0.0.0.

SQL>
SQL> SET FEEDBACK ON SQL_ID
SQL>
SQL> select name, open_mode, DBID, database_role from v$database;

NAME      OPEN_MODE                  DBID DATABASE_ROLE
--------- -------------------- ---------- ----------------
DIXITDB   READ WRITE            805967680 PRIMARY

1 row selected.

SQL_ID: buv1x6g781ug2
SQL>

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: | Leave a Comment »

repmgr failover done, but where is my Standby ?

Posted by FatDBA on December 15, 2020

Hey Guys,

Last weekend I’ve got a call from one of my friend asking about a weird issue in his master-slave (primary-standby) setup where he’s using repmgr for automatic failover and switchover operations. He said after the failover (server crashed) with role reversal or promotion the old standby becomes new primary (expected, all good till here), but as soon as the old primary comes back online and rejoins the configuration, it started as a primary too. I mean you can imagine something like below, both of the two hosts becomes master and there is no sign of a new standby.


[postgresdb@fatdba ~]$ repmgr -f /etc/repmgr.conf cluster show

 ID | Name  | Role    | Status    | Upstream | Location | Connection string
----+-------+---------+-----------+----------+----------+-----------------------------------------
 1  | node1 | primary | * running |          | default  | host=db_node1 dbname=repmgr user=repmgr
 2  | node2 | primary |   running |          | default  | host=db_node2 dbname=repmgr user=repmgr
 

He checked repmgr logs on the standby node and everything looks okay, standby promotion was sucessfull. It seems something was stopping the repmgr to update the information and bring that crashed node (previous primary) back as a standby. So the final situation is there are two primary nodes at this point.

So, how to and where to start from ?
A quick solution to this problem is to create an UPDATE TRIGGER on one of the internal table EVENTS of repmgr database (it creates its own database with default name of repmgr). The logic of creating a trigger is to handle events “standby_promote”, I mean when it happens a script is triggered that will bring back the old primary as new standby.

Next we would be needing a script that will help to automate the standby promotion. For that purpose you can use this script (link) or you can write something similar. And now when we have to call a bash script or to write stored procedures in a shell, we need the ‘.plsh’ extension in the database. Let’s install one of the most popular extension to do that, petere/plsh (link)


[postgresdb@fatdba ~]$ cd /usr/share/postgresql/12/extension/
[postgresdb@fatdba ~]$ sudo git clone https://github.com/petere/plsh.git
[postgresdb@fatdba ~]$ cd plsh
[postgresdb@fatdba ~]$ sudo make PG_CONFIG=/usr/bin/pg_config
[postgresdb@fatdba ~]$ sudo apt-get install make
[postgresdb@fatdba ~]$ sudo apt-get install gcc
[postgresdb@fatdba ~]$ sudo apt-get install postgresql-server-dev-12
[postgresdb@fatdba ~]$ sudo make install PG_CONFIG=/usr/bin/pg_config
 

Now, next create the extension in the database.


[postgresdb@fatdba ~]$ psql -U repmgr
 

This one needs to be executed only n primary side.


repmgr=# create extension plsh;
 

Now, let’s create the function and a trigger to invoke the script failover_promote.sh


[postgresdb@fatdba ~]$ psql
postgres=# CREATE FUNCTION failover_promote() RETURNS trigger AS $$
#!/bin/sh
/bin/bash /tmp/psqlscripts/failover_promote.sh $1 $2
$$
LANGUAGE plsh;
 

And the UPDATE TRIGGER on EVENTS table to call function created above.


postgres=#  create trigger failover
after insert
on events
for each row
execute procedure failover_promote();
 

Yes, that’s it. All done!
Now you can mimic the failover scenario i.e by stopping the primary database and check repmgr logs on the standby server.
You will see the standby is promoted and becomes new primary and as soon it’s finished, it will start pinging the crashed node and the moment it is back it will be promoted as a new standby.

Hope It helped!
Prashant Dixit

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

 
%d bloggers like this: