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

From: Stefan Keller <sfkeller(at)gmail(dot)com>
To: pgsql-general List <pgsql-general(at)postgresql(dot)org>
Subject: How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)
Date: 2011-04-17 20:04:12
Message-ID: BANLkTindcHg_oEpdfu8esF-k-iCwS7G9Og@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

4. Create a 'read-only' user (login role) with only read access to
user defined tables:
GRANT SELECT ... TO read_only_user

5. Optimize and secure session by following parameters:

SET transaction_read_only TO FALSE;
SET TRANSACTION READ ONLY;

6. Go to step 3 in case of new data or a harddisk crash.

Questions:
=> Any comments on securing such a PostgreSQL instance further?
All user tables reside in schema PUBLIC, Ok?

=> Any comments on making this PostgreSQL instance 'robust'?
E.g. which situations (except for harddisk crashes) can leave a
read-only dataset in an inconsistent state where PostgreSQL server
can't restart? An immediate shutdown?

=> Any comments on speeding up/optimizing such a read-only dataset?
What about wal_level and archive_mode?

Yours, Stefan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andy Colson 2011-04-18 00:08:28 Re: Indexing strategy
Previous Message Tom Lane 2011-04-17 19:16:28 Re: Installing PGDG on a fresh CentOS 5.6