PostgreSQL单个表误删除进行恢复
PostgreSQL单个表误删除进行恢复最后编辑时间:2022年1月23日00:09:24恢复步骤:1)首先找到最近的备份文件(日常备份必不可少)2)创建一个新的数据库,并用备份文件进行恢复3)在新的数据库中将目标表的数据进行导出4)将该表数据进行恢复以下为测试示例:[postgres@rhel6wcb ~]$ pg_dump -h 127.0.0.1 -p 5432 -d test -Fc -f
·
PostgreSQL单个表误删除进行恢复
最后编辑时间:2022年1月23日00:09:24
恢复步骤:
1)首先找到最近的备份文件(日常备份必不可少)
2)创建一个新的数据库,并用备份文件进行恢复
3)在新的数据库中将目标表的数据进行导出
4)将该表数据进行恢复
以下为测试示例:
[postgres@rhel6wcb ~]$ pg_dump -h 127.0.0.1 -p 5432 -d test -Fc -f pg_dump.dmp
[postgres@rhel6wcb ~]$ ls -lth
total 35M
-rw-rw-r-- 1 postgres postgres 35M Dec 2 22:23 pg_dump.dmp
[postgres@rhel6wcb ~]$ psql
psql (11.7)
Type "help" for help.
postgres@postgres=>\c test
You are now connected to database "test" as user "postgres".
postgres@test=>\dt+ test_big1
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+----------+--------+-------------
public | test_big1 | table | postgres | 280 MB |
(1 row)
postgres@test=>select count(*) from test_big1;
count
---------
5000000
(1 row)
postgres@test=>drop table test_big1;
DROP TABLE
postgres@postgres=>create database test2;
CREATE DATABASE
[postgres@rhel6wcb ~]$ pg_restore -h 127.0.0.1 -p 5432 -d test2 pg_dump.dmp
[postgres@rhel6wcb ~]$ psql
psql (11.7)
Type "help" for help.
postgres@postgres=>\c test2
You are now connected to database "test2" as user "postgres".
postgres@test2=>\dt+ test_big1;
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+----------+--------+-------------
public | test_big1 | table | postgres | 280 MB |
(1 row)
[postgres@rhel6wcb ~]$ pg_dump -h 127.0.0.1 -p 5432 -d test2 -t test_big1 -Fp -f test_big1.sql
[postgres@rhel6wcb ~]$ ls -lht
total 261M
-rw-rw-r-- 1 postgres postgres 227M Dec 2 22:27 test_big1.sql
-rw-rw-r-- 1 postgres postgres 35M Dec 2 22:23 pg_dump.dmp
[postgres@rhel6wcb ~]$ psql test <test_big1.sql
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 5000000
CREATE INDEX
[postgres@rhel6wcb ~]$ psql
psql (11.7)
Type "help" for help.
postgres@postgres=>\c test
You are now connected to database "test" as user "postgres".
postgres@test=>\dt+ test_big1;
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+----------+--------+-------------
public | test_big1 | table | postgres | 280 MB |
(1 row)
postgres@test=>select count(*) from test_big1;
count
---------
5000000
(1 row)
到此误删除的表的数据已经恢复。
更多推荐
所有评论(0)