Re: [ext] Re: Losing data because of problematic configuration?

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

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

From: Holtgrewe, Manuel <manuel.holtgrewe@bih-charite.de>
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!

From: Tom Lane <tgl@sss.pgh.pa.us>
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?
 
"Holtgrewe, Manuel" <manuel.holtgrewe@bih-charite.de> writes:
> 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 по дате отправления:

Предыдущее
От: "Peter J. Holzer"
Дата:
Сообщение: Re: Notify When Streaming Replication Failover Occurred (slave promoted to master).
Следующее
От: "Holtgrewe, Manuel"
Дата:
Сообщение: Re: [ext] Re: Losing data because of problematic configuration?