Scheduling EMP Database Backup in Linux Server

As most EMP runs on linux, this topic provides step by step guide to enable daily, weekly or monthly backup procedures in a linux server.


1. Setting up the system administrator user in the linux

You need to be the "root" user in linux to perform the following:


1.1 Creating the linux user

We create a special user for data backup so that this task could be perform by a technician without giving this user root privileges to perform other functions.

At the linux command prompt, type the following:


[root@localhost ~]# useradd sysadmin

[root@localhost ~]# passwd sysadmin

Changing password for user sysadmin.

New UNIX password:

BAD PASSWORD: it is based on a dictionary word

Retype new UNIX password:

passwd: all authentication tokens updated successfully.

[root@localhost ~]#



1.2 Giving PostgreSQL database access permissions to sysadmin users

A linux user may not have permissions to access the PostgreSQL database. To enable the permissions, do the following:


[root@localhost ~]# su - postgres

-bash-3.00$ createuser sysadmin

Shall the new user be allowed to create databases? (y/n) y

Shall the new user be allowed to create more new users? (y/n) y


-bash-3.00$ exit


[root@localhost ~]#



1.3 Creating sub directories within the sysadmin home folder


[root@localhost ~]# su - sysadmin

[sysadmin@localhost ~]$ mkdir bin

[sysadmin@localhost ~]$ mkdir public_html

[sysadmin@localhost ~]$ mkdir -p public_html/backup

[sysadmin@localhost ~]$



2. Shell script and cron job


2.1 Creating the backup shell script

You can use vim editor or other text editor to create a shell script at /home/sysadmin/bin/ The content of the shell script should be as below:


/usr/bin/psql -c 'vacuum full' wsemp

mkdir -p /home/sysadmin/public_html/backup/

/usr/bin/pg_dump -i -d -O -R wsemp > /home/sysadmin/public_html/backup/`hostname`-backup-db.sql

2.2 Create the crontab.txt


The following file create a cron table that runs the script at 4am every morning. For consistency, lets create this file at /home/sysadmin/bin/crontab.txt



0 4 * * *       /home/sysadmin/bin/




It is absolutely important for you to modify the permissions of the so that it is executable:


[sysadmin@localhost ~]$ chmod a+x /home/sysadmin/bin/


Activate the crontab.txt by typing the following command:


[sysadmin@localhost ~]$ crontab /home/sysadmin/bin/crontab.txt


If you would like to find out more information about writing the crontab.txt, type the following command:


[sysadmin@localhost ~]$ man 5 crontab


For normal crontab manual, type the following:


[sysadmin@localhost ~]$ man crontab


Next, ensure that the shell script works properly by typing the following in the terminal:

[sysadmin@localhost ~]$ ~/bin/

Then, ensure that the backup is created by typing the following:

[sysadmin@localhost ~]$ ls -l ~/public_html/backup/

Ensure that the listing appear has a backup with today's date and its size is not zero. Example:

-rw-r--r-- 1 sysadmin sysadmin 23798508 2009-07-30 11:03 <your server's hostname>-backup-db.sql

3. Setting Up Apache Web Server (optional)


3.1 Changing the permissions of the sysadmin sub directories for SE (Security Enhanced) Linux


[root@localhost ~]# chcon -R -t httpd_user_content_t /home/sysadmin


3.2 Changing the read and execute permissions of the directories


[root@localhost ~]# chmod -R a+r+x+w /home/sysadmin


3.3 Modifying the permissions of apache web server configurations:


First of all, ensure that apache is automatically started when system boot up, type the following command to configure system services:


[root@localhost ~]# system-config-services &


Inside the popup window, enable the httpd services.



Open up the apache web server configuration file, you may want to backup a copy of this file you start modifying it:


[root@erhan ~]# vi /etc/httpd/conf/httpd.conf


Modify the following part of the file to configure the permissions:



# UserDir: The name of the directory that is appended onto a user's home

# directory if a ~user request is received.


# The path to the end user account 'public_html' directory must be

# accessible to the webserver userid.  This usually means that ~userid

# must have permissions of 711, ~userid/public_html must have permissions

# of 755, and documents contained therein must be world-readable.

# Otherwise, the client will only receive a "403 Forbidden" message.


# See also:


<IfModule mod_userdir.c>


    # UserDir is disabled by default since it can confirm the presence

    # of a username on the system (depending on home directory

    # permissions).


    #UserDir disable


    # To enable requests to /~user/ to serve the user's public_html

    # directory, remove the "UserDir disable" line above, and uncomment

    # the following line instead:


    UserDir public_html



# Control access to UserDir directories.  The following is an example

# for a site where these directories are restricted to read-only.


<Directory /home/*/public_html>

    AllowOverride FileInfo AuthConfig Limit

    Options MultiViews Indexes SymLinksIfOwnerMatch IncludesNoExec


        Order allow,deny

        Allow from all


    <LimitExcept GET POST OPTIONS>

        Order deny,allow

        Deny from all




Restart the apache web server:


[root@localhost ~]# /etc/init.d/httpd restart


If you have already dumped a database file there, skip the following step, if not, simple run the following command:


[root@localhost ~]# su - sysadmin

[sysadmin@localhost ~]$ /home/sysadmin/bin/


Open up a web browser and access the following URL:




4. Using JBoss to enable download of database (optional)


Instead of umping the database files into /home/sysadmin/public_html/backup, you can also dump the file directly into $JBOSS_HOME/server/default/deploy/jbossweb-tomcat50.sar/ROOT.war/backup.


[root@erhan ~]# cd $JBOSS_HOME/server/default/deploy/jbossweb-tomcat50.sar/ROOT.war/

[root@erhan ~]# mkdir backup


Once the backup directory in jboss is created, you can modify the /home/sysadmin/bin/ accordingly to dump the files into the correct directory.

To download the files, you can use a web-browser and point to http://localhost:8080/backup


5. Using FTP to download the databases

You can use any FTP program to download the files by using the sysadmin username and password.

Alternatively, if you are using a web browser to download the files, you can type the following:




for example:


If you encounter an error message saying something like "OPPS: Failed to change directory : /home/sysadmin", that's probably caused by SE Linux permissions. You can type the following commands as root to disable the SE Linux, and everything should work fine:


[root@erhan ~]# setsebool -P ftp_home_dir 1
[root@erhan ~]# service vsftpd restart


6. Backing up database into another server

Backing up database into another server is as simple as using the crontab and wget commands, advanced users can also use scp, rsync, or curl commands to pull the data from the database server.

7. Setting Up Crontab To Auto Transfer DB Backup File To Another Server Where SSH Port Is Not 22

Click here for full instruction.


8. Install FTP Client (CuteFTP or WS FTP) in your Windows desktop or notebook to download the database

Cute FTP -