How to run PostgreSQL in a Single-User Mode

PostgreSQL provides a special execution mode called single-user mode, where the database runs as a standalone backend process without the postmaster (server). This mode is extremely useful for debugging, recovery, and exploring internal behavior such as parsing, planning, and execution.

Single-user mode runs PostgreSQL as a single backend process:

• No client-server architecture
• No concurrent connections
• Direct interaction with the database engine
• Useful for debugging and internal exploration

You can check available options using:

postgres --help

Relevant options for single-user mode:

Options for single-user mode:
  --single           selects single-user mode (must be first argument)
  DBNAME             database name (defaults to user name)
  -d 0-5             override debugging level
  -E                 echo statement before execution
  -j                 do not use newline as interactive query delimiter
  -r FILENAME        send stdout and stderr to given file

Check Running Clusters

First, check active PostgreSQL clusters:

pg_lsclusters

Example output:

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Stop the Cluster

Single-user mode requires the server to be stopped:

sudo systemctl stop postgresql@18-main.service

Check again the status of the cluster to confirm that the current status of the postgres cluster is down.

Result :

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 down   postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Create a Minimal Configuration File

Create a minimal configuration file:

sudo nano /tmp/pg_minimal.conf

Add:

data_directory = '/var/lib/postgresql/18/main'
hba_file = '/etc/postgresql/18/main/pg_hba.conf'
ident_file = '/etc/postgresql/18/main/pg_ident.conf'
ssl = off

You can check the location of these files inside postgresql

postgres=# show data_directory;
       data_directory        
-----------------------------
 /var/lib/postgresql/18/main
(1 row)
postgres=# show hba_file ;
              hba_file               
-------------------------------------
 /etc/postgresql/18/main/pg_hba.conf
(1 row)
postgres=# show ident_file ;
              ident_file               
---------------------------------------
 /etc/postgresql/18/main/pg_ident.conf
(1 row)
postgres=# show ssl;
 ssl 
-----
 on
(1 row)

Start PostgreSQL in Single-User Mode

Run PostgreSQL manually:

sudo -u postgres /home/cybrosys/pg18/bin/postgres \
  --single \
  -d 0 \
  -D /var/lib/postgresql/18/main \
  -c config_file=/tmp/pg_minimal.conf \
  postgres

output:

NOTICE:  database system was shut down...
PostgreSQL stand-alone backend 17.6
backend>

Running a Query

select * from pg_database;

Result :

1: oid	(typeid = 26, len = 4, typmod = -1, byval = t)
	 2: datname	(typeid = 19, len = 64, typmod = -1, byval = f)
	 3: datdba	(typeid = 26, len = 4, typmod = -1, byval = t)
	 4: encoding	(typeid = 23, len = 4, typmod = -1, byval = t)
	 5: datlocprovider	(typeid = 18, len = 1, typmod = -1, byval = t)
	 6: datistemplate	(typeid = 16, len = 1, typmod = -1, byval = t)
	 7: datallowconn	(typeid = 16, len = 1, typmod = -1, byval = t)
	 8: dathasloginevt	(typeid = 16, len = 1, typmod = -1, byval = t)
	 9: datconnlimit	(typeid = 23, len = 4, typmod = -1, byval = t)
	10: datfrozenxid	(typeid = 28, len = 4, typmod = -1, byval = t)
	11: datminmxid	(typeid = 28, len = 4, typmod = -1, byval = t)
	12: dattablespace	(typeid = 26, len = 4, typmod = -1, byval = t)
	13: datcollate	(typeid = 25, len = -1, typmod = -1, byval = f)
	14: datctype	(typeid = 25, len = -1, typmod = -1, byval = f)
	15: datlocale	(typeid = 25, len = -1, typmod = -1, byval = f)
	16: daticurules	(typeid = 25, len = -1, typmod = -1, byval = f)
	17: datcollversion	(typeid = 25, len = -1, typmod = -1, byval = f)
	18: datacl	(typeid = 1034, len = -1, typmod = -1, byval = f)
	----
	 1: oid = "5"	(typeid = 26, len = 4, typmod = -1, byval = t)
	 2: datname = "postgres"	(typeid = 19, len = 64, typmod = -1, byval = f)
	 3: datdba = "10"	(typeid = 26, len = 4, typmod = -1, byval = t)
	 4: encoding = "6"	(typeid = 23, len = 4, typmod = -1, byval = t)
	 5: datlocprovider = "c"	(typeid = 18, len = 1, typmod = -1, byval = t)
	 6: datistemplate = "f"	(typeid = 16, len = 1, typmod = -1, byval = t)
	 7: datallowconn = "t"	(typeid = 16, len = 1, typmod = -1, byval = t)
	 8: dathasloginevt = "f"	(typeid = 16, len = 1, typmod = -1, byval = t)
	 9: datconnlimit = "-1"	(typeid = 23, len = 4, typmod = -1, byval = t)
	10: datfrozenxid = "730"	(typeid = 28, len = 4, typmod = -1, byval = t)
	11: datminmxid = "1"	(typeid = 28, len = 4, typmod = -1, byval = t)
	12: dattablespace = "1663"	(typeid = 26, len = 4, typmod = -1, byval = t)
	13: datcollate = "en_IN"	(typeid = 25, len = -1, typmod = -1, byval = f)
	14: datctype = "en_IN"	(typeid = 25, len = -1, typmod = -1, byval = f)
	17: datcollversion = "2.35"	(typeid = 25, len = -1, typmod = -1, byval = f)

This output is different from normal SQL clients because it shows internal tuple structure.

Exploring Debug Levels (-d)

Debug Level 1

-d 1

Shows startup-level debug logs:

Example :

