Re: read-only database

Hi all,

I've read TODO list in the 8.0.1,
then I'm interested in the following topic.

> * Allow a warm standby system to also allow read-only queries

Does anyone have any plan to work on this?

I think we need to extend the pg_database catalog to
have a database state (read-only or writable),
and also need to extend ALTER DATABASE command
to change the state.

To make a database read-only, ALTER DATABASE command may change
MyDatabaseReadOnly flag (like XactReadOnly)
and update the pg_database catalog.
If MyDatabaseReadOnly is true, check_xact_readonly() or
ExecCheckXactReadOnly() will prevent from updating the database.

Otherwise, I think we can also prevent from updating the database
around acquiring the XLogInsert lock.

Any ideas? or any suggestions?

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp

Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> writes:
>> * Allow a warm standby system to also allow read-only queries

> Does anyone have any plan to work on this?

> I think we need to extend the pg_database catalog to
> have a database state (read-only or writable),
> and also need to extend ALTER DATABASE command
> to change the state.

Uh, no, because changing that would by definition not be a read-only
operation. Therefore there'd be no way to enter the read-only state,
and definitely no way to get out of it again. Furthermore, the
envisioned behavior is cluster-wide not per-database: the point is
to not execute transactions and not generate WAL entries, and you
don't get to be selective about that. (If it doesn't work like that,
you couldn't use it for the intended purpose of examining the state
of a hot-standby PITR backup that is actively tracking WAL logs
shipped from a master. It'd also not be useful for looking at
a corrupted cluster.)

I'd view this as a postmaster state that propagates to backends.
Probably you'd enable it by means of a postmaster option, and the
only way to get out of it is to shut down and restart the postmaster
without the option.

regards, tom lane

Tom Lane wrote:
> Uh, no, because changing that would by definition not be a read-only
> operation. Therefore there'd be no way to enter the read-only state,
> and definitely no way to get out of it again.

I saw Oracle's reference manual, and found ALTER DATABASE OPEN READ ONLY command
to make a stand-by database.

In Oracle, when the user execute the command, the database goes read-only mode.
Is this a bad idea?

I guess some users need per-database read-only state.
Don't we need to have both read-only and writable databases in single cluster?

Of course, the super-user can change the database state even in read-only.

> Furthermore, the
> envisioned behavior is cluster-wide not per-database: the point is
> to not execute transactions and not generate WAL entries, and you
> don't get to be selective about that. (If it doesn't work like that,
> you couldn't use it for the intended purpose of examining the state
> of a hot-standby PITR backup that is actively tracking WAL logs
> shipped from a master. It'd also not be useful for looking at
> a corrupted cluster.)
>
> I'd view this as a postmaster state that propagates to backends.
> Probably you'd enable it by means of a postmaster option, and the
> only way to get out of it is to shut down and restart the postmaster
> without the option.

I agree this is a reasonable way to make cluster-wide read-only state.

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/

Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> writes:
> Tom Lane wrote:
>> Uh, no, because changing that would by definition not be a read-only
>> operation. Therefore there'd be no way to enter the read-only state,
>> and definitely no way to get out of it again.

> I saw Oracle's reference manual, and found ALTER DATABASE OPEN READ ONLY command
> to make a stand-by database.

Perhaps, but that's *not* what the TODO item is about.

regards, tom lane

Tom Lane wrote:
>>I saw Oracle's reference manual, and found ALTER DATABASE OPEN READ ONLY command
>>to make a stand-by database.
>
> Perhaps, but that's *not* what the TODO item is about.

I see.

Thanks for comments.
--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/

On Thu, 2005-03-17 at 12:40 +0900, Satoshi Nagayasu wrote:
> Tom Lane wrote:
> >>I saw Oracle's reference manual, and found ALTER DATABASE OPEN READ ONLY command
> >>to make a stand-by database.
> >
> > Perhaps, but that's *not* what the TODO item is about.
>
> I see.
>
> Thanks for comments.

The Oracle ALTER DATABASE command is part of the multi-step process to
mount an Oracle database into a running instance. It isn't something
that you can dip in and out of when you choose, as far as I am aware:
you can only run it on a warm standby database that is receiving logs
shipped from another database.
i.e. Oracle warm-standby databases have 3 run states (ok, more, but...)
1. warm standby
2. read-only
3. fully-operational
You can switch between 1 and 2 and back again, but once you go to 3 then
you cannot switch back.

