Adding a linux user/group and modifying user's primary group

Adding a linux user/group and modifying the primary group of the user.

Steps:

[root@localhost ~]# groupadd bigdata

[root@localhost ~]# grep -i bigdata /etc/group
bigdata:x:1001:
[root@localhost ~]#

[root@localhost ~]# useradd -m hadoop

[root@localhost ~]# id hadoop
uid=1001(hadoop) gid=1002(hadoop) groups=1002(hadoop)

[root@localhost ~]# ls -altr /home/hadoop
total 12
-rw-r--r--. 1 hadoop hadoop 231 Mar 31  2020 .bashrc
-rw-r--r--. 1 hadoop hadoop 193 Mar 31  2020 .bash_profile
-rw-r--r--. 1 hadoop hadoop  18 Mar 31  2020 .bash_logout
drwxr-xr-x. 4 root   root    35 Mar  1 02:00 ..
drwx------. 2 hadoop hadoop  62 Mar  1 02:00 .

[root@localhost ~]# usermod -g bigdata hadoop

[root@localhost ~]# ls -altr /home/hadoop
total 12
-rw-r--r--. 1 hadoop bigdata 231 Mar 31  2020 .bashrc
-rw-r--r--. 1 hadoop bigdata 193 Mar 31  2020 .bash_profile
-rw-r--r--. 1 hadoop bigdata  18 Mar 31  2020 .bash_logout
drwxr-xr-x. 4 root   root     35 Mar  1 02:00 ..
drwx------. 2 hadoop bigdata  62 Mar  1 02:00 .

[root@localhost ~]#

[root@localhost ~]# passwd hadoop
Changing password for user hadoop.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@localhost ~]#

<<<<<<<<<<< set a strong password

Now logged in as hadoop.

Last failed login: Mon Mar  1 02:06:41 EST 2021 from gateway on ssh:notty
Last login: Mon Mar  1 02:06:20 2021

[hadoop@localhost ~]$

[hadoop@localhost ~]$ ls -altr
total 16
-rw-r--r--. 1 hadoop bigdata 231 Mar 31  2020 .bashrc
-rw-r--r--. 1 hadoop bigdata 193 Mar 31  2020 .bash_profile
-rw-r--r--. 1 hadoop bigdata  18 Mar 31  2020 .bash_logout
drwxr-xr-x. 4 root   root     35 Mar  1 02:00 ..
-rw-------. 1 hadoop bigdata   5 Mar  1 02:06 .bash_history
drwx------. 2 hadoop bigdata  83 Mar  1 02:06 .
[hadoop@localhost ~]$

This closes this blog.Thanks

Vagrant box - authentication issues

Vagrant box - authentication issues

Box Os: Centos 7.9
Kernel: 3.10.0-1160.6.1.el7.x86_64

Symptoms:
1) Authentication Failure after setting up the box with insecure key on first reboot (vagrant halt/up/ssh). This is expected since the Vagrant will replace the insecure key on first boot.

    default: SSH auth method: private key
    default: Warning: Authentication failure. Retrying...
    default: Warning: Authentication failure. Retrying...

2) Even after generating a new key using the below method and copying the public key to the vagrant box the authorized_keys file will become zero bytes on restart (vagrant halt/up). This leads to authentication failure again.

$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/<useracnt>/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/<useracnt>/.ssh/id_rsa
Your public key has been saved in /home/<useracnt>/.ssh/id_rsa.pub
The key fingerprint is:
SHA256:7KVti+u55MszInXV0py5gkjyAucdhfWoE3R5NtgqUcs <useracnt>@<machineid>
The key's randomart image is:
+---[RSA 3072]----+
|      .+++       |
|     .oo++=      |
|      .oE+.= o   |
|  . o ++. o *    |
|   + =o+So.. .   |
|    o =oo+. .    |
|     o .+ o.     |
|    . .+o+ .     |
|     . oX*.      |
+----[SHA256]-----+

3) We then set config.ssh.insert_key = false in Vagranfile, this ensures the authorized_keys arent replaced. But still we get encryption related error as below.

The private key you're attempting to use with this Vagrant box uses
an unsupported encryption type. The SSH library Vagrant uses does not support
this key type. Please use `ssh-rsa` or `ssh-dss` instead. Note that
sometimes keys in your ssh-agent can interfere with this as well,
so verify the keys are valid there in addition to standard
file paths.

