Installing VMware SQL with Postgres for Kubernetes Extensions

This topic covers installation steps for the extensions packaged with VMware Postgres Operator 1.2 and later. The extensions include:

• Orafce
• PostGIS
• pgAudit

Refer to the linked extension documentation for instructions on using the extensions.

pgAudit

pgAudit is packaged with the Postgres instance images. It needs to be manually installed on the primary data pod:

1. Connect to the primary pod via psql and use:

   CREATE EXTENSION pgaudit;
   

2. Verify the installation using a command similar to:

   select count(*) from pg_extension where extname = 'pgaudit';
   

   count
   -------
   1
   (1 row)
   

For further details on Session and Object logging, see Session Audit logging and Object Session logging in the VMware Postgres documentation.

Orafce

Orafce is packaged with the Postgres instance images. It needs to be manually installed on the primary data pod:

1. Connect to the primary pod via psql and use:

   CREATE EXTENSION orafce;
   

2. To verify the installation:

   SELECT months_between(date '1995-02-02', date '1995-01-01');
   

   The output should be similar to:

   months_between
   ------------------
   1.03225806451613
   (1 row)
   

For more information, refer to the Orafce documention.

PostGIS

PostGIS is packaged with the Postgres instance images. It needs to be manually installed on the primary data pod:

1. Connect to the primary pod via psql and use:

   CREATE EXTENSION postgis;
   

2. To verify the installation:

   SELECT st_pointfromtext('POINT(1 1)');
   

   The output should be similar to:

   st_pointfromtext
   --------------------------------------------
   0101000000000000000000F03F000000000000F03F
   (1 row)
   

For more information refer to the PostGIS documentation.

Address Standardizer

1. Connect to the primary pod via psql and use:

   CREATE EXTENSION address_standardizer;
   

2. To verify the installation:

   SELECT num, street, city, state, zip FROM parse_address('1 Devonshire Place PH301, Boston, MA 02109');
   

   You should see output similar to:

   num |         street         |  city  | state |  zip
   -----+------------------------+--------+-------+-------
   1   | Devonshire Place PH301 | Boston | MA    | 02109
   (1 row)
   

For more information refer to the Address Standardizer documentation.