connect

 

New Script with Mail Feature

#!/usr/bin/env bash
# script to start / stop postgres DB for airflow.
source  ~/.sapiens_profile
export PATH=/cdwapps/local/pgsql/bin:$PATH
export PGDATA=/cdwsapps/airflow/database
export PGPORT=5433
PG_LOG_FILE=/cdwapps/airflow/logs/postgres.log
num_trys=0
if [ $# -ne 1 ]
then
    echo -e "Need atleast one Action be specified start or stop"
    echo -e "Syntax to execute the script: ./pg-control.sh start|stop"
    exit
fi
if [ $1 == "start" ]
then
    while [[ ${num_trys} -lt 3 ]];do
        if pg_ctl status; then
            echo -e "postgre is running for PGDATA Location ${PGDATA} \n"
            break
        else
            ((num_trys++))
            echo -e "Airflow PG ${PGDATA} not running \n"
            echo -e "Attempt: ${num_trys} to restart..."
            pg_ctl start -D ${PGDATA} -l ${PG_LOG_FILE}
            echo -e "Waiting 30 seconds for DB to come up..."
            sleep 30
            if pg_ctl status; then
                echo -e "PG ${PGDATA} has been started successfully..."
                break
            elif [ ${num_trys} == 2 ]; then
                echo -e "Hello" > /tmp/airflow-fail-alert
                echo -e "Airflow PostgreSQL Failed to Start after configured retries" >> /tmp/airflow-fail-alert
                echo -e "Please check and take immediate action" >> /tmp/airflow-fail-alert
                echo -e "Thanks\nTHIS IS AUTO GENERATED EMAIL. DO NOT REPLY" >> /tmp/airflow-fail-alert
                mail -s "ALERT - Airflow PostgreSQL failed to start" < /tmp/airflow-fail-alert
            fi
        fi
    done
elif [ $1 == "stop" ]
then
    while [[ ${num_trys} -lt 3 ]];do
        if ! pg_ctl status; then
            echo -e "postgre is running for PGDATA Location ${PGDATA} \n"
            echo -e "Attempt: ${num_trys} to stop..."
            pg_ctl stop -D ${PGDATA} -t 180
            if [ $? -eq 0 ]
            then
                echo -e "Stop command Successful."
            else
                echo -e "Hello" > /tmp/airflow-fail-alert
                echo -e "Airflow PostgreSQL Stop command Failed" >> /tmp/airflow-fail-alert
                echo -e "Please check and take immediate action" >> /tmp/airflow-fail-alert
                echo -e "Thanks\nTHIS IS AUTO GENERATED EMAIL. DO NOT REPLY" >> /tmp/airflow-fail-alert
                mail -s "ALERT - Airflow PostgreSQL Stop Command Failed" < /tmp/airflow-fail-alert
                
            fi
            echo -e "Waiting 30 seconds for DB Gracefully shutdown..."
            sleep 30
            if ! pg_ctl status; then
                echo -e "PG ${PGDATA} has been stopped successfully..."
                break
            fi
            
            ((num_trys++))
        else
            break
        fi
    done
fi

 

 

Old Script

#!/usr/bin/env bash
# script to start / stop postgres DB for airflow.

source  ~/.sapiens_profile

export PATH=/cdwapps/local/pgsql/bin:$PATH

export PGDATA=/cdwsapps/airflow/database

export PGPORT=5433

PG_LOG_FILE=/cdwapps/airflow/logs/postgres.log

num_trys=0

if [ $# -ne 1 ]
then
    echo -e "Need atleast one Action be specified start or stop"
    echo -e "Syntax to execute the script: ./pg-control.sh start|stop"
    exit
fi

if [ $1 == "start" ]
then
    while [[ ${num_trys} -lt 3 ]];do
        if pg_ctl status; then
            echo -e "postgre is running for PGDATA Location ${PGDATA} \n"
            break
        else
            ((num_trys++))
            echo -e "Airflow PG ${PGDATA} not running \n"
            echo -e "Attempt: ${num_trys} to restart..."

            pg_ctl start -D ${PGDATA} -l ${PG_LOG_FILE}

            echo -e "Waiting 30 seconds for DB to come up..."
            sleep 30

            if pg_ctl status; then
                echo -e "PG ${PGDATA} has been started successfully..."
                break
            fi
        fi
    done
elif [ $1 == "stop" ]
then
    while [[ ${num_trys} -lt 3 ]];do
        if ! pg_ctl status; then
            echo -e "postgre is running for PGDATA Location ${PGDATA} \n"
            echo -e "Attempt: ${num_trys} to stop..."

            pg_ctl stop -D ${PGDATA} -t 180

            if [ $? -eq 0 ]
            then
                echo -e "Stop command Successful."
            else
                echo -e "Stop command Failed"
            fi

            echo -e "Waiting 30 seconds for DB Gracefully shutdown..."
            sleep 30

            if ! pg_ctl status; then
                echo -e "PG ${PGDATA} has been stopped successfully..."
                break
            fi
            
            ((num_trys++))
        else
            break
        fi
    done
fi

The following script can be added to /etc/systemd/system/postgresql.service

[Unit]
Description=PostgreSQL Airflow DB Server
After=network.target

[Service]
Type=forking
Restart=on-failure
RestartSec=60s
User=postgres
Group=postgres


# Maximum number of seconds pg_ctl will wait for postgres to start.  Note that
# PGSTARTTIMEOUT should be less than TimeoutSec value.
Environment=PGSTARTTIMEOUT=270
Environment=PGSTOPTIMEOUT=150
Environment=PGLOGFILE=/var/log/postgresql/pg-airflow.log

Environment=PGDATA=/var/lib/postgresql/10/main
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA} -l ${PGLOGFILE}  -t ${PGSTARTTIMEOUT}
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -t ${PGSTOPTIMEOUT}

# Give a reasonable amount of time for the server to start up/shut down.
# Ideally, the timeout for starting PostgreSQL server should be handled more
# nicely by pg_ctl in ExecStart, so keep its timeout smaller than this value.
TimeoutSec=300

[Install]
WantedBy=multi-user.target

These commands might need to be executed.

$ sudo systemctl daemon-reload # load the updated service file from disk
$ sudo systemctl enable postgresql
$ sudo systemctl start postgresql