Thread: open database on read only file system

open database on read only file system

From

xujian

Date:

07 October 2016, 22:43:53

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

Re: open database on read only file system

From

Scott Whitney

Date:

07 October 2016, 23:06:26

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@postgresql.org <pgsql-admin-owner@postgresql.org> on behalf of xujian <jamesxu@outlook.com>
Sent: Friday, October 7, 2016 2:43 PM
To: pgsql-admin@postgresql.org
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?

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/

Re: open database on read only file system

From

xujian

Date:

08 October 2016, 00:46:08

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@journyx.com
To: jamesxu@outlook.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] open database on read only file system
Date: Fri, 7 Oct 2016 20:06:09 +0000

Re: open database on read only file system

From

"David G. Johnston"

Date:

08 October 2016, 01:06:27

On Fri, Oct 7, 2016 at 2:46 PM, xujian <jamesxu@outlook.com> wrote:

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

For some definition of "work" something like this might give you satisfactory results. You'd likely get more reliable answers if you explain exactly what your intended usage pattern is.

In short - the software has not been designed to operate in production on a read-only file system and likely few people, if anyone, is going to be able to give any kind of assurance that the system won't crash if operated in such a manner.

You will need to ensure that you have at least one read/write file system setup as a tablespace in the cluster so that large queries can make use of temporary result areas.

David J.

Re: open database on read only file system

From

Scott Whitney

Date:

08 October 2016, 02:55:11

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@outlook.com>
Date: 10/07/2016 4:46 PM (GMT-06:00)
To: Scott Whitney <scott@journyx.com>, pgsql-admin@postgresql.org
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@journyx.com
To: jamesxu@outlook.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] open database on read only file system
Date: Fri, 7 Oct 2016 20:06:09 +0000

Re: open database on read only file system

From

Scott Whitney

Date:

08 October 2016, 02:56:31

Yes. This is the proper answer. Mine is shorter, but this is the entire point.

The results you get by enabling PG to run (temporarily) on RO doesn't guarantee production results.

-------- Original message --------
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: 10/07/2016 5:06 PM (GMT-06:00)
To: xujian <jamesxu@outlook.com>
Cc: Scott Whitney <scott@journyx.com>, pgsql-admin@postgresql.org
Subject: Re: [ADMIN] open database on read only file system

On Fri, Oct 7, 2016 at 2:46 PM, xujian <jamesxu@outlook.com> wrote:

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

For some definition of "work" something like this might give you satisfactory results. You'd likely get more reliable answers if you explain exactly what your intended usage pattern is.

You will need to ensure that you have at least one read/write file system setup as a tablespace in the cluster so that large queries can make use of temporary result areas.

David J.

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/

Re: open database on read only file system

From

xujian

Date:

08 October 2016, 05:15:24

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@journyx.com
To: jamesxu@outlook.com; pgsql-admin@postgresql.org
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.

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@journyx.com
To: jamesxu@outlook.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] open database on read only file system
Date: Fri, 7 Oct 2016 20:06:09 +0000

Re: open database on read only file system

From

"David G. Johnston"

Date:

10 October 2016, 17:37:48

On Fri, Oct 7, 2016 at 7:15 PM, xujian <jamesxu@outlook.com> wrote:

we even don't want superuser change the data. so setting the readonly on file system level seems a good solution for me.

You haven't said what business need you are trying to meet with this solution but I'd posit that making PostgreSQL work on a RO file system is overkill...

David J.