PG14中安装uuid-ossp

Oracle或MySQL可以通过函数sys_guid()或uuid()来获得uuid，如：bdf7eb80-776f-11eb-b4e3-0800277427f4。默认postgresql并没有该功能，但是可以通过扩展uuid-ossp来实现。如果只需要随机生成的（版本4）UUIDs，可考虑使用pgcrypto组件中的 gen_random_uuid()函数来替代。

检查是否已安装扩展uuid-ossp

select * from pg_extension;
或
\dx

--输出结果如下：
postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# select * from pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 13876 | plpgsql |       10 |           11 | f              | 1.0        |           | 
(1 row)

可以看到postgresql目前没有安装uuid-ossp扩展。

检查是否有可用来安装的扩展uuid-ossp

--查看当前可用的扩展
postgres=# select * from pg_available_extensions;
        name        | default_version | installed_version |                                comment                                 
--------------------+-----------------+-------------------+------------------------------------------------------------------------
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
 plperl             | 1.0             |                   | PL/Perl procedural language
 plperlu            | 1.0             |                   | PL/PerlU untrusted procedural language
 plpython2u         | 1.0             |                   | PL/Python2U untrusted procedural language
 plpythonu          | 1.0             |                   | PL/PythonU untrusted procedural language
 adminpack          | 2.1             |                   | administrative functions for PostgreSQL
 amcheck            | 1.3             |                   | functions for verifying relation integrity
 bloom              | 1.0             |                   | bloom access method - signature file based index
 btree_gin          | 1.3             |                   | support for indexing common datatypes in GIN
 btree_gist         | 1.6             |                   | support for indexing common datatypes in GiST
 citext             | 1.6             |                   | data type for case-insensitive character strings
 cube               | 1.5             |                   | data type for multidimensional cubes
 dblink             | 1.2             |                   | connect to other PostgreSQL databases from within a database
 dict_int           | 1.0             |                   | text search dictionary template for integers
 dict_xsyn          | 1.0             |                   | text search dictionary template for extended synonym processing
 earthdistance      | 1.1             |                   | calculate great-circle distances on the surface of the Earth
 file_fdw           | 1.0             |                   | foreign-data wrapper for flat file access
 fuzzystrmatch      | 1.1             |                   | determine similarities and distance between strings
 hstore             | 1.8             |                   | data type for storing sets of (key, value) pairs
 intagg             | 1.1             |                   | integer aggregator and enumerator (obsolete)
 intarray           | 1.5             |                   | functions, operators, and index support for 1-D arrays of integers
 isn                | 1.2             |                   | data types for international product numbering standards
 lo                 | 1.1             |                   | Large Object maintenance
 ltree              | 1.2             |                   | data type for hierarchical tree-like structures
 old_snapshot       | 1.0             |                   | utilities in support of old_snapshot_threshold
 pageinspect        | 1.9             |                   | inspect the contents of database pages at a low level
 pg_buffercache     | 1.3             |                   | examine the shared buffer cache
 pg_freespacemap    | 1.2             |                   | examine the free space map (FSM)
 seg                | 1.4             |                   | data type for representing line segments or floating-point intervals
 pg_prewarm         | 1.2             |                   | prewarm relation data
 pg_stat_statements | 1.9             |                   | track planning and execution statistics of all SQL statements executed
 pg_surgery         | 1.0             |                   | extension to perform surgery on a damaged relation
 pg_trgm            | 1.6             |                   | text similarity measurement and index searching based on trigrams
 pgcrypto           | 1.3             |                   | cryptographic functions
 pgrowlocks         | 1.2             |                   | show row-level locking information
 pgstattuple        | 1.5             |                   | show tuple-level statistics
 pg_visibility      | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
 postgres_fdw       | 1.1             |                   | foreign-data wrapper for remote PostgreSQL servers
 autoinc            | 1.0             |                   | functions for autoincrementing fields
 insert_username    | 1.0             |                   | functions for tracking who changed a table
 moddatetime        | 1.0             |                   | functions for tracking last modification time
 refint             | 1.0             |                   | functions for implementing referential integrity (obsolete)
 tablefunc          | 1.0             |                   | functions that manipulate whole tables, including crosstab
 tcn                | 1.0             |                   | Triggered change notifications
 tsm_system_rows    | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 tsm_system_time    | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit
 unaccent           | 1.1             |                   | text search dictionary that removes accents
 sslinfo            | 1.2             |                   | information about SSL certificates
 xml2               | 1.1             |                   | XPath querying and XSLT
 bool_plperlu       | 1.0             |                   | transform between bool and plperlu
 bool_plperl        | 1.0             |                   | transform between bool and plperl
 hstore_plperl      | 1.0             |                   | transform between hstore and plperl
 hstore_plperlu     | 1.0             |                   | transform between hstore and plperlu
 jsonb_plperlu      | 1.0             |                   | transform between jsonb and plperlu
 jsonb_plperl       | 1.0             |                   | transform between jsonb and plperl
 hstore_plpythonu   | 1.0             |                   | transform between hstore and plpythonu
 hstore_plpython2u  | 1.0             |                   | transform between hstore and plpython2u
 hstore_plpython3u  | 1.0             |                   | transform between hstore and plpython3u
 jsonb_plpythonu    | 1.0             |                   | transform between jsonb and plpythonu
 jsonb_plpython2u   | 1.0             |                   | transform between jsonb and plpython2u
 jsonb_plpython3u   | 1.0             |                   | transform between jsonb and plpython3u
 ltree_plpythonu    | 1.0             |                   | transform between ltree and plpythonu
 ltree_plpython2u   | 1.0             |                   | transform between ltree and plpython2u
 ltree_plpython3u   | 1.0             |                   | transform between ltree and plpython3u
(64 rows)

可以看到postgres目前并没有可用的uuid-ossp扩展。
此时，直接创建uuid-ossp会报错，如：

postgres=# create extension "uuid-ossp";
ERROR:  could not open extension control file "/data/pgdb/pgsql/share/extension/uuid-ossp.control": No such file or directory
postgres=# 

注意：
要用双引号将uuid-ossp引起来，因为有个中划线“-”。

PG源码文件下编译安装扩展

安装uuid依赖包

不联网场景下， uuid-devel需下载上传安装，iso中没有
uuid-devel-1.6.2-26.el7.x86_64.rpm 下载地址:http://rpmfind.net/linux/centos/7.9.2009/os/x86_64/Packages/uuid-devel-1.6.2-26.el7.x86_64.rpm

su - root
yum -y install e2fsprogs-devel uuid uuid-devel libuuid-devel
rpm -ivh uuid-devel-1.6.2-26.el7.x86_64.rpm
cd /data/pgdb/pgsql
./configure --prefix=/data/pgdb/pgsql --with-uuid=ossp   #prefix 安装目录

该操作只是在已安装完PG后，把uuid-ossp编译安装进了PG，不影响现有库。

执行编译配置

#进入扩展目录
cd /data/pgdb/pgsql/contrib/uuid-ossp
#编译安装
make && make install

创建扩展

查看可用扩展

postgres=# select * from pg_available_extensions;
...
uuid-ossp          | 1.1             |                   | generate universally unique identifiers (UUIDs)

可以看到已经有扩展uuid-ossp了。下面可以创建了。

创建扩展

postgres=# create extension "uuid-ossp";
CREATE EXTENSION

使用扩展

安装扩展成功以后，就可以使用函数uuid_generate_v4()来生产uuid了。

postgres=# select uuid_generate_v4();
           uuid_generate_v4           
--------------------------------------
 c05ad6c7-ea34-4d36-8d09-4097cd7bae65
(1 row)