Postgres Database Backup

1. Fully Automated backup

Need to install postgres (preferably the same version) on the backup machine

In the Postgres directory to create a password to save the file (plaintext, so confidentiality is important), if not established, because each backup must enter a password, not automatic backup

# create a. pgpass file touch. Pgpass # write the following in a file that can be multiple lines # 127.0.0.1:5432:dbname:dbuser:dbpassword # 127.0.0.1:5432:dbname2:dbuser2:dbpassword2

Create automatic scripts that automatically divide folders by month

# !/bin/sh. /etc/profile. ~/. Bash_profiledirname=$ (date +%y%m) filename=$ (date +%y%m%d%h%m%  -p/home/postgres/backup/$dirname/H [ipaddr]-u [dbUser] [dbname]| gzip>/home/postgres/backup/$dirname/bk_$filename. gz

Create crontab automatic execution

2. Recovering a Database

Unzip GZ

# Direct decompression, do not retain the original GZ file gunzip xxx.gz # unzip the preserved source Gz file gunzip-c xxx.gz > Xxx.sql

Get in the Pgsql console.

Pgsql-u DbUser

# switch to the database you want to import \c DbName; # Import SQL file \i xxx.sql
#退出控制台
\q

Online Hot Backup (archive)--pending research
(a) Backup
1. Configure Archiving Mode
Configuring the archive requires editing the postgresql.conf file, which defaults to the/usr/local/pgsql/data/directory
Vim/usr/local/pgsql/data/postgesql.conf
Archive_mode = On
Archive_command = ' cp%p/usr/local/pgsql/backup/archived_log/%f '
Note:%p the path to the log file to be archived,%f is the file name of the log file to be archived
2. Start the database
Pg_ctl–d/usr/local/pgsql/data start
3, CREATE DATABASE Arch
Createdb Arch
4. Create a table and insert a record
Psql Arch
arch=# CREATE TABLE TB (a int);
arch=# INSERT into TB (a) values (1);
5, create a backup
arch=# Select Pg_start_backup (' Baseline ');
6. Back up the entire data directory
tar–jcv–f/usr/local/pgsql/backup/baseline.tar.bz2/usr/local/pgsql/data/
7, stop Backup
Psql Arch
arch=# select Pg_stop_backup ();
8, insert new record, then switch log, repeat 3 times
arch=# INSERT into TB (a) values (2);
arch=# select Pg_switch_xlog ();
arch=# INSERT into TB (a) values (3);
arch=# select Pg_switch_xlog ();
arch=# INSERT into TB (a) values (4);
arch=# select Pg_switch_xlog ();
9. Copy the Wal log file under/data/pg_xlog/to the pre-set archive directory, ensuring that the resulting Wal logs are archived.
(ii) Recovery
1, Stop the database
Pg_ctl–d/usr/local/pgsql/data/stop
2, delete/data/
rm–r/usr/local/pgsql/data/
3, restore Backup
Tar–jxv–f/usr/local/pgsql/backup/baseline.tar.bz2–c/
4. Empty all files in the/data/pg_xlog/directory
rm–r/usr/local/pgsql/data/pg_xlog/
5, create the/pg_xlog/and its archive_status directory below
mkdir/usr/local/pgsql/data/pg_xlog/
Mkdir/usr/local/pgsql/data/pg_xlog/archive_status
6, create the recovery.conf in the/data/directory
Vim/usr/local/pgsql/data/recovery.conf
Restore_command = ' cp/usr/local/pgsql/backup/archived_log/%f '%p "'
7. Start the database
Pg_ctl–d/usr/local/pgsql/data/start
Everything's fine. The database will automatically apply the Wal log for recovery
8. See if the database arch is restored
Psql Arch
arch=# SELECT * from TB;
A
---
1
2
3
4
(4 rows)
At this point, the data has been successfully restored!

Postgres Database Backup