PostgreSQL 14 vs Oracle – system partitioning

Yesterday there was a discussion on the use of system partitioning and
what is the case in PostgreSQL 14. I like the constructive discussion because:

  • You can always learn something new from more experienced people
  • You pre-verify your opinions and analyze them
  • The flow of ideas allow for development

Initially, it seemed that this is not possible in the core version of PostgreSQL 14. However, after a few tests / trials, it turned out that there is a light in the tunnel ….

What is system partitioning actually?
Briefly (with examples!) it is described in the note ID 785462.1
System partitioning preserves the administrative benefits of partitioning unfortunately along with the lack of performance advantages for tables where you can’t sensible to define a partitioning scheme.
The aforementioned note gives us the following properties of system partitioning:

  • Provides the ability to create a single table that has many physical partitions
  • No partition key is defined
  • You define a specific number of partitions
  • You must define which partition the data goes in when doing an insert of data
  • Delete and update SQL does not require any special syntax

There are 2 types of partitioning in PostgreSQL – table inheritance and declarative partitioning. Let’s see how it works for declarative partitioning.

Attempt 1 – Let’s try not to define partitioning on the root table

CREATE TABLE p_measurement_system (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

CREATE TABLE p_measurement_system_p1 PARTITION OF p_measurement_system DEFAULT;
-- ERROR:  "p_measurement_system" is not partitioned

This was to be expected.

Attempt 2 – let’s do a wide range of partitioning

CREATE TABLE p_measurement_system (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE p_measurement_system_p1 PARTITION OF p_measurement_system DEFAULT;
CREATE TABLE p_measurement_system_p2 PARTITION OF p_measurement_system FOR VALUES FROM ('1900-01-01') TO ('2999-01-01');

insert into p_measurement_system_p2 values (1,'2021-01-03',1,1);

CREATE TABLE p_measurement_system_p3 PARTITION OF p_measurement_system FOR VALUES FROM ('1901-01-01') TO ('2999-01-01');
ERROR:  partition "p_measurement_system_p3" would overlap partition "p_measurement_system_p2"
LINE 1: ...ARTITION OF p_measurement_system FOR VALUES FROM ('1901-01-0...

Ok, boundaries are verified. In fact, it limits us to one partition in the table (a total of 2 – one for values ​​and one default for out of range values). No advantage of partitioning and it is not compliant with point 3 from system partitions list. The conclusion is that system partitioning for declarative partitioning cannot be done.

What does this look like for table inheritance?

Here we can try to take advantage of the fact that we are fully responsible for guarding the boundaries between partitions.

CREATE TABLE p_measurement_system (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

CREATE TABLE p_measurement_system_p1 (
     CHECK ( 1 = 1 )
) INHERITS (p_measurement_system);
CREATE TABLE p_measurement_system_p2 (
     CHECK ( 1 = 1 )
) INHERITS (p_measurement_system);

insert into p_measurement_system_p1 values (1,'2021-01-03',1,1);
insert into p_measurement_system_p2 values (2,'2021-01-04',2,2);

select count(*) from p_measurement_system;
-- Returns 2 rows

As in Oracle, we have to manually specify partition we would like to insert data.
Ok. Is there any change in DELETE / UPDATE syntax? No.

testdb=# delete from p_measurement_system_p1;
DELETE 1
testdb=# delete from p_measurement_system where city_id = 1;
DELETE 0

We don’t have to change syntax for DML (of course excluding INSERTs).
There is one difference between the Oracle implementation and the one presented here.
For table inheritance PostgreSQL planner always has to include possibility of data existence in root table.

explain delete from p_measurement_system where city_id = 1;

                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Delete on p_measurement_system  (cost=0.00..66.34 rows=0 width=0)
   Delete on p_measurement_system p_measurement_system_1
   Delete on p_measurement_system_p1 p_measurement_system_2
   Delete on p_measurement_system_p2 p_measurement_system_3
   ->  Append  (cost=0.00..66.34 rows=19 width=10)
         ->  Seq Scan on p_measurement_system p_measurement_system_1  (cost=0.00..0.00 rows=1 width=10)
               Filter: (city_id = 1)
         ->  Seq Scan on p_measurement_system_p1 p_measurement_system_2  (cost=0.00..33.12 rows=9 width=10)
               Filter: (city_id = 1)
         ->  Seq Scan on p_measurement_system_p2 p_measurement_system_3  (cost=0.00..33.12 rows=9 width=10)
               Filter: (city_id = 1)

In Oracle when we try insert to table with system partitioning we have below.

insert into sales values (1,100,2)
SQL Error: ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
14701. 00000 -  "partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method"
*Cause:    User attempted not to use partition-extended syntax
           for a table partitioned by the System method
*Action:   Must use of partition-extended syntax in contexts mentioned above.

In PostgreSQL it is allowed

testdb=# insert into p_measurement_system values (3,'2019-01-04',3,1);
INSERT 0 1

If we want to have consistent behavior among both database engines we could use insert trigger.

create or replace function no_insert_p_measurement_system() returns trigger language plpgsql as $$
begin
  RAISE 'Cannot insert data directly into p_measurement_system table';
  return null;
end $$;

create or replace trigger inserting before insert on p_measurement_system for each row execute procedure no_insert_p_measurement_system();

Let’s try to insert data there:

testdb=# select count(*) from p_measurement_system;
 count
-------
     2
(1 row)

testdb=# insert into p_measurement_system values (3,'2011-01-03',3,3);
ERROR:  Cannot insert data directly into p_measurement_system table
CONTEXT:  PL/pgSQL function no_insert_p_measurement_system() line 3 at RAISE
testdb=# select count(*) from p_measurement_system;
 count
-------
     2
(1 row)

We have workaround which is compliant with all system partitioning requirements. Oracle implementation is consistent, PostgreSQL more stapled, but looks good.
Yes, a lot of additional administration overhead associated with table inheritance, but because we don’t have routing trigger for each inserted row, performance penalty should not be significant.

PostgreSQL 14 vs Oracle – Partitioning (part I)

Partitioning – very often used feature of databases – could have a lot of features.
How many? Well check Oracle Partitioning feature timeline here and PostgreSQL equivalent here.
Yes, PostgreSQL for some features seems to be behind, but you have it for free.
Below is table with comparison of basic partition features.

Partitioning’s featureOraclePostgreSQL 14
Transparent for applicationenable row movementInherited – no, manual DELETE + INSERT needed Declarative – already enabled
Partition Pruning/EliminationEmbeddedEnabled by default. Could be disabled globally.
Convert table to partitioned tableOnline reorganization EXCHANGE TABLENot applicable / pg_pathman ?
Export / Backup / Recover – partitionsRMAN could only recover.
and EXPDP transport by partition
First detach partition to be standard table.
Local partitioned indexRequires LOCAL keywordDefault for all partitioned indexes
Global IndexesRequires GLOBAL keywordNot possible
Inherited partitioning implemented by create ruleN/A. Equivalent of CREATE RULE from PostgreSQL in Oracle is instead of trigger for DELETE, UPDATE, INSERT and using views for SELECTCREATE RULE combines both.
Range, List, HashSupportedSupported
Composite PartitioningSupportedSupported
Oracle vs PostgreSQL partitioning – part I

Partitioning could be used to implement Information Lifecycle Management to move(or drop) old partitions. In Oracle this could be achieved by static style (define retention policies) or dynamic style (using Automatic Data Optimization). Of course in middle – in case of Oracle – we will use EXCHANGE PARTITION operation to detach from new table and put into old table. This operation is online. In PostgreSQL there is no exchange partition operation. So how to detach partition ?

testdb=# ALTER TABLE p_measurement DETACH PARTITION p_measurement_y2021m01 CONCURRENTLY;
ERROR:  cannot detach partitions concurrently when a default partition exists

PostgreSQL supports similar construct like MAXVALUE to provide out-of-range data. Just in case. In PostgreSQL it could be done by marking partition as DEFAULT

CREATE TABLE p_measurement_oor PARTITION OF p_measurement
    DEFAULT;

DETACH PARTITION operation has 2 modes – standard and concurrently. Concurrently mode avoids EXCLUSIVE ACCESS lock on partitioned table. In busy environments it’s better avoiding defining DEFAULT partition and handle out-of-data range data differently.

Update 2022/07/04: Composite partitioning is possible in PostgreSQL too.

MariaDB – Install with Ansible playbook

MariaDB is open-source database forked from MySQL several years ago. 
It claims to maintain high combability with MySQL, witch library binary parity and exact matching withy MySQL APIs (Community Edition). Unfortunately, we cannot rely on this. Newer versions have too many incompatible features and this trend is growing forced by Oracle who is releasing MySQL brands independently. Apart from the early versions, MariaDB should be treated as an independent engine. 
It took my several hours to find exact steps to install it correctly on Oracle Linux 7, but finally Ansible playbook is ready. Oracle Linux 7 handles EPEL a bit differently and we have to run dedicated script to configure this repo. mysql_user module could be used to manage user in MariaDB.

Enjoy ! (yes password should encrypted using ansible-vault string command, but I leave it for interested as exercise).

--- 
- name: MariaDB 
  hosts: "{{ nodes }}" 
  gather_facts: true 
  become: true 
  vars: 
    mysql_root_password: "Password1" 
  tasks: 
    - name: Install EPEL 
      yum: 
        name: oracle-epel-release-el7 
        state: present 
      become: true 
    - name: Confgigure EPEL repo 
      ansible.builtin.shell: /usr/bin/ol_yum_configure.sh 
    - name: remove the mysql-community-libs package 
      yum: 
        name: mysql-community-libs 
        state: absent 
    - name: remove the mysql-community-common 
      yum: 
        name: mysql-community-common 
        state: absent 
    - name: install mariadb 
      yum: 
        name: 
          - mariadb-server 
        state: latest 
    - name: install python-setuptools 
      yum: 
        name: 
          - python-setuptools 
        state: latest 
    - name: install pip 
      yum: 
        name: 
          - python3-pip 
        state: latest 
    - name: install python3-PyMySQL 
      yum: 
        name: 
          - python3-PyMySQL 
        state: present 
    - name: install python-PyMySQL 
      yum: 
        name: 
          - python-PyMySQL 
        state: present 
    - name: start mariadb 
      service: 
        name: mariadb 
        enabled: true 
        state: started 
    - name: mysql_root_password 
      mysql_user: 
        login_user: root 
        login_password: "{{ mysql_root_password }}" 
        user: root 
        check_implicit_admin: true 
        password: "{{ mysql_root_password }}" 
        host: localhost 
    - name: remove remote root 
      mysql_user: 
        check_implicit_admin: true 
        login_user: root 
        login_password: "{{ mysql_root_password }}" 
        user: root 
        host: "{{ ansible_fqdn }}" 
        state: absent 

PostgreSQL 14 – Migrate from Oracle Linux 7 to 8

Today I’ve migrated 2 database clusters from Oracle Linux 7 to Oracle Linux 8 using backup/restore approach. First one has default PostgreSQL tablespaces and second (“mycluster”) has 2 user-defined tablespaces. Yes, if you are coming with Oracle ecosystem background you thread tablespaces very seriously as a lot of features associated with tablespaces could be used in multiple use cases. In PostgreSQL tablespace is not important database entity (but they could be useful if you have enable TDE encryption only for some database in cluster). More here

Worth of mentioning is tablespaces usage in backup/recovery migration approach. User-defined tablespaces are backed separately and not stored in “base” directory.

Their existence adds one step more for recovery process:

  • Uncompress tablespace-related archive to separated directory (as best practice don’t keep them in base directory)
  • Update tablespace_map file in uncompressed base folder for pointing to new locations

This step could be quite easily automated too using Ansible playbook, role or other automation software you are using. After restoring tablespace my tablespace_map file looks like below:

It worth to mentioned that “core” PostgreSQL doesn’t have something like backup repository included (like in Oracle controlfile or RMAN Catalog). Good news it is produced by pg_basebackup tool. For “mycluster” cluster it looks like below.

One important note you have to remember are extensions. If you have them on source and would like to have them on target – install them before restore (as most of them required installation on operating system level or PostgreSQL binaries) as in my case:

I’ve installed postgresql14-contrib package with yum and this helped me pg_stat_statements extension work correctly.

As we see existence of user tablespaces in PostgreSQL makes backup/recovery migration approach a bit more complicated, but this is not hard blocker or something which couldn’t be easily automated.

Postgres 14 – SQL tuning with Hashing and Sorting

Inspired by Connor McDonald blog entry and because I am evaluating/learning advanced SQL topic in PostgreSQL I’ve decided to check how group aggregates (sorts using required!) and hash aggregated behaves in PostgreSQL 14.2. In summary Connor used below features/observations/notes around Oracle world:

  • SORT GROUP BY operation in plan for producing aggregated data
  • HASH GROUP BY operation in plan for producing aggregated data for majority of cases faster then above operation
  • Query subfactoring with materialized hint – as solution for improving performance for initial problem

Ok. How it works with PostgreSQL 14 ? Maybe first I’ll put some output number and results from exercise.

TestAggregation method from execution planExecution Time [ms]Notes
AverageMixedAggregate = Hash Aggreagate5919
AverageGroupAggregate 11190enable_hashagg=off
Median by using percentile_contGroupAggregate19032
Median by using custom aggregate (function)HashAggregate74944
Median by using percentile_cont – using subquery factoringHashAggregate + GroupAggregate21961
Median by using percentile_cont – using recursive subquery factoring – version 1Materialized HashAggregate + GroupAggregate23706
Median by using percentile_cont – using recursive subquery factoring – version 2Materialized GroupAggregate + HashAggregate19863
Median by using custom aggregate (function) – using recursive subquery factoring – version 1Materialized HashAggregate + GroupAggregate92456
Median by using custom aggregate (function) – using recursive subquery factoring – version 2Materialized GroupAggregate + HashAggregate85620
Median by using custom aggregate (function) – using subquery factoringHashAggregate + HashAggregate 81861

Source table used for tests had around 21 millions of rows. Created from oil data:

COPY t_oil FROM PROGRAM 'curl https://www.cybertec-postgresql.com/secret/oil_ext.txt';

-- Executed multiple times
insert into t_oil select * from t_oil;

Query used for checking average function.

explain (analyze, buffers) select region, country, avg(production)
    from t_oil
    where country in ('USA','Canada','Iran','Oman')
    group by rollup (region, country);

Median by using percentile_cont function

explain (analyze, buffers) select region, country, avg(production), percentile_cont(0.5) WITHIN GROUP (ORDER BY production)
    from t_oil
    where country in ('USA','Canada','Iran','Oman')
    group by rollup (region, country);

Custom aggregate function

CREATE OR REPLACE FUNCTION _final_median(numeric[])
   RETURNS numeric AS
$$
   SELECT AVG(val)
   FROM (
     SELECT val
     FROM unnest($1) val
     ORDER BY 1
     LIMIT  2 - MOD(array_upper($1, 1), 2)
     OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
   ) sub;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE AGGREGATE median(numeric) (
  SFUNC=array_append,
  STYPE=numeric[],
  FINALFUNC=_final_median,
  INITCOND='{}'
);

explain (analyze, buffers) select region, country, avg(production), median(production)
    from t_oil
    where country in ('USA','Canada','Iran','Oman')
    group by rollup (region, country);

Query recursive subfactoring used – version 1 (for versions for median by custom aggregate and without recursive – change function and remove RECURSIVE keyword respectively).

explain (analyze, buffers)
with RECURSIVE t1 as
( 
  select region, country, percentile_cont(0.5) WITHIN GROUP (ORDER BY production)
    from t_oil
    where country in ('USA','Canada','Iran','Oman')
    group by rollup (region, country)
),
t2 as
( 
  select region, country, avg(production)
  from t_oil
  where country in ('USA','Canada','Iran','Oman')
  group by rollup (region, country)
)
select *
from t1, t2
where t1.region = t2.region
and   t1.country = t2.country;

Query recursive subfactoring used – version 2

explain (analyze, buffers)
with RECURSIVE t1 as
( 
 select region, country, avg(production)
  from t_oil
  where country in ('USA','Canada','Iran','Oman')
  group by rollup (region, country)
),
t2 as
( 
    select region, country, percentile_cont(0.5) WITHIN GROUP (ORDER BY production)
    from t_oil
    where country in ('USA','Canada','Iran','Oman')
    group by rollup (region, country)
)
select *
from t1, t2
where t1.region = t2.region
and   t1.country = t2.country;

Summary

  • Median functions in PostgreSQL have multiple (and different) implementation possible. Check here and here. I’ve not checked version from pgnx as I couldn’t install pgnx client in my Oracle Linux 8 environment. No version for Postgres 14 and problem with installation from source. Different aggregation methods used.
  • PostgreSQL doesn’t have something like hints in Oracle, but some equivalent could be found in parameters and/or syntax. EnterpriseDB Advanced Server offers some Oracle-like hints, but I have not checked them yet.
  • Subquery factoring is possible in PostgreSQL. Equivalent for materialized hint from Oracle is WITH RECURSIVE syntax.
  • I don’t manage to get better execution time when I’ve split query into 2 subqueries with subfactoring (work_mem in my cluster for testing was quite big – 8 GB – so sortin in memory/sorting in disks problem was eliminiated).
  • I didn’t find a way to enforce using recursive (materialized) subfactoring for each view. I’m not sure if it is possible in PostgreSQL or is it something which is totally under optimizer control. So subquery solution in PostgreSQL is not 100% equivalent for Connor’s solution in Oracle. I suspect result could be better if both subqueries could be “materialized”. Funny this is even Materialize string appears in execution plan and most articles in Web have something like “Oracle database has materialized subquery factory enabled, is it possible in PostgreSQL?”. Btw. PostgreSQL using ANSI naming for this -> Common Table Expression (CET).
  • Order of subqueries had influence on execution plan. Better to materialized GroupAggregation than HashAggregation.
  • PostgreSQL offers similar capabilities for calculation aggregates as in Oracle

PostgreSQL – pgBackRest like Oracle RMAN

Time for next “classic adventure” in PostgreSQL 🙂 . PostgreSQL has plenty of extensions and tools available. You could very fit minimal database server or packed version ready for handle enterprise. Classic tool for physical backups is pg_basebackup. Unfortunately it doesn’t provide RMAN catalog like capabilities (reporting, manage retention period, backup/restore based on tags). Good news is that we have 2 serious tools for helping with backup/restore area:

In this post I would quickly summarize my observation about pgBackRest.

Advantages

  • Full / Incremental / Differential backups
  • Encryption / Compression
  • Standby database backup offload possibility
  • Multiple configuration options
  • Central repository for backups in enterprise
  • Possibility of restore single database (but only one, other internal databases remains inactive)
  • Not required reset incarnation if restore from previous backups (be careful ! you don’t have any stopper to restore from previous incarnation !
  • supported by commercial vendors

Disadvantages

  • pgBackRest backups entire cluster only
  • No information about actual backup size on disk
  • pgBackRest process has to be restarted after configuration changes
  • No tablespace level backup (but it is not possible with classic methods too ; tablespace is not so important abstract like in other RDBMs)
  • Entire cluster or only single database restore. No solution for cases between, like: restore 2 databases out of 5

Important Note ! If you would like to install this software in RHEL 8 / Oracle Linux please choose version from CrunchyData. I’ve tried install directly from source or from PostgreSQL Yum repo, but only version from CruncyData works. In next episode I will focus on barman.

PostgreSQL 14 – can Slony-I replication be installed ?

PostgreSQL historically have a plenty of logical replication systems. Including Logical Replication into core since 2017 simplified configuration for basic cases and improved performance as it is WAL-based. Recently I’ve received some question if it is possible to use Slony replication system which is already deployed in environment and there is no migration planned quickly for new replication system. Slony-I replication is trigger based replication and lives in PostgreSQL ecosystem since 2007. Excellent summary could be found here.

Last Slony-I version – 2.2.10 – seems to be quite old ; few years back. Exercise to perform was checking if we could install Slony-I in Postgres 14 and perform shift migration or plan for logical replication mechanism before.
Installation was performed from source.

[root@postgres14p ~]# cd /var/lib/pgsql/tools/slony/slony1-engine

[root@postgres14p slony1-engine]# dnf install autoconf

[root@postgres14p slony1-engine] autconf # required to build configure - prereqs for makefile

./configure --prefix=/usr --with-pgconfigdir=/usr/pgsql-14/bin

    configure: WARNING:

    *** Without Flex you will not be able to build Slony-I from CVS nor

    *** change any of the scanner definition files.  You can obtain Flex from-

    *** a GNU mirror site.  (If you are using the official distribution of

    *** Slony-I then you do not need to worry about this because the Flex

    checking if you have requested documentation building... no

    configure: creating ./config.status

    config.status: creating Makefile.global

    config.status: creating GNUmakefile

    config.status: creating slony1.spec

    config.status: creating Makefile.port

    config.status: creating config.h*** output is pre-generated.)

Additional packages have to be installed – flex & byacc.

dnf -y install flex

dnf -y install byacc

./configure --prefix=/usr --with-pgconfigdir=/usr/pgsql-14/bin

make install

All seems to be installed successfully.

    make[2]: Entering directory '/var/lib/pgsql/tools/slony/slony1-engine/tools/altperl'

    /bin/sh /var/lib/pgsql/tools/slony/slony1-engine/config/mkinstalldirs /etc

    /bin/sh /var/lib/pgsql/tools/slony/slony1-engine/config/mkinstalldirs /usr/pgsql-14/lib/

    /bin/sh /var/lib/pgsql/tools/slony/slony1-engine/config/mkinstalldirs

    The altperl tools won't be installed unless --with-perltools is specified in configure

    make[2]: Leaving directory '/var/lib/pgsql/tools/slony/slony1-engine/tools/altperl'

    make[1]: Leaving directory '/var/lib/pgsql/tools/slony/slony1-engine/tools'

    All of Slony-I is successfully installed

There is excellent admin guide with prepared step-by-step how to replicate pgbench data. Of course some points are not valid for version 14 (for example using createlang tool for create procedural language in database cluster), but documentation is full of well prepared example together with scripts ready to use. Final result was positive – Yes. Slony-I could be installed in Postgres-14 and logical replication using it should be fine there.

PostgreSQL vs Oracle Multitenant – read only databases / PDBs

PostgreSQL database using term “database cluster” to name multiple databases packed into single engine. Oracle has the same architecture – multitenant. Today we are going to check how read only approach works in PostgreSQL. In Oracle we know that:

  • It is possible to have each PDB in read-write / read-only mode independently
  • In version 12.1 trigger for database event was required to enforce settings during startup, since 12.2 declared configuration doesn’t require additional modified startup mechanism to enforce it

Let’s check how it works for PostgreSQL. My version (database na os) are 14.2 and 8.5.

[postgres@postgres14p ~]$ psql --version
psql (PostgreSQL) 14.2
[postgres@postgres14p ~]$ cat /etc/oracle-release
Oracle Linux Server release 8.5

All databases in cluster:
postgres=# select datname from pg_database;

qwqwqw
template0
template1
testdb
abab
newdb
postgres
(7 rows)

Is it possible to check if databases are read-write / read-only ? No, In PostgreSQL all databases in cluster are read-write databases and there is no mechanism in core to make them read only.
After quick checking there seems to be extension to provide this functionality.

[root@postgres14p ~]# dnf search postgresql* | grep 14 | grep cluster

Last metadata expiration check: 1:26:21 ago on Wed 09 Mar 2022 08:04:04 AM UTC.

pg_readonly_14.x86_64 : PostgreSQL extension which allows to set all cluster databases read only.

[root@postgres14p ~]# dnf install pg_readonly_14

Of course, we have to restart cluster, simple reload is not enough and when trying to install this extension engine gives us a catch:

postgres=#  create extension pg_readonly;

server closed the connection unexpectedly

        This probably means the server terminated abnormally

        before or while processing the request.

The connection to the server was lost. Attempting reset: Failed.

!?>

After successfully installation and enable in one of database – abab we enable read-only mode.

abab=# select get_cluster_readonly();

 get_cluster_readonly

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

 f

(1 row)

postgres=# select get_cluster_readonly();

 get_cluster_readonly

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

 f

(1 row)

After successfully installation let’s try insert something:

postgres=# select * from tab5;

 id

---

  2

(1 row)

postgres=# insert into tab5 values (3);

ERROR:  pg_readonly: pgro_main: invalid statement because cluster is read-only

postgres=# select set_cluster_readonly();

 set_cluster_readonly

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

 t

(1 row)

What about databases where this extensions was not installed ?

abab=# \c testdb

You are now connected to database "testdb" as user "postgres".

testdb=# create table test (id integer);

ERROR:  pg_readonly: pgro_main: invalid statement because cluster is read-only
Read-only feature seems to be for entire cluster, even not enabled for other databases.
Is read-only setting is permanent ? No, it is not.

[postgres@postgres14p ~]$ pg_ctl restart

waiting for server to shut down…. done

server stopped

waiting for server to start….2022-03-09 10:08:19.593 UTC [77573] LOG:  pg_readonly:_PG_init(): pg_readonly extension is enabled

2022-03-09 10:08:19.601 UTC [77573] LOG:  redirecting log output to logging collector process

2022-03-09 10:08:19.601 UTC [77573] HINT:  Future log output will appear in directory “log”.

 done

server started

psql

postgres=# \c testdb

testdb=# create table test (id integer);

CREATE TABLE

Is it possible to use Oracle 12.1 trick with database event trigger ? No, PostgreSQL doesn’t have triggers for these events. More here. Solutions seems to be custom start/stop scripts and modifying system-D daemon for start PostgreSQL to fire putting cluster in read-only mode.
I agree that in majority case we want entire cluster either read-write or read-only mode. We could mitigate this problem with create separated database cluster just for read-only workload, but we paying with additional resources and we are loosing one of the benefit of multitenant architecture – consolidation and reducing resource footprint.

PostgreSQL 14 – WAL-based replication across minor versions

PostreSQL has multiple options for building replication. Among them two seems to be similar to
Oracle Data Guard offer:

  • WAL Log-shipping – applying entire WAL (similar to Oracle redo logs/ archived redo logs), but not managed – database in read-only mode.
  • Streaming replication – it is similar to Oracle Real-Time Apply when changes from primary server are applied as soon as possible without waiting for WAL/standby redo logs switching. Interesting fact is that WAL files always have 16MB size and it is not dependent of number of changes made to database. It is different behavior comparing Oracle where size vary. Of course we talking about manually switching or frequent switches enforced to minimize recovery point objective for idle time in database. In PostgreSQL this parameter is archive_timeout, in Oracle it is archive_lag_target. If we set it to quite low value we have to remember to secure additional space for WAL files.

Interesting is method of preventing of WAL files deletion on primary database server. In Oracle this is achieved by archived redo log deletion policy specified in RMAN configuration. In Postgres there is no centralized place for all backup/recovery process management. For protection of removing WAL files we are using replication slots.

I’ve checked if we could use WAL based replication. I have primary server in 14.1 version and standby server in 14.2 version. I’ve managed successfully build streaming application among them.
Streaming works correctly:

2022-02-25 09:13:33.135 UTC [1557] LOG: started streaming WAL from primary at 4/70000000 on timeline 1

Unfortunately it is not officially supported in PostgreSQL (but documentation says it should work). In Oracle we could the same behavior (for example when using Standby-first patching approach). Supported for 31 days, but in practice I saw cases where we have replication between different patch levels for more than 60 days.

I’ve spent several hours for troubleshooting why my streaming replication doesn’t work. I’ve discovered PostreSQL uses 2 configuration files: postgres.conf and postgres.auto.conf. First is Oracle pfile and second spfile. I had strange values for primary_conninfo and after correction all started to work.

PostgreSQL 14.1 – pgbadger and performance statistics

One topic I’m working now currently, it checking PostresSQL (both Iaas and Paas options) statistics collections capability and what performance metrics we could derive from it.
First part – Iaas option.
PostgresSQL came up with separated process and component for collecting statistics information – Statistics Collector. I’m positively surprised it has very modular structure – you could a lot of option to control what could be logged. Two major disadvantages comparing with Oracle are:

  • Statistics are not update instantaneously. Active processes flush statistics info only when going to idle state or exiting (except for our session)
  • No repository for historical statistics = performance related catalog views are only dynamic and automatically flushed to central repository

This could be somehow mitigated by using external open-source extensions like pg_profile, pg_stat_wait, pganalyze. However most promising seemed to be pgbadger. PostgreSQL could be configured to log a lot messages into server log file. Below is the configuration set I’ve ended up for it

log_min_duration_statement = 0 # log every statement
log_statement='none' # strange, but it works. With 'all' value it doesn't !
log_checkpoints = on
log_connections = on
log_disconnections=on
log_lock_waits=on
log_temp_files=on
log_autovacuum_min_duration=0
log_error_verbosity=default

I’ve realized that it is necessary as majority of them are not logged in catalog views.
After running pgbench and generating report using pgbadger I managed to see statistics.

Ok, what is performance cost of statistics taken from server log.
Comparison for workload for 15 clients, 3 threads and 10k transactions per client gave me response.
Test on local virtual environment.

Cost was 10% (from 989 transactions/s dropped to 886 transactions/s). It is quite big cost.
However I’ve logged every SQLs. I have to find a way to avoid log internal sqls …