Print-friendly Version

Backing Up a PostgreSQL Database to a Remote Server With the Solaris 10 OS

César Augusto Sánchez Baquero, September 2007

Here's how to keep a periodic backup of a PostgreSQL database running on the Solaris 10 OS, and save it on another Solaris system. You have to follow these steps.

Note: I used the Solaris 10 OS in the database systems and the Solaris 10 11/06 release (also called Update 3) in the backup system. PostgreSQL 8.2.4 was used here.

Create a Database User for the Backup

CREATE USER backupuser ENCRYPTED PASSWORD 'user_password'

Give the user read permissions over the relations in the database you want to back up using the GRANT SELECT command.

Edit the pg_hba.conf database configuration file to allow the new user to connect to the database using a UNIX domain socket connection. Add a line like this:

local database_name backupuser trust

With this configuration, the trusted user may connect to the server with no password only from a local UNIX account. Be careful with the system users that may connect to the database machine (only managers should log into the system in a production database server).

Signal the postmaster process to make the configuration take place:

/usr/local/pgsql/bin/pg_ctl reload

Create a user in the server where the backup will be transferred and saved.

You have to create a system user that will be used to save the backup coming from the database server.

Set the home directory in a partition with enough disk space.

Revoke permissions from groups and others to access this user's home directory.

Create and transfer the security file for the connection.

In the database server machine, use the postgres system user to run the following command:

ssh-keygen -t rsa

Transfer the created file ~/.ssh/id_rsa.pub to the receiver server. For security, use the command sftp for the file upload.

In the receiver machine, move and rename the transferred file:

mv id_rsa.pub ~/.ssh/authorized_keys

Prepare the Script

You can use a script like this one:

#Variable with the backup filename
date '+FILENAME=%Y-%m-%d-%H_%M_%S.sql' > pgbak.tmp.sh

echo "export LD_LIBRARY_PATH=/usr/lib:/lib:/usr/sfw/lib:/usr/local/lib" \
   >> pgbak.tmp.sh

echo "export PATH=/usr/bin:/usr/ucb:/etc:.: \
   /opt/sfw/bin:/usr/sfw/bin:/usr/local/pgsql/bin" >> pgbak.tmp.sh

echo "pg_dump -U backupuser database_name  > $FILENAME" \ 
   >> pgbak.tmp.sh
echo "gzip $FILENAME" >> pgbak.tmp.sh
echo "chmod go-rwx $FILENAME.gz" >> pgbak.tmp.sh
echo "chmod u-w $FILENAME.gz" >> pgbak.tmp.sh
echo "rsync --rsh=\"/usr/bin/ssh -i /home/postgres/.ssh/id_rsa\" \
   $FILENAME.gz system_user@RECEIVER_SERVER_IP_ADDRESS:/home/system_user" \
   >> pgbak.tmp.sh
echo "rm -f $FILENAME.gz" >> pgbak.tmp.sh

chmod u+x pgbak.tmp.sh
/usr/bin/bash ./pgbak.tmp.sh

################## end of script ###################

It's assumed that the database server runs with the user postgres and the home directories are placed in / home/. Replace the database name, server IP address and system user name with your own values.

The script creates a temporary file with instructions for saving the database address in a file named with the current date and the .sql extension. The temporary file is executed at the end of the script, completing the following tasks:

• Set the environment variables LD_LIBRARY_PATH and PATH.
• Call the database utility command pg_dump and direct the output to a new SQL file.
• Compress the SQL file using gzip.
• Transfer the compressed file using rsync and SSH.

Save the script as /home/postgres/bin/database_backup.sh and change the file permissions to allow user execution:

chmod u+x /home/postgres/bin/database_backup.sh

Test the script running it in a terminal.

Run the command /home/postgres/bin/database_backup.sh to test the script.

Sometimes rsync may produce error output like this:

bash:rsync:command not found

To fix it, you have to create a symbolic link for rsync in /usr/bin in both the sender and receiver servers:

ln -s /opt/sfw/bin/rsyn /usr/bin

If rsync is not installed in /opt/sfw/bin use the command type rsync to find where it is installed.

Program the Job

In order to automatically run the command, you have to program a new job using crontab.

Using the postgres user, open a terminal and set the environment variable EDITOR:

export EDITOR=vi

Run the following command to open the user's crontab list for editing (using the text editor vi) :

crontab -e

Edit the user's crontab, adding a line like this:

0 22 * * * /home/postgres/bin/database_backup.sh

Here, the backup is executed daily at 22 hours. See cron documentation if you want a different recurrence.

Back Up the Complete Database Server (Optional)

If you want to back up the complete database server (that is, all the databases) you have to use the pg_dumpall utility rather than pg_dump and allow trust access for the backupuser to all databases in the pg_hba.conf file.

Unless otherwise licensed, code in all technical manuals herein (including articles, FAQs, samples) is provided under this License.