|
Thanks for Scott and David's reply. The cluster is for read only purpose, and no client should change the data.
Set default_transaction_read_only can make the database "readonly", however, client can change the setting in his session. Using readonly user role might be another choice, but this cluster is restored automatically from other cluster, and we have to re-granting permission for all the client, we even don't want superuser change the data. so setting the readonly on file system level seems a good solution for me. And I also have impression that real only FS has better performance due to the linux os cache(correct?). I know PGDATA should be on read-write FS, but putting table space on readonly FS sounds reasonable to me. yes, we will definitely test the change, fully :). thanks again for your help! James From: scott@xxxxxxxxxxx To: jamesxu@xxxxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxx Subject: Re: [ADMIN] open database on read only file system Date: Fri, 7 Oct 2016 23:54:55 +0000 Well, I mean, yeah. You're (presumably) trying to test changes before going live? But you're changing the code to run on an RO system? That's not an apples-to-apples test, and (while unlikely) your results could be different in production. If **I** were doing this? I would set up replication, get it in sync to my internal test server, break the replication, promote the slave and run my tests.
That's a 1:1.
That would make me the most confident.
-------- Original message -------- From: xujian <jamesxu@xxxxxxxxxxx> Date: 10/07/2016 4:46 PM (GMT-06:00) To: Scott Whitney <scott@xxxxxxxxxxx>, pgsql-admin@xxxxxxxxxxxxxx Subject: RE: [ADMIN] open database on read only file system Thanks Scott. Do you know if there is any way to support read-only FS? for instance patch or extension.
I noticed the error is from file md.c
========
/*
* mdopen() -- Open the specified relation.
*
* Note we only open the first segment, when there are multiple segments.
*
* If first segment is not present, either ereport or return NULL according
* to "behavior". We treat EXTENSION_CREATE the same as EXTENSION_FAIL;
* EXTENSION_CREATE means it's OK to extend an existing relation, not to
* invent one out of whole cloth.
*/
static MdfdVec *
mdopen(SMgrRelation reln, ForkNumber forknum, int behavior)
========
so I changed the file open mode from O_RDWR to O_RDONLY
fd = PathNameOpenFile(path, O_RDONLY | PG_BINARY, 0600);
and it works. I can open the user database, and query table now. Do you think if there is any potential problem of this change?
I just need to open database on read-only file system, and no need any changes on the cluster. thanks
James
From: scott@xxxxxxxxxxx To: jamesxu@xxxxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxx Subject: Re: [ADMIN] open database on read only file system Date: Fri, 7 Oct 2016 20:06:09 +0000
To my knowledge, it is not, no.
The FS must be read/write. What I would do in this situation is to migrate it to a test/new system where no one can access it and bring it up r/w. Or set up streaming replication. That gives you select-only. From: pgsql-admin-owner@xxxxxxxxxxxxxx <pgsql-admin-owner@xxxxxxxxxxxxxx> on behalf of xujian <jamesxu@xxxxxxxxxxx>
Sent: Friday, October 7, 2016 2:43 PM To: pgsql-admin@xxxxxxxxxxxxxx Subject: [ADMIN] open database on read only file system Hi,
Could you please let me know if it is possible to open database on read-only file system?
I have a psotgresql 9.6 cluster, PGDATA folder is on READ-WRITE volume(system volume), user database is created on a table space which is located on another volume(data volume). Now we set the data volume to read only mode,
before we changed the mode, I also disabled the auto vacuum, run checkpoint,
and vacuum free. We reboot the service, everything was fine until we tried to access the database.
===============
postgres=# \c userdb;
FATAL: could not open file "pg_tblspc/16384/PG_9.6_201608131/16468/2662": Read-only file system
===============
we enabled the debug mode, in log file, we saw
===============
FATAL: 42501: could not open file "pg_tblspc/16384/PG_9.6_201608131/16468/2662": Read-only file system
LOCATION: mdopen, md.c:609
===============
Does anyone know if there is anyway to run database on read-only file system? thanks
James
Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757 www.journyx.com p 512.834.8888
f 512-834-8858
Do you receive our promotional emails? You can subscribe or unsubscribe to those emails at http://go.journyx.com/emailPreference/e/4932/714/
|
