Recent Postgres versions PANIC due to missing fsync functionality

[heldchen]

Please fill out the below information:

• Your Windows build number: 10.0.18334.1

• What you're doing and what's happening: postgresql recently changed their fsync handling, and since then, it shuts down in WSL after a few minutes - i.e. once it tries to flush files to the disk. https://www.postgresql.org/docs/11/release-11-2.html#id-1.11.6.5.5

• What's wrong / what should be happening instead:

root@devbox:/var/log/postgresql# tail postgresql-11-main.log
2019-02-18 15:33:55.464 CET [13679] LOG:  terminating any other active server processes
2019-02-18 15:33:55.464 CET [13686] WARNING:  terminating connection because of crash of another server process
2019-02-18 15:33:55.464 CET [13686] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2019-02-18 15:33:55.464 CET [13686] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2019-02-18 15:33:55.504 CET [13679] LOG:  all server processes terminated; reinitializing
2019-02-18 15:33:55.546 CET [14101] LOG:  database system was interrupted; last known up at 2019-02-18 15:28:52 CET
2019-02-18 15:33:55.547 CET [14101] PANIC:  could not flush dirty data: Function not implemented
2019-02-18 15:33:55.552 CET [13679] LOG:  startup process (PID 14101) was terminated by signal 6: Aborted
2019-02-18 15:33:55.552 CET [13679] LOG:  aborting startup due to startup process failure
2019-02-18 15:33:55.560 CET [13679] LOG:  database system is shut down

a workaround is to revert to previous version's behavior. this will result in warnings instead of a PANIC:

2019-02-18 15:48:28.778 CET [1550] postgres@postgres WARNING:  could not flush dirty data: Function not implemented
2019-02-18 15:48:28.787 CET [1550] postgres@postgres WARNING:  could not flush dirty data: Function not implemented
2019-02-18 15:48:28.792 CET [1550] postgres@postgres WARNING:  could not flush dirty data: Function not implemented
2019-02-18 15:48:28.798 CET [1550] postgres@postgres WARNING:  could not flush dirty data: Function not implemented
2019-02-18 15:48:28.803 CET [1550] postgres@postgres WARNING:  could not flush dirty data: Function not implemented
2019-02-18 15:48:28.805 CET [1550] postgres@postgres WARNING:  could not flush dirty data: Function not implemented
2019-02-18 15:48:28.812 CET [1550] postgres@postgres WARNING:  could not flush dirty data: Function not implemented
2019-02-18 15:48:28.818 CET [1550] postgres@postgres WARNING:  could not flush dirty data: Function not implemented
2019-02-18 15:48:28.820 CET [1550] postgres@postgres WARNING:  could not flush dirty data: Function not implemented
2019-02-18 15:48:28.820 CET [1550] postgres@postgres WARNING:  could not flush dirty data: Function not implemented

[therealkenc]

No strace log provided but an ENOSYS in this sort of context almost certainly points to #645.

[heldchen]

yeah had troubles getting an strace due to the multiple process spawned. sorry about that...

[therealkenc]

Appears to be this code now fires. Haven't tried, mind.

[RNinKyoto]

I've got the same problem. What should I do is back to postgresql9.3 according to this?

[RNinKyoto]

This made me understood

[kevin-klein]

"data_sync_retry = true" appended to the end of /etc/postgresql/10/main/postgresql.conf fixed it for me.

[aaronfulkerson]

This made me understood

In case anyone is here looking for an easy fix, setting fsync to off in postgresql.conf works also.

[pauloscardine]

"data_sync_retry = true" appended to the end of /etc/postgresql/10/main/postgresql.conf fixed it for me.

The screen is flooded with WARNING: could not flush dirty data: Function not implemented when I tried to create a database but otherwise seems to work.

[therealkenc]

this