Database , Application and Fusion Technology : June 2022

Monday, June 20, 2022

PostgreSQL hot standby. ( DR solution for PostgreSQL)

I followed below steps to create the hot standby.

PRIMARY SERVER - 192.168.230.50

STANDBY SERVER - 192.168.230.51

On Primary:
===========

postgres=# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'H1j@ck123';

CREATE ROLE

postgres=# \du

List of roles

Role name | Attributes | Member of

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

hecm_read | Password valid until infinity | {}

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

read_user | | {readaccess}

readaccess | Cannot login | {}

replica | Replication | {}

Change below settings on postgresql.conf:

archive_mode = on

#archive_command = 'true'

archive_command = 'cp -i %p /u01/PostgreSQL/10/data/archive/%f'

hot_standby = on

max_wal_size = 1GB

max_wal_senders = 10

wal_level = hot_standby

wal_log_hints = on

wal_keep_segments = 10

wal_receiver_status_interval = 5s

hot_standby_feedback = on

listen_addresses = '*'

Add below entries in pg_hba.conf

host replication replica 192.168.230.51/24 md5

On Standby:
===========

Set below parameters in postgresql.conf:

archive_mode = on

#archive_command = 'true'

archive_command = 'cp -i %p /u01/PostgreSQL/10/data/archive/%f'

hot_standby = on

max_wal_size = 1GB

max_wal_senders = 10

wal_level = hot_standby

wal_log_hints = on

wal_keep_segments = 10

wal_receiver_status_interval = 5s

hot_standby_feedback = on

-bash-4.2$ telnet 192.168.230.50 5432

Trying 192.168.230.50...

telnet: connect to address 192.168.230.50: No route to host

Firewall disabled on Primary so it able to connect from standby

-bash-4.2$ ./pg_basebackup -h 192.168.230.50 -D /u01/PostgreSQL/10/data -U replica -v -P

Password:

pg_basebackup: initiating base backup, waiting for checkpoint to complete

pg_basebackup: checkpoint completed

pg_basebackup: write-ahead log start point: 0/15000028 on timeline 1

pg_basebackup: starting background WAL receiver

293182/293182 kB (100%), 1/1 tablespace

pg_basebackup: write-ahead log end point: 0/15000130

pg_basebackup: waiting for background process to finish streaming ...

pg_basebackup: base backup completed

[root@pgstand ~]# cd /u01/PostgreSQL/10/data/

[root@pgstand data]# cat recovery.conf

standby_mode = 'on'

primary_conninfo = 'host=192.168.230.50 port=5432 user=replica password=replicauser@'

restore_command = 'cp ///u01/PostgreSQL/10/data/archive/%f %p'

trigger_file = '/tmp/postgresql.trigger.5432'

-bash-4.2$ ./pg_ctl start -D /u01/PostgreSQL/10/data

waiting for server to start....2020-06-18 18:15:52.293 IST [4117] LOG: listening on IPv4 address "0.0.0.0", port 5432

2020-06-18 18:15:52.293 IST [4117] LOG: listening on IPv6 address "::", port 5432

2020-06-18 18:15:52.295 IST [4117] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

2020-06-18 18:15:52.406 IST [4117] LOG: redirecting log output to logging collector process

2020-06-18 18:15:52.406 IST [4117] HINT: Future log output will appear in directory "log".

done

server started

Now standby is ready as a hot standby.

Check replication status on Primary:

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

postgres=# \x

Expanded display is on.

Testing:
========

Create an object in Primary and see if that replicates to Secondary:

On Primary:
===========

postgres=# create database newtestdb;

CREATE DATABASE

On Standby:
===========

postgres=# \l

List of databases

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

postgres=# \c newtestdb

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

newtestdb=# \dt+ t

newtestdb=# create table D ( name text);

ERROR: cannot execute CREATE TABLE in a read-only transaction

Thanks,

Thursday, June 16, 2022

MySQL 8 installation on RHEL7 64bit using RPM - ( MYSQL installation using RPM )

Below can be followed to install MYSQL 8 on RHEL7 64bit

[root@pgprim PG_RPMS]# ls -ltr mysq*

-rw-r--r--. 1 7155 31415 14668996 Mar 24 17:13 mysql-community-client-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 2574424 Mar 24 17:13 mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 648680 Mar 24 17:13 mysql-community-common-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 445936940 Mar 24 17:14 mysql-community-debuginfo-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 1949700 Mar 24 17:14 mysql-community-devel-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 4214948 Mar 24 17:14 mysql-community-embedded-compat-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 2216040 Mar 24 17:15 mysql-community-icu-data-files-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 1575404 Mar 24 17:15 mysql-community-libs-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 683148 Mar 24 17:15 mysql-community-libs-compat-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 55984868 Mar 24 17:15 mysql-community-server-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 22960584 Mar 24 17:16 mysql-community-server-debug-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 7155 31415 270450256 Mar 24 17:17 mysql-community-test-8.0.29-1.el7.x86_64.rpm

-rw-r--r--. 1 root root 823879680 May 30 20:30 mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar

[root@pgprim PG_RPMS]# rpm -ivh mysql-community-common-8.0.29-1.el7.x86_64.rpm

warning: mysql-community-common-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY

Preparing... ################################# [100%]

Updating / installing...

1:mysql-community-common-8.0.29-1.e################################# [100%]

[root@pgprim PG_RPMS]# rpm -ivh mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm

warning: mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY

Preparing... ################################# [100%]

Updating / installing...

1:mysql-community-client-plugins-8.################################# [100%]

[root@pgprim PG_RPMS]# rpm -ivh mysql-community-libs-8.0.29-1.el7.x86_64.rpm

warning: mysql-community-libs-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY

error: Failed dependencies:

mariadb-libs is obsoleted by mysql-community-libs-8.0.29-1.el7.x86_64

[root@pgprim PG_RPMS]# rpm -e mariadb-libs*

error: package mariadb-libs* is not installed

[root@pgprim PG_RPMS]# rpm -qa| grep mariadb-libs

mariadb-libs-5.5.35-3.el7.x86_64

[root@pgprim PG_RPMS]# rpm -e mariadb-libs-5.5.35-3.el7.x86_64

error: Failed dependencies:

libmysqlclient.so.18()(64bit) is needed by (installed) qt-mysql-1:4.8.5-8.el7.x86_64

libmysqlclient.so.18()(64bit) is needed by (installed) perl-DBD-MySQL-4.023-5.el7.x86_64

libmysqlclient.so.18()(64bit) is needed by (installed) net-snmp-1:5.7.2-18.el7.x86_64

libmysqlclient.so.18()(64bit) is needed by (installed) postfix-2:2.10.1-6.el7.x86_64

libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) qt-mysql-1:4.8.5-8.el7.x86_64

libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) perl-DBD-MySQL-4.023-5.el7.x86_64

libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) postfix-2:2.10.1-6.el7.x86_64

mariadb-libs(x86-64) = 1:5.5.35-3.el7 is needed by (installed) mariadb-1:5.5.35-3.el7.x86_64

mariadb-libs(x86-64) = 1:5.5.35-3.el7 is needed by (installed) mariadb-server-1:5.5.35-3.el7.x86_64

mysql-libs is needed by (installed) net-snmp-1:5.7.2-18.el7.x86_64

[root@pgprim PG_RPMS]# rpm -e --allmatches mariadb-libs-5.5.35-3.el7.x86_64