5. Configuration

By the end of this chapter: You know where each setting is, what to change and how, and when a change takes effect.

5.1 Map of configuration locations

5.2 Managed block

The installation script writes this block at the end of postgresql.conf. The script rewrites it at every reinstallation, upgrade, and reinitialization.

# >>> gritdb managed begin
(required block)
port = <port>
(tuning block — absent in a --no-config installation)
# <<< gritdb managed end

Do not change the required block.

You adjust the tuning block to the server specification.

• The installation script calculates the three memory values (shared_buffers, effective_cache_size, and work_mem) from the server memory. It writes them at the end of the tuning block. The line just before them is # memory-derived by install_gritdb.sh: …. The calculation is in 5.5.

Lines that you write yourself outside or inside the block behave as follows. When the same setting appears on several lines, PostgreSQL uses the last line (configuration file).

So do not edit postgresql.conf directly. Change settings with ALTER SYSTEM (5.3).

⚠ Do not delete or edit the two marker lines (# >>> gritdb managed begin and # <<< gritdb managed end)

• Reason: The installation script finds the block by the two markers. If a marker is broken, the next installation or upgrade can lose settings in postgresql.conf (QuickStart, chapter 6, prohibition 4).
• Instead: Change settings with ALTER SYSTEM (5.3).

5.3 How to change settings and precedence

Change settings that you keep for a long time with ALTER SYSTEM. ALTER SYSTEM values take precedence over the managed block and remain after a reinstallation or upgrade.

ALTER SYSTEM SET <setting> = '<value>';

• For when a change takes effect, see 5.4 (restart, reload).
• To check, run SELECT pg_reload_conf(); first. Do this for restart settings too. Then run SELECT name, setting, source, pending_restart FROM pg_settings WHERE name = '<setting>';. For a reload setting, setting shows the new value. For a restart setting, setting still shows the old value, and pending_restart is t. In that case, follow the restart procedure in 5.4.
• To revert, run ALTER SYSTEM RESET <setting>;. For when it takes effect, see 5.4.

Exceptions:

• After a reinitialization (3.1), there are no ALTER SYSTEM values. So look at the old postgresql.auto.conf and add them again.

When you change the time zone, change the server time zone and the log time zone together.

ALTER SYSTEM SET timezone = '<time zone>';
ALTER SYSTEM SET log_timezone = '<time zone>';
SELECT pg_reload_conf();

• An example of <time zone> is Asia/Seoul. The settings are described in TimeZone and log_timezone.
• To check, run SHOW timezone; in a new session. It returns <time zone>.
• Analytics sessions (GritDB engine) do not use this setting for dates and times. They use the OS time zone at the time GritDB started. If the starting shell has TZ, they use that value. So set timezone only to the same value as the OS time zone.
• To change the time zone, change the OS time zone, timezone, and log_timezone to the same value. Then restart (the restart procedure in 5.4). The OS administrator changes the OS time zone with timedatectl set-timezone <time zone>. Check that Time zone in timedatectl and SHOW timezone; are the same.
• For the time zone of analytics session results, see 1.8 and 10.3.

5.4 When changes take effect

Each setting takes effect at a different time. Use context in pg_settings to find out when.

SELECT name, setting, context, pending_restart FROM pg_settings WHERE name IN ('<setting>');

• ALTER SYSTEM only writes to postgresql.auto.conf, so settings other than postmaster settings take effect only after pg_reload_conf().
• If pending_restart is t, the setting takes effect only after a restart. The value becomes t only after a reload.
• Do not send the HUP signal to GritDB processes. If you do, the process terminates (4.3). Reload only with SELECT pg_reload_conf();.
• To apply postmaster settings (max_connections, max_worker_processes, and so on), restart all of GritDB. Do not restart PostgreSQL alone. If you do, the values that GritDB computed at start stay out of date.
• If you change max_connections, max_worker_processes, autovacuum_max_workers, or max_wal_senders, look for the shm_limit_mb warning in 5.6 after the restart. Do the same if the vCPU count changes (virtual server resize). To see the values that GritDB read at start, run grep 'channel capacity inputs' ~/gritdb1/log/grit_admin_main.log | tail -1.

Restart procedure (for settings to take effect):

1. Stop the applications, poolers, reports, and BI, CDC, and ETL tools, and disconnect the remaining sessions (4.2 “Disconnect remaining sessions”).
2. Run ~/gritdb1/bin/gritdb-stop. Then do the stop completion check (4.2).
3. See 4.1 for TMPDIR, and run ~/gritdb1/bin/gritdb-start. Then run pg_isready twice (4.1).
4. To check, run the check query in 5.3. setting shows the new value, and pending_restart is f.

• If you do not disconnect the sessions, the normal stop (4.2) switches to a quick stop. With open sessions left, it switches after about 30 seconds. With running statements, it switches after up to 300 seconds. This can leave a core file (9.6). The next start takes the recovery path (4.3). When the stop switches, log/grit_admin_main.log gets a falling back to QuickStop line.

If the restart fails, the start screen shows GritAdmin process exited … (QuickStart GDB-BOOT-005). In that case, follow the steps below.

1. Find the cause in the FATAL: or ASSERT FAILED: lines of tail -20 ~/gritdb1/log/postgresql.log and in the abort record log/GRIT_SURFER_ASSERT.log (9.1). For example, could not map anonymous shared memory means that shared_buffers is too large. ASSERT FAILED: GritSurfer: max_worker_processes=<value> exceeds … means that the value is more than 256 (6.4.1 step 5).
2. Check that GritDB is stopped (stop completion check in 4.2).
3. Only while the server is down, make a copy of ~/gritdb1/datafiles/pgdata/postgresql.auto.conf and delete the line of the setting that you just changed. Do not edit this file while the server is running (configuration file). For an engine setting, set the value in that file back to the default in the table in 5.6.
4. Start again (4.1), and then do the check in 5.3. The start after a failed start goes through recovery (4.3). grit_admin_main.log then has DetectScenario: ABNORMAL_RESTART.

5.5 Recommended values by server size

The installation script sets the three PostgreSQL memory values from the server memory and writes them at the end of the tuning block (5.2). It calculates them again at every reinstallation, upgrade, and reinitialization.

The table below shows example values by memory. For the actual values, see the line on the installation screen.

• To check, see the [INFO] PostgreSQL memory: … line on the installation screen (QuickStart 3.2). The # memory-derived by install_gritdb.sh: … line in the managed block also shows the values (5.2).
• If the PostgreSQL share is under 4 GB, the script writes no values. In that case, the installation screen shows [WARN] PostgreSQL memory share is below 4GB …, and PostgreSQL runs with its defaults.
• The calculation assumes 100 connections (the max_connections default). So if you raise max_connections, lower work_mem in the same ratio. Use ALTER SYSTEM to lower it (5.3).
• To change a value, override it with ALTER SYSTEM (5.3). If you edit inside the managed block, the next installation replaces it with a newly calculated value (5.2).
• 1.1 has no setting that reduces the vCPUs and memory that GritDB uses, so size the server to fit (1.3).
• The other values in the tuning block assume 200 GB or more of free disk (1.3). For example, max_wal_size is 64 GB.

5.6 GritDB engine configuration

• The files are ~/gritdb1/config/grit_{burst,plug,surfer,unify}.config. Write key = value on one line (spaces around = are optional). Lines that start with # are comments. Do not add a comment at the end of a line. With key = 8 # memo, the value becomes 8 # memo, and GritDB does not start. Write a comment on its own line.
• If the same key appears on several lines, GritDB uses the last line. So when you edit a key, edit its last line.
• A change takes effect only after a restart (the restart procedure in 5.4).
• The installation script does not overwrite existing configuration files. For new keys at an upgrade, see 3.4 and 3.5.

⚠ Do not delete keys from the configuration files

• Reason: If even one key is missing, GritDB does not start.
• Instead: Change only the values. To go back to a default, use the default in the table below.

• If read_threads or shm_limit_mb is out of range, GritDB does not start. If connect_timeout_ms is out of range, GritDB uses the default.
• After editing, restart (the restart procedure in 5.4). Then check that each key uses the new value.
  • For all three keys, run grep -F '[<key>] =' ~/gritdb1/log/grit_surfer_main.log | tail -1 to check the value read. It returns [<key>] = <new value>. At start, the GritDB engine logs one line per key with the value that it read from the configuration file. If the value differs, check the key name in the line you edited again. Also check that this line is the last line for that key.
  • For read_threads, run grep 'read_threads=' ~/gritdb1/log/grit_catalog_processor.log | tail -1. It returns CatalogProcessor: starting (read_threads=<new value>, …).
  • For shm_limit_mb, run grep 'channel capacity inputs' ~/gritdb1/log/grit_admin_main.log | tail -1. It returns shm_limit=<new value> MB.
  • For connect_timeout_ms, check the value read in the first item above. Also check that log/postgresql.log has no new invalid line (shown below) after the restart.
• When GritDB does not start, the log (9.1) has the following lines. For a missing key, Config validation failed. Missing knobs in .config files: is followed by - <key>, or <key> is missing in <file> appears. For a value that is not an integer, <key>='<value>' is not an integer appears. For an out-of-range value, <key>=<value> out of range [<min>, <max>] appears.
• If connect_timeout_ms is wrong, GritDB still starts, and log/postgresql.log gets GritSurfer: grit_burst.session.connect_timeout_ms='<value>' invalid (range [1, 3600000]), using default 300000 ms.
• If the warning grit_surfer.channel.shm_limit_mb=<value> is below the <required> MB the derived channel plan needs appears in log/grit_admin_main.log at start, GritDB runs with fewer session channels. Transfer of large results can slow down. In that case, raise the value to at least the value in Set grit_surfer.channel.shm_limit_mb >= <required> at the end of the line, and restart.
• For the log level and size keys, see 9.2.

If a problem occurs

• If the managed block has a problem or a start fails, see the GDB-INSTALL and GDB-BOOT entries in QuickStart, chapter 7, and TroubleShooting.

Learn more

• For the full table of settings, see A.2.
• PostgreSQL 17 documentation: Setting Parameters, pg_settings, ALTER SYSTEM, Resource Consumption, and Write Ahead Log