Re: Losing data because of problematic configuration? - Mailing list pgsql-general
| From | Ron |
|---|---|
| Subject | Re: Losing data because of problematic configuration? |
| Date | |
| Msg-id | 328bf795-e499-4272-d1f7-e273ea0d4b9a@gmail.com Whole thread Raw |
| In response to | Losing data because of problematic configuration? ("Holtgrewe, Manuel" <manuel.holtgrewe@bih-charite.de>) |
| Responses |
Re: [ext] Re: Losing data because of problematic configuration?
("Holtgrewe, Manuel" <manuel.holtgrewe@bih-charite.de>)
|
| List | pgsql-general |
On 6/15/21 5:42 AM, Holtgrewe, Manuel wrote:
What kind of transaction did you use?
Did you commit the transaction?
Why?
Did you CHECKPOINT beforehand?
(I'm hypothesizing that data didn't get flushed to disk, and so Pg "cleaned itself up" after the crash.)
Hi,
I have a database that is meant to have high-performance for bulk insert operations. I've attached my postgres.conf file.
However, I'm seeing the following behaviour. At around 12:04, I have started the database. Then, I did a bulk insert and that completed.
What kind of transaction did you use?
Did you commit the transaction?
I then went on to kill postgres processes at 12:33 with SIGSEGV signal.
Why?
Did you CHECKPOINT beforehand?
(I'm hypothesizing that data didn't get flushed to disk, and so Pg "cleaned itself up" after the crash.)
I'm getting the following log excerpt:
< 2021-06-15 12:33:03.656 CEST > LOG: database system was interrupted; last known up at 2021-06-15 12:04:13 CEST
< 2021-06-15 12:33:03.656 CEST > DEBUG: removing all temporary WAL segments
< 2021-06-15 12:33:04.525 CEST > DEBUG: checkpoint record is at 60/7C377C78[snip]
< 2021-06-15 12:33:04.537 CEST > DEBUG: resetting unlogged relations: cleanup 1 init 0
< 2021-06-15 12:33:04.553 CEST > DEBUG: unlinked file "base/16384/107877"
[... snip ... ]
< 2021-06-15 12:33:27.556 CEST > DEBUG: copying base/16384/80948_init to base/16384/80948
< 2021-06-15 12:33:36.705 CEST > DEBUG: performing replication slot checkpoint
< 2021-06-15 12:33:38.394 CEST > DEBUG: attempting to remove WAL segments older than log file 00000000000000600000007C
< 2021-06-15 12:33:38.394 CEST > DEBUG: removing write-ahead log file "00000001000000600000007C"
< 2021-06-15 12:33:38.403 CEST > DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 1
< 2021-06-15 12:33:38.419 CEST > DEBUG: oldest MultiXactId member is at offset 1
< 2021-06-15 12:33:38.419 CEST > DEBUG: MultiXact member stop limit is now 4294914944 based on MultiXact 1
< 2021-06-15 12:33:38.428 CEST > DEBUG: shmem_exit(0): 1 before_shmem_exit callbacks to make
< 2021-06-15 12:33:38.428 CEST > DEBUG: shmem_exit(0): 5 on_shmem_exit callbacks to make
So it looks as if the database jumps back "half an hour" to ensure consistent data. Everything in between is lost.
What would be configuration settings to look into to make this more stable? Is there a way to "force flush state to disk" from the application/through postgres API/SQL?
Thank you,
Manuel
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
pgsql-general by date: