One post tagged with "IvorySQL"

Grant Zhou, head of Highgo North America Research Institute, delivered the technical explanation of IvorySQL project at the meeting on May 26, 2022 Ottawa time.

This conference mainly introduces who we are, what IvorySQL is, IvorySQL key function points, community contribution guide, developer guide, etc. Based on PostgreSQL 14, IvorySQL adds some exciting features on top of PostgreSQL!

IvorySQL provides the following features based on PostgreSQL:

• Oracle compatible Packages

• Oracle compatible PL/iSQL procedural language

• GUC to switch between Oracle and PostgreSQL modes

• Oracle compatible syntax for various DDL operations

• Oracle compatible date/time functions

• Built-in Orafce (https://github.com/orafce/orafce)

Many more …, please read the release notes from https://www.ivorysql.org/zh-CN/releases-page

• Please check the Issues tab in GitHub regularly to get the latest feature lists

• Welcome everyone to submit the features you want here

https://github.com/IvorySQL/IvorySQL/issues

bilibili Video explanation link:

https://www.bilibili.com/video/BV17U4y1y7nZ?spm_id_from=333.999.list.card_archive.click

Join the IvorySQL community by subscribing to mailing lists:

• Hackers List
• Users List

Also, don't forget to give us a ⭐ on Github

IvorySQL is an open source project. One of the core goals of this project is to deliver oracle compatibility, So that one can use the oracle code on IvorySQL database server.

Providing Oracle compatibility on top of PostgreSQL is a multi-dimensional task. Ranging from providing the Oracle compatible SQL syntax to adding support for data types that are either missing or behaves differently in PostgreSQL. One of the main core of Oracle compatibility is to provide the compatible PL (procedural language) in PostgreSQL that is functionally and syntactical compatible with Oracle's PL/SQL. For that purpose IvorySQL has added a new PL language PL/iSQL that that accepts, understands and executes the PL/SQL syntax. And as you know one of the core feature of Oracle's PL/SQL is the PACKAGES. Package is a schema object in Oracle that contains definitions for a group of related functionalities and is one of the most widely used feature of Oracle.

So In this blog I will give a introduction of Packages followed by an example on how you can create, use, and destroy Oracle style packages with IvorySQL.

Packages​

So what are packages? The Packages are grouping of PL/iSQL code, divided in logical program units. In other words a package can be considered a collection of related functions, procedures, variables or cursors. This collection is collectively be accessed via the common name.

IvorySQL has PL/iSQL language that accepts, understands and executes the PL/SQL code. The packages use this same language. The packages have two main components.

• Package Specification The package specification lists all the items that can be accessed from outside the package. such as functions, procedures, variables and cursors. This is also know as public specification.

• Package Body The Package Body contains the implementation of all functions and procedures that are listed in the package specification. In addition these, it can also implement more function and procedure or other elements.

These elements that are not in the package specification, will be treated private members of the package and these can only be referenced from within the package. The outside access is not permitted.

The package body can also have a special code block called initializer block. This is a special because this block is executed only once per session, at the very beginning when the package is first accessed.

Let's see some example of the code and see how a package operates.

Example​

Generic Example​

First let's set the compatibility mode, so we can access oracle compatible features available in the ivorysql database.

SET compatible_mode TO oracle;

CREATE TABLE books (
    id     INT, 
    title  VARCHAR2(100), 
    author VARCHAR2(100),
    price  NUMBER);

INSERT INTO books VALUES (10, 'The Hobbit', 'J. R. R. Tolkien', 10.0);
INSERT INTO books VALUES (11, 'Winnie-the-Pooh', 'A. A. Milne', 5.0);
INSERT INTO books VALUES (12, 'Peter Pan', 'James Matthew Barrie', 4.0);
INSERT INTO books VALUES (13, 'Charlie and the Chocolate Factory', 'Roald Dahl', 5.0);
INSERT INTO books VALUES (14, 'SThe Jungle Book', 'Rudyard Kipling', 9.0);
INSERT INTO books VALUES (15, 'The Little White Bird', 'James Matthew Barrie', 3.0);

Let's create a simple package. This package declares a cursor to list all available books. Have some subprograms to list, add and remove books. It also declares a some private variables to keep count and book information.

CREATE OR REPLACE PACKAGE mybooks AS
    CURSOR      booksinfo IS SELECT * from BOOKS;

    PROCEDURE list_books;
    FUNCTION add_book(title VARCHAR2(100), author VARCHAR2(100), 
         price NUMBER) RETURN bool;
    PROCEDURE remove_book(book_title VARCHAR2(100));
END;
ivorysql$# /
CREATE PACKAGE

CREATE OR REPLACE PACKAGE BODY mybooks AS

    -- declare private variables
    bookinfo    booksinfo%rowtype; 
    books_count INT;

    PROCEDURE list_books AS
    BEGIN
        OPEN booksinfo;
        RAISE INFO 'Book Info:';
        RAISE INFO '';
        LOOP
            FETCH booksinfo into bookinfo;
            EXIT WHEN NOT FOUND;

            RAISE INFO '  Name    = %', bookinfo.title;
            RAISE INFO '  Author  = %', bookinfo.author;
            RAISE INFO '  Price   = %', bookinfo.price;
            RAISE INFO '------------------------------';
        END LOOP;
        RAISE INFO 'Total Books   = %', books_count;
        CLOSE booksinfo;
    END;

    FUNCTION add_book(title VARCHAR2(100), author VARCHAR2(100),
        price NUMBER) RETURN bool AS
    BEGIN
        INSERT INTO BOOKS VALUES (
            books_count + 1,
            title,
            author,
            price);
        
        books_count := books_count + 1;
        RETURN true;

        EXCEPTION WHEN OTHERS THEN
            RETURN false;
    END;

    PROCEDURE remove_book(book_title VARCHAR2(100)) AS
    BEGIN
        DELETE FROM BOOKS WHERE title = book_title;

        EXCEPTION WHEN OTHERS THEN
            RAISE INFO 'Book % not found', book_title;
    END;

-- initializer block
BEGIN           
    books_count := 0;
    SELECT count(*) INTO books_count
        FROM BOOKS;
END;
ivorysql$# /
CREATE PACKAGE BODY

Let checkout the count. This anonymous block tries to access the private members of the package, which should result in error.

ivorysql=# DECLARE
ivorysql$#   nbooks int := 0;
ivorysql$# BEGIN
ivorysql$#     nbooks := mybooks.books_count;
ivorysql$#     RAISE INFO 'Total Books   = %', nbooks;
ivorysql$# END;
ivorysql$# /
2022-05-26 16:35:32.328 PKT [63554] ERROR:  package private variable ("mybooks.books_count") is not accessible

Let's list all available books using the subprogram list_books of the package mybooks.

ivorysql=# BEGIN
ivorysql$#     mybooks.list_books;
ivorysql$# END;
ivorysql$# /
INFO:  Book Info:
INFO:  
INFO:    Name    = The Hobbit
INFO:    Author  = J. R. R. Tolkien
INFO:    Price   = 10
INFO:  ------------------------------
INFO:    Name    = Winnie-the-Pooh
INFO:    Author  = A. A. Milne
INFO:    Price   = 3
....
....
INFO:  Total Books   = 6
DO
ivorysql=#

Let's add a new book using the subprogram add_book of the package mybooks.

DECLARE
  added bool;
BEGIN
    added := mybooks.add_book('The Cat in the Hat', 'Dr. Seuss', 10);
    IF added = TRUE THEN
        RAISE INFO 'new book added';
    END IF;
END;
/

Dropping a Package​

One can either drop the package body or complete package using:

• DROP PACKAGE BODY [package name]

ivorysql=# DROP PACKAGE BODY mybooks;
DROP PACKAGE BODY

• DROP PACKAGE [package name

ivorysql=# DROP PACKAGE mybooks;
DROP PACKAGE

Package Invocation with different rights​

We are going to create two packages with invoker and definer rights and watch the results to see how they differentiate.

ivorysql=> SELECT current_user;
 current_user 
--------------
 ivorysql
(1 row)

-- Create a Package with INVOKER rights

CREATE OR REPLACE PACKAGE pkg_invrights AUTHID CURRENT_USER AS
    PROCEDURE curr_user;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_invrights AS
    PROCEDURE curr_user AS
    BEGIN
        RAISE INFO 'Current User: %', current_user;
    END;
END;
/

-- Create a Package with DEFINER rights

CREATE OR REPLACE PACKAGE pkg_defrights AUTHID DEFINER AS
    PROCEDURE curr_user;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_defrights AS
    PROCEDURE curr_user AS
    BEGIN
        RAISE INFO 'Current User: %', current_user;
    END;
END;
/

Let's see the package with invoker rights first.

ivorysql=> CALL pkg_invrights.curr_user;
INFO:  Current User: ivorysql
CALL

Let's see the package with definer rights.

ivorysql=> CALL pkg_defrights.curr_user;
INFO:  Current User: ivorysql
CALL

Let's switch to another user named ivuser

ivorysql=> \c ivorysql ivuser;
You are now connected to database "ivorysql" as user "ivuser".

Let's see the package with invoker rights first.

ivorysql=> CALL pkg_invrights.curr_user;
INFO:  Current User: ivuser
CALL

Let's see the package with definer rights.

ivorysql=> CALL pkg_defrights.curr_user;
INFO:  Current User: ivorysql
CALL

As shown above when the package with invoker rights (pkg_invrights) is called, it's using the privileges of the user that invoked the package. However, when a definer package (pkg_defrights) is called, it still uses the privileges of the package owner.

IvorySQL can be built on Linux, OSX, Unix, and Windows platforms, and is basically the same as PostgreSQL compiled and installed. This article describes the steps for compiling source code on a Linux-based system.

Preparation​

1.1 Obtaining the IvorySQL source code​

For Windows or Linux, if you have Git installed, you can use git to download:

git clone https://github.com/IvorySQL/IvorySQL.git

git clone https://gitee.com/IvorySQL/IvorySQL.git

Or go directly to Github or Gitee to download:

https://github.com/IvorySQL/IvorySQL

https://gitee.com/IvorySQL/IvorySQL

Github can be a bit slow, use it if it works, and switch to Gitee if it doesn't.

As of this writing, the latest release of IvorySQL is 1.2, released on February 28, 2022.

The source version used in this article is also IvorySQL 1.2.

1.2 Installing the dependency packages​

To compile IvorySQL from source, you must ensure that a prerequisite packages are available on your system.

Run the following command to install related packages:

sudo yum install -y bison-devel readline-devel zlib-devel openssl-devel wget
sudo yum groupinstall -y 'Development Tools'

Note: "Development Tools" includes GCC, make, Flex, bison.

Compilation and installation​

The source code obtained in front of the folder IvorySQL, next we will enter this folder for operation.

2.1 configuration​

Run the following command as the Root user:

./configure

Note: Since --prefix is not provided, /usr/local/pgSQL will be used as default installation path.

To use a different installation path, such as "/ usr/local/ivorysql/ivorysql - 1.2" :

./configure --prefix=/usr/local/ivorysql/ivorysql-1.2

For more configure parameters, try ./configure --help. Also check out the PostgreSQL manual.

2.2 Compilation and Installation​

After the configuration is complete, run make to compile:

make

To test the newly compiled service using regression tests before installing it, use either of the following commands:

make check
make check-world

Then install:

make install

Initialize the database service​

We only have a simple configuration here, can be local and remote connection is ok.

3.1 Creating an OS User​

In the root session, create user ivorysql:

/usr/sbin/groupadd ivorysql
/usr/sbin/useradd -g ivorysql ivorysql -c "IvorySQL1.2 Server"
passwd ivorysql

3.2 Creating a Data Directory​

Next you need to create the data directory and change the permissions. Run the following command in the root session.

mkdir -p /ivorysql/1.2/data
chown -R ivorysql.ivorysql /ivorysql/1.2/

**Note: The data directory is not placed in "/var/lib/ivorysql/ivorysql-1/data" when you do installation using the RPMs.

3.3 Environment Variables​

Switch to ivorysql user, modify the /home/ivorysqL/. bash_profile file, and configure environment variables:

umask 022
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH
export PATH=/usr/local/pgsql/bin:$PATH
export PGDATA=/ivorysql/1.2/data

Make environment variables take effect in the current IVorysQL user session:

source .bash_profile

You can also log in again or start a session with a new user ivorysQL.

3.4 Configuring the Firewall​

If the firewall is enabled, port 5333 needs to be opened:

firewall-cmd --zone=public --add-port=5333/tcp --permanent
firewall-cmd --reload

Note: The default port is 5333. If this port is disabled, external clients will not be able to connect to the IP address.

3.5 the initialization​

In user ivorysql, simply execute initdb to complete the initialization:

initdb

Note: InitDB operates in the same way as PostgreSQL, and can be initialized in the same way as PG.

3.6 Starting the Database​

Use pg_ctl to start the database service:

pg_ctl start

Check the status, the startup is successful:

pg_ctl status

pg_ctl: server is running (PID: 29549)

/usr/local/pgsql/bin/postgres

Configure services​

Here is a simple configuration, can be local and remote connection is ok

4.1 Client Authentication​

Add the following information to /ivorysql/1.2/data/pg_hba.conf:

host    all             all             0.0.0.0/0               trust

Note: this is trust, which means you can log in without secret.

Run the following command to load the configuration:

pg_ctl reload

4.2 Basic Parameters​

Connect to database through PSQL:

psql

Modify listening address:

alter system set listen_addresses = '*';

Note: By default the server does not allow connections from external hosts.

Restart the service for the Settings to take effect:

pg_ctl restart

4.3 Daemon Service​

Create service file:

touch /usr/lib/systemd/system/ivorysql.service

The edited content is as follows:

[Unit]
Description=IvorySQL 1.2 database server
Documentation=https://www.ivorysql.org
Requires=network.target local-fs.target
After=network.target local-fs.target

[Service]
Type=forking

User=ivorysql
Group=ivorysql

Environment=PGDATA=/ivorysql/1.2/data/

OOMScoreAdjust=-1000

ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA}
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA}
ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA}

