CODEWITHSUNDEEP
postgresql
jl6
jl6

Reputation: 6414

Is Postgresql Continuous Archiving practical for maintaning a complete database history?

If I enable Continuous Archiving from day one, are the resulting logs a practical method of keeping a complete point-in-time history of all database operations? I guess transaction volume will be a factor, so assume less than 1000 transactions a day.

Upvotes: 2

Views: 267

Answers (1)

kgrittn
kgrittn

Reputation: 19491

That depends on what you mean by "a complete point-in-time history of all database operations".

A base backup and all of the Write-Ahead Log (WAL) files (also often referred to as the transaction log or xlog) from the start of the backup forward should allow you to recover to any point in time. To minimize recovery time, though, it is a good idea to take a fresh base backup periodically. (Many people do this weekly or monthly, but I've heard of people doing is much less frequently.)

These logs are oriented toward physical storage of the data, not logical statements, so it is currently not possible to determine the SQL statements which generated the xlog. So if you're looking for an audit trail of what happened, it is not currently suitable for that.

There is a team of PostgreSQL developers working on logical replication, to allow broader uses of xlog data, for probable release in version 9.3, which won't be out for over a year. Until then, people use trigger-based logging for such audit trails.

Upvotes: 5

Related Questions