Identifying PostgreSQL Bloat and fixing it without downtime

Original Post can be viewed at Identifying PostgreSQL Bloat and fixing it without downtime

MVCC (Multi-Version Concurrency Control) feature allows databases to provide concurrent access to data. This allows each SQL statement to see a snapshot of data as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same…

AskDba.org Weblog

Understanding Nested Lists Dictionaries of JSON in Python and AWS CLI


After lots of hair pulling, bouts of frustration, I was able to grasp this nested list and dictionary thingie in JSON output of AWS cli commands such as describe-db-instances and others. If you run the describe-db-instances for rds or describe-instances for ec2, you get a huge pile of JSON mumbo-jumpo with all those curly and square brackets studded with colons and commas. The output is heavily nested.


For example, if you do :

aws rds (more...)

S3 bucket size using aws cli

To get the total objects count and total size for folder within S3 bucket recursively –


aws s3 ls --summarize --human-readable --recursive s3://content-dev/mywork/dumb | grep 'Total'

Total Objects: 1922
   Total Size: 297.4 GiB

AWS: Accessing S3 buckets from the internet and from ec2 instances (2)

After accessing a S3 bucket from an ec2 instance by adding an IAM role to my EC2 instance, i tried to get the access from an ec2 instance by using a AWS endpoint.

First step: Launch an ec2 instance within your VPC and try to access your S3 bucket:

[ec2-user@ip-172-31-30-93 ~]$ wget https://s3-eu-west-1.amazonaws.com/my.webtest/website.json
--2019-02-02 18:29:28--  https://s3-eu-west-1.amazonaws.com/my.webtest/website.json
Auflösen des Hostnamen »s3-eu-west-1.amazonaws.com (s3-eu-west-1.amazonaws.com)«... 52.218. (more...)

AWS CloudFormation Linter Installation and Quick Introduction

AWS Cloudformation is an evolving managed service which facilitates infrastructure as a code in the cloud. What it means is that you can create AWS resources like EC2 instances, S3 buckets and many more just by writing code instead of using GUI console.



For instances, if you want to create 100 EC2 instances for production, and then later you have to create same for development and for testing and then may be later on, you (more...)

Step by Step Troubleshooting and Validation of AWS CloudFormation Yaml template

CloudFormation (CFN) is infrastructure as a code service of AWS. You just tell CFN your desired state of resources and it creates them in order while resolving dependencies. You mention these resources in a file called as template which can be written in YAML or JSON. YAML being bit more user readable, is widely used now. YAML is great but you have to be aware of its indentation.


Following is a step by step troubleshooting (more...)

AWS: overview of all postings

After nearly an half year of looking into AWS, here a summary of all postings related to that topic:
ECS & S3 (Computing & Storage):

When AWS SCT Unable to Connect to MySQL in RDS or EC2

AWS Schema Conversion Tool (SCT) is one of the must tool for a successful migration of databases to AWS RDS.

If you are trying to connect to MySQL hosted on EC2 instance or RDS and unable to make the connection despite of setting the security groups correctly and making sure that port, hostname, username and password are right, then first check the log of SCT.




2019-03-22 19:40:16.866 [   1]     GENERAL INFO    global_settings:
(more...)

Saving AWS RDS and Redshift Billing Costs using Reserved Instances

Original Post can be viewed at Saving AWS RDS and Redshift Billing Costs using Reserved Instances

In this post, we would understand what are reserved instances, when do you need them and various ways to monitor them in your environment. As more enterprises are embracing AWS cloud technologies, we need to understand AWS’s breadth of services and pricing options to right size our compute resources and run efficient infrastructure. Cost optimization…

AskDba.org Weblog

Updating Oracle Opatch with AWS Systems Manager Run Command at Scale

AWS Systems Manager (SSM) is a managed service by AWS, which is used to configure and manage EC2 instances, other AWS resources, and on-premise VMs/servers at scale. SSM frees you up from having ssh or bastion host access to the remote resources.


Pre-requisites of SSM:

The managed instances need to have SSM agent running.
The managed instances need to be assigned an IAM role with policy AmazonEC2RoleforSSM.
The managed insatnces need to have a meaningful (more...)

AWS: Accessing S3 buckets from the internet and from ec2 instances

After reading about endpoints for AWS S3 i was wondering how i can use this feature.

First step was to create a bucket and just tried to access this bucket over the internet:

$ aws s3api create-bucket --bucket my.webtest --region eu-west-1 --create-bucket-configuration LocationConstraint=eu-west-1
{
    "Location": "http://my.webtest.s3.amazonaws.com/"
}
And the i put this location into my browser:


This is like expected, because i did not allow public access for this (more...)

AWS: IAM & security – Best practices: Using a non-root user

After my successful solutions architect practice i knew that i had to take care of my shortcomings in security.
So i decided to visit the IAM (Identity and Access Managemen) of AWS:

  • Delete your root access keys
    I created them for using aws cli (installation or creating a static webpage)
  • Activate MFA on your root account
    No - i will not use multi factor authentication for my playground.
  • Create individual IAM users
    I (more...)

AWS: What services are free of charge? How to control your costs…(part 3)

After looking into my bill (see post 1 and post 2) i was keen, how fine granular the cost statistics are inside the aws web console. So i moved to the billing dashboard and to the cost explorer:
This provides you with the following dashboard:
You can play around with the settings to get for example something like this:




AWS Billing: Set an alarm to a cost threshold

Knowing about some details about the costs inside aws and some of the services (especially, that Docker, VPNs and Kubernetes are not included in the free trials)

i set an alarm on my account, to get informed about new costs:

So move to "Cloudwatch" and there you have to choose "Alarms":

 Then "Create Alarm":
 And click on "select (more...)

AWS: AWS Solutions Architect Associate – Practice

After reading the book AWS Certified Solutions Architect - Official Study Guide i decided to go for a online exam at https://aws.amazon.com/training/




I had to answer 25 question in about 30 minutes, which was quite exhausting. Only a few minutes after the exam i got the following mail:
Hmmm.
3.0 Specify Secure Applications and Architectures: 50%
An unconvincing result for this area, but with some more reading and more exercises i should (more...)

AWS: Logging? CloudTrail!

Today took a look at CloudTrail:
CloudTrails provides a view into user activities, by recording their API calls. On the AWS webpages you can find the following graphic:

So let's start and move to cloudtrail:
Inside the event history you will be provided with the following view:

Here you can see my efforts for the posting AWS: How to delete a static website via aws cli.
If you expand such an event, you (more...)

AWS: What services are free of charge? How to control your costs…(part 2)

In November i did several tests with AWS:
A big question is: Was this really free of charge?
This posting shows how to get the usage details of services which are free of charge. 

Here now some details about EKS, (more...)

AWS: What services are free of charge? How to control your costs…

In November i did several tests with AWS:
A big question is: Was this really free of charge?

Let's go to the Billing Dashboard:
On this page you will get this listing:
If you click on "View all", you get a (more...)

AWS: How to delete a static website via aws cli

After the creation of a static website in S3 via cli, now the deletion:

First try was:

$ aws s3api delete-bucket --bucket my.webtest

An error occurred (BucketNotEmpty) when calling the DeleteBucket operation: The bucket you tried to delete is not empt
Ok. This will not work. First get the objects:

$ aws s3api list-objects --bucket my.webtest
{
    "Contents": [
        {
            "LastModified": "2018-11-17T19:18:53.000Z",
            "ETag": "\"e56b419be959169c15260cd721735e47\"",
            "StorageClass": "STANDARD",
(more...)

Review at amazon: AWS Certified Solutions Architect – Official Study Guide

This weekend i read this book:

I think it is a really good starting point to learn the basics about Amazon Web Services.
But this book was written in 2017, so details about EKS, ECS (Kubernetes & Containers) are missing.

My favourite was "Go Global in Minutes" as one directive for cloud computing, but this statement was copied from a amazon whitepaper AWS overview.



If you are interested, take a look at my review (more...)