On 6/15/21 5:42 AM, Holtgrewe, Manuel wrote:
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.
