Command to prune archive at restartpoints
in [PgSql]
|
From: Robert Haas on 13 Jun 2010 12:31 On Sat, Jun 12, 2010 at 4:51 PM, Dimitri Fontaine <dfontaine(a)hi-media.com> wrote: > Dimitri Fontaine <dfontaine(a)hi-media.com> writes: >> Also, should I try to send a patch implementing my proposal (internal >> command exposed as a function at the SQL level, and while at it, maybe >> the internal command "pg_archive_bypass" to mimic /usr/bin/true as an >> archive_command)? > > I had to have a try at it, even if quick and dirty. I've not tried to > code the pg_archive_bypass internal command for lack of discussion, but > I still think it would be great to have it. > > So here's a "see my idea in code" patch, that put the previous code by > Simon into a backend function. As the goal was not to adapt the existing > code intended as external to use the internal APIs, you'll find it quite > ugly I'm sure. > > For example, this #define XLOG_DATA_FNAME_LEN has to go away, but that > won't help having the idea accepted or not, and as I'm only warming up, > I didn't tackle the problem. If you want me to do it, I'd appreciate > some guidance as how to, though. > > It goes like this: > > dim=# select pg_switch_xlog(); > �pg_switch_xlog > ---------------- > �0/1000098 > (1 row) > > dim=# select pg_archive_cleanup('0/1000098'); > DEBUG: �removing "pg_xlog/000000010000000000000000" > DEBUG: �removing "pg_xlog/000000010000000000000001" > �pg_archive_cleanup > -------------------- > �t > (1 row) > > I hope you too will find this way of interfacing is easier to deal with > for everybody (from code maintenance to user settings). I'm a bit perplexed here. The archive cleanup has to run on the standby, not the master, right? Whereas pg_switch_xlog() can only run on the master. The purpose of making this a standalone executable is so that people who have, for example, multiple standbys, can customize the logic without having to hack the backend. Pushing this into the backend would defeat that goal; plus, it wouldn't be usable at all for people who aren't running Hot Standby. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Dimitri Fontaine on 13 Jun 2010 13:04 Robert Haas <robertmhaas(a)gmail.com> writes: > I'm a bit perplexed here. The archive cleanup has to run on the > standby, not the master, right? Whereas pg_switch_xlog() can only run > on the master. I used it just to show a possible use case, easy to grasp. Sorry if that's confusing instead. > The purpose of making this a standalone executable is > so that people who have, for example, multiple standbys, can customize > the logic without having to hack the backend. Pushing this into the > backend would defeat that goal; plus, it wouldn't be usable at all for > people who aren't running Hot Standby. In the simple cases, what you want to be able to easily choose is just the first XLOG file you're NOT cleaning. And this is the only argument you give the function. So you can either use the backend function as your internal command for archive cleanup, or use a script that choose where to stop cleaning then call it with that as an argument (it's SQL callable). What it does is unlink the file. If that behavior doesn't suit you, it's still possible to use an external command and tune some already proposed scripts. I just don't see how an external binary has more to offer than a backend function here. It's more code to maintain, it's harder to setup for people, and if it does not suit you, you still have to make you own script but you can not use what we ship easily (you have to get the sources and code in C for that). What I'm after is being able to tell people to just setup a GUC to a given value, not to copy/paste a (perl or bash) script from the docs, make it executable under their system, then test it and run it in production. We can do better than that, and it's not even hard. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 13 Jun 2010 14:25 On Sun, Jun 13, 2010 at 1:04 PM, Dimitri Fontaine <dfontaine(a)hi-media.com> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> I'm a bit perplexed here. �The archive cleanup has to run on the >> standby, not the master, right? �Whereas pg_switch_xlog() can only run >> on the master. > > I used it just to show a possible use case, easy to grasp. Sorry if > that's confusing instead. > >> �The purpose of making this a standalone executable is >> so that people who have, for example, multiple standbys, can customize >> the logic without having to hack the backend. �Pushing this into the >> backend would defeat that goal; plus, it wouldn't be usable at all for >> people who aren't running Hot Standby. > > In the simple cases, what you want to be able to easily choose is just > the first XLOG file you're NOT cleaning. And this is the only argument > you give the function. > > So you can either use the backend function as your internal command for > archive cleanup, or use a script that choose where to stop cleaning then > call it with that as an argument (it's SQL callable). > > What it does is unlink the file. If that behavior doesn't suit you, it's > still possible to use an external command and tune some already proposed > scripts. I just don't see how an external binary has more to offer than > a backend function here. It's more code to maintain, it's harder to > setup for people, and if it does not suit you, you still have to make > you own script but you can not use what we ship easily (you have to get > the sources and code in C for that). > > What I'm after is being able to tell people to just setup a GUC to a > given value, not to copy/paste a (perl or bash) script from the docs, > make it executable under their system, then test it and run it in > production. We can do better than that, and it's not even hard. We're not going to make them cut/paste anything from the docs. We're going to provide a production-ready executable they can just use, which should be installed (presumably, already with the correct permissions) by their packaging system if they install postgresql-contrib or the equivalent. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Dimitri Fontaine on 13 Jun 2010 14:51 Robert Haas <robertmhaas(a)gmail.com> writes: >> Robert Haas <robertmhaas(a)gmail.com> writes: >>> The purpose of making this a standalone executable is >>> so that people who have, for example, multiple standbys, can customize >>> the logic without having to hack the backend. Pushing this into the >>> backend would defeat that goal; plus, it wouldn't be usable at all for >>> people who aren't running Hot Standby. > > We're not going to make them cut/paste anything from the docs. We're > going to provide a production-ready executable they can just use, > which should be installed (presumably, already with the correct > permissions) by their packaging system if they install > postgresql-contrib or the equivalent. I still run against people not wanting to trust contrib. I still read here from time to time that contrib's chapter is maintaining working examples of extensibility, not maintaining production ready add-ons. Other than that, you proposed something flexible and easy to customize, and you end up with an executable binary that will only offer one behavior (unlink), the only option is where to stop (%r). The backend function I'm proposing uses the same option, but is easier to call from a script, should you need to customize. You don't even have to run the script locally or remember where is the XLOG directory of that instance. You could operate over a JDBC connection, e.g. I now realize that my proposal ain't helping if Streaming Replication is filling the standby's pg_xlog and hot_standby = off. I don't remember that SR rebuilds pg_xlog on the standby though, does it? The proposed script will only cleanup XLOGDIR in fact, so if you use a common archive elsewhere then you still need some external command not provided by the project. So we still need the script example in the docs. I think that the pg_archivecleanup binary is a good solution, all the more if not shipped in contrib, but that the SQL callable function is better. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Dunstan on 13 Jun 2010 15:03
Dimitri Fontaine wrote: > I still read > here from time to time that contrib's chapter is maintaining working > examples of extensibility, not maintaining production ready add-ons. > > Even if this were true, and I don't believe it is, ISTM the solution would be to have a utility command alongside the other utility commands like pg_controldata. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |