背景

pg_idkit是rust写的一个PostgreSQL UUID生成方法函数大集合插件.

后期将集成到宇宙最强PostgreSQL学习镜像中.

《2023-PostgreSQL/DuckDB/MySQL/PolarDB-X Docker镜像学习环境 ARM64版, 已集成热门插件和工具》

《2023-PostgreSQL/DuckDB/MySQL/PolarDB-X Docker镜像学习环境 AMD64版, 已集成热门插件和工具》

安装pg_idkit

克隆项目

docker exec -ti pg bash    
    
    
cd /tmp    
git clone --depth 1 https://github.com/VADOSWARE/pg_idkit.git

配置cargo源, 参考: https://mirrors.ustc.edu.cn/help/crates.io-index.html

# export CARGO_HOME=/root      
      
# mkdir -vp ${CARGO_HOME:-$HOME/.cargo}      
      
# vi ${CARGO_HOME:-$HOME/.cargo}/config      
    
[source.crates-io]      
replace-with = 'ustc'      
      
[source.ustc]      
registry = "sparse+https://mirrors.ustc.edu.cn/crates.io-index/"

安装pg_idkit插件

cd /tmp/pg_idkit    
    
cargo install cargo-get cargo-pgrx just    
# 如果对pgrx有版本要求请参考修改, 例如:
  # grep pgrx Cargo.toml 
  # cargo install --locked --version 0.10.2 cargo-pgrx
    
cargo pgrx init    # create PGRX_HOME 后, 立即ctrl^c 退出    
cargo pgrx init --pg14=`which pg_config`    # 不用管报警    
    
PGRX_IGNORE_RUST_VERSIONS=y cargo pgrx install --pg-config `which pg_config`

  Installing extension    
     Copying control file to /usr/share/postgresql/14/extension/pg_idkit.control    
     Copying shared library to /usr/lib/postgresql/14/lib/pg_idkit.so    
 Discovering SQL entities    
  Discovered 35 SQL entities: 0 schemas (0 unique), 35 functions, 0 types, 0 enums, 0 sqls, 0 ords, 0 hashes, 0 aggregates, 0 triggers    
     Writing SQL entities to /usr/share/postgresql/14/extension/pg_idkit--0.2.1.sql    
    Finished installing pg_idkit

使用pg_idkit:

postgres=# create extension pg_idkit ;    
CREATE EXTENSION    
    
postgres=# \dx    
                                                  List of installed extensions    
   Name   | Version |   Schema   |                                         Description                                              
----------+---------+------------+----------------------------------------------------------------------------------------------    
 pg_idkit | 0.2.1   | public     | multi-tool for generating new/niche universally unique identifiers (ex. UUIDv6, ULID, KSUID)    
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language    
(2 rows)    
    
postgres=# \df    
                                          List of functions    
 Schema |                Name                 |     Result data type     | Argument data types | Type     
--------+-------------------------------------+--------------------------+---------------------+------    
 public | idkit_cuid2_generate                | text                     |                     | func    
 public | idkit_cuid2_generate_text           | text                     |                     | func    
 public | idkit_cuid_extract_timestamptz      | timestamp with time zone | val text            | func    
 public | idkit_cuid_generate                 | text                     |                     | func    
 public | idkit_cuid_generate_text            | text                     |                     | func    
 public | idkit_ksuid_extract_timestamptz     | timestamp with time zone | val text            | func    
 public | idkit_ksuid_generate                | text                     |                     | func    
 public | idkit_ksuid_generate_text           | text                     |                     | func    
 public | idkit_ksuidms_extract_timestamptz   | timestamp with time zone | val text            | func    
 public | idkit_ksuidms_generate              | text                     |                     | func    
 public | idkit_ksuidms_generate_text         | text                     |                     | func    
 public | idkit_nanoid_generate               | text                     |                     | func    
 public | idkit_nanoid_generate_text          | text                     |                     | func    
 public | idkit_pushid_generate               | text                     |                     | func    
 public | idkit_pushid_generate_text          | text                     |                     | func    
 public | idkit_timeflake_extract_timestamptz | timestamp with time zone | val text            | func    
 public | idkit_timeflake_generate            | text                     |                     | func    
 public | idkit_timeflake_generate_text       | text                     |                     | func    
 public | idkit_timeflake_generate_uuid       | uuid                     |                     | func    
 public | idkit_ulid_extract_timestamptz      | timestamp with time zone | val text            | func    
 public | idkit_ulid_from_uuid                | text                     | uuid uuid           | func    
 public | idkit_ulid_from_uuid_text           | text                     | uuid text           | func    
 public | idkit_ulid_generate                 | text                     |                     | func    
 public | idkit_ulid_generate_text            | text                     |                     | func    
 public | idkit_uuidv6_extract_timestamptz    | timestamp with time zone | val text            | func    
 public | idkit_uuidv6_generate               | text                     |                     | func    
 public | idkit_uuidv6_generate_text          | text                     |                     | func    
 public | idkit_uuidv6_generate_uuid          | uuid                     |                     | func    
 public | idkit_uuidv7_extract_timestamptz    | timestamp with time zone | val text            | func    
 public | idkit_uuidv7_generate               | text                     |                     | func    
 public | idkit_uuidv7_generate_text          | text                     |                     | func    
 public | idkit_uuidv7_generate_uuid          | uuid                     |                     | func    
 public | idkit_xid_extract_timestamptz       | timestamp with time zone | val text            | func    
 public | idkit_xid_generate                  | text                     |                     | func    
 public | idkit_xid_generate_text             | text                     |                     | func    
(35 rows)    
    
    
    
postgres=# select idkit_ksuidms_generate();    
   idkit_ksuidms_generate        
-----------------------------    
 2ZyFFpsNplQfZLzuFpXvi0vSEhH    
(1 row)    
    
postgres=# select idkit_ksuidms_generate();    
   idkit_ksuidms_generate        
-----------------------------    
 2ZyFG60s2KHkrAnwHgDG7UFIbmj    
(1 row)

🐘 🪪 `pg_idkit`

postgres=# CREATE EXTENSION pg_idkit;    
CREATE EXTENSION    
    
postgres=# SELECT idkit_uuidv7_generate();    
        idkit_uuidv7_generate    
--------------------------------------    
 018c106f-9304-79bb-b5be-4483b92b036c

Description

pg_idkit is a Postgres extension for generating many popular types of identifiers:

This Postgres extension is made possible thanks to [pgrx][pgrx].

Quickstart

You can try out pg_idkit incredibly quickly by using docker, and a previously released package of pg_idkit:

docker run \    
    --rm \    
    -e POSTGRES_PASSWORD=replace_this \    
    -p 5432 \    
    --name pg_idkit \    
    ghcr.io/vadosware/pg_idkit:0.2.1-pg16.1-alpine3.18-amd64

[!WARNING]
Currently only amd64 (x86_64) images are present/supported (See pg_idkit packages).

Work to support more platforms is described in issue #30

Once the postgres server is running, open another shell and connect to the dockerized Postgres instance running on port 5432:

➜ docker exec -it pg_idkit psql -U postgres    
psql (16.1)    
Type "help" for help.    
    
postgres=# CREATE EXTENSION pg_idkit;    
CREATE EXTENSION    
    
postgres=# SELECT idkit_uuidv7_generate();    
        idkit_uuidv7_generate    
--------------------------------------    
 018c106f-9304-79bb-b5be-4483b92b036c    
(1 row)

Installing `pg_idkit`

📃 From Source

Source install

To build pg_idkit from source, clone this repository and run the following:

cargo install cargo-get cargo-pgrx just    
just package

After running these commands you should see the following directory structure in target/release/pg_idkit-pg16:

target/release/pg_idkit-pg16    
├── home    
│   └── <user>    
│       └── .pgrx    
│           └── 16.1    
│               └── pgrx-install    
│                   ├── lib    
│                   │   └── postgresql    
│                   │       └── pg_idkit.so    
│                   └── share    
│                       └── postgresql    
│                           └── extension    
│                               ├── pg_idkit--0.2.1.sql    
│                               └── pg_idkit.control    
└── usr    
    ├── lib    
    │   └── postgresql    
    │       └── pg_idkit.so    
    └── share    
        └── postgresql    
            └── extension    
                └── pg_idkit.control    
    
24 directories, 8 files

As the installation of the extension into a specific version of postgres uses your local installation of pgrx-managed Postgres by default (normally at $HOME/.pgrx), cargo pgrx package reproduces the directory structure in target/release. You can safely ignore the shorter usr/lib/user/share tree.

In the example above, the files you need for a Postgres extension are:

