Re: [ext] Re: Losing data because of problematic configuration?
| От | Holtgrewe, Manuel |
|---|---|
| Тема | Re: [ext] Re: Losing data because of problematic configuration? |
| Дата | |
| Msg-id | a983ed6d6b5140adbca00d4b4b5fc649@bih-charite.de обсуждение исходный текст |
| Ответ на | Re: [ext] Re: Losing data because of problematic configuration? ("Holtgrewe, Manuel" <manuel.holtgrewe@bih-charite.de>) |
| Список | 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 по дате отправления:
Предыдущее
От: "Holtgrewe, Manuel"Дата:
Сообщение: Re: [ext] Re: Losing data because of problematic configuration?