Over the years I saw some people find themselves in position where they have to start dealing with PostgreSQL with minimal, or none, prior exposure. This leads to problems with seemingly easy tasks – how to change config? How to find stuff in logs?

So I decided to write some blogposts to be able to point such people to pre-made tutorials.

And I start today, with information on how to find PostgreSQL config files.

First of all – when talking about config files people usually mean postgresql.conf, pg_hba.conf, or (less often) pg_ident.conf.

All these files traditionally lived in PostgreSQL data directory – that is the directory in which PostgreSQL kept its data.

This was then modified by people providing packages with PostgreSQL for various OS'es/distributions, and nowadays config files are usually kept in /etc, while data directory is somewhere in /var, /home, or /who/knows/where.

But – it can still be configured to any location you'd like.

So, let's start. You were given PostgreSQL server to do stuff with, and you need to change config. Where is it?

Assuming you can connect as superuser, you can run these queries:

=$ SHOW config_file;
           config_file
----------------------------------
 /home/pgdba/DATA/postgresql.conf
(1 ROW)
 
=$ SHOW hba_file;
           hba_file
------------------------------
 /home/pgdba/DATA/pg_hba.conf
(1 ROW)
 
=$ SHOW ident_file;
           ident_file
--------------------------------
 /home/pgdba/DATA/pg_ident.conf
(1 ROW)

And the paths will be revealed.

But what if you can't?

You will need shell access to the server that the Pg is running on.

Depending on version of PostgreSQL, and the way it was packaged main Pg process can be named “postgres" or “postmaster". So we need to find the process. To do so, on both Linux and MacOs I can run:

=$ ps -xao pid,command | grep -E 'post(gres|master)'
  17887 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
  17889 postgres: 13/main: checkpointer 
  17890 postgres: 13/main: background writer 
  17891 postgres: 13/main: walwriter 
  17892 postgres: 13/main: autovacuum launcher 
  17893 postgres: 13/main: stats collector 
  17894 postgres: 13/main: logical replication launcher 
  30164 /home/pgdba/work/bin/postgres
  30165 postgres: logger 
  30167 postgres: checkpointer 
  30168 postgres: background writer 
  30169 postgres: walwriter 
  30170 postgres: autovacuum launcher 
  30171 postgres: archiver last was 0000000100000000000000AB
  30172 postgres: stats collector 
  30173 postgres: logical replication launcher 
  44369 postgres: depesz depesz [local] idle

In here we see that there are two separate postgres instances. If you're on linux you can see it a bit better thanks to ps's f options:

=$ ps -fxao pid,command | grep -E 'post(gres|master)'
  17887 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
  17889  \_ postgres: 13/main: checkpointer 
  17890  \_ postgres: 13/main: background writer 
  17891  \_ postgres: 13/main: walwriter 
  17892  \_ postgres: 13/main: autovacuum launcher 
  17893  \_ postgres: 13/main: stats collector 
  17894  \_ postgres: 13/main: logical replication launcher 
  30164 /home/pgdba/work/bin/postgres
  30165  \_ postgres: logger 
  30167  \_ postgres: checkpointer 
  30168  \_ postgres: background writer 
  30169  \_ postgres: walwriter 
  30170  \_ postgres: autovacuum launcher 
  30171  \_ postgres: archiver last was 0000000100000000000000AB
  30172  \_ postgres: stats collector 
  30173  \_ postgres: logical replication launcher 
  44369  \_ postgres: depesz depesz [local] idle

Not sure how to get such tree on Mac.

Anyway – processes “postgres: …" are child processes, so the two main Pg processes are:

• pid: 17887, command: /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
• pid: 30164, command: /home/pgdba/work/bin/postgres

In case of pid 17887 everything is visible: -D option tells us where is data directory, and -c config_file tells us where postgresql.conf is.

Inside this conf file there are are locations for hba and ident conf files:

=$ sudo grep -E '(hba|ident)\.conf' /etc/postgresql/13/main/postgresql.conf
hba_file = '/etc/postgresql/13/main/pg_hba.conf'        # host-based authentication file
ident_file = '/etc/postgresql/13/main/pg_ident.conf'    # ident configuration file

What is interesting, it is possible that you will see process like this:

pgdba      52628  1.5  0.0 205732 23588 ?        Ss   13:18   0:00 /home/pgdba/WORK/bin/postgres -D /home/pgdba/confs

where data directory is actually somewhere else.

The thing is that when Pg starts, if it didn't get config_file path it will open config files in data directory. Which in turn can redirect real data directory somewhere else:

=$ grep data_directory /home/pgdba/confs/postgresql.conf
data_directory = '/home/pgdba/data'             # USE DATA IN another directory

This leaves the case of postgres process with no args, like this:

USER         PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
pgdba      68112  0.0  0.0 205732 23644 ?        Ss   14:00   0:00 /home/pgdba/WORK/bin/postgres

With such process, we can still find the config. It was most likely started with environment variable PGDATA set to where data directory is.

On Linux I can:

=$ sudo cat /proc/68112/environ | tr '\0' '\n' | grep ^PG | sort
PGDATA=/home/pgdba/DATA
PG_GRANDPARENT_PID=68066
PGSYSCONFDIR=/home/pgdba/WORK/etc/postgresql

PGSYSCONFDIR is irrelevant, and this directory (at least for me) doesn't even exist.

But what is important is PGDATA. This is where data_directory resides, and in there I shall find conf files.

I'm not sure how to get full environment of running process on Mac, but we can, at least, extract current working directory, using:

=$ sudo lsof -p 68112 -a -d cwd
COMMAND    PID  USER   FD   TYPE DEVICE SIZE/OFF     NODE NAME
postgres 68112 pgdba  cwd    DIR  253,0     4096 63834384 /home/pgdba/DATA

And this, too, shows where data_directory is located, with conf files below.

This leaves one big gaping hole in this post – how to do the same on Windows? Sorry, but I have absolutely no idea. Perhaps someone reading it, having access, and knowledge, of Pg on Windows, can chime in?

There are also *BSD systems and other Unix systems, but I would assume that methods used on Linux or Mac will work in some way on most of them.

Hope you'll find it useful.