How to Backup PostgreSQL Databases Server

Overview

PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

PostgreSQL implements the majority of the SQL:2011 standard, is ACID-compliant and transactional (including most DDL statements) avoiding locking issues using multiversion concurrency control (MVCC), provides immunity to dirty reads and full serializability; handles complex SQL queries using many indexing methods that are not available in other databases; has updateable views and materialized views, triggers, foreign keys; supports functions and stored procedures, and other expandability, and has a large number of extensions written by third parties. In addition to the possibility of working with the major proprietary and open source databases, PostgreSQL supports migration from them, by its extensive standard SQL support and available migration tools. Proprietary extensions in databases such as Oracle can be emulated by built-in and third-party open source compatibility extensions. Recent versions also provide replication of the database itself for availability and scalability.

PostgreSQL is cross-platform and runs on many operating systems including Linux, FreeBSD, OS X, Solaris, and Microsoft Windows. On OS X, PostgreSQL has been the default database starting with Mac OS X 10.7 Lion Server,] and PostgreSQL client tools are bundled with in the desktop edition. The vast majority of Linux distributions have it available in supplied packages.

PostgreSQL is developed by the PostgreSQL Global Development Group, a diverse group of many companies and individual contributors. It is free and open-source software, released under the terms of the PostgreSQL License, a permissive free-software license.

Let’s Start creating script for backup, I assume that your PostgreSQL is already running

Step 1: Login as a postgresql user and type the below commands in your console terminal

[[email protected] ~]$ sudo su - postgres
 Last login: Sat Feb 27 11:37:14 SGT 2016 on pts/3
 -bash-4.2$
 -bash-4.2$
 -bash-4.2$ psql
 psql (9.2.14)
 Type "help" for help.
postgres=#
postgres=#

 

Step 2: List all the available databases to verify which database will be backup

postgres=# \l
                       List of databases
 Name       | Owner           | Encoding  | Collate     | Ctype   | Access privileges
 ------------+-----------------+----------+-------------+---------+-----------------------
 mydatabase | asterisksqluser | UTF8  | en_US.UTF-8 | en_US.UTF-8 |
 postgres   | postgres        | UTF8  | en_US.UTF-8 | en_US.UTF-8 |
 template0  | postgres        | UTF8  | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
            |                 |       |             |             | postgres=CTc/postgres
 template1  | postgres        | UTF8  | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
            |                 |       |             |             | postgres=CTc/postgres
 (4 rows)

 

Step 3: Run the command to make a backup using “pg_dump”

Backup database using pg_dump command. pg_dump is a utility for backing up a PostgreSQL database. It dumps only one database at a time. General syntax:

pg_dump mydatabase > mydatabase-2016Feb27.sql

This will dump a mydatabase database

And type the following command

$ pg_dump mydatabase > mydatabase-2016Feb27.dump.sql

And this is to restore a database:

$ psql -d mydatabase -f mydatabase-2016Feb27.dump.sql

If you need to compress the files if its large to save disk space you can do this below commands

$ pg_dump mydatabase | gzip -c > mydatabase-2016Feb27.dump.gz

To restore database use the following command:

$ gunzip mydatabase-2016Feb27.dump.gz
$ psql -d mydatabase -f mydatabase-2016Feb27.dump.sql

 

Step 4: There’s another option is use to pg_dumpall command. to backup all your postgresql databases

$ pg_dumpall > all-database.2016Feb27.sql

OR

$ pg_dumpall | gzip -c > all-database.2016Feb27.sql.gz

To restore backup use the following command:

$ psql -f all-database.2016Feb27.sql postgres

 

Step 5: Create script to run in crontab and only keep 30 days of copy and we will call it “backup-sql.sh”

#!/bin/bash
# Location to place backups.
backup_dir="/home/user/postgres-backup/"
#String to append to the name of the backup files
backup_date=`date +%d-%m-%Y`
#Numbers of days you want to keep copy of your databases
number_of_days=30
databases=`psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
for i in $databases; do
  if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
    echo Dumping $i to $backup_dir$i\_$backup_date
    pg_dump -Fc $i > $backup_dir$i\_$backup_date
  fi
done
find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;

Now change the permission for “backup-sql.sh” by issuing below commands.

chmod +x backup-sql.sh

Step 6: Add the “backup-sql.sh” to run in scheduled job

$ sudo crontab -e

And add a line like this one to run everyday @ 1AM

* 1 * * * /home/fsmendoza/script/backup-sql.sh >/dev/null 2>&1

postgresql-b

 

Be the first to comment on "How to Backup PostgreSQL Databases Server"

Leave a comment

Your email address will not be published.