target/release/home/<user>/.pgrx/16.1/pgrx-install/lib/postgresql/pg_idkit.so
target/release/home/<user>/.pgrx/16.1/pgrx-install/share/postgresql/extension/pg_idkit--0.2.1.sql
target/release/home/<user>/.pgrx/16.1/pgrx-install/share/postgresql/extension/pg_idkit.control

Install these files in the relevant folders for your Postgres installation – note that exactly where these files should go can can differ across linux distributions and containerized environments.

💽 From Binary

Binary install

If running a custom version of locally/globally manually installed Postgres, you may download (and verify the checksum of) a shared library version from the releases, and add it as one of your shared_preload_libraries in postgresql.conf.

Assuming you have downloaded the pg_idkit-vX.X.X.so file to /etc/postgresql/extensions, you might change the file like this:

postgresql.conf

shared_preload_libraries = '/etc/postgresql/extensions/pg_idkit-vX.X.X.so'

Once your postgres instance is started up, you should be able to CREATE EXTENSION:

postgres=# CREATE EXTENSION pg_idkit;    
CREATE EXTENSION    
postgres=# SELECT idkit_uuidv7_generate();    
        idkit_uuidv7_generate    
--------------------------------------    
 018c106f-9304-79bb-b5be-4483b92b036c

🐳 Dockerfile

Dockerfile

To use pg_idkit easily from a containerized environment, you can use the pg_idkit image, built from postgres:

docker run \    
    --rm \    
    -e POSTGRES_PASSWORD=replace_this \    
    -p 5432 \    
    --name pg_idkit \    
    ghcr.io/vadosware/pg_idkit:0.2.1-pg16.1-alpine3.18-amd64

From another terminal, you can exec into the pg_idkit container and enable pg_idkit:

➜ docker exec -it pg_idkit psql -U postgres    
psql (16.1)    
Type "help" for help.    
    
postgres=# CREATE EXTENSION pg_idkit;    
CREATE EXTENSION    
postgres=# SELECT idkit_uuidv7_generate();    
        idkit_uuidv7_generate    
--------------------------------------    
 018c106f-9304-79bb-b5be-4483b92b036c    
(1 row)

[!WARNING]
Currently only amd64 (x86_64) images are present/supported (See pg_idkit packages).

Work to support more platforms is described in issue #30

📦 Debian (RPM)

RPM install

RPMs are produced upon every official release of pg_idkit.

Grab a released version of the RPM (or build one yourself by running just build-rpm after setting up local development).

For example, with an RPM named pg_idkit-0.2.1-pg16.x86_64.rpm, you should be able to run:

dnf install pg_idkit-0.2.1-pg16.x86_64.rpm

Prior Art

There are some other projects in the Postgres ecosystem that implement alternative UUID generation mechanisms.

Here are some you may or may not have heard of:

Setting up for local development

Interested in contributing on the project? Set up your local development environment w/ docs/local-development.md.

Contributing

Contributions are welcome!

If you find a bug or an impovement that should be included in pg_idkit, create an issue.

If you’d like to contribute code, get started by:

Reading the local development guide
Creating an issue (if necessary) to explain the new feature/bugfix/etc
Forking this repository
Creating a feature/bugfix/etc branch (we expect [conventional commits][conventional-commits], i.e. feat: new awesome feature)
Opening a Pull Request to this repository

PostgreSQL pg_idkit: 各种 UUID 生成方法函数大集合

Sun Dec 24, 2023

1500 Words|Read in about 7 Min

Tags:

背景

安装pg_idkit

更多参考pg_idkit README.md

🐘 🪪 `pg_idkit`

Description

Quickstart

Installing `pg_idkit`

Source install

Binary install

Dockerfile

RPM install

Prior Art

Setting up for local development

Contributing

See Also

Sun Dec 24, 2023

1500 Words|Read in about 7 Min

Tags:

Sun Dec 24, 2023

1500 Words|Read in about 7 Min

Tags:

背景

安装pg_idkit

更多参考pg_idkit README.md

🐘 🪪 `pg_idkit`

Description

Quickstart

Installing pg_idkit

Source install

Binary install

Dockerfile

RPM install

Prior Art

Setting up for local development

Contributing

See Also

Sun Dec 24, 2023

1500 Words|Read in about 7 Min

Tags:

Installing `pg_idkit`