r/programming • • Nov 16 '14

PostgreSQL vs. MS SQL Server - A comparison of two relational databases from the point of view of a data analyst

http://www.pg-versus-ms.com/
171 Upvotes

321 comments sorted by

View all comments

Show parent comments

2

u/sgoody Nov 20 '14

I'm sure the last time I remember reading the documentation that if you wanted to recover a database to a point in time you had to shutdown the PostgreSQL service, do some file copies/deletes for data files and then start the service back up, at which point you get a point in time for all databases.

If that is the case, then this seems a bit limiting to me that you have to A) stop the world B) restore every database or nothing

Of course this is only what I have read and may be either incorrect or out of date.

1

u/doublehyphen Nov 20 '14 edited Nov 20 '14

Thta is not how one would do a PITR in PostgreSQL. What you do is ahead of time taking a base backup (a copy of the entire data directory) and then save all generated WAL (PostgreSQL deletes WAL as soon as none needs it anymore). When you have to do the PITR you start a new PostgreSQL instance against the base backup which starts to apply the WAL up until the point you wish to recover to.

So, yes, it is restore every database or nothing, but you do not need to stop anything to do the restore since the restore is normally done on another server, or at least another instande at the same server.

Personally I think this is the sound way to do it since I may just want to do the PITR to get accidnetally deleted data back, which I will then manually put back into production. And in that case I do not want my production database to be touched at all while digging for the data.

1

u/sgoody Nov 20 '14 edited Nov 20 '14

That seems like a work around which would work, but it seems a bit convoluted, especially if your databases are large.

But thanks for the tip.

1

u/doublehyphen Nov 21 '14

Yeah, it is a bit convoluted, but there are a couple of tools like Barman and OmniPTR which makes it simple for you.