0

My PostgreSQL service is timing out. I have read here that if not provided, default timeout is 60 seconds and we can configure PGCTLTIMEOUT environment variable. I am not sure whether to add in for user or for system and how can I validate that it is working.

Since this timeout issue is rarely occurring I don't know how to test if it is working.

Update: Pated PG logs

2021-04-15 10:27:07.594 IST [21148] WARNING:  autovacuum not started because of misconfiguration 
2021-04-15 10:27:07.594 IST [21148] HINT:  Enable the "track_counts" option. 
2021-04-15 10:27:07.684 IST [9952] LOG:  database system was interrupted; last known up at 2021-01-27 22:17:24 IST 
2021-04-15 10:30:57.914 IST [9952] LOG:  database system was not properly shut down; automatic recovery in progress 
2021-04-15 10:30:57.935 IST [9952] LOG:  redo starts at 897/8CFDE078 
2021-04-15 10:31:42.132 IST [9952] LOG:  invalid record length at 897/935E1C78: wanted 24, got 0 
2021-04-15 10:31:42.132 IST [9952] LOG:  redo done at 897/935E1C50 
2021-04-15 10:31:42.132 IST [9952] LOG:  last completed transaction was at log time 2021-01-27 22:21:28.931241+05:30
2021-04-15 10:31:46.019 IST [21148] LOG:  database system is ready to accept connections

In event viewer I can see the timeout error and service was not up. I checked in task manager, there postgres.exe was running but not pg_ctl.exe.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Imad
  • 7,126
  • 12
  • 55
  • 112
  • What does the PostgreSQL log file say? – Laurenz Albe Jun 24 '21 at 12:20
  • @LaurenzAlbe Update the same in question... – Imad Jun 25 '21 at 06:21
  • Start it manually with `pg_ctl` and look at the errors. And enable `track_counts`, that may well have broken your database. – Laurenz Albe Jun 25 '21 at 06:41
  • @LaurenzAlbe Actually it does start on my local, or even on the same machine where it failed. It is intermittent. There is no problem with the database because I manually killed postgres.exe and restarted pg_ctl.exe. It worked and database was also fine – Imad Jun 25 '21 at 06:50

1 Answers1

0

The problem is that you don't shut down your database properly when you stop it, but you make it crash.

That makes it necessary to perform crash recovery on startup, as your log file shows. This can take longer or shorter, depending how much work has to be recovered. Sometimes that takes longer than the timeout, sometimes it doesn't. But even if pg_ctl times out, the server will eventually start.

The proper solution is to change your setup so that PostgreSQL is properly shut down instead of crashed.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263