fbpx

AWS

How to Fix Pg_ctl command not found

by

Sometimes after a fresh PostgreSQL install if we want to start or stop from PostgreSQL rather than controlling the PostgreSQL instance start and stop from the root. And when we run the pg_ctl from the command prompt as a PostgreSQL Linux user we see the error bash: pg_ctl: command not found… 

In this article, lets learn how to fix it

As PostgreSQL check your pg base directory 

 

And now add this PostgreSQL to the Linux path so that it’s always available. I would recommand to create a postgresql env. file so that it more easy to handle rather than messing with other systems runing on linux operating system.

				
					--Step 1 
-bash-4.2$ echo $PGDATA
/var/lib/pgsql/10/data

--Step 2
-bash-4.2$ echo "PATH=/usr/pgsql-10/bin:$PATH">>~/.postgre_10.profile

--Step 3
chmod 777 .postgre_10.profile

--Step 4 
. .postgre_10.profile

--Step 5
Check pg_ctl now

-bash-4.2$ pg_ctl --version
pg_ctl (PostgreSQL) 10.17
-bash-4.2$

HA Proxy For MySQL Master – Slave

by

There are scenarios where we have to provide the high ability to MySQL database instances and we use the Master and Slave replication method of MySQL database.

In the same case to segregate the Read and Write database traffic. We widly use HA- Proxy. It is a feature rich open source Load blancing tool with many unique features like reverse proxy but in out case we are going to use it only for Hight aviliblity purpose.

				
					root@haproxy01:~# haproxy -v
HA-Proxy version 2.0.13-2ubuntu0.3 2021/08/27 - https://haproxy.org/

 

How to Install it?

You simply use yum or apt commands to install it

				
					sudo apt install -y haproxy

 

Check  the version 

 

 

 

 

Install Mysql Client for HA Proxy Node to communicate with mysql master and slave databases.

 

				
					apt-get install -y mysql-client
cd /etc/haproxy/
cp haproxy.cfg haproxy.cfg_org
vim haproxy.cfg

				
			
				
					root@haproxy01:~# cat /etc/haproxy/haproxy.cfg
global
	log 127.0.0.1 local0 notice
        log /dev/log    local0
  	user haproxy
	group haproxy

	# Default SSL material locations

defaults
	log global
	mode tcp
        option tcplog
	retries 2
	timeout client 30m
	timeout connect 4s
    	timeout server 30m
	timeout check 5s

listen stats
        mode http
        bind *:9201
        stats enable
        stats uri /stats
        stats realm Strictly\ Private
        stats auth admin:admin

listen mysql-cluster
       bind *:3306
       mode tcp
       option mysql-check user haproxy_user
       balance roundrobin
       server master 192.168.56.205:3306 check
       server slave1 192.168.56.206:3306 check

listen mysql-cluster1
    bind 192.168.1.208:3306
    mode tcp
    option mysql-check user haproxy_user
    balance roundrobin
    server mysql-1 192.168.1.205:3306 check
    server mysql-2 192.168.1.206:3306 check

Create HA proxy user on mysql01/205 on primary node

				
					GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'%' IDENTIFIED BY 'Oracle@123' WITH GRANT OPTION;

flush privileges;

Test the configuration and it should start without error & Target should come up on GUI

				
					haproxy -f /etc/haproxy/haproxy.cfg -db

systemctl restart haproxy.service

Check HA proxy GUI and see all the MySQL target is up and running fine using HA Proxy Admin link:

HA Proxy Link Structure:

http://<localhost or IP/HostName/stats

http://192.168.1.208:9201/stats

Default Credentials : 

UserName : admin

Password: admin

Read More...

How to Rebuild MongoDB Replica-Set Node Fast in Few Minutes

by

Sometimes it happens that the MongoDB replica set node goes out of sync from replica set members and due to high lag, it cannot synchronise itself from the replica set members.

There are traditional ways to rebuild the node, like removing the replica set node from the cluster and adding it back, but the issue arises when your database size is in TBs, and node sync takes hours or days. Also, in some cases, it degrades the performance. You use the following steps and rebuild your replica set member in a few minutes.

Step1

Remove the node from cluster

				
					Prod:PRIMARY> rs.remove("<IP Address or hostname of the node to be removed>:27017")
Step2

login to ssh to Secondary Node and delete all the content of mongodb directory folder 

Step3

Log in to the Primary node. If you don’t know who is primary run this command rs. slave(ok)  from any node and then run rs.status(). rs. status will give you the [rimary node IP address or Hostname. Just ssh or login to primary and shut the MongoDB services

				
					--Stop MongoDB

--linux 
service mongod stop
or 
sudo systemctl stop mongodb

or 

mongod --dbpath /path/to/your/db --shutdown


-- Windows
net stop MongoDB

--MongoDB prompt
> use admin
> db.shutdownServer();

or 

mongo --eval "db.getSiblingDB('admin').shutdownServer()"

-- Mac

ps -ef| grep -i mongo
kill -9 <pid></pid>
Step 4

Zip/tar the MongoDB data folder from the Primary node and Copy or SCP to the secondary node Data folder according to mongo.conf. And start the MongoDB services of primary node

Step 5

Start the MongoDB process and if it fails check the MongoDB log. If required inrse the MongoDB service timeout to 800 (TimeoutStartSec=800) and reload the process using systemctl daemon-reload. Once MongoDB services are running fine add the node from the primary. After node addition check synchronization delay using rs. printslavereplicationinfo() and once it’s zero you are GOOD!!!.

				
					Prod:PRIMARY> rs.add("<IP Address or hostname of the node to be removed>:27017")


rs.printslavereplicationinfo()

Read More...

Free Database Client For PostGresql

by

1. pgAdmin

Single pack with Database client, multiple Database Administration tools and monitoring capabilities. You can also perform Backup and recovery from this tool.

2. dbeaver

One of my favourite tools to query remote databases with 100 different database connectivity options. And its 100% free with frequent big fixed and updates 

3. HeidiSQL

OpenSource and Rich database client with many database connectivity options. Frequent bug fixed and stable.

Read More...

Essential AWS Services for Database Administrators to Learn

by

Why AWS?

Cloud is becoming a vital part of Database Administration because it provides various database services & Infrastructure to run Database Ecosystems instantly. AWS (Amazon Web Services) is one of the pioneers in the Cloud according to the Gartner magic quadrant. Knowing more cloud infrastructure technologies is going to give more mileage to your Administrator career. In this article, you will find some of the AWS services which Database Administrators should know as they are basic to run Database opration.

Essential AWS Services List For Database Administrator (DBA)

Network
VPCs
Subnets
Elastic IPs
Internet Gateways
Network ACLs
Route Tables
Security Groups
Private Subnets
Public Subnets
AWS Direct Connect

Virtual Machine 
EC2
AWS Work Space

Storage
EBS
EFS
S3

Database as Services (RDS)
MySQL / MariaDB
PostgreSQL
Oracle
Micrsoft SQL Server
AWS Aurora PostgreSQL/MySQL

Database Managed Services
AWS Dynamo DB
AWS Elasticsearch 
Amazon DocumentDB

Messaging & Event Base Processing 
Apache Kafka (Amazon MSK)

 

Warehousing/ OLAP /Analytics Stagging DB
AWS Redshift

 

Monitoring 
Cloud watch
Amazon Grafana
Amazon Prometheus

 

Email Service
Amazon Simple Notification Service

Security 
IAM
Secrets Manager

Database Task Automation
AWS Batch
AWS Lambda
Cloud Formation

Command-line interface (CLI) to Manage AWS Services
AWSCLI

Migration 
Database Migration Service

Budget 
AWS Cost Explorer
AWS Budgets

Some other Services & Combination worth of Exploring

Bastion Host For DBA
MongoDB running on EC2
ELK (Elastic Search , LogStach, Kibana) running on EC2
Tunnels for Non stranded ports for Database Connections for more security
pg_pool or pg_Bouncer for PostgreSQL Databases

Stay Tuned For Latest Database, Cloud & Technology Trends

Read More >>

MySQL query output to csv

by

There are situations when we have to run the select queries on MySQL databases and redirect the output in .cav format. This is required when we have to share the data with the developer or send it to some ETL job for external tables. In addition, if you are running MySQL on AWS RDS you get limited options to get the MySQL query output to CSV. As an alternative, you can use the AWS S3 bucket but again it’s a complex and cost-involved way of getting MySQL query output to CSV. I am going to show you an easy alternative that will work on a normal Linux command prompt. Using this you can fetch a long SQL output in CSV from MySQL RDS or from on promises MySQL Database instance.

 

Step 1: log in to any jump host or bastion host

ssh [email protected]

Step 2: if your SQL output is going to take a long time use screen

screen -S mysql2csv_date

Step 3 Run MySQL export batch command and sed Linux command

mysql -A -h testdb01.us-east-1.rds.amazonaws.com -u root -p --batch -e "select * from testdb01.table_x where date_created >='2021-07-01 00:00:00'  AND LENGTH(emp_id) < 30 order by date_created;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > mysq2csv_output.csv

Step 4: once the command is completed you will see the output in .csv under the file mysq2csv_output.csv

Hope you like the article

References: https://dev.mysql.com/doc/refman/8.0/en/select-into.html

 

Read More

Take MySQL backup From Jenkins Job

How to fix ORA-28368: cannot auto-create wallet
AWS Services and their Azure alternatives 
How to Manage AWS S3 Bucket with AWS CLI

How to connect PostgreSQL Database from PgAdmin
How to create AWS rds PostgreSQL Database
Convert pem to ppk
How to Install PuTTy on Window
How to Install and create AWS EC2 Instance using Terraform
AWS MySQL RDS Database Creation using AWSCLI
How to Create MySQL Database with AWS RDS
How to connect to AWS MySQL / MariaDB RDS or EC2 database from MySQL WorkBench