Postgres Pro
Downloads
Home   >   mailing lists

Thread: Get data back after drop Command

Get data back after drop Command

From
Adarsh Sharma
Date:
Dear all,

Today by mistake I issued a drop table statement in Postgresql database.
Now is it possible to get that data back through WAL.
My PostgresPLus Version : 8.4
OS : Linux ( CentOs )

My postgresql.conf parameters are :

# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

fsync = on
synchronous_commit = on
wal_sync_method = fsync

full_page_writes = on
wal_buffers = 64kB
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
checkpoint_segments = 32
checkpoint_timeout = 5min
checkpoint_completion_target = 0.5
checkpoint_warning = 30s
archive_mode = on
archive_command = ''
archive_timeout = 0


Please let me know the steps needed to get data back or ways to do.
PFA  list of files in my pg_xlog directory.

Thanks



[root@ pg_xlog]# ls -ls
total 1082668
16404 -rw------- 1 postgres postgres 16777216 Aug  3 16:12 000000010000000B00000068
16404 -rw------- 1 postgres postgres 16777216 Aug 18 15:03 000000010000000B00000069
16404 -rw------- 1 postgres postgres 16777216 Aug 24 16:09 000000010000000B0000006A
16404 -rw------- 1 postgres postgres 16777216 Aug 25 12:37 000000010000000B0000006B
16404 -rw------- 1 postgres postgres 16777216 Aug 25 15:13 000000010000000B0000006C
16404 -rw------- 1 postgres postgres 16777216 Aug 29 09:24 000000010000000B0000006D
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B0000006E
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B0000006F
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000070
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:57 000000010000000B00000071
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000072
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000073
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:57 000000010000000B00000074
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:57 000000010000000B00000075
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:57 000000010000000B00000076
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000077
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000078
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000079
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B0000007A
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:57 000000010000000B0000007B
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B0000007C
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:57 000000010000000B0000007D
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B0000007E
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B0000007F
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:57 000000010000000B00000080
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:57 000000010000000B00000081
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000082
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000083
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000084
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000085
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:57 000000010000000B00000086
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:57 000000010000000B00000087
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000088
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000089
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B0000008A
16404 -rw------- 1 postgres postgres 16777216 Jul  7 13:00 000000010000000B0000008B
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B0000008C
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B0000008D
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B0000008E
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B0000008F
16404 -rw------- 1 postgres postgres 16777216 Jul  7 13:00 000000010000000B00000090
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000091
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:58 000000010000000B00000092
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B00000093
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B00000094
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B00000095
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B00000096
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B00000097
16404 -rw------- 1 postgres postgres 16777216 Jul  7 13:00 000000010000000B00000098
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B00000099
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B0000009A
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B0000009B
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B0000009C
16404 -rw------- 1 postgres postgres 16777216 Jul  7 13:00 000000010000000B0000009D
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B0000009E
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B0000009F
16404 -rw------- 1 postgres postgres 16777216 Jul  7 13:00 000000010000000B000000A0
16404 -rw------- 1 postgres postgres 16777216 Jul  7 13:00 000000010000000B000000A1
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B000000A2
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B000000A3
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B000000A4
16404 -rw------- 1 postgres postgres 16777216 Jul  7 13:00 000000010000000B000000A5
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B000000A6
16404 -rw------- 1 postgres postgres 16777216 Jul  7 12:59 000000010000000B000000A7
16404 -rw------- 1 postgres postgres 16777216 Jul  7 13:00 000000010000000B000000A8
16404 -rw------- 1 postgres postgres 16777216 Jul  7 13:00 000000010000000B000000A9
    4 drwx------ 2 postgres postgres     4096 Aug 25 15:13 archive_status

Re: Get data back after drop Command

From
John R Pierce
Date:
On 08/28/11 9:33 PM, Adarsh Sharma wrote:
> Today by mistake I issued a drop table statement in Postgresql database.
> Now is it possible to get that data back through WAL.

when's your last full backup from?

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast