Contrib Modules:
In PostgreSQL, “contrib” stands for “contributed modules”. These are additional functionalities and features developed by the community that are not part of the core PostgreSQL distribution, but can be easily added to enhance the capabilities of the database.
Contributed modules are distributed separately and can be installed on top of PostgreSQL to provide extra functionalities. They cover a wide range of functionalities like data types, functions, operators, and procedural languages to full-fledged extensions that add new features to the database.
To use a contributed module, you typically need to follow these steps:
1 – Make sure the “contrib” package is installed: In some distributions, PostgreSQL may come with separate packages for “contrib extensions”.
|
1 2 3 |
[root@postgres1 ~]$ su postgres [postgres@postgres1 ~]$ /usr/local/pgsql/bin/psql psql (11.0) |
Type “help” for help.
|
1 2 |
postgres-# create extension pg_stat_statements; ERROR: could not open extension control file "/usr/local/pgsql/share/extension/pg_stat_statements.control": No such file or directory |
Which Means contrib modules are haven’t installed.
2 – Install contrib module:
• Move to the contrib directory
|
1 |
[root@postgres1 ~]$ cd /u01/postgresql-11/contrib |
• Run below command
|
1 |
[root@postgres1 contrib]$ make install |
• Change the parameters in postgresql.conf file
|
1 2 |
[root@postgres1 contrib]$ cd /u01/data1 [root@postgres1 data1]$ vi postgresql.conf |
In the postgresql.conf file, find the shared_preload_libraries parameter and add “pg_stat_statements” to it.
Shared_preload_libraries = ‘pg_stat_statements’
Now save and exit postgresql.conf file editor and restart the cluster by running below command:
|
1 |
[postgres@postgres1 ~]$ /usr/local/pgsql/bin/pg_ctl restart |
3 – Enable the module: After Installing the “contrib” package, you need to enable the specific module you want to use. This is typically done by executing the ‘CREATE EXTENSION command. For example, to enable the “pg_stat_statements” extension, you would run:
|
1 2 |
Postgres=# create extension pg_stat_stements; CREATE EXTENSION |
4 – Ensure the extension is installed:
To check if it’s installed, you can run the following SQL command
|
1 |
Postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statement'; |
Here are commands to check the list of installed extensions:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
Postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+---------------------------------------------------------- pg_stat_statements | 1.6 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows) postgres=# SELECT * FROM pg_extension; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition --------------------+----------+--------------+----------------+------------+-----------+------------- plpgsql | 10 | 11 | f | 1.0 | | pg_stat_statements | 10 | 2200 | t | 1.6 | | (2 rows) postgres=# select * from pg_database; datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dat tablespace | datacl --------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---- -----------+------------------------------------- postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 13236 | 561 | 1 | 1663 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 13236 | 561 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres} template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 13236 | 561 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres} dbname | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 13236 | 561 | 1 | 1663 | organisation | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 13236 | 561 | 1 | 1663 | (5 rows) To list all the records in pg_stat_statements run the below command: postgres=# \x Expanded display is on. postgres=# select * from pg_stat_statements; userid | 10 dbid | 13237 queryid | 6325763357626316301 query | select * from pg_database calls | 2 total_time | 0.105227 min_time | 0.042843 max_time | 0.062384 mean_time | 0.0526135 stddev_time | 0.0097705 rows | 10 shared_blks_hit | 4 shared_blks_read | 0 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 |
Droping an extension:
To drop an extension in PostgreSQL, you can use the DROP EXTENSION command. Here’s how to drop pg_stat_statements extension:
|
1 2 |
Postgres=# DROP EXTENSION IF EXISTS pg_stat_statements; DROP EXTENSION |
Author : Prudhvi Teja |
LinkedIn : http://linkedin.com/in/prudhvi-teja-nagabhyru-715052224
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTExperts is always active on social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Note: Please test scripts in Non Prod before trying in Production.
Loading...