TimeoutSec=0

[Install]
WantedBy=multi-user.target

Note: There are many ways to write service. Be careful when using it in the production environment. Repeat the test several times.

Stop the database service started by pg_ctl, enable the systemd service, and start:

systemctl enable --now ivorysql.service

IvorSQL Database service operation commands:

systemctl start ivorysql.service            --启动数据库服务
systemctl stop ivorysql.service             --停止数据库服务
systemctl restart ivorysql.service          --重启数据库
systemctl status ivorysql.service           --查看数据库状态
systemctl reload ivorysql.service           --可以满足部分数据库配置修改完后生效

Database Client​

The IvorySQL client is the same as the PostgreSQL client.

5.1 PSQL connection​

Connecting from ivorysql OS user:

[ivorysql@Node02 ~]$ psql
psql (15devel)
Type "help" for help.

ivorysql=#

5.2 DBeaver connection​

DBeaver is a powerful open source tool with the following connectivity configurations:

5.3 Connect using HighGo Developer​

HighGo Developer is a tool independently developed by Highgo, which supports not only Highgo database, but also PostgreSQL and IvorySQL database. The connection configuration is as follows:

Uninstall​

If required IvorySQL can be uninstalled by compiling and uninstalling.

6.1 Backing Up Data​

The data directory is under "/ ivorysQL /1.2/data", so we can protect the directory, It is recomended to stop the database service and make a backup.

