This document provides a comprehensive reference for all pgsqlite configuration options.
pgsqlite can be configured through:
- Command line arguments (highest priority)
- Environment variables (with
PGSQLITE_prefix) - Default values (lowest priority)
| Option | CLI Flag | Environment Variable | Default | Description |
|---|---|---|---|---|
| Port | --port, -p |
PGSQLITE_PORT |
5432 |
PostgreSQL port to listen on |
| Database | --database, -d |
PGSQLITE_DATABASE |
sqlite.db |
Path to SQLite database file |
| Log Level | --log-level |
PGSQLITE_LOG_LEVEL |
info |
Logging level (error, warn, info, debug, trace) |
| In-Memory | --in-memory |
PGSQLITE_IN_MEMORY |
false |
Use in-memory SQLite database |
| Socket Directory | --socket-dir |
PGSQLITE_SOCKET_DIR |
/tmp |
Directory for Unix domain socket |
| No TCP | --no-tcp |
PGSQLITE_NO_TCP |
false |
Disable TCP listener, use only Unix socket |
| Hide Internal Tables | --hide-internal-tables |
PGSQLITE_HIDE_INTERNAL_TABLES |
false |
Hide pgsqlite's internal __pgsqlite_* tables and their indexes from client sqlite_master / sqlite_schema reads. See notes below. |
--hide-internal-tables rewrites client references to sqlite_master /
sqlite_schema into a filtered relation, so pgsqlite's own __pgsqlite_*
bookkeeping objects and the indexes they own are not listed.
Where the rewrite applies. Read contexts only:
- plain
SELECT(including CTEs, subqueries and joins) - the
SELECTsource of anINSERT ... SELECT - the body of
CREATE VIEW(includingCREATE MATERIALIZED VIEW,CREATE TEMP VIEW, andCREATE VIEW IF NOT EXISTS) - the
AS SELECTsource ofCREATE TABLE ... AS SELECT
Where it does not apply. UPDATE and DELETE are never rewritten, including
their subqueries — filtering there would silently change which rows a write
touches, and SQLite rejects writes to sqlite_master on its own anyway.
References qualified with another database (otherdb.sqlite_master) or with
temp. are left alone, because those name a different relation than the one the
filter substitutes for. CREATE TABLE t (col ...) AS SELECT ... — a CTAS with an
explicit column list — is also skipped. In all of these cases the client simply
sees the unfiltered catalog; the flag never rejects a query it cannot handle.
The filter predicate is persisted into your view definitions. This is a
knowingly accepted trade-off, and it is the one surprising consequence. SQLite
stores the literal CREATE VIEW text, so a view created while this flag is on
has the filter baked into its stored DDL:
CREATE VIEW v AS SELECT name FROM (SELECT * FROM sqlite_master
WHERE SUBSTR(name, 1, 11) <> '__pgsqlite_'
AND (tbl_name IS NULL OR SUBSTR(tbl_name, 1, 11) <> '__pgsqlite_')) AS sqlite_masterThat text is what you get back from sqlite_master.sql and from
information_schema.views.view_definition — so a definition intended to hide the
internal prefix ends up displaying it. Two further consequences follow from the
rewrite happening at creation time: a view created while the flag is on keeps
filtering after the flag is turned off, and a view created while the flag was off
keeps listing internal objects after it is turned on. CREATE TABLE ... AS SELECT is unaffected — SQLite stores the expanded column list, not the select —
so it is a clean one-shot filtered copy.
Scope. Hiding is listing-only, not access control. The internal tables remain
queryable when named explicitly (SELECT * FROM __pgsqlite_schema), and the flag
does not affect the materialized pg_* / information_schema_* relations or
PRAGMA table_list.
| Option | CLI Flag | Environment Variable | Default | Description |
|---|---|---|---|---|
| SSL Enabled | --ssl |
PGSQLITE_SSL |
false |
Enable SSL/TLS support (TCP only) |
| SSL Certificate | --ssl-cert |
PGSQLITE_SSL_CERT |
Auto-generated | Path to SSL certificate file |
| SSL Key | --ssl-key |
PGSQLITE_SSL_KEY |
Auto-generated | Path to SSL private key file |
| SSL CA | --ssl-ca |
PGSQLITE_SSL_CA |
None | Path to CA certificate file (optional) |
| SSL Ephemeral | --ssl-ephemeral |
PGSQLITE_SSL_EPHEMERAL |
false |
Generate ephemeral certificates on startup |
| Option | CLI Flag | Environment Variable | Default | Description |
|---|---|---|---|---|
| Row Description Cache Size | --row-desc-cache-size |
PGSQLITE_ROW_DESC_CACHE_SIZE |
1000 |
Number of RowDescription cache entries |
| Row Description Cache TTL | --row-desc-cache-ttl |
PGSQLITE_ROW_DESC_CACHE_TTL_MINUTES |
10 |
RowDescription cache TTL in minutes |
| Parameter Cache Size | --param-cache-size |
PGSQLITE_PARAM_CACHE_SIZE |
500 |
Number of parameter cache entries |
| Parameter Cache TTL | --param-cache-ttl |
PGSQLITE_PARAM_CACHE_TTL_MINUTES |
30 |
Parameter cache TTL in minutes |
| Query Cache Size | --query-cache-size |
PGSQLITE_QUERY_CACHE_SIZE |
1000 |
Number of query plan cache entries |
| Query Cache TTL | --query-cache-ttl |
PGSQLITE_QUERY_CACHE_TTL |
600 |
Query cache TTL in seconds |
| Execution Cache TTL | --execution-cache-ttl |
PGSQLITE_EXECUTION_CACHE_TTL |
300 |
Execution metadata TTL in seconds |
| Result Cache Size | --result-cache-size |
PGSQLITE_RESULT_CACHE_SIZE |
100 |
Number of result set cache entries |
| Result Cache TTL | --result-cache-ttl |
PGSQLITE_RESULT_CACHE_TTL |
60 |
Result cache TTL in seconds |
| Statement Pool Size | --statement-pool-size |
PGSQLITE_STATEMENT_POOL_SIZE |
100 |
Prepared statement pool size |
| Schema Cache TTL | --schema-cache-ttl |
PGSQLITE_SCHEMA_CACHE_TTL |
300 |
Schema cache TTL in seconds |
| Cache Metrics Interval | --cache-metrics-interval |
PGSQLITE_CACHE_METRICS_INTERVAL |
300 |
Cache metrics logging interval in seconds |
| Option | CLI Flag | Environment Variable | Default | Description |
|---|---|---|---|---|
| Buffer Monitoring | --buffer-monitoring |
PGSQLITE_BUFFER_MONITORING |
false |
Enable buffer pool monitoring |
| Buffer Pool Size | --buffer-pool-size |
PGSQLITE_BUFFER_POOL_SIZE |
50 |
Buffer pool size |
| Buffer Initial Capacity | --buffer-initial-capacity |
PGSQLITE_BUFFER_INITIAL_CAPACITY |
4096 |
Initial buffer capacity in bytes |
| Buffer Max Capacity | --buffer-max-capacity |
PGSQLITE_BUFFER_MAX_CAPACITY |
65536 |
Maximum buffer capacity in bytes |
| Option | CLI Flag | Environment Variable | Default | Description |
|---|---|---|---|---|
| Auto Cleanup | --auto-cleanup |
PGSQLITE_AUTO_CLEANUP |
false |
Enable automatic memory pressure response |
| Memory Monitoring | --memory-monitoring |
PGSQLITE_MEMORY_MONITORING |
false |
Enable detailed memory monitoring |
| Memory Threshold | --memory-threshold |
PGSQLITE_MEMORY_THRESHOLD |
67108864 |
Memory threshold for cleanup (bytes) |
| High Memory Threshold | --high-memory-threshold |
PGSQLITE_HIGH_MEMORY_THRESHOLD |
134217728 |
High memory threshold (bytes) |
| Memory Check Interval | --memory-check-interval |
PGSQLITE_MEMORY_CHECK_INTERVAL |
10 |
Memory check interval in seconds |
| Option | CLI Flag | Environment Variable | Default | Description |
|---|---|---|---|---|
| Enable Memory Mapping | --enable-mmap |
PGSQLITE_ENABLE_MMAP |
false |
Enable memory mapping for large values |
| Min MMap Size | --mmap-min-size |
PGSQLITE_MMAP_MIN_SIZE |
65536 |
Minimum size for memory mapping (bytes) |
| Max MMap Memory | --mmap-max-memory |
PGSQLITE_MMAP_MAX_MEMORY |
1048576 |
Max in-memory size before temp files (bytes) |
| Temp Directory | --temp-dir |
PGSQLITE_TEMP_DIR |
System temp | Directory for temporary files |
| Option | CLI Flag | Environment Variable | Default | Description |
|---|---|---|---|---|
| Journal Mode | --pragma-journal-mode |
PGSQLITE_JOURNAL_MODE |
WAL |
SQLite journal mode (DELETE, TRUNCATE, PERSIST, MEMORY, WAL, OFF) |
| Synchronous Mode | --pragma-synchronous |
PGSQLITE_SYNCHRONOUS |
NORMAL |
SQLite synchronous mode (OFF, NORMAL, FULL, EXTRA) |
| Cache Size | --pragma-cache-size |
PGSQLITE_CACHE_SIZE |
-64000 |
SQLite page cache size (negative = KB, positive = pages) |
| MMap Size | --pragma-mmap-size |
PGSQLITE_MMAP_SIZE |
268435456 |
SQLite memory-mapped I/O size in bytes |
| Option | CLI Flag | Environment Variable | Default | Description |
|---|---|---|---|---|
| Migrate | --migrate |
N/A | false |
Run pending migrations and exit |
# Basic usage with custom port
pgsqlite --port 5433 --database myapp.db
# In-memory database with SSL
pgsqlite --in-memory --ssl
# Aggressive caching for read-heavy workloads
pgsqlite \
--query-cache-size 5000 \
--result-cache-size 500 \
--statement-pool-size 200
# Production-like settings with monitoring
pgsqlite \
--database /data/prod.db \
--pragma-journal-mode WAL \
--pragma-synchronous FULL \
--buffer-monitoring \
--memory-monitoring \
--auto-cleanup
# Unix socket only (no TCP)
pgsqlite --socket-dir /var/run/pgsqlite --no-tcp# Basic configuration
export PGSQLITE_PORT=5433
export PGSQLITE_DATABASE=/data/myapp.db
export PGSQLITE_LOG_LEVEL=debug
# Enable SSL with custom certificates
export PGSQLITE_SSL=true
export PGSQLITE_SSL_CERT=/etc/pgsqlite/server.crt
export PGSQLITE_SSL_KEY=/etc/pgsqlite/server.key
# Performance tuning
export PGSQLITE_QUERY_CACHE_SIZE=5000
export PGSQLITE_RESULT_CACHE_SIZE=500
export PGSQLITE_STATEMENT_POOL_SIZE=200
# Run pgsqlite
pgsqliteCreate a .env file in your working directory:
# Server settings
PGSQLITE_PORT=5432
PGSQLITE_DATABASE=./data/production.db
PGSQLITE_LOG_LEVEL=info
# Performance
PGSQLITE_JOURNAL_MODE=WAL
PGSQLITE_SYNCHRONOUS=NORMAL
PGSQLITE_QUERY_CACHE_SIZE=2000
PGSQLITE_RESULT_CACHE_SIZE=200
# SSL
PGSQLITE_SSL=true
PGSQLITE_SSL_CERT=./certs/server.crt
PGSQLITE_SSL_KEY=./certs/server.key
# Monitoring
PGSQLITE_BUFFER_MONITORING=1
PGSQLITE_MEMORY_MONITORING=1pgsqlite --in-memory --log-level debugpgsqlite \
--in-memory \
--pragma-synchronous OFF \
--query-cache-size 100 \
--result-cache-size 50pgsqlite \
--database /data/prod.db \
--pragma-journal-mode WAL \
--pragma-synchronous NORMAL \
--query-cache-size 5000 \
--result-cache-size 500 \
--statement-pool-size 200 \
--auto-cleanup \
--sslWhen monitoring is enabled, pgsqlite logs detailed metrics:
- Cache hit rates: Monitor effectiveness of query and result caching
- Memory usage: Track memory consumption and cleanup events
- Buffer pool stats: Understand buffer utilization
- Connection metrics: Track active connections and query patterns
Enable monitoring with:
pgsqlite \
--memory-monitoring \
--buffer-monitoring \
--cache-metrics-interval 60