Re: How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Stefan Keller
> Sent: Sunday, April 17, 2011 2:04 PM
> To: pgsql-general List
> Subject: [GENERAL] How to configure a read-only database server and
> session? (Was: read-only UNLOGGED tables)
>
> 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.
>
> I compiled following steps in order to secure and speedup such
> PostgreSQL/PostGIS instance:
>
> 1. Re-configure PostgreSQL server as following:
>
> a. Disabling autovacuum daemon.
> b. Setting postgresql.conf parameters:
> fsync=off
> synchronous_commit=off
> full_page_writes=off
>
> 2. Restart server, login as db admin, create database, create an app.-
> user.
>
> 3. Load dataset...:
> a. with owner 'app.-user' in schema PUBLIC;
> b. create indexes;
> c. issue a VACUUM ANALYZE command on user tables.

Might consider setting your indexes to be fill factor 100 if you have not already. Be aware of what this will mean when you "load dataset" in the future for a refresh.

- Mark