🥇'About, but not cluster' or how we implemented national alternatives for DBMS

All characters are fictional, trademarks belong to their respective owners, any coincidences are random, and in general, this is my 'subjective assessment, please do not break the door…'.

We have significant experience in translating information systems with logic in databases from one DBMS to another. In light of the Government Resolution No. 1236 dated 16.11.2016, this often involves migration from Oracle to PostgreSQL. How to organize the process as efficiently and painlessly as possible—this is something we can discuss separately; today, we will talk about the peculiarities of using clusters and what challenges you may face when building heavily loaded distributed systems with complex logic in procedures and functions.

Spoiler alert – yes, CAPP, RAC, and pg multimaster are very different solutions.

Suppose you've already migrated all the logic from PL/SQL to PG/SQL. And your regression tests are just fine; now you're certainly thinking about scaling since the load tests are not very promising, especially on that hardware originally laid out for the project under the other DBMS. Suppose you found a solution from the domestic vendor 'Postgres Professional' with an option called 'multimaster,' available only in the 'maximum' version of 'Postgres Pro Enterprise,' which, by description, seems very much like what you need, and at first glance, you might think: 'Oh! Instead of RAC, this is just perfect! Plus, with tech support at home!'.

But do not rush to rejoice, as we will describe why you should be aware of these nuances, since they are difficult to foresee even after thoroughly reading the product documentation. Assess whether you will be ready to frequently update the DBMS versions directly in the production environment, as some defects are incompatible with industrial operation and are difficult to detect during testing.
Start with a careful reading of the 'multimaster' section—'limitations' on the manufacturer's website.

The first issue you may encounter is the peculiarities of transaction processing in the so-called 'two-phase' mode, and sometimes, other than rewriting the entire logic of your procedures, there’s no way to fix it. Here’s a simple example:

create table test1 (id integer, id1 integer);
insert into test1 values (1, 1),(1, 2);
 
ALTER TABLE test1 ADD CONSTRAINT test1_uk UNIQUE (id,id1) DEFERRABLE INITIALLY DEFERRED;
 
update test1
           set id1 =
               case id1
                 when 1
                 then 2
                 else id1 - sign(2 - 1)
               end
         where id1 between 1 and 2;

An error occurs:

ERROR:  [MTM] Transaction MTM-1-2435-10-605783555137701 (10654) is aborted on node 3. Check its log to see error details.

You can struggle with dead locks in versions 10.5, 10.6 for a long time, and the only known solution that undermines the essence of the cluster is to remove 'problematic' tables from the cluster, i.e. make_table_local, but at least this will allow you to work without everything being blocked due to hanging transaction commits. Or you can upgrade to version 11.2, which should help, but maybe not, don't forget to check.

In some versions, you might encounter an even more mysterious lock:

username= mtm and backend_type = background worker

And in this situation, the only thing that can help you is upgrading the DBMS version to 11.2 or higher, but it may not help either.

Certain operations with indexes can lead to errors that explicitly state the problem is with Bi-Directional Replication; in the MTM logs, you will clearly see BDR. Could it be 2ndQuadrant? No... we bought multimaster, it's just a coincidence, that's the name of the technology.

[MTM] bdr doesn't support index rechecks
[MTM] 12124: REMOTE begin abort transaction 4083
[MTM] 12124: send ABORT notification for transaction  (5467) local xid=4083 to coordinator 3
[MTM] Receive ABORT_PREPARED logical message for transaction MTM-3-25030-83-605694076627780 from node 3
[MTM] Abort prepared transaction MTM-3-25030-83-605694076627780 status InProgress from node 3 originId=3
[MTM] MtmLogAbortLogicalMessage node=3 transaction=MTM-3-25030-83-605694076627780 lsn=9fff448

If you are using temporary tables, despite the assurances that 'the multimaster extension replicates data completely automatically. You can simultaneously perform write transactions and work with temporary tables on any node of the cluster.'

Then, in fact, you will find that replication does not work for all tables used in the procedure if the code includes the creation of a temporary table, and even using multimaster.remote_functions will not help; you will have to upgrade or rewrite your logic in the procedure. If you need to use both the multimaster and pg_pathman extensions simultaneously within 'Postgres Pro Enterprise' v 10.5, check that with this simple example:

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

CREATE TABLE measurement_y2019m06 PARTITION OF measurement FOR VALUES FROM ('2019-06-01') TO ('2019-07-01');
insert into measurement values (1, to_date('27.06.2019', 'dd.mm.yyyy'), 1, 1);
insert into measurement values (2, to_date('28.06.2019', 'dd.mm.yyyy'), 1, 1);
insert into measurement values (3, to_date('29.06.2019', 'dd.mm.yyyy'), 1, 1);
insert into measurement values (4, to_date('30.06.2019', 'dd.mm.yyyy'), 1, 1);

The logs on the database nodes start showing the following errors:

…
 PATHMAN_CONFIG doesn't contain relation 23245
> find_in_dynamic_libpath: trying "/opt/.../ent-10/lib/pg_pathman"
> find_in_dynamic_libpath: trying "/opt//.../ent-10/lib/pg_pathman.so"
> DEBUG: find_in_dynamic_libpath: trying "/opt/.../ent-10/lib/pg_pathman"
> find_in_dynamic_libpath: trying "/opt/.../ent-10/lib/pg_pathman.so"
> PrepareTransaction(1) name: unnamed; blockState: PREPARE; state: INPROGR, xid/subid/cid: 6919/1/40
> StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0
> switched to timeline 1 valid until 0/0
…
Transaction MTM-1-13604-7-612438856339841 (6919) is aborted on node 2. Check its log to see error details.
...
[MTM] 28295: REMOTE begin abort transaction 7017
…
[MTM] 28295: send ABORT notification for transaction (6919) local xid=7017 to coordinator 1

What these errors are, you can find out from technical support; after all, you purchased it for a reason.

What to do? Right! Upgrade to ‘Postgres Pro Enterprise’ to v 11.2

It is important to note that a sequence, being an object of a replicated database, does not have a global value throughout the cluster; each sequence is local to each node. If you have fields with unique constraints that use a sequence, you can only increment it by the equivalent of the node number in the cluster since the number of nodes in the cluster will determine how quickly the sequence grows, and int will run out faster than you expect. To simplify working with sequences, the product even includes a function called alter_sequences that will make the necessary increments for each sequence on all nodes, but be prepared that the function may not work in all versions. Of course, you can write it yourself based on code from GitHub or modify it directly in the database. Additionally, fields with the types serial and bigserial will work more reliably, but using them will likely require rewriting your procedure and function code. The function monotonic_sequences may be useful to some.

Until version 11.2 of ‘Postgres Pro Enterprise’, replication will only work if there are unique primary keys, so keep this in mind during development.

It is worth mentioning the peculiarities of npgsql's operation specifically in a clustered solution; these issues do not arise on a single node but are quite present in a multi-master setup.
In some versions, you may encounter the following error:

Exception Details: Npgsql.PostgresException: 25001: SET TRANSACTION ISOLATION LEVEL command
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and its origin in the code.

What can be done? Just avoid using certain versions. You need to be aware of them, as the error does not appear in just one version, and even after its first fix, you may encounter it later. You should also be prepared for this and ideally cover all identified database management system defects that the manufacturer fixes with separate regression tests. Trust, but verify.

If the application uses npgsql and switches between nodes thinking they are all identical, you might encounter the following error:

EXCEPTION: Npgsql.PostgresException (0x80004005): XX000: cache lookup failed for type ...

This error will occur because binding is performed

(NpgsqlConnection.GlobalTypeMapper.MapComposite("some_composite_type");)

of composite types when starting the application for all connections. As a result, you get an identifier from one node, and when querying another node, it does not match, leading to an error. Therefore, working transparently with composite types in a cluster will be impossible for some applications without additional rewrites on the application side (if you can manage to do that).

As we all know, the overall assessment of the cluster's state is very important for diagnostics and timely measures during operation. In the product, you will find certain features designed to make your life easier, but sometimes they may deliver results that are completely unexpected, even for the manufacturer.

For example:

select mtm.collect_cluster_info();
on each node returns the same result:
(1,Online,0,0,0,2,3,0,0,0,1,0,0,1,1,3,7,0,0,0,"2018-10-31 05:33:06")
(2,Online,0,0,0,2,3,0,0,0,1,0,0,1,1,3,7,0,0,0,"2018-10-31 05:33:06")
(3,Online,0,0,0,2,3,0,0,0,1,0,0,1,1,3,7,0,0,0,"2018-10-31 05:33:09")

But why does the LiveNodes field consistently show the number 2, when, according to the multi-master operation description, it should match the number AllNodes=3? Answer: you need to update the database version.

Be prepared to collect logs from all nodes, as you will often see the message 'the error is in the log of another node.' Tech support will accept any defects you report and inform you when the next version is ready, which may sometimes require stopping the service or take a while to implement (depending on the size of your DBMS). Do not expect that operational issues will trouble the vendor significantly, and that updates due to identified defects will involve vendor representatives; in fact, it may not even be necessary to involve vendor representatives, as you might end up with a disassembled cluster in production without a backup.

In fact, the license for the commercial product honestly warns: 'This software is provided on an

If you haven't figured out which product we're talking about, this experience was gained from a year of using Postgres Pro Enterprise database. You can draw your own conclusions; the state of the product is such that it could grow mushrooms.

However, this would not be a problem if the issues were addressed in a timely and efficient manner.

But this is precisely what does not happen. Apparently, the manufacturer lacks the resources to promptly address the identified bugs.

Only registered users can participate in the survey. Please log in, please.

Do you have experience transitioning from a foreign/proprietary DBMS to an open-source/national one?

21,3%Yes, positive 10
10,6%Yes, negative 5
21,3%No, we haven't changed the DBMS 10
4,3%We changed the DBMS, but nothing changed 2
42,6%View results 20

47 users voted. 12 users abstained.

Source: habr.com

'Pro, but not a cluster' or how we replaced our databases with domestic alternatives

Do you have experience transitioning from a foreign/proprietary DBMS to an open-source/national one?