Get PostgreSQL on OCI

PostgreSQL on OCI

Ready or not — PostgreSQL is now an offering on Oracle Cloud Infrastructure (OCI).

Already there are a number of Oracle sources of information for PostgreSQL.

We are happy to announce the General Availability of Oracle Cloud Infrastructure (OCI) Database with PostgreSQL version 14.9.

Introducing OCI Database with PostgreSQL: Completing Our Cloud Database Suite for Every Need

Then, there is the official Oracle site for OCI Database with PostgreSQL:

Explore OCI Database with PostgreSQL

Oracle’s innovative managed PostgreSQL service, OCI Database with PostgreSQL, combines cutting-edge open source technology with the robustness and speed of Oracle Cloud Infrastructure (OCI).

There are also some existing PostgreSQL OCI Terraform projects here to get your feet wet with PostgreSQL:

https://github.com/oracle-devrel/terraform-oci-arch-postgresql

https://github.com/oracle-devrel/terraform-oci-cloudbricks-postgresql

Whether you want to use PostgreSQL as a service, or just get an idea of the differences between Oracle and PostgreSQL on-prem, then let’s investigate the some of the similarities between PostgreSQL and Oracle database.

Shutdown and startup

Shutdown and startup in Oracle from the sqlplus command line client, like this:

# Oracle
# from the command line, then inside the sqlplus client
sqlplus / as sysdba

shutdown immediate

startup

Compare this with the shutdown and startup in PostgreSQL from the command line, like this:

# PostgreSQL
# from the command line
pg_ctl stop

pg_ctl start

Login

In Oracle, a user connects to an account or schema in a database, and can do some basic operations.

# Oracle
# setup environment variables for the mydb Oracle database
. oraenv
mydb

# login
sqlplus / as sysdba

--
SELECT version FROM v$instance;

-- do some maths
SELECT 2 + 2 FROM DUAL;

-- now exit
exit

For comparison, in PostgreSQL, a user connects to a database and then can do some basic operations like this:

# PostgreSQL
# login to the mydb PostgreSQL database and do some stuff
psql mydb

--
SELECT version();

-- do some maths
SELECT 2 + 2;

-- now exit with a meta-command 
\q

User privileges and roles

In PostgreSQL, a role is an entity that can own database objects and have database privileges; a role can be considered a “user”, a “group”, or both depending on how it is used.

• CREATE ROLE — define a new database role. Note: CREATE GROUP is now an alias for CREATE ROLE.
• CREATE USER is the same as CREATE ROLE except that it implies LOGIN. ie CREATE ROLE name LOGIN is equivalent to CREATE USER name.

PostgreSQL database roles are global across a database cluster installation (and not per individual database).

-- PostgreSQL
-- create a user with login privilege and password
CREATE ROLE foo 
WITH LOGIN PASSWORD 'secret';

-- show the roles
SELECT rolname FROM pg_roles;

A group can contain many users, so manage the group to manage privileges for many users.

Roles can own database objects and can hold privileges to access other objects. Also there are predefined roles (just like in Oracle). The pg_database_owner role owns the public schema.

-- PostgreSQL
-- SET ROLE - set the current user identifier of the current session.
SELECT SESSION_USER, CURRENT_USER;
SET ROLE 'Fred';
SELECT SESSION_USER, CURRENT_USER;

In PostgreSQL, user privileges are granted like this:

-- PostgreSQL
-- examples of object privileges
GRANT SELECT, INSERT, DELETE, UPDATE ON accounts TO joe;
GRANT SELECT, INSERT, DELETE, UPDATE ON mytable TO PUBLIC;

-- show roles
SELECT current_user;

-- Oracle
-- show roles, including roles of roles
SELECT * FROM SESSION_ROLES;

Tablespaces

In PostgreSQL, tablespaces are owned by users/roles.

A small number of objects, like role, database, and tablespace names, are defined at the cluster level and stored in the pg_global tablespace.

CREATE TABLESPACE registers a new cluster-wide tablespace.

-- PostgreSQL
-- create a new cluster-wide tablespace
CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';

-- List all tablespaces
SELECT spcname FROM pg_tablespace;

-- Also use the meta-command.
\db

After creation of a tablespace, grant users access to it. ie CREATE privilege.
Once created, a tablespace can be used from any database, provided the requesting user has sufficient privilege.

System parameters

In Oracle, the ALTER SYSTEM command can be used at the CDB level, or the PDB level to set system parameters.

-- Oracle
-- set a system parameter
ALTER SYSTEM SET dg_broker_start=TRUE;

-- reset
ALTER SYSTEM RESET dg_broker_start;

-- Oracle
-- SHOW - show the value of a system parameter
show parameter

In PostgreSQL, the ALTER SYSTEM command is used for changing server configuration parameters across the entire database cluster.

-- PostgreSQL
-- set a system parameter
ALTER SYSTEM SET wal_level=replica;

-- reset
ALTER SYSTEM RESET wal_level;

-- PostgreSQL
-- SHOW - show the value of a run-time parameter
SHOW ALL;

Database architecture

In Oracle, the general Oracle hierarchy is composed of:

• CDB
• PDBs
• schema containing tables/objects

A CDB will contain many PDBs, and they can be listed with:

-- Oracle
SELECT NAME, OPEN_MODE FROM V$PDBS;

The redo log files for a CDB will contain the redo records for all the PDBs.

Then to create a schema in Oracle:

-- Oracle
-- Create a schema or user
CREATE USER myschema;

-- Create a schema or user and define a password
CREATE USER myschema IDENTIFIED BY 'password';

Note: in Oracle, a schema contains objects only owned by it.

In PostgreSQL the general hierarchy is:

• cluster
• database
• schema containing tables/objects

A PostgreSQL database cluster contains one or more named databases. List all the PostgreSQL databases with:

-- PostgreSQL
SELECT datname FROM pg_database;

-- Also the meta-command is available
\l

The Write-Ahead Log (WAL) contains the transactions and is at the cluster level, so is shared amongst all the PostgreSQL databases.

Schemas are created in PostgreSQL as below:

-- PostgreSQL
-- Create a schema
CREATE SCHEMA myschema;

-- Create a schema and define a password
CREATE SCHEMA schema_name AUTHORIZATION user_name;

Note: in PostgreSQL, a schema can contain objects owned by different owners!

Schema qualifying

In Oracle, define the schema for the current session with the ALTER SESSION command.

-- Oracle
-- set a schema in your session:
ALTER SESSION SET current_schema = myschema;

INSERT INTO employees_temp (employee_id, salary, commission_pct)
VALUES (301, 2500, 0);

-- Note: in Oracle, not autocommit by default
commit;

In PostgreSQL, define a qualified name with the SET command:

-- PostgreSQL
-- set a qualified name for a schema like this, so don't need to specify it each time.
SET search_path TO myschema;

-- Note: in PostgreSQL, autocommit by default
INSERT INTO products (product_no, name, price)
VALUES (1, 'Cheese', 9.99);

Tables and datatypes

In Oracle, the basic data types are named as:

• BINARY_DOUBLE: 8 byte floating point. ie hardware arithmetic.
• BINARY_FLOAT: 4 byte floating point. ie hardware arithmetic.
• VARCHAR2: variable length character string.
• NUMBER: user specified precision.
• DATE: date and time.

PostgreSQL has similarly named datatypes, as follows:

• DOUBLE PRECISION: 8 byte floating point.
• REAL: 4 byte floating point.
• VARCHAR: variable length character string.
• NUMERIC: user specified precision.
• DATE: date and time.

In PostgreSQL, by default, the CREATE TABLE command is for the public schema. (Note: all users have access to the public schema.)

