How do you set the shared_buffers parameter in PostgreSQL 14 on Windows machine?

Question

I have PostgreSQL 14 server running on Windows 10. I have read here that the default value for the shared_buffer parameter is low and increasing it can improve performance for some jobs. However, it appears that the shared_buffer parameter cannot be increased if the server (or database?) has already been created.

So, my question is: at what point in the setup process can I increase the value of this parameter on my machine?

Changing it just requires you to restart the database (thus momentarily severing all existing connections), not recreate it. — jjanes, Sep 07 '22 at 16:53

score 3 · Accepted Answer · edited Sep 08 '22 at 05:35

3

Changing shared_buffers does not require setting up a new database cluster.

Specifically, the manual says:

This parameter can only be set at server start

In Windows terms, it means that any new values are applied at the time the Postgres Service starts.

You should be able to change the value in postgresql.conf (which is usually located at C:\Program Files\PostgreSQL\<version_number>\data\postgresql.conf) and then restart the Postgres service

Since you're using v. 14, you may also opt to use ALTER SYSTEM instead of fiddling with postgresql.conf:

ALTER SYSTEM SET shared_buffers TO <your_value>;

And then restart the service

edited Sep 08 '22 at 05:35

Frank Heikens

117,544
24
142
135

answered Sep 07 '22 at 17:04

richyen

8,114
4
13
28

is postgresql.conf a file? Where do I find it? – jtam Sep 07 '22 at 17:06
yes, it's a config file -- try looking in `C:\Program Files\PostgreSQL\14\data\postgresql.conf`? – richyen Sep 07 '22 at 17:09
How do I run this alter query if I can't access the database? Editing postgresql.conf isn't allowing any access either. – Kwahn Feb 28 '23 at 03:31

nbk · Answer 2 · 2022-09-07T18:05:50.153

1

that is not what the manual of postgres says,

It only states, after increasing or decreasing the value , you need to restart the server, because it allocates the new memory size while starting.

see manual

edited Sep 07 '22 at 18:05

answered Sep 07 '22 at 17:01

nbk

45,398
8
30
47

How do you set the shared_buffers parameter in PostgreSQL 14 on Windows machine?

2 Answers2