Re: [HACKERS] psql readline win32
Well, we should *at least* provide it from the source build. Since it does work (with a small kludge, but it does work). Me, I'm not fully happy with psql on win32. I want my tab completion! (which the gui tools don't do either, from what I can tell. At least pgadmin doesn't. Yet.) Yeah, I am not against doing so and having more features is always cool! But I just suspect if we can afford the cost. Actually I tried a little bit on the thrysoee version you mentioned: $ ./config.guess i686-pc-mingw32 $./configure ... configure: error: libtermcap, libcurses or libncurses are required! This may give us a hint that port is not very easy though. You can get pdcurses to get past that step. But it's still not easy. See for example http://www.coldie.net/node/131. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stats collector performance improvement
On Mon, 2006-01-02 at 16:48 -0500, Tom Lane wrote: The two compromises that were made in the original stats design to make it fast were (1) stats updates lag behind reality, and (2) some updates may be missed entirely. Now that we have a couple of years' field experience with the code, it seems that (1) is acceptable for real usage but (2) not so much. We decided that the stats update had to occur during execution, in case the statement aborted and row versions were not notified. That means we must notify things as they happen, yet could use a reliable queuing system that could suffer a delay in the stats becoming available. But how often do we lose a backend? Could we simply buffer that a little better? i.e. don't send message to stats unless we have altered at least 10 rows? So we would buffer based upon the importance of the message, not the actual size of the message. That way singleton-statements won't generate the same stats traffic, but we risk losing a buffers worth of row changes should we crash - everything would still work if we lost a few small row change notifications. We can also save lots of cycles on the current statement overhead, which is currently the worst part of the stats, performance-wise. That definitely needs redesign. AFAICS we only ever need to know the SQL statement via the stats system if the statement has been running for more than a few minutes - the main use case is for an admin to be able to diagnose a rogue or hung statement. Pushing the statement to stats every time is just a big overhead. That suggests we should either have a pull or a deferred push (longer-than-X-secs) approach. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] WAL bypass for INSERT, UPDATE and DELETE?
On Thu, 2005-12-22 at 12:12 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Currently, CTAS optimization requires a heap_sync during ExecEndPlan. It would be easy enough to extend this so that it also works for INSERT, UPDATE and DELETE. If you tried to do it that way you'd break the system completely. Not all updates go through the executor. I think it's a bad idea anyway; you'd be adding overhead to the lowest level routines in order to support a feature that would be very seldom used, at least in comparison to the number of times those routines are executed. The current thinking seems to be that we should implement an ALTER TABLE RELIABILITY statement that applies to COPY, INSERT, UPDATE and DELETE. If you tried to do it that way you'd break the system completely. Not all updates go through the executor. Where would I put a heap_sync to catch all of the I, U, D cases? (Possibly multiple places). Or were you thinking of things like ALTER TABLE TYPE? Or perhaps inheritance? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Thu, Dec 29, 2005 at 11:24:28AM -0500, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Andrew Dunstan wrote: I an horribly scared that this will be used as a performance boost for normal use. I would at least like to see some restrictions that make it harder to mis-use. Perhaps restrict to superuser? Certainly restrict to table owner. I can see the argument for superuser-only: decisions about data integrity tradeoffs should be reserved to the DBA, who is the one who will get blamed if the database loses data, no matter how stupid his users are. But I'm not wedded to that. I could live with table-owner. I dislike restricting to super-user, and to some extent even table owner. The reason is that if you have some automated batch process, you don't want that process running as a superuser. Also, it is often awkward to require that the user running that batch own the table. I'd much rather see this as a grantable permission on the table. (The same is true with truncate, btw). This way, if a DBA knew he could trust a specific role, he could allow for these operations on a specific table. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Fri, Dec 30, 2005 at 12:58:15PM -0500, Bruce Momjian wrote: Andrew Dunstan wrote: My concern is more about making plain that this is for special operations, not normal operations. Or maybe I have misunderstood the purpose. Rephrase that as full ownership rights must be obtained to load data in a way that requires dropping any existing indexes and locking out other users of the table. I don't think the use-case for this will be very large for non-owners, or indeed even for owners except during initial table creation; and so I don't think the above argument is strong. Those restrictions aren't true of Bruce's proposed drop and delete/truncate recovery modes, are they? Only the owner could do the ALTER, for sure, but once the owner sets it, any user with permission to write to the table would have those characteristics. Dumb question: if the ALTER is done inside a transaction, and then reverted at the end of the transaction, does that mean that no other transactions would have those permissions? I think the general use-case is that you only one the session doing the ALTER to be able to use these special modes, not anyone else who happens to be hitting the table at that time... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Permissions vs SERIAL columns
If nothing else, this should at least be documented in
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
On Fri, Dec 30, 2005 at 07:32:18PM +0100, Magnus Hagander wrote:
Haven't seen this discussed in a while, but I do recall it being
mentioned sometime before...
The problem:
testdb=# create table mytable (id serial, txt text);
testdb=# grant insert on mytable to user2;
GRANT
testdb=# \connect testdb user2
You are now connected to database testdb as user user2.
testdb= insert into mytable (txt) values ('foobar');
ERROR: permission denied for sequence mytable_id_seq
What I'd like to happen is for the grant for INSERT on the table to
cascade into an UPDATE permission on the sequence (when associated with
a SERIAL column only, of course).
Coming from a different database, such as MSSQL, makes people forget
this very easily, and it becomes very annoying.
Is this something that can be done without too much work? Anything you
can do in current pg even, just me not knowing how?
//Magnus
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
On Sun, Jan 01, 2006 at 09:03:00PM +0100, Andreas Pflug wrote: Marc G. Fournier wrote: On Sun, 1 Jan 2006, Tom Lane wrote: I was reminded of $subject by http://archives.postgresql.org/pgsql-admin/2006-01/msg2.php While I haven't tried it, I suspect that allowing a DNS host name would take little work (basically removing the AI_NUMERICHOST flag passed to getaddrinfo in hba.c). There was once a good reason not to allow it: slow DNS lookups would lock up the postmaster. But now that we do this work in an already-forked backend, with an overall timeout that would catch any indefinite blockage, I don't see a good reason why we shouldn't let people use DNS names. Thoughts? Security? I'd bet most pg_hba.conf entries will be (private) networks, not hosts. Since private networks defined in DNS are probably quite rare, only few people could benefit. Those who *do* define specific host entries, are probably quite security aware. They might find DNS safe for their purposes, but they'd probably like a function that shows the resulting hba entries after DNS resolution. I don't know if the normal DNS libraries allow this, but it would be cool if you could specify that an entry in pg_hba.conf could be looked up from /etc/hosts, but not from generic DNS. AFAIK that would eliminate the possibility of spoofing. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
* Jim C. Nasby ([EMAIL PROTECTED]) wrote: I dislike restricting to super-user, and to some extent even table owner. The reason is that if you have some automated batch process, you don't want that process running as a superuser. Also, it is often awkward to require that the user running that batch own the table. The owner of the table could be a role which the batch runner is part of (along with whatever other roles you wish to have 'owner'-level permissions on the table). I'd much rather see this as a grantable permission on the table. (The same is true with truncate, btw). This way, if a DBA knew he could trust a specific role, he could allow for these operations on a specific table. In general, I do prefer that permissions be seperably grantable. Being able to grant 'truncate' permissions would be really nice. Is the only reason such permission doesn't exist due to no one working on it, or is there other disagreement about it? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Jim C. Nasby [EMAIL PROTECTED] writes: Dumb question: if the ALTER is done inside a transaction, and then reverted at the end of the transaction, does that mean that no other transactions would have those permissions? I think the general use-case is that you only one the session doing the ALTER to be able to use these special modes, not anyone else who happens to be hitting the table at that time... Such an ALTER would certainly require exclusive lock on the table, so I'm not sure that I see much use-case for doing it like that. You'd want to do the ALTER and commit so as not to lock other people out of the table entirely while doing the bulk data-pushing. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Add a Known Issues section
On Sun, Jan 01, 2006 at 08:37:02PM -0500, Tom Lane wrote: Qingqing Zhou [EMAIL PROTECTED] writes: On Sun, 1 Jan 2006, Tom Lane wrote: Aren't they all known issues? You need to be a lot clearer about what distinction you intend to draw, and why it's so important that it deserves to be the principal classification metric for TODO. ... However, there is blur border line between them ... I don't think we want the top-level division of TODO to be a classification that is inherently in-the-eye-of-the-beholder. There would be way too much time wasted arguing what goes where, to little purpose --- because, quite frankly, whether someone else thinks XYZ is an issue has nothing to do with whether any given developer is going to spend time on it tomorrow. A good test might be: would a release be held because of this item (assuming it was introduced by something in that release). BTW, a specific example that comes to mind is cluster disobeying MVCC. IIRC that results in data integrity issues, and I suspect that it would end up holding a release. ISTM this should be a high priority item because of the data integrity issue. It might be useful to pick up the postgresql gotchas list that's out on the net someplace, and expand and maintain it as a resource oriented mainly at new users: here are some things you might not have expected to behave like that. I don't think this should have anything directly to do with TODO though. That is an interesting idea, though since the author of that list is already doing the work to maintain it, maybe we just point people there (which has the bonus of letting them see that there's a much larger MySQL gotchas list...) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Stephen Frost [EMAIL PROTECTED] writes: In general, I do prefer that permissions be seperably grantable. Being able to grant 'truncate' permissions would be really nice. Is the only reason such permission doesn't exist due to no one working on it, or is there other disagreement about it? Lack of appetite for having forty nonstandard kinds of privilege, I suppose ;-) Given that we now have roles, it's fairly easy to grant table owner to trusted people, so the use-case for special privilege types has dropped off dramatically IMHO. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stats collector performance improvement
On Tue, Jan 03, 2006 at 09:40:53AM +, Simon Riggs wrote: On Mon, 2006-01-02 at 16:48 -0500, Tom Lane wrote: We can also save lots of cycles on the current statement overhead, which is currently the worst part of the stats, performance-wise. That definitely needs redesign. AFAICS we only ever need to know the SQL statement via the stats system if the statement has been running for more than a few minutes - the main use case is for an admin to be able to diagnose a rogue or hung statement. Pushing the statement to stats every time is just a big overhead. That suggests we should either have a pull or a deferred push (longer-than-X-secs) approach. I would argue that minutes is too long, but of course this could be user-adjustable. I suspect that even waiting just a second could be a huge win, since this only matters if you're executing a lot of statements and you won't be doing that if those statements are taking more than a second or two to execute. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Tue, Jan 03, 2006 at 11:29:02AM -0500, Tom Lane wrote: Stephen Frost [EMAIL PROTECTED] writes: In general, I do prefer that permissions be seperably grantable. Being able to grant 'truncate' permissions would be really nice. Is the only reason such permission doesn't exist due to no one working on it, or is there other disagreement about it? Lack of appetite for having forty nonstandard kinds of privilege, I suppose ;-) Given that we now have roles, it's fairly easy to grant table owner to trusted people, so the use-case for special privilege types has dropped off dramatically IMHO. Yeah, I hadn't thought about that. I agree; if you trust some process enough to have MVCC-affecting rights then you should be able to trust it with full ownership rights. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Tue, Jan 03, 2006 at 11:26:51AM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Dumb question: if the ALTER is done inside a transaction, and then reverted at the end of the transaction, does that mean that no other transactions would have those permissions? I think the general use-case is that you only one the session doing the ALTER to be able to use these special modes, not anyone else who happens to be hitting the table at that time... Such an ALTER would certainly require exclusive lock on the table, so I'm not sure that I see much use-case for doing it like that. You'd want to do the ALTER and commit so as not to lock other people out of the table entirely while doing the bulk data-pushing. Maybe this just isn't clear, but would EXCLUSIVE block writes from all other sessions then? The post I replied to mentioned that the ALTER would affect all backends is why I'm wondering... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Jim C. Nasby [EMAIL PROTECTED] writes: On Tue, Jan 03, 2006 at 11:26:51AM -0500, Tom Lane wrote: Such an ALTER would certainly require exclusive lock on the table, so I'm not sure that I see much use-case for doing it like that. You'd want to do the ALTER and commit so as not to lock other people out of the table entirely while doing the bulk data-pushing. Maybe this just isn't clear, but would EXCLUSIVE block writes from all other sessions then? I don't think it should (which implies that EXCLUSIVE is a bad name). My point is that ALTER RELIABILITY would have to gain exclusive lock for long enough to change the table's reliability marking --- you have to synchronize such a change with other transactions' activity on the table, and table-level locks are the only mechanism we have for that. It's not different from a schema change such as adding a column. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: In general, I do prefer that permissions be seperably grantable. Being able to grant 'truncate' permissions would be really nice. Is the only reason such permission doesn't exist due to no one working on it, or is there other disagreement about it? Lack of appetite for having forty nonstandard kinds of privilege, I suppose ;-) Given that we now have roles, it's fairly easy to grant table owner to trusted people, so the use-case for special privilege types has dropped off dramatically IMHO. The problem is that you might want to grant 'truncate' to people who *aren't* particularly trusted. For truncate, at least I have a real-world use-case for it. I've got a number of users who are required to fill in on the order of 300 seperate tables. We provide a number of different ways of doing the uploads: ODBC phppgadmin Our own phppgadmin-like interface Web-based streaming CSV uploader The CSV uploader is by far the most popular because it allows them to easily reload a table from the files they have. The uploader starts off with a 'delete from x' right now. I've been looking at (but don't particularly like) writing a setuid pl/pgsql function so that I can make the uploader be able to truncate the tables. This would almost entirely eliminate the need to do vacuums on the tables. It's very rare for there to be multiple things happening on a given database at a given time too. What does happen though, is that these tables are often used immediately after they've been uploaded which means they needs to be analyzed too. Again, something which would be very nice if the uploader could do. Same with vacuums, in fact, if the uploader could vacuum the tables I probably wouldn't need truncate as badly (though it'd still be nice), since I could just delete from table; vacuum; What these users *can't* do, by any means, is drop tables, or change the structure or types or keys or anything else having to do with the table definitions. Writing setuid pl/pgsql functions for each of these is circumventing the ACL and permission system of the database; working *around* it instead of *with* it, which makes me somewhat nervous and feels like a lack in the database. :/ Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
--- Jim C. Nasby [EMAIL PROTECTED] escreveu: I don't know if the normal DNS libraries allow this, but it would be cool if you could specify that an entry in pg_hba.conf could be looked up from /etc/hosts, but not from generic DNS. AFAIK that would eliminate the possibility of spoofing. Take a look at 'man /etc/host.conf'. Euler Taveira de Oliveira euler[at]yahoo_com_br ___ Yahoo! doce lar. Faça do Yahoo! sua homepage. http://br.yahoo.com/homepageset.html ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
* Jim C. Nasby ([EMAIL PROTECTED]) wrote: Yeah, I hadn't thought about that. I agree; if you trust some process enough to have MVCC-affecting rights then you should be able to trust it with full ownership rights. About that, I have to disagree. :) I've got a case where this isn't true, see my other post please. Giving someone truncate rights is *very* different from allowing them to drop a table or change the definition of the table. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Tue, Jan 03, 2006 at 11:26:51AM -0500, Tom Lane wrote: Such an ALTER would certainly require exclusive lock on the table, so I'm not sure that I see much use-case for doing it like that. You'd want to do the ALTER and commit so as not to lock other people out of the table entirely while doing the bulk data-pushing. Maybe this just isn't clear, but would EXCLUSIVE block writes from all other sessions then? I don't think it should (which implies that EXCLUSIVE is a bad name). Agreed, EXCLUSIVE was used to mean an _exclusive_ writer. The new words I proposed were PRESERVE or STABLE. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Euler Taveira de Oliveira wrote: --- Jim C. Nasby [EMAIL PROTECTED] escreveu: I don't know if the normal DNS libraries allow this, but it would be cool if you could specify that an entry in pg_hba.conf could be looked up from /etc/hosts, but not from generic DNS. AFAIK that would eliminate the possibility of spoofing. Take a look at 'man /etc/host.conf'. That won't work for per application settings. I think this is a non starter. I have been thinking more about possible real world use cases for this facility. I suspect they will be comparatively rare. In cases where you don't trust DNS you shouldn't use it, and in cases where you do you probably know the address(es) anyway. If the change is simple it's worth doing, but it's not a huge leap. The biggest wrinkle will probably be handling names that map to multiple addresses. One thing that bothers me slightly is that we would need to look up each name (at least until we found a match) for each connection. If you had lots of names in your pg_hba.conf that could be quite a hit. We need to test this not with one but with a couple of hundred names, maybe, to see what the hit is like. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Stephen Frost [EMAIL PROTECTED] writes: The problem is that you might want to grant 'truncate' to people who *aren't* particularly trusted. For truncate, at least I have a real-world use-case for it. I don't find this use-case particularly convincing. If the users are allowed to delete all data in a given table, then that table must be dedicated to them anyway; so it's not that easy to see why you can't risk giving them ownership rights on it. The worst they can do is screw up their own data, no? In any case, I don't see what's so wrong with the model of using SECURITY DEFINER interface functions when you want a security restriction that's finer-grain than the system provides. I really *don't* want to see us trying to, say, categorize every variety of ALTER TABLE as a separately grantable privilege. I could live with something like a catchall ADMIN privilege ... except it's not clear how that would differ from ownership. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Andrew Dunstan [EMAIL PROTECTED] writes: One thing that bothers me slightly is that we would need to look up each name (at least until we found a match) for each connection. If you had lots of names in your pg_hba.conf that could be quite a hit. A possible answer to that is to *not* look up the names from pg_hba.conf, but instead restrict the feature to matching the reverse-DNS name of the client. This limits the cost to one lookup per connection instead of N (and it'd be essentially free if you have log_hostnames turned on, since we already do that lookup in that case). I'm not sure about the relative usefulness of this compared to the forward-lookup case, nor whether it's riskier or less risky from a spoofing point of view. But something to consider. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
On Tue, Jan 03, 2006 at 12:43:03PM -0500, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: One thing that bothers me slightly is that we would need to look up each name (at least until we found a match) for each connection. If you had lots of names in your pg_hba.conf that could be quite a hit. A possible answer to that is to *not* look up the names from pg_hba.conf, but instead restrict the feature to matching the reverse-DNS name of the client. This limits the cost to one lookup per connection instead of N (and it'd be essentially free if you have log_hostnames turned on, since we already do that lookup in that case). I'm not sure about the relative usefulness of this compared to the forward-lookup case, nor whether it's riskier or less risky from a spoofing point of view. But something to consider. I think it's riskier. I have my own PTR records, that I can make be whatever I wish without any authority verifying that my actions are proper. Although, most people don't, this is because most people don't ask for them, or don't know how or where to ask for them. The security benefit is in the address, not in the name. The convenience is in the name, and not the address. I'm not seeing why forward lookups are bad. There are several options available to controlling this, including configuring /etc/hosts to be searched first, before DNS, using nsswitch.conf. Another option, is to use your own DNS server, with its own zone records, such that /etc/resolv.conf uses 127.1, which will serve my own records, before doing a recursive lookup over the network. It's not a big deal. I'd recommend keeping documentation, and inlined comment warnings all over the place - but if people want to use names, I'm not seeing the problem. They can already hang themselves, by granting full access to the Internet, by network mask? :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Tom Lane schrieb: Andrew Dunstan [EMAIL PROTECTED] writes: One thing that bothers me slightly is that we would need to look up each name (at least until we found a match) for each connection. If you had lots of names in your pg_hba.conf that could be quite a hit. A possible answer to that is to *not* look up the names from pg_hba.conf, but instead restrict the feature to matching the reverse-DNS name of the client. This limits the cost to one lookup per connection instead of N (and it'd be essentially free if you have log_hostnames turned on, since we already do that lookup in that case). Or alternatively (documented) scan and translate the names only on restart or sighup. This would limit the overhead and changes to the confile-scanner only and would at least enable symbolic names in the config files. (Of course w/o any wildcards - that would be the drawback) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
[EMAIL PROTECTED] writes: On Tue, Jan 03, 2006 at 12:43:03PM -0500, Tom Lane wrote: I'm not sure about the relative usefulness of this compared to the forward-lookup case, nor whether it's riskier or less risky from a spoofing point of view. But something to consider. I think it's riskier. I have my own PTR records, that I can make be whatever I wish without any authority verifying that my actions are proper. Yeah, that occurred to me after a few moments' thought. We could do one extra forward lookup to confirm that the reverse-lookup name maps back to the IP address. It's not a big deal. Depends on how many names you want to put into pg_hba.conf. I don't offhand see a use-case for very many, but maybe there is one. Even if there are a lot, they'd not be expensive to look up if there is a local nameserver that is authoritative for those names ... which I'd think would be the normal case. The more outside names you've got in pg_hba.conf, the more open you are to spoofing. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
The issue is folks that DON'T set reverse DNS, I.E. have generic rDNS set on their IP's. I've seen (in my ISP days, and on my mailserver) LOTS of folks that can't/won't update Their rDNS, even though it's a STATICLY assigned address. And, as an example, my house IP changes when the PPPoE moves, and I have a DynDns.org Hostname that changes to support that, as well as a CNAME out of my domain to Point to it. Just more things to think about. -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.459.1309 Email: [EMAIL PROTECTED] Web: www.pervasive.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Tuesday, January 03, 2006 11:43 AM To: Andrew Dunstan Cc: Euler Taveira de Oliveira; Jim C. Nasby; Andreas Pflug; Marc G. Fournier; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf? Andrew Dunstan [EMAIL PROTECTED] writes: One thing that bothers me slightly is that we would need to look up each name (at least until we found a match) for each connection. If you had lots of names in your pg_hba.conf that could be quite a hit. A possible answer to that is to *not* look up the names from pg_hba.conf, but instead restrict the feature to matching the reverse-DNS name of the client. This limits the cost to one lookup per connection instead of N (and it'd be essentially free if you have log_hostnames turned on, since we already do that lookup in that case). I'm not sure about the relative usefulness of this compared to the forward-lookup case, nor whether it's riskier or less risky from a spoofing point of view. But something to consider. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
* Tom Lane ([EMAIL PROTECTED]) wrote: I don't find this use-case particularly convincing. If the users are allowed to delete all data in a given table, then that table must be dedicated to them anyway; so it's not that easy to see why you can't risk giving them ownership rights on it. The worst they can do is screw up their own data, no? Being able to delete all data in a given table in no way implies ownership rights. The tables are part of a specification which the users are being asked to respond to. Being able to change the table types or remove the constraints put on the tables would allow the users to upload garbage which would then affect downstream processing. We can't guarentee this won't happen anyway but we try to confine the things they can mess up to a reasonable set which we can check for (and do, through a rather involved error checking system). There are *alot* of things built on top of the table structures and having them change would basically break the whole system (without the appropriate changes being made to the other parts of the system). In any case, I don't see what's so wrong with the model of using SECURITY DEFINER interface functions when you want a security restriction that's finer-grain than the system provides. I really *don't* want to see us trying to, say, categorize every variety of ALTER TABLE as a separately grantable privilege. I could live with something like a catchall ADMIN privilege ... except it's not clear how that would differ from ownership. I don't think anyone's asked for 'ALTER TABLE' privileges to be seperately grantable. It seems to me that the privileges which *need* to be grantable are ones associated with DML statements. I would classify TRUNCATE, VACUUM and ANALYZE as DML statements (along with select, insert, update, and delete). They're PostgreSQL-specific DML statements but they still fall into that category. I don't think it's a coincidence that the SQL-defined DML statements are all, individually, grantable. That doesn't mean I think we should get rid of RULE, REFERENCES or TRIGGER, though honestly I've very rarely needed to grant any of them (I don't think I've ever granted RULE or TRIGGER...). References is DDL-oriented, but for *other* tables; RULE and TRIGGER are DDL and I can't really justify why someone other than the owner would need them but I'm guessing someone's using them. I don't think their existance should imply that if we ever change the grants again we have to include all types of 'ALTER TABLE', etc, though. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] HOOKS for Synchronous Replication?
I fixed the patch and now it compiles in windows. The first one did not compile because there were some problems in the Makefile. It is currently available for download at http://gorda.di.uminho.pt/community/pgsqlhooks/. Regards, Alfranio ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] cvs tip - stats buffer process consuming 100% cpu
I just noticed that the stats buffer process is consuming 100% cpu as soon as a backend is started, and continues after that backend is ended: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 15150 postgres 25 0 27004 948 508 S 99.9 0.0 0:30.97 postmaster # ps -ef |grep 15150 postgres 15150 15143 78 11:29 pts/300:00:38 postgres: stats buffer process postgres 15151 15150 0 11:29 pts/300:00:00 postgres: stats collector process (gdb) bt #0 0x00383b8c2633 in __select_nocancel () from /lib64/libc.so.6 #1 0x0055e896 in PgstatBufferMain (argc=Variable argc is not available. ) at pgstat.c:1921 #2 0x0055f73b in pgstat_start () at pgstat.c:614 #3 0x00562fda in reaper (postgres_signal_arg=Variable postgres_signal_arg is not available. ) at postmaster.c:2175 #4 signal handler called #5 0x00383b8c2633 in __select_nocancel () from /lib64/libc.so.6 #6 0x00560d0f in ServerLoop () at postmaster.c:1180 #7 0x00562443 in PostmasterMain (argc=7, argv=0x88df20) at postmaster.c:943 #8 0x005217fe in main (argc=7, argv=0x88df20) at main.c:263 I noticed a recent discussion on the stats collector -- is this related to a recent change? Joe ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] cvs tip - stats buffer process consuming 100% cpu
Interesting. Here is the patch I just applied:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/pgstat.c.diff?r1=1.116r2=1.117
The only guess I have is that select() is modifying the timeout
structure on return, but I didn't think it did that, does it?
Googling shows Linux does modify the structure (see bottom):
http://groups.google.com/group/comp.unix.programmer/browse_frm/thread/a53c7c4a71cb48e5/5f0bbcc9fe0230a2?lnk=stq=select+timeout+modifyrnum=9#5f0bbcc9fe0230a2
so I will fix the code accordingly. Patch attached and applied.
---
Joe Conway wrote:
I just noticed that the stats buffer process is consuming 100% cpu as
soon as a backend is started, and continues after that backend is ended:
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
15150 postgres 25 0 27004 948 508 S 99.9 0.0 0:30.97 postmaster
# ps -ef |grep 15150
postgres 15150 15143 78 11:29 pts/300:00:38 postgres: stats buffer
process
postgres 15151 15150 0 11:29 pts/300:00:00 postgres: stats
collector process
(gdb) bt
#0 0x00383b8c2633 in __select_nocancel () from /lib64/libc.so.6
#1 0x0055e896 in PgstatBufferMain (argc=Variable argc is not
available.
) at pgstat.c:1921
#2 0x0055f73b in pgstat_start () at pgstat.c:614
#3 0x00562fda in reaper (postgres_signal_arg=Variable
postgres_signal_arg is not available.
) at postmaster.c:2175
#4 signal handler called
#5 0x00383b8c2633 in __select_nocancel () from /lib64/libc.so.6
#6 0x00560d0f in ServerLoop () at postmaster.c:1180
#7 0x00562443 in PostmasterMain (argc=7, argv=0x88df20) at
postmaster.c:943
#8 0x005217fe in main (argc=7, argv=0x88df20) at main.c:263
I noticed a recent discussion on the stats collector -- is this related
to a recent change?
Joe
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: src/backend/postmaster/pgstat.c
===
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.117
diff -c -c -r1.117 pgstat.c
*** src/backend/postmaster/pgstat.c 3 Jan 2006 16:42:17 - 1.117
--- src/backend/postmaster/pgstat.c 3 Jan 2006 19:52:14 -
***
*** 1871,1884
msgbuffer = (char *) palloc(PGSTAT_RECVBUFFERSZ);
/*
-* Wait for some work to do; but not for more than 10 seconds. (This
-* determines how quickly we will shut down after an ungraceful
-* postmaster termination; so it needn't be very fast.)
-*/
- timeout.tv_sec = 10;
- timeout.tv_usec = 0;
-
- /*
* Loop forever
*/
for (;;)
--- 1871,1876
***
*** 1918,1923
--- 1910,1924
maxfd = writePipe;
}
+ /*
+* Wait for some work to do; but not for more than 10 seconds.
(This
+* determines how quickly we will shut down after an ungraceful
+* postmaster termination; so it needn't be very fast.) struct
timeout
+* is modified by some operating systems.
+*/
+ timeout.tv_sec = 10;
+ timeout.tv_usec = 0;
+
if (select(maxfd + 1, rfds, wfds, NULL, timeout) 0)
{
if (errno == EINTR)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
On Tue, 3 Jan 2006, Tino Wildenhain wrote: One thing that bothers me slightly is that we would need to look up each name (at least until we found a match) for each connection. If you had lots of names in your pg_hba.conf that could be quite a hit. A possible answer to that is to *not* look up the names from pg_hba.conf, but instead restrict the feature to matching the reverse-DNS name of the client. This limits the cost to one lookup per connection instead of N (and it'd be essentially free if you have log_hostnames turned on, since we already do that lookup in that case). Or alternatively (documented) scan and translate the names only on restart or sighup. This would limit the overhead and changes to the confile-scanner only and would at least enable symbolic names in the config files. (Of course w/o any wildcards - that would be the drawback) That's what I suggested yesterday, but others didn't like it and the possibility of using /etc/hosts or a name server on the local network to mitigate speed concerns makes me think they're right. Jon -- Jon Jensen End Point Corporation http://www.endpoint.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] cvs tip - stats buffer process consuming 100% cpu
Bruce Momjian pgman@candle.pha.pa.us writes: The only guess I have is that select() is modifying the timeout structure on return, but I didn't think it did that, does it? You shouldn't assume so; I think it does on some platforms. The Single Unix Spec says On successful completion, the object pointed to by the timeout argument may be modified. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Tue, Jan 03, 2006 at 12:08:05PM -0500, Bruce Momjian wrote: Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Tue, Jan 03, 2006 at 11:26:51AM -0500, Tom Lane wrote: Such an ALTER would certainly require exclusive lock on the table, so I'm not sure that I see much use-case for doing it like that. You'd want to do the ALTER and commit so as not to lock other people out of the table entirely while doing the bulk data-pushing. Maybe this just isn't clear, but would EXCLUSIVE block writes from all other sessions then? I don't think it should (which implies that EXCLUSIVE is a bad name). Agreed, EXCLUSIVE was used to mean an _exclusive_ writer. The new words I proposed were PRESERVE or STABLE. This seems to seriously limit the usefulness, though. You'll only want to use EXCLUSIVE/PRESERVE/STABLE when you've got a specific set of DML to do, that you know you can recover from. But if at the same time some other part of the system could be doing what it thinks will be ACID DML to that same table, you're now in trouble. At a minimum that would need to be clearly spelled out in the docs. I think it also makes a very strong use-case for exposing table-level shared locks as well, since that would at least allow other backends to continue reading from the table. Idealistically, if EXCLUSIVE/PRESERVE/STABLE does it's thing by only appending new pages, it would be nice if other backends could continue performing updates at the same time, assuming there's free space available elsewhere within the table (and that you'd be able to recover those logged changes regardless of the non-logged operations). But that's a pretty lofty goal... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Jim C. Nasby wrote: I don't think it should (which implies that EXCLUSIVE is a bad name). Agreed, EXCLUSIVE was used to mean an _exclusive_ writer. The new words I proposed were PRESERVE or STABLE. This seems to seriously limit the usefulness, though. You'll only want to use EXCLUSIVE/PRESERVE/STABLE when you've got a specific set of DML to do, that you know you can recover from. But if at the same time some other part of the system could be doing what it thinks will be ACID DML to that same table, you're now in trouble. At a minimum that would need to be clearly spelled out in the docs. I think it also makes a very strong use-case for exposing table-level shared locks as well, since that would at least allow other backends to continue reading from the table. We would be creating a new lock type for this. Idealistically, if EXCLUSIVE/PRESERVE/STABLE does it's thing by only appending new pages, it would be nice if other backends could continue performing updates at the same time, assuming there's free space available elsewhere within the table (and that you'd be able to recover those logged changes regardless of the non-logged operations). But that's a pretty lofty goal... Idealistically, yep. It would be great if we could put a helmet on and the computer would read your mind. :-) Basically meaning your idea of update while EXCLUSIVE/PRESERVE/STABLE is happening is never going to be implemented because it is just too hard to do, and too prone to error. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Tue, Jan 03, 2006 at 04:20:47PM -0500, Bruce Momjian wrote: Jim C. Nasby wrote: I don't think it should (which implies that EXCLUSIVE is a bad name). Agreed, EXCLUSIVE was used to mean an _exclusive_ writer. The new words I proposed were PRESERVE or STABLE. This seems to seriously limit the usefulness, though. You'll only want to use EXCLUSIVE/PRESERVE/STABLE when you've got a specific set of DML to do, that you know you can recover from. But if at the same time some other part of the system could be doing what it thinks will be ACID DML to that same table, you're now in trouble. At a minimum that would need to be clearly spelled out in the docs. I think it also makes a very strong use-case for exposing table-level shared locks as well, since that would at least allow other backends to continue reading from the table. We would be creating a new lock type for this. Sorry if I've just missed this in the thread, but what would the new lock type do? My impression is that as it stands you can either do: BEGIN; ALTER TABLE EXCLUSIVE; ... ALTER TABLE SHARE; --fsync COMMIT; Which would block all other access to the table as soon as the first ALTER TABLE happens. Or you can: ALTER TABLE EXCLUSIVE; ... ALTER TABLE SHARE; Which means that between the two ALTER TABLES every backend that does DML on that table will not have that DML logged, but because there's no exclusive lock that DML would be allowed to occur. BTW, there might be some usecase for the second scenario, in which case it would probably be better to tell the user to aquire a table-lock on their own rather than do it automatically as part of the update... Basically meaning your idea of update while EXCLUSIVE/PRESERVE/STABLE is happening is never going to be implemented because it is just too hard to do, and too prone to error. What I figured. Never hurts to ask though. :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Windows Installer Bug
I am writing to you all just to inform you of a bug(probably), that I came across while trying to install PostgreSQL 8.1.0-2 and 8.1.1-1 on WinXP with SP2, using the PostgreSQL installer. I had done a windows clean install and then installed straight away PostgreSQL 8.0.4. It was the only software installed on windows and worked perfectly. After a day or two, I noticed that the PostgreSQL 8.1.0-2 was released. I decided two uninstall PostgreSQL 8.0.4 and install the new 8.1.0-2 version. So I did, using the following unistall procedure: 1. I unistalled PostgreSQL 8.0.4 form the control panel in the usual manner that all windows application are uninstalled. 2. I used the Computer Management program of the Administrative Tools of windows and deleted the PostgreSQL user that had been created during the installation of PostgreSQL 8.0.4. 3. Finally, I removed all garbage left by the uninstaller in the registry. 4. I restarted windows. I have to stress that I did not have any other software installed on windows at the moment and I had the Windows firewall service disabled. So I tried to install the new version 8.1.0-2 but got an error. I tried at least 15 times and I always got the same error. The error was manifesting itself during the installation as follows: During the Activating procedural languages installation part, I get: Failed to connect to the database. Procedural languages files are installed, but are not activated in any databases. When I press OK it continues and I get: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. When I press OK again, it continues and I get: Failed to connect to the 'template1' database. Contrib files are installed, but are not activated in any databases. Pressing OK for a final time gives me this: could not send startup packet: Connection reset by peer (0x2746/10054) ** I uninstalled the 8.1.0-2 PostgreSQL which was not working and I tried to install again the 8.0.4, as some people in the novice and Admin mailing lists suggested. The 8.0.4 installer worked fine and I had PostgreSQL 8.0.4 up and running in no time. Then I uninstalled 8.0.4 and tried to install 8.1.0-2 for a last time. The same errors appeared once again. * If that is not a bizzare behaviour of the installer, what is? * The same problem appeared when some people I know tried to install PostgreSQL 8.1.0-2 as well. But they did not have the time nor the inclination to make this known to the public by posting to the mailing lists. I have no feedback of the same people trying the 8.1.1-1 version. I believe they have not tried it yet. Now that PostgreSQL 8.1.1-1 is out, I decided to test my luck again. I repeated the whole procedure and I got exactly the same erros that manifested when trying to install 8.1.0-2. I uninstalled and I tried to install 8.0.4. That went ok again and I did not have any problems. All the problems appear whenever I try to install any version after the 8.0.4. As someone suggested, these errors indicate a proper install where the server crashes after install. That is exactly what happens. Another strange thing is that when the first error prompt appears, if I look for any log files, I can not find any. Maybe that is because that error appears after the installation of the core engine. I dont know. But! That bizzare behaviour does not manifest itself when using the installers of versions of PostgreSQL prior to 8.1.x. For all versions of PostgreSQL that I have successfully installed so far (8.0.4 and all others prior to that), I used the folder C:\Program Files\Postgresql\ for the PostgrerSQL files and the folder D:\DBASES\ for the database files. I tried the default folders as well but it did not make any difference. At any unistallation I delete the folder D:\DBASES manually. All other folders are deleted automatically by the unistaller. Can anybody please help? It is very annoying not to be able to use the DB. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
On Tue, Jan 03, 2006 at 12:08:46 -0600, Larry Rosenman [EMAIL PROTECTED] wrote: The issue is folks that DON'T set reverse DNS, I.E. have generic rDNS set on their IP's. I've seen (in my ISP days, and on my mailserver) LOTS of folks that can't/won't update Their rDNS, even though it's a STATICLY assigned address. And, as an example, my house IP changes when the PPPoE moves, and I have a DynDns.org Hostname that changes to support that, as well as a CNAME out of my domain to Point to it. Business class (or Speakeasy) broadband connections will generally allow you to have custom PTR records for static IP addresses. When using broadband for sheep, the ssh tunnel idea is probably a better solution. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Why don't we allow DNS names in pg_hba.conf?
Bruno Wolff III wrote: On Tue, Jan 03, 2006 at 12:08:46 -0600, Larry Rosenman [EMAIL PROTECTED] wrote: The issue is folks that DON'T set reverse DNS, I.E. have generic rDNS set on their IP's. I've seen (in my ISP days, and on my mailserver) LOTS of folks that can't/won't update Their rDNS, even though it's a STATICLY assigned address. And, as an example, my house IP changes when the PPPoE moves, and I have a DynDns.org Hostname that changes to support that, as well as a CNAME out of my domain to Point to it. Business class (or Speakeasy) broadband connections will generally allow you to have custom PTR records for static IP addresses. When using broadband for sheep, the ssh tunnel idea is probably a better solution. Understood. I was just pointing out the issue. I have had to whitelist some of my mortgage brokers e-mail servers because they hadn't gotten rid of the generic rDNS for their mailserver. I just wanted the issue to be in the archives when/if people don't like whatever gets committed/ -- Larry Rosenman Database Support Engineer PERVASIVE SOFTWARE. INC. 12365B RIATA TRACE PKWY 3015 AUSTIN TX 78727-6531 Tel: 512.231.6173 Fax: 512.459.1309 Email: [EMAIL PROTECTED] Web: www.pervasive.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Jim C. Nasby wrote: We would be creating a new lock type for this. Sorry if I've just missed this in the thread, but what would the new lock type do? My impression is that as it stands you can either do: BEGIN; ALTER TABLE EXCLUSIVE; ... ALTER TABLE SHARE; --fsync COMMIT; Which would block all other access to the table as soon as the first ALTER TABLE happens. Or you can: ALTER TABLE EXCLUSIVE; ... ALTER TABLE SHARE; Which means that between the two ALTER TABLES every backend that does DML on that table will not have that DML logged, but because there's no exclusive lock that DML would be allowed to occur. Right, the DML will be single-threaded and fsync of all dirty pages will happen before commit of each transaction. BTW, there might be some usecase for the second scenario, in which case it would probably be better to tell the user to aquire a table-lock on their own rather than do it automatically as part of the update... Basically meaning your idea of update while EXCLUSIVE/PRESERVE/STABLE is happening is never going to be implemented because it is just too hard to do, and too prone to error. What I figured. Never hurts to ask though. :) Actually, it does hurt because it generates discussion volume for no purpose. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Fri, 2005-12-30 at 16:14 -0500, Bruce Momjian wrote: Simon Riggs wrote: The implications of EXCLUSIVE are: - there will be a check on each and every I, U, D to check the state of the relation - *every* operation that attempts a write lock will attempt to acquire an EXCLUSIVE full table lock instead - following successful completion of *each* DML statement, the relation will be heap_sync'd involving a full scan of the buffer cache Yes, I think that is it. What we can do is implement EXCLUSIVE to affect only COPY at this point, and document that, and later add other commands. Can I clarify the wording of the syntax? Is EXCLUSIVE the right word? How about FASTLOAD or BULKLOAD? Those words seem less likely to be misused in the future - i.e. we are invoking a special mode, rather than invoking a special go faster option. The problem with the FASTLOAD/BULKLOAD words is that EXCLUSIVE mode is probably not the best for loading. I would think TRUNCATE would be a better option. In fact, in loading a table, I think both EXCLUSIVE and TRUNCATE would be the same, mostly. You would create the table, set its RELIABILITY to TRUNCATE, COPY into the table, then set the RELIABILITY to SHARE or DEFAULT. The second ALTER has to sync all the dirty data blocks, which the same thing EXCLUSIVE does at the conclusion of COPY. So, we need a name for EXCLUSIVE mode that suggests how it is different from TRUNCATE, and in this case, the difference is that EXCLUSIVE preserves the previous contents of the table on recovery, while TRUNCATE does not. Do you want to call the mode PRESERVE, or EXCLUSIVE WRITER? Anyway, the keywords are easy to modify, even after the patch is submitted. FYI, I usually go through keywords.c looking for a keyword we already use. I'm very happy for suggestions on what these new modes are called. So, to summarize, I think we should add DROP/TRUNCATE, and use that by default (or optionally off?) in pg_dump, and, assuming we want EXCLUSIVE for more than just COPY, we need to add ALTER TABLE EXCLUSIVE. Would you mind stating again what you mean, just so I can understand this? Your summary isn't enough. New ALTER TABLE mode, perhaps call it PERSISTENCE: ALTER TABLE tab PERSISTENCE DROP ON RECOVERY ALTER TABLE tab PERSISTENCE TRUNCATE ON RECOVERY These would drop or truncate all tables with this flag on a non-clean start of the postmaster, and write something in the server logs. However, I don't know that we have the code in place to DROP/TRUNCATE in recovery mode, and it would affect all databases, so it could be quite complex to implement. In this mode, no WAL logs would be written for table modifications, though DDL commands would have to be logged. Right now, this will be a TODO item... it looks like it will take some thought to implement correctly. ALTER TABLE tab PERSISTENCE PRESERVE (or STABLE?) Table contents are preserved across recoveries, but data modifications can happen only one at a time. I don't think we have a lock mode that does this, so I am worried a new lock mode will have to be created. A simplified solution at this stage would be to take an exclusive lock on the table, but really we just need a single-writer table lock, which I don't think we have. initially this can implemented to only affect COPY but later can be done for other commands. ExclusiveLock locks out everything apart from readers, no new lock mode AFAICS. Implementing that is little additional work for COPY. Tom had a concern about setting this for I, U, D commands via the executor. Not sure what the details of that are, as yet. We can use either of the unlogged modes for pg_dump, so I'd suggest its this one. Everybody happy with this being the new default in pg_dump, or should it be an option? ALTER TABLE tab PERSISTENCE DEFAULT This would be our current default mode, which is full concurrency and persistence. I'm thinking whether the ALTER TABLE statement might be better with two bool flags rather than a 3-state char. flag 1: ENABLE LOGGING | DISABLE LOGGING flag 2: FULL RECOVERY | TRUNCATE ON RECOVERY Giving 3 possible sets of options: -- the default ALTER TABLE mytable ENABLE LOGGING FULL RECOVERY; (default) -- EXCLUSIVE mode ALTER TABLE mytable DISABLE LOGGING FULL RECOVERY; ...which would be used like this ALTER TABLE mytable DISABLE LOGGING; COPY or other bulk data manipulation SQL ALTER TABLE mytable ENABLE LOGGING; ...since FULL RECOVERY is the default. -- multiuser temp table mode ALTER TABLE mytable DISABLE LOGGING TRUNCATE ON RECOVERY; ...which would usually be left on all the time which only uses one new keyword LOGGING and yet all the modes are fairly explicit as to what they do. An alternative might be the slightly more verbose: ALTER TABLE mytable DISABLE LOGGING FORCE EXCLUSIVE TABLE LOCK; which would be turned off by ALTER
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Tue, 2006-01-03 at 16:20 -0500, Bruce Momjian wrote: Jim C. Nasby wrote: Idealistically, if EXCLUSIVE/PRESERVE/STABLE does it's thing by only appending new pages, it would be nice if other backends could continue performing updates at the same time, assuming there's free space available elsewhere within the table (and that you'd be able to recover those logged changes regardless of the non-logged operations). But that's a pretty lofty goal... Idealistically, yep. It would be great if we could put a helmet on and the computer would read your mind. :-) Basically meaning your idea of update while EXCLUSIVE/PRESERVE/STABLE is happening is never going to be implemented because it is just too hard to do, and too prone to error. The reason for locking the whole table was to ensure that we do not have a mixture of logged and non-logged writers writing to the same data blocks, since that could damage blocks unrecoverably in the event of a crash. (Though perhaps only if full_block_writes is on) The ALTER TABLE .. EXCLUSIVE/(insert name) mode would mean that *any* backend who took a write lock on the table, would lock out the whole table. So this new mode is not restricted to the job/user who ran the ALTER TABLE command. (I would note that that is how Oracle and Teradata do this for pre-load utility table locking, but why should we follow them on that?) Currently, when we add a new row when the FSM is empty, we check the last block of the table. That would cause multiple writers to access the same blocks and so we would be in danger. The only way to avoid that would be for logged writers (who would use the FSM if it were not empty) to notify back to the FSM that they have just added a block - and remove the behaviour to look for the last block. Anyway, one step at a time. *Maybe* we can do that in the future, but right now I'd like to add the basic fast write/load functionality. Also, I think I will do the docs first this time, just so everyone can read what we're getting ahead of time, to ensure we all agree. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Tue, 2006-01-03 at 17:38 -0500, Bruce Momjian wrote: Right, the DML will be single-threaded and fsync of all dirty pages will happen before commit of each transaction. heap_sync() would occur at end of statement, as it does with CTAS. We could delay until EOT but I'm not sure I see why; in most cases they'd be the same point anyway. I'd been toying with the idea of making the freshly added blocks live only in temp_buffers to avoid the shared_buffers overhead, but that was starting to sounds too wierd for my liking. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stats collector performance improvement
Jim C. Nasby [EMAIL PROTECTED] writes: I would argue that minutes is too long, but of course this could be user-adjustable. I suspect that even waiting just a second could be a huge win, since this only matters if you're executing a lot of statements and you won't be doing that if those statements are taking more than a second or two to execute. That's not necessarily true at all. You could just as easily have a performance problem caused by a quick statement that is being executed many times as a slow statement that is being executed few times. That is, you could be executing dozens of queries that take seconds or minutes once a second but none of those might be the problem. The problem might be the query that's taking only 300ms that you're executing hundreds of of times a minute. Moreover, if you're not gathering stats for queries that are fast then how will you know whether they're performing properly when you look at them when they do show up? -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Sat, 2005-12-31 at 12:59 +0100, Michael Paesold wrote: Bruce Momjian wrote: The --single-transaction mode would apply even if the dump was created using an earlier version of pg_dump. pg_dump has *not* been altered at all. (And I would again add that the idea was not my own) I assume you mean this: http://archives.postgresql.org/pgsql-patches/2005-12/msg00257.php I guess with the ALTER commands I don't see much value in the --single-transaction flag. I am sure others suggested it, but would they suggest it now given our current direction. I just want to add that --single-transaction has a value of it's own. There were times when I wanted to restore parts of a dump all-or-nothing. This is possible with PostgreSQL, unlike many other DBM systems, because people like Tom Lane have invested in ensuring that all DDL is working without implicitly committing an enclosing transaction. Using pg_restore directly into a database, it is not possible to get a single transaction right now. One has to restore to a file and manually added BEGIN/COMMIT. Just for that I think --single-transaction is a great addition and a missing feature. I think more people have a use-case for that. I did originally separate the --single-transaction patch for this reason. I think its a valid patch on its own and its wrapped and ready to go, with some deletions from the doc patch. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
On Thu, 2005-12-29 at 11:37 -0500, Bruce Momjian wrote: Having COPY behave differently because it is in a transaction is fine as long as it is user-invisible, but once you require users to do that to get the speedup, it isn't user-invisible anymore. Since we're agreed on adding ALTER TABLE rather than COPY LOCK, we have our explicit mechanism for speedup. However, it costs a single line of code and very very little execution time to add in the optimization to COPY to make it bypass WAL when executed in the same transaction that created the table. Everything else is already there. As part of the use_wal test: + if (resultRelInfo-ri_NumIndices == 0 + !XLogArchivingActive() (cstate-rel-rd_createSubid != InvalidSubTransactionId )) + use_wal = false; the value is already retrieved from cache... Can anyone see a reason *not* to put that change in also? We just don't advertise it as the suggested route to gaining performance, nor would we rely on it for pg_dump/restore performance. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Bruce, Basically meaning your idea of update while EXCLUSIVE/PRESERVE/STABLE is happening is never going to be implemented because it is just too hard to do, and too prone to error. What I figured. Never hurts to ask though. :):) Actually, it does hurt because it generates discussion volume for no purpose. Zowie!! Surely you didn't mean that the way it sounded? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Inconsistent syntax in GRANT
Folks, Just got tripped up by this: GRANT SELECT ON table1 TO someuser; GRANT SELECT ON table1_id_seq TO someuser; both work However, GRANT SELECT ON TABLE table1 TO someuser; ... works, while GRANT SELECT ON SEQUENCE table1_id_seq TO someuser; ... raises an error. This is inconsistent. Do people agree with me that the parser should accept SEQUENCE there, since the optional object name works for all other objects? Is there some technical reason this is difficult to do? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Deferrable UNIQUE INDEX?
Folks, I just stumbled across a little problem that looks like this: CREATE TABLE foo ( id SERIAL PRIMARY KEY, f_text TEXT NOT NULL ); CREATE UNIQUE INDEX uniq_foo_f_text ON foo(LOWER(TRIM(f_text))); COPY foo(f_text) FROM stdin; a b c \. Oh, shucks. I'd like to permute these. Lemme start a transaction: [EMAIL PROTECTED] BEGIN; BEGIN [EMAIL PROTECTED] UPDATE foo set f_text = 'a' where id=3; ERROR: duplicate key violates unique constraint uniq_foo_f_text Dang! Everything was going to be kosher at the end of the transaction, but I never got a chance. Is there some way to make the index check INITIALLY DEFERRABLE the way a regular column/table constraint could be? I'd much appreciate any insights into this :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Deferrable UNIQUE INDEX?
David Fetter said: Is there some way to make the index check INITIALLY DEFERRABLE the way a regular column/table constraint could be? I'd much appreciate any insights into this :) The docs say: Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. The TODO list has: Allow DEFERRABLE UNIQUE constraints? send in a patch ;-) cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Inconsistent syntax in GRANT
--- Josh Berkus josh@agliodbs.com escreveu: However, GRANT SELECT ON TABLE table1 TO someuser; ... works, while GRANT SELECT ON SEQUENCE table1_id_seq TO someuser; ... raises an error. This is inconsistent. Do people agree with me that the parser should accept SEQUENCE there, since the optional object name works for all other objects? Is there some technical reason this is difficult to do? It should but it's not implemented yet. There is no difficulty in doing it. But I want to propose the following idea: if some object depends on another object and its type is 'DEPENDENCY_INTERNAL' we could grant/revoke privileges automagically to it. Or maybe create another type of dependency to do so. Comments? Euler Taveira de Oliveira euler[at]yahoo_com_br ___ Yahoo! doce lar. Faça do Yahoo! sua homepage. http://br.yahoo.com/homepageset.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Deferrable UNIQUE INDEX?
On Jan 3, 2006, at 19:45 , David Fetter wrote: Dang! Everything was going to be kosher at the end of the transaction, but I never got a chance. Is there some way to make the index check INITIALLY DEFERRABLE the way a regular column/table constraint could be? Happy New Year, David! I've run into this when reorganizing nested-set hierarchies (though then it's with integers rather than text). There isn't a way to do this directly, as you've found out, but you can use a similar workaround. You could prepend a string to the key during the permutation, and strip the prefix after permutation is finished. Not the answer you're looking for, but perhaps you'll be able to use this workaround. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Incremental Backup Script
On 12/26/05 11:04, Qingqing Zhou wrote: Gregor Zeitlinger [EMAIL PROTECTED] wrote Also, I was wondering whether it is always safe to copy the current WAL file, i.e. may the current WAL file be invalid in any circumstance? If you mean current WAL file is the xlog segment in use, then it is dangerous. We only backup the xlog segments that have been fully used up. As per docs, if the databases are rarely updated it could take a long time for the WAL segment to roll over. We need to backup the current segment to guarantee we have the latest trasactions archived at time of failure. http://www.postgresql.org/docs/8.1/interactive/backup-online.html If you are concerned about being able to recover right up to the current instant, you may want to take additional steps to ensure that the current, partially-filled WAL segment is also copied someplace. This is particularly important if your server generates only little WAL traffic (or has slack periods where it does so), since it could take a long time before a WAL segment file is completely filled and ready to archive. One possible way to handle this is to set up a cron job that periodically (once a minute, perhaps) identifies the current WAL segment file and saves it someplace safe. Gregor: can you explain how to identify the current file? I had implemented a backup and restore script for PITR but stumbled at this point. The page above does not specify how this is to be done. I appreciate the addition of PITR - it's better than nothing (nothing being full dumps) in some respects. Ideally, we need to be able to dump deltas for a single database. In practice, restoration using the PITR method is awkward. I guess you would tarball the current data files, do a full restore, do a full dump of the database you are interested in, ditch the restored data files and replace them with the ones you tarballed, then do a database load from the full dump. The only way to avoid having the other databases on the server offline is to restore to a second postgresql instance. Not complaining, just saying :-) Regards, Qingqing Zach. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Deferrable UNIQUE INDEX?
Andrew Dunstan [EMAIL PROTECTED] writes: David Fetter said: Is there some way to make the index check INITIALLY DEFERRABLE the way a regular column/table constraint could be? The TODO list has: Allow DEFERRABLE UNIQUE constraints? send in a patch ;-) This is definitely on the wish-list, because it is both useful and required by the SQL spec ... but it seems nontrivial. I recall some discussions in the pghackers archives about how to do it. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [Bizgres-general] WAL bypass for INSERT, UPDATE and
Josh Berkus wrote: Bruce, Basically meaning your idea of update while EXCLUSIVE/PRESERVE/STABLE is happening is never going to be implemented because it is just too hard to do, and too prone to error. What I figured. Never hurts to ask though. :):) Actually, it does hurt because it generates discussion volume for no purpose. Zowie!! Surely you didn't mean that the way it sounded? Actually, I did. Throwing out random ideas that have little useful purpose and just confuse the discussion is not helpful. Wouldn't it be nice if PostgreSQL allowed commands to be typed backwards so people could program by looking at the screen through a mirror is not helpful. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Deferrable UNIQUE INDEX?
On Tue, Jan 03, 2006 at 11:17:16PM -0500, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: David Fetter said: Is there some way to make the index check INITIALLY DEFERRABLE the way a regular column/table constraint could be? The TODO list has: Allow DEFERRABLE UNIQUE constraints? send in a patch ;-) This is definitely on the wish-list, because it is both useful and required by the SQL spec ... but it seems nontrivial. I recall some discussions in the pghackers archives about how to do it. There was some discussion in the followups to this posting: http://archives.postgresql.org/pgsql-hackers/2005-01/msg00882.php Alvaro mentioned a strategy here http://archives.postgresql.org/pgsql-hackers/2005-01/msg00904.php and the tough part appears to have come down to disk-spilling logic. I'm out of my depth here, but maybe we could figure out just what components would need to be touched and how... Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [DOCS] Online backup vs Continuous backup
How about: use Online backup or Hot backup to refer to either method of back since they are both done while the system is online or hot. If you want to get specific refer to doing a sql dump etc for using pg_dump Then use Incremental backup to refer to the whole process of the WAL archival etc Refer to the actual log files themselves as transaction logs. That all seems to be pretty intuitive and non-ambiguous non-confusing to me. On Dec 26, 2005, at 11:44 AM, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I suggest the following patch to rename our capability Continuous Backup. This doesn't seem like an improvement. Online backup is the standard terminology AFAIK. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [DOCS] Online backup vs Continuous backup
I think it would all make more sense if we described the use of archive_command = something as being in WAL Archive Mode. That would then allow us to say: You can only take Online Backups while in WAL Archive Mode. If you ever wish to perform PITR, you must use WAL Archive Mode. If you backed-up in WAL Archive Mode, you can perform an Archive Recovery. It seems to me there are two different context in which one would be making statements like this. And what we are allowed to say depends greatly on context. These contexts are as follows: 1) Explaining the feature set of postgres to a potential user. 2) Explaining to an actual postgres user how to actually do something. In the first case it makes the most sense to me to use industry standard or very intuitive terminology to the extend that it exists. ie (Transaction Logs vs. WAL). Incremental Backup and Point in Time Recovery seem to be fairly commonly used and understood database buzzwords for someone to investigate the feature set of an RDBMS. In the second case it seems to me that the most important thing is that you pick terminology that is consistent, unambiguous and clearly defined. Log archival, PITR, etc are not point and click operations like they are in say MS SQL Server. This gives us more flexibility but it also requires a deeper understanding. If someone is unwilling or unable to to learn whatever terminology you happen to come up with then it seems to me they shouldn't even be attempting to set up one of those features. At the same time if the terminology you uses changes all the time (is not consistent), or if you can't figure out what any of the terms mean (they are not clearly defined) or if you use terms like online backup to mean both types of backup but then use it once in a specific circumstance where only one usage is appropriate (you are using the terms ambiguously) then users will be confused and it will be your fault not theirs. Just my 2 cents Rick Gigger ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Incremental Backup Script
I would certainly like some instructions on this as well. On Jan 3, 2006, at 8:41 PM, Zach Bagnall wrote: On 12/26/05 11:04, Qingqing Zhou wrote: Gregor Zeitlinger [EMAIL PROTECTED] wrote Also, I was wondering whether it is always safe to copy the current WAL file, i.e. may the current WAL file be invalid in any circumstance? If you mean current WAL file is the xlog segment in use, then it is dangerous. We only backup the xlog segments that have been fully used up. As per docs, if the databases are rarely updated it could take a long time for the WAL segment to roll over. We need to backup the current segment to guarantee we have the latest trasactions archived at time of failure. http://www.postgresql.org/docs/8.1/interactive/backup-online.html If you are concerned about being able to recover right up to the current instant, you may want to take additional steps to ensure that the current, partially-filled WAL segment is also copied someplace. This is particularly important if your server generates only little WAL traffic (or has slack periods where it does so), since it could take a long time before a WAL segment file is completely filled and ready to archive. One possible way to handle this is to set up a cron job that periodically (once a minute, perhaps) identifies the current WAL segment file and saves it someplace safe. Gregor: can you explain how to identify the current file? I had implemented a backup and restore script for PITR but stumbled at this point. The page above does not specify how this is to be done. I appreciate the addition of PITR - it's better than nothing (nothing being full dumps) in some respects. Ideally, we need to be able to dump deltas for a single database. In practice, restoration using the PITR method is awkward. I guess you would tarball the current data files, do a full restore, do a full dump of the database you are interested in, ditch the restored data files and replace them with the ones you tarballed, then do a database load from the full dump. The only way to avoid having the other databases on the server offline is to restore to a second postgresql instance. Not complaining, just saying :-) Regards, Qingqing Zach. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
