PGConf.Russia 2022

Disaster-tolerant cluster implies minimal data loss in the event of the main data center disaster and switching to the backup data center. The data loss takes place due to the asynchronous replication between the main data center and the backup one. However, there's a solution that can resolve the existing problem and ensure zero data loss in the case of the main data center disaster. My talk covers this solution for zero data loss.

PostgreSQL has a number of peculiarities that you need to take into account not only while maintaining your database but also when designing your database schema. Experienced PostgreSQL are well aware of vacuuming process. On the web one can find tons of materials covering its internals, configurations and monitoring. Many valuable talks about vacuum were given at numerous conferences. However, we still face the common wraparound problem when the maximum possible number of transactions (xid) is reached. It happens even on databases that are relatively small in size. In my presentation, I will share a customer case that looks interesting to me. A chain of mistakes made at different stages of the database's life cycle once caused a disaster. The database fully stopped for one week, we detected a wraparound and spotted corrupted blocks. Maintenance was problematical, and we spent sleepless nights in search of a solution. We managed to achieve a local win as we finally restored the database, but it's not the end of this story, which makes it even more interesting.

The talk covers the use of PostgreSQL, LUI and LUI4ORA2PG for building an analytical reporting system.

The talk tackles the following topics:

• migration from an Oracle environment;
• application of JSON functions;
• how the temporary tables helped us;
• our own means for load testing and bottlenecks detection;
• how to make beautiful GeoJSON format maps to display diagrams on them;
• installation and testing of the system on an "Elbrus" computer;
• what became an obstacle or was missing when using PostgreSQL.

The history of growth of the Live Universal Interface (LUI) web application development tool and the LUI4ORA2PG migration tool can be found in our previous presentations at PGConf conferences:

https://pgconf.ru/2019/118109 ;

https://pgconf.ru/201911/264095 ;

https://pgconf.ru/2020/262456 ;

https://pgconf.ru/2021/288310 .

This presentation is about NULLs implementation in the Postgres database: - What is NULL? - How is it handled by various Postgres functions? - How are NULLs stored in the database? - Indexing of NULLs