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
-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.
