pgbadger session - 3: A first look of pgbadger report

 Let us try to generate a very simple pgbadger report

1. Run a simple query:

\c pgbenchdb

select pg_sleep(2);

2. launch the below command:

pgbager $PGDATA/log/postgresql-Sun.log

3. Download the out.html generated in the same directory from where the previous command was run.

4. Open the Report and navigate the report:

Flow diagram:

>> postgreslog --- pgbadger --> output in html format (JSON)

You can get an incremental updates as well for the report.

YouTube Video link:

pgbadger session 1 - installation of pgbadger

 Steps to install pgbadger

From pgBadger documentation: https://pgbadger.darold.net/documentation.html

1. Download the pgbadger from github

url: https://github.com/darold/pgbadger

2. Install using the below step

        tar xzf pgbadger-11.x.tar.gz

        cd pgbadger-11.x/

        perl Makefile.PL

        make && sudo make install

Instead I followed the below steps instead of 1 & 2:

mkdir pgbadger

git clone https://github.com/darold/pgbadger.git pgbadger

perl Makefile.PL

make && sudo make install

-bash-4.2$ git clone https://github.com/darold/pgbadger.git pgbadger

Cloning into 'pgbadger'...

remote: Enumerating objects: 5038, done.

remote: Counting objects: 100% (726/726), done.

remote: Compressing objects: 100% (297/297), done.

remote: Total 5038 (delta 458), reused 541 (delta 403), pack-reused 4312

Receiving objects: 100% (5038/5038), 12.47 MiB | 13.21 MiB/s, done.

Resolving deltas: 100% (3122/3122), done.

-bash-4.2$

-bash-4.2$ perl Makefile.PL

Checking if your kit is complete...

Looks good

Writing Makefile for pgBadger

-bash-4.2$

As root:

[root@vcentos79-postgres-ha1 pgbadger]# make && sudo make install

which: no pod2markdown in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)

Makefile:824: You must install pod2markdown to generate README.md from doc/pgBadger.pod

echo "=head1 SYNOPSIS" > doc/synopsis.pod

./pgbadger --help >> doc/synopsis.pod

echo "=head1 DESCRIPTION" >> doc/synopsis.pod

sed -i.bak 's/ +$//g' doc/synopsis.pod

rm doc/synopsis.pod.bak

sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod

sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod

rm doc/pgBadger.pod.bak

Manifying blib/man1/pgbadger.1p

rm doc/synopsis.pod

which: no pod2markdown in (/sbin:/bin:/usr/sbin:/usr/bin)

Makefile:824: You must install pod2markdown to generate README.md from doc/pgBadger.pod

echo "=head1 SYNOPSIS" > doc/synopsis.pod

./pgbadger --help >> doc/synopsis.pod

echo "=head1 DESCRIPTION" >> doc/synopsis.pod

sed -i.bak 's/ +$//g' doc/synopsis.pod

rm doc/synopsis.pod.bak

sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod

sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod

rm doc/pgBadger.pod.bak

Manifying blib/man1/pgbadger.1p

Installing /usr/local/share/man/man1/pgbadger.1p

Installing /usr/local/bin/pgbadger

Appending installation info to /usr/lib64/perl5/perllocal.pod

rm doc/synopsis.pod

[root@vcentos79-postgres-ha1 pgbadger]# echo $?

0

[root@vcentos79-postgres-ha1 pgbadger]#

Error#1:

-bash-4.2$ perl Makefile.PL

Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1.

BEGIN failed--compilation aborted at Makefile.PL line 1.

Resolution:

yum install perl-devel

...

Transaction test succeeded

Running transaction

  Installing : gdbm-devel-1.10-8.el7.x86_64                                                                                                                             1/10

  Installing : pyparsing-1.5.6-9.el7.noarch                                                                                                                             2/10

  Installing : systemtap-sdt-devel-4.0-13.el7.x86_64                                                                                                                    3/10

  Installing : perl-ExtUtils-Manifest-1.61-244.el7.noarch                                                                                                               4/10

  Installing : perl-Test-Harness-3.28-3.el7.noarch                                                                                                                      5/10

  Installing : libdb-devel-5.3.21-25.el7.x86_64                                                                                                                         6/10

  Installing : perl-ExtUtils-MakeMaker-6.68-3.el7.noarch                                                                                                                7/10

  Installing : perl-ExtUtils-Install-1.58-299.el7_9.noarch                                                                                                              8/10

  Installing : 4:perl-devel-5.16.3-299.el7_9.x86_64                                                                                                                     9/10

  Installing : 1:perl-ExtUtils-ParseXS-3.18-3.el7.noarch                                                                                                               10/10

  Verifying  : 1:perl-ExtUtils-ParseXS-3.18-3.el7.noarch                                                                                                                1/10

  Verifying  : libdb-devel-5.3.21-25.el7.x86_64                                                                                                                         2/10

  Verifying  : perl-Test-Harness-3.28-3.el7.noarch                                                                                                                      3/10

  Verifying  : perl-ExtUtils-Install-1.58-299.el7_9.noarch                                                                                                              4/10

  Verifying  : perl-ExtUtils-Manifest-1.61-244.el7.noarch                                                                                                               5/10

  Verifying  : systemtap-sdt-devel-4.0-13.el7.x86_64                                                                                                                    6/10

  Verifying  : pyparsing-1.5.6-9.el7.noarch                                                                                                                             7/10

  Verifying  : gdbm-devel-1.10-8.el7.x86_64                                                                                                                             8/10

  Verifying  : perl-ExtUtils-MakeMaker-6.68-3.el7.noarch                                                                                                                9/10

  Verifying  : 4:perl-devel-5.16.3-299.el7_9.x86_64                                                                                                                    10/10

Installed:

  perl-devel.x86_64 4:5.16.3-299.el7_9

Dependency Installed:

  gdbm-devel.x86_64 0:1.10-8.el7                          libdb-devel.x86_64 0:5.3.21-25.el7                       perl-ExtUtils-Install.noarch 0:1.58-299.el7_9

  perl-ExtUtils-MakeMaker.noarch 0:6.68-3.el7             perl-ExtUtils-Manifest.noarch 0:1.61-244.el7             perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7

  perl-Test-Harness.noarch 0:3.28-3.el7                   pyparsing.noarch 0:1.5.6-9.el7                           systemtap-sdt-devel.x86_64 0:4.0-13.el7

Complete!

Error#2:

-bash-4.2$ make && sudo make install

which: no pod2markdown in (/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/usr/pgsql-15/bin:/usr/pgsql-15/lib)

Makefile:824: You must install pod2markdown to generate README.md from doc/pgBadger.pod

cp pgbadger blib/script/pgbadger

/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pgbadger

echo "=head1 SYNOPSIS" > doc/synopsis.pod

./pgbadger --help >> doc/synopsis.pod

echo "=head1 DESCRIPTION" >> doc/synopsis.pod

sed -i.bak 's/ +$//g' doc/synopsis.pod

rm doc/synopsis.pod.bak

sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod

sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod

rm doc/pgBadger.pod.bak

Manifying blib/man1/pgbadger.1p

rm doc/synopsis.pod

We trust you have received the usual lecture from the local System

Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.

    #2) Think before you type.

    #3) With great power comes great responsibility.

[sudo] password for postgres:

postgres is not in the sudoers file.  This incident will be reported.

-bash-4.2$

Fix: Run the same command as root

[root@vcentos79-postgres-ha1 pgbadger]# make && sudo make install

which: no pod2markdown in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)

Makefile:824: You must install pod2markdown to generate README.md from doc/pgBadger.pod

echo "=head1 SYNOPSIS" > doc/synopsis.pod

./pgbadger --help >> doc/synopsis.pod

echo "=head1 DESCRIPTION" >> doc/synopsis.pod

sed -i.bak 's/ +$//g' doc/synopsis.pod

rm doc/synopsis.pod.bak

sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod

sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod

rm doc/pgBadger.pod.bak

Manifying blib/man1/pgbadger.1p

rm doc/synopsis.pod

which: no pod2markdown in (/sbin:/bin:/usr/sbin:/usr/bin)

Makefile:824: You must install pod2markdown to generate README.md from doc/pgBadger.pod

echo "=head1 SYNOPSIS" > doc/synopsis.pod

./pgbadger --help >> doc/synopsis.pod

echo "=head1 DESCRIPTION" >> doc/synopsis.pod

sed -i.bak 's/ +$//g' doc/synopsis.pod

rm doc/synopsis.pod.bak

sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod

sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod

rm doc/pgBadger.pod.bak

Manifying blib/man1/pgbadger.1p

Installing /usr/local/share/man/man1/pgbadger.1p

Installing /usr/local/bin/pgbadger

Appending installation info to /usr/lib64/perl5/perllocal.pod

rm doc/synopsis.pod

[root@vcentos79-postgres-ha1 pgbadger]# echo $?

0

[root@vcentos79-postgres-ha1 pgbadger]#

3. Verify if the pgbadger file is accessible, if so from where

-bash-4.2$ which pgbadger

/usr/local/bin/pgbadger

-bash-4.2$ ls -altr /usr/local/share/man/man1/

