Thread: Would it be possible

Dear all,

I am using Postgres-8.4.2 on Windows system.
I have 2 databases in my postgres database ( globedatabase (21GB), urldatabase).

I restore globedatabase from a .sql file on yesterday morning.I insert some new data in that database.
In the evening, by mistake I issued a drop database globedatabase command.

Today morning, I restore again the same database from backup (.sql) file.
My .sql file have data till yesterday morning but I want newly insert data now. Is it possible.

Is it possible to get the data back till the state before drop database command.

My pglog files is in the E:/data directory & Binary log is also enabled.

Please let me know if it is possible. It's urgent.


Thanks & Regards
Adarsh Sharma

* Adarsh Sharma wrote:

> In the evening, by mistake I issued a *drop database globedatabase* command.

> Is it possible to get the data back till the state before drop database
> command.
>
> My pglog files is in the E:/data directory & Binary log is also enabled.

You do not mention that you have a file-system level backup from before
the DROP DATABASE. Assuming you do not have one, then no, it is not
possible. You cannot restore WAL to a cluster that is not in the same
state it was in when that WAL was generated.

If you have the fs-level backup (the kind of backup you need to use
pg_start_backup()/pg_stop_backup() to create), as well as all WAL
segments from before the time of that backup until the moment you want
to go back to, then you can do it (but only for the entire cluster, so
you might need to install Postgres somewhere else just for recovery).
Look in the manual under "Point In Time Recovery" (PITR).

--
Christian

Adarsh Sharma wrote:

> I am using Postgres-8.4.2 on Windows system.
> I have 2 databases in my postgres database ( globedatabase (21GB),
urldatabase).
>
> I restore globedatabase from a .sql file on yesterday morning.I insert
some new data in that database.
> In the evening, by mistake I issued a drop database globedatabase
command.
>
> Today morning, I restore again the same database from backup (.sql)
file.
> My .sql file have data till yesterday morning but I want newly insert
data now. Is it possible.
>
> Is it possible to get the data back till the state before drop
database command.
>
> My pglog files is in the E:/data directory & Binary log is also
enabled.
>
> Please let me know if it is possible. It's urgent.

I assume you mean "transaction log" when you say "binary log".

It is possible to restore your database if you have a file system backup
of the database in some state before the "DROP DATABASE" command.
Then you could start from there and restore the database to a point
just before the DROP.

If you have no file system backup, there is no way.
See
http://www.postgresql.org/docs/current/static/continuous-archiving.html

Yours,
Laurenz Albe

* Adarsh Sharma wrote:

> I have following files in my pg_xlog directory :
>
> 000000010000000700000091
[...]
> 000000010000000700000098
>
> I think I issued the drop database command 1 month ago.
>  From the manual, I understand that my segment files are recycled to
> newer ones :

PostgreSQL always writes WAL, but to be able to use it for PITR, you
have to use WAL archiving. If you don't, the log files are only usable
for crash recovery (bringing the tables back to a consistent state after
the power fails or Postgres or your OS crashes). I recommend that you
read the whole "Backup and Restore" chapter in the manual, and set up a
test environment in which you can do some experiments to make sure you
understand how the system works and what you can do in any given situation.

> /My archive_status folder is empty.
> How would we know that which data these segment files corresponds too.

WAL is a continuous stream of changes to the database, on a fairly low
level. Inserting data into a table affects not only the table itself,
but also indexes, maybe some statistics. The WAL files contain all these
individual updates, mixed together. If you still had the WAL from when
you inserted the data, it might be possible to extract the raw data from
them (other people have tried), but ...

> I followed below steps 1 month ago :
> 1. Load globdatabase through backup.sql (21 GB)file
> 2. Insert some data near about 3-4 tables ( KB) data.
> 3. Drop database globdatabase.
> 4. Load globdatabase through backup.sql (21GB)file
>
> May be there is chance because we work very rarely on that system.
> Now i have the backup file bt I want that 3-4 tables.

... by reloading the database after the DROP without WAL archiving
enabled, the system has already recycled those log segments you are
interested in many hundred times over.

On 25/07/11 13:11, Adarsh Sharma wrote:

> I restore globedatabase from a .sql file on yesterday morning.I insert
> some new data in that database.
> In the evening, by mistake I issued a *drop database globedatabase* command.

Did you make a copy of the database files as soon as you realized what
you had done?

If you did not, you can probably not recover the data because you have
kept using the database. It will have overwritten your deleted data with
other data by now.

> Today morning, I restore again the same database from backup (.sql) file.

Most likely you destroyed your old data by doing that.

> Please let me know if it is possible. It's urgent.

If it's also really important, stop using PostgreSQL RIGHT NOW. Shut it
down. Then call an expert who might be able to help you, if you're
really really lucky.

  http://www.postgresql.org/support/professional_support