Thread: 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@nttdata.co.jp> OpenSource Development Center, NTT DATA Corp. http://www.nttdata.co.jp
Satoshi Nagayasu <nagayasus@nttdata.co.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@nttdata.co.jp> OpenSource Development Center, NTT DATA Corp. http://www.nttdata.co.jp/
Satoshi Nagayasu <nagayasus@nttdata.co.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@nttdata.co.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@nttdata.co.jp> OpenSource Development Center, NTT DATA Corp. http://www.nttdata.co.jp diff -ru postgresql-8.0.1.orig/src/backend/executor/execMain.c postgresql-8.0.1/src/backend/executor/execMain.c --- postgresql-8.0.1.orig/src/backend/executor/execMain.c 2005-01-15 02:53:33.000000000 +0900 +++ postgresql-8.0.1/src/backend/executor/execMain.c 2005-03-21 13:12:22.000000000 +0900 @@ -43,6 +43,7 @@ #include "optimizer/clauses.h" #include "optimizer/var.h" #include "parser/parsetree.h" +#include "postmaster/postmaster.h" #include "utils/acl.h" #include "utils/guc.h" #include "utils/lsyscache.h" @@ -127,7 +128,7 @@ * If the transaction is read-only, we need to check if any writes are * planned to non-temporary tables. */ - if (XactReadOnly && !explainOnly) + if ( (XactReadOnly || ReadOnlyCluster) && !explainOnly) ExecCheckXactReadOnly(queryDesc->parsetree); /* diff -ru postgresql-8.0.1.orig/src/backend/postmaster/postmaster.c postgresql-8.0.1/src/backend/postmaster/postmaster.c --- postgresql-8.0.1.orig/src/backend/postmaster/postmaster.c 2005-01-13 01:38:17.000000000 +0900 +++ postgresql-8.0.1/src/backend/postmaster/postmaster.c 2005-03-21 13:21:17.000000000 +0900 @@ -236,6 +236,8 @@ extern int optreset; #endif +bool ReadOnlyCluster = false; + /* * postmaster.c - function prototypes */ @@ -440,7 +442,7 @@ opterr = 1; - while ((opt = getopt(argc, argv, "A:a:B:b:c:D:d:Fh:ik:lm:MN:no:p:Ss-:")) != -1) + while ((opt = getopt(argc, argv, "A:a:B:b:c:D:d:Fh:ik:lm:MN:no:p:rSs-:")) != -1) { switch (opt) { @@ -515,6 +517,9 @@ case 'p': SetConfigOption("port", optarg, PGC_POSTMASTER, PGC_S_ARGV); break; + case 'r': + SetConfigOption("readonly_cluster", "true", PGC_POSTMASTER, PGC_S_ARGV); + break; case 'S': /* diff -ru postgresql-8.0.1.orig/src/backend/tcop/utility.c postgresql-8.0.1/src/backend/tcop/utility.c --- postgresql-8.0.1.orig/src/backend/tcop/utility.c 2005-01-25 02:46:29.000000000 +0900 +++ postgresql-8.0.1/src/backend/tcop/utility.c 2005-03-21 13:13:45.000000000 +0900 @@ -47,6 +47,7 @@ #include "parser/parse_expr.h" #include "parser/parse_type.h" #include "postmaster/bgwriter.h" +#include "postmaster/postmaster.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rewriteRemove.h" #include "storage/fd.h" @@ -265,7 +266,7 @@ static void check_xact_readonly(Node *parsetree) { - if (!XactReadOnly) + if (!XactReadOnly && !ReadOnlyCluster) return; /* diff -ru postgresql-8.0.1.orig/src/backend/utils/misc/guc.c postgresql-8.0.1/src/backend/utils/misc/guc.c --- postgresql-8.0.1.orig/src/backend/utils/misc/guc.c 2005-01-01 14:43:08.000000000 +0900 +++ postgresql-8.0.1/src/backend/utils/misc/guc.c 2005-03-21 13:06:42.000000000 +0900 @@ -851,6 +851,15 @@ #endif }, + { + {"readonly_cluster", PGC_POSTMASTER, UNGROUPED, + gettext_noop("Enables the postmaster read-only."), + NULL + }, + &ReadOnlyCluster, + false, NULL, NULL + }, + /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL diff -ru postgresql-8.0.1.orig/src/include/postmaster/postmaster.h postgresql-8.0.1/src/include/postmaster/postmaster.h --- postgresql-8.0.1.orig/src/include/postmaster/postmaster.h 2005-01-01 07:03:39.000000000 +0900 +++ postgresql-8.0.1/src/include/postmaster/postmaster.h 2005-03-21 13:03:16.000000000 +0900 @@ -34,6 +34,7 @@ extern HANDLE PostmasterHandle; #endif +extern bool ReadOnlyCluster; extern int PostmasterMain(int argc, char *argv[]); extern void ClosePostmasterPorts(bool am_syslogger);