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

Поиск
Список
Период
Сортировка
От Stefan Keller
Тема How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)
Дата
Msg-id BANLkTindcHg_oEpdfu8esF-k-iCwS7G9Og@mail.gmail.com
обсуждение исходный текст
Ответы Re: How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)  (Craig Ringer <craig@postnewspapers.com.au>)
Re: How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)  ("mark" <dvlhntr@gmail.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Installing PGDG on a fresh CentOS 5.6
Следующее
От: Andy Colson
Дата:
Сообщение: Re: Indexing strategy