Re: [GENERAL] How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)
On 04/18/2011 04:04 AM, Stefan Keller wrote: 5. Optimize and secure session by following parameters: SET transaction_read_only TO FALSE; SET TRANSACTION READ ONLY; AFAIK, neither of those have any effect on security. They're purely advisory hints to Pg. Personally I think it'd be cool if read-only transactions were denied the use of INSERT/UPDATE/DELETE, any untrusted PLs, and any INSERT/UPDATE/DELETE via SPI from PLs. But would be cool isn't want to try to implement it and I'm sure if it were easy, it'd have already been done. All user tables reside in schema PUBLIC, Ok? Yep. Make sure you don't grant CREATE on public to the target user, only grant USAGE, and revoke all from public. = 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? AFAIK: - Kill -9 of a backend - segfault / sigabrt / sigbus / etc of a backend (though backends may try to handle some of these it'd normally be unsafe and I doubt it, but I haven't checked) - Kill -9 of the postmaster - OS crash or unexpected reset - probably other things = Any comments on speeding up/optimizing such a read-only dataset? Depending on dataset size and access patterns, it could be worth pinning a few indexes in a tablespace that lives on a ramdisk. Usually Pg's and the OS's cache management will do the job well, but if you know more than them - say, that this index will always be really hot, or that certain queries are more important than others and must be more responsive - you can play with that sort of thing. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
