D
D
Dannerty2018-11-12 08:20:30
PostgreSQL
Dannerty, 2018-11-12 08:20:30

Postgresql backup, can it be configured?

Hello. Tell me, is it possible to set up backups in Postgresql separately?
There is a database, it has several tables that represent logs / statistics. It is necessary to set up separate backups for the main database and for these tables. Everything would be simple if the data in the tables were fixed, but some fields in these tables change. Therefore, it is necessary to somehow take into account the changes made.
Ideally, I would like to set up a copying scheme as follows:
On a certain day, make a full backup of the database and statistics, and on all other days / hours, make a copy of the main database without statistics + (new records and changes made in the statistics tables).
Tell me, is it possible to do this in Postgresql?
UPD. Suppose on Monday I have a full backup of all databases and statistics. Let's say there are 20 million records in the statistics table.
The next backup occurs in 3 hours. During these 3 hours, 50 thousand records arrived in the statistics table and another 5 thousand were changed. The backup should include the database itself without statistics (it's clear how to do it) and that's just these 50 thousand new records and information about changing these 5 thousand, and the table itself should not be completely copied.
So that if something happens, it would be possible to restore the database itself first, then restore the full statistics backup created on the same Monday, and then pull up only new records and make changes to the statistics tables that occurred after the backup was created on Monday.

Answer the question

In order to leave comments, you need to log in

3 answer(s)
S
Sergey Gornostaev, 2018-11-12
@sergey-gornostaev

Yes, it's possible . Look at the options -tand-T

K
ky0, 2018-11-12
@ky0

Look in the direction of PITR - in my opinion, this is exactly what you need, that is, the ability to roll back (more precisely, on the contrary, roll back) the base at a specific point in time. A full backup is made, and all changes that occur after it are added to WAL files.

D
Dannerty, 2018-11-12
@Dannerty

In general, I decided to make this option:
Create additional tables to collect changes in the statistics tables. Hang up insert\update triggers on statistics tables, which will insert\modify records in additional tables. And then from these extra. tables the data will be transferred where it is necessary through insert on conflict.
Everything seems to work in theory.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question