How to make Postgres work reliably on Linux (Raspberry Pi in this case)?

Question

linux

Good afternoon!
Faced a problem.
I have a number of devices that are based on the Raspberry Pi.
They run software (in Java) that works with the Postgres database, which runs on the same board.
After some time of work, problem areas appeared in the database, obvious glitches (characters and service tables change, etc.). As far as I understand, the reason for this is the features of memory cards, which begin to fail (I encountered similar things on phones, registrars) and data begins to be lost.
Perhaps this is due to the fact that, unfortunately, power is sometimes lost and the boards are overloaded, i.e. no proper shutdown.
I use not the worst memory cards (Transcend, Kingston, Samsung), because I understand that by changing the cards the problem cannot be solved.
The problem is not to buy a new memory card and replace the old one. The problem is data loss.
Maybe someone has a similar experience how to ensure more or less reliability of such systems?
Here the question is not even for raspberry, but in general for Linux-based systems (and not only)
I have periodic database dumps. But again, I ran into the question that at one point suddenly the dumps stop being created due to a failure in the service tables. Therefore, dumps alone cannot clearly solve the problem.
Will it give me something if I replicate to another base that runs on the same board? Will they be able to recover in the event of a failure?
Is it possible to self-diagnose the database itself to check the integrity, consistency of data and service tables? This would give me the opportunity to catch problems early on.
I will be glad to any advice!

Answer 1

Maybe someone has a similar experience how to ensure more or less reliability of such systems?
Here the question is not even for raspberry, but in general for Linux-based systems (and not only)

Just applies to rasberry. Flash drives, whatever they are, are unreliable.
1) To begin with, fence any guaranteed food.
2) ECC memory is used on servers for a reason, so even in the case of a spherical "good" in a vacuum, errors can occur.
3) Take out a DB on other storage.
For general reliability:
1) Use f2fs instead of ext3\4.
2) Transfer the root \ partition to read-only mode
3) Store the database separately from the system. Connected via usb hdd\ssd. Mount over the network from a more reliable source.
4) You can get confused with overlays. Conventionally, throw the directory with the database into the overlay, where the upper level will be in tmpfs and the lower one on the flash drive. Run once every N hours/day a merge from the upper to the lower level.
As a reliable power supply, you can use a powerbank or even a regular uninterruptible power supply. The advantage of the latter is that it can be hooked up to a raspberry and see how much charge, what condition and take action if the power is transferred to the battery. Those. in your case, for example, how to launch the merge according to point 4.

Answer 2

Dmitry Alexandrov , thank you for such a detailed and exhaustive answer!
I will study each of the points in more detail, because. in some things I encounter for the first time (f2fs, overlays).
With uninterruptible power supply, it really turned out to be a "jamb", because. when I started thinking about how to provide my system with guaranteed power, another organization that is involved in the project promised that they would provide me with guaranteed 220V. They sort of ensured that by installing a UPS with good batteries, but they periodically climb there fixing "jambs" in their equipment and thus managing to turn off my equipment as well ...
Regarding the use of powerbank, I decided to create a separate topic, so as not to confuse here all together.
There I stated my concerns about this and the results of disappointing experiments with my powerbank.
Well, i.e. the logic of work should be the following - after starting the board, should I mount an external hard drive, and then start postgres, which will already work with external storage? Or did I misunderstand the idea?
And for a DB all the same where files of its DB are stored?
And tell me please how to turn off Linux correctly so as not to damage anything? I understand that
I need to first stop the same DB, and other processes that can perform any kind of write to the memory card?