pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedopen database on read only file system
8+ messages / 3 participants
[nested] [flat]
* open database on read only file system
@ 2016-10-07 19:43 xujian <jamesxu@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: xujian @ 2016-10-07 19:43 UTC (permalink / raw)
To: pgsql-admin
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 systemLOCATION: mdopen, md.c:609===============
Does anyone know if there is anyway to run database on read-only file system? thanks
James
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: open database on read only file system
@ 2016-10-07 20:06 Scott Whitney <scott@journyx.com>
parent: xujian <jamesxu@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Whitney @ 2016-10-07 20:06 UTC (permalink / raw)
To: xujian <jamesxu@outlook.com>; pgsql-admin
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?
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: open database on read only file system
@ 2016-10-07 21:46 xujian <jamesxu@outlook.com>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 2 replies; 8+ messages in thread
From: xujian @ 2016-10-07 21:46 UTC (permalink / raw)
To: Whitney Scott <scott@journyx.com>; pgsql-admin
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_RDONLYfd = 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
JamesFrom: 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
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?
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: open database on read only file system
@ 2016-10-07 22:06 David G. Johnston <david.g.johnston@gmail.com>
parent: xujian <jamesxu@outlook.com>
1 sibling, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2016-10-07 22:06 UTC (permalink / raw)
To: xujian <jamesxu@outlook.com>; +Cc: Whitney Scott <scott@journyx.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: open database on read only file system
@ 2016-10-07 23:54 Scott Whitney <scott@journyx.com>
parent: xujian <jamesxu@outlook.com>
1 sibling, 1 reply; 8+ messages in thread
From: Scott Whitney @ 2016-10-07 23:54 UTC (permalink / raw)
To: xujian <jamesxu@outlook.com>; pgsql-admin
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
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?
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: open database on read only file system
@ 2016-10-07 23:56 Scott Whitney <scott@journyx.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Whitney @ 2016-10-07 23:56 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; xujian <jamesxu@outlook.com>; +Cc: pgsql-admin
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<mailto: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. ?
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: open database on read only file system
@ 2016-10-08 02:15 xujian <jamesxu@outlook.com>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 8+ messages in thread
From: xujian @ 2016-10-08 02:15 UTC (permalink / raw)
To: Whitney Scott <scott@journyx.com>; pgsql-admin; david.g.johnston@gmail.com <david.g.johnston@gmail.com>
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.
-------- 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
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?
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: open database on read only file system
@ 2016-10-10 14:37 David G. Johnston <david.g.johnston@gmail.com>
parent: xujian <jamesxu@outlook.com>
0 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2016-10-10 14:37 UTC (permalink / raw)
To: xujian <jamesxu@outlook.com>; +Cc: Whitney Scott <scott@journyx.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2016-10-10 14:37 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-07 19:43 open database on read only file system xujian <jamesxu@outlook.com>
2016-10-07 20:06 ` Scott Whitney <scott@journyx.com>
2016-10-07 21:46 ` xujian <jamesxu@outlook.com>
2016-10-07 22:06 ` David G. Johnston <david.g.johnston@gmail.com>
2016-10-07 23:56 ` Scott Whitney <scott@journyx.com>
2016-10-07 23:54 ` Scott Whitney <scott@journyx.com>
2016-10-08 02:15 ` xujian <jamesxu@outlook.com>
2016-10-10 14:37 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox