Postgresql Query

On Mon, Aug 19, 2019 at 2:15 AM Bansal, Abhishek (Abhishek)
<bansal7@avaya.com> wrote:

Hello, Team,

Am running into a hard situation where few files are emptied with the below directory of the PostgreSQL.

The files marked as red are emptied which caused server behaving very bad, could you please let us know if there is any way we can retrieve these emptied files?

The data is probably gone. It's possible some of all may still reside
in the underlying disks depending how it was deleted and how much disk
activity has occurred since the deletion event but the restoration
process would be very complex with low chance of recovery.

If the database is allowing queries, Inside of the system catalogs,
you can figure out which tables those files are attached to; it looks
like two tables were impacted. The number before the period can be
matched against pg_class.relfilenode. Once you've identified the
tables, you need to restore them from backup and get the database back
in shape assuming you can't or don't want to do a full database
restore from backup. What happens so that he files were emptied?

merlin

Hi, Merlin,

Thank you for the reply

When files are emptied then it caused the outage for other servers using this server as a administration. The files were emptied because the disk space was keep increasing due so we emptied these files which eventually reduced the disk size however it caused another issues

Below is the directory where the files are emptied

/var/lib/pgsql/data/base

Abhishek

-----Original Message-----
From: Merlin Moncure <mmoncure@gmail.com>
Sent: Monday, August 19, 2019 7:09 PM
To: Bansal, Abhishek (Abhishek) <bansal7@avaya.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Postgresql Query

On Mon, Aug 19, 2019 at 2:15 AM Bansal, Abhishek (Abhishek) <bansal7@avaya.com<mailto:bansal7@avaya.com>> wrote:

Hello, Team,

Am running into a hard situation where few files are emptied with the below directory of the PostgreSQL.

The files marked as red are emptied which caused server behaving very bad, could you please let us know if there is any way we can retrieve these emptied files?

The data is probably gone. It's possible some of all may still reside in the underlying disks depending how it was deleted and how much disk activity has occurred since the deletion event but the restoration process would be very complex with low chance of recovery.

If the database is allowing queries, Inside of the system catalogs, you can figure out which tables those files are attached to; it looks like two tables were impacted. The number before the period can be matched against pg_class.relfilenode. Once you've identified the tables, you need to restore them from backup and get the database back in shape assuming you can't or don't want to do a full database

restore from backup. What happens so that he files were emptied?

merlin

On Mon, Aug 19, 2019 at 9:18 AM Bansal, Abhishek (Abhishek)
<bansal7@avaya.com> wrote:

Hi, Merlin,

Thank you for the reply

When files are emptied then it caused the outage for other servers using this server as a administration. The files were emptied because the disk space was keep increasing due so we emptied these files which eventually reduced the disk size however it caused another issues

Below is the directory where the files are emptied

/var/lib/pgsql/data/base

OK, your data is gone. Essentially, you destroyed it; making any
direct change to the files is extremely dangerous particularly if the
database is running. The right we to free space is to delete data
from the database at the SQL level and do maintenance operations (like
VACUUM FULL or CLUSTER) to mange the files down in terms of size.

I'm sorry fo the bad news but now it's time to move ahead. Since the
database volume is full there are no likely recovery options except to
restore the impacted from backup. With some effort you may be able to
get the current database back to a consistent state so that you may be
able to restore specific tables (rather than a full restore) but this
requires in depth knowledge of database internals. If you have no
database then the only way forward is to likely to do emergency
recovery to grab out everything that is salvageable and get a
consistent database back up.

merlin

Hi, Merlin,
Thank you for the update

Ok so there seems now no procedure, either complex, to restore the emptied files?
Would you like me to recommend few courses for PostgreSQL databases that will really help us understanding it better and nevertheless the troubleshooting steps?

Abhishek

-----Original Message-----
From: Merlin Moncure <mmoncure@gmail.com>
Sent: Monday, August 19, 2019 8:21 PM
To: Bansal, Abhishek (Abhishek) <bansal7@avaya.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Postgresql Query

On Mon, Aug 19, 2019 at 9:18 AM Bansal, Abhishek (Abhishek) <bansal7@avaya.com> wrote:

Hi, Merlin,

Thank you for the reply

When files are emptied then it caused the outage for other servers
using this server as a administration. The files were emptied because
the disk space was keep increasing due so we emptied these files which
eventually reduced the disk size however it caused another issues

Below is the directory where the files are emptied

/var/lib/pgsql/data/base

