How To Backup Automation With Cronjobs Schedjuling Data Postgresql On CENTOS 7

I will teach you build schedjuling backup data postgresql on centos 7 with crondjobs, in here i want to backup file database postgresql in ip 10.20.10.111, and then if u have success login, you must be use user postgres because in here i want to backup data postgres in user postgres. so i move to user postgres with command "su - postgres" and see where your location with command "pwd".

Ok now, One step you must be login with your pc with ssh :

dba2@dba-Veriton-Z4620G:~$ ssh root@10.20.10.111

root@10.20.10.111's password:

Last login: Thu Apr 19 16:54:32 2018 from 10.20.8.1

Step 2, move user root to user postgres with command “su” and check location with command “pwd” :

[root@prod-odgpostgres ~]# su - postgres

Last login: Kam Apr 19 10:36:21 WIB 2018 on pts/0

[postgres@prod-odgpostgres ~]$ pwd

/home/postgres

[postgres@prod-odgpostgres ~]$

And then see large disk for create location backup on user postgres, why see disk? Because in your large disk will be location backup, if in here not create new disk but use large disk, I will use disk name “/dev/vda1” because space disk 148G, so I will build directories backup and monthly, why build 2 directories? Because in directory backup I will save data backup all day (1-7 day) but in directory monthly just rekap all data every monthly.

