Thread: Cluster feature: Start/stop archiving at runtime
Since I didn't hear anything about "API into the Parser / Parser as an independent module", I just wrote my own summary of what I think the idea was there is and am moving on. I'll try to track down more info at PG East next month when I run into some of the people I think care about this. Next up is "Start/stop archiving at runtime". First set of questions: -Is this referring to the current PITR archiving as done via archive_command, does it intend to cover a wider scope than that, or is "archiving" being used in a general replication sense instead not related to that? -What is the issue with Skype and failed nodes this is alluding to? -Pg-2 is mentioned as having something related to this; story? -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
Greg Smith escribió: > Since I didn't hear anything about "API into the Parser / Parser as an > independent module", I just wrote my own summary of what I think the > idea was there is and am moving on. I'll try to track down more info > at PG East next month when I run into some of the people I think care > about this. > > Next up is "Start/stop archiving at runtime". First set of questions: > > -Is this referring to the current PITR archiving as done via > archive_command, does it intend to cover a wider scope than that, or > is "archiving" being used in a general replication sense instead not > related to that? > > -What is the issue with Skype and failed nodes this is alluding to? > > -Pg-2 is mentioned as having something related to this; story? > > What happend with Skype and its nodes? It´s very interested for me because I use too the Skytools for many HA solutions with PostgreSQL. Where I can find the code/status developmet of Pg-2? Greg, Will you talk about this on the PgEast 2010? Regards to all -- -------------------------------------------------------------------------------- "Para ser realmente grande, hay que estar con la gente, no por encima de ella." Montesquieu Ing. Marcos Luís Ortíz Valmaseda -------------------------------------------------------- -- Database Architect/Administrator -- -- PostgreSQL RDBMS -- -- http://www.postgresql.org -- -- Data WareHouse -- Business Intelligence Apprentice -- -- Linux User # 418229 -- -------------------------------------------------------- **************************************************************************** ** Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD) ** ** Contacto: ** ** Correo: centalad@uci.cu ** ** Telf: +53 07-837-3737 ** ** +53 07-837-3714 ** ** ** ** Universidad de las Ciencias Informáticas ** ** http://www.uci.cu ** ****************************************************************************
> Since I didn't hear anything about "API into the Parser / Parser as an > independent module", I just wrote my own summary of what I think the > idea was there is and am moving on. I'll try to track down more info at > PG East next month when I run into some of the people I think care about > this. I have took a look at your description. That is totally different from what I thought. Statement replication and/or load balancing middle ware need to know SQL statement is SELECT or others. If it is a (read only) SELECT, it can be routed to standby node of Streaming replication clusters for example. For this purpose, such a middle ware ought to be able to parse SQL. If PostgreSQL provide its raw parser as a C library, it will be very usefull for such middle ware since they do not need to re-implement their own SQL parser (as pgpool-II already did). > Next up is "Start/stop archiving at runtime". First set of questions: > > -Is this referring to the current PITR archiving as done via > archive_command, does it intend to cover a wider scope than that, or is > "archiving" being used in a general replication sense instead not > related to that? Pgpool-II is capable to re-sync a DB node while pgpool-II and PostgreSQL are running. It is called "on line recovery". This is done by taking a base backup and WAL archiving. After successfull completeion of on line backup, it does not need continuous WAL archiving anymore. Therefore what I need is, set archive_mode to off without restarting PostgreSQL. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp > -What is the issue with Skype and failed nodes this is alluding to? > > -Pg-2 is mentioned as having something related to this; story? > > > -- > Greg Smith 2ndQuadrant US Baltimore, MD > PostgreSQL Training, Services and Support > greg@2ndQuadrant.com www.2ndQuadrant.us > > > -- > Sent via pgsql-cluster-hackers mailing list (pgsql-cluster-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-cluster-hackers
Tatsuo Ishii wrote: >> Since I didn't hear anything about "API into the Parser / Parser as an >> independent module", I just wrote my own summary of what I think the >> idea was there is and am moving on. I'll try to track down more info at >> PG East next month when I run into some of the people I think care about >> this. >> > > I have took a look at your description. That is totally different from > what I thought. Statement replication and/or load balancing middle > ware need to know SQL statement is SELECT or others. If it is a (read > only) SELECT, it can be routed to standby node of Streaming > replication clusters for example. For this purpose, such a middle ware > ought to be able to parse SQL. If PostgreSQL provide its raw parser as > a C library, it will be very usefull for such middle ware since they > do not need to re-implement their own SQL parser (as pgpool-II already > did). > > I never claimed I really understand what entry was alluding to and just made the first wild guess that came to mind. I'll be happy to replace it with the alternate explanation you suggested for what it was intended to address. The part I still don't see is how these two comments in that section fit into what you're describing here: * statement based replication need to replay certain constructs with CONSTANT values you provide * Figure out which you need to replace... quite difficult Those are the parts I was basing my guess as to the intended application here on. If this is just intended to improve relaying SELECT statements over to another node, why would you need to replace anything with constants or other values? -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
Ing . Marcos Luís Ortíz Valmaseda wrote: > What happend with Skype and its nodes? It´s very interested for me > because I use too the Skytools for > many HA solutions with PostgreSQL. It sounds like Skype has situations where it would prefer to pause archiving when nodes go down, am just looking for more details about why that is. > Where I can find the code/status developmet of Pg-2? http://wiki.postgresql.org/wiki/Postgres-2 gives an overview. > Greg, Will you talk about this on the PgEast 2010? I'm doing a talk at PG East 2010, but not on this topic. The main talk list is up: http://www.postgresqlconference.org/2010/east/talks and there are a few replication-related ones on there. The main reason I mentioned it is that East tends to have a few of the Slony developers floating around, and I had the impression it was the app that wanted this parser feature. It looks like I was wrong about that. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
> Tatsuo Ishii wrote: > >> Since I didn't hear anything about "API into the Parser / Parser as an > >> independent module", I just wrote my own summary of what I think the > >> idea was there is and am moving on. I'll try to track down more info at > >> PG East next month when I run into some of the people I think care about > >> this. > >> > > > > I have took a look at your description. That is totally different from > > what I thought. Statement replication and/or load balancing middle > > ware need to know SQL statement is SELECT or others. If it is a (read > > only) SELECT, it can be routed to standby node of Streaming > > replication clusters for example. For this purpose, such a middle ware > > ought to be able to parse SQL. If PostgreSQL provide its raw parser as > > a C library, it will be very usefull for such middle ware since they > > do not need to re-implement their own SQL parser (as pgpool-II already > > did). > > > > > > I never claimed I really understand what entry was alluding to and just > made the first wild guess that came to mind. I'll be happy to replace > it with the alternate explanation you suggested for what it was intended > to address. The part I still don't see is how these two comments in > that section fit into what you're describing here: > > * statement based replication need to replay certain constructs with > CONSTANT values you provide > * Figure out which you need to replace... quite difficult I guess these are talking about server internally generated values: i.e. CURRENT_TIMESTAMP, sequence, oid, random() and so on (Pgpool-II recently solved problems with CURRENT_TIMESTAMP and large object oid BTW). To solve the problem we need a SQL parser to find out certain constructs needed to be replaced with CONSTANT. > Those are the parts I was basing my guess as to the intended application > here on. If this is just intended to improve relaying SELECT statements > over to another node, why would you need to replace anything with > constants or other values? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Based on the feedback here, http://wiki.postgresql.org/wiki/ClusterFeatures now has what seems like a reasonably correct description of "API into the Parser / Parser as an independent module", and I wrote up a first pass for "Start/stop archiving at runtime" too--that code I know the internals of, just needed a bit more context. The spots marked with ? are specific details I think could use more notes, but at least the basic outline of the features each make sense to me now. I'll keep looking for updates about these two for the rest of the week, so if you have suggestions keep them coming. In the first month of working on this in a more organized way, we've gotten reasonable starting descriptions for the first 4 items on this 14 item long list. If we just keep up that feature/week pace, by the PGCon meeting the whole list should just be filled in. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
Greg, Great! Thanks! -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp > Based on the feedback here, > http://wiki.postgresql.org/wiki/ClusterFeatures now has what seems like > a reasonably correct description of "API into the Parser / Parser as an > independent module", and I wrote up a first pass for "Start/stop > archiving at runtime" too--that code I know the internals of, just > needed a bit more context. The spots marked with ? are specific details > I think could use more notes, but at least the basic outline of the > features each make sense to me now. > > I'll keep looking for updates about these two for the rest of the week, > so if you have suggestions keep them coming. In the first month of > working on this in a more organized way, we've gotten reasonable > starting descriptions for the first 4 items on this 14 item long list. > If we just keep up that feature/week pace, by the PGCon meeting the > whole list should just be filled in. > > -- > Greg Smith 2ndQuadrant US Baltimore, MD > PostgreSQL Training, Services and Support > greg@2ndQuadrant.com www.2ndQuadrant.us >
Greg Smith wrote: > Next up is "Start/stop archiving at runtime". First set of questions: > > -Is this referring to the current PITR archiving as done via > archive_command, does it intend to cover a wider scope than that, or is > "archiving" being used in a general replication sense instead not > related to that? > > -What is the issue with Skype and failed nodes this is alluding to? > AFAIK the only issue is that we cannot set archive_mode without restarting Postgres. This leads to archive_mode always set to "on" and a dummy archive_command being used. The background for this is that we don't know in advance when we need to create a PITR clone of a particular database. regards, Martin
Greg Smith escribió: > Ing . Marcos Luís Ortíz Valmaseda wrote: >> What happend with Skype and its nodes? It´s very interested for me >> because I use too the Skytools for >> many HA solutions with PostgreSQL. > > It sounds like Skype has situations where it would prefer to pause > archiving when nodes go down, am just looking for more details about > why that is. > > >> Where I can find the code/status developmet of Pg-2? > > http://wiki.postgresql.org/wiki/Postgres-2 gives an overview. > >> Greg, Will you talk about this on the PgEast 2010? > > I'm doing a talk at PG East 2010, but not on this topic. The main > talk list is up: http://www.postgresqlconference.org/2010/east/talks > and there are a few replication-related ones on there. > > The main reason I mentioned it is that East tends to have a few of the > Slony developers floating around, and I had the impression it was the > app that wanted this parser feature. It looks like I was wrong about > that. > I just think that this idea for the parser is awesome, they should look and discuss the idea with you and Tatsuo Ishii that has great thoughs about that. The work done in PgPool-II is awesome, we use it here too for pymes that want to adopt PostgreSQL for its business. If these features was included on the parser like a library apart, many things can be reproduced with that, PgPool-II have had allways with VOLATILE functions, this could change that. I´m looking potential project where give my help for POstgreSQL; wich I use since 2003. I want to work on the total SQL/MED implementation on PostgreSQL, and the other project that I have looked is Postgres-2. Soon, you ´ll have news about our work on Cuba about all this. Regards to all -- -------------------------------------------------------------------------------- "Para ser realmente grande, hay que estar con la gente, no por encima de ella." Montesquieu Ing. Marcos Luís Ortíz Valmaseda -------------------------------------------------------- -- Database Architect/Administrator -- -- PostgreSQL RDBMS -- -- http://www.postgresql.org -- -- Data WareHouse -- Business Intelligence Apprentice -- -- Linux User # 418229 -- -------------------------------------------------------- **************************************************************************** ** Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD) ** ** Contacto: ** ** Correo: centalad@uci.cu ** ** Telf: +53 07-837-3737 ** ** +53 07-837-3714 ** ** ** ** Universidad de las Ciencias Informáticas ** ** http://www.uci.cu ** ****************************************************************************
On Sun, Feb 28, 2010 at 9:08 PM, Martin Pihlak <martin.pihlak@gmail.com> wrote: > Greg Smith wrote: >> Next up is "Start/stop archiving at runtime". First set of questions: >> >> -Is this referring to the current PITR archiving as done via >> archive_command, does it intend to cover a wider scope than that, or is >> "archiving" being used in a general replication sense instead not >> related to that? >> >> -What is the issue with Skype and failed nodes this is alluding to? >> > > AFAIK the only issue is that we cannot set archive_mode without restarting > Postgres. This leads to archive_mode always set to "on" and a dummy > archive_command being used. The background for this is that we don't know > in advance when we need to create a PITR clone of a particular database. Changing archive_command and reloading the postgresql.conf (signaling a SIGHUP) is not sufficient for you? You only need more user-friendly API to do that? Or more intelligent functionality? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
> On Sun, Feb 28, 2010 at 9:08 PM, Martin Pihlak <martin.pihlak@gmail.com> wrote: > > Greg Smith wrote: > >> Next up is "Start/stop archiving at runtime". First set of questions: > >> > >> -Is this referring to the current PITR archiving as done via > >> archive_command, does it intend to cover a wider scope than that, or is > >> "archiving" being used in a general replication sense instead not > >> related to that? > >> > >> -What is the issue with Skype and failed nodes this is alluding to? > >> > > > > AFAIK the only issue is that we cannot set archive_mode without restarting > > Postgres. This leads to archive_mode always set to "on" and a dummy > > archive_command being used. The background for this is that we don't know > > in advance when we need to create a PITR clone of a particular database. > > Changing archive_command and reloading the postgresql.conf (signaling > a SIGHUP) is not sufficient for you? You only need more user-friendly > API to do that? Or more intelligent functionality? IMO it's not sufficient. According to the docs: "If archive_command is an empty string (the default) while archive_mode is enabled, WAL archiving is temporarily disabled, but the server continues to accumulate WAL segment files in the expectation that a command will soon be provided. Setting archive_mode to a command that does nothing but return true, e.g. /bin/true, effectively disables archiving, but also breaks the chain of WAL files needed for archive recovery, so it should only be used in unusual circumstances" My interpretation of this paragraph is, if we set archive_command to empty string, PostgreSQL accumulates all WAL under pg_xlog. i.e. no WAL resuing occurs. Maybe set archive_command to /bin/true is enough for pgpool-II, but it needs to rewrite postgresql.conf by pgpool. That may make users to feel uncomfortable, I'm afraid. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Tue, Mar 2, 2010 at 4:42 PM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote: > My interpretation of this paragraph is, if we set archive_command to > empty string, PostgreSQL accumulates all WAL under pg_xlog. i.e. no > WAL resuing occurs. Maybe set archive_command to /bin/true is enough > for pgpool-II, but it needs to rewrite postgresql.conf by pgpool. That > may make users to feel uncomfortable, I'm afraid. I understood that what is needed here is the capability to start/stop archiving without changing any configuration file. Instead of reloading the conf file, what API do you want? Controlling archiving via system catalog like pg_autovacuum (though it's obsolete) is sufficient? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
> On Tue, Mar 2, 2010 at 4:42 PM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote: > > My interpretation of this paragraph is, if we set archive_command to > > empty string, PostgreSQL accumulates all WAL under pg_xlog. i.e. no > > WAL resuing occurs. Maybe set archive_command to /bin/true is enough > > for pgpool-II, but it needs to rewrite postgresql.conf by pgpool. That > > may make users to feel uncomfortable, I'm afraid. > > I understood that what is needed here is the capability to start/stop > archiving without changing any configuration file. Instead of reloading > the conf file, what API do you want? Controlling archiving via system > catalog like pg_autovacuum (though it's obsolete) is sufficient? I prefer to use functions. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp