When it comes to reliability and recover-ability, PostgreSQL is as good a database as any other RDBMS implementation we see in the market. Postgres very strongly complies with ACID philosophy of Relational Databases. It has Multi-version concept where the older snapshot of rows/data is preserved until any transaction which may need it lives (something similar to UNDO tablespaces or rollback segment of Oracle). The database recover-ability is ensured by WAL segments which you can relate to redo log files of Oracle or transaction logs of SQL Server. These WAL files are useful for single phase recovery (after an OS or hardware crash). You can enable Archiving of these WAL files and copy them to a separate disk or server and use it for complex recovery. I will talk about this at length in one of my next blogs.
One thing which I have seen Oracle DBAs boast about is multiplexing of redo log files by creating multiple redo log groups. Well that is something which is not there is PostgreSQL out of box (and mainly because it’s not really needed given that we have RAID devices and multiplexing/mirroring at OS/storage level). Still if you have a need to multiplex your WAL segments you can do that quite easily v9.2 onwards with pg_receivexlog.
Do pg_receivexlog is a utility which is based on streaming replication and can stream the WAL files in real time. Unlike archive_command it does not require WAL files to be completely filled (16MB) before they can sent over. The files replicated/streamed can be used for archiving or replication.
You can setup a local stream for which you need to-
1. set max_wal_senders to a value 1 or greater. If you have setup replication and using pg_basebackup please count them too.
max_wal_senders=2
2. Set the wal_level to archive or hot_standby
wal_level=hot_standby
3. Set pg_hba.conf to allow replication connections from local (UNIX Socket)
local replication postgres trust
4. Restart your DB server after these changes
pg_ctl restart -m fast
5. Start pg_receivexlog-
pg_receivexlog -D /usr/local/pgsql/wal_2 -U postgres
Make sure that the PGHOST is not set in the session from where you are firing this command.
This command will copy (stream) the WALs from a server locally (on default port pointed by PGPORT) and store them in secondary WAL location – /usr/local/pgsql/wal_2
Now if your server crashes and you lose your WAL directory, all you need to do is look for a file with “.partial” as extension in your secondary WAL location (/usr/local/pgsql/wal_2), rename the file to remove the extension. Then copy the content of secondary WAL location to primary location and restart the server. The last chuck of changes were present in this “.partial” file which otherwise would not have got archived.
Note: There is one caveat though, the files in secondary WAL directory will not get rotated or removed automatically, you may do that on periodic basis with pg_archivecleanup command.