PostgresSQL administration

In my line of work and also in my personal activity (See OpenLearning Project Page), I need to administrate a Postgresql Database.I'm going to share what I need to do.In this paper, I don't explain each parameters : I only give my configuration for my purpose.If you want details about configuration, see the official website od PostgreSQL.
I encourage you to let me know you advice and what you think of my paper.Please do not hesitate to report.It's important for me and other users.

My server configuration :

• Debian 8.0 x64 (Jessie)
• PostgreSQL 9.4.13

Backup the database

Here is my backup script :

       
#!/bin/sh
###################################################################
# Save [DATABASE_NAME] database
#
# @author  : ...
# @version : 1.0
#
###################################################################


BACKUP_DIR=/opt/[DATABASE_NAME]/postgresql-backup
MAILTO="[MAIL]"

current_time=$(date "+%Y.%m.%d-%H.%M.%S")
backup_file="$BACKUP_DIR/[DATABASE_NAME]-dump_${current_time}.sql"

# Log script output
log()
{
    echo $1
}


##########################################################################################
# MAIN
##########################################################################################


log ""
log ""
log "#####################################################################"
log "START $0 script at $current_time"
log "#####################################################################"

log ""
log "Saving Postgresql database in ${backup_file}"

# Remarque .pgpass present dans /root/
`pg_dump --format=custom --no-owner -U postgres -v -b -f $backup_file -h 127.0.0.1 [DATABASE_NAME]`
if [ -s $backup_file ] ; then
  echo 'Dump succeeded'
  echo 'Dump succeeded'  | mail -s "[DATABASE_NAME] save database SUCCESS" $MAILTO
else
  echo 'Dump failed'
  echo 'Dump failed'  | mail -s "[DATABASE_NAME] save database FAILED" $MAILTO
fi

current_time=$(date "+%Y.%m.%d-%H.%M.%S")
log "#####################################################################"
log "END synchro script at $current_time"
log "#####################################################################"
exit 0
       

Note :
- We use postgres user to avoid permission problem.
- You have to change [DATABASE_NAME] and [MAIL] in order to use this script.
- This script ask you a password.To avoid this, see next section.

Automatic connection

Automatic connection is necessary by the backup script for crontab purpose.First of all, we need to set a password for postgres user :

       
su - postgres
psql
ALTER USER postgres WITH PASSWORD 'MY_PASSWORD';
       
 

To be sure that is work, you have to test it :

       
psql -U postgres -h 127.0.0.1 -W
       

It should ask a password and you will be able to connect at Postgresql.
Then, we create and fill a .pgpass file like the following :

       
vi /root/.pgpass
127.0.0.1:5432:[DATABASE_NAME]:postgres:[MY_PASSWORD]
       

After, make a chmod :

       
chmod 600 /root/.pgpass
       

Now, the following command should work :

       
psql -U postgres -h 127.0.0.1 -W
       

That's all ! Now a script using pg_backup should work.
To finish, we execute the backup script in crontab every night at 22:00 :

       
crontab -e
00 22 * * * /root/app/scripts/crontab/save_db.sh >> /var/app/archive/postgresql/save_db.log
       

Restore the database

Advice : the most important is to test restore of the database !

The following script ask you a password.Usually, it should not because of .pg_pass file ! It is not really important because restore isn't use in crontab script.

       
#!/bin/sh

###################################################################
# Restore [DATABASE_NAME] database
#
###################################################################
clear
current_time=$(date "+%Y.%m.%d-%H.%M.%S")
echo ""
echo "#####################################################################"
echo "START $0 script to RESTORE [DATABASE_NAME] DATABASE  at $current_time"
echo "#####################################################################"
echo ""


filepath=""
echo "Please enter PGSQL's backup file path ( e.g : /opt/[DATABASE_NAME]/postgresql-backup/[DATABASE_NAME]-dump_2017.08.24-10.28.35.sql) : "
read filepath
if [ "$filepath" = "" ];then
        echo "You must write a backup file path !"
        echo "END"
        exit 1
fi

if [ -e "$filepath" ];then
        echo "Restoring Postgresql database from ${filepath}..."
        pg_restore --if-exists -v -c -h 127.0.0.1 -U postgres -d [DATABASE_NAME] ${filepath}
else
        echo "File not found : $filepath"
fi

current_time=$(date "+%Y.%m.%d-%H.%M.%S")

echo "#####################################################################"
echo "END synchro script at $current_time"

echo "#####################################################################"
exit 0     
 

Note : You need to create an empty database before restoring.

Monitoring your database

To improve our application, we need to identify heavy request which have a duration upper than 50 ms.That's why we activate logging :

 vi /etc/postgresql/9.4/main/postgresql.conf
logging_collector = on
log_rotation_age = 1d
log_rotation_size = 100MB

Then we modify following parameters :

       
log_min_duration_statement = 50
log_statement = 'mod'

After, you only need restart the database :

service postgresql restart
ls /var/lib/postgresql/9.4/main/pg_log/

You will log file and you can test logging with simple request : select pg_sleep(0.5);


Source :
https://www.depesz.com/2011/05/06/understanding-postgresql-conf-log/