Postgres的热备恢复:
连接数据库
[postgres@localhost ~]$ psql wmpay
Welcome to psql 8.3.3, the PostgreSQLinteractive 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
列出表
wmpay=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------------------------------+----------+----------
public | SEQ_POS_OPERATION_LOG | sequence | postgres
public | SEQ_POS_TRANSACTION_FLOW | sequence | postgres
public | di_responsecode | table | postgres
public | m_seq_creator | table | postgres
public | pos_notice | table | postgres
public | pos_notice_id_seq | sequence | postgres
public | pos_operation_log | table | postgres
public | pos_operation_log_detail | table | postgres
public | pos_operation_log_operation_number_seq| sequence | postgres
public | pos_transaction_flow | table | postgres
public | pos_transaction_flow_detail | table | postgres
public |pos_transaction_flow_trans_number_seq |sequence | postgres
public | test_backup | table | postgres
public | version | table | postgres
public | version_id_seq | sequence | postgres
public | versioning | table | postgres
(16 rows)
列出所有数据库
wmpay=# \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
wmpay | postgres | UTF8
(4 rows)
删除wmpay数据库:
模拟灾难
wmpay=# \c postgres
You are now connected to database"postgres".
postgres=# drop database wmpay;
DROP DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(3 rows)
postgres=#
关闭数据库:
postgres=# \q
[postgres@localhost ~]$ pg_ctl stop
LOG: received smart shutdown request
LOG: autovacuum launcher shutting down
LOG: shutting down
waiting for server to shutdown....LOG: database system is shutdown
done
server stopped
[postgres@localhost ~]$
把备份的文件夹恢复到原来数据库所在的位置
[postgres@localhost ~]$ mv wmpay_bak/pgdata
创建 recovery.conf文件
或把 recovery.done 改名为recovery.conf
[postgres@localhost ~]$ cd pgdata
[postgres@localhost pgdata]$ ls
backup_label global pg_ident.conf pg_tblspc pg_xlog postmaster.pid
backup_label.old pg_clog pg_multixact pg_twophase postgresql.conf recovery.done
base pg_hba.conf pg_subtrans PG_VERSION postmaster.opts
[postgres@localhost pgdata]$ catrecovery.done
restore_command = 'cp -f/home/postgres/wmpay_archive_log/%f %p'
[postgres@localhost pgdata]$ mvrecovery.done recovery.conf
[postgres@localhost pgdata]$ ls
backup_label global pg_ident.conf pg_tblspc pg_xlog postmaster.pid
backup_label.old pg_clog pg_multixact pg_twophase postgresql.conf recovery.conf
base pg_hba.conf pg_subtrans PG_VERSION postmaster.opts
修改文件夹权限
[postgres@localhost ~]$ chmod 0700 pgdata
启动数据库,数据库开始自动恢复
[postgres@localhost ~]$ pg_ctl start
pg_ctl: another server might be running;trying to start server anyway
LOG: database system was interrupted; last known up at 2013-05-14 10:54:47CST
LOG: starting archive recovery
LOG: restore_command = 'cp -f /home/postgres/wmpay_archive_log/%f %p'
LOG: restored log file "00000008.history" from archive
LOG: restored log file "000000080000000100000072.000000E0.backup"from archive
LOG: restored log file "000000080000000100000072" from archive
LOG: automatic recovery in progress
LOG: redo starts at 1/72000120
cp: 无法获取"/home/postgres/wmpay_archive_log/000000080000000100000073"的文件状态(stat): 没有那个文件或目录
LOG: unexpected pageaddr 1/56000000 in log file 1, segment 115, offset 0
LOG: redo done at 1/72000120
LOG: restored log file "000000080000000100000072" from archive
cp: 无法获取"/home/postgres/wmpay_archive_log/00000009.history" 的文件状态(stat): 没有那个文件或目录
LOG: selected new timeline ID: 9
LOG: restored log file "00000008.history" from archive
LOG: archive recovery complete
server starting
[postgres@localhost ~]$ LOG: database system is ready to acceptconnections
LOG: autovacuum launcher started
登陆数据库:
[postgres@localhost ~]$ psql
Welcome to psql 8.3.3, the PostgreSQLinteractive 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
查看wmpay数据库是否被恢复
postgres=# \l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
wmpay | postgres | UTF8
(4 rows)
连接wmpay数据库
postgres=# \c wmpay
You are now connected to database"wmpay".
查看数据
wmpay=# \d
List of relations
Schema| Name | Type | Owner
--------+----------------------------------------+----------+----------
public | SEQ_POS_OPERATION_LOG | sequence | postgres
public | SEQ_POS_TRANSACTION_FLOW | sequence | postgres
public | di_responsecode | table | postgres
public | m_seq_creator | table | postgres
public | pos_notice | table | postgres
public | pos_notice_id_seq | sequence | postgres
public | pos_operation_log | table | postgres
public | pos_operation_log_detail | table | postgres
public |pos_operation_log_operation_number_seq | sequence | postgres
public | pos_transaction_flow | table | postgres
public | pos_transaction_flow_detail | table | postgres
public |pos_transaction_flow_trans_number_seq |sequence | postgres
public | test_backup | table | postgres
public | version | table | postgres
public | version_id_seq | sequence | postgres
public | versioning | table |postgres
(16 rows)
wmpay=#