Re: moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

Maxim Boguk wrote:
>> database and transaction log backup compression? not available?

> Transaction log backup compression not available (however could be easily archived via external utilities like bzip2).

Well, in PostgreSQL you backup transaction logs by setting "archive_command",
which is a operating system command you write.
You just put a "gzip -1" in there and your WAL archive will be compressed.

>> - recovery from hardware or software corruption -
>>
>> suppose I am running a mission critical database (which is also relatively large, say > 1TB)
>> and I encounter a corruption of some sort (say, due to hardware or software bug)
>> on individual database pages or a number of pages in a database
>>
>> How do I recover quickly and without losing any transactions? MS-SQL and Oracle can restore
>> individual pages (or sets of pages) or restore individual database files and then allow me
>> to roll forward transaction log to bring back every last transaction. It can
>> be done on-line or off-line. How do I achieve the same in PostgreSQL 9.4?
>> One solution I see may be via complete synchronous replication of the database to another server.
>> I am but sure what happens to the corrupt page(s) - does it get transmitted corrupt
>> to the mirror server so I end up with same corruption on both databases or is there some protection against this?

​> It's depend where a corruption happen, if pages become corrupted due to some
> problems with physical storage (filesystem) in that case a replica data should be ok.

I would not count on that.
I have had a case where a table file got corrupted due to hardware problems.
Pages that contained data were suddenly zeroed.
PostgreSQL recognizes such a block as empty, so the user got no error, but
data were suddenly missing. What does a user do in such a case? He/she grumbles
and enters the data again. This insert will be replicated to the standby (which was
fine up to then) and will cause data corruption there (duplicate primary keys).

PostgreSQL replicates the physical block, so data corruption that does not
trigger an error will be replicated.
You should enable checksums to minimize that risk.

If bad comes to worse, you'll just have to recover, although I'd say that in most cases
a standby database will help you survive a hardware failure.

Yours,
Laurenz Albe