pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedGet data back after drop Command
3+ messages / 3 participants
[nested] [flat]
* Get data back after drop Command
@ 2011-08-29 04:48 Adarsh Sharma <adarsh.sharma@orkash.com>
2011-08-29 23:39 ` Re: Get data back after drop Command pasman pasmański <pasman.p@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Adarsh Sharma @ 2011-08-29 04:48 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Get data back after drop Command
2011-08-29 04:48 Get data back after drop Command Adarsh Sharma <adarsh.sharma@orkash.com>
@ 2011-08-29 23:39 ` pasman pasmański <pasman.p@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: pasman pasmański @ 2011-08-29 23:39 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Get data back after drop Command
@ 2011-08-29 12:33 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2011-08-29 12:33 UTC (permalink / raw)
To: adarsh.sharma@orkash.com; pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-08-29 23:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-29 04:48 Get data back after drop Command Adarsh Sharma <adarsh.sharma@orkash.com>
2011-08-29 23:39 ` pasman pasmański <pasman.p@gmail.com>
2011-08-29 12:33 Re: Get data back after drop Command Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox