A. Reference

By the end of this chapter: You find options, settings, SQL functions, log files, directories, diagnostic queries, and terms in one place.

A.1 Commands and options

• The authoritative source for installation options is the table in 2.1 of chapter 2.

gritdb-start and gritdb-stop options:

• Other options in the gritdb-start usage (--asan, --valgrind, --crash-policy) are for development and testing, so do not use them. If you pass --crash-policy, the start fails.
• When gritdb-start receives an unknown option, it prints Unknown option: <option> and does not start.
• gritdb-stop ignores unknown options and runs a normal stop. So for a quick stop, check the spelling (--quick, -q).
• Neither command has a time limit on how long it waits to finish (4.1, 4.2).

Exit codes:

• An abnormal termination after the start completes does not show in the gritdb-start exit code. So recheck after 20 seconds, as in 4.1.

Environment variables:

• There are no environment variables for the customer to set. GritDB commands do not read GRITDB_HOME, PGDATA, or PGPORT. They find the installation directory from the location of the executable.
• Start GritDB from a shell without environment variables that start with PG and without LD_LIBRARY_PATH (4.1).
• The TMPDIR value in the starting shell becomes the temporary file location of the GritDB engine (1.4, 7.6).

• Use bin/ commands not in this table (createdb, pg_controldata, pg_waldump, and so on) as the PostgreSQL 17 documentation describes. Do not run operations that rewrite registered tables (6.7).

A.2 Settings

• Meaning of the settings (PostgreSQL): shared_preload_libraries, wal_level, search_path, port, shared_buffers, wal_buffers, max_wal_size, min_wal_size, checkpoint_timeout, bgwriter_lru_maxpages, effective_cache_size, work_mem, restart_after_crash
• The managed block is rewritten at every installation, upgrade, and reinitialization. So set values that you want to keep with ALTER SYSTEM (5.3).
• To check when a setting takes effect, run SELECT name, setting, context, pending_restart FROM pg_settings WHERE name = '<setting>'; (5.4).
• Other PostgreSQL settings that this guide tells you to change: number of workers (6.4.1 step 5); number of tables copied at the same time during the initial copy, max_sync_workers_per_subscription (6.8); number of connections, max_connections (5.4, 10.2); time zone (5.3); listen_addresses (11.4); audit logging (11.9)
• The tables in 5.2 and 5.6 of chapter 5 are the authoritative source. The authoritative source for extension settings is 6.6.

A.3 SQL functions

• With the installation defaults, every account can run these functions. Narrow the privileges in 11.5. Only the service account uses the register and unregister functions, so do not grant them to roles. After you complete 11.5, regular accounts cannot run them even if you grant them.
• Status functions return only the objects of the connected database. Only get_gritsurfer_unify_checkpoints() is common to all databases.
• The arguments of the history function can differ by installation package version. So check them with \df get_gritsurfer_query_history before you use it (3.5, 6.5).
• The tables in 6.3 and 6.5 of chapter 6 are the authoritative source.

Frequently used columns:

Query status (status):

• Functions in the gritdb schema that are not in this section are for GritDB internal use. Do not call them. Revoke their execute privileges in 11.5.

A.4 Log files

• The table in 9.1 of chapter 9 is the authoritative source.

A.5 Directories

Installation directory (~/gritdb1):

• Installation and upgrade change the installed files. Upgrade leaves configuration and state as they are (3.5).
• A development package also has run/gritdb/, tools/, .bashrc, and build/ (chapter 0).

Inside datafiles/:

• Relocation symlinks (2.4): datafiles → --datafiles real path, datafiles/pgdata → --pgdata real path
• To check, run readlink -f ~/gritdb1/datafiles. It returns the real path set at installation.

Inside run/:

A.6 Diagnostic queries

Copy them into ~/gritdb1/bin/psql -p 5432 -d <db> opened as the service account. Queries not marked “in each database” give the same result in any database.

WAL retained by the recovery replication slot:

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots WHERE slot_name = 'gritsurfer_slot';

• View and function: pg_replication_slots, pg_current_wal_lsn()
• It is normal if active is f, wal_status is reserved, and retained_wal does not keep growing.

Count and size of cold files by status (in each database):

SELECT schema_name, table_name, status, count(*) AS files,
       pg_size_pretty(sum(file_size_bytes)) AS size
FROM get_gritsurfer_coldfiles()
GROUP BY schema_name, table_name, status
ORDER BY schema_name, table_name, status;

• It is normal if STAGED does not keep growing and INACTIVE disappears soon (7.8).

unify checkpoint age:

SELECT max(ckpt_lsn) AS ckpt_lsn, now() - max(created_at) AS age
FROM get_gritsurfer_unify_checkpoints();

• It is normal if age stays short (7.2). You can also see the same thing in the file with stat -c '%y' ~/gritdb1/datafiles/checkpoint/grit_unify.cp. The value is the last time the unify checkpoint advanced.

Long transactions:

SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start LIMIT 10;

• It is normal if there are no long-open transactions and no idle in transaction. If there are any, end them in that application.

Remaining prepared transactions (PREPARE TRANSACTION):

SELECT gid, prepared, owner, database FROM pg_prepared_xacts;

• It is normal if pg_prepared_xacts has 0 rows.

Registered table list (in each database):

SELECT schema_name, table_name, register_ts FROM get_gritsurfer_tables() ORDER BY 1, 2;

• TOAST tables registered along with the tables also appear.

Recent analytic queries (in each database; check the arguments for each version, 6.5):

SELECT query_start_time, status, pg_end_time - query_start_time AS total,
       left(query_text, 60) AS query
FROM get_gritsurfer_query_history(100, now() - interval '1 hour')
ORDER BY query_start_time DESC;

Evidence of a restart:

SELECT pg_postmaster_start_time();

• It is normal if the value is the same as at the previous check. If it changed, a restart happened (7.9). The function is described in pg_postmaster_start_time().

Other queries:

A.7 Oracle and Tibero mapping

• This table continues the table in QuickStart 1.3.

A.8 Glossary