[postgres@prod-odgpostgres ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/vda1 150G 2.4G 148G 2% /

devtmpfs 3.8G 0 3.8G 0% /dev

tmpfs 3.9G 4.0K 3.9G 1% /dev/shm

tmpfs 3.9G 49M 3.8G 2% /run

tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup

tmpfs 783M 0 783M 0% /run/user/0

[postgres@prod-odgpostgres ~]$mkdir /home/postgres/backup

[postgres@prod-odgpostgres ~]$mkdir /home/postgres/monthly

[postgres@prod-odgpostgres ~]$ll

total 8

drwxrwxr-x 2 postgres postgres 123 Apr 18 18:01 backup

-rw-rw-r-- 1 postgres postgres 383 Feb 5 17:13 logfile

drwxrwxr-x 2 postgres postgres 6 Apr 19 10:36 monthly

And if finished build 2 directories in location home postgres:

[postgres@prod-odgpostgres ~]$ cd /home/postgres

[postgres@prod-odgpostgres ~]$ pwd

/home/postgres

So, now you must be create new file pgdump.sh with this location, with use vi or if you not like vi, you can use nano, but you must install nano if in your OS not installed software nano.

Because in here I use OS centos 7, I will gift you install nano, just follow command :

[postgres@prod-odgpostgres ~]$ yum -y install nano

[postgres@prod-odgpostgres ~]$ vi pgdump.sh

Copy this script in your new file pgdump:

. /home/postgres/.bash_profile

DT=`date +%Y%m%d`

DT2=`date -d 'now - 1month' +%Y%m%d`

OSID=$1

DBNAME=`echo $OSID|cut -d1 -f1`

pg_dump ${DBNAME} > {LocationPath}/${DBNAME}_${DT}.sql

rm -f {LocationPath}/*${DT2}*

zip -r -P d4t4b4sepostgresql {LocationPath}/${DBNAME}_${DT}.zip {LocationPath}/${DBNAME}_${DT}.sql

rm -f {LocationPath}/*${DT}*.sql

 

After finished copying file I gave in the previous command, you must be save with command ctrl+c → shift+: → wq! , and enter. So after create file,you can see this your file with command “ll” and you can see mode and owner file your created.

[postgres@prod-odgpostgres ~]$ ll

total 8

drwxrwxr-x 2 postgres postgres 123 Apr 18 18:01 backup

-rw-rw-r-- 1 postgres postgres 383 Feb 5 17:13 logfile

drwxrwxr-x 2 postgres postgres 6 Apr 19 10:36 monthly
-rw-rw-r-- 1 postgres postgres 417 Apr 13 14:34 pgdump.sh

And you must be check status port your postgres is on or off? If on your default port postgres show 5432, but if off not show 5432 with PID/program name postmaster.

[postgres@prod-odgpostgres ~]$ netstat -pltn

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN -

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -

tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 24504/postmaster

tcp6 0 0 :::111 :::* LISTEN -

tcp6 0 0 :::22 :::* LISTEN -

tcp6 0 0 :::5432 :::* LISTEN 24504/postmaster

And you can see how to start postgres with binary and you can see file database postgres with command “ps -ef”:

[postgres@prod-odgpostgres ~]$ ps -ef | grep postgres

root 18033 18020 0 16:56 pts/0 00:00:00 su - postgres

postgres 18034 18033 0 16:56 pts/0 00:00:00 -bash

postgres 18059 18034 0 16:59 pts/0 00:00:00 ps -ef

postgres 18060 18034 0 16:59 pts/0 00:00:00 grep --color=auto postgres

postgres 24504 1 0 Feb09 ? 00:00:13 /usr/local/pgsql/bin/postmaster -D /DATA

postgres 24505 24504 0 Feb09 ? 00:02:02 postgres: startup process recovering 000000010000000000000015

postgres 24508 24504 0 Feb09 ? 00:00:33 postgres: checkpointer process

postgres 24509 24504 0 Feb09 ? 00:01:40 postgres: writer process

postgres 24511 24504 0 Feb09 ? 00:00:03 postgres: stats collector process

postgres 24512 24504 0 Feb09 ? 01:20:26 postgres: wal receiver process streaming 0/15E4AC90

If port use default 5432 you just follow keyword psql, but if u use port custom configuration, u must follow keyword with port your custom psql -p 6432 (why -p ? because -p same comment port)

[postgres@prod-odgpostgres ~]$ psql

psql (9.6.3)

Type "help" for help.

postgres=# \l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

--------------------+----------+----------+-------------+-------------+-----------------------

ABCSYARIAH_DOLPINS | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

(4 rows)

postgres=#

And now, u get copy paste database you want backup, if i want backup database "ABCSYARIAH_DOLPINS", how can i do ? you must be back to user postgres and then you must move location in home postgres, so in there your edit again file pgdump.sh with command vi or nano, in this i use command vi "vi pgdump.sh", and in this file u change key ${DB_NAME} to database in your psql, why u change? because if backup cant be backup on key ${DB_NAME} u must change key with DB, i want to use db "ABCSYARIAH_DOLPINS" and the u must be follow location folder backup.

postgres=# \q

[postgres@prod-odgpostgres ~]$ vi pgdump.sh

Copy this script in your new file pgdump with db if script with {DBNAME} not can be build file backup .zip:

. /home/postgres/.bash_profile

DT=`date +%Y%m%d`

DT2=`date -d 'now - 1month' +%Y%m%d`

OSID=$1

DBNAME=`echo $OSID|cut -d1 -f1`

pg_dump ABCSYARIAH_DOLPINS > /home/postgres/backup/ABCSYARIAH_DOLPINS_${DT}.sql

rm -f /home/postgres/backup/*${DT2}*

zip -r -P d4t4b4sepostgresql /home/postgres/backup/ABCSYARIAH_DOLPINS_${DT}.zip /home/postgres/backup/ABCSYARIAH_DOLPINS_${DT}.sql

rm -f /home/postgres/backup/*${DT}*.sql

Change mode and owner akses file on pgdump.sh in user postgres, if u create file in user root u must be owner to postgres and change mode, but if u create file and folder backup in user postgres u just change mode to 775 with follow key in here:

[postgres@prod-odgpostgres ~]$ chmod 775 pgdump.sh

[postgres@prod-odgpostgres ~]$ ll

total 8

drwxrwxr-x 2 postgres postgres 123 Apr 18 18:01 backup

-rw-rw-r-- 1 postgres postgres 383 Feb 5 17:13 logfile

drwxrwxr-x 2 postgres postgres 6 Apr 19 10:36 monthly

-rwxrwxr-x 1 postgres postgres 417 Apr 13 14:34 pgdump.sh

So if u finished change mode and owner, now you can do running file pgdump.sh, how you can do running file pgdump.sh? just be simple, you must be follow key in here:

[postgres@prod-odgpostgres ~]$ ./pgdump.sh

adding: home/postgres/backup/ABCSYARIAH_DOLPINS_20180419.sql (deflated 62%)

[postgres@prod-odgpostgres ~]$ cd backup/

[postgres@prod-odgpostgres backup]$ ll

total 218952

-rw-rw-r-- 1 postgres postgres 56049000 Apr 19 17:26 ABCSYARIAH_DOLPINS_20180419.zip

[postgres@prod-odgpostgres backup]$

Now can i do backup automation with crondjobs, how can do that? i wan to teach you build schedjule backup automation with crondjobs. one step you must follow key in here:

[postgres@prod-odgpostgres ~]$ crontab -e

And if u finished follow key u will see the new window for the schedjuling data backup. in there you must insert time clock and location file pgdump.sh and then insert database your psql, in here i want to backup data is time 18.01 WIB in all day (sunday-monday)

01 18 * * * /home/postgres/pgdump.sh ABCSYARIAH_DOLPINS

And if you finished insert script, you must be save with command ctrl+c, shift+:, keyword wq! and enter , so if save you must be back to user root, if you still in user postgres, you must be back to user root. and in user root you must be follow key restart cronjobs in here:

[postgres@prod-odgpostgres ~]$ exit

logout

[root@prod-odgpostgres ~]# cat /etc/redhat-release

CentOS Linux release 7.4.1708 (Core)

[root@prod-odgpostgress ~]# systemctl restart crond

If u use OS centos < 7 you must be follow command :

[postgres@prod-odgpostgres ~]$ exit

logout

[root@prod-odgpostgress ~]# service crond restart

And now you just waiting for the backup file until created in time 18.01 and see results. Thank you.

###### sorry if my english is so bad.