PostgreSQL useful features

Default featured post

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,


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 this link.