Manually Creating Backup Postgresql From Terminal CentOS

Summary:In this post i have tried to explain, how to take a manual database backup postgresql from terminal through commands.

Environment:

Operating System: CentOS-6.3

Data-Base: Postgresql-8.4

Follow the steps listed below.

Step-1: Connect to your server through ssh from local pc, using putty or any ssh Client.

ssh username@yourserverip and then enter the password to get in to the server.

sshserver

Step-2: Find the location where you have the directory on which you have given the permission for backup storage during the installation of Postgresql.

if you are not familiar with that. then run the command to find the location of the postgres configuration.

Selection_002

you have find the location where you can take the backup of your database.

Step-3:  cd /var/lib/pgsql/backup/

ls your directory to know about what already there

then login into your postgres

Selection_003

Step-4: psql postgres / su postgres

$ you are in postgres ; now list all the databases you have on the server running on.

$ psql -l

List of databases
Name | Owner | Encoding
-----------+-----------+----------
article | sathiya | UTF8
backup | postgres | UTF8
erp | geekstuff | UTF8
geeker | sathiya | UTF8

great!
now

Step-5: Backup a Single Postgres DataBase

I want to take backup of geeker from all the DataBases,

Selection_004

Now Press Enter

It will take a bet time and depends on the size of your database so don’t worry, nothing is going wrong.

And here you go, backup is completed. a size of 9.3GB backup created within 3 minutes.

now i have to copy this backup to some safe place in my backups Directory.

$exit

ls -lh

yourbackup.sql bla bla bla.

cp yourbackup.sql /some/safe/backup/Directory/and zip the backup.

#tar -zcvf yourbackup.sql.tar.gz yourbakcup.sql

Step-6: Now let go for backup of all the databases.

#su postgres

bash-3.2$ pg_dumpall > full-database-backup-Current-date.sql
Here you have to take a cup of tea or to attained a call of your friend or even you can go out for smoke. It will take time.

oh great its done,  I just back after 1o minutes and server is free of process.

Lets check how much size is of the full backup.

bash-3.2$ exit

roo@myserver:# ls -lh
total 52G
-rw-r–r– 1 postgres postgres 52G  full-database-backup-Current-date.sql
Ahan nice.

Its fast.

Now move it to the safe place.

#mv full-database-backup-Current-date.sql /home/sysadmin/

That’s All for today. If any one have confusion or some issue, just comment here.

In next we will be Importing the backup to the new server with fresh installation of the postgres.

Leave a Reply

Your email address will not be published. Required fields are marked *