Re: [ext] Re: Losing data because of problematic configuration? - Mailing list pgsql-general
| From | Holtgrewe, Manuel |
|---|---|
| Subject | Re: [ext] Re: Losing data because of problematic configuration? |
| Date | |
| Msg-id | a983ed6d6b5140adbca00d4b4b5fc649@bih-charite.de Whole thread Raw |
| In response to | Re: [ext] Re: Losing data because of problematic configuration? ("Holtgrewe, Manuel" <manuel.holtgrewe@bih-charite.de>) |
| List | pgsql-general |
Please ignore this email. I could not reproduce it after all.
Dr. Manuel Holtgrewe, Dipl.-Inform.
Bioinformatician
Core Unit Bioinformatics – CUBI
Berlin Institute of Health / Max Delbrück Center for Molecular Medicine in the Helmholtz Association / Charité – Universitätsmedizin Berlin
Visiting Address: Invalidenstr. 80, 3rd Floor, Room 03 028, 10117 Berlin
Postal Address: Chariteplatz 1, 10117 Berlin
E-Mail: manuel.holtgrewe@bihealth.de
Phone: +49 30 450 543 607
Fax: +49 30 450 7 543 901
Web: cubi.bihealth.org www.bihealth.org www.mdc-berlin.de www.charite.de
Sent: Wednesday, June 16, 2021 10:54:49 AM
To: Tom Lane
Cc: pgsql-general@lists.postgresql.org
Subject: Re: [ext] Re: Losing data because of problematic configuration?
Hi again,
thank you for pinpointing the issue.
I have now updated the table with "ALTER TABLE $table SET LOGGED" (actually it's a partitioned table and I've altered both the main table and the partitions).
I wanted to double-check the result and what I found out using "select relpersistence, relname from pg_class" that, e.g., ${table_name}_383_pkey still has its relpersistence set to "u" whereas ${table_name}_383 has its relpersistence set to "p" now.
Does anyone have an idea what I'm doing wrong here?
Thank you!
Manuel
Sent: Tuesday, June 15, 2021 5:53:54 PM
To: Tom Lane
Cc: pgsql-general@lists.postgresql.org
Subject: Re: [ext] Re: Losing data because of problematic configuration?
>> < 2021-06-15 12:33:04.537 CEST > DEBUG: resetting unlogged relations: cleanup 1 init 0
>
> Are you perhaps keeping your data in an UNLOGGED table? If so, resetting
> it to empty after a crash is exactly what's supposed to happen. The
> entire point of UNLOGGED is that the performance benefits come at the
> cost of losing the data on crash.
D'oh!
Yes, that is the case. I once used this but I was convinced that I took this back in some version. It is not in my main deployment, though. Now I have to find out (a) why I have diverging deployment and (b) how that bug came about to be.
Thanks a lot!
Sent: Tuesday, June 15, 2021 3:39:31 PM
To: Holtgrewe, Manuel
Cc: pgsql-general@lists.postgresql.org
Subject: [ext] Re: Losing data because of problematic configuration?
> So it looks as if the database jumps back "half an hour" to ensure consistent data. Everything in between is lost.
Postgres does not lose committed data --- if it did, we'd consider that a
fairly serious bug. (Well, there are caveats of course. But most of them
have to do with operating-system crashes or power loss, neither of which
are at stake here.)
I am wondering about this though:
> < 2021-06-15 12:33:04.537 CEST > DEBUG: resetting unlogged relations: cleanup 1 init 0
Are you perhaps keeping your data in an UNLOGGED table? If so, resetting
it to empty after a crash is exactly what's supposed to happen. The
entire point of UNLOGGED is that the performance benefits come at the
cost of losing the data on crash.
regards, tom lane
pgsql-general by date: