Thread: Get data back after drop Command
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.
Below is the list of files in my pg_xlog directory.
Thanks
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.
Below is the 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
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. > archive_mode = on > archive_command = '' If you were doing Point In Time Recovery (PITR) backups, you could restore your last base backups and recover using WAL files up to just before the table was dropped: http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html#BACKUP-PITR-RECOVERY The empty archive_command suggests that you're not doing proper PITR style backups, so it appears the answer to your question is "No." There is a chance that an expert might still be able to recover some of the data. That's likely to be time-consuming and expensive, but if the table is not backed up and has sufficient value to you, it might be worth it. -Kevin
Thanks for your post. Sometimes i apply small changes to live database. your accident warns me that recovery from backup is long and not happy. -- ------------ pasman