Re: Would it be possible

From: Christian Ullrich
Subject: Re: Would it be possible
Date: ,
Msg-id: 4E2D1A52.6000803@chrullrich.net
(view: Whole thread, Raw)
In response to: Would it be possible  (Adarsh Sharma)
List: pgsql-general

Tree view

Would it be possible  (Adarsh Sharma, )
 Re: Would it be possible  (Christian Ullrich, )
 Re: Would it be possible  ("Albe Laurenz", )
 Re: Would it be possible  (Christian Ullrich, )
 Re: Would it be possible  (Craig Ringer, )

* 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.


pgsql-general by date:

From: vijay bikas
Date:
Subject: unable to find function to encrypt text using 3des algorithm with 3 independent keys
From: Achilleas Mantzios
Date:
Subject: Re: Disallow access from psql, or allow access only from specific client app