Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 67770B5DC3B for ; Mon, 29 Aug 2011 01:48:09 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 56167-01 for ; Mon, 29 Aug 2011 04:48:02 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp152.dfw.emailsrvr.com (smtp152.dfw.emailsrvr.com [67.192.241.152]) by mail.postgresql.org (Postfix) with ESMTP id C94BAB5DC37 for ; Mon, 29 Aug 2011 01:48:01 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp25.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id BCE6E2D0168; Mon, 29 Aug 2011 00:48:00 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp25.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 5694C2D01C9 for ; Mon, 29 Aug 2011 00:47:59 -0400 (EDT) Message-ID: <4E5B1A0E.8030805@orkash.com> Date: Mon, 29 Aug 2011 10:18:14 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Get data back after drop Command Content-Type: multipart/alternative; boundary="------------060406090705080907020801" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201108/219 X-Sequence-Number: 38206 This is a multi-part message in MIME format. --------------060406090705080907020801 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 ------------------------------------------------------------------------ [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 --------------060406090705080907020801 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
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 




[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
--------------060406090705080907020801--