4) We then generate rsa key with advanced option as below... which generated RSA 4096 instead of RSA 3072. We copy the public key using ssh-copy-id, still the encryption message is thrown.

$ ssh-keygen -t rsa -b 4096
Generating public/private rsa key pair.
Enter file in which to save the key (/home/<useracnt>/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/<useracnt>/.ssh/id_rsa
Your public key has been saved in /home/<useracnt>/.ssh/id_rsa.pub
The key fingerprint is:
SHA256:hrSfT0367PoPlEMojHJZGJIbKdS9JadCDkdWiR2ayVs <useracnt>@<machineid>
The key's randomart image is:
+---[RSA 4096]----+
| ..o+B+=.        |
|  ooO=*=o  .     |
|   **oE*o . .    |
|    +*oo . . .   |
|    ..o S   =    |
|       o . = .   |
|        o o o    |
|         o o .   |
|          o+=..  |
+----[SHA256]-----+

Error:

The private key you're attempting to use with this Vagrant box uses
an unsupported encryption type. The SSH library Vagrant uses does not support
this key type. Please use `ssh-rsa` or `ssh-dss` instead. Note that
sometimes keys in your ssh-agent can interfere with this as well,
so verify the keys are valid there in addition to standard
file paths.

Now Solution:

5) Now after some search, we go through several solutions, none worked and I dont want to workaround the issue. Finally hit https://stackoverflow.com/questions/22922891/vagrant-ssh-authentication-failure solution offered by Sankari steps followed, it worked until resolving the encryption issue, one additional step as detailed below was needed for the fix.

    a. Generate the ssh-key pair fresh in the client centos machine...

[vagrant@localhost ~]$ ssh-keygen -t rsa -b 4096
Generating public/private rsa key pair.
Enter file in which to save the key (/home/vagrant/.ssh/id_rsa):
/home/vagrant/.ssh/id_rsa already exists.
Overwrite (y/n)? y
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/vagrant/.ssh/id_rsa.
Your public key has been saved in /home/vagrant/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:9FVGrkwfmAn7XD5jMBJ+tkG6C3zWLv4BcX/vlSkuYzI vagrant@localhost.localdomain
The key's randomart image is:
+---[RSA 4096]----+
|          o ..+  |
|         . * B   |
|        . * & +  |
|       o . # @ . |
|        S * B B .|
|         + + . ++|
|          o o. oo|
|         E =.....|
|          =.+.  .|
+----[SHA256]-----+

    b. Copy the public key to authorized_keys with 0600 permisson.
    c. Copy/Paste the private key to local cygwin64's .ssh/ directory.
    d. Try vagrant ssh now, it is still attempting to use the below private key [so the stackoverflow solution steps stop here]

debug1: Will attempt key: <windowsdrive>:/VBoxVms/vagrant_home/vcentos79_hivetest/.vagrant/machines/default/virtualbox/private_key  explicit

    e. Add the below config setting to Vagrantfile config.ssh.private_key_path = ['~/.ssh/id_rsa'], this seems to have done the trick.

debug1: Trying private key: <windowsdrive>:/cygwin64/home/<useracnt>/.ssh/id_rsa

    f. Now the vagrant ssh works fine.

debug1: client_input_global_request: rtype hostkeys-00@openssh.com want_reply 0
Last login: Mon Mar  1 01:01:08 2021 from gateway
[vagrant@localhost ~]$

    g. Try a vagrant halt/up. Should be fine.

$ vagrant halt
==> default: Attempting graceful shutdown of VM...
<useracnt>@<machineid> ~/dba/vcentos79_hivetest

$ vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Clearing any previously set forwarded ports...
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2222 (host) (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2222
    default: SSH username: vagrant
    default: SSH auth method: private key
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
==> default: Mounting shared folders...
    default: /vagrant => <windowsdrive>:/VBoxVms/vagrant_home/vcentos79_hivetest
==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`
==> default: flag to force provisioning. Provisioners marked to run always will still run.
<useracnt>@<machineid> ~/dba/vcentos79_hivetest

This closes this blog. Thank you for your visit.

How to stop/start a postgres db

Lab: How to stop/start a postgres db

As postgres user (recommendation is never allow direct connection to postgres, allow other users with su to switch to postgres user).

Start:

[postgres@localhost ~]$ pg_ctl -D /pgData/data -l logfile start
waiting for server to start.... done
server started

[postgres@localhost ~]$
Where /pgData/data is the datafile directory where the postgres database is stored
Examine the postgres db status:

[postgres@localhost ~]$ ps -ef|grep -i postgres
root      9284  9269  0 02:40 pts/0    00:00:00 su - postgres
postgres  9285  9284  0 02:40 pts/0    00:00:00 -bash
postgres  9319     1  0 02:42 ?        00:00:00 /pgBin/12.5/bin/postgres -D /pgData/data
postgres  9321  9319  0 02:42 ?        00:00:00 postgres: checkpointer
postgres  9322  9319  0 02:42 ?        00:00:00 postgres: background writer
postgres  9323  9319  0 02:42 ?        00:00:00 postgres: walwriter
postgres  9324  9319  0 02:42 ?        00:00:00 postgres: autovacuum launcher
postgres  9325  9319  0 02:42 ?        00:00:00 postgres: stats collector
postgres  9326  9319  0 02:42 ?        00:00:00 postgres: logical replication launcher
postgres  9327  9285  0 02:42 pts/0    00:00:00 ps -ef
postgres  9328  9285  0 02:42 pts/0    00:00:00 grep --color=auto -i postgres
[postgres@localhost ~]$

9319 is the parent process for the postgres process tree

[postgres@localhost ~]$ pg_ctl status -D /pgData/data
pg_ctl: server is running (PID: 9319)
/pgBin/12.5/bin/postgres "-D" "/pgData/data"
[postgres@localhost ~]$

Stop:

[postgres@localhost ~]$ pg_ctl -D /pgData/data stop
waiting for server to shut down.... done
server stopped
[postgres@localhost ~]$

Status:

[postgres@localhost ~]$ pg_ctl status -D /pgData/data
pg_ctl: no server running
[postgres@localhost ~]$

Steps to enable postgresql database to accept remote connections

Lab Excercise: Try to connect to our test database using test role/user through  pgAdmin version 4.29 console when postgres configured in a virtualbox with NAT network(port forwarded)

We need to do the following if we use virtualbox with NAT network alone.

OS details: Centos 7.9

Steps:

1) NAT network allow internet access but it doesnt allow incoming connections. Hence we setup port forwarding for it to work. Add a port forwarding in virtualbox console for the vm which we are testing.

127.0.0.1-54320 => VM's 5432 (ex. port for postgres)

2) Backup & Edit postgres.conf file, then enable both port & listen_address to '*'
3) Backup & Edit pg_hba.conf file, then make the below entry

host    all    all    10.0.0.0/16    md5

4) stop the firewall or add exception
a) stop the firewall - pgAdmin connects perfectly fine
b) Add exception via 
i) service [see below] - didnt work as expected the pgAdmin console was timing out always. Reason could be the netstat -plantu or ss -plantu didnt show the postgresql service against port we have postgres running, insteas it showed postgres. 

But firewall-cmd --get-service was showing postgresql as the service.

[root@localhost ~]# firewall-cmd --add-service=postgresql --permanent
success
[root@localhost ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources:
  services: dhcpv6-client ssh
  ports:
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:
[root@localhost ~]# firewall-cmd --reload
success
[root@localhost ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources:
  services: dhcpv6-client postgresql ssh
  ports:
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:
[root@localhost ~]#
[root@localhost ~]# firewall-cmd --get-service|tr " " "\n"
..
plex
pmcd
pmproxy
pmwebapi
pmwebapis
pop3
pop3s
postgresql
privoxy
..
zabbix-server
[root@localhost ~]#

ii) Try adding the port to exception - pgAdmin connects fine.

[root@localhost ~]# firewall-cmd --add-port=<port>/tcp --permanent
success
[root@localhost ~]# firewall-cmd --reload
success
[root@localhost ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources:
  services: dhcpv6-client ssh
  ports: <port>/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:
[root@localhost ~]#

Thanks for your visit to the blog.

Postgres Custom Installation to the directory we choose instead going with default rpm install [/usr/local/pgsql]

 Postgres Custom Installation to the directory we choose instead going with default rpm install [/usr/local/pgsql]

Environment: 

Centos 7.9 - x86_64

Vagrant box: 

Custom build box

Postgres version: 

12.5 (latest in rel 12 by the time of this documentation)

Install doc: 

https://www.postgresql.org/docs/12/install-procedure.html

List of options decided to customize:

1) Path of the postgres binary [--prefix=PREFIX]

2) Other options to include in configure [--with-pgport=1512 for version 12, --with-perl,--with-python for pl/perl & pl/python support]

Options understood but considered for later testing:

1) --with-segsize=SEGSIZE
2) --with-blocksize=BLOCKSIZE
3) --with-wal-blocksize=BLOCKSIZE
4) --enable-dtrace

Leaving them at their default values.

Postgres recommended options not considered for now:

This install is majorly focused on administration side learning rather than development.

1) --enable-debug
2) --enable-cassert

Prerequisites:

https://www.postgresql.org/docs/12/install-requirements.html

make version 3.80 or newer
GCC
tar, gzip, bzip2
Perl 5.8.3
Python 2.4 min.
openssl

Not a exhaustive list, but the ones I am intrested in.

Verification of the precheck:

[root@localhost ~]# make --version

GNU Make 3.82

Built for x86_64-redhat-linux-gnu

Copyright (C) 2010  Free Software Foundation, Inc.

License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>

This is free software: you are free to change and redistribute it.

There is NO WARRANTY, to the extent permitted by law.

[root@localhost ~]# which openssl

/usr/bin/openssl

[root@localhost ~]# perl --version

This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi

(with 41 registered patches, see perl -V for more detail)

[root@localhost ~]# python --version

Python 2.7.5

[root@localhost ~]#

So I have all the packages I need.

Directory structure and memory foot print:

[root@localhost ~]# df -h

Filesystem               Size  Used Avail Use% Mounted on

/dev/sdb1                2.1G   33M  2.1G   2% /tmp

/dev/mapper/dbvg-db00    2.0G   33M  2.0G   2% /pgBin

/dev/mapper/dbvg-db01    2.0G   33M  2.0G   2% /pgData

/dev/mapper/dbvg-db03    2.0G   33M  2.0G   2% /pgBackup

/dev/mapper/dbvg-db02    2.0G   33M  2.0G   2% /pgWal

[root@localhost ~]# free -k

              total        used        free      shared  buff/cache   available

Mem:        1882080      201560     1476888        8792      203632     1528440

Swap:       3774456           0     3774456

[root@localhost ~]#

Steps to install the postgres binary:

Step 1) Source download

Download the binary from https://www.postgresql.org/docs/12/install-getsource.html => https://www.postgresql.org/ftp/source/v12.5/

Step 2)  Decompress and unatar

[root@localhost pgbin]# ls -altr

total 133628
drwxrwxrwx. 1 vagrant vagrant      4096 Nov  9 17:39 postgresql-12.5
-rwxrwxrwx. 1 vagrant vagrant 136826880 Jan  9 03:46 postgresql-12.5.tar
drwxrwxrwx. 1 vagrant vagrant      4096 Jan  9 03:50 ..
drwxrwxrwx. 1 vagrant vagrant         0 Jan  9 03:51 .
[root@localhost pgbin]# find . -name 'configure'
./postgresql-12.5/configure
[root@localhost pgbin]#

Step 3) Create necessary directory

Create directory 12.5 under /pgBin mountpoint

Step 4) Launch configure command with necessary flags

cd /pgBin/src12.5/postgresql-12.5/

./configure --prefix=/pgBin/12.5 --with-pgport=1512 --with-perl --with-python

You may want to consider installing the below listed packages based on error.

Extra packages installing: 

yum install perl-ExtUtils-MakeMaker perl-ExtUtils-Embed

yum install readline-devel

yum install zlib-devel

yum install python-devel

You can examine config.log or on screen message, the config once finished successful move to the next step.

We have the below list of files freshly created:

-rw-r--r--.  1 root root  83906 Jan  9 04:19 config.log_readline

-rw-r--r--.  1 root root  71852 Jan  9 06:53 config.log_zlibdevel

-rwxr-xr-x.  1 root root  40367 Jan  9 07:01 config.status

-rw-r--r--.  1 root root   3998 Jan  9 07:01 GNUmakefile

drwxrwxrwx. 16 1107 1107   4096 Jan  9 07:01 src

-rw-r--r--.  1 root root 442216 Jan  9 07:01 config.log

drwxrwxrwx.  6 1107 1107   4096 Jan  9 07:01 .

[root@localhost postgresql-12.5]#

Complete list:

[root@localhost postgresql-12.5]# find . -mtime -1 -ls

2117696    4 drwxrwxrwx   6 1107     1107         4096 Jan  9 07:01 .

1316371    4 drwxrwxrwx  16 1107     1107         4096 Jan  9 07:01 ./src

3146075    4 drwxrwxrwx  31 1107     1107         4096 Jan  9 07:01 ./src/include

3148247   36 -rw-r--r--   1 root     root        32788 Jan  9 07:01 ./src/include/pg_config.h

3148248    4 -rw-r--r--   1 root     root            1 Jan  9 07:01 ./src/include/stamp-h

3148249    4 -rw-r--r--   1 root     root          323 Jan  9 07:01 ./src/include/pg_config_ext.h

3148250    4 -rw-r--r--   1 root     root            1 Jan  9 07:01 ./src/include/stamp-ext-h

3148252    0 lrwxrwxrwx   1 root     root           30 Jan  9 07:01 ./src/include/pg_config_os.h -> ../../src/include/port/linux.h

1317040    4 drwxrwxrwx   2 1107     1107         4096 Jan  9 07:01 ./src/interfaces/ecpg/include

3148251    4 -rw-r--r--   1 root     root          572 Jan  9 07:01 ./src/interfaces/ecpg/include/ecpg_config.h

1319139    4 -rw-r--r--   1 root     root            1 Jan  9 07:01 ./src/interfaces/ecpg/include/stamp-h

2119058    0 drwxrwxrwx   6 1107     1107          252 Jan  9 07:01 ./src/backend/port

2119591    0 lrwxrwxrwx   1 root     root           37 Jan  9 07:01 ./src/backend/port/tas.s -> ../../../src/backend/port/tas/dummy.s

2119592    0 lrwxrwxrwx   1 root     root           38 Jan  9 07:01 ./src/backend/port/pg_sema.c -> ../../../src/backend/port/posix_sema.c

2119593    0 lrwxrwxrwx   1 root     root           38 Jan  9 07:01 ./src/backend/port/pg_shmem.c -> ../../../src/backend/port/sysv_shmem.c

3148246   36 -rw-r--r--   1 root     root        34059 Jan  9 07:01 ./src/Makefile.global

1319140    0 lrwxrwxrwx   1 root     root           31 Jan  9 07:01 ./src/Makefile.port -> ../src/makefiles/Makefile.linux

2119586   84 -rw-r--r--   1 root     root        83906 Jan  9 04:19 ./config.log_readline

2119587   72 -rw-r--r--   1 root     root        71852 Jan  9 06:53 ./config.log_zlibdevel

2119588  432 -rw-r--r--   1 root     root       442216 Jan  9 07:01 ./config.log

2119590   40 -rwxr-xr-x   1 root     root        40367 Jan  9 07:01 ./config.status

3148245    4 -rw-r--r--   1 root     root         3998 Jan  9 07:01 ./GNUmakefile

[root@localhost postgresql-12.5]#

Examining a file to see what it has...

./src/Makefile.global

Snipped content: This shows that we chose pgBin/12.5 for our base path in the binary install and postgres configure created a make file which will be later used in the make process.

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

prefix := /pgBin/12.5

exec_prefix := ${prefix}

datarootdir := ${prefix}/share

bindir := ${exec_prefix}/bin

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

Step 5) Launch make command

make world

Other options are make or make all. Since make world included better coverage chose make world over all or just make.

Output: 

PostgreSQL, contrib, and documentation successfully made. Ready to install.

[root@localhost ~]# ls -altr /pgBin/12.5/

total 0

drwxr-xr-x. 2 root root  6 Jan  9 03:55 .

drwxr-xr-x. 4 root root 33 Jan  9 04:08 ..

[root@localhost ~]#

<<< so this directory is still empty

Step 6) make check

As non root user this step has to be done, so I chose vagrant which is the only other user I have in this step.

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

 All 194 tests passed.

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

this step created the below directory under src binary....

drwxrwxr-x.  4 vagrant vagrant     30 Jan  9 07:43 tmp_install

Step 7) make install-world

make install-world

Output:

...

PostgreSQL, contrib, and documentation installation complete.

<<<<<<<<<<<<<<<<< This completes installation.

Post Install

Step 1) Environment Variable config:

 The step below will make runtime library [after installation to enable the run-time linker to find the shared libraries faster].

Optional : /sbin/ldconfig /pgBin/12.5/lib

Environment variable to go into ~/.bash_profile

LD_LIBRARY_PATH=/pgBin/12.5/lib

export LD_LIBRARY_PATH

PATH=/pgBin/12.5/bin:$PATH

export PATH

MANPATH=/pgBin/12.5/share/man:$MANPATH

export MANPATH

Step 2) Adding postgres user

[root@localhost pgData]# adduser postgres

[root@localhost pgData]# id postgres

uid=1001(postgres) gid=1001(postgres) groups=1001(postgres)

[root@localhost pgData]#

Step 3) changing ownership of the data directory

mkdir /pgData/data

chown postgres:postgres /pgData/data

Step 4) Initialize the first postgres database

su - postgres

.bash_profile is edited with the variables we formed in step 1 of post install.

initdb -D /pgData/data

pg_ctl -D /pgData/data -l logfile start

createdb test

psql test

Output below:

[postgres@localhost ~]$ initdb -D /pgData/data

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

This user must also own the server process.

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

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

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

Data page checksums are disabled.

fixing permissions on existing directory /pgData/data ... ok

creating subdirectories ... ok

selecting dynamic shared memory implementation ... posix

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting default time zone ... America/New_York

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

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

You can change this by editing pg_hba.conf or using the option -A, or

--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /pgData/data -l logfile start

[postgres@localhost ~]$ pg_ctl -D /pgData/data -l logfile start

waiting for server to start.... done

server started

[postgres@localhost ~]$ createdb test

[postgres@localhost ~]$ psql test

psql (12.5)

Type "help" for help.

test=# \c

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

test=# \l

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

test=#

Understanding oracle RMAN backup footprint through python program

Dears,

I made an attempt here to chart the RMAN backup foot (useful in capacity planning visualisation) print based on recovery window based retention window.

The python code is very simple, but it took few hours to bring out the logic (algorithm) to chart the data.

Objective: 

In this blog, we will use python to plot RMAN backup foot print for n days (to my screen resolution I limited the max days to 40, but you can increase it if you wish by a simple code change) based on RMAN recovery window based retention.

Code:

############################

# variable declaration , value assignment and validation begins here
j=0
k=0

# backup recovery window
print('please enter the backup recovery window')
a=input()
m=int(a)

# backup data display window
while True:
    print('please enter the number of days to visualize the backup data')
    b=input()
    n=int(b)
    if n>40:
        print('please enter less than 40 days to visualize the backup data')
        continue
    else:
        break

# level 0 backup frequency (number of days once)
print('please enter level0 backup frequency')
c=input()
o=int(c)

# variable declaration ends here

#display the header
dstr='|||||BTYP||'
for i in range(n-1,0,-1):
    p=len(str(i))
    j=5-p-2
    dstr=dstr+' '*j+'D'+str(i)+'|'
k=len(dstr)
dstr2=dstr + ' LC | AC |'
v=len(dstr2)-k
print(dstr2)
#header display closes here

#backup data visualization
for i in range(1,n):
    if i%o==1:
        if i==1:
            l='L0+A|'
            t=l
        else:
            l='L0+A|' + l
            t='L0+A|'
    else:
        if (i%o>=2) and (i>o):
            if (m==o) and (i%m==2):
                l='   A|'*(o-1) + 'L0+A|'
                l='   A|' + 'L0+A|' + l
                t='   A|'
            elif (m<o) and (i%o==m+1):
                l='   A|'*m + 'L0+A|'
                t='   A|'
            elif (m>o) and (i>m) and (i%o==(m-6)%o):
                l='   A|'* (o-1) + 'L0+A|'
                u=int(m/o)
                l='   A|'* (m%o) + 'L0+A|' + l * u
                t='   A|'
            else:
                l='   A|' + l
                t='   A|'
        else:
            l='   A|' + l
            t='   A|'
    p=len(str(i))
    j=5-p-2
    q=' '*j + 'D' + str(i) + '|' + t
    r=k-len(q)
    s=q + ' '*(n-i-1)*5 + '|' + l
    w=len(s)
    d=l.count('L0')
    e=len(str(d))
    f=l.count('A')
    g=len(str(f))
    x=s + ' ' * (k-w) + ' ' * (5-e-1) + str(d) + '|' + ' ' * (5-g-1) + str(f) + '|'
    print (x)
#backup data visualization closes here

############################

Please note I didnt add data points for L1, I will post that as an enhancement. So this code can help with L0 and Archives. Also may be next to next version, I will see if I can make the data displayed as an image of printed as csv, so we can increase the days and recovery window to see (larger foot print).

Result:

please enter the backup recovery window

3

please enter the number of days to visualize the backup data

21

please enter level0 backup frequency

5

|||||BTYP|| D20| D19| D18| D17| D16| D15| D14| D13| D12| D11| D10|  D9|  D8|  D7|  D6|  D5|  D4|  D3|  D2|  D1| LC | AC |

  D1|L0+A|                                                                                               |L0+A|   1|   1|

  D2|   A|                                                                                          |   A|L0+A|   1|   2|

  D3|   A|                                                                                     |   A|   A|L0+A|   1|   3|

  D4|   A|                                                                                |   A|   A|   A|L0+A|   1|   4|

  D5|   A|                                                                           |   A|   A|   A|   A|L0+A|   1|   5|

  D6|L0+A|                                                                      |L0+A|   A|   A|   A|   A|L0+A|   2|   6|

  D7|   A|                                                                 |   A|L0+A|   A|   A|   A|   A|L0+A|   2|   7|

  D8|   A|                                                            |   A|   A|L0+A|   A|   A|   A|   A|L0+A|   2|   8|

  D9|   A|                                                       |   A|   A|   A|L0+A|                            1|   4|

 D10|   A|                                                  |   A|   A|   A|   A|L0+A|                            1|   5|

 D11|L0+A|                                             |L0+A|   A|   A|   A|   A|L0+A|                            2|   6|

 D12|   A|                                        |   A|L0+A|   A|   A|   A|   A|L0+A|                            2|   7|

 D13|   A|                                   |   A|   A|L0+A|   A|   A|   A|   A|L0+A|                            2|   8|

 D14|   A|                              |   A|   A|   A|L0+A|                                                     1|   4|

 D15|   A|                         |   A|   A|   A|   A|L0+A|                                                     1|   5|

 D16|L0+A|                    |L0+A|   A|   A|   A|   A|L0+A|                                                     2|   6|

 D17|   A|               |   A|L0+A|   A|   A|   A|   A|L0+A|                                                     2|   7|

 D18|   A|          |   A|   A|L0+A|   A|   A|   A|   A|L0+A|                                                     2|   8|

 D19|   A|     |   A|   A|   A|L0+A|                                                                              1|   4|

 D20|   A||   A|   A|   A|   A|L0+A|                                                                              1|   5|

>>> 

In the above example, I used 3 days of recovery window, 5 days once L0 occurrence and 21 days of data to visualise my foot print.

Below are the list of charts that everyone looks forward :)

Recovery window: 8 days

Backup data to display: 21 days

L0 backup occurencce: 7 days once

please enter the backup recovery window : 15

please enter the number of days to visualize the backup data: 30

please enter level0 backup frequency : 7

Thanks for your visit. Please provide your valuable feedbacks.

 

Use Oracle ASH to draw oracle session block chain

Updated on : 14 Jul 2019, since the original version released on 12Jul2019 had #2 bugs, which is fixed now

Objective: On our day to day DBA life, there are chances we might end up needing the db session block chain from the past, if it is current or live issue - then you have V$WAIT_CHAINS to see the block chain live.But if it is from the past, how do you pull it?

DBA_HIST_ACTIVE_SESS_HISTORY stores the information, but it needs some effort in mapping the block chain with the volume of data we have to process.

Instead that, I just used oracle's hierarchial query (thanks to Tanel Poder for sparking the idea in me) and some join tweeks to come up with the below sqls.

Version tested: 11.2.0.1


SQL1 - use the below row source [this query gives the sample sorted session, what it is doing along with what is its blocker and what is that doing, in case there is no information, it marks that session as IDLE - with username marked as NOSESSION]

with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 11:10:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID
,a.BLOCKING_SESSION
,a.BLOCKING_SESSION_SERIAL#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select * from dashmap
order by 1,2,3,4,5;

Output:

Query 2: To get a map of block chain and other session details [this query gives you a over view of all the sessions - i) sessions with no blockers ii) sessions with blockers available in ash. The only caveat is you wont get the sessions with blockers, but the blockers arent visible in ash, see the hierarchial query looks for 2 conditions, a) sessions with no blockers and b) sessions with blockers & such blockers are available in ash, I dont know today if there is way to get those sessions with blockers which arent available/visible in ash using hierarchial query]

with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 11:10:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select /*+ materialize */ user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID abino
,a.BLOCKING_SESSION absid
,a.BLOCKING_SESSION_SERIAL# abs#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select /* parallel(4) */
sampt
,ino
,sid
,s#
,stype
,pgm
,iuser
,sql
,event
,wc
,tw
,mach
,own
,obty
,obnm
,suobnm
,bino
,bsid
,bs#
,buser
,bsql
,bpgm
,bevent
,bwc
,btw
,bmach
,bown
,bobty
,bobnm
,bsuobnm
,RPAD('.', (level-1)*2, '.') ||ino||'~'||sid||'~'||s#||'~'||iuser AS tree
,level
,CONNECT_BY_ROOT (ino||'~'||sid||'~'||s#||'~'||iuser) AS root_id
,LTRIM(SYS_CONNECT_BY_PATH((ino||'~'||sid||'~'||s#||'~'||iuser), '->'),'->') AS path
,CONNECT_BY_ISLEAF AS leaf
,CONNECT_BY_ISCYCLE AS cycle
from dashmap
START WITH (abino is null
and absid is null
and abs# is null)
CONNECT BY NOCYCLE prior (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser) = (sampi||'~'||bino||'~'||bsid||'~'||bs#||'~'||buser)
ORDER SIBLINGS BY (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser)
;

Output:

Query 3: To filter out only the session blocking chain . Remeber to read the conditions the query works on from last (query#2) [this query will filter out only blocked sessions with their blocker details, kind of chain identity]

with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 12:05:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select /*+ materialize */ user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID abino
,a.BLOCKING_SESSION absid
,a.BLOCKING_SESSION_SERIAL# abs#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select *
from
(
select /* parallel(4) materialize */
sampt
,ino
,sid
,s#
,stype
,pgm
,iuser
,sql
,event
,wc
,tw
,mach
,own
,obty
,obnm
,suobnm
,bino
,bsid
,bs#
,buser
,bsql
,bpgm
,bevent
,bwc
,btw
,bmach
,bown
,bobty
,bobnm
,bsuobnm
,RPAD('.', (level-1)*2, '.') ||ino||'~'||sid||'~'||s#||'~'||iuser AS tree
,level lvl
,CONNECT_BY_ROOT (ino||'~'||sid||'~'||s#||'~'||iuser) AS root_id
,LTRIM(SYS_CONNECT_BY_PATH((ino||'~'||sid||'~'||s#||'~'||iuser), '->'),'->') AS path
,CONNECT_BY_ISLEAF AS lf
,CONNECT_BY_ISCYCLE AS cycle
from dashmap
START WITH (abino is null
and absid is null
and abs# is null)
CONNECT BY NOCYCLE prior (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser) = (sampi||'~'||bino||'~'||bsid||'~'||bs#||'~'||buser)
ORDER SIBLINGS BY (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser)
)
where (lvl>1)
;

Output: 

Remember the caveat: The only caveat  of query#2 & query#3 is you wont get the sessions with blockers, but the blockers aren't visible in ash, see the hierarchical query (#2 & #3) looks for 2 conditions, a) sessions with no blockers and b) sessions with blockers & such blockers are available in ash, I don't know today if there is way to get those sessions with blockers which aren't available/visible in ash using hierarchical query]



If you find it difficult to follow this blog, please bear with me, I will write more explanation. Also if you want the excel view of the data, please get in touch with me through feedback button.

Foot notes - 19Jul23 - to get the output formatted

set time on

set timing on

set lines 2000

set pages 49999

col sampt for a30

col pgm for a18

col iuser for a20

col event for a30

col mach for a30

col own for a30

col obty for a30

col obnm for a30

col subonm for a30

col buser for a20

col bpgm for a18

col bevent for a30

col bmach for a30

col bown for a30

col bobty for a30

col bobnm for a30

col bsuobnm for a30

col tree for a70

col root_id for a70

col path for a700

set colsep ,

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

As usual thanks for the visit :)
Have a good day.