cybrosys@cybrosys:~$ sudo -u postgres /home/cybrosys/pg18/bin/postgres   --single   -d 1   -D /var/lib/postgresql/18/main   -c config_file=/tmp/pg_minimal.conf   postgres
2026-03-25 14:05:11.435 GMT [2627353] DEBUG:  mmap(150994944) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory
2026-03-25 14:05:11.447 GMT [2627353] NOTICE:  database system was shut down at 2026-03-25 14:05:05 GMT
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  checkpoint record is at 0/152B830
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  redo record is at 0/152B830; shutdown true
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  next transaction ID: 751; next OID: 16388
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  oldest unfrozen transaction ID: 730, in database 1
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  oldest MultiXactId: 1, in database 1
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  commit timestamp Xid oldest/newest: 0/0
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  transaction ID wrap limit is 2147484377, limited by database with OID 1
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  starting up replication slots
2026-03-25 14:05:11.447 GMT [2627353] DEBUG:  xmin required by slots: data 0, catalog 0
2026-03-25 14:05:11.448 GMT [2627353] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
2026-03-25 14:05:11.448 GMT [2627353] DEBUG:  MultiXact member stop limit is now 4294914944 based on MultiXact 1
PostgreSQL stand-alone backend 17.6
backend> 

This helps understand WAL, transaction IDs, and system state.

Debug Level 2

-d 2

Adds more internal logs:

cybrosys@cybrosys:~$ sudo -u postgres /home/cybrosys/pg18/bin/postgres   --single   -d 2   -D /var/lib/postgresql/18/main   -c config_file=/tmp/pg_minimal.conf   postgres
2026-03-25 14:05:29.258 GMT [2629700] DEBUG:  mmap(150994944) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  dynamic shared memory system will support 679 segments
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  created dynamic shared memory control segment 643938204 (27176 bytes)
2026-03-25 14:05:29.270 GMT [2629700] NOTICE:  database system was shut down at 2026-03-25 14:05:23 GMT
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  checkpoint record is at 0/152B8A8
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  redo record is at 0/152B8A8; shutdown true
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  next transaction ID: 751; next OID: 16388
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  oldest unfrozen transaction ID: 730, in database 1
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  oldest MultiXactId: 1, in database 1
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  commit timestamp Xid oldest/newest: 0/0
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  transaction ID wrap limit is 2147484377, limited by database with OID 1
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  starting up replication slots
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  xmin required by slots: data 0, catalog 0
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  starting up replication origin progress state
2026-03-25 14:05:29.270 GMT [2629700] DEBUG:  reading stats file "pg_stat/pgstat.stat"
2026-03-25 14:05:29.271 GMT [2629700] DEBUG:  removing permanent stats file "pg_stat/pgstat.stat"
2026-03-25 14:05:29.271 GMT [2629700] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
2026-03-25 14:05:29.271 GMT [2629700] DEBUG:  MultiXact member stop limit is now 4294914944 based on MultiXact 1
PostgreSQL stand-alone backend 17.6
backend> 

Now you start seeing memory management and statistics system behavior.

Higher Debug Levels

• -d 3: Shows parse tree
• -d 4: Shows parse tree + rewritten tree + execution plan
• -d 5: Even deeper internal execution details

These levels are extremely useful when exploring PostgreSQL source code internals.

Using the -E Flag (Echo Queries)

-E

Example:

sudo -u postgres /home/cybrosys/pg18/bin/postgres \
  --single \
  -E \
  -D /var/lib/postgresql/18/main \
  -c config_file=/tmp/pg_minimal.conf \
  postgres

Run:

select version();

Output:

statement: select version();
	 1: version	(typeid = 25, len = -1, typmod = -1, byval = f)
	----
	 1: version = "PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit"	(typeid = 25, len = -1, typmod = -1, byval = f)
	----

This prints the query before execution, useful for debugging query flow.

Using the -j Flag (Query Delimiter Control)

-j

Example:

sudo -u postgres /home/cybrosys/pg18/bin/postgres \
  --single \
  -j \
  -D /var/lib/postgresql/18/main \
  -c config_file=/tmp/pg_minimal.conf \
  postgres

Now queries behave differently:

SELECT
  datname,
  datdba
FROM
  pg_database;

Execution behavior:

• Requires double Enter or
• Use Ctrl + D to execute

This disables the newline as the delimiter.

Redirecting Logs with -r

You can store logs in a file:

sudo -u postgres /home/cybrosys/pg17/bin/postgres \
  --single \
  -d 2 \
  -E \
  -r /tmp/debug.log \
  -D /var/lib/postgresql/17/main \
  -c config_file=/tmp/pg_minimal.conf \
  postgres

Then check logs:

cat /tmp/debug.log

Example output:

DEBUG: checkpoint record is at 0/152BE48
DEBUG: next transaction ID: 751
DEBUG: removing permanent stats file
DEBUG: writing stats file "pg_stat/pgstat.stat"
NOTICE: database system is shut down

This is extremely useful for offline debugging and analysis.

Key Observations

1. Single-user mode bypasses the postmaster and runs a direct backend.
2. Output format exposes internal tuple representation.
3. Debug levels (-d) reveal:
   • Storage engine behavior
   • WAL and checkpoint details
   • Query parsing and planning
4. Flags like -E, -j, and -r help control execution and logging.
5. Ideal for:
   • Debugging PostgreSQL internals
   • Learning execution flow
   • Testing source code modifications

Running PostgreSQL in single-user mode gives a unique, low-level view of how the database engine operates. Unlike normal client interactions, this mode exposes internal structures, execution flow, and debugging details that are otherwise hidden. By experimenting with different debug levels and flags, you can observe how queries are parsed, rewritten, planned, and executed inside PostgreSQL.

For developers working with PostgreSQL source code or trying to optimize database behavior, single-user mode is not just a tool; it is a window into the core of the database engine.