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.