-- PostgreSQL create table
CREATE TABLE films (
 code char(5) CONSTRAINT firstkey PRIMARY KEY,
 title varchar(40) NOT NULL,
 did integer NOT NULL,
 date_prod date,
 kind varchar(10),
 len interval hour to minute
);

For tables that are owned as public, the ownership can be transferred as follows:

-- PostgreSQL: transfer a table to a new owner
ALTER TABLE table_name OWNER TO new_owner;

Table use of space

In Oracle, can query table/segment usage for any object using the dba_segments dictionary view.

-- Oracle
-- space used for a segment. eg table
SELECT SUM(blocks), SUM(bytes)/POWER(1024,3) 
FROM dba_segments
WHERE owner=UPPER('&own') AND segment_name=UPPER('&ind');

In PostgreSQL, each table is comprised of pages of 8k bytes each, and those pages can be queried.

-- PostgreSQL
-- space used for a table (each relpage is 8k bytes)
SELECT pg_relation_filepath(oid), relpages 
FROM pg_class 
WHERE relname = 'customer';

Bulk load from a table to a file

In PostgreSQL, perform bulk loads with the COPY command.

COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written.

COPY moves data between PostgreSQL tables and standard file-system files.

-- PostgreSQL
-- example of a bulk load from a table to a file
COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;

Each backend running COPY will report its progress in the pg_stat_progress_copy view.

Bulk load from a table to a table

There is a bulk load feature in Oracle, and it can be performed as follows:

-- Oracle
alter session set current_schema=test;

-- optionally name the transaction
SET TRANSACTION NAME 'bulkload';

-- bulk load in Oracle with the Append hint
INSERT /*+ APPEND */ INTO &tbl
 SELECT product_no, name, price
 FROM new_products
 WHERE release_date = 'today'
LOG ERRORS INTO
  err$_&tbl('bulkload_'||TO_CHAR(sysdate,'Dy_YYYYMMDD'))
REJECT LIMIT UNLIMITED;

-- In Oracle, by default, need to explicitly commit a transaction
COMMIT;

Ideally, a bulk load to a table will have indexes already dropped.

There is also a bulk load feature in PostgreSQL.

-- PostgreSQL
INSERT INTO products (product_no, name, price)
 SELECT product_no, name, price
 FROM new_products
 WHERE release_date = `today`;

Also to prevent incremental WAL logging while loading, disable archiving and streaming replication, by setting wal_level to minimal, archive_mode to off, and max_wal_senders to zero. But note that changing these settings requires a server restart, and makes any base backups taken before unavailable for archive recovery and standby server, which may lead
to data loss.

Data archiving and migrating

To migrate data to another PostgreSQL database, use a logical backup tool: pg_dumpall.

-- PostgreSQL
-- data dump
pg_dumpall > outputfile

An archive file created from pg_dump can be restored with pg_restore.

pg_restore -d dbname filename

Transactions

In PostgreSQL, by default (without BEGIN), PostgreSQL executes transactions in “autocommit” mode.
Note: also START TRANSACTION has the same functionality as BEGIN.

With autocommit enabled, commits can be postponed by explicitly issuing BEGIN at the start and COMMIT at the end.

-- PostgreSQL
-- In PostgreSQL, postpone a commit
BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;

If you prefer autocommit-off, you might wish to set it in the system-wide psqlrc file or your ~/.psqlrc file.

-- For the current session
SET AUTOCOMMIT=OFF

For Oracle, a statement does not autocommit by default, and it is better to explicitly commit at the boudary of the transaction.

-- Oracle
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
-- explicitly commit, as Oracle has no autocommit by default
COMMIT;

-- Oracle can name transactions, and also set the transaction isolation level
SET TRANSACTION READ ONLY NAME 'Toronto';
SELECT product_id, quantity_on_hand FROM inventories WHERE warehouse_id = 5; 

COMMIT;

In Sqlplus, it is possible to manually enable auto commit for the session. However on exit of Sqlplus, there will be an auto commit whether the session was enabled autocommit, or not.

Concurrency