OK, your data is gone. Essentially, you destroyed it; making any direct change to the files is extremely dangerous particularly if the database is running. The right we to free space is to delete data from the database at the SQL level and do maintenance operations (like VACUUM FULL or CLUSTER) to mange the files down in terms of size.

I'm sorry fo the bad news but now it's time to move ahead. Since the
database volume is full there are no likely recovery options except to restore the impacted from backup. With some effort you may be able to get the current database back to a consistent state so that you may be able to restore specific tables (rather than a full restore) but this requires in depth knowledge of database internals. If you have no database then the only way forward is to likely to do emergency recovery to grab out everything that is salvageable and get a consistent database back up.

merlin

On Mon, Aug 19, 2019 at 10:03 AM Bansal, Abhishek (Abhishek)
<bansal7@avaya.com> wrote:

Hi, Merlin,
Thank you for the update

Ok so there seems now no procedure, either complex, to restore the emptied files?
Would you like me to recommend few courses for PostgreSQL databases that will really help us understanding it better and nevertheless the troubleshooting steps?

As you've described the situation, the data is gone. This is not
really a database problem; you emptied the files by hand. As it
relates to training, it seems like you need some. There are a lot of
very good free resources online but you may also want to read a book
or two.

merlin

Ok thank you, Merlin, for your time replying to my questions patiently
Have a good one

Abhishek

-----Original Message-----
From: Merlin Moncure <mmoncure@gmail.com>
Sent: Monday, August 19, 2019 8:48 PM
To: Bansal, Abhishek (Abhishek) <bansal7@avaya.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Postgresql Query

On Mon, Aug 19, 2019 at 10:03 AM Bansal, Abhishek (Abhishek) <bansal7@avaya.com> wrote:

Hi, Merlin,
Thank you for the update

Ok so there seems now no procedure, either complex, to restore the emptied files?
Would you like me to recommend few courses for PostgreSQL databases that will really help us understanding it better and nevertheless the troubleshooting steps?

As you've described the situation, the data is gone. This is not
really a database problem; you emptied the files by hand. As it
relates to training, it seems like you need some. There are a lot of very good free resources online but you may also want to read a book or two.

merlin

Hi, Merlin,

I've last question for this thread, sorry

Inside - /var/lib/pgsql/data/base - so can't we delete or empty any files? How we come to know which files don’t get affected?

Abhishek

-----Original Message-----
From: Bansal, Abhishek (Abhishek)
Sent: Monday, August 19, 2019 8:52 PM
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: RE: Postgresql Query

Ok thank you, Merlin, for your time replying to my questions patiently Have a good one

Abhishek

-----Original Message-----

From: Merlin Moncure <mmoncure@gmail.com<mailto:mmoncure@gmail.com>>

Sent: Monday, August 19, 2019 8:48 PM

To: Bansal, Abhishek (Abhishek) <bansal7@avaya.com<mailto:bansal7@avaya.com>>

Cc: pgsql-bugs@lists.postgresql.org<mailto:pgsql-bugs@lists.postgresql.org>

Subject: Re: Postgresql Query

On Mon, Aug 19, 2019 at 10:03 AM Bansal, Abhishek (Abhishek) <bansal7@avaya.com<mailto:bansal7@avaya.com>> wrote:

Hi, Merlin,

Thank you for the update

Ok so there seems now no procedure, either complex, to restore the emptied files?

Would you like me to recommend few courses for PostgreSQL databases that will really help us understanding it better and nevertheless the troubleshooting steps?

As you've described the situation, the data is gone. This is not

really a database problem; you emptied the files by hand. As it

relates to training, it seems like you need some. There are a lot of very good free resources online but you may also want to read a book or two.

merlin

"Bansal, Abhishek (Abhishek)" <bansal7@avaya.com> writes:

Inside - /var/lib/pgsql/data/base - so can't we delete or empty any files? How we come to know which files don’t get affected?

You shouldn't manually mess with *anything* under the data/ directory,
except for the documented configuration files (postgresql.conf etc).

Exceptions to this rule are very rare, and you'd better know exactly
what you're doing.

regards, tom lane

Sure, thank you Tom,

Abhishek

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, August 20, 2019 12:50 AM
To: Bansal, Abhishek (Abhishek) <bansal7@avaya.com>
Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: Postgresql Query

"Bansal, Abhishek (Abhishek)" <bansal7@avaya.com> writes:

Inside - /var/lib/pgsql/data/base - so can't we delete or empty any files? How we come to know which files don’t get affected?

You shouldn't manually mess with *anything* under the data/ directory, except for the documented configuration files (postgresql.conf etc).

Exceptions to this rule are very rare, and you'd better know exactly what you're doing.

regards, tom lane