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

[Alex Clay]

Hi Jim,

The postgresql logs are usually owned by the postgres user and not visible to other users. This is a security measure since your logs can contain sensitive information. I normally use the command line to work with these files which allows me switch to the postgres user and have full access. In your installation you could try something like this to watch the log files:

sudo su - postgres -c "tail -f  /Library/PostgreSQL/9.5/data/logs/$(ls -t /Library/PostgreSQL/9.5/data/logs | head -1)" 

Use ctrl-c to exit the tail. I'm assuming your log files are at /Library/PostgreSQL/9.5/data/log. If not, you may need to adjust this command accordingly.

If you want to use a GUI tool like Console.app or a text editor to view the files, you have a couple options.

First, you could change ownership on the logging folder to allow less-restrictive access. It sounds like you're using the EnterpriseDB PostgreSQL installation which keeps the logs inside the data cluster. You can't give the data cluster less-restrictive permissions, so you would need to first relocate the logs folder somewhere else. If you want to go this route you'd setup the destination folder, adjust the log_directory value in the postgres config files, and restart the server. You could run commands like this to make this change:

mkdir -p /Library/Logs/postgresql/9.5
sudo chown postgres /Library/Logs/postgresql/9.5
sudo echo "log_directory = '/Library/Logs/postgresql/9.5'" >> /Library/PostgreSQL/9.5/data/postgresql.conf
sudo su - postgres -c "pg_ctl restart -m f"

The logs would be located in /Library/Logs/postgresql/9.5 and should be readable by your user account. I made a couple assumptions about your installation in those commands. If they don't work let me know what errors you get an I'll adjust them accordingly.

The second option would be to copy the log files to another location on disk, change the permissions, then open them with a text editor. This is easier but doesn't give you live logging. Something like these commands would put a copy of the files on your desktop in a "postgres_logs" folder:

mkdir ~/Desktop/postgres_logs
sudo cp -R /Library/PostgreSQL/9.5/data/logs* ~/Desktop/postgres_logs
sudo chown -R $(whoami) ~/Desktop/postgres_logs

Again, I'm assuming your current logs are at /Library/PostgreSQL/9.5/data/logs, so if this command doesn't work you may need to adjust it accordingly.

The logs will help you find errors, but not locks or slow queries To review active locks, I recommend connecting to the database using psql and running:

SELECT * FROM pg_stat_activity;

This system view will give you logs of information about current activity in the database server. If you want a GUI solution, try pgAdmin. It had a nice interface to this view that also joins in pg_locks to give you information on exactly what is locked.

For query times you might try the pg_stat_statements module:

https://www.postgresql.org/docs/9.5/static/pgstatstatements.html <https://www.postgresql.org/docs/9.5/static/pgstatstatements.html>

This extra module tracks statements run in your database, including execution times. There are a number of blog posts on the web for how to use this module. I recommend these couple posts to get you started:

http://www.cybertec.at/pg_stat_statements-the-way-i-like-it/ <http://www.cybertec.at/pg_stat_statements-the-way-i-like-it/>
http://www.craigkerstiens.com/2013/01/10/more-on-postgres-performance/ <http://www.craigkerstiens.com/2013/01/10/more-on-postgres-performance/>

I hope this helps get you started!

Alex


> On Mar 12, 2017, at 11:05, Jim Pistrang <jim at jpcr.com> 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
> 
> -- 
> Jim Pistrang
> JP Computer Resources
> 413-256-4569
> <http://www.jpcr.com>
> 
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com