How to Load or Import a .csv File to a PostgreSQL Table

If you need to populate a PostGreSQL table with data that comes in .csv (comma-separated values) file format, here are the steps to do so:

1.  Login to PostGreSQL with your user that can access and manipulate the table

psql -h dba-works-01 -U dbaworks salesdb

2.  Use the copy command to populate the table with data from .csv file

COPY transactions FROM '/path-to-csv-file/transactions.csv' WITH (FORMAT csv);

NOTE that you can use COPY when you have superuser root privileges.

OR you can use \COPY if you do not have superuser access.

\COPY transactions FROM '/path-to-csv-file/transactions.csv' 

DELIMITER ',' CSV

If you want to learn about PostgreSQL or update your knowledge about it, you can check out this book below:

PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database

CSV Photo by Mika Baumeister on Unsplash
  

Add a Command Alias to Linux User Profile

Problem:  I'm tired of typing long commands in the Linux command prompt whenever I want something done. Not only that, typing these commands is prone to typo errors.

Solution: Add a command alias to our user profile.

Here's how:

1.  Edit your Linux OS user profile (or .bash_profile)  and add a command alias

dbaworks@dbasrv1:~$ vi .profile

-- add the command alias

# set PATH so it includes user's private bin directories

PATH="$HOME/bin:$HOME/.local/bin:$PATH"

alias check_mongo='ps -ef | grep mongo'

2.  Reload your OS user profile

dbaworks@dbasrv1:~$ . .profile

3.  You can now run the command alias and get the desired result:

dbaworks@dbasrv1:~$ check_mongo

--- output

dbaworks@dbasrv1:~$ check_mongo 
mongod 9655 1 1 Jun29 ? 04:15:13 /usr/bin/mongod -- configsvr --replSet csReplSet1 --port 27050 -f /data/mongod.conf --bind_ip_all 

Learn more about Linux shell scripting using this guide book:

The Linux Command Line : A Complete Introduction

 



Linux Shell Photo by Gabriel Heinzer on Unsplash
  

Shell Script to Automatically Create an AMI Backup of an EC2 Instance (AWS)

Requirements: 

- Access to a Linux machine where shell script will be created

- Access keys to an Amazon Web Services (AWS) Account where the EC2 Instance is hosted

Script name: ami-bash.sh

Input: Name of the instance

How to run: ami-backup.sh <instance_name>

What it does: 

1.  Asks for INSTANCE_NAME

2.  Sets the LOG directory and LOGFILE to record execution

3.  Runs the AWS CLI describe-instances to extract the INSTANCE_ID

4.  Sets the name of the AMI IMAGE_NAME  

5.  Runs the AWS CLI create-image to create the AMI

6.  Runs a loop to check if the image has been created and available

Finished result: An AMI backup of the instance

Script:

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

# name:  ami-backup.sh
# usage: ami-backup.sh <instance_name>
#        ami-backup.sh my-db-instance

INSTANCE_NAME=$1

LOGDIR=/tmp/log
LOGFILE=${LOGDIR}/ami-backup-$INSTANCE_NAME-$(date +"%Y-%m-%d-%H-%M-%S").log

INSTANCE_ID=`aws ec2 -describe-instances --filters \
    "Name=tag:Name,Values=$INSTANCE_NAME" \
    | grep "InstanceId" \
    | grep -Po ': "\K[^"]*'`
IMAGE_NAME=ami-backup-$INSTANCE_NAME-$(date +"%Y-%m-%d-%H-%M")

echo "$(date +"%Y-%m-%d-%H:%M:%S") ami-backup.sh started." >> $LOGFILE
echo "$IMAGE_NAME AMI will be created." >> $LOGFILE

IMAGE_ID=`aws ec2 create-image --instance-id $INSTANCE_ID \
     --name "$IMAGE_NAME" \
     --description "$INSTANCE_NAME AMI - $(date +"%Y-%m-%d-%H-%M")" \
     | grep -Po ': "\K[^"]*'`

while [ true ]
do
   echo "AMI creation in progress..." >> $LOGFILE
   IMAGE_CREATION_STATUS=`aws ec2 describe-images --filters \
      "Name=name,Values=$IMAGE_NAME" | grep "State" | grep -Po ': "\K[^"]*'`
      echo "AMI $IMAGE_NAME created." >> $LOGFILE
      break
   fi
   sleep 300
done

echo "$(date +"%Y-%m-%d-%H:%M:%S") ami-backup.sh completed." >> $LOGFILE

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

AWS Command Line Interface User Guide



The Linux Command Line : A Complete Introduction

 
Free Book: AWS Command Line Interface: User Guide Kindle Edition



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

Amazon Photo by Igor Shalyminov on Unsplash