PG数据库管理_扩展包的安装以及如何查询。

查询某个database已经安装的扩展
postgres=# select name,default_version,installed_version,left(comment,30) as comment
from  pg_available_extensions
where installed_version IS NOT NULL
order by name;
        name        | default_version | installed_version |            comment             
--------------------+-----------------+-------------------+--------------------------------
 pg_stat_statements | 1.6             | 1.6               | track execution statistics of 
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
(2 rows)

查询某个database所有的的扩展

select name,default_version,installed_version,left(comment,30) as comment
from  pg_available_extensions
order by name;

postgres=# select name,default_version,installed_version,left(comment,30) as comment
from  pg_available_extensions
where installed_version IS NOT NULL
order by name;
        name        | default_version | installed_version |            comment             
--------------------+-----------------+-------------------+--------------------------------
 pg_stat_statements | 1.6             | 1.6               | track execution statistics of 
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
(2 rows)

        name        | default_version | installed_version |            comment             
--------------------+-----------------+-------------------+--------------------------------
 adminpack          | 2.0             |                   | administrative functions for P
 amcheck            | 1.1             |                   | functions for verifying relati
 autoinc            | 1.0             |                   | functions for autoincrementing
 bloom              | 1.0             |                   | bloom access method - signatur
 btree_gin          | 1.3             |                   | support for indexing common da
 btree_gist         | 1.5             |                   | support for indexing common da
 citext             | 1.5             |                   | data type for case-insensitive
 cube               | 1.4             |                   | data type for multidimensional
 dblink             | 1.2             |                   | connect to other PostgreSQL da
 dict_int           | 1.0             |                   | text search dictionary templat
 dict_xsyn          | 1.0             |                   | text search dictionary templat
 earthdistance      | 1.1             |                   | calculate great-circle distanc
 file_fdw           | 1.0             |                   | foreign-data wrapper for flat 
 fuzzystrmatch      | 1.1             |                   | determine similarities and dis
 hstore             | 1.5             |                   | data type for storing sets of 
 insert_username    | 1.0             |                   | functions for tracking who cha
 intagg             | 1.1             |                   | integer aggregator and enumera
 intarray           | 1.2             |                   | functions, operators, and inde
 isn                | 1.2             |                   | data types for international p
 lo                 | 1.1             |                   | Large Object maintenance
 ltree              | 1.1             |                   | data type for hierarchical tre
 moddatetime        | 1.0             |                   | functions for tracking last mo
 pageinspect        | 1.7             |                   | inspect the contents of databa
 pg_buffercache     | 1.3             |                   | examine the shared buffer cach
 pg_freespacemap    | 1.2             |                   | examine the free space map (FS
 pg_prewarm         | 1.2             |                   | prewarm relation data
 pg_stat_statements | 1.6             | 1.6               | track execution statistics of 
 pg_trgm            | 1.4             |                   | text similarity measurement an
 pg_visibility      | 1.2             |                   | examine the visibility map (VM
 pgcrypto           | 1.3             |                   | cryptographic functions
 pgrowlocks         | 1.2             |                   | show row-level locking informa
 pgstattuple        | 1.5             |                   | show tuple-level statistics
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
 postgres_fdw       | 1.0             |                   | foreign-data wrapper for remot
 refint             | 1.0             |                   | functions for implementing ref
 seg                | 1.3             |                   | data type for representing lin
 tablefunc          | 1.0             |                   | functions that manipulate whol
 tcn                | 1.0             |                   | Triggered change notifications
 timetravel         | 1.0             |                   | functions for implementing tim
 tsm_system_rows    | 1.0             |                   | TABLESAMPLE method which accep
 tsm_system_time    | 1.0             |                   | TABLESAMPLE method which accep
 unaccent           | 1.1             |                   | text search dictionary that re
(42 rows)

查询已经安装的扩展包的更多内容

postgres=# \dx+ pg_stat_statements
Objects in extension "pg_stat_statements"
          Object description          
--------------------------------------
 function pg_stat_statements(boolean)
 function pg_stat_statements_reset()
 view pg_stat_statements
(3 rows)

select pg_describe_object(D.classid,D.objid,0) as discription
from pg_catalog.pg_depend as d inner join pg_catalog.pg_extension as E
on d.refobjid =E.oid
where 
d.refclassid=‘pg_catalog.pg_extension‘::pg_catalog.regclass and
deptype=‘e‘ and
e.extname= ‘pg_stat_statements‘;

postgres=# \dx+ pg_stat_statements
Objects in extension "pg_stat_statements"
          Object description          
--------------------------------------
 function pg_stat_statements(boolean)
 function pg_stat_statements_reset()
 view pg_stat_statements
(3 rows)

postgres=# select pg_describe_object(D.classid,D.objid,0) as discription
postgres-# from pg_catalog.pg_depend as d inner join pg_catalog.pg_extension as E
postgres-# on d.refobjid =E.oid
postgres-# where 
postgres-# d.refclassid=‘pg_catalog.pg_extension‘::pg_catalog.regclass and
postgres-# deptype=‘e‘ and
postgres-# e.extname= ‘pg_stat_statements‘;
             discription              
--------------------------------------
 view pg_stat_statements
 function pg_stat_statements(boolean)
 function pg_stat_statements_reset()
(3 rows)

扩展包的安装

1. 扩展包一般在share/contrib
通过安装postgresql-contrib来获取。
查看服务器上的扩展包
select * from pg_available_extensions;

2. 扩展包安装

create extension pg_stat_statements;

psql -p 5432 -d mydb -c "create extension pg_stat_statements;"

建议通过专门的schema来安装扩展包

create extension pg_stat_statements schema 我自己的扩展包;

PG数据库管理_扩展包的安装以及如何查询。

上一篇:[IOI2000]邮局 题解(四边形不等式优化 DP)


下一篇:如何在JAVA代码中进行数据库加锁操作?