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

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 the link below,
https://wiki.postgresql.org/wiki/Date_and_Time_dimensions
The outcome is simply amazing.

Leave a Reply