查询某个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 我自己的扩展包;