6.2 Compilation and Uninstallation​

Switch to the source directory and run the following commands:

make uninstall
make clean

Delete residual directories and files:

systemctl disable ivorysql.servicemake                  --Disable the service
mv /usr/lib/systemd/system/ivorysql.service /tmp/       --delete or move the service file to /tmp
rm -fr /usr/local/pgsql                                 --Remove the installed files

The ivorysql user and the corresponding environment variables can be cleared according to the situation.

The rest is the data directory "/ ivorysQL /1.2/data", please be sure to take a backup before processing.

There are installed dependency packages, you can decide whether to uninstall according to the situation.

Postscript​

• For more detailed operations, see PostgreSQL.

• You can also refer to the IvorySQL source code under the documentation: /root/ivorysql/readme.md.

• You can also open a link to the website:

  https://gitee.com/IvorySQL/IvorySQL/blob/master/README.md。

• If you have any question, welcome to the official community IvorySQL warehouse: github.com/IvorySQL/IvorySQL submit issue.

Join the IvorySQL community by subscribing to mailing lists:

• Hackers List
• Users List

Also, don't forget to give us a ⭐ on Github

Install software through the YUM source​

This section uses Centos7.9 as an example to show how to install the IvorySQL database. Here is the simplest YUM source installation.

1.1 Installing the YUM Source​

Download address: https://yum.highgo.ca/ivorysql.html

Click "DOWNLOAD REPO RPM" to DOWNLOAD ivorysqL-release-1.0-1.noarch. RPM and upload it to Centos7.9:

Or use wget on Centos7 to download:

wget https://yum.highgo.ca/dists/ivorysql-rpms/repo/ivorysql-release-1.0-1.noarch.rpm

Install ivorysql - release - 1.0-1. Noarch. RPM:

rpm -ivh ivorysql-release-1.0-1.noarch.rpm

After installation, will create the YUM source configuration file: / etc/yum.repos. d/ivorysql.repo.

Search to view related installation packages:

yum search ivorysql

The search results are described in Table 1:

Table 1 Description of YUM sources

Note: 1, 2, 5, 10 are required for RPM package installation, hyperlinks can be downloaded separately.

1.2 installation IVORYSQL​

To install the database service, install ivorysQL1-Server.

Run the following command in the user root session:

yum install -y ivorysql1-server

Installation List:

ivorysql1-server.x86_64 0:1.2-1.rhel7

Dependent installation:

• ivorysql1.x86_64 0:1.2-1.rhel7 ivorysql1-contrib.x86_64 0:1.2-1.rhel7

• ivorysql1-libs.x86_64 0:1.2-1.rhel7 libicu.x86_64 0:50.2-4.el7_7

