0

I am running a WAL log shipping standby/backup server configuration, and I have been using pg_basebackup and to create a full backup of a PostgreSQL database without issues for awhile. But as of this week, I had to use it again, and is failing with error below. I have tried increasing max_wal_senders to 20 and wal_sender_timeout to 600s but it is giving the same error. This error usually happens at the very end of the backup but sometimes it also happens in the middle. The title is the part of the error that appears most relevant.

WARNING:  terminating connection because of crash of another server process
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.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
pg_basebackup: error: could not receive data from WAL stream: SSL SYSCALL error: EOF detected
pg_basebackup: error: could not read COPY data: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
pg_basebackup: removing data directory "/var/lib/postgresql/12/main"

This is from what I can tell the relevant part of the log file from the last time it crashed when I tried this. The rest of the logs from this time are about terminating connections because of a server process crash just like the WARNING from the original error.

2022-12-30 04:40:51.058 CST [18121] nathan@ISO PANIC:  could not fdatasync file "000000040000038100000036": Input/output error
2022-12-30 04:40:51.058 CST [18121] nathan@ISO STATEMENT:  COMMIT
2022-12-30 04:40:51.239 CST [1746] LOG:  server process (PID 18121) was terminated by signal 6: Aborted

I am using PostgreSQL 12 on Ubuntu 18.04.

Improve this question
3
  • 1
    How is your disk space doing?
    – Mark Stewart
    Dec 30, 2022 at 19:20
  • 1
    Good. On the standby, 40GB of 915GB used. On the primary, 237GB of 439GB used. The DB is I believe about 199GB.
    – geckels1
    Dec 30, 2022 at 23:58
  • 2
    That suggests that you have a flaky disk, or some other component of the IO system.
    – jjanes
    Dec 31, 2022 at 15:50

1 Answer 1

Reset to default
0

While I'm not really sure why it worked, I changed wal_sender_timeout to 0 (to disable it) and added the parameter sslmode=disable to the pg_basebackup command, and it worked.

UPDATE: I tried with a new HD and was getting a timeout error. I then set wal_sender_timeout to 0, and it worked fine.

Improve this answer
2
  • 2
    Unless you can change those settings back and forth several times and consistently get the error under one setting and not get the error under the other, I would say you are chasing ghosts, and you just happened to got lucky once .
    – jjanes
    Dec 31, 2022 at 15:53
  • In doing research about this problem, sslmode=disable was suggested as a fix from a knowledgeable Postgres user. I also did a health check on the HD and it said everything was fine. I was doing a bunch of cloning of the HD that did not always go smoothly so I do have a hunch that effected the HD health even if it didn't show up in the check (or I read the report wrong). It's also been acting a touch flaky. Thx for your help!
    – geckels1
    Dec 31, 2022 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.