PostgreSQL has Multiversion Concurrency Control (MVCC) which provides transaction isolation.

In PostgreSQL, reading never blocks writing and writing never blocks reading. (Same behaviour as Oracle.)

PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete.

SQL standard transaction isolation levels

In PostgreSQL, the Read Committed mode is the default isolation level in PostgreSQL.

PostgreSQL’s Read Uncommitted mode behaves like Read Committed.

Oracle Database offers the Read Committed (default) and serializable isolation levels. Also, the database offers a read-only mode.

Routine maintenance for PostgreSQL

Oracle is a largely maintenance free database.

However, PostgreSQL does have some maintenance requirements that the Oracle database doesn’t have.

Vacuuming old rows

For Multiversion concurrency control (MVCC) relies on the old rows existing in the table.

But to cleanup the old rows, periodic vacuuming is required.

(Note: vacuuming is performed by the autovacuum daemon, but can be manually performed.)

The VACUUM command recovers row space for reuse, and update data statistics, plus other tasks.

VACUUM will recover those rows applicable for reuse.

-- PostgreSQL
-- VACUUM - garbage-collect and optionally analyze a database
VACUUM ANALYZE

For a progress report of VACUUM use the pg_stat_progress_vacuum view.

VACUUM FULL

Progress for VACUUM FULL commands is reported via pg_stat_progress_cluster view.

Also TRUNCATE reclaims disk space immediately, rather than requiring a subsequent VACUUM operation.

-- PostgreSQL
TRUNCATE bigtable;

transaction ID number management

Transaction IDs have limited size (32 bits).

Note: 32 bits represents at most 4,294,967,296 states, or signed integers -2,147,483,648 to 2,147,483,647.

A cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound. The result is data corruption.

To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions.
Note: the system will shut down and refuse to start any new transactions once there are fewer than three million transactions left until wraparound.

log file management (ie error log)
Setting the configuration parameter logging_collector to true in postgresql.conf.

Collecting performance statistics

The PostgreSQL query planner relies on statistical information about the contents of tables in order to generate good plans for queries. These statistics are gathered by the ANALYZE command.

The autovacuum daemon, if enabled, will automatically issue ANALYZE commands.

Use the pg_stat_progress_analyze view to report on the status of the ANALYZE command.

In PostgreSQL, ANALYZE collects statistics about the contents of tables in the current database, and stores the results in the pg_statistic system catalog.

Note: pg_statistic is only readable by a superuser.

-- PostgreSQL
-- collect statistics
-- SKIP_LOCKED = dont not wait for any conflicting locks to be released
ANALYZE VERBOSE SKIP_LOCKED

Can also specify a table name and column to analyse.

-- PostgreSQL
-- collect statistics
ANALYZE VERBOSE SKIP_LOCKED table_name column_name

Run the ANALYZE command regularly to update statistics.

Viewing performance statistics

Entries in pg_statistic (and pg_statistic) are updated by the ANALYZE and VACUUM ANALYZE commands.

-- PostgreSQL
-- for general public access, instead of the pg_statistic catalog
SELECT attname, inherited, n_distinct,
 array_to_string(most_common_vals, E'\n') as most_common_vals
FROM pg_stats
WHERE tablename = 'road';

-- also
SELECT histogram_bounds 
FROM pg_stats
WHERE tablename='tenk1' AND attname='unique1';

View statistics on the table columns.

-- PostgreSQL
SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'tenk1%';

Viewing execution plans

Explain an execution plan in PostgreSQL as follows:

-- PostgreSQL
EXPLAIN SELECT count(*) 
FROM measurement 
WHERE logdate >= DATE '2008–01–01';

EXPLAIN SELECT * FROM tenk1;

Explain plan and execute also.

-- PostgreSQL
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

Explain and execute and also rollback.

-- PostgreSQL
BEGIN;
EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;
ROLLBACK;

General performance monitoring

State of a process, and the current query can be determined with the pg_stat_activity view.

