Postgres的热备恢复

来源：互联网发布：gta5pc版美女捏脸数据编辑：程序博客网时间：2024/06/08 19:15

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=#