total 48

drwxr-xr-x. 21 root root   243 Mar 19  2023 ..

-r--r--r--.  1 root root 45127 Jan 28 06:38 pgbadger.1p

drwxr-xr-x.  2 root root    25 Jan 28 06:38 .

-bash-4.2$ pgbadger --help

Usage: pgbadger [options] logfile [...]

    PostgreSQL log analyzer with fully detailed reports and graphs.

Arguments:

..

YouTube video:

PostgreSQL: Schema Copy from one DB to other

Objective: Perform schema copy in postgresql from one db to other

Method: pg_dump/pg_restore

Backup dump destination: /pgwal/logicalbkp (4GB space left)

pg version: 14.7 (source) | 15.2 (target)

PGDATA: /pgdata/14/data (14.7) | /pgdata/15/data (15.2)

Schema: test

DB Name: pgbenchdb

option to use: -n, --schema=PATTERN         dump the specified schema(s) only

Steps:

1) Perform a backup of the schema (5432 port)

Command:

\dn+

\dt test.*

select * from test.table1;

output:

postgres=# \c pgbenchdb

psql (15.2, server 14.7)

You are now connected to database "pgbenchdb" as user "postgres".

pgbenchdb=# \dn+

                          List of schemas

  Name  |  Owner   |  Access privileges   |      Description

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

 public | postgres | postgres=UC/postgres+| standard public schema

        |          | =UC/postgres         |

 test   | postgres |                      |

(2 rows)

pgbenchdb=# \dt test.*

        List of relations

 Schema | Name | Type  |  Owner

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

 test   | tbl1 | table | postgres

(1 row)

pgbenchdb=#

Backup Command:

pg_dump \

-d pgbenchdb \

-U postgres \

-p 5432 \

-n test \

-v > /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql 2>/pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log

Backup Command Output:

-bash-4.2$ pg_dump \

> -d pgbenchdb \

> -U postgres \

> -p 5432 \

> -n test \

> -v > /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql 2>/pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log

-bash-4.2$

2) Verify the backup for any errors

ls -altr /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql

grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log

Output:

-bash-4.2$ ls -altr /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql

-rw-r--r--. 1 postgres postgres 1222 Aug 16 22:31 /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql

-bash-4.2$ grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log

-bash-4.2$ view /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log

-bash-4.2$ view /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql

-bash-4.2$

3) Perform a restore of the schema in target (same server - 5433 port)

Before restore:

postgres=# \c pgbenchdb

You are now connected to database "pgbenchdb" as user "postgres".

pgbenchdb=# \dn+

                          List of schemas

  Name  |  Owner   |  Access privileges   |      Description       

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

 public | postgres | postgres=UC/postgres+| standard public schema

        |          | =UC/postgres         | 

(1 row)

Restore Command:

psql -p 5433 \

-d pgbenchdb \

-U postgres \

-f /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql \

-L /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log

Restore Command Output:

-bash-4.2$ psql -p 5433 \

> -d pgbenchdb \

> -U postgres \

> -f /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql \

> -L /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log

SET

SET

SET

SET

SET

 set_config

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

(1 row)

SET

SET

SET

SET

CREATE SCHEMA

ALTER SCHEMA

SET

SET

CREATE TABLE

ALTER TABLE

COPY 4

4) Validate for any error

grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log

Output:

-bash-4.2$ view /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log

-bash-4.2$ grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log

SET client_min_messages = warning;

-bash-4.2$

5) Verify the table accessibility in target:

Command:

\dn+

\dt test.*

select * from test.table1;

Output:

postgres=# \c pgbenchdb

You are now connected to database "pgbenchdb" as user "postgres".

pgbenchdb=# \dn+

                          List of schemas

  Name  |  Owner   |  Access privileges   |      Description

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

 public | postgres | postgres=UC/postgres+| standard public schema

        |          | =UC/postgres         |

 test   | postgres |                      |

(2 rows)

pgbenchdb=# \dt test.*

        List of relations

 Schema | Name | Type  |  Owner

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

 test   | tbl1 | table | postgres

(1 row)

pgbenchdb=# select * from test.tbl1;

 id | name

----+------

  1 | R

  1 | R

  1 | R

  1 | R

(4 rows)

pgbenchdb=#

YouTube Video of the same:

Using pg_dump & pg_restore to backup & restore a database in postgres (including permission verify)

 Dears,

In this blog, we will backup/restore a database. In addition we will also verify if the permission we granted to an object in the database to an user is restored properly or not.

Steps @ highlevel:

Create a DB
Grant permisson to a user in a schema present in the db
pg_dump to back it up
Drop the DB
Then try restoring the DB using pg_restore
Check the user permisson