What Tom just proposed about having an additional run-state between
running-recovery and fully-operational is essentially the same thing.
Tom's overall strategy would be workable in PostgreSQL terms, with some
work and would provide similar functionality to Oracle.

Having said that, I believe we're not close to making that work just yet
- its a good end goal because there may be choices along the way that
may need to consider the longer term goal.

Best Regards, Simon Riggs

Tom Lane wrote:
> I'd view this as a postmaster state that propagates to backends.
> Probably you'd enable it by means of a postmaster option, and the
> only way to get out of it is to shut down and restart the postmaster
> without the option.

I've created a patch to make a postmaster read-only.
(attached patch can be applied to 8.0.1)

Read-only state can be enabled/disabled by the postmaster option,
or the postgresql.conf option.

If you start the postmaster with "-r" options,
the cluster will go to read-only.

% pg_ctl -o "-i -r" -D $PGDATA start

Or if you set "readonly_cluster = true" in the postgresql.conf,
the cluster will also become read-only.

Any comments?
--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp

Satoshi Nagayasu wrote:
>
> Tom Lane wrote:
> > I'd view this as a postmaster state that propagates to backends.
> > Probably you'd enable it by means of a postmaster option, and the
> > only way to get out of it is to shut down and restart the postmaster
> > without the option.
>
> I've created a patch to make a postmaster read-only.
> (attached patch can be applied to 8.0.1)
>
> Read-only state can be enabled/disabled by the postmaster option,
> or the postgresql.conf option.
>
> If you start the postmaster with "-r" options,
> the cluster will go to read-only.
>
> % pg_ctl -o "-i -r" -D $PGDATA start
>
> Or if you set "readonly_cluster = true" in the postgresql.conf,
> the cluster will also become read-only.

Nice idea. I have attached a new patch which has a few adjustments.

First, we are moving away from using postmaster flags, and instead
encouraging people to use postgresql.conf, so I removed the -r flag but
added an entry in postgresql.conf for this. I can see why it might be
nice to have it as a postmaster flag, but at a certain point the number
of flags gets too confusing so postgresql.conf is better. Second, I
changed it so it can be modified by a sighup to the postmaster, which
can't be done with a postmaster flag.

Also, I renamed it to server_read_only because that seems more
consistent than readonly_cluster.

Also, I added documentation for this postgresql.conf variable.

With this change, I see we now have three read_only options:

transaction_read_only
default_transaction_read_only
server_read_only

The first one is not documented (should it be?) and I assume allows you
to query and change the READ ONLY status of a single transaction, while
default_transaction_read_only affects all new transactions for the
session, and server_read_only is for all transactions on the server.

It seems server_read_only is the same as default_transaction_read_only
except it can't be changed. It seems more like a secure version of
default_transaction_read_only rather than something new.

If we set default_transaction_read_only to true in postgresql.conf,
could we just prevent that from being changed by a session. As I
remember we have abandoned the idea of trying to limit session changes
to postgresql.conf values so maybe this is the way we have to go.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> It seems server_read_only is the same as default_transaction_read_only
> except it can't be changed.

I thought the TODO item was for a low-level read-only option, suitable
for trying to look at a corrupted database or run off a read-only volume.
This is very far from being that --- it allows temp table creation/use,
and it still eats transaction IDs so it is certainly not read-only to
xlog or clog.

I am not sure I see any use case for this implementation: it is
read-only enough to get in your way, without being read-only enough
to derive any real benefit.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > It seems server_read_only is the same as default_transaction_read_only
> > except it can't be changed.
>
> I thought the TODO item was for a low-level read-only option, suitable
> for trying to look at a corrupted database or run off a read-only volume.
> This is very far from being that --- it allows temp table creation/use,
> and it still eats transaction IDs so it is certainly not read-only to
> xlog or clog.
>
> I am not sure I see any use case for this implementation: it is
> read-only enough to get in your way, without being read-only enough
> to derive any real benefit.

I am not sure I see the use case either but I developed it so everyone
could look at it and decide if it is useful. When true, it is basically
a unchangable default_transaction_read_only.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

I think the read-only has two meanings for the user.

First is the internal state. XID, OID or something like that.
In these cases, the internal state mustn't be changed.
Some users will need the read-only for internal state.

Second is read-only for the user data contents.
In some cases, the user want to make the user data as read-only.
For this purpose, the user doesn't care XID or OID, I guess.

