26

I have installed postgresql on windows 10 on usb disk. Every day when i start my pc in work from sleep and plug in the disk again then trying to start postgresql i get this error:

FATAL: the database system is starting up

The service starts with following command:

E:\PostgresSql\pg96\pgservice.exe "//RS//PostgreSQL 9.6 Server"

It is the default one.

logs from E:\PostgresSql\data\logs\pg96

2019-02-28 10:30:36 CET [21788]: [1-1] user=postgres,db=postgres,app=[unknown],client=::1 FATAL:  the database system is starting up
2019-02-28 10:31:08 CET [9796]: [1-1] user=postgres,db=postgres,app=[unknown],client=::1 FATAL:  the database system is starting up

I want this start up to happen faster.

Improve this question
3
  • Are you stopping the service before putting your PC to sleep/unplugging the disk? Commented Feb 28, 2019 at 9:48
  • No - i do not - is that the right thing to do? Commented Feb 28, 2019 at 9:55
  • Yes! (Details below) Commented Feb 28, 2019 at 10:41

1 Answer 1

Reset to default
43

When you commit data to a Postgres database, the only thing which is immediately saved to disk is the write-ahead log (WAL). The actual table changes are only applied to the in-memory buffers, and won't be permanently saved to disk until the next checkpoint.

If the server is stopped abruptly, or if it suddenly loses access to the file system, then everything in memory is lost, and the next time you start it up, it needs to resort to replaying the WAL in order to get the tables back to the correct state (which can take quite a while, depending on how much has happened since the last checkpoint). And until it's finished, any attempt to use the server will result in FATAL: the database system is starting up.

If you make sure you shut the server down cleanly before unplugging the disk - giving it a chance to set a checkpoint and flush all of its buffers - then it should be able to start up again more or less immediately.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Reducing checkpoint_timeout and/or max_wal_size causes checkpoints to occur more often. This allows faster after-crash recovery. But Checkpoints are fairly expensive, first because they require writing out all currently dirty buffers, and second because they result in extra subsequent WAL traffic. These are the important points to be noted in the above shared url!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.