• libtirpc.x86_64 0:0.2.4-0.16.el7 libxslt.x86_64 0:1.1.28-6.el7

• python3.x86_64 0:3.6.8-18.el7 python3-libs.x86_64 0:3.6.8-18.el7

• python3-pip.noarch 0:9.0.3-8.el7 python3-setuptools.noarch 0:39.2.0-10.el7

1.3 Installed Directory​

Table 2 describes the file directories generated during the YUM installation process.

Table 2 Installation directory file description

1.4 RPM installation​

RPM installations, by the way, need to be installed in this order:

yum install -y libicu libxslt python3                   --先安装依赖
rpm -ivh ivorysql1-libs-1.2-1.rhel7.x86_64.rpm
rpm -ivh ivorysql1-1.2-1.rhel7.x86_64.rpm
rpm -ivh ivorysql1-contrib-1.2-1.rhel7.x86_64.rpm --nodeps
rpm -ivh ivorysql1-server-1.2-1.rhel7.x86_64.rpm

So, it's quite convenient to install using YUM. If not, download it separately and install it in this order.

Download address: https://yum.highgo.ca/ivorysql.html

Click BROWSE Repository-1 to find the corresponding package to download (note: dependency packages are not available on the website).

Initialize the database​

The IvorySQL database is easy to initialize and start or stop by default.

2.1 Default Initialization​

To initialize the system, run the following command as user root:

/usr/local/ivorysql/ivorysql-1/bin/ivorysql-1-setup initdb

Note: IvorysQL-1 is read by default because SERVICE_NAME is not provided.

Enable and start the service:

systemctl enable --now ivorysql-1.service

IvorysqL-1-setup

ivorysql-1-setup {initdb|check_upgrade|upgrade} [SERVICE_NAME]

Note: For details about how to use this command, run ivorysqL-1-setup --help.

2.2 Custom Initialization​

If we have a requirement to specify the data file path (/ ivorysQL /1.2/data/) and port number (5866), then we can customize the initialization.

According to the default service file "/ usr/lib/systemd/system/ivorysql - 1. The service" in the same directory replication of a new "ivorysql - 5866. The service" :

cp /usr/lib/systemd/system/ivorysql-1.service /usr/lib/systemd/system/ivorysql-5866.service

Modify Add the following information:

Environment=PGDATA=/ivorysql/1.2/data/
Environment=PGPORT=5866

Specify SERVICE_NAME as ivorysQL-5866 to initialize:

/usr/local/ivorysql/ivorysql-1/bin/ivorysql-1-setup initdb ivorysql-5866

Enable and start the service:

systemctl enable --now ivorysql-5866.service

2.3 Common Service Operations​

IvorSQL Database service operation commands:

systemctl start ivorysql-1.service              --启动数据库服务
systemctl stop ivorysql-1.service               --停止数据库服务
systemctl restart ivorysql-1.service            --重启数据库
systemctl status ivorysql-1.service         --查看数据库状态
systemctl reload ivorysql-1.service         --可以满足部分数据库配置修改完后生效

Configure database services​

We only have a simple configuration here, can be local and remote connection is ok

3.1 Client Authentication​

Modify the/var/lib/ivorysql/ivorysql - 1 / data/pg_hba. Conf, add the following content:

host    all             all             0.0.0.0/0               scram-sha-256

Run the following command to load the configuration:

systemctl reload ivorysql-1.service

3.2 Basic Parameters​

Switch to user ivorysQL:

su - ivorysql

Connect to database through PSQL:

psql

Change ivorysQL password for remote connection:

alter system set listen_addresses = '*';
alter user ivorysql password '666666';

Note: User ivorysQL does not have a password. If you do not change the password, you can change the client authentication mode to Trust to avoid secret login.

Return to user root and restart the service for the Settings to take effect:

systemctl restart ivorysql-1.service

The firewall​

If the firewall is enabled, port 5333 needs to be opened:

firewall-cmd --zone=public --add-port=5333/tcp --permanent
firewall-cmd --reload

Note: The default ivorysQL service port is 5333. If this port is disabled, external clients fail to connect to the IVorysQL service through an IP address.

