[postgre@daduxiong ~]$ more /usr/local/pgsql/data/postgresql.conf |grep archive_
archive_mode = on # allows archiving to be done
archive_command = 'cp -i %p /archive/%f >/dev/null' 2,重新启动数据库
[iyunv@daduxiong ~]# service postgresql stop
Stopping PostgreSQL: server stopped
ok
[iyunv@daduxiong ~]# service postgresql start
Starting PostgreSQL: ok 3,启动备份
[postgre@daduxiong archive]$ psql postgres
Welcome to psql 8.3.10, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# create table abc(id integer);
CREATE TABLE
postgres=# insert into abc values(1);
INSERT 0 1
postgres=# \q
7,此时假设数据库出现问题,停止数据库,拷贝日志
[iyunv@daduxiong pgsql]# service postgresql stop
Stopping PostgreSQL: server stopped
ok
[postgre@daduxiong archive]$ cp $PGDATA/pg_xlog/*00* /archive/
[iyunv@daduxiong data]# service postgresql start
Starting PostgreSQL: ok
---省略日志部分内容
LOG: selected new timeline ID: 3
LOG: restored log file "00000002.history" from archive
LOG: archive recovery complete
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
cp: overwrite `/archive/000000010000000000000008'? cp: overwrite `/archive/000000010000000000000009'? cp: overwrite `/archive/000000020000000000000009'? cp: overwrite `/archive/00000002000000000000000A'?
13,验证恢复结果。检查之前创建的表与记录。
代码
[postgre@daduxiong archive]$ psql postgres
Welcome to psql 8.3.10, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# select * from abc;
id
----
1
(1 row)
postgres=# \q
[iyunv@daduxiong data]# ls -l
total 80
-rw------- 1 postgre postgre 147 Aug 31 10:26 backup_label.old
drwx------ 6 postgre postgre 4096 Aug 27 11:33 base
drwx------ 2 postgre postgre 4096 Aug 31 10:41 global
drwx------ 2 postgre postgre 4096 Aug 10 11:06 pg_clog
-rwx------ 1 postgre postgre 3429 Aug 10 11:10 pg_hba.conf
-rwx------ 1 postgre postgre 1460 Aug 10 11:06 pg_ident.conf
drwx------ 4 postgre postgre 4096 Aug 10 11:06 pg_multixact
drwx------ 2 postgre postgre 4096 Aug 10 11:06 pg_subtrans
drwx------ 2 postgre postgre 4096 Aug 10 11:06 pg_tblspc
drwx------ 2 postgre postgre 4096 Aug 10 11:06 pg_twophase
-rwx------ 1 postgre postgre 4 Aug 10 11:06 PG_VERSION
drwx------ 3 postgre postgre 4096 Aug 31 10:35 pg_xlog
-rwx------ 1 postgre postgre 16727 Aug 31 09:42 postgresql.conf
-rwx------ 1 postgre postgre 59 Aug 31 10:35 postmaster.opts
-rw------- 1 postgre postgre 47 Aug 31 10:35 postmaster.pid
-rwxr-x--- 1 postgre postgre 39 Aug 31 10:34 recovery.done