Re: open database on read only file system

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/ 

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux