Postgres useful features

Previously I wrote some posts about Postgres such as basic commands, trigger, replications, etc. In this post I will go one more level more and start playing with config files, logs and other stuff.
The first thing that I would like to demonstrate is postgres log file. Basically live postgres log file is a very useful monitor the process of starting postgres to see whether any problems occur or not. For instance, due to one misconfiguration in my case, I was not able to start postgres and I have spent quite sometimes to troubleshoot and find a way to monitor the starting process. Finally I have found the following command which helped me a lot to identify the problem.

$ pg_ctl -D /var/lib/pgsql/data start

For getting list of stored procedure run this commands,

SELECT p.proname FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE   n.nspname = 'public';

Getting location of hba config file,

SHOW hba_file;

Getting number of maximum connection,

SHOW max_connections;

Knowing trigger is enable or not

SELECT pg_trigger.* FROM pg_trigger,pg_class,pg_tables WHERE tgrelid=relfilenode AND relname=tablename AND tgname = 'MY_TRIGGER_NAME’;

If tgenabled value is 0 means trigger is enabled, if it is D means trigger is disabled.
Enabling trigger for table XYZ and trigger ABC


Disabling trigger for table XYZ and trigger ABC


Now let’s move to an exciting thing. Assume we want to generate a calendar (AKA time measure) which has bunch of features such as week number, month start, month end and so on for particular year(s) and store it in a table for any purposes. To do so postgresql has a plenty of features but writing such a query or function is just cumbersome and time consuming. As bounce of this post, I had already found the query and you can refer to it from the link below,
The outcome is simply amazing.

Leave a Reply