Re: Losing data because of problematic configuration?

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.