by 
Previously I wrote some posts about PostgreSQL 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 PostgreSQL log file. Basically live PostgreSQL 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
ALTER TABLE XYZ ENABLE TRIGGER ABC;
Disabling trigger for table XYZ and trigger ABC
ALTER TABLE XYZ DISABLE 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 this link.
