fbpx

Automation

How to Resize PostgreSQL rds Disk Size Using awscli commands

by

To resize the disk size of a PostgreSQL RDS instance using the AWS CLI, you need to follow these steps but before that try to get the used and free space by running these commands from my artical

1.First, stop the RDS instance.

				
					aws rds stop-db-instance --db-instance-identifier <instance_name>

Modify the RDS instance’s disk size using the modify-db-instance command:

				
					aws rds modify-db-instance --db-instance-identifier <instance_name> --allocated-storage <new_size_in_gb>

start the instance

				
					aws rds start-db-instance --db-instance-identifier <instance_name>
Note: The size change will only occur when you stop and start the instance. Also, back up your data before performing any disk size changes.

Copy Table From one PostgreSQL RDS to Another using Python Boto3

by

To copy a table from one PostgreSQL RDS instance to another using Python and boto3, you will need to:

  1. Install the Python library psycopg2, which is a PostgreSQL adapter for Python. You can do this by running pip install psycopg2 in your terminal.

  2. Import the necessary modules:​

				
					import boto3
import psycopg2

  1. Connect to both the source and target RDS instances using the psycopg2 library. You will need to specify the host, port, database name, username, and password for each RDS instance.

  2. Once you have established connections to both RDS instances, you can use the psycopg2 library to create a cursor object for each connection. A cursor allows you to execute PostgreSQL commands and retrieve data.

  3. Use the cursor to execute a SELECT statement on the source table. This will retrieve all of the rows from the table.

  4. Iterate over the rows returned by the SELECT statement, and use the cursor for the target RDS instance to execute an INSERT statement for each row. This will copy the data from the source table to the target table.

Here is some sample code that demonstrates how to do this:

				
					# Connect to the source RDS instance
conn1 = psycopg2.connect(host="source-rds-instance.abc123.us-east-1.rds.amazonaws.com", port=5432, database="mydatabase", user="myuser", password="mypassword")
cur1 = conn1.cursor()

# Connect to the target RDS instance
conn2 = psycopg2.connect(host="target-rds-instance.abc123.us-east-1.rds.amazonaws.com", port=5432, database="mydatabase", user="myuser", password="mypassword")
cur2 = conn2.cursor()

# Select all rows from the source table
cur1.execute("SELECT * FROM mytable")

# Iterate over the rows and insert them into the target table
for row in cur1:
    cur2.execute("INSERT INTO mytable VALUES (%s, %s, %s)", row)

# Commit the changes to the target RDS instance
conn2.commit()

# Close the cursors and connections
cur1.close()
conn1.close()
cur2.close()
conn2.close()

I hope this helps! Let me know if you have any questions.

Python Script to Check PostgreSQL Database Running Status?

by

Here is a Python script that you can use to check the health of a PostgreSQL database:

				
					import psycopg2

def check_database_health(host, port, user, password, database):
    # Connect to the database
    conn = psycopg2.connect(host=host, port=port, user=user, password=password, database=database)
    cursor = conn.cursor()

    # Run a simple query to check the health of the database
    cursor.execute("SELECT 1")
    result = cursor.fetchone()

    # Check the result of the query
    if result == (1,):
        print("Database is healthy")
    else:
        print("Database is not healthy")

# Test the function with some sample connection details
check_database_health(host="localhost", port=5432, user="postgres", password="password", database="mydatabase")

This script uses the psycopg2 library to connect to the PostgreSQL database and run a simple query (SELECT 1). If the query returns the expected result ((1,)), the database is considered to be healthy. If the query returns any other result, the database is considered to be not healthy.

You can customize this script by replacing the connection details (host, port, user, password, and database) with your own values and by adding additional checks to ensure the health of the database.

I hope this helps! Let me know if you have any questions.

How to Build Application and MySQL RDS using Terraform

by

Here is a basic example of how you can use Terraform to create an EC2 instance with Apache and an RDS MySQL database. Keep in mind that this is just a starting point and you will likely need to customize it to fit your specific needs.

First, you will need to define the provider for AWS:

				
					provider "aws" {
  access_key = "ACCESS_KEY"
  secret_key = "SECRET_KEY"
  region     = "REGION"
}

Next, you can define the EC2 instance resource:

				
					resource "aws_instance" "example" {
  ami           = "AMI_ID"
  instance_type = "t2.micro"
  key_name      = "KEY_NAME"

  security_groups = [
    "SECURITY_GROUP_NAME"
  ]

  user_data = <<-EOF
              #!/bin/bash
              yum update -y
              yum install -y httpd
              service httpd start
              chkconfig httpd on
              echo "Hello, World!" > /var/www/html/index.html
              EOF
}