-- PostgreSQL
-- check for long running open transactions
SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer)
 || '.' ||
 to_hex(pid)
FROM pg_stat_activity;

-- view wait events
SELECT pid, wait_event_type, wait_event 
FROM pg_stat_activity 
WHERE wait_event is NOT NULL;

Logical backup and restore

For PostgreSQL, backup and migration as follows.

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.

# PostgreSQL
# create a file of SQL commands to restore a database
# ie a logical backup of a single database
pg_dump dbname > dumpfile

# restore the database using the file of SQL commands
psql dbname < dumpfile

Note: pg_dump dumps only a single database at a time, and it does not dump information about roles or tablespaces (because those are cluster-wide rather than per-database).

If a blank database needs to be created first, before running the SQL commands, then use:

# PostgreSQL
createdb -T template0 dbname

Note: need to recreate users before importing.
Note: can also backup large databases by piping to gzip and split.

Note: also do an ANALYZE after the import.

Use pg_dumpall to dump a whole cluster.

# PostgreSQL
# dump a whole cluster
pg_dumpall > dumpfile

# restore a whole cluster
psql -f dumpfile postgres

Physical backup and restore

There are number of options to perform a PostgreSQL physical backup and restore, and they include:

File system backup: but the whole cluster must be shutdown, or use a storage snapshot method that includes the WAL files so rollforward is possible.
But could also use an inconsistent file system backup and the WAL files to restore the whole cluster.
Also setup WAL archiving in the postgresql.conf file.

Base Backup: pg_basebackup can perform a backup but then you also need all the WAL files generated during the backup.

pg_basebackup makes an exact copy of the database cluster’s files, while making sure the server is put into and out of backup mode automatically.
Note: At the beginning of the backup, a checkpoint needs to be performed on the source server.

# PostgreSQL
# create a base backup
pg_basebackup -h mydbserver -D /usr/local/pgsql/data

# verify the base backup
pg_verifybackup /usr/local/pgsql/data

Standalone Hot Backup: use the pg_basebackup tool, and include the -X parameter to include all the WAL information required to restore the backup. This type of backup can’t be used for point-in-time recovery.

PL/SQL and PL/pgSQL

Oracle has functions, procedures, and packages. Packages are used to group functions and procedures. Also packages can use package-level variables to store session state.

-- Oracle PL/SQL
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(
  v_name varchar2,
  v_version varchar2)
RETURN varchar2 IS
BEGIN
  IF v_version IS NULL THEN
  RETURN v_name;
  END IF;
  RETURN v_name || '/' || v_version;
END;
/

show errors;

PostgreSQL has functions and procedures, but no packages. Instead of packages, use schemas to organise functions and procedures. Store session state in temporary tables.

-- PostgreSQL PL/pgSQL
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(
  v_name varchar,
  v_version varchar)
RETURNS varchar AS $$
BEGIN
  IF v_version IS NULL THEN
  RETURN v_name;
  END IF;
  RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;

Dynamic cursors

Oracle database dynamic cursors is as below:

-- Oracle dyamic cursor
EXECUTE IMMEDIATE 'SELECT count(*) FROM mytable WHERE inserted_by = :x AND inserted <= :y'
INTO rec 
USING checked_user, checked_date;

Also Oracle database has the DBMS_SQL package, where the EXECUTE IMMEDIATE syntax can’t be used.

The Oracle database dynamic cursors is very similar to the PostgreSQL has dynamic cursors.

-- PostgreSQL dynamic cursor
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
INTO rec
USING checked_user, checked_date;

This guide showed that there are many similarities between PostgreSQL and Oracle, and also some key differences.

Paul Guerin has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. Since 2015, his work has been featured in the IOUG Best Practices Tip Booklet, and in publications from AUSOUG, Oracle Technology Network, Quest, and Oracle Developers (Medium). He was awarded as a most valued contributor for the My Oracle Support Community (2019), and continues to be a participant of the Oracle ACE program.