So, we can implement them in different way.
I think both are necessary.

Bruce Momjian wrote:
> Tom Lane wrote:
>
>>Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>>
>>>It seems server_read_only is the same as default_transaction_read_only
>>>except it can't be changed.
>>
>>I thought the TODO item was for a low-level read-only option, suitable
>>for trying to look at a corrupted database or run off a read-only volume.
>>This is very far from being that --- it allows temp table creation/use,
>>and it still eats transaction IDs so it is certainly not read-only to
>>xlog or clog.
>>
>>I am not sure I see any use case for this implementation: it is
>>read-only enough to get in your way, without being read-only enough
>>to derive any real benefit.
>
>
> I am not sure I see the use case either but I developed it so everyone
> could look at it and decide if it is useful. When true, it is basically
> a unchangable default_transaction_read_only.
>

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/

Satoshi Nagayasu <nagayasus(at)nttdata(dot)co(dot)jp> writes:
> I think the read-only has two meanings for the user.
> First is the internal state. XID, OID or something like that.
> In these cases, the internal state mustn't be changed.
> Some users will need the read-only for internal state.

> Second is read-only for the user data contents.
> In some cases, the user want to make the user data as read-only.
> For this purpose, the user doesn't care XID or OID, I guess.

> So, we can implement them in different way.
> I think both are necessary.

Indeed, but we already have a implementation of the second form, in
a reasonably spec-compliant fashion. The TODO item concerns the first
form, which is something that the current system cannot do at all.

regards, tom lane

On Mon, May 09, 2005 at 09:02:07AM +0900, Satoshi Nagayasu wrote:
> I think the read-only has two meanings for the user.
>
> First is the internal state. XID, OID or something like that.
> In these cases, the internal state mustn't be changed.
> Some users will need the read-only for internal state.
>
> Second is read-only for the user data contents.
> In some cases, the user want to make the user data as read-only.
> For this purpose, the user doesn't care XID or OID, I guess.
>
> So, we can implement them in different way.
> I think both are necessary.

But the second is only a subset of the first, no? So why not just
implement the first? Put another way, why do you think the second is
necessary?

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
Thou shalt check the array bounds of all strings (indeed, all arrays), for
surely where thou typest "foo" someone someday shall type
"supercalifragilisticexpialidocious" (5th Commandment for C programmers)

> But the second is only a subset of the first, no? So why not just
> implement the first? Put another way, why do you think the second is
> necessary?

Because there is "default_transaction_read_only" option and
implementation.

My implementation is an extension of the existing option.

I wanted to make the postmaster read-only, and found
"default_transaction_read_only" option, but it can be overwritten.

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/

Satoshi Nagayasu wrote:
> I wanted to make the postmaster read-only, and found
> "default_transaction_read_only" option, but it can be overwritten.

I mean it can be overridden by the user. I don't want that.

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/

Satoshi Nagayasu wrote:
>
> Satoshi Nagayasu wrote:
> > I wanted to make the postmaster read-only, and found
> > "default_transaction_read_only" option, but it can be overwritten.
>
> I mean it can be overridden by the user. I don't want that.

I understand, but we haven't gotten enough requests from people for a
new option that can't be overridden.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Mon, May 09, 2005 at 10:13:22PM -0400, Bruce Momjian wrote:
> Satoshi Nagayasu wrote:
> >
> > Satoshi Nagayasu wrote:
> > > I wanted to make the postmaster read-only, and found
> > > "default_transaction_read_only" option, but it can be overwritten.
> >
> > I mean it can be overridden by the user. I don't want that.
>
> I understand, but we haven't gotten enough requests from people for a
> new option that can't be overridden.

The ability to have PGDATA in read-only media (like CDs) has been
requested a lot of times, hasn't it?

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"Porque francamente, si para saber manejarse a uno mismo hubiera que
rendir examen... ¿Quién es el machito que tendría carnet?" (Mafalda)

Alvaro Herrera wrote:
> On Mon, May 09, 2005 at 10:13:22PM -0400, Bruce Momjian wrote:
> > Satoshi Nagayasu wrote:
> > >
> > > Satoshi Nagayasu wrote:
> > > > I wanted to make the postmaster read-only, and found
> > > > "default_transaction_read_only" option, but it can be overwritten.
> > >
> > > I mean it can be overridden by the user. I don't want that.
> >
> > I understand, but we haven't gotten enough requests from people for a
> > new option that can't be overridden.
>
> The ability to have PGDATA in read-only media (like CDs) has been
> requested a lot of times, hasn't it?

