Discovering postgres binary directory location

On 11/11/20 2:22 PM, Raul Kaubi wrote:

Hi

CentOS 7
Postgres 9 to 12

I am looking ways to universally discover postgresql binary directory
for monitoring purpose.
For example postgres 12, it is: */usr/pgsql-12*

pg_config --bindir
/usr/local/pgsql12/bin

So what would be the best way to discover this in specific host.

Regards
Raul

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Raul, hi Adrian,

On 11. Nov, 2020, at 23:26, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 11/11/20 2:22 PM, Raul Kaubi wrote:

Hi
CentOS 7
Postgres 9 to 12
I am looking ways to universally discover postgresql binary directory for monitoring purpose.
For example postgres 12, it is: */usr/pgsql-12*

pg_config --bindir
/usr/local/pgsql12/bin

or by query:

postgres=# select setting from pg_config where name = 'BINDIR';
setting
-------------------------
/data/postgres/12.4/bin
(1 row)

Cheers,
Paul

On any given server there could be zero, one, or many PGHOME/bin
locations. The OP wants to identify all of them. The default location
used by package-based installers is different from the default location of
software built from source, and when building from source you can specify a
non-default location. My server has PG 9.6, 12.1, and 13 from RPM and also
12.1 from source.

The solution might be as easy as finding all installations of a core
PostgreSQL executable like pg_ctl. I would not search for psql since you
will find one or more links in system folders and so your monitoring tool
would need some logic to filter out the links. The word postgres is both
an executable and a user directory name. Hence I am using pg_ctl in my
example. This example was written on CentOS 7.7.

# find / -name pg_ctl
/usr/pgsql-13/bin/pg_ctl
/usr/local/pgsql/bin/pg_ctl
/usr/pgsql-12/bin/pg_ctl
/usr/pgsql-9.6/bin/pg_ctl
/root/Downloads/postgresql-12.1/src/bin/pg_ctl
/root/Downloads/postgresql-12.1/src/bin/pg_ctl/pg_ctl
You have mail in /var/spool/mail/root

Notice above my server has three RPM installs and one source code install.
Also notice the results contain two false positives (in the Downloads
directory) and also a message about mail. You'll have to figure out how to
separate the good and bad results.

To strip off the file name and only return the directory, you can used the
-printf option as shown below or if not supported on your system use a host
command like sed or awk. This does not remove false positives or system
messages.

find / -name 'pg_ctl' -printf "%h\n"
/usr/pgsql-13/bin
/usr/local/pgsql/bin
/usr/pgsql-12/bin
/usr/pgsql-9.6/bin
/root/Downloads/postgresql-12.1/src/bin
/root/Downloads/postgresql-12.1/src/bin/pg_ctl
You have mail in /var/spool/mail/root

Careful when stripping out the false positives. PostgreSQL installed from
source can be pretty much anywhere including a Downloads directory, /tmp,
and so on. In my case, the Downloads directory has a src sub-directory,
which tells me it is just a staging area for source code.

-Mark

On Thu, Nov 12, 2020 at 1:22 AM Paul Förster <paul.foerster@gmail.com>
wrote:

Hi Mark,

On 12. Nov, 2020, at 16:19, Mark Johnson <remi9898@gmail.com> wrote:

# find / -name pg_ctl
/usr/pgsql-13/bin/pg_ctl
/usr/local/pgsql/bin/pg_ctl
/usr/pgsql-12/bin/pg_ctl
/usr/pgsql-9.6/bin/pg_ctl
/root/Downloads/postgresql-12.1/src/bin/pg_ctl
/root/Downloads/postgresql-12.1/src/bin/pg_ctl/pg_ctl
You have mail in /var/spool/mail/root

how about searching for pg_ctl only inside a bin directory:

$ find / -type f -name "pg_ctl" -exec grep "/bin/" {} \; 2>/dev/null
Binary file /data/postgres/12.4/bin/pg_ctl matches
Binary file /data/postgres/13.0/bin/pg_ctl matches

That should also solve your source tree and root mail problems.

Cheers,
Paul

Hi Mark,

On 12. Nov, 2020, at 16:37, Paul Förster <paul.foerster@gmail.com> wrote:

how about searching for pg_ctl only inside a bin directory:

$ find / -type f -name "pg_ctl" -exec grep "/bin/" {} \; 2>/dev/null
Binary file /data/postgres/12.4/bin/pg_ctl matches
Binary file /data/postgres/13.0/bin/pg_ctl matches

That should also solve your source tree and root mail problems.

btw., you can also do it without calling grep:

$ find / -type f -executable -regex "*/bin/pg_ctl" 2>/dev/null

At least on openSUSE. But I guess, it should be pretty much the same on CentOS.

Cheers,
Paul