Setup:
Create a database:

postgres=# create database pgdmptst;
CREATE DATABASE
postgres=#

Connect to the db:

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

Create a schema:

pgdmptst=# create schema sch_pgdmptst;
CREATE SCHEMA
pgdmptst=#

Create a table:

create table sch_pgdmptst.pgtst_tbl1
(
id int PRIMARY KEY
,name text
);
INSERT INTO sch_pgdmptst.pgtst_tbl1
SELECT i, lpad('TST',mod(i,100),'CHK')
FROM generate_series (1,10000) s(i);

pgdmptst=# create table sch_pgdmptst.pgtst_tbl1
pgdmptst-# (
pgdmptst(# id int PRIMARY KEY
pgdmptst(# ,name text
pgdmptst(# );
CREATE TABLE

pgdmptst=# INSERT INTO sch_pgdmptst.pgtst_tbl1
pgdmptst-# SELECT i, lpad('TST',mod(i,100),'CHK')
pgdmptst-# FROM generate_series (1,10000) s(i);
INSERT 0 10000

pgdmptst=# select count(1) from sch_pgdmptst.pgtst_tbl1;
 count
-------
 10000
(1 row)
pgdmptst=#

Grant permission:

pgdmptst=# grant select on sch_pgdmptst.pgtst_tbl1 to barman;
GRANT
pgdmptst=#

Now let us backup this schema:

https://www.postgresql.org/docs/current/app-pgdump.html

Option: -Fc

pg_dump -Fc pgdmptst >/pgBACKUP/pgdump/26nov22_pgdmptst_scn1.dmp

-bash-4.2$ pg_dump -Fc pgdmptst >/pgBACKUP/pgdump/26nov22_pgdmptst_scn1.dmp
-bash-4.2$ ls -altr
total 36
drwxr-xr-x. 3 postgres postgres    20 Nov 26 16:40 ..
drwxr-xr-x. 2 postgres postgres    39 Nov 26 16:40 .
-rw-r--r--. 1 postgres postgres 33574 Nov 26 16:40 26nov22_pgdmptst_scn1.dmp
-bash-4.2$

Let us drop the database:

postgres=# drop database pgdmptst;
DROP DATABASE
postgres=#

Check if the database is no more:

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileg
es
-----------+----------+----------+-------------+-------------+------------+-----------------+------------------
-----
 pgtst_db  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres
    +
           |          |          |             |             |            |                 | postgres=CTc/post
gres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres
    +
           |          |          |             |             |            |                 | postgres=CTc/post
gres
(4 rows)
postgres=#

Restore the database:

Command: pg_restore -C -d postgres 26nov22_pgdmptst_scn1.dmp --verbose

Actual output:

-bash-4.2$ pg_restore -C -d postgres 26nov22_pgdmptst_scn1.dmp --verbose
pg_restore: connecting to database for restore
pg_restore: creating DATABASE "pgdmptst"
pg_restore: connecting to new database "pgdmptst"
pg_restore: creating SCHEMA "sch_pgdmptst"
pg_restore: creating TABLE "sch_pgdmptst.pgtst_tbl1"
pg_restore: processing data for table "sch_pgdmptst.pgtst_tbl1"
pg_restore: creating CONSTRAINT "sch_pgdmptst.pgtst_tbl1 pgtst_tbl1_pkey"
pg_restore: creating ACL "sch_pgdmptst.TABLE pgtst_tbl1"

Verify the restore:

-bash-4.2$ psql
psql (15.0)
Type "help" for help.
postgres=# \c pgdmptst
You are now connected to database "pgdmptst" as user "postgres".

pgdmptst=# \dt
Did not find any relations.

pgdmptst=# \dn
         List of schemas
     Name     |       Owner
--------------+-------------------
 public       | pg_database_owner
 sch_pgdmptst | postgres
(2 rows)

pgdmptst=# set search_path='sch_pgdmptst';
SET

pgdmptst=# select count(1) from pgtst_tbl1;
 count
-------
 10000
(1 row)

pgdmptst=#

Now let us examine if barman has its permisson restored...

Command: select * from information_schema.table_privileges where lower(grantee)='barman';

pgdmptst=# select * from information_schema.table_privileges where lower(grantee)='barman';
 grantor  | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarch
y
----------+---------+---------------+--------------+------------+----------------+--------------+--------------
--
 postgres | barman  | pgdmptst      | sch_pgdmptst | pgtst_tbl1 | SELECT         | YES          | YES
(1 row)
pgdmptst=#

So the barman permisson is back :)

This closes this blog.

Thanks