Right. I am saying the idea of having a GUC that acts like
"default_transaction_read_only" but can't be changed isn't something
that has been requested frequently.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> The ability to have PGDATA in read-only media (like CDs) has been
> requested a lot of times, hasn't it?

It's come up a few times ... more than an un-overridable read-only mode
anyway. Also, I should think that those who want a secure read-only
mode want it enforced selectively --- for instance, assuredly read-only
for some users but not others. I can hardly see any use case for the
patch as proposed; it seems to have all the disadvantages of a low-level
read-only mode (eg, not selective) without any of the advantages.

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> > The ability to have PGDATA in read-only media (like CDs) has been
> > requested a lot of times, hasn't it?
>
> It's come up a few times ... more than an un-overridable read-only mode
> anyway. Also, I should think that those who want a secure read-only
> mode want it enforced selectively --- for instance, assuredly read-only
> for some users but not others. I can hardly see any use case for the
> patch as proposed; it seems to have all the disadvantages of a low-level
> read-only mode (eg, not selective) without any of the advantages.

Having removed our security for not allowing override of things like
log_statement, it seems we need a more general capability for
controlling how something can be set that no one can change.

One nify trick would be to use '=' in postgresql.conf for things that
can be over-ridden by the user, and ':=' for values that can not be
changed. I do think we need that functionality for a variety of
purposes.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Having removed our security for not allowing override of things like
> log_statement, it seems we need a more general capability for
> controlling how something can be set that no one can change.

The initial implementation was definitely pretty broken, but I agree
we should try again.

I think that transaction_read_only and default_transaction_read_only
are a special case: they embody our implementation of SQL-spec-mandated
features (SET TRANSACTION READ ONLY and friends), and so any messing
about with them has to surmount the objection that it'll be breaking
spec-mandated behavior. But the other things we wanted this for in
the past, such as logging control, were outside the scope of the spec
AFAIR.

regards, tom lane

Bruce Momjian wrote:
>>It's come up a few times ... more than an un-overridable read-only mode
>>anyway. Also, I should think that those who want a secure read-only
>>mode want it enforced selectively --- for instance, assuredly read-only
>>for some users but not others. I can hardly see any use case for the
>>patch as proposed; it seems to have all the disadvantages of a low-level
>>read-only mode (eg, not selective) without any of the advantages.

Our company has some PostgreSQL replication systems
for our customers. I need to switch the database state between
read-only and writable for recovering or maintenance.

As I mentioned before, I wanted to the read-only database mode.
It is the per-database state.

http://archives.postgresql.org/pgsql-hackers/2005-03/msg00540.php

However, if it is not provided, we have to find alternative way
to get our purpose.

So I'm still looking for how to make the (user) database as read-only.

--
NAGAYASU Satoshi <nagayasus(at)nttdata(dot)co(dot)jp>
OpenSource Development Center,
NTT DATA Corp. http://www.nttdata.co.jp/

> As I mentioned before, I wanted to the read-only database mode.
> It is the per-database state.
>
> http://archives.postgresql.org/pgsql-hackers/2005-03/msg00540.php
>
> However, if it is not provided, we have to find alternative way
> to get our purpose.
>
> So I'm still looking for how to make the (user) database as read-only.
>

Mammoth PostgreSQL Replicator could do this. If you set a database to a
slave and tell it to be a slave for all tables it would be read only.

Sincerely,

Joshua D. Drake
Command Prompt, Inc.

On E, 2005-05-09 at 20:21 -0700, Joshua D. Drake wrote:
> > As I mentioned before, I wanted to the read-only database mode.
> > It is the per-database state.
> >
> > http://archives.postgresql.org/pgsql-hackers/2005-03/msg00540.php
> >
> > However, if it is not provided, we have to find alternative way
> > to get our purpose.
> >
> > So I'm still looking for how to make the (user) database as read-only.
> >
>
> Mammoth PostgreSQL Replicator could do this. If you set a database to a
> slave and tell it to be a slave for all tables it would be read only.

Would it still not have the same issues as Slony (one can create new
tables) this patch (one can create temp tables) ?

--
Hannu Krosing <hannu(at)tm(dot)ee>