The client connects to the database​

The IvorySQL client is the same as the PostgreSQL client.

4.1 PSQL connection​

Operating system user IvorysQL session connection:

[root@Node02 ~]# su - ivorysql 
Last login: Wed Apr 27 23:58:57 CST 2022 on pts/0
-bash-4.2$ psql
psql (14.2)
Type "help" for help.

ivorysql=#

4.2 DBeaver connection​

DBeaver is a powerful open source tool with the following connectivity configurations:

4.3 Highgo Developer connection​

Highgo Developer is a tool independently developed by Highgo, which supports not only Highgo database, but also PostgreSQL and IvorySQL database. The connection configuration is as follows:

Uninstall​

Since we installed through yum, it is recommended to use yum to uninstall. Try not to use RPM, which may cause incomplete uninstall. Of course, you can also uninstall it according to the Yum installation list.

5.1 Backing Up Data​

Data directory in/var/lib/ivorysql under/data/ivorysql - 1, so we can this directory will be protected, and best to stop the service, do a backup.

5.2 YUM uninstall​

Stop the database service first:

systemctl stop ivorysql-1.service

Use the "yum history list" to determine the transaction ID of the yum installation:

[root@Node02 ~]# yum history list
Loaded plugins: fastestmirror
ID     | Login user               | Date and time    | Action(s)      | Altered
-------------------------------------------------------------------------------
     5 | root <root>              | 2022-04-27 12:38 | Install        |   11  <
     4 | root <root>              | 2022-03-26 16:08 | Install        |   35 > 
     3 | root <root>              | 2022-03-26 16:07 | I, U           |   19   
     2 | root <root>              | 2022-03-26 16:07 | I, U           |   73   
     1 | System <unset>           | 2022-03-26 15:59 | Install        |  299   
history list

You can see that ID 5 is the transaction that performs the installation. Run the following command to uninstall (replace XX with 5) :

yum history undo XX

You can also use the following command to uninstall:

yum remove ivorysql-server

However, the command is not completely uninstalled, only removing two dependencies and failing to remove eight dependencies. You can decide whether to uninstall this way depending on whether to keep these dependencies.

Postscript​

• For more detailed operations, see postgresQL operations. For example, direct initialization with initDB.

• You can also refer to the IvorySQL documentation:

/usr/share/doc/ivorysqL1-1.2/readme.rpm -dist

• If you have any question, welcome to the official community IvorySQL warehouse: github.com/IvorySQL/IvorySQL submit issue.

Join the IvorySQL community by subscribing to mailing lists:

• Hackers List
• Users List

Also, don't forget to give us a ⭐ on Github

The Postgres Conference in Silicon Valley is the largest PG conference on the West Coast and one of the most important Postgres conferences of the year in the US and globally. It will be held on April 7-8, 2022 (PDT) at the Hilton Hotel SAN Jose, California, USA. As an annual technology event for PostgreSQL technologists, practitioners, and enthusiasts around the world, the Silicon Valley Conference is dedicated to bringing together and discussing the relationship between people, Postgres, and data! The conference is as inclusive and fair as silicon Valley, bringing together the best speakers, audiences and sponsors, all working to create growth opportunities for the global Postgres ecosystem.

About 200 people attended the meeting, the largest offline meeting since the outbreak.

Bruce Momjian, a key member of the community, attended the conference, which was organized by PostgresConf,Joshua D.Drake, Jim Mlodgenski, and others. People from the United States,China, Canada, Brazil, Spain, Germany, India, Pakistan and other countries participated.

Grant Zhou, head of Highgo North America Research Institute and Secretary general of PostgreSQL China International Advisory Committee, was invited to participate in PostgreSQL internal development discussions and community project contributions.

The following is a two-day Postgres conference briefing in Silicon Valley brought to you by the IvorySQL open source database community.

Some of the speech​

Digital Rights and Privacy: Concerns for the 21st century--by Andres Arrieta

The Electronic Frontier Foundation has been protecting and fighting for our civil liberties for over 30 years. In those 30 years a lot has happened: Our relationship to the internet has fundamentally changed, and yet, in many ways, our understanding of how it works has remained stagnant. 30 years ago, the Internet was a mystical place of wizardry with many unknowns, and a realm which was difficult to access. Today it’s a core part of our life that we have become reliant on, and while it has become far easier than ever to access the many gifts that the Internet provides, understanding of how it works - from policy makers to enforcement agencies - continues to lag. During that time, EFF and its mission have grown to cover many of the aspects that technology has brought for better or worse and to help protect those impacted by it, while ensuring a bright future with innovation that improves our lives. So what is happening today and where are our efforts going? Why have some issues like privacy and competition become so strongly intertwined, and what new opportunities and threats lay ahead? I’ll walk you through some of our work, some of the areas of concern, and some of the things I believe will help us work towards a better future.

