!Omnis: hoping for some Postgres hints on logs and logging

[Bastiaan Olij]

Hey Jim,

As others have all ready reacted, PostgreSQL is pretty picky when it
comes to rights especially on Linux and Mac OS X. As a result only the
PostgreSQL user has access to these folders. You can "sudo su postgres"
in terminal to get in there or as Mr Blaymires stated, use pgAdmin.

We however are Omnis programmers and as Omnis programmers we can make
our live even easier ;)
Provided you are connected to your database using a superuser role the
following commands can be sent to Postgres to retrieve the data into
Omnis after which you can use code to scan through and analyse your logs :)

--
show data_directory
--
Returns the path on the server to the data folder (you'll need this
later on)

--
select pg_ls_dir("pg_log")
--
Lists all the log files in the pg_log folder, interestingly this
function has the data folder as the working directory so you don't need
to append the path unlike the following command:

--
select pg_read_file("[lvDataPath]/pg_log/[lvLogFilename]", 0, 1000000)
--
Provided lvDataPath contains the path you retrieved with "show
data_directory" and lvLogFilename contains a filename you grabbed from
the list of log files returned by pg_ls_dir, this will return the
contents of that log file as text.

Now you can parse the data and do all sorts of funky things with it.
Note that these functions only work for superusers, they do not work for
normal users (that would be a big security risk) and as they are
executed from the perspective of the postgres user access to files is
limited to rights for that user, but you can do some funky things with it.

Much more info about these commands can be found here:
https://www.postgresql.org/docs/9.5/static/functions-admin.html
Scroll down to 9.26.9. Other stuff on this page is pretty cool, like how
you could write you own backup process :)

Finally note that what gets logged is very dependent on your settings as
well. Obviously logging is very conservative by default as you want to
limit logging in a production environment but have a browse through your
postgresql.conf file, you can set it up to log any command, duration of
queries, have it log queries that take longer then a certain amount of
time, etc. Worth gold in a development environment where you care less
about these log files growing rapidly

Cheers,

Bas



On 13/03/2017 2:05 AM, Jim Pistrang wrote:
> Hey Postgres wizards:
>
> I have PostgreSQL 9.5 installed on OS X 10.12.3.  I have tried to educate myself online, but I am totally mystified on how to view the postgres log files.  Ideally I would like to open log files in a text editor or spreadsheet so I can search, etc.  I particularly want to see..
>   if any unwanted locking is happening
>   any sql errors that might be encountered
>   speed of select and fetch statements
>
> I've seen references to logs residing in /Library/PostgreSQL/9.5/data but the /data folder is locked and I don't have access to it.
>
> Can anyone offer any guidance here?
>
> Thanks,
>
> Jim
>


-- 
Kindest Regards,

Bastiaan Olij
e-mail: bastiaan at basenlily.me
web: http://www.basenlily.me
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij