Home > How To > Backup PostGRE Database and ‘rsync’ to Another Server with Password-less Entry

Backup PostGRE Database and ‘rsync’ to Another Server with Password-less Entry

We had a requirement that we need to create a backup of postGre Database,
And rsync it to Another server. Here is How we did it.

BACK UP DATABASE
Here is the script to take backup of PostgreSQL Database.
BACKUP_DIR_PATH=”/backup/myDB/Here”
PGUSER_DB=”username”
export PGPASSWORD=”password”
time=`date ‘+%d’-‘%m’-‘%H’:’%M’:’%S’ `
/usr/pgsql-9.0/bin/pg_dump –host localhost –port 5432 –username $PGUSER_DB –format custom –blobs –verbose –file “$BACKUP_DIR_PATH/backup-databaseName-$time.backup” “database_name_here”

‘rsync’ DATA TO ANOTHER SERVER
Here is the script to sync backed-up data to another Server
DIR_LOCAL=”/backup/myDB/Here”
DIR_REMOTE=”/home/user/db_backup_from_dev
REMOTE_USER=”username”
REMOTE_SERVER=”192.168.0.8″
rsync -avzh -e ssh $DIR_LOCAL  $REMOTE_USER@$REMOTE_SERVER:$DIR_REMOTE
To Complete rsync we need to create a secure channel (PASSWORDLESS Entry) to the Destination Server.
creating a secure channel PASSWORD-LESS Entry to the Destination Server.
Sync between two hosts source_machine and destination_machine.
On source_machine.
$ ssh-keygen -t rsa
This will prompt for a passphrase. Just press the enter key. 
It’ll then generate an identification (private key) and a public key. 
Do not ever share the private key with anyone! 
ssh-keygen shows where it saved the public key. 
This is by default ~/.ssh/id_rsa.pub:
Your public key has been saved in /.ssh/id_rsa.pub
Transfer the id_rsa.pub file to destination_machine.
On destination_machine.
Login as the remote user which you plan to use when you run rsync on source_machine.
Copy the contents of id_rsa.pub to ~/.ssh/authorized_keys
$ cat id_rsa.pub >> ~/.ssh/authorized_keys
$ chmod 700 ~/.ssh/authorized_keys
If this file does not exists, then the above command will create it. 
Make sure you remove permission for others to read this file.
 
If its a public key, why prevent others from reading this file? Probably, the owner of the key has distributed it to a few trusted users and has not placed any additional security measures to check if its really a trusted user.
Note that ssh by default does not allow root to log in.
Advertisements
Categories: How To
  1. July 3, 2012 at 12:29 pm

    We can restore the PostGRE Database into remote server using below command

    # scp backupfilename remoteserver_ip:/destination directory

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: