IvorySQL

https://docs.ivorysql.org
Admin
IvorySQL is an open-source, Oracle-compatible PostgreSQL database project by Highgo Software,...
Tokens:173,130
Snippets:2,107
Trust Score:7.9
Update:3 months ago
Show doc for...
Tokens:
Raw
### Start New IvorySQL Service

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/operation_guide

Start the background service for the newly installed IvorySQL version.

```bash
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
```

--------------------------------

### Run pg_upgrade on Windows

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.22/v1.22/21

This example demonstrates how to execute the pg_upgrade utility on Windows, specifying the data and binary directories for both the old and new IvorySQL installations. Directory paths are enclosed in quotes.

```cmd
pg_upgrade.exe
        --old-datadir "C:/Program Files/IvorySQL/1.5/data"
        --new-datadir "C:/Program Files/IvorySQL/2.1/data"
        --old-bindir "C:/Program Files/IvorySQL/1.5/bin"
        --new-bindir "C:/Program Files/IvorySQL/2.1/bin"
```

--------------------------------

### Install Dependencies

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pgdog

Installs necessary dependencies for building PgDog on Ubuntu.

```bash
sudo apt update && \
    sudo apt install -y cmake clang curl pkg-config \
        libssl-dev git build-essential mold rustup \
        docker
```

--------------------------------

### Compile and Install Nginx

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v5.0/v5.0/4.7.1

Compiles and installs Nginx from source. This includes extracting the archive, configuring the build with SSL support, and then performing the make and make install steps.

```bash
## 解压缩nginx-1.20.1.tar.gz安装包

[root@cloud web]# tar -zxvf nginx-1.20.1.tar.gz

## 解压后生成nginx-1.20.1文件夹

[root@cloud web]# ls -lrt

总用量 3924

-rwxrwxr-x. 1 root root 1061461 5月 25 2021 nginx-1.20.1.tar.gz

-rwxrwxr-x. 1 root root 2943732 10月 9 16:43 dist.tar.gz

drwxrwxr-x. 9 1001 1001 186 10月 9 16:53 nginx-1.20.1

drwxrwxrwx. 4 root root 103 10月 21 13:20 dist

## 配置导向

[root@cloud web]# cd nginx-1.20.1

[root@cloud nginx-1.20.1]# ./configure --prefix=/opt/cloud/nginx --with-http_ssl_module

## 编译安装

[root@cloud nginx-1.20.1]# make

[root@cloud nginx-1.20.1]# make install
```

--------------------------------

### Install Build Dependencies

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_ai_query

Installs the necessary development libraries for building the extension.

```bash
sudo apt-get install libcurl4-openssl-dev
```

--------------------------------

### Install pg_partman from Source

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_partman

Steps to clone the pg_partman repository, switch to a specific stable version, and install it using make.

```bash
git clone https://github.com/pgpartman/pg_partman.git
cd pg_partman
git switch 5.2-STABLE

make && sudo make install
```

--------------------------------

### Install libcurl Development Files

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.17/v1.17/38

Installs the necessary development files for libcurl, a prerequisite for compiling the pgsql-http extension.

```bash
#Install dependency
sudo apt install libcurl4-openssl-dev
```

--------------------------------

### Install RPM Dependencies

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/installation_guide

Installs necessary dependencies for RPM installation using dnf.

```bash
$ sudo dnf install -y lz4 libicu libxslt python3
```

--------------------------------

### IvorySQL Server Start

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v5.3/9

The main command to start the IvorySQL database server.

```bash
postgres [OPTION]…
```

--------------------------------

### Sequence Next Value Example

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/developer_guide

Example demonstrating how to create a sequence and retrieve its next value using nextval.

```sql
create sequence sq;

select sq.nextval;
```

--------------------------------

### Compile and Install IvorySQL from Source

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.22/v1.22/6

Compile the IvorySQL source code using 'make' and then install it. It's recommended to run 'make check' or 'make all-check-world' for regression testing before installation.

```bash
make
make check
make all-check-world
sudo make install
```

--------------------------------

### Enable and Start IvorySQL Systemd Service

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.17/v1.17/6

Enable the IvorySQL systemd service to start on boot and start it immediately. This command manages the database service lifecycle.

```bash
systemctl enable --now ivorysql.service
```

--------------------------------

### Install Extension Shared Objects on New Standby

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.22/v1.22/21

This step requires installing the same extension shared object files on the new standby servers as were installed on the new primary cluster. This ensures compatibility and functionality of extensions.

```text
Install the same extension shared object files on the new standby as in the new primary cluster.
```

--------------------------------

### Build and Install pg_profile and Dependencies

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_profile

Build and install pg_profile and its required extensions from source. This includes dblink, pg_stat_statements, and pg_profile itself.

```bash
# 构建并安装 pg_profile 及其依赖扩展
make -C contrib/dblink install
make -C contrib/pg_stat_statements install
make -C contrib/pg_profile install
```

--------------------------------

### Compile and Install pgddl from Source

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pgddl

Steps to download, compile, and install the pgddl extension from its source code. Ensure the PG_CONFIG environment variable is set to the correct pg_config path for your IvorySQL installation.

```bash
cd pgddl
# Set PG_CONFIG environment variable to the pg_config path, eg: /usr/local/ivorysql/ivorysql-5/bin/pg_config
make PG_CONFIG=/path/to/pg_config
make PG_CONFIG=/path/to/pg_config install
```

--------------------------------

### Clone and Install pg_cron from Source

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_cron

Steps to clone the pg_cron repository and install it using make. Ensure pg_config is in your PATH.

```bash
# 拉取pg_cron源码
git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
# 将pg_config的路径设置到PATH环境变量里，eg：
export PATH=/usr/local/ivorysql/ivorysql-5/bin/:$PATH
make
make install
```

--------------------------------

### Compile and Install pg_ai_query

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_ai_query

Steps to clone, build, and install the pg_ai_query extension after setting up the IvorySQL environment.

```bash
git clone --recurse-submodules https://github.com/benodiwal/pg_ai_query.git
cd pg_ai_query
mkdir build && cd build
export PATH="$HOME/works/repo/ivorysql/IvorySQL/inst/bin:$PATH"
cmake .. -DCMAKE_INSTALL_PREFIX=$HOME/works/repo/ivorysql/IvorySQL/inst
make && sudo make install
```

--------------------------------

### Install Rust Toolchain

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.22/v1.22/40

Installs the Rust toolchain required for compiling pgvectorscale from source.

```bash
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh
```

--------------------------------

### Download Nginx Installation Package

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v5.0/v5.0/4.7.1

Downloads the Nginx 1.20.1 tarball. This is the first step in manually installing Nginx.

```bash
[root@cloud web]# wget https://nginx.org/download/nginx-1.20.1.tar.gz

[root@cloud web]# ls -lrt

总用量 3924

-rwxrwxr-x. 1 root root 1061461 5月 25 2021 nginx-1.20.1.tar.gz

-rwxrwxr-x. 1 root root 2943732 10月 9 16:43 dist.tar.gz

drwxrwxrwx. 4 root root 103 10月 21 13:20 dist
```

--------------------------------

### Install AGE Dependencies

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/age

Installs necessary build tools and Python libraries for compiling Apache AGE.

```bash
# Ubuntu / Debian
sudo apt install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache libssl-dev pkg-config

# Install AGE dependencies
sudo apt install python3 python3-pip python3-dev
pip3 install antlr4-runtime4
```

--------------------------------

### Compile and Install pgsql-http Extension

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.17/v1.17/38

Compiles and installs the pgsql-http extension from source code. Ensure pg_config is accessible in your PATH.

```bash
tar xvf pgsql-http-1.7.0.tar.gz
cd pgsql-http-1.7.0
# Ensure pg_config is accessible in PATH, eg: /usr/local/ivorysql/ivorysql-1/bin/pg_config
make
sudo make install
```

--------------------------------

### Array Access Examples

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.17/v1.17/7

Provides examples of accessing array elements and slices, including multi-dimensional arrays and function results.

```sql
mytable.arraycolumn[4]
```

```sql
mytable.two_d_column[17][34]
```

```sql
$1[10:42]
```

```sql
(arrayfunction(a,b))[42]
```

--------------------------------

### Field Selection Examples

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.17/v1.17/7

Shows examples of selecting fields from composite types, including cases where parentheses are required for clarity.

```sql
mytable.mycolumn
```

```sql
$1.somecolumn
```

```sql
(rowfunction(a,b)).col3
```

```sql
(compositecol).somefield
```

```sql
(mytable.compositecol).somefield
```

--------------------------------

### Sample Output of pg_show_plans_q

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_show_plans

Example output from the pg_show_plans_q view, detailing PID, level, plan, and the associated SQL query.

```text
-[ RECORD 1 ]------------------------------------------------------------------------------------
pid   | 11473
level | 0
plan  | Sort  (cost=72.08..74.58 rows=1000 width=80)
      |   Sort Key: pg_show_plans.pid, pg_show_plans.level
      |   ->  Hash Left Join  (cost=2.25..22.25 rows=1000 width=80)
      |         Hash Cond: (pg_show_plans.pid = s.pid)
      |         Join Filter: (pg_show_plans.level = 0)
      |         ->  Function Scan on pg_show_plans  (cost=0.00..10.00 rows=1000 width=48)
      |         ->  Hash  (cost=1.00..1.00 rows=100 width=44)
      |               ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.00 rows=100 width=44)
query | SELECT p.pid, p.level, p.plan, a.query FROM pg_show_plans p
      |    LEFT JOIN pg_stat_activity a
      |    ON p.pid = a.pid AND p.level = 0 ORDER BY p.pid, p.level;
-[ RECORD 2 ]------------------------------------------------------------------------------------
pid   | 11517
level | 0
plan  | Function Scan on print_item  (cost=0.25..10.25 rows=1000 width=524)
query | SELECT * FROM print_item(1,20);
-[ RECORD 3 ]------------------------------------------------------------------------------------
pid   | 11517
level | 1
plan  | Result  (cost=0.00..0.01 rows=1 width=4)
query |

```

--------------------------------

### Install New IvorySQL Binaries on Standby Servers

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.22/v1.22/21

This is a placeholder instruction indicating that new IvorySQL binaries and support files must be installed on all standby servers as part of the upgrade process for replication setups.

```text
Ensure new binaries and support files are installed on all standby servers.
```

--------------------------------

### Start PgDog Service

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pgdog

Launches the PgDog proxy with specified configuration and user files.

```bash
./target/release/pgdog --config ./pgdog.toml --users ./users.toml
```

--------------------------------

### Standard SQL Comment Example

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/developer_guide

Demonstrates the syntax for a standard single-line SQL comment, which starts with double hyphens and extends to the end of the line.

```sql
-- This is a standard SQL comment

```

--------------------------------

### Sample Output of pg_show_plans

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_show_plans

Example output from the pg_show_plans view, showing PID, level, user ID, database ID, and the query plan.

```text
  pid  | level | userid | dbid  |                                 plan
-------+-------+--------+-------+-----------------------------------------------------------------------
 11473 |     0 |     10 | 16384 | Function Scan on pg_show_plans  (cost=0.00..10.00 rows=1000 width=56)
 11504 |     0 |     10 | 16384 | Function Scan on print_item  (cost=0.25..10.25 rows=1000 width=524)
 11504 |     1 |     10 | 16384 | Result  (cost=0.00..0.01 rows=1 width=4)
(3 rows)
```

--------------------------------

### Describe a Foreign Data Wrapper Object

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v5.4/110

Uses pg_describe_object to get a textual description of a database object, specifically a foreign-data wrapper in this example.

```sql
postgres=# select oid,relname from pg_class where oid in (select distinct classid from pg_depend) order by oid;
 oid  |         relname
------+-------------------------
 1247 | pg_type
 1255 | pg_proc
 1259 | pg_class
 2328 | pg_foreign_data_wrapper
 2602 | pg_amop
 2603 | pg_amproc
 2606 | pg_constraint
 2612 | pg_language
 2616 | pg_opclass
 2617 | pg_operator
 2618 | pg_rewrite
 2620 | pg_trigger
 2753 | pg_opfamily
 3600 | pg_ts_dict
 3602 | pg_ts_config
 3764 | pg_ts_template
(17 rows)

postgres=# select * from pg_foreign_data_wrapper ;
  oid  | fdwname  | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
-------+----------+----------+------------+--------------+--------+------------
 16954 | file_fdw |       10 |      16952 |        16953 |        |
(1 row)

postgres=# select pg_describe_object(2328,16954,0);
      pg_describe_object
-------------------------------
 foreign-data wrapper file_fdw
(1 row)

postgres=#
```

--------------------------------

### Install PgDog from Source

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pgdog

Downloads, extracts, and compiles PgDog from its source code.

```bash
wget https://github.com/pgdogdev/pgdog/archive/refs/tags/v0.1.45.tar.gz
tar -zxf v0.1.45.tar.gz
cd pgdog-0.1.45

# 编译完后，会在 target/release 目录下生成可执行文件
cargo build --release
```

--------------------------------

### Verify IvorySQL Database Startup

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/installation_guide

Check running processes to confirm the IvorySQL database has started successfully. Look for 'postgres' processes associated with your installation.

```bash
ps -ef | grep postgres
```

--------------------------------

### Prepare Test Environment

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/oracle_compatibility/compat_create_index_online

Sets up base test tables, including a table for unique indexes and a partitioned table, populating them with sample data.

```sql
-- 基础测试表
CREATE TABLE tbl_ci_online (
    id      NUMBER(10)    PRIMARY KEY,
    name    VARCHAR2(100),
    dept_id NUMBER(10),
    salary  NUMBER(10,2),
    status  VARCHAR2(20)
);
INSERT INTO tbl_ci_online
    SELECT g, 'name'||g, MOD(g,20), g*100.0, CASE WHEN MOD(g,2)=0 THEN 'ACTIVE' ELSE 'INACTIVE' END
    FROM generate_series(1, 1000) g;

-- 唯一索引测试表
CREATE TABLE tbl_ci_unique (
    id    NUMBER(10)    PRIMARY KEY,
    email VARCHAR2(200) NOT NULL
);
INSERT INTO tbl_ci_unique SELECT g, 'user'||g||'@example.com' FROM generate_series(1, 200) g;

-- 分区表
CREATE TABLE tbl_ci_part (
    id     NUMBER(10),
    region VARCHAR2(20)
) PARTITION BY RANGE (id);
CREATE TABLE tbl_ci_part_p1 PARTITION OF tbl_ci_part FOR VALUES FROM (1)    TO (501);
CREATE TABLE tbl_ci_part_p2 PARTITION OF tbl_ci_part FOR VALUES FROM (501)  TO (1001);
INSERT INTO tbl_ci_part SELECT g, CASE WHEN g<=500 THEN 'east' ELSE 'west' END
    FROM generate_series(1, 1000) g;
```

--------------------------------

### IvorySQL Aggregate Example: count(*)

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v5.4/4.3

Demonstrates the use of count(*) to get the total number of input rows. Aggregate functions generally ignore null inputs, meaning rows where one or more expressions evaluate to null are discarded.

```sql
count(*)
```

--------------------------------

### Start New IvorySQL 5.0 Service and Log

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/operation_guide

Start the new IvorySQL 5.0 database service and direct its logs to a specified file.

```bash
/usr/ivory-5/bin/pg_ctl -D ./data -l logfile start
```

--------------------------------

### Clone and Build pg_show_plans

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_show_plans

Clone the pg_show_plans repository and build the extension using make. Ensure pg_config is accessible in your PATH.

```bash
git clone https://github.com/cybertec-postgresql/pg_show_plans.git
cd pg_show_plans

make PG_CONFIG=/usr/ivory-5/bin/pg_config
make PG_CONFIG=/usr/ivory-5/bin/pg_config install
```

--------------------------------

### Install DEB Dependencies

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/installation_guide

Installs necessary dependencies for DEB package installation using apt.

```bash
$ sudo apt -y install pkg-config libreadline-dev libicu-dev libldap2-dev uuid-dev tcl-dev libperl-dev python3-dev bison flex openssl libssl-dev libpam-dev libxml2-dev libxslt-dev libossp-uuid-dev libselinux-dev gettext
```

--------------------------------

### Create Partitioned Table and Global Unique Index

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.17/v1.17/41

Example demonstrating the creation of a partitioned table and then adding a global unique index, both with and without specifying an index name.

```sql
-- Create partitioned table
CREATE TABLE gidxpart (a int, b int, c text) PARTITION BY RANGE (a);
CREATE TABLE gidxpart1 PARTITION OF gidxpart FOR VALUES FROM (1) TO (10);
CREATE TABLE gidxpart2 PARTITION OF gidxpart FOR VALUES FROM (10) TO (100);
CREATE TABLE gidxpart3 PARTITION OF gidxpart FOR VALUES FROM (100) TO (200);

-- Create global unique index on partitioned table (specify index name)
CREATE UNIQUE INDEX gidx_u ON gidxpart USING btree(b) GLOBAL;

-- Create global unique index on partitioned table (do not specify index name)
CREATE UNIQUE INDEX ON gidxpart (b) GLOBAL;
```

--------------------------------

### Install Hiredis Library

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/redis_fdw

Installs the Hiredis library, a dependency for redis_fdw. Ensure you have wget and tar installed.

```bash
wget https://github.com/redis/hiredis/archive/refs/tags/v1.3.0.tar.gz
tar xzvf v1.3.0.tar.gz
cd hiredis-1.3.0
make
sudo make install
sudo ldconfig
```

--------------------------------

### Install Ivory Operator

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v5.0/v5.0/4.6.2

Apply the Kustomize manifests to install the Ivory Operator. This includes creating the namespace and the default installation.

```bash
kubectl apply -k examples/kustomize/install/namespace
kubectl apply --server-side -k examples/kustomize/install/default
```

--------------------------------

### Compile and Install PgAudit

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pgaudit

Steps to download, compile, and install the PgAudit extension using PGXS. Ensure the PGHOME environment variable is set correctly.

```shell
wget https://github.com/pgaudit/pgaudit/archive/refs/tags/18.0.tar.gz
tar -xf 18.0.tar.gz
cd pgaudit-18.0
make install USE_PGXS=1 PG_CONFIG=$PGHOME/bin/pg_config
```

--------------------------------

### Install wal2json from Source

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/wal2json

Compile and install the wal2json plugin from its source code. Ensure PG_CONFIG points to your IvorySQL installation.

```bash
# From https://github.com/eulerto/wal2json/releases/tag/wal2json_2_6 Download the 2.6 source package wal2json_2_6.zip

unzip wal2json_2_6.zip
cd wal2json_2_6

# Compile and install the plugin
make PG_CONFIG=/usr/ivory-5/bin/pg_config
make PG_CONFIG=/usr/ivory-5/bin/pg_config install
```

--------------------------------

### Compile and Install Nginx

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/cloud_platform/ivorysql_cloud_installation

Steps to extract the Nginx tarball, configure the build with SSL module support, and then compile and install Nginx to the specified directory (/opt/cloud/nginx).

```bash
## 解压缩nginx-1.20.1.tar.gz安装包

[root@cloud web]# tar -zxvf nginx-1.20.1.tar.gz

## 解压后生成nginx-1.20.1文件夹

[root@cloud web]# ls -lrt
总用量 3924

-rwxrwxr-x. 1 root root 1061461 5月 25 2021 nginx-1.20.1.tar.gz

-rwxrwxr-x. 1 root root 2943732 10月 9 16:43 dist.tar.gz

drwxrwxr-x. 9 1001 1001 186 10月 9 16:53 nginx-1.20.1

drwxrwxrwx. 4 root root 103 10月 21 13:20 dist

## 配置导向

[root@cloud web]# cd nginx-1.20.1

[root@cloud nginx-1.20.1]# ./configure --prefix=/opt/cloud/nginx --with-http_ssl_module

## 编译安装

[root@cloud nginx-1.20.1]# make

[root@cloud nginx-1.20.1]# make install
```

--------------------------------

### Install Nginx Dependencies

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/cloud_platform/ivorysql_cloud_installation

Install required development packages for Nginx compilation using yum. Ensure pcre-devel, openssl, and openssl-devel are installed.

```bash
[root@host30 cloud]# yum -y install pcre-devel

[root@host30 cloud]# yum -y install openssl openssl-devel
```

--------------------------------

### Grant Permissions to Installation User

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/installation_guide

Assign ownership of the installation directory to the specified user. Replace 'ivorysql' and '/usr/ivory-5/' with your actual user and installation path.

```bash
sudo chown -R ivorysql:ivorysql /usr/ivory-5/
```

--------------------------------

### Compile and Install pg_stat_monitor

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_stat_monitor

Compile and install the pg_stat_monitor extension using PGXS. Ensure PG_CONFIG is set to your IvorySQL installation's pg_config path.

```bash
cd pg_stat_monitor
export PG_CONFIG=/usr/ivory-5/bin/pg_config
make USE_PGXS=1
make USE_PGXS=1 install
```

--------------------------------

### Compile and Install pg_textsearch from Source

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_textsearch

Steps to download, compile, and install the pg_textsearch plugin from its source code. Ensure PG_CONFIG points to your IvorySQL installation.

```bash
# From https://github.com/timescale/pg_textsearch/archive/refs/tags/v0.6.1.tar.gz download the source package
tar xzvf v0.6.1.tar.gz
cd pg_textsearch-0.6.1

# Compile and install the plugin
make PG_CONFIG=/usr/ivory-5/bin/pg_config
make PG_CONFIG=/usr/ivory-5/bin/pg_config install
```

--------------------------------

### Configure IvorySQL Source Build

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_ai_query

Example configuration options for compiling IvorySQL from source.

```bash
./configure \
--prefix=$PWD/inst \
--enable-cassert \
--enable-debug \
--enable-tap-tests \
--enable-rpath \
--enable-nls \
--enable-injection-points \
--with-tcl \
--with-python \
--with-gssapi \
--with-pam \
--with-ldap \
--with-openssl \
--with-libedit-preferred \
--with-uuid=e2fs \
--with-ossp-uuid \
--with-libxml \
--with-libxslt \
--with-perl \
--with-icu \
--with-libnuma
```

--------------------------------

### Install IvorySQL using DEB Package

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.22/v1.22/6

Install the downloaded IvorySQL DEB package using the 'apt' command. This is a straightforward method for installing IvorySQL on Ubuntu.

```bash
sudo apt install ./IvorySQL-1.22-5a0861c-20260416.amd4.deb
```

--------------------------------

### Start and Check IvorySQL Service with pg_ctl

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.17/v1.17/6

Start the IvorySQL database service using 'pg_ctl' and check its status. This is a manual method for starting and managing the database.

```bash
pg_ctl -D $PGDATA -l logfile start
pg_ctl -D $PGDATA status
```

