0

I have an application in C# that should set shared_buffers of postgresql server to at least 512MB on the app's first start. So, I need to perform the following operations in my code:

  1. Check if shared_buffers are less than 512MB.
  2. Set shared_buffers to 512MB.
  3. Restart postgresql server in order to apply changes.
  4. Continue running application after restart is completed.

How to do this in code?

  • Does `shared_buffers` values change after your app's first start ? if so how ? – Vivek S. Jul 21 '15 at 09:07
  • No, I need to change it only once on the first start. Users probably have default postgresql settings when they start application at the first time. –  Jul 21 '15 at 10:00
  • So why you need _Check if shared_buffers are less than 512MB._ ? – Vivek S. Jul 21 '15 at 10:04
  • Because possibly user set it manually to higher value and I don't need to change it. –  Jul 21 '15 at 11:00

1 Answers1

2

In PostgreSQL 9.4 and above you can use ALTER SYSTEM SET to modify postgresql.auto.conf to add parameters.

So you could:

SHOW shared_buffers;

and if it's too low:

ALTER SYSTEM
  SET shared_buffers = '512MB';

You must then restart PostgreSQL. This, by design, cannot be done from within PostgreSQL because if there's a problem during restart then you can't connect to it anymore, so you could lock yourself out too easily. You can reload the config with SELECT pg_reload_conf(), but shared_buffers only takes effect at full restart, not just a config reload.

So your app will need to use an external OS-specific and install-method-specific way to restart PostgreSQL once you've made the config change. Or just tell the user to restart it for you.

To restart:

  • For any platform (Windows, Mac or Linux) with PostgreSQL custom installed and managed, use pg_ctl restart.

  • On Windows, installed as a service: use net stop [servicename] then net start [servicename]

  • On older Linux (pre-systemd) with PostgreSQL installed system-wide: Use the service command, e.g. service restart [servicename]. This must be run as root or via sudo.

  • On newer Linux (systemd) with PostgreSQL installed system-wide: Use systemctl restart [servicename].service

If you're using an older PostgreSQL without ALTER SYSTEM SET you'll have to open, modify and write postgresql.conf.

To avoid having to parse and modify the whole postgresql.conf I find it useful to append the include_dir = 'conf.d' option and then create a conf.d/myapp.conf in the datadir with just the config overrides I want.

include_dir is available in 9.3 and newer. For still-older versions you've just got to modify postgresql.conf directly.

If your app is responsible for installing and setting up PostgreSQL in the first place you can just set the desired shared_buffers in the config file after initdb and before starting up the database.

If you are bundling PostgreSQL in your application this is the approach I strongly prefer. Don't use the installer, just bundle the binaries in your installer. Start PostgreSQL when your app starts by invoking the postgres command directly, or by using pg_ctl. Stop it when your program exits. Use a non-default port (i.e. don't use port 5432), do not install under a postgres user, put the data directory into something like %PROGRAMDATA%\MyApp\postgres and otherwise avoid conflicting with official PostgreSQL installs. This will save you a lot of grief.

See:

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1. How to get a full path to `postgresql.conf`? 2. Is it safe and effective enough to stop postgresql server after application exit? I see standart postgresql server installed as a service in Windows is doing some work with database after application has been shut down. Also, what if app closed unexpectedly or user just killed the process? Then, myapp_postgres server will continue to run. –  Jul 22 '15 at 06:59
  • 1
    @AndrewPlakhotnyi `SHOW config_file` gives you the config file location, either as a full path or relative to the data directory (`SHOW data_directory`). Of course that's with the server running. The server looks for the config file in the datadirectory by default, unless you start it with command line options telling it to look elsewhere. – Craig Ringer Jul 22 '15 at 07:09
  • @AndrewPlakhotnyi As for being safe to app manage it - honestly, nearly impossible to say since I know nothing about your application. It's generally reasonable enough, and it's not like it matters much if PostgreSQL stays running after an app crash/force quit. Your app just has to notice that it's already running when it starts back up. Alternately, you can register it as a Windows service (but *use a different port, data directory and service name to the default PostgreSQL installs please*) using `pg_ctl register` then use the usual Windows APIs for service management. – Craig Ringer Jul 22 '15 at 07:11
  • @AndrewPlakhotnyi Depends mainly on whether you want multiple user accounts on the same machine to share the same data in your app or not. Both approaches are fine IMO. – Craig Ringer Jul 22 '15 at 07:12
  • @CraigRinger, opinions on difference btwn `pg_ctl restart` and `net stop ...` on a Windows machine? – raphael Aug 17 '16 at 17:40
  • 1
    @raphael If it's configured as a Windows service, use `net`. If it's running standalone, use `pg_ctl`. – Craig Ringer Aug 18 '16 at 00:38