PGConf.Russia 2022

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.

Every DBMS stores its data on a hard disc, so you may face a situation when your data on disc gets corrupted. This can happen due to a controller failure, logical or physical data corruption; there are also other reasons. If it is just the index file that gets corrupted, the index recreation command will enable you to restore data consistency in your DBMS. If a table file or a file of a system section gets corrupted, data restore is impossible. In this case, you need to invent workarounds. You can try to restore this data from backup files, copy it from the corrupted table, or find another way to solve this problem. In this talk, we'll tackle several cases of data corruption on disc and describe the options for restoring the data from the corrupted tables.

This talk covers the Oracle-to-Postgres Pro migration project for the billing system of the Federal State Unitary Enterprise “State Air Traffic Management Corporation of the Russian Federation” employed to collect the air navigation charges for the use of airspace.

We'll talk about the ways to change the database records without updates. To do this, we'll explore what's going on at the lowest level and see what can happen if we make changes in data files in different operation modes.