Past, Present, and Future of Logical Replication--by Amit Kapila In this talk, I will tell how the Logical Replication has been evolved over years in PostgreSQL. This will explain some of the major recent enhancements like facilitating logical replication of two-phase and large in-progress transactions. I will share my views on how this technology can be leveraged to build a highly scalable and available database solution for large Enterprises. After that, I will also talk about some of the important enhancements being discussed in this technology area for future releases of PostgreSQL. I'll also cover how this technology can be enhanced for data migration from PostgreSQL to other databases.

IvorySQL--An Open Source Oracle-compatible Database Based on PostgreSQL--by GRANT ZHOU There are many users who need to migrate their applications from Oracle to the open source Postgres, but in order to support the new database, users often need to re-develop the application, which is inconvenient. If there is a database based on Postgres and compatible with most Oracle syntax and functions, it will be too convenient for customers. However, the official Postgres project will not accept this kind of code submission. After all, Postgres is Postgres, and Oracle is Oracle. So, let's make an Oracle compatible database. This talk will introduce how to make a database compatible with Oracle syntax based on PG, and introduce the IvorySQL project. This project is an open source project (Apache 2.0) led by Highgo Software, and currently has released version 1.2 based on PostgreSQL 14.2. Welcome everyone to contribute on this open source Oracle compatible database - IvorySQL, powered by PostgreSQL.

Non-Relational Postgres--by Bruce Momjian Postgres has always had strong support for relational storage. However, there are many cases where relational storage is either inefficient or overly restrictive. This talk shows the many ways that Postgres has expanded to support non-relational storage, specifically the ability to store and index multiple values, even unrelated ones, in a single database field. Such storage allows for greater efficiency and access simplicity, and can also avoid the negatives of entity-attribute-value (eav) storage. The talk will cover many examples of multiple-value-per-field storage, including arrays, range types, geometry, full text search, xml, json, and records.

Using Global database in Amazon Aurora PostgreSQL--by Shayon Sanyal and Sukhpreet Bedi An Amazon Aurora Global database is designed for globally distributed applications, allowing a single Amazon Aurora database to span multiple AWS Regions. It replicates your data with no impact on database performance, enables fast local reads with low latency in each Region, and provides disaster recovery (DR) from Region-wide outages. In this session, learn how to plan for cross-Region DR and easily scale Aurora reads across the world to place your applications close to your users. You also learn how to meet your recovery point objective (RPO) and recovery time objective (RTO) with Amazon Aurora global databases.

Amazon Babelfish for Aurora--by chandra pathivada Now the Babelfish for Aurora is available. This presentation is about how Babelfish helps customers to migrate SQL Server workloads to Postgres. In this presentation, we are going to give a demo of what is Babelfish , the internals of Aurora ,Aurora for SQL Server DBAs using Babelfish, and Labs on Application Migration.

The Biography of a Modern Cloud Native Application--by Alexandra Elchinoff and Karthik Ranganathan Modern cloud native applications lead exciting lives - from their birth in the cloud and dealing with massive unplanned success to surviving cloud outages and handling customers worldwide. In this talk, Yugabyte CTO Karthik Ranganathan walks through the eventful life of a successful cloud native app, as seen from the perspective of the data layer.

Some of the pictures​

Postgres Conference in Silicon Valley 2022​

https://postgresconf.org/conferences/SV2022

Join the IvorySQL community by subscribing to mailing lists:

• Hackers List
• Users List

Also, don't forget to give us a ⭐ on Github

Hi guys, PostgresWorld Webinars is back!

PostgreSQL plays an important role in the development of databases in China. Many database products around the world choose PostgreSQL as their technology development path. However, the problem of migrating applications from Oracle to Open source Postgres became the biggest obstacle, so we created the IvorySQL open source project, which is based on the latest PostgreSQL 14 database with strong Oracle compatibility. But why do we have to do IvorySQL open source project? How is it technically different from Postgres and Oracle? This webinar invites you to join us.

This webinar is hosted by Grant Zhou on IvorySQL - An Open Source Oracle-compatible Database Based on PostgreSQL.

Guest introduction​

