Thread: Discovering postgres binary directory location
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
So what would be the best way to discover this in specific host.
Regards
Raul
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
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 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
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