Re: How to configure a read-only database server?

On Tue, Apr 19, 2011 at 12:08 AM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
> I browsed the faq and looked at PostgreSQL performance books but I
> could not find the obvious:
> How to configure a read-only database server?
>
> I have a single-disk virtual Linux system and a read-only dataset
> which is exposed to internet and completely replaced from time to
> time.
>
> This is what I found so far:
>
> * Disabling autovacuum daemon.

I guess this will give you only small benefits as the daemon won't
find any tables with modifications.

> * Setting postgresql.conf parameters:
>   fsync=off
>   synchronous_commit=off

Since you don't commit changes the effect of this might be small as well.

>   full_page_writes=off
>
> * For the session:
>   SET transaction_read_only TO FALSE;

Did you mean "TRUE"?

>   SET TRANSACTION READ ONLY;

What about

ALTER DATABASE x SET default_transaction_read_only = on;

?

> * What about wal_level and archive_mode?
>
> => Any comments on speeding up/optimizing such database server?

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/