--------------------------------

### Verify PgDog Installation

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pgdog

Checks the installed PgDog version.

```bash
./target/release/pgdog --version
```

--------------------------------

### Take a Performance Sample

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_profile

Initiate the first performance sample collection using the take_sample() function. Subsequent calls will collect new samples.

```sql
-- 第一次采样
SELECT * FROM profile.take_sample();

-- ……业务负载运行一段时间后，再次采样
SELECT * FROM profile.take_sample();
```

--------------------------------

### Create pg_show_plans Extension

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/ecosystem_components/pg_show_plans

After restarting the server, connect to your target database and create the pg_show_plans extension using the SQL command.

```sql
CREATE EXTENSION pg_show_plans;
```

--------------------------------

### Install IvorySQL Deb Package

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v4.6/v4.6/6.html

Installs the downloaded IvorySQL deb package.

```bash
$ sudo dpkg -i IvorySQL-4.6-a50789d-20250304.amd64.deb
```

--------------------------------

### Initialize Standby Node with Base Backup

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/cluster_setup

Creates a base backup of the master node on the standby node and sets up streaming replication. Replace placeholders with your master node's IP and port.

```bash
$ sudo pg_basebackup -F p -P -X fetch -R -h <primary_ip> -p <primary_port> -U ivorysql -D /usr/local/ivorysql/ivorysql-5/data
```

--------------------------------

### Install IvorySQL

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/installation_guide

Installs IvorySQL to the directory specified by the --prefix during configuration.

```bash
$ sudo make install
```

--------------------------------

### Create a Base Backup

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.17/v1.17/21

Creates a base backup of the server 'mydbserver' and stores it in the local directory '/usr/local/pgsql/data'.

```bash
$ pg_basebackup -h mydbserver -D /usr/local/pgsql/data
```

--------------------------------

### Field Selection Examples

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v1.23/v1.22/7

Shows examples of selecting fields from composite types, including cases where parentheses are required for clarity, and how to select all fields using '.*'.

```sql
mytable.mycolumn
```

```sql
$1.somecolumn
```

```sql
(rowfunction(a,b)).col3
```

```sql
(compositecol).somefield
```

```sql
(mytable.compositecol).somefield
```

```sql
(compositecol).*
```

--------------------------------

### Run IvorySQL with Podman

Source: https://docs.ivorysql.org/cn/ivorysql-doc/v5.3/4.6.4

Starts an IvorySQL container using Podman, mapping ports and setting a password.

```bash
$ podman run --name ivorysql -p 5434:5432 -e IVORYSQL_PASSWORD=123456 -d ivorysql/ivorysql:5.3-ubi8
```

--------------------------------

### Install DEB Package

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/installation_guide

Installs the downloaded IvorySQL DEB package using dpkg.

```bash
$ sudo dpkg -i IvorySQL-5.0-a50789d-20250304.amd64.deb
```

--------------------------------

### Partitioned Table ONLINE Index Build

Source: https://docs.ivorysql.org/cn/ivorysql-doc/master/oracle_compatibility/compat_create_index_online

Shows how to create an ONLINE index on a partitioned table, noting that it silently degrades to a regular build. Verifies index validity across parent and partitions.

```sql
-- 分区表父级索引 ONLINE
-- 注：ONLINE 在分区表上静默降级为普通构建，与 Oracle 行为一致
CREATE INDEX idx_part_online ON tbl_ci_part (id) ONLINE;

-- 验证索引已创建且有效（降级为普通构建，父级索引 + 各分区子索引均 VALID）
SELECT c.relname, i.indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname LIKE '%idx_part_online%'
ORDER BY c.relname;
-- 期望：idx_part_online（父）及两个分区子索引 indisvalid = t

-- 分区表 ONLINE + TABLESPACE
CREATE INDEX idx_part_online_tbs ON tbl_ci_part (region) ONLINE TABLESPACE pg_default;
```
Tokens:
Raw