Postgresql – Battery backed write cache and Postgres

cachepostgresql

I will be getting a SAN with a battery backed write cache for a new Postgres data warehouse and am not sure what settings to apply to make proper use of it. If the cache is enabled in the SAN is that enough or do I need to set wal_sync_method, synchronous_commit or others?

The warehouse will be handling 5-10 million row inserts a day once the current data sources are turned on. This will most likely grow in the future.

We will be using Postrges 9.3 and most likely Centos 6.4.

Thanks in advance.

Best Answer

Literature

I gathered you some links you should read:

Possible Workflow

Basically your workflow might be the following, which is extracted as a combination of all tips of the above links:

  1. Turn on postgres setting fsync if it is not.
  2. Set synchronous_commit to "on" (or "local") if it is set to "off".
  3. Run diskchecker.pl just for sanity to see if you bought something good and it is configured correctly.
  4. See what sync method postgres selected automatically with show wal_sync_method;
  5. Run pg_test_fsync
  6. and use it's output to
    1. check if you are affected by FLUSH CACHE EXT/SYNCHRONIZE CACHE problem.
    2. decide on the basis of the additional information provided here (in paragraph wal_sync_method) and here (in paragraph test_fsync and below) what value to set wal_sync_method to.
  7. Check if your BBU and file-system prohibit partial writes of 8kb pages, if yes you may disable full_page_writes (see last paragraph here).

Remark (why this might not be enough)

Note that this seems very reliable, but quoted from one of the links above:

Critics of the battery backup approach suggest that if you run such a system under load, eventually you’ll have a failure in this relatively complicated cache method that will corrupt your database in a way that’s nearly impossible to detect or recover from.

So even having a BBU and a correctly configured server instance, this does not remove the burden to backup everything regularly, and does not remove the possibility to loose some data.

Legal Note

I am not responsible for any lost data ;) neither due to lack of advice here or a wrong tip, nor through anything else.

Related Question