Grant Zhou currently lived in Canada, he is the founder of Highgo Software North America, and he is leading the company PostgreSQL development team with members from Canada, China and Pakistan. He is working as the secretary of the International Consultant Committee of the PostgreSQL China Association at the same time, and also proud to be the Organizer & Asia Liaison at PostgresConf. He worked in Alcatel-Lucent (Nokia) company for more than ten years and had rich experiences on high availability, real-time telecommunication systems, database technologies and Unix/Linux programming.

In December 2021, the team announced the first available version of IvorySQL database, which is the only open source database with Oracle compatibility features based on PostgreSQL. On Feburay 28, 2022, based on PostgreSQL 14.2, IvorySQL 1.2 was released.

Conference Highlights​

There are many users who need to migrate their applications from Oracle to the open source Postgres, but in order to support the new database, users often need to re-develop the application, which is inconvenient. If there is a database based on Postgres and compatible with most Oracle syntax and functions, it will be too convenient for customers. However, the official Postgres project will not accept this kind of code submission. After all, Postgres is Postgres, and Oracle is Oracle. So, let's make an Oracle compatible database. This talk will introduce how to make a database compatible with Oracle syntax based on PG, and introduce the IvorySQL project. This project is an open source project (Apache 2.0) led by Highgo Software and currently has released version 1.2 based on PostgreSQL 14.2. Welcome everyone to contribute to this open source Oracle compatible database - IvorySQL, powered by PostgreSQL.

Time and Link​

• Time: Stay tuned for the IvorySQL Project Webinar on March 31, 2022 at 1:00pm PDT.
• Participating website：https://postgresconf.org/accounts/sign_up
• Watch the link：https://postgresconf.org/conferences/PostgresWorld-Webinars-2022/program/proposals/ivorysql-an-open-source-oracle-compatible-database-based-on-postgresql

Join the IvorySQL community by subscribing to mailing lists:

• Hackers List
• Users List

Also, don't forget to give us a ⭐ on Github

Just when the world was packing the bags for the holiday season and getting ready to welcome the New year we were hard at work and were giving the final touches to the project that our team was working on since the start of 2021. And it was the 15th of December just before the day end we got all green lights and silently released the very first version of IvorySQL before cleaning up the desk for that day.

IvorySQL at Glance​

IvorySQL is Apache 2.0 licensed Open Source Oracle compatible PostgreSQL. The very first release of IvorySQL is derived from PostgreSQL 14 with a firm commitment to always remain 100% compatible and a Drop-in replacement of the latest PostgreSQL.

IvorySQL adds a compatible_db GUC on top of existing standard PostgreSQL configuration parameters. The compatible_db is a toggle switch to switch between Oracle and PostgreSQL compatibility modes. The second major highlight of IvorySQL is PL/iSQL procedural language that supports oracle’s PL/SQL syntax. These two additions sit at the core of Oracle compatibility of IvorySQL without breaking the standard PostgreSQL compatibility. The compatible_db switches the behavior of functions and objects that exist in both Oracle and PostgreSQL and acts differently, while PL/iSQL builds the foundation for running the oracle code on IvorySQL with minimal changes.

IvorySQL comes with numerous Oracle compatibility features including Oracle style PACKAGES, DATA Types, and Conversion Functions. For details of Oracle compatibility features in IvorySQL refer to IvorySQL documentation

We are committed to following the principles of open source way​

IvorySQL is committed to abiding by the principles of open-source ways and we strongly believe in building a healthy and inclusive community. We maintain that good ideas can come from anywhere, and the best ideas should win. Only by including diverse perspectives, we can reach to the best decision. While the first version of IvorySQL is mainly focused on Oracle Compatibility features, going forward the future road map and feature set will be determined by the community in an open-source way.

Contributing to the IvorySQL​

There are plenty of ways to contribute to IvorySQL. You can contribute by providing the documentation updates, by providing the translations for the documentation. If you have design skills you can contribute to the IvorySQL website project.
Testing the IvorySQL and reporting issues, by issuing pull requests for bug fixes or new features or answering the questions on mailing lists are some ways to contribute to the IvorySQL project and all types of contributions are welcomed and appreciated by the IvorySQL community.

Getting Started​

All IvorySQL related projects including database server, website, and documentation are hosted and managed through Github. You can download the source code or released packages and track the current issues, pull requests, and discussions through the IvorySQL Github page.

Visit http://www.ivorysql.org and read the project documentation and contribution guidelines.

Join the IvorySQL community by subscribing to mailing lists:

• Hackers List
• Users List

Also, don't forget to give us a ⭐ on Github