I just spent several hours debugging why my PostgreSQL installation was so slow after a system reboot induced by a power outage. It seems at some point recently after I upgraded my PostgreSQL package, the init/configuration script was updated. The default value for PGOPTS was set to "-N 512 -B 1024". I didn't know what these options meant, so I assumed they were sane defaults. However unfortunately, the -B option overrides the shared_buffers setting in postgresql.conf with a much lower value than I need. It took me a long time and much messing around with sysctl, trying to work out why Postgres was always allocating 8MB of shared buffers no matter what I put in the configuration file, without generating any warnings, before I realized that it must have been this change which triggered my performance problems. The default for PG_OPTS should be empty. Users can override it if they need to. However, in my experience the postgresql documentation tells you to configure these parameters through postgresql.conf, not through the commandline or /etc/conf.d/postgresql. At the very least I'd expect a warning upon start-up if shared_buffers and the -B parameter specify different values. Otherwise it's too confusing to work out what's going on. Reproducible: Always Steps to Reproduce: 1. Create a PostgreSQL database 2. Edit postgresql.conf, set shared_buffers to say 2048 3. Start PostgreSQL 4. Run psql and enter the command "show shared_buffers;". It will show 8MB, despite the configuration file asking for 16MB. 5. Edit /etc/conf.d/postgresql, remove the -B option and restart postgres. Shared buffers will now be the expected value. Actual Results: described above Expected Results: described above
PG_OPTS is still available but commented out per default (in dev-db/postgresql-server)