EMP Database Backup

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

CREATE USER

-bash-3.00$ exit

logout

[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/wsemp_backup.sh 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 wsemp_backup.sh script at 4am every morning. For consistency, lets create this file at /home/sysadmin/bin/crontab.txt

 

 

0 4 * * *       /home/sysadmin/bin/wsemp_backup.sh

 

 

 

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

 

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

 

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

 

 

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: http://httpd.apache.org/docs/misc/FAQ.html#forbidden

#

<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

</IfModule>

#

# 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

    <Limit GET POST OPTIONS>

        Order allow,deny

        Allow from all

    </Limit>

    <LimitExcept GET POST OPTIONS>

        Order deny,allow

        Deny from all

    </LimitExcept>

</Directory>

 

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/wsemp_backup.sh

 

Open up a web browser and access the following URL:

http://localhost/~sysadmin/backup

 

 

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/wsemp_backup.sh 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:

 

ftp://sysadmin:thepassword@ip_address/user_home_directory/filename.sql

  

for example: ftp://sysadmin:thepassword@company.wavelet.biz/public_html/backup/filename.sql

 

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 - http://www.cuteftp.com/cuteftppro/

WS FTP - http://www.wsftp.com/