Viewing entries tagged with restore. Return to full view.

Locating the recovery point just before a dropped table

A common example when talking about why it's a good thing to be able to do PITR (Point In Time Recovery) is the scenario where somebody or some thing (operator or buggy application) dropped a table, and we want to do a recover to right before the table was dropped, to keep as much valid data as possible.

PostgreSQL comes with nice functionality to decide exactly what point to perform a recovery to, which can be specified at millisecond granularity, or that of an individual transaction. But what if we don't know exactly when the table was dropped? (Exactly being at the level of specific transaction or at least millisecond).

On way to handle that is to "step forward" through the log one transaction at a time until the table is gone. This is obviously very time-consuming.

Assuming that DROP TABLE is not something we do very frequently in our system, we can also use the pg_xlogdump tool to help us find the exact spot to perform the recovery to, in much less time. Unfortunately, the dropping of temporary tables (implicit or explicit) is included in this, so if your application uses a lot of temporary tables this approach will not work out of the box. But for applications without them, it can save a lot of time.

Let's show an example. This assumes you have already set up the system for log archiving, you have a base backup that you have restored, and you have a log archive.

The first thing we do is try to determine the point where a DROP TABLE happened. We can do this by scanning for entries where rows have been deleted from the pg_class table, as this will always happen as part of the drop.

Continue reading

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

Postgres Open 2017
Sep 6-8, 2017
San Francisco, USA
PGConf.EU 2017
Oct 24-27, 2017
Warsaw, Poland
PGConf.Asia
Dec 4-6, 2017
Tokyo, Japan

Past

PGDay.RU
Jul 5-7, 2017
St Petersburg, Russia
PGDay.UK
Jul 4, 2017
London, UK
Amsterdam PUG
Jun 29, 2017
Amsterdam, Netherlands
PGCon 2017
May 23-26, 2017
Ottawa, Canada
FOSS-North
Apr 26, 2017
Gothenburg, Sweden
More past conferences