Next, you can define the EC2 instance resource:a

This will create an EC2 instance using the specified AMI, with Apache installed and running. The user_data script will run when the instance is launched, installing and starting Apache, and creating a simple “Hello, World!” index page.

To create an RDS MySQL database, you can use the following resource definition:

				
					resource "aws_db_instance" "example" {
  allocated_storage    = 10
  storage_type         = "gp2"
  engine               = "mysql"
  engine_version       = "5.7"
  instance_class       = "db.t2.micro"
  name                 = "DB_NAME"
  username             = "DB_USERNAME"
  password             = "DB_PASSWORD"
  parameter_group_name = "default.mysql5.7"
  skip_final_snapshot  = true
  vpc_security_group_ids = [
    "SECURITY_GROUP_ID"
  ]
}

This will create an RDS MySQL database with the specified parameters.

You will also need to create an output to display the public IP address of the EC2 instance:

				
					output "instance_ip" {
  value = "${aws_instance.example.public_ip}"
}

With these resources defined, you can use Terraform to create the EC2 instance and RDS MySQL database by running the following commands:

				
					terraform init
terraform apply

This will create the EC2 instance and RDS MySQL database, and display the public IP address of the EC2 instance. You can then visit this IP address in a web browser to see the “Hello, World!” page.

I hope this helps! Let me know if you have any questions or need further assistance.

See the Full Code:

				
					provider "aws" {
  access_key = "ACCESS_KEY"
  secret_key = "SECRET_KEY"
  region     = "REGION"
}

resource "aws_instance" "example" {
  ami           = "AMI_ID"
  instance_type = "t2.micro"
  key_name      = "KEY_NAME"

  security_groups = [
    "SECURITY_GROUP_NAME"
  ]

  user_data = <<-EOF
              #!/bin/bash
              yum update -y
              yum install -y httpd
              service httpd start
              chkconfig httpd on
              echo "Hello, World!" > /var/www/html/index.html
              EOF
}

resource "aws_db_instance" "example" {
  allocated_storage    = 10
  storage_type         = "gp2"
  engine               = "mysql"
  engine_version       = "5.7"
  instance_class       = "db.t2.micro"
  name                 = "DB_NAME"
  username             = "DB_USERNAME"
  password             = "DB_PASSWORD"
  parameter_group_name = "default.mysql5.7"
  skip_final_snapshot  = true
  vpc_security_group_ids = [
    "SECURITY_GROUP_ID"
  ]
}

output "instance_ip" {
  value = "${aws_instance.example.public_ip}"
}

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 >>

Take MySQL backup From Jenkins Job

by

Take MySQL Database Backup From Jenkins

In this post, we will explain and practically show how you can configure MySQL database backup using Jenkins jobs. in easy words, you will automate the MySQL database backup process from Jenkins GUI and MySQL dump command. Normally we use MySQL dump from CLI or using the windows option. In this case, just automate the MySQL dump triggering from a shell script which will be called by Jenkins job with parameter. MySQL dump needs few parameters to run just pass them by build with parameter option in jenkins.

 

1. Log to Jenkins default URL and port: http://192.168.56.21:8080/

If you want to install and setup Jenkins see this article

2. Go to New items

3.  Give some Name and select FreeStyle project

 

4.  In the build section click on Add build step radio button and select Execute shell.

5. Select boolean parameter for MySQL database host and database superuser password which is root password in MySQL database case.

 

6.  In this build part give the shell script name with full path and parameters like $HOST_IP & $Password

 

Shell Script

[root@master01 ~]# cat /opt/jenkins_scripts/mysql_full_db_backup.sh
#!/bin/bash
HOST_IP=$1
PASSWORD=$2
echo "starting mysql database full backup"
mysqldump -h $HOST_IP --all-databases --single-transaction --quick --lock-tables=false > /opt/jenkins_scripts/mysql_backup/full_backup_$(date +%F_%N).sql -u root -p$PASSWORD
echo "Backup has been done"

 

[su_box title=”IMP Note” box_color=”#fe2227″ title_color=”#101112″]Note: Make sure Jenkins have execute (chmod +x /opt/jenkins_scripts/mysql_full_db_backup.sh)  privilege on script[/su_box]

 

7. Let’s execute the Jenkins job

7.1  click on the the MySQL database backup job

 

7.2  Click on build with Parameter

 

7.3  Give your database server name or IP. In my case Mysql database running on the same server that’s thy, I will give localhost and database root password

 

7.4 Once your job is executed go to that job open it in console output mode and check the status of the job. And you are DONE!!!

Read More

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