D
D
Dmitry2017-10-28 20:57:19
PostgreSQL
Dmitry, 2017-10-28 20:57:19

How to restore a PostgreSQL database from two dumps?

The site creates a database dump every night (in plain text format):

sudo -u postgres pg_dump ${DB} | gzip > ../backup/`date +%Y-%m-%d`.${DB}.sql.gz

The editors on the site accidentally demolished some of the entries. It seems to be not scary - there is a backup from the 26th, but as luck would have it, on the 27th and 28th, users added many more entries to the site.
Now you need to somehow return the erased data that is in the database from the 26th (but there is no new data added by users) to the database from the 28th (with new records, but without the old necessary ones).
Tell me how to do it?
PS. Since when you directly roll onto an existing database (I thought it might work out), errors are pouring in, I wrote a dump parser in Python and can form line-by-line records like
COPY table1 (id, поля... ) FROM stdin;
1 данные для полей
\.
COPY table1 (id, поля...) FROM stdin;
2 данные для полей
\.

in order to try to push the data one line into the existing database, but the database also chewed a little, basically everything spits back with the error "The key already exists."
As a crooked solution, I see the creation of a separate base from the 26th and from it, through Python, select objects and add them to the base from the 28th programmatically. But it's still half a day to code.
How "beautifully" is such a task solved only by means of PostgreSQL? After all, is it resolved?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question