From: Scott Whitney <scott@journyx.com>
To: xujian <jamesxu@outlook.com>, "pgsql-admin@postgresql.org"
	<pgsql-admin@postgresql.org>
Subject: Re: open database on read only file system
Thread-Topic: [ADMIN] open database on read only file system
Thread-Index: AQHSINNYee79D28WnEKNDEntslyHsaCdaqJvgAAcHACAACQCVw==
Date: Fri, 7 Oct 2016 23:54:55 +0000
Message-ID: <cmqpipd2aeyul04yifqd2dvs.1475884481598@email.android.com>
References: 
 <BAY181-W523DAD585129C574CC0E7DA1C60@phx.gbl>,<BY1PR11MB03906B5B3592576B3D4491F1A7C60@BY1PR11MB0390.namprd11.prod.outlook.com>,<BAY181-W65B2AF3CFDD6A29D89C51AA1C60@phx.gbl>
In-Reply-To: <BAY181-W65B2AF3CFDD6A29D89C51AA1C60@phx.gbl>
Accept-Language: en-US
Content-Language: en-US
received-spf: None (protection.outlook.com: journyx.com does not designate
 permitted sender hosts)
spamdiagnosticoutput: 1:99
spamdiagnosticmetadata: NSPM
Content-Type: multipart/alternative;
	boundary="_000_cmqpipd2aeyul04yifqd2dvs1475884481598emailandroidcom_"
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-originalarrivaltime: 07 Oct 2016 23:54:55.1470
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 83db917a-cb55-44ba-82ef-25aa3e32343f
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY1PR11MB0390
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

--_000_cmqpipd2aeyul04yifqd2dvs1475884481598emailandroidcom_
Content-Type: text/plain; charset="gb2312"
Content-Transfer-Encoding: base64

V2VsbCwgSSBtZWFuLCB5ZWFoLiBZb3UncmUgKHByZXN1bWFibHkpIHRyeWluZyB0byB0ZXN0IGNo
YW5nZXMgYmVmb3JlIGdvaW5nIGxpdmU/IEJ1dCB5b3UncmUgY2hhbmdpbmcgdGhlIGNvZGUgdG8g
cnVuIG9uIGFuIFJPIHN5c3RlbT8gVGhhdCdzIG5vdCBhbiBhcHBsZXMtdG8tYXBwbGVzIHRlc3Qs
IGFuZCAod2hpbGUgdW5saWtlbHkpIHlvdXIgcmVzdWx0cyBjb3VsZCBiZSBkaWZmZXJlbnQgaW4g
cHJvZHVjdGlvbi4NCg0KSWYgKipJKiogd2VyZSBkb2luZyB0aGlzPyBJIHdvdWxkIHNldCB1cCBy
ZXBsaWNhdGlvbiwgZ2V0IGl0IGluIHN5bmMgdG8gbXkgaW50ZXJuYWwgdGVzdCBzZXJ2ZXIsIGJy
ZWFrIHRoZSByZXBsaWNhdGlvbiwgcHJvbW90ZSB0aGUgc2xhdmUgYW5kIHJ1biBteSB0ZXN0cy4N
Cg0KVGhhdCdzIGEgMToxLg0KDQpUaGF0IHdvdWxkIG1ha2UgbWUgdGhlIG1vc3QgY29uZmlkZW50
Lg0KDQoNCi0tLS0tLS0tIE9yaWdpbmFsIG1lc3NhZ2UgLS0tLS0tLS0NCkZyb206IHh1amlhbiA8
amFtZXN4dUBvdXRsb29rLmNvbT4NCkRhdGU6IDEwLzA3LzIwMTYgNDo0NiBQTSAoR01ULTA2OjAw
KQ0KVG86IFNjb3R0IFdoaXRuZXkgPHNjb3R0QGpvdXJueXguY29tPiwgcGdzcWwtYWRtaW5AcG9z
dGdyZXNxbC5vcmcNClN1YmplY3Q6IFJFOiBbQURNSU5dIG9wZW4gZGF0YWJhc2Ugb24gcmVhZCBv
bmx5IGZpbGUgc3lzdGVtDQoNClRoYW5rcyBTY290dC4gRG8geW91IGtub3cgaWYgdGhlcmUgaXMg
YW55IHdheSB0byBzdXBwb3J0IHJlYWQtb25seSBGUz8gZm9yIGluc3RhbmNlIHBhdGNoIG9yIGV4
dGVuc2lvbi4NCg0KSSBub3RpY2VkIHRoZSBlcnJvciBpcyBmcm9tIGZpbGUgbWQuYw0KPT09PT09
PT0NCi8qDQogKiBtZG9wZW4oKSAtLSBPcGVuIHRoZSBzcGVjaWZpZWQgcmVsYXRpb24uDQogKg0K
ICogTm90ZSB3ZSBvbmx5IG9wZW4gdGhlIGZpcnN0IHNlZ21lbnQsIHdoZW4gdGhlcmUgYXJlIG11
bHRpcGxlIHNlZ21lbnRzLg0KICoNCiAqIElmIGZpcnN0IHNlZ21lbnQgaXMgbm90IHByZXNlbnQs
IGVpdGhlciBlcmVwb3J0IG9yIHJldHVybiBOVUxMIGFjY29yZGluZw0KICogdG8gImJlaGF2aW9y
Ii4gIFdlIHRyZWF0IEVYVEVOU0lPTl9DUkVBVEUgdGhlIHNhbWUgYXMgRVhURU5TSU9OX0ZBSUw7
DQogKiBFWFRFTlNJT05fQ1JFQVRFIG1lYW5zIGl0J3MgT0sgdG8gZXh0ZW5kIGFuIGV4aXN0aW5n
IHJlbGF0aW9uLCBub3QgdG8NCiAqIGludmVudCBvbmUgb3V0IG9mIHdob2xlIGNsb3RoLg0KICov
DQpzdGF0aWMgTWRmZFZlYyAqDQptZG9wZW4oU01nclJlbGF0aW9uIHJlbG4sIEZvcmtOdW1iZXIg
Zm9ya251bSwgaW50IGJlaGF2aW9yKQ0KPT09PT09PT0NCg0Kc28gSSBjaGFuZ2VkIHRoZSBmaWxl
IG9wZW4gbW9kZSBmcm9tIE9fUkRXUiB0byBPX1JET05MWQ0KZmQgPSBQYXRoTmFtZU9wZW5GaWxl
KHBhdGgsIE9fUkRPTkxZIHwgUEdfQklOQVJZLCAwNjAwKTsNCg0KYW5kIGl0IHdvcmtzLiBJIGNh
biBvcGVuIHRoZSB1c2VyIGRhdGFiYXNlLCBhbmQgcXVlcnkgdGFibGUgbm93LiBEbyB5b3UgdGhp
bmsgaWYgdGhlcmUgaXMgYW55IHBvdGVudGlhbCBwcm9ibGVtIG9mIHRoaXMgY2hhbmdlPw0KSSBq
dXN0IG5lZWQgdG8gb3BlbiBkYXRhYmFzZSBvbiByZWFkLW9ubHkgZmlsZSBzeXN0ZW0sIGFuZCBu
byBuZWVkIGFueSBjaGFuZ2VzIG9uIHRoZSBjbHVzdGVyLiB0aGFua3MNCg0KSmFtZXMNCg0KDQpf
X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fXw0KRnJvbTogc2NvdHRAam91cm55eC5jb20N
ClRvOiBqYW1lc3h1QG91dGxvb2suY29tOyBwZ3NxbC1hZG1pbkBwb3N0Z3Jlc3FsLm9yZw0KU3Vi
amVjdDogUmU6IFtBRE1JTl0gb3BlbiBkYXRhYmFzZSBvbiByZWFkIG9ubHkgZmlsZSBzeXN0ZW0N
CkRhdGU6IEZyaSwgNyBPY3QgMjAxNiAyMDowNjowOSArMDAwMA0KDQpUbyBteSBrbm93bGVkZ2Us
IGl0IGlzIG5vdCwgbm8uDQoNCg0KVGhlIEZTIG11c3QgYmUgcmVhZC93cml0ZS4NCg0KDQpXaGF0
IEkgd291bGQgZG8gaW4gdGhpcyBzaXR1YXRpb24gaXMgdG8gbWlncmF0ZSBpdCB0byBhIHRlc3Qv
bmV3IHN5c3RlbSB3aGVyZSBubyBvbmUgY2FuIGFjY2VzcyBpdCBhbmQgYnJpbmcgaXQgdXAgci93
Lg0KDQoNCk9yIHNldCB1cCBzdHJlYW1pbmcgcmVwbGljYXRpb24uIFRoYXQgZ2l2ZXMgeW91IHNl
bGVjdC1vbmx5Lg0KDQoNCl9fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fDQpGcm9tOiBw
Z3NxbC1hZG1pbi1vd25lckBwb3N0Z3Jlc3FsLm9yZyA8cGdzcWwtYWRtaW4tb3duZXJAcG9zdGdy
ZXNxbC5vcmc+IG9uIGJlaGFsZiBvZiB4dWppYW4gPGphbWVzeHVAb3V0bG9vay5jb20+DQpTZW50
OiBGcmlkYXksIE9jdG9iZXIgNywgMjAxNiAyOjQzIFBNDQpUbzogcGdzcWwtYWRtaW5AcG9zdGdy
ZXNxbC5vcmcNClN1YmplY3Q6IFtBRE1JTl0gb3BlbiBkYXRhYmFzZSBvbiByZWFkIG9ubHkgZmls
ZSBzeXN0ZW0NCg0KSGksDQogICAgICAgQ291bGQgeW91IHBsZWFzZSBsZXQgbWUga25vdyBpZiBp
dCBpcyBwb3NzaWJsZSB0byBvcGVuIGRhdGFiYXNlIG9uIHJlYWQtb25seSBmaWxlIHN5c3RlbT8N
Cg0KSSBoYXZlIGEgcHNvdGdyZXNxbCA5LjYgY2x1c3RlciwgUEdEQVRBIGZvbGRlciBpcyBvbiBS
RUFELVdSSVRFIHZvbHVtZShzeXN0ZW0gdm9sdW1lKSwgdXNlciBkYXRhYmFzZSBpcyBjcmVhdGVk
IG9uIGEgdGFibGUgc3BhY2Ugd2hpY2ggaXMgbG9jYXRlZCBvbiBhbm90aGVyIHZvbHVtZShkYXRh
IHZvbHVtZSkuIE5vdyB3ZSBzZXQgdGhlIGRhdGEgdm9sdW1lIHRvIHJlYWQgb25seSBtb2RlLCBi
ZWZvcmUgd2UgY2hhbmdlZCB0aGUgbW9kZSwgSSBhbHNvIGRpc2FibGVkIHRoZSBhdXRvIHZhY3V1
bSwgcnVuIGNoZWNrcG9pbnQsDQphbmQgdmFjdXVtIGZyZWUuIFdlIHJlYm9vdCB0aGUgc2Vydmlj
ZSwgZXZlcnl0aGluZyB3YXMgZmluZSB1bnRpbCB3ZSB0cmllZCB0byBhY2Nlc3MgdGhlIGRhdGFi
YXNlLg0KPT09PT09PT09PT09PT09DQpwb3N0Z3Jlcz0jIFxjIHVzZXJkYjsNCkZBVEFMOiAgY291
bGQgbm90IG9wZW4gZmlsZSAicGdfdGJsc3BjLzE2Mzg0L1BHXzkuNl8yMDE2MDgxMzEvMTY0Njgv
MjY2MiI6IFJlYWQtb25seSBmaWxlIHN5c3RlbQ0KPT09PT09PT09PT09PT09DQoNCndlIGVuYWJs
ZWQgdGhlIGRlYnVnIG1vZGUsIGluIGxvZyBmaWxlLCB3ZSBzYXcNCj09PT09PT09PT09PT09PQ0K
RkFUQUw6ICA0MjUwMTogY291bGQgbm90IG9wZW4gZmlsZSAicGdfdGJsc3BjLzE2Mzg0L1BHXzku
Nl8yMDE2MDgxMzEvMTY0NjgvMjY2MiI6IFJlYWQtb25seSBmaWxlIHN5c3RlbQ0KTE9DQVRJT046
ICBtZG9wZW4sIG1kLmM6NjA5DQo9PT09PT09PT09PT09PT0NCg0KRG9lcyBhbnlvbmUga25vdyBp
ZiB0aGVyZSBpcyBhbnl3YXkgdG8gcnVuIGRhdGFiYXNlIG9uIHJlYWQtb25seSBmaWxlIHN5c3Rl
bT8gdGhhbmtzDQoNCkphbWVzDQoNCg0KDQoNCg0KSm91cm55eCwgSW5jLg0KNzYwMCBCdXJuZXQg
Um9hZCAjMzAwDQpBdXN0aW4sIFRYIDc4NzU3DQp3d3cuam91cm55eC5jb20NCg0KcCA1MTIuODM0
Ljg4ODgNCmYgNTEyLTgzNC04ODU4DQoNCkRvIHlvdSByZWNlaXZlIG91ciBwcm9tb3Rpb25hbCBl
bWFpbHM/IFlvdSBjYW4gc3Vic2NyaWJlIG9yIHVuc3Vic2NyaWJlIHRvIHRob3NlIGVtYWlscyBh
dCBodHRwOi8vZ28uam91cm55eC5jb20vZW1haWxQcmVmZXJlbmNlL2UvNDkzMi83MTQvDQo=

--_000_cmqpipd2aeyul04yifqd2dvs1475884481598emailandroidcom_
Content-Type: text/html; charset="gb2312"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dgb2312">
<meta content=3D"text/html; charset=3Dgb2312">
<style>
<!--
.hmmessage p
	{margin:0px;
	padding:0px}
body.hmmessage
	{font-size:12pt;
	font-family:=CE=A2=C8=ED=D1=C5=BA=DA}
-->
</style>
</head>
<body class=3D"hmmessage">
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 a=
pples-to-apples test, and (while unlikely) your results could be different =
in production.
<div><br>
</div>
<div>If **I** were doing this? I would set up replication, get it in sync t=
o my internal test server, break the replication, promote the slave and run=
 my tests.</div>
<div><br>
</div>
<div>That's a 1:1.</div>
<div><br>
</div>
<div>That would make me the most confident.</div>
<br>
<br>
-------- Original message --------<br>
From: xujian &lt;jamesxu@outlook.com&gt; <br>
Date: 10/07/2016 4:46 PM (GMT-06:00) <br>
To: Scott Whitney &lt;scott@journyx.com&gt;, pgsql-admin@postgresql.org <br=
>
Subject: RE: [ADMIN] open database on read only file system <br>
<br>
<div>
<div dir=3D"ltr">Thanks Scott. Do you know if there is any way to support r=
ead-only FS? for instance patch or extension.
<div><br>
</div>
<div>I noticed the error is from file md.c</div>
<div>=3D=3D=3D=3D=3D=3D=3D=3D</div>
<div>
<div>/*</div>
<div>&nbsp;*<span class=3D"Apple-tab-span" style=3D"white-space:pre"> </spa=
n>mdopen() -- Open the specified relation.</div>
<div>&nbsp;*</div>
<div>&nbsp;* Note we only open the first segment, when there are multiple s=
egments.</div>
<div>&nbsp;*</div>
<div>&nbsp;* If first segment is not present, either ereport or return NULL=
 according</div>
<div>&nbsp;* to &quot;behavior&quot;. &nbsp;We treat EXTENSION_CREATE the s=
ame as EXTENSION_FAIL;</div>
<div>&nbsp;* EXTENSION_CREATE means it's OK to extend an existing relation,=
 not to</div>
<div>&nbsp;* invent one out of whole cloth.</div>
<div>&nbsp;*/</div>
<div>static MdfdVec *</div>
<div>mdopen(SMgrRelation reln, ForkNumber forknum, int behavior)</div>
</div>
<div>=3D=3D=3D=3D=3D=3D=3D=3D</div>
<div><br>
</div>
<div>so I changed the file open mode from O_RDWR to O_RDONLY</div>
<div>fd =3D PathNameOpenFile(path, O_RDONLY | PG_BINARY, 0600);</div>
<div><br>
</div>
<div>and it works. I can open the user database, and query table now. Do yo=
u think if there is any potential problem of this change?</div>
<div>I just need to open database on read-only file system, and no need any=
 changes on the cluster. thanks</div>
<div><br>
</div>
<div>James</div>
<br>
<br>
<div>
<hr id=3D"stopSpelling">
From: scott@journyx.com<br>
To: jamesxu@outlook.com; pgsql-admin@postgresql.org<br>
Subject: Re: [ADMIN] open database on read only file system<br>
Date: Fri, 7 Oct 2016 20:06:09 &#43;0000<br>
<br>
<style style=3D"display:none">
<!--
-->
</style>
<div id=3D"ecxdivtagdefaultwrapper" style=3D"font-size:12pt; color:#000000;=
 font-family:Calibri,Arial,Helvetica,sans-serif">
To my knowledge, it is not, no.<br>
<br>
<br>
The FS must be read/write.<br>
<br>
<br>
What I would do in this situation is to migrate it to a test/new system whe=
re no one can access it and bring it up r/w.<br>
<br>
<br>
Or set up streaming replication. That gives you select-only.<br>
<br>
<br>
<div style=3D"color:rgb(0,0,0)">
<hr tabindex=3D"-1" style=3D"display:inline-block; width:98%">
<div id=3D"ecxdivRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif"=
 color=3D"#000000" style=3D"font-size:11pt"><b>From:</b> pgsql-admin-owner@=
postgresql.org &lt;pgsql-admin-owner@postgresql.org&gt; on behalf of xujian=
 &lt;jamesxu@outlook.com&gt;<br>
<b>Sent:</b> Friday, October 7, 2016 2:43 PM<br>
<b>To:</b> pgsql-admin@postgresql.org<br>
<b>Subject:</b> [ADMIN] open database on read only file system</font>
<div>&nbsp;</div>
</div>
<div>
<div dir=3D"ltr">Hi,
<div>&nbsp; &nbsp; &nbsp; &nbsp;Could you please let me know if it is possi=
ble to open database on read-only file system?</div>
<div><br>
</div>
<div>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).&nbsp;<span style=3D"font-size:12pt">Now we=
 set the data volume to read only mode,
 before we changed the mode, I also disabled the auto&nbsp;</span>vacuum<sp=
an style=3D"font-size:12pt">, run checkpoint,&nbsp;</span></div>
<div><span style=3D"font-size:12pt">and&nbsp;</span>vacuum<span style=3D"fo=
nt-size:12pt">&nbsp;free. We reboot the service, everything was fine&nbsp;<=
/span>until<span style=3D"font-size:12pt">&nbsp;we tried to access the data=
base.</span></div>
<div><span style=3D"font-size:12pt">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D</span></div>
<div>
<div>postgres=3D# \c userdb;</div>
<div>FATAL: &nbsp;could not open file &quot;pg_tblspc/16384/PG_9.6_20160813=
1/16468/2662&quot;: Read-only file system</div>
<div>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</div>
<div><br>
</div>
<div>we enabled the debug mode, in log file, we saw</div>
<div>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</div>
<div>
<div>FATAL: &nbsp;42501: could not open file &quot;pg_tblspc/16384/PG_9.6_2=
01608131/16468/2662&quot;: Read-only file system</div>
<div>LOCATION: &nbsp;mdopen, md.c:609</div>
</div>
<div>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</div>
<div><br>
</div>
<div>Does anyone know if there is anyway to run database on read-only file =
system? thanks</div>
<div><br>
</div>
<div>James</div>
<div><br>
</div>
<div><br>
</div>
<div style=3D"font-size:12pt"><br>
</div>
</div>
</div>
</div>
</div>
</div>
<div style=3D""><br>
<div><br>
</div>
<div><span></span>
<div><span style=3D"">Journyx, Inc.</span></div>
<div>7600 Burnet Road #300 <br>
Austin, TX 78757 <br>
www.journyx.com <br>
</div>
<div><br>
</div>
<div>
<div><span style=3D"">p 512.834.8888&nbsp;</span></div>
<div>f 512-834-8858&nbsp;</div>
<div><br>
</div>
</div>
<div>Do you receive our promotional emails? You can subscribe or unsubscrib=
e to those emails at http://go.journyx.com/emailPreference/e/4932/714/&nbsp=
;<br>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>

--_000_cmqpipd2aeyul04yifqd2dvs1475884481598emailandroidcom_--