Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Consider that you still need outage windows, at least tiny ones occasionally. So remember that your data sources must buffer what they send in case the DB is briefly unavailable. If you don't, you're setting yourself up for a lot of pain later. –  Craig Ringer Feb 24 at 23:56

1 Answer 1

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.

share|improve this answer
    
Thanks, I will definitely be going through your advice in detail when setting up the db :) –  nash Feb 24 at 17:20
1  
Looks good - but I'd leave full_page_writes on unless I had a system that was documented and proven in tests not to tear pages. Note that Pg uses 8kb pages, not the 512 byte or 4k pages of most disk subsystems, and it's the full 8kb page that must be atomically written. Also, totally agree, nothing removes the need for good backups. Use a streaming replica with fallback WAL archiving, keep a PITR base backup, and do periodic logical dumps. –  Craig Ringer Feb 24 at 23:53
    
I added the 8kb remark to my answer. It is indeed important enough to note it explicitly. –  GhostGambler Feb 25 at 7:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.