Re: [GENERAL] WARNINGs after starting backup server created with PITR
Erik Jones <[EMAIL PROTECTED]> writes: >>> 2008-01-17 21:47:34 CST 7598 :WARNING: relation "table_name" page >>> 5728 is uninitialized --- fixing >> >> If you do a vacuum on the master, do you get the same warnings? > /me runs VACUUM VERBOSE on the two tables that would matter. > Nope. What worries me is, that since I have a verified case of rsync > thinking it had successfully transferred a WAL, the same may have > happened with these files during the base backup. Does that warning, > in fact, entail that there were catalog entries for those files, but > that the file was not there, and by "fixing" it the server just > created empty files? Not necessarily. What the warning actually means is that VACUUM found an all-zeroes page within a table. There are scenarios where this is not unexpected, particularly after a crash on the master. The reason is that adding a page to a table is a two-step process. First we write() a page of zeroes at the current EOF; this is basically to make the filesystem reserve the space. We don't want to report that we've committed a page-full of new rows and then discover there's no disk space for them. Then we initialize the page (ie set up the page header) and start putting rows into it. But these latter operations happen inside a shared buffer, and might not reach disk until the next checkpoint. Now, the insertions of the rows are entered into the WAL log, and once the first such WAL entry has reached disk, the page will be re-initialized by WAL replay if there's a crash. But there's an interval between the filesystem's extension of a table with zeroes and the first WAL entry related to the page reaching disk. If you get a crash in that interval then the all-zeroes page will still be there after recovery, and will go unused until VACUUM reclaims it (and produces the WARNING). So this would explain some zero pages (though not large numbers of them) if you'd had crashes on the master. I'm not sure offhand whether there's any case in which bringing up a PITR slave is close enough to crash recovery that the same mechanism could apply to produce a zero page on the slave where there had been none on the master. In any case, 125 different zeroed pages is pretty hard to explain by such a mechanism (especially if they were scattered rather than in contiguous clumps). I tend to agree that it sounds like there was something wrong with the rsync mirroring process. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ATTN: Clodaldo was Performance problem. Could it be related to 8.3-beta4?
Clodoaldo <[EMAIL PROTECTED]> writes: > The old server reproduces the behavior of the new one. Well, that's just weird. Let's see, FC6 has oprofile --- maybe oprofile measurements on 8.2 and 8.3 on the same box would tell something. Do you know how to use that? Alternatively, if you could give me ssh access to the box, I'd be willing to do the measuring. But oprofile requires root access so maybe that's more than you want to trust me ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PHP and Postgres arrays
Hannes Dorbath wrote: Yannick Warnier wrote: I thought about it, but it's not very portable if you want to ship a PHP application. But I admit I will fall back to that solution if I cannot find any other (considering PostgreSQL might be the only open-source database to offer in-fields arrays anyway). You can use the build in function array_to_string: http://www.postgresql.org/docs/8.3/static/functions-array.html Then use implode(YOUR_DELIMITER, YOUR_STRING) to get an array. Another solution would be to use the MDB2 (or similar) PEAR wrapper. b ---(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: [GENERAL] PHP and Postgres arrays
Yannick Warnier wrote: I thought about it, but it's not very portable if you want to ship a PHP application. But I admit I will fall back to that solution if I cannot find any other (considering PostgreSQL might be the only open-source database to offer in-fields arrays anyway). You can use the build in function array_to_string: http://www.postgresql.org/docs/8.3/static/functions-array.html -- Best regards, Hannes Dorbath ---(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: [GENERAL] Stupid question about WAL archiving
Which is exactly why I pointed out that using pg_standby's -k switch was the more reliable option. And supposedly even that switch is not needed once we can get to 8.3, which should be soon. Even the -k switch can be an issue since you don't really know how many you should keep around. David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] WARNINGs after starting backup server created with PITR
On Jan 18, 2008, at 4:43 PM, Brian Wipf wrote: On 18-Jan-08, at 2:32 PM, Erik Jones wrote: What worries me is, that since I have a verified case of rsync thinking it had successfully transferred a WAL, the same may have happened with these files during the base backup. Does that warning, in fact, entail that there were catalog entries for those files, but that the file was not there, and by "fixing" it the server just created empty files? We archive WALs directly to an NFS mount. We once had a zero-byte WAL file archived, which I believe was the result of a temporary issue with the NFS mount. We had to perform a new base backup since the WAL was deleted/reused by PG because it was told it was archived successfully. It sounds similar to the problem you experienced. Do you rsync to an NFS mount? If this issue is occurring when archiving WALs, I agree that it could be occurring when trying to get a base backup. For our primary, er, main, onsite standby server that's also what we do. But, this was a co-location to co-location transfer so there was no NFS mount, it was a direct rsync to the server at the other co- location. For WAL files, I've already decided to write a WALShipper utility that will handle shipping WALs to multiple standbys with verfication, but for the base backup, this is distressing. We do have the option to do the base backup to a portable USB drive and then carry it to the second co-lo for now. But, pretty soon we're going to be surpassing the available limits in portably drive capacity unless we invest in tape drives. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] WARNINGs after starting backup server created with PITR
On 18-Jan-08, at 2:32 PM, Erik Jones wrote: What worries me is, that since I have a verified case of rsync thinking it had successfully transferred a WAL, the same may have happened with these files during the base backup. Does that warning, in fact, entail that there were catalog entries for those files, but that the file was not there, and by "fixing" it the server just created empty files? We archive WALs directly to an NFS mount. We once had a zero-byte WAL file archived, which I believe was the result of a temporary issue with the NFS mount. We had to perform a new base backup since the WAL was deleted/reused by PG because it was told it was archived successfully. It sounds similar to the problem you experienced. Do you rsync to an NFS mount? If this issue is occurring when archiving WALs, I agree that it could be occurring when trying to get a base backup. Brian Wipf <[EMAIL PROTECTED]> ---(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: [GENERAL] ATTN: Clodaldo was Performance problem. Could it be related to 8.3-beta4?
2008/1/16, Tom Lane <[EMAIL PROTECTED]>: > I went through this thread again, and noticed something that no one > seems to have remarked on at the time: the vmstat numbers near the > bottom of this post > > http://archives.postgresql.org/pgsql-general/2008-01/msg00161.php > > show close to 100% I/O wait time (either that or 50% idle 50% I/O wait, > which I suspect is an artifact). We subsequently concluded that the > "SELECT" side of the INSERT/SELECT command is not where the problem is, > so all the cycles are going into the actual row insertion part. > > I don't know of any reason to think that insertion is slower in 8.3 > than it was in 8.2, and no one else has reported anything of the sort. > So I'm leaning to the idea that this suggests some kind of > misconfiguration of the disk setup in Clodoaldo's new server. There > was some earlier discussion about not having the RAID configured right: Now it is tested in this configuration, the old server: Fedora Core 6, AMD XP2600, 2 GB mem, two 7200 ide disks with pg_xlog alone in the second disk. This is 8.2.6: fahstats=# explain analyze fahstats-# insert into usuarios ( fahstats(# data, fahstats(# usuario, fahstats(# pontos, fahstats(# wus fahstats(# ) fahstats-# select fahstats-# (select data_serial from data_serial) as data, fahstats-# ui.usuario_serial as usuario, fahstats-# sum(pontos) as pontos, fahstats-# sum(wus) as wus fahstats-# from usuarios_temp as ut inner join usuarios_indice as ui fahstats-# on ut.usuario = ui.usuario_nome and ut.n_time = ui.n_time fahstats-# group by data, ui.usuario_serial fahstats-# ; QUERY PLAN Subquery Scan "*SELECT*" (cost=307438.81..331782.20 rows=885214 width=20) (actual time=31433.335..35989.973 rows=885281 loops=1) -> HashAggregate (cost=307438.81..320717.02 rows=885214 width=12) (actual time=31433.318..33886.039 rows=885281 loops=1) InitPlan -> Seq Scan on data_serial (cost=0.00..1.01 rows=1 width=4) (actual time=0.016..0.018 rows=1 loops=1) -> Merge Join (cost=102838.10..254834.62 rows=5260318 width=12) (actual time=12146.535..29242.146 rows=891140 loops=1) Merge Cond: ((ut.n_time = ui.n_time) AND ((ut.usuario)::text = "inner"."?column4?")) -> Index Scan using usuarios_temp_ndx on usuarios_temp ut (cost=0.00..55486.37 rows=891140 width=26) (actual time=0.217..8457.332 rows=891140 loops=1) -> Sort (cost=102838.10..105051.14 rows=885214 width=22) (actual time=12146.264..13215.173 rows=891180 loops=1) Sort Key: ui.n_time, (ui.usuario_nome)::text -> Seq Scan on usuarios_indice ui (cost=0.00..15398.14 rows=885214 width=22) (actual time=0.055..1266.373 rows=885321 loops=1) Trigger for constraint datas: time=28494.257 calls=885281 Total runtime: 824920.034 ms (12 rows) Time: 825219.242 ms 8.3RC1: QUERY PLAN Subquery Scan "*SELECT*" (cost=315346.40..339490.66 rows=877973 width=20) (actual time=28527.088..34628.084 rows=877895 loops=1) -> HashAggregate (cost=315346.40..328516.00 rows=877973 width=12) (actual time=28527.060..32082.655 rows=877895 loops=1) InitPlan -> Seq Scan on data_serial (cost=0.00..1.01 rows=1 width=4) (actual time=0.018..0.021 rows=1 loops=1) -> Merge Join (cost=101200.86..257473.27 rows=5787212 width=12) (actual time=9796.697..25537.218 rows=883729 loops=1) Merge Cond: ((ut.n_time = ui.n_time) AND ((ut.usuario)::text = (ui.usuario_nome)::text)) -> Index Scan using usuarios_temp_ndx on usuarios_temp ut (cost=0.00..50587.20 rows=883729 width=23) (actual time=0.254..6940.743 rows=883729 loops=1) -> Sort (cost=101199.42..103394.35 rows=877973 width=19) (actual time=9796.386..10962.868 rows=883769 loops=1) Sort Key: ui.n_time, ui.usuario_nome Sort Method: quicksort Memory: 63286kB -> Seq Scan on usuarios_indice ui (cost=0.00..14526.73 rows=877973 width=19) (actual time=0.080..1158.713 rows=877935 loops=1) Trigger for constraint datas: time=44379.117 calls=877895 Total runtime: 8865110.176 ms (13 rows) Time: 8865629.601 ms Both versions where analized before running. The old server reproduces the behavior of the new one. Regards, Clodoaldo Pinto Neto ---(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: [GENERAL] PHP and Postgres arrays
Le vendredi 18 janvier 2008 à 23:10 +0100, Ivan Sergio Borgonovo a écrit : > On Fri, 18 Jan 2008 23:01:09 +0100 > Yannick Warnier <[EMAIL PROTECTED]> wrote: > > > Hello, > > > I've been searching (not very thoroughly, but still) for a way to > > get Postgres arrays right into PHP arrays, but it seems that the > > Postgres driver simply doesn't allow that. > > What about using a stored procedure to return the array as a joined > row? I thought about it, but it's not very portable if you want to ship a PHP application. But I admit I will fall back to that solution if I cannot find any other (considering PostgreSQL might be the only open-source database to offer in-fields arrays anyway). Yannick ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PHP and Postgres arrays
On Fri, 18 Jan 2008 23:01:09 +0100 Yannick Warnier <[EMAIL PROTECTED]> wrote: > Hello, > I've been searching (not very thoroughly, but still) for a way to > get Postgres arrays right into PHP arrays, but it seems that the > Postgres driver simply doesn't allow that. What about using a stored procedure to return the array as a joined row? -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PHP and Postgres arrays
Hello, I've been searching (not very thoroughly, but still) for a way to get Postgres arrays right into PHP arrays, but it seems that the Postgres driver simply doesn't allow that. The only solution seems to use some user-defined functions[1] to split the result of a query (a string) into a PHP array. Some Perl-related readings [2] seem to say that Postgres actually provides a string, loosing the possibility to get it as an array, but these comments date back to 2005. Any chance someone around here might tell me more about this and possibly give me some better way to get those arrays than copy-pasting the user-proposed PHP functions? Thanks, Yannick Warnier [1] http://www.php.net/manual/en/ref.pgsql.php#58660 [2] http://www.perlmonks.org/?node_id=474518 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Replication Using Triggers
Andreas 'ads' Scherbaum wrote: Have a plperl function that creates connections to all servers in the cluster (replication partners), and issues the supplied write query to them, possibly with a tag of some sort to indicated it is a replicated query (to prevent circular replication). Have a post execute trigger that calls the above replication function if the query was issued directly (as opposed to replicated), and passes it the query it just executed if it was successful. Not sure here if you mean literally the SQL query that was executed - in which case you have all sorts of problems with sequences and functions returning different values. Indeed, but sequences at least can be worked around. Post-execute, sequence number used should be available already, and the sequence offset and increment can be set so as to ensure they do not clash. That's what MySQL does (and I must apologize for making the comparison all the time). Sequences are only one (small) problem. What about functions returning different results (volatile) for each call? Just imagine random() or now(). Yes, that's a problem. The bodge workaround for that is to save the master's state for such functions and re-pack it from a function into a literal in a pre-execution trigger, and then replicate the literals. What about inserts or updates selecting parts of table data? You can't be sure to get exactly the same results on the slave. You can if you have an ordering consistency check mechanism, as I mentioned in the other mail. Recovery when "something goes wrong" (tm), however, could get interesting, especially under heavy distributed write load. If there's a counter and a hash, I guess you could lock everything, find the one with the biggest counter, and release the lock on everything else until it catches up, then re-lock, then replicate. It would add a fair bit of latency, though. Gordan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Forgot to dump old data before re-installing machine
On Fri, 18 Jan 2008, Tom Lane wrote: pg_controldata already provides this information, no? At least barring the case of wrong-time_t-size, which we already know we want to fix. It provides some of it, and I think you could make a case that the text file format Dave suggested could be prototyped in an improved form by combining the output from pg_controldata plus pg_config plus uname -a. If people knew to save a copy of the output from those religiously into the base of the database directory to aid possibly unrelated people who have to restore that data later, that would be nice. In the cases I was mentioning, people can't run pg_controldata until they have binaries installed, and having a simple text file that contained the information needed to do that right in the first place would give some guidance as to get to that step. You have to put yourself in the shoes of the person who has a database backup and a crashed server to appreciate that anything that makes this process easier is a huge help. Imagine that the previous DBA just quit (nuking the server on his way out) and you have the kind of real-world crisis people really run into. It would be nice if things progressed to where, for example, someone could hand me a database backup I know nothing about, I could look for this handy text file information in the base directory, see: CONFIGURE = '--build=i686-redhat-linux-gnu' ... VERSION = PostgreSQL 8.2.5 Linux host.gregsmith.com 2.6.18-8.1.4.el5xen #1 SMP Thu May 17 05:27:09 EDT 2007 i686 i686 i386 GNU/Linux And now I've got a real good idea what binary this dump came from and how to get the instance back up and running in a few seconds of work. Maybe it's a suggested best-practice for now to save such a thing, maybe it gets included as an automatic feature at initdb time in 8.4, but I think it's a worthwhile idea to work toward helping people with. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] WARNINGs after starting backup server created with PITR
On Jan 18, 2008, at 3:17 PM, Tom Lane wrote: Erik Jones <[EMAIL PROTECTED]> writes: I recently started up a server created using PITR (exact detail below) and received about 125 of these type of errors spanning pages in about 10 different tables: 2008-01-17 21:47:34 CST 7598 :WARNING: relation "table_name" page 5728 is uninitialized --- fixing If you do a vacuum on the master, do you get the same warnings? /me runs VACUUM VERBOSE on the two tables that would matter. Nope. What worries me is, that since I have a verified case of rsync thinking it had successfully transferred a WAL, the same may have happened with these files during the base backup. Does that warning, in fact, entail that there were catalog entries for those files, but that the file was not there, and by "fixing" it the server just created empty files? Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Replication Using Triggers
Hello, On Fri, 18 Jan 2008 17:37:07 + (GMT) [EMAIL PROTECTED] wrote: > This is what I have in mind: > > Have a plperl function that creates connections to all servers in the > cluster (replication partners), and issues the supplied write query to > them, possibly with a tag of some sort to indicated it is a replicated > query (to prevent circular replication). > > Have a post execute trigger that calls the above replication function if > the query was issued directly (as opposed to replicated), and passes it > the query it just executed if it was successful. > > > > Not sure here if you mean literally the SQL query that was executed - in > > which case you have all sorts of problems with sequences and functions > > returning different values. > > Indeed, but sequences at least can be worked around. Post-execute, > sequence number used should be available already, and the sequence offset > and increment can be set so as to ensure they do not clash. That's what > MySQL does (and I must apologize for making the comparison all the time). Sequences are only one (small) problem. What about functions returning different results (volatile) for each call? Just imagine random() or now(). What about inserts or updates selecting parts of table data? You can't be sure to get exactly the same results on the slave. > > But there are plenty of solutions that do a lot better than this. Slony-I > > is > > the most polular. My favourite is a spin on the old db_mirror that used to > > be > > part of the Postgres distribution. > > How would Slony be better? It doesn't seem to support master-master > replication for one. But it work's. At least master-slave. > The problem with this is that the ordering becomes inconsistent with > multiple masters. That would be a bit too inconsistent for my liking. As > far as I can tell, that is also why MySQL's current replication method is > unsuitable for more than ring-replication. Having said that, ring suffers > from similar race conditions, it's more of a hack than a solution. A lot books are written about this topic, many details researched and still master-master replication i a very complicated topic. You should start reading about vector and Lamport clocks. This are the (very) basic (among others) for your sync problem. > Now that I think about it, I'm not actually sure that waiting for global > success before final commit would make update/delete without race > condition as they won't fail, but can still yield inconsistencies due to > race conditions. Still, I think it's worth having despite this issue. Sure, you are right. You now are about to find out what other ppl are researching the last *uhm* 20 years ;-) > > You seem to be re-inventing the wheel, and the re-invention is not quite as > > round as the existing wheel :-) > > Not quite - I think multi-master capability is important. Yes. But master-master or even multi-master is a very complicated topic. So let's start with something more easy, like master-slave. This works, you don't have race conditions and that's a good starting point for more research. > I presume you mean that you cannot attach triggers to schema changes. Yes, > I had thought of that a minute ago. I don't suppose this could be deemed a > feature request for CREATE/ALTER/DROP schema level triggers? ;) More triggers, not only for ddl changes, would be nice anyway. I see fields of application for some of my own projects ;-) Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Replication Using Triggers
Andrew Sullivan wrote: On Fri, Jan 18, 2008 at 04:09:45PM +, [EMAIL PROTECTED] wrote: That's just it - I don't think any user-land libraries would actually be required. One of supposed big advantages of MySQL is it's straightforward replication support. It's quite painful to see PostgreSQL suffer purely for the sake of lack of marketting in this department. :-( The "straigtforward" replication support in MySQL is seriously broken. I am not arguing that it isn't! :-) I am merely trying to implement something at least as good (or rather, no more broken) for PostgreSQL with a minimum of effort. We (by which I really mean "Jan") spent a great deal of time on the design of Slony (and it's add-on nature is a feature, not a bug -- one thing it can do is cross-version upgrades on PostgreSQL versions that were out before Slony was finished being dfesigned) to avoid several nasty corner cases that are sort of waved aside in the MySQL documentation. Designing a replication system that works well 80% of the time is a waste of effort, because the times when you really need it are all already in that 20% of cases that you won't cover with the simple-minded solution. Specifically, 1) That's what MySQL does (it either ignores errors or stops replication on encountering an error, which of those two it does is selectable, but that's about it). That's got to be _the_ most brain-dead approach to replication I've ever heard. It chooses the two least good of all possible worlds, and when you get into your particular version of hell at 0-dark:30, you have to spend some time first figuring out which hell you happen to be in. I couldn't agree more. But I don't see another multi-master replication solution on the horizon. In any case, fire and forget asynchronous replication a-la MySQL. Having a choice between transactions and speed is good. :-) if this is what you believe, then you don't need a database to store your data anyway. I can make your data system faster by storing all your data on /dev/null. Writes will be very fast indeed. Fantastically put. :-) But in the meantime, until a better multi-master replication solution becomes available, I think I'll stick with the current plan. I suppose some kind of a write counter with a rolling write query hash could be implemented. Replicator function issues locks and compares the counters/hashes to establish whether a state is consistent on all nodes before a write query is replicated. It's a kludge and a horrible one at that, and it will slow down the writes under load, but I think it would work for ensuring ordering consistency with not-commutative write operations. Gordan ---(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: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote: > "Dave Page" <[EMAIL PROTECTED]> writes: > > On 18/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote: > Ah. That would work better than what I thought you were suggesting, but > I still don't trust it a whole lot --- there's the problem of "universal > binaries" (PPC & PPC64 & Intel) for instance, which I believe some > people have managed to build Postgres as. Yes, I maintain such a distribution :-). Actually, the suggested output I posted earlier was from a Universal build - the uname output shows Intel only of course, but the CFLAGS do show both architectures. The other way of building a universal binary (other than cross compiling one architecture as I do), is to take native binaries from each platform and literally glue them together. That won't show the differences through CFLAGS, but each section of the universal binary would have it's own native uname output. /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] WARNINGs after starting backup server created with PITR
Erik Jones <[EMAIL PROTECTED]> writes: > I recently started up a server created using PITR (exact detail > below) and received about 125 of these type of errors spanning pages > in about 10 different tables: > 2008-01-17 21:47:34 CST 7598 :WARNING: relation "table_name" page > 5728 is uninitialized --- fixing If you do a vacuum on the master, do you get the same warnings? regards, tom lane ---(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: [GENERAL] 7.4.6 to 8.2.5 - ' changes to $_$
Bricklen Anderson wrote: Steve Clark wrote: function from 7.4.x postgres CREATE FUNCTION update_dns(text, text) RETURNS integer AS 'UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;' LANGUAGE sql; I load it into 8.2.5 - then dump it out and it is changed to CREATE FUNCTION update_dns(text, text) RETURNS integer AS $_$UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;$_$ LANGUAGE sql; notice $_$ where the single ' use to be. Is there some way to keep this from happening? The reason is we have systems in the field that have configuration information stored in 7.4.x. We want to upload that db info load it into an 8.2.5 db massage it then send it back to the unit in the field. I realize there are things I am going to have to fix up in the 8.2.5 dump to be able to load it back into the 7.4.x db but I want to minimize that as much as possible. We have some units in the field running 8.1.3 and it does not change the ' to $_$. Thanks, Steve I think "--disable-dollar-quoting" will work. (pg_dump --help) Thanks a lot. I missed that option in the man page - but now I see it. ---(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: [GENERAL] Forgot to dump old data before re-installing machine
Greg Smith <[EMAIL PROTECTED]> writes: > The usual advice, telling them to replicate the binaries used to create it > in the first place, isn't always the easiest to follow. It seems to me > that including a "environment at cluster creation" note in $PGDATA like > Dave suggests would be helpful for these cases; PG_VERSION just isn't > enough information. I'd also throw in the locale information used for the > cluster, as that seems like something it would be nice to have in simple > text form as well there and is also a spot people are confused about. pg_controldata already provides this information, no? At least barring the case of wrong-time_t-size, which we already know we want to fix. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 7.4.6 to 8.2.5 - ' changes to $_$
Steve Clark wrote: function from 7.4.x postgres CREATE FUNCTION update_dns(text, text) RETURNS integer AS 'UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;' LANGUAGE sql; I load it into 8.2.5 - then dump it out and it is changed to CREATE FUNCTION update_dns(text, text) RETURNS integer AS $_$UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;$_$ LANGUAGE sql; notice $_$ where the single ' use to be. Is there some way to keep this from happening? The reason is we have systems in the field that have configuration information stored in 7.4.x. We want to upload that db info load it into an 8.2.5 db massage it then send it back to the unit in the field. I realize there are things I am going to have to fix up in the 8.2.5 dump to be able to load it back into the 7.4.x db but I want to minimize that as much as possible. We have some units in the field running 8.1.3 and it does not change the ' to $_$. Thanks, Steve I think "--disable-dollar-quoting" will work. (pg_dump --help) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Stupid question about WAL archiving
On Jan 18, 2008, at 2:34 PM, Tom Lane wrote: Glyn Astill <[EMAIL PROTECTED]> writes: I'll set up a cron job to remove them for now, however I'll have a look at pg_standby Keep in mind that if you delete a log segment that's not yet been sent to the standby, you've hosed the standby --- you'll have to take a fresh base backup and reload the standby with it. This is probably okay for disaster recovery, but you don't want your script creating the disaster all by itself. Which is exactly why I pointed out that using pg_standby's -k switch was the more reliable option. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Replication Using Triggers
On Fri, Jan 18, 2008 at 04:09:45PM +, [EMAIL PROTECTED] wrote: > > That's just it - I don't think any user-land libraries would actually be > required. One of supposed big advantages of MySQL is it's straightforward > replication support. It's quite painful to see PostgreSQL suffer purely > for the sake of lack of marketting in this department. :-( The "straigtforward" replication support in MySQL is seriously broken. We (by which I really mean "Jan") spent a great deal of time on the design of Slony (and it's add-on nature is a feature, not a bug -- one thing it can do is cross-version upgrades on PostgreSQL versions that were out before Slony was finished being dfesigned) to avoid several nasty corner cases that are sort of waved aside in the MySQL documentation. Designing a replication system that works well 80% of the time is a waste of effort, because the times when you really need it are all already in that 20% of cases that you won't cover with the simple-minded solution. Specifically, > 1) That's what MySQL does (it either ignores errors or stops replication > on encountering an error, which of those two it does is selectable, but > that's about it). That's got to be _the_ most brain-dead approach to replication I've ever heard. It chooses the two least good of all possible worlds, and when you get into your particular version of hell at 0-dark:30, you have to spend some time first figuring out which hell you happen to be in. In any case, > fire and forget asynchronous replication a-la MySQL. Having a choice > between transactions and speed is good. :-) if this is what you believe, then you don't need a database to store your data anyway. I can make your data system faster by storing all your data on /dev/null. Writes will be very fast indeed. A ---(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: [GENERAL] Stupid question about WAL archiving
Glyn Astill <[EMAIL PROTECTED]> writes: > I'll set up a cron job to remove them for now, however I'll have a > look at pg_standby Keep in mind that if you delete a log segment that's not yet been sent to the standby, you've hosed the standby --- you'll have to take a fresh base backup and reload the standby with it. This is probably okay for disaster recovery, but you don't want your script creating the disaster all by itself. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] plpythonu
On 1/18/08, Erik Jones <[EMAIL PROTECTED]> wrote: > On Jan 18, 2008, at 7:48 AM, Stuart Bishop wrote: > > plpython !=3D plpythonu. > > > > plpython was the 'secure' sandboxed version. The Python devs gave up > > supporting any sort of sandboxing feature in Python declaring it > > impossib= > > le. > > Someone should definitely take a look at this: http:// > sayspy.blogspot.com/2007/05/i-have-finished-securing-python.html > > That guy claims he's locked down the python interpreter there. Interesting. But the problem has never been in locking down the interpreter vX.Y, but locking down interpreter vX.Y+1, when previously work was done on vX.Y. Without upstream developers cooperation this has been too painful. So the interesting thing in the posting is not that he succeeded locking Python down, but that he is pushing the patch to core. -- marko ---(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: [GENERAL] Postgresql 8.2.4 on linux-sparc problem
Zelinskiy Alexander <[EMAIL PROTECTED]> writes: > One again it happen. Looking back at your original post ... maybe you should try a non-Gentoo kernel. Gentoo doesn't have the greatest reputation for stability, and on a non-mainstream architecture like sparc64 it'd likely be even buggier than its usual reputation. The symptoms you cite are consistent with a disk operation getting stuck in the kernel, and a system that locks up to the point of requiring a hard reset sounds even more like a kernel problem. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 7.4.6 to 8.2.5 - ' changes to $_$
function from 7.4.x postgres CREATE FUNCTION update_dns(text, text) RETURNS integer AS 'UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;' LANGUAGE sql; I load it into 8.2.5 - then dump it out and it is changed to CREATE FUNCTION update_dns(text, text) RETURNS integer AS $_$UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;$_$ LANGUAGE sql; notice $_$ where the single ' use to be. Is there some way to keep this from happening? The reason is we have systems in the field that have configuration information stored in 7.4.x. We want to upload that db info load it into an 8.2.5 db massage it then send it back to the unit in the field. I realize there are things I am going to have to fix up in the 8.2.5 dump to be able to load it back into the 7.4.x db but I want to minimize that as much as possible. We have some units in the field running 8.1.3 and it does not change the ' to $_$. Thanks, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [OT] Slony Triggers pulling down performance?
On Fri, Jan 18, 2008 at 03:14:41PM +0800, Ow Mun Heng wrote: > Just wondering if my 'Perceived' feeling that since implementing slony > for master/slave replication of select tables, my master database > performance is getting slower. It imposes a performance penalty, yes. A ---(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: [GENERAL] [OT] Slony Triggers pulling down performance?
[EMAIL PROTECTED] (Ow Mun Heng) writes: > Just wondering if my 'Perceived' feeling that since implementing slony > for master/slave replication of select tables, my master database > performance is getting slower. > > I'm constantly seeing a very high amount of IO wait. ~40-80 according to > vmstat 1 > > and according to atop. (hdb/hdc = raid1 mirror) > DSK | hdb | busy 83% | read1052 | write 50 | avio7 ms > | > DSK | hdc | busy 81% | read1248 | write 49 | avio6 ms > | The triggers generate some extra I/O, as they go off and write tuples into sl_log_1/sl_log_2, so there's certainly a cost, there. When you pull data from sl_log_1/sl_log_2, that will have a cost, too. Replication does not come at zero cost... -- (format nil "[EMAIL PROTECTED]" "cbbrowne" "linuxdatabases.info") http://www3.sympatico.ca/cbbrowne/finances.html "Power tends to corrupt and absolute power corrupts absolutely." -- First Baron Acton, 1834 - 1902 ---(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
[GENERAL] WARNINGs after starting backup server created with PITR
I recently started up a server created using PITR (exact detail
below) and received about 125 of these type of errors spanning pages
in about 10 different tables:
2008-01-17 21:47:34 CST 7598 :WARNING: relation "table_name" page
5728 is uninitialized --- fixing
Looking in the archives I found this thread: http://
archives.postgresql.org/pgsql-general/2007-10/msg01538.php. However,
in that case he wasn't taking the base backup from the primary server
(which I did) and the actual warning was never explained by anybody
as to what it means, does it entail a botched backup?
This backup was made using rsync across co-locations, so before
actually running pg_start_backup and the actual PITR rsync run, we
ran rsync a couple times over a few days to shorten the amount of
time for the main rsync run. Sometime after the standby was current
we had an issue where a WAL file wasn't successfully archived to the
standby server (even though rsync apparently reported that it was as
seen in the successful archive message in the primary server's log),
so I stopped the standby and re-ran the backup process:
pg_start_backup('standby') -> rsync primary to standby ->
pg_stop_backup() -> start standby in recovery mode. At that point,
once the standby was again "caught up" with the most recently shipped
WAL file, I brought it out of recovery mode to run a dump. It was
once it was out of recovery that those warnings appeared.
Is this backup screwed?
Erik Jones
DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.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: [GENERAL] Default_with_oids
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 18 Jan 2008 15:08:43 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Joanne Salerno <[EMAIL PROTECTED]> writes: > > Why is "default_with_oids =false" the default in 8.2.1 ? > > Putting OIDs in user tables has been deprecated for quite some time > now. I suggest looking for a newer version of PgAccess... IIRC PgAccess is long dead. They really need to move to PgAdmin or OOBase or something like that. Joshua D. Drake > > regards, tom lane > > ---(end of > broadcast)--- TIP 4: Have you searched our > list archives? > >http://archives.postgresql.org/ > - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHkQpOATb/zqfZUUQRAtUoAJ99EGdA1J/HhLSuIXjKYwMDDx3bdACeMSM/ MB0vMMEjOEnAdzHeVp7gp3g= =PmWd -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Default_with_oids
Joanne Salerno <[EMAIL PROTECTED]> writes: > Why is "default_with_oids =false" the default in 8.2.1 ? Putting OIDs in user tables has been deprecated for quite some time now. I suggest looking for a newer version of PgAccess... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
On Fri, 18 Jan 2008, Dave Page wrote: For just about zero cost we could drop something like: Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386 Configuration: '--prefix=/usr/local/pgsql83/' '--enable-integer-datetimes' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--without-tk' '--with-bonjour' '--with-pam' '--with-krb5' 'CFLAGS=-O -g -arch i386 -arch ppc' 'LDFLAGS=-ltcl' in a file in $PGDATA Stepping away from the question of whether it would have helped in this specific case for a second, around once a month or so there's someone in a panic here because they have a filesystem copy of a database they can't figure out how to use. Often the original server is a puddle of molten metal or something by that point and the person trying to restore the data is rather stressed. The usual advice, telling them to replicate the binaries used to create it in the first place, isn't always the easiest to follow. It seems to me that including a "environment at cluster creation" note in $PGDATA like Dave suggests would be helpful for these cases; PG_VERSION just isn't enough information. I'd also throw in the locale information used for the cluster, as that seems like something it would be nice to have in simple text form as well there and is also a spot people are confused about. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Default_with_oids
I recently went from Postgres 7.4.8 to 8.2.1 (Linux Platform). After conversion I noticed my forms created with the utility PGACCESS failed. I found since then that PGACCESS requires oids to perform as I used it prior to the conversion. If I set "default_with_oids = true" then the forms work in 8.2.1. My questions: Why is "default_with_oids =false" the default in 8.2.1 ? Does the use of oids effect database performance? Is "default_with_oids" set per server, database, or by table? Thank you. -- Joanne R. Salerno ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stupid question about WAL archiving
Thanks Erik, I'll set up a cron job to remove them for now, however I'll have a look at pg_standby --- Erik Jones <[EMAIL PROTECTED]> wrote: > On Jan 18, 2008, at 11:17 AM, Glyn Astill wrote: > > > My server ran out of disk space because my archive directory was > full > > ow write ahead logs. > > > > My warm standby had lost it's mounted NFS volume and thus stopped > > reading in the archives from the master. > > > > Would I have run out of space if the standby hadn't stopped > reading > > them in? > > > > I.e, should I be deleting the old logs myself or should the warm > > standby be managing them? > > Depends on what you're using run your warm standby in your > recovery.conf. pg_standby has the -k flag for NUMFILESTOKEEP. > Where > I work, we have a cron job that deletes WAL archives more than > three > days old. Admittedly, using pg_standby's -k option is probably > more > reliable. > > Erik Jones > > DBA | Emma® > [EMAIL PROTECTED] > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > > > __ Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SVN event hooks for PL/PGSQL functions and DDL?
I don't mean to be thick headed, but ... On Jan 18, 2008 1:11 AM, Blazej Oleszkiewicz <[EMAIL PROTECTED]> wrote: > Its simple. ... it seems not so simple, as (1) SVN docs say don't modify the repository before a commit, (2) I would think that I would need to call pg_dump BEFORE the commit in order to get a diff on the function code if appropriate, playing hell with (1) above. If the version control doesn't track my on the fly changes to the functions, this little project is all for naught. I will start experimenting today, but I would love any random thoughts on this topic. -W > Create runable script "post-commit" in > /hooks > > script body may look like > > == begin == > #! /bin/sh > pg_dump [option...] [dbname] > /path/to/db/backups/$2.dump > == end == > > "$1" is REPOS > "$2" is REVISION > > > It works as follows > When you make commit on SVN it runs the hook script which makes db > dump (look at pg_dump options). > > Regards, > Blazej > > 2008/1/16, Webb Sprague <[EMAIL PROTECTED]>: > > > In another thread, someone mentioned writing hooks for Subversion that > > would grab function definitions and DDL statements from the current > > database and push them into the repository? > > > > Does anyone have a few scripts/ cookbook examples for this? Is there > > a cookbook section on the postgres wiki where they might go? I am > > sure I can figure this out, but I wouldn't mind cheating > > > > Thx > > > > ---(end of broadcast)--- > > TIP 6: explain analyze is your friend > > > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgresql 8.2.4 on linux-sparc problem
Hello, Tom. One again it happen. postgres 11009 1 0 Jan12 ?00:00:12 /usr/bin/ postmaster -D /data/idx/pgdata --silent-mode=true postgres 11027 11009 0 Jan12 ?00:26:55 postgres: logger process postgres 11029 11009 0 Jan12 ?00:00:21 postgres: writer process postgres 11030 11009 0 Jan12 ?00:16:50 postgres: stats collector process postgres 16751 11009 1 20:40 ?00:00:12 postgres: stat stat 10.0.0.2(41239) idle postgres 16753 11009 0 20:40 ?00:00:11 postgres: stat stat 10.0.0.2(41244) idle postgres 16758 11009 3 20:41 ?00:00:35 postgres: stat stat 10.0.0.2(50546) SELECT postgres 16760 11009 0 20:42 ?00:00:00 postgres: stat stat 10.0.0.2(50573) idle postgres 16761 11009 99 20:42 ?00:16:59 postgres: stat stat 10.0.0.2(50577) idle postgres 16762 11009 0 20:43 ?00:00:00 postgres: stat stat 10.0.0.2(50603) INSERT I tried to use gdb but no success. machupicchu ~ # gdb /usr/bin/postgres 16761 GNU gdb 6.6 Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "sparc-unknown-linux-gnu"... Using host libthread_db library "/lib/libthread_db.so.1". Attaching to program: /usr/bin/postgres, process 16761 I enabled full logging to find a query after which pg die. From postgresql.conf: log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = off log_min_messages = error log_min_error_statement = error log_min_duration_statement = 1000 log_duration = on log_line_prefix = '%m, %s, %r, %p, ' log_statement = 'all' But I can't find nothing about PID 16761 or tcp port 50577 in logs! I can find neighbour, but not this process. Else: # vmstat 1 procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 4 24 1408 25832 180800 373866400 523 315 8 1 84 6 4 24 1408 25832 180800 373866400 0 0 1010 14 0 50 0 50 4 24 1408 25832 180800 373866400 0 0 1005 11 0 50 0 50 4 24 1408 25832 180800 373866400 0 0 1005 11 0 50 0 50 4 24 1408 25832 180800 373866400 0 0 1004 11 0 50 0 50 4 24 1408 25832 180800 373866400 0 0 1005 13 0 50 0 50 4 24 1408 25832 180800 373866400 0 0 1004 13 0 50 0 50 4 24 1408 25832 180800 373866400 0 0 1004 11 0 50 0 50 Looks like problem is in IO-wait? What to do? Where to dig? In some weeks a want to migrate to 2 redundant sun fire v440 servers with 4 storages. Main idea is to have HW redundancy. But now.. Don't know what to say to my boss. HW is fine, but a lot of data loss.. :) p.s. I commented my configuration for _mem options. Now it's default. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stupid question about WAL archiving
On Jan 18, 2008, at 11:17 AM, Glyn Astill wrote: My server ran out of disk space because my archive directory was full ow write ahead logs. My warm standby had lost it's mounted NFS volume and thus stopped reading in the archives from the master. Would I have run out of space if the standby hadn't stopped reading them in? I.e, should I be deleting the old logs myself or should the warm standby be managing them? Depends on what you're using run your warm standby in your recovery.conf. pg_standby has the -k flag for NUMFILESTOKEEP. Where I work, we have a cron job that deletes WAL archives more than three days old. Admittedly, using pg_standby's -k option is probably more reliable. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Replication Using Triggers
On Jan 18, 2008, at 11:37 AM, [EMAIL PROTECTED] wrote: That's one thing. The other problem that most trigger based replication systems have problems with is propogating schema changes - because (I think) you can attach triggers to schema changes. I presume you mean that you cannot attach triggers to schema changes. Yes, I had thought of that a minute ago. I don't suppose this could be deemed a feature request for CREATE/ALTER/DROP schema level triggers? ;) I'm sure he did. Also, there was a little bit of discussion a week or so ago about ddl & ddl/dml (such as TRUNCATE) triggers. With the imminent release of 8.3, now's the time to pipe up with feature requests. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.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: [GENERAL] Stupid question about WAL archiving
On Friday 18 January 2008 09:17:10 Glyn Astill wrote: > My server ran out of disk space because my archive directory was full > ow write ahead logs. > > My warm standby had lost it's mounted NFS volume and thus stopped > reading in the archives from the master. > > Would I have run out of space if the standby hadn't stopped reading > them in? > > I.e, should I be deleting the old logs myself or should the warm > standby be managing them? either delete them yourself, use a cron job to delete them (something like find . -mtime 60 -delete) , or if you are using pg_standby look at -k (which specifies the number of old files to keep > > > ___ > Yahoo! Answers - Got a question? Someone out there knows the answer. Try it > now. > http://uk.answers.yahoo.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 -- Darcy Buskermolen Command Prompt, Inc. +1.503.667.4564 X 102 http://www.commandprompt.com/ PostgreSQL solutions since 1997 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trouble with UTF-8 data
On Jan 18, 2008, at 12:00 AM, Albe Laurenz wrote: 0xEDA7A1 (UTF-8) corresponds to UNICODE code point 0xD9E1, which, when interpreted as a high surrogare and followed by a low surrogate, would correspond to the UTF-16 encoding of a code point between 0x88400 and 0x887FF (depending on the value of the low surrogate). These code points do not correspond to any valid character. So - unless there is a flaw in my reasoning - there's something fishy with these data anyway. Janine, could you give us a hex dump of that line from the copy statement? Certainly. Do you want to see it as it came from the old database, or after I ran it through iconv? Although iconv wasn't able to solve this problem it did fix others in other tables; unfortunately I have no way of knowing if it also mangled some data at the same time. The version of iconv I have does know about UTF16 so I tried using that as the "from" encoding instead of UTF8, but the result had new errors in places where the original data was good, so that was obviously a step backwards. BTW, in case it matters I found out I misidentified the version of PG this data came from - it's actually 7.3.6. thanks, janine ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Forgot to dump old data before re-installing machine
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Dave Page wrote: >> That said, is zlib used by toast or do we have some other code for >> that? If it is used for that, do we record it's presence or absence in >> pg_control? > Nope, toast uses its own compression code. pg_dump/pg_restore use zlib, but I believe there's a pretty specific error message if you try to read a compressed archive with a non-zlib-enabled pg_restore. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] transactionid lock type?
Jan de Visser <[EMAIL PROTECTED]> writes: > So what could make my transaction decide to wait for that other tx? Usually what this indicates is blocking to acquire a row-level lock, eg that transaction is waiting to see if it can update a row that the other one already updated. In 8.1 and later you can find out which row is contended for by seeing what row-level lock is held by the *waiting* transaction (yes, really, not the waited-for one). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [OT] RAID controllers blocking one another?
Joshua D. Drake wrote: That seem that slow for SATA on a write. Assuming 20 (on each controller) disks random write I would guess the best you could pull off would be about 200 Megs a second. That's on a good day. For random writes/reads that sure is OK. I interpreted those numbers as pure sequential benchmark load. -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] case dumbiness in return from functions
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Actually I think the standard mandates case-folding (though to upper > case rather than lower, i.e. the other way around) That's how I read it too. SQL99 5.2 saith 22) The case-normal form of the of a is used for purposes such as and including determination of identifier equivalence, representation in the Definition and Information Schemas, and representation in diagnostics areas. NOTE 44 - Any lower-case letters for which there are no upper- case equivalents are left in their lower-case form. 23) The of a is equivalent to an in which every letter that is a lower- case letter is replaced by the equivalent upper-case letter or letters. This treatment includes determination of equivalence, representation in the Information and Definition Schemas, representation in the diagnostics area, and similar uses. In particular this appears to me to specifically require that column names returned by a SELECT be case-normalized, which is what the OP would like us to not do. regards, tom lane ---(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: [GENERAL] Replication Using Triggers
This is what I have in mind: Have a plperl function that creates connections to all servers in the cluster (replication partners), and issues the supplied write query to them, possibly with a tag of some sort to indicated it is a replicated query (to prevent circular replication). Have a post execute trigger that calls the above replication function if the query was issued directly (as opposed to replicated), and passes it the query it just executed if it was successful. Not sure here if you mean literally the SQL query that was executed - in which case you have all sorts of problems with sequences and functions returning different values. Indeed, but sequences at least can be worked around. Post-execute, sequence number used should be available already, and the sequence offset and increment can be set so as to ensure they do not clash. That's what MySQL does (and I must apologize for making the comparison all the time). If the replication failed on any node, the whole thing gets rolled back. This would effectively give star topology synchronous replication with very little effort, and no need for any external code. Am I missing something obvious that would prevent this from working? It would give replication capabilities better than MySQL's (which can only handle ring based multi-master replication) for the sake of about 100 lines of code. None of the required functionality required is new to PostgreSQL, either. But there are plenty of solutions that do a lot better than this. Slony-I is the most polular. My favourite is a spin on the old db_mirror that used to be part of the Postgres distribution. How would Slony be better? It doesn't seem to support master-master replication for one. I can't talk about how Slony works, but db_mirror uses a very fast 'C' function to capture changes in a set of simple replication tables. A replication process then takes data from those tables and replicates (using actual values not the SQL statement) to any number of other servers. If one of the servers is down, the data remains in the replication tables until that node returns (or is removed). Interesting. I was thinking about making an auxiliary feature that just writes a pending queue log for a server when it cannot establish the $dbh, and when it manages to connect, it attempts to re-play the log before issuing new queries. The problem with this is that the ordering becomes inconsistent with multiple masters. That would be a bit too inconsistent for my liking. As far as I can tell, that is also why MySQL's current replication method is unsuitable for more than ring-replication. Having said that, ring suffers from similar race conditions, it's more of a hack than a solution. Now that I think about it, I'm not actually sure that waiting for global success before final commit would make update/delete without race condition as they won't fail, but can still yield inconsistencies due to race conditions. Still, I think it's worth having despite this issue. The problem with db_mirror was that the replication process was written in Perl. This worked fine for simple tests but was ridiculously slow for replicating tables holding big BYTEA structures. I re-wrote the replication code in 'C' and it can replicate just about arbitrarily complex transactions is close to real-time. Yes, I can see how big blobs can be an issue for performance. :-( You seem to be re-inventing the wheel, and the re-invention is not quite as round as the existing wheel :-) Not quite - I think multi-master capability is important. Is there an existing implementation of this? Perhaps a perl program that creates the required triggers and stored procedures from looking at a schema? What you've described here would be pretty simple to implement. However, I think you've greatly underestimated the performance issues involved. If you need to push data to multiple databases before each transaction commits I think you'll find that pretty slow. Only if transactions are used. I'm basing the requirements on "at least as good as MySQL", which this would meet without transactions. If transactions are wanted they could be enabled, otherwise it could just be fire and forget asynchronous replication a-la MySQL. Having a choice between transactions and speed is good. :-) Synchronous replication tends to imply it works on all servers simultaneously or not on any. If any server fails a transaction it's rolled back on all servers. What you're describing sounds asynchronous to me. Indeed, I spotted that above. The transactions roll back of they fail, but this alone does not quite ensure cross-node consistency of the data. Some kind of special DELETE/UPDATE handling would be required to fix this, but I don't have a definitive idea on how this could be handled. Will have to think about it a bit more. One thing I haven't quite thought of a good way to do with this approach is the e
[GENERAL] Stupid question about WAL archiving
My server ran out of disk space because my archive directory was full ow write ahead logs. My warm standby had lost it's mounted NFS volume and thus stopped reading in the archives from the master. Would I have run out of space if the standby hadn't stopped reading them in? I.e, should I be deleting the old logs myself or should the warm standby be managing them? ___ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.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: [GENERAL] [OT] RAID controllers blocking one another?
Joshua D. Drake wrote: That seem that slow for SATA on a write. Assuming 20 (on each controller) disks random write I would guess the best you could pull off would be about 200 Megs a second. That's on a good day. For random writes/reads that sure is OK. I interpreted those numbers as pure sequential benchmark load. -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [OT] RAID controllers blocking one another?
2 more things: Get BBUs for the controllers (If you have not already). Upgrade to the latest 3ware firmware (code set 9.4.2). There was data corruption bug with RAID 6. -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
"Dave Page" <[EMAIL PROTECTED]> writes:
> On 18/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote:
>> uname is a separate executable. If you do system("uname") you'll get
>> results that reflect how uname was built, not how Postgres was built.
> My suggestion was that we take the output of uname at configure/build
> time and bung it in a macro, not do anything with system() at
> runtime...
Ah. That would work better than what I thought you were suggesting, but
I still don't trust it a whole lot --- there's the problem of "universal
binaries" (PPC & PPC64 & Intel) for instance, which I believe some
people have managed to build Postgres as.
> Anyway, Peter's suggestion seems much tidier.
Agreed. Also we could have it today if we base it off inspection of
pg_control_version.
regards, tom lane
---(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: [GENERAL] [OT] RAID controllers blocking one another?
Sean Davis wrote: We are PURE amateurs, so that is possible. We are running 12-disk RAID6 (750GB drives--this might be a difference from what you are thinking) partitions under LVM. We have write-cache enabled and are using ext3 filesystems. Any suggestions? We use the 9650 controllers in RAID 6 a lot. With 12 discs you should be able to get about 550MB/sec sequential write and 680MB/sec read easily. But it's already pretty clear to me why you can't get anywhere near that: The first thing is that ext3 is a file system designed for small block devices that have a single physical head to seek -- not for large arrays that consist of multiple physical devices that can seek independently. Especially this is true for the anticipatory I/O scheduler that is used in conjunction with ext3. Additionally ext3 does not know any form of stripe alignment which is an absolute requirement for arrays that use a parity based RAID level (RAID 5/6). If you don't stripe align your file system to the stripe width configured in your controller, you will suffer massive I/O trashing. Your controller and discs will be busy with Load-XOR-Store cycles. That means that your discs will do a ton of micro seeks on every write instead of just writing the chunk of data out. Both performance and lifespan of your discs will suffer for no reason. My recommendation is either Linux XFS or Solaris ZFS. Both are designed form the ground up for such setups. If this box is not yet in production and you like to test something I can give you a fitting mkfs.xfs line for your setup -- the defaults won't work well. The last thing to note is that LVM will mess with any kind of stripe alignment, so it's quite useless for parity based arrays. If you need serious volume management look into ZFS. Finally you might need to do tweak some scheduler params, depending if you chose to go with deadline or CFQ. Deadline is recommend by 3ware and usually the best choice for PostgreSQL as well. Speaking of PostgreSQL, put $PGDATA on a RAID 10 array, it will suffer on RAID 5/6. -- Best regards, Hannes Dorbath ---(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: [GENERAL] [OT] RAID controllers blocking one another?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 18 Jan 2008 16:54:17 +0100 Hannes Dorbath <[EMAIL PROTECTED]> wrote: > Sean Davis wrote: > > 150-200MB/s writing and somewhat faster for reading > > That actually seems dead slow. Whatever RAID level you configured, > there is no sane way for it to be that slow. Is this a RAID 5/6 > array? Did you forgot to align your file system to stripe boundaries? That seem that slow for SATA on a write. Assuming 20 (on each controller) disks random write I would guess the best you could pull off would be about 200 Megs a second. That's on a good day. So 200MBs per controllers isn't that out of bounds. I would agree that seems slow for reads though. And to answer the question, no I have not seen the behavior he is experiencing but to be honest I would never run that many sata drives. I would have long pushed to SAS on that scale. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHkOFvATb/zqfZUUQRAoPLAKCGLze19/Z5VZ/d4TVEQQUC18dTcgCeLVTD Abjvqf686r0eX2K1sxo8giY= =G5t+ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote:
> uname is a separate executable. If you do system("uname") you'll get
> results that reflect how uname was built, not how Postgres was built.
Right, I realise it's a seperate executable, but doesn't configure
rely on it anyway? Meaning if someones system has a uname that tells
configure it's a PPC when it's actually an Intel, the resulting binary
is likely to go bang anyway, assuming it even builds.
My suggestion was that we take the output of uname at configure/build
time and bung it in a macro, not do anything with system() at
runtime...
Anyway, Peter's suggestion seems much tidier.
/D
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
Peter Eisentraut <[EMAIL PROTECTED]> writes: > What might be better is if we had an explicit endianness mark in pg_control > rather than relying on users discovering endianness problems by seemingly > corrupted version numbers. Chicken-and-egg problem there: you won't know if there's an endianness flag to check without having tested pg_control_version. What would work better is to add some code that checks whether pg_control_version looks like the byte-swap of a small number, and prints a suitably modified error message if so. I would not previously have thought this was worth the trouble, but given what we now know about Apple's migration process, it might be worth another half dozen lines of code and a new error message. What was that about string freeze ;-) ? 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: [GENERAL] Forgot to dump old data before re-installing machine
"Dave Page" <[EMAIL PROTECTED]> writes:
> On 18/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Zero cost and also zero benefit. The missing piece of information here
>> was that the executable being used was running under PPC emulation, and
>> I'll bet money that there would have been nothing in either uname or
>> pg_config output that would have told us that.
> I'd wager there would be a fairly good chance that a PPC-only binary
> on a Mac would most likely have been built on a PPC, and thus mention
> that in the uname output at build time. I can't imagine many folks are
> building PPC-only binaries on Intels.
uname is a separate executable. If you do system("uname") you'll get
results that reflect how uname was built, not how Postgres was built.
I think this is likely to lead to more confusion, not less --- if we'd
had such output in the directory, it might have led us to disregard
the clear evidence of the wrong-endian version number, and fruitlessly
bark up some other tree instead.
regards, tom lane
---(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: [GENERAL] Forgot to dump old data before re-installing machine
Dave Page wrote: > That said, is zlib used by toast or do we have some other code for > that? If it is used for that, do we record it's presence or absence in > pg_control? Nope, toast uses its own compression code. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] transactionid lock type?
Hello, I have two concurrent transactions, both heavy R/W type things. I now see that one is backed up behind in the other, and pg_locks shows me this: sep=# select locktype, transactionid, transaction from pg_locks where not granted; locktype| transactionid | transaction ---+---+- transactionid | 3391481 | 3391528 (1 row) The TFM tells me (in the description of pg_locks): "Every transaction holds an exclusive lock on its transaction ID for its entire duration. If one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID. That will succeed only when the other transaction terminates and releases its locks." So what could make my transaction decide to wait for that other tx? I've checked to see that all updates to rows shared by the two transactions are done late in the process, but this lock happens almost immediately after the second tx starts... Comments? jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Replication Using Triggers
[EMAIL PROTECTED] wrote: On Fri, 18 Jan 2008, Erik Jones wrote: This is what I have in mind: Have a plperl function that creates connections to all servers in the cluster (replication partners), and issues the supplied write query to them, possibly with a tag of some sort to indicated it is a replicated query (to prevent circular replication). Have a post execute trigger that calls the above replication function if the query was issued directly (as opposed to replicated), and passes it the query it just executed if it was successful. Not sure here if you mean literally the SQL query that was executed - in which case you have all sorts of problems with sequences and functions returning different values. If the replication failed on any node, the whole thing gets rolled back. This would effectively give star topology synchronous replication with very little effort, and no need for any external code. Am I missing something obvious that would prevent this from working? It would give replication capabilities better than MySQL's (which can only handle ring based multi-master replication) for the sake of about 100 lines of code. None of the required functionality required is new to PostgreSQL, either. But there are plenty of solutions that do a lot better than this. Slony-I is the most polular. My favourite is a spin on the old db_mirror that used to be part of the Postgres distribution. I can't talk about how Slony works, but db_mirror uses a very fast 'C' function to capture changes in a set of simple replication tables. A replication process then takes data from those tables and replicates (using actual values not the SQL statement) to any number of other servers. If one of the servers is down, the data remains in the replication tables until that node returns (or is removed). The problem with db_mirror was that the replication process was written in Perl. This worked fine for simple tests but was ridiculously slow for replicating tables holding big BYTEA structures. I re-wrote the replication code in 'C' and it can replicate just about arbitrarily complex transactions is close to real-time. You seem to be re-inventing the wheel, and the re-invention is not quite as round as the existing wheel :-) Is there an existing implementation of this? Perhaps a perl program that creates the required triggers and stored procedures from looking at a schema? What you've described here would be pretty simple to implement. However, I think you've greatly underestimated the performance issues involved. If you need to push data to multiple databases before each transaction commits I think you'll find that pretty slow. Only if transactions are used. I'm basing the requirements on "at least as good as MySQL", which this would meet without transactions. If transactions are wanted they could be enabled, otherwise it could just be fire and forget asynchronous replication a-la MySQL. Having a choice between transactions and speed is good. :-) Synchronous replication tends to imply it works on all servers simultaneously or not on any. If any server fails a transaction it's rolled back on all servers. What you're describing sounds asynchronous to me. One thing I haven't quite thought of a good way to do with this approach is the equivalent of the useful (albeit deprecated) LOAD DATA FROM MASTER command, that gets the server in sync by dropping and re-loading all the tables from the master(s) (or rather, peers in a multi-master star replication), and enables it in the replication. It would be neater than requiring downtime or global write locks. But I guess that could wait until version 2. :) That's one thing. The other problem that most trigger based replication systems have problems with is propogating schema changes - because (I think) you can attach triggers to schema changes. Thanks. Gordan Pete -- Peter Wilson : http://www.whitebeam.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Am Freitag, 18. Januar 2008 schrieb Dave Page: > > It got figured out when someone who knew what they were looking for > > peeked at the byte ordering in a file which for all we knew at the > > time might have been damaged anyway > > What might be better is if we had an explicit endianness mark in pg_control > rather than relying on users discovering endianness problems by seemingly > corrupted version numbers. Seems reasonable to me. Obviously I'd mixed up the datetime/time_t issues I ran into previously, so having the configure options as well would be largely useless. That said, is zlib used by toast or do we have some other code for that? If it is used for that, do we record it's presence or absence in pg_control? /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] transactionid lock type?
Hello, I have two concurrent transactions, both heavy R/W type things. I now see that one is backed up behind in the other, and pg_locks shows me this: sep=# select locktype, transactionid, transaction from pg_locks where not granted; locktype| transactionid | transaction ---+---+- transactionid | 3391481 | 3391528 (1 row) The TFM tells me (in the description of pg_locks): "Every transaction holds an exclusive lock on its transaction ID for its entire duration. If one transaction finds it necessary to wait specifically for another transaction, it does so by attempting to acquire share lock on the other transaction ID. That will succeed only when the other transaction terminates and releases its locks." So what could make my transaction decide to wait for that other tx? I've checked to see that all updates to rows shared by the two transactions are done late in the process, but this lock happens almost immediately after the second tx starts... Comments? jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Forgot to dump old data before re-installing machine
Am Freitag, 18. Januar 2008 schrieb Dave Page: > It got figured out when someone who knew what they were looking for > peeked at the byte ordering in a file which for all we knew at the > time might have been damaged anyway What might be better is if we had an explicit endianness mark in pg_control rather than relying on users discovering endianness problems by seemingly corrupted version numbers. > For just about zero cost we could drop something like: > > > Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed > Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386 I think we should address the problem were it happens. Adding this output will increase the amount of information available for causing confusion, while it would probably still require expert knowledge to read an endianness issue out of that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote: > Hm? integer_datetimes is encoded separately and there's a very specific > error message if it's wrong. The case I think you are remembering was > caused by a width-of-time_t discrepancy, which should be fixed but it's > got nothing to do with anything else. Yeah, I think you're probably right there. > Zero cost and also zero benefit. The missing piece of information here > was that the executable being used was running under PPC emulation, and > I'll bet money that there would have been nothing in either uname or > pg_config output that would have told us that. I'd wager there would be a fairly good chance that a PPC-only binary on a Mac would most likely have been built on a PPC, and thus mention that in the uname output at build time. I can't imagine many folks are building PPC-only binaries on Intels. How much money did you have in mind? :-) /D ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
"Dave Page" <[EMAIL PROTECTED]> writes: > On 18/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote: >> That's what pg_control is for. We figured out easily enough that this >> was an endianness problem; having had "big endian" somewhere in >> cleartext wouldn't have improved matters. > It got figured out when someone who knew what they were looking for > peeked at the byte ordering in a file which for all we knew at the > time might have been damaged anyway - and the same test wouldn't have > spotted an integer_datetimes difference for example, something which > bit Greg & I recently and had us puzzled for a while. Hm? integer_datetimes is encoded separately and there's a very specific error message if it's wrong. The case I think you are remembering was caused by a width-of-time_t discrepancy, which should be fixed but it's got nothing to do with anything else. > For just about zero cost we could drop something like: > > Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed > Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386 > Configuration: '--prefix=/usr/local/pgsql83/' > '--enable-integer-datetimes' '--with-openssl' '--with-perl' > '--with-python' '--with-tcl' '--without-tk' '--with-bonjour' > '--with-pam' '--with-krb5' 'CFLAGS=-O -g -arch i386 -arch ppc' > 'LDFLAGS=-ltcl' > Zero cost and also zero benefit. The missing piece of information here was that the executable being used was running under PPC emulation, and I'll bet money that there would have been nothing in either uname or pg_config output that would have told us that. 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: [GENERAL] Replication Using Triggers
On Fri, 18 Jan 2008, Erik Jones wrote: Is there any reason why PostgreSQL replication solutions are all add-on 3rd party ones? Because no one solution would be appropriate for everyone. The core team and contributors feel that their time is better spent on the database itself rather than developing and maintaining multiple different replication solutions and dealing with the support thereof. What has been done is to add some extra hooks in 8.3 for replication triggers that can help to specialize when/if a given trigger fires. Hmm, selective trigger firing sounds interesting. Is there any reason why replication couldn't be implemented using triggers and a handful of stored procedures? That's usually how it's done. Well, plus some external user-land application libraries. That's just it - I don't think any user-land libraries would actually be required. One of supposed big advantages of MySQL is it's straightforward replication support. It's quite painful to see PostgreSQL suffer purely for the sake of lack of marketting in this department. :-( This is what I have in mind: Have a plperl function that creates connections to all servers in the cluster (replication partners), and issues the supplied write query to them, possibly with a tag of some sort to indicated it is a replicated query (to prevent circular replication). Have a post execute trigger that calls the above replication function if the query was issued directly (as opposed to replicated), and passes it the query it just executed if it was successful. If the replication failed on any node, the whole thing gets rolled back. That sounds pretty brittle. Do you really want all progress in your databases to stop if there is a network issue to a single server? 1) That's what MySQL does (it either ignores errors or stops replication on encountering an error, which of those two it does is selectable, but that's about it). 2) If there is a network/server issue, that would be detected because the $dbh would break. If the $dbh breaks, then plperl can either attempt to re-connect, or failing that, boot the node from the replication cluster (could have a node list with active/fenced flag in a separate config schema). Neither approach would be difficult to implement. Then it could just not bother reconnecting the fenced node until the user updates the node status in the said config schema table. Configuration schema could also be replicated. This would effectively give star topology synchronous replication with very little effort, and no need for any external code. Am I missing something obvious that would prevent this from working? It would give replication capabilities better than MySQL's (which can only handle ring based multi-master replication) for the sake of about 100 lines of code. None of the required functionality required is new to PostgreSQL, either. Is there an existing implementation of this? Perhaps a perl program that creates the required triggers and stored procedures from looking at a schema? What you've described here would be pretty simple to implement. However, I think you've greatly underestimated the performance issues involved. If you need to push data to multiple databases before each transaction commits I think you'll find that pretty slow. Only if transactions are used. I'm basing the requirements on "at least as good as MySQL", which this would meet without transactions. If transactions are wanted they could be enabled, otherwise it could just be fire and forget asynchronous replication a-la MySQL. Having a choice between transactions and speed is good. :-) One thing I haven't quite thought of a good way to do with this approach is the equivalent of the useful (albeit deprecated) LOAD DATA FROM MASTER command, that gets the server in sync by dropping and re-loading all the tables from the master(s) (or rather, peers in a multi-master star replication), and enables it in the replication. It would be neater than requiring downtime or global write locks. But I guess that could wait until version 2. :) That's why most of the available third party solutions are asynchronous. The biggest options are out there are Slony and Londiste (both master-slave, asynchronous) and Bucardo (asynchronous, but supports both master-master and master-slave) which, as you would have it, is written in Perl. I looked at all of the above, and they all seemed (to meat least) to involve unnecessary complication or limitations I saw as unreasonable (or both). I looked at Bucardo in detail, and I was rather disappointed to see that it only supports two master nodes at the moment. Thanks. Gordan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote: > "Dave Page" <[EMAIL PROTECTED]> writes: > > Note to the other hackers - is it worth having initdb dump the > > architecture details and configure options used into the cluster in a > > human readble form so we can pickup on this sort of thing more easily > > in the future? > > That's what pg_control is for. We figured out easily enough that this > was an endianness problem; having had "big endian" somewhere in > cleartext wouldn't have improved matters. It got figured out when someone who knew what they were looking for peeked at the byte ordering in a file which for all we knew at the time might have been damaged anyway - and the same test wouldn't have spotted an integer_datetimes difference for example, something which bit Greg & I recently and had us puzzled for a while. For just about zero cost we could drop something like: Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386 Configuration: '--prefix=/usr/local/pgsql83/' '--enable-integer-datetimes' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--without-tk' '--with-bonjour' '--with-pam' '--with-krb5' 'CFLAGS=-O -g -arch i386 -arch ppc' 'LDFLAGS=-ltcl' in a file in $PGDATA which would make it much easier for users and hackers to see where the cluster came from and compare it to the actual build. /D ---(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: [GENERAL] [OT] RAID controllers blocking one another?
Sean Davis wrote: 150-200MB/s writing and somewhat faster for reading That actually seems dead slow. Whatever RAID level you configured, there is no sane way for it to be that slow. Is this a RAID 5/6 array? Did you forgot to align your file system to stripe boundaries? -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [OT] RAID controllers blocking one another?
Sean Davis wrote: I know this is off-topic, but I know lots of folks here deal with very large disk arrays; it is hard to get real-world input on machines such as these. In my experience the 3ware support staff is competent and replies within one business day. Just sign up at: https://www.3ware.com/userregistration.asp?uType=eUser&boolCompany=1 and place a ticket. Though let us know when you got that solved please. -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Replication Using Triggers
On Jan 18, 2008, at 9:21 AM, [EMAIL PROTECTED] wrote: Hi, Is there any reason why PostgreSQL replication solutions are all add-on 3rd party ones? Because no one solution would be appropriate for everyone. The core team and contributors feel that their time is better spent on the database itself rather than developing and maintaining multiple different replication solutions and dealing with the support thereof. What has been done is to add some extra hooks in 8.3 for replication triggers that can help to specialize when/if a given trigger fires. Is there any reason why replication couldn't be implemented using triggers and a handful of stored procedures? That's usually how it's done. Well, plus some external user-land application libraries. This is what I have in mind: Have a plperl function that creates connections to all servers in the cluster (replication partners), and issues the supplied write query to them, possibly with a tag of some sort to indicated it is a replicated query (to prevent circular replication). Have a post execute trigger that calls the above replication function if the query was issued directly (as opposed to replicated), and passes it the query it just executed if it was successful. If the replication failed on any node, the whole thing gets rolled back. That sounds pretty brittle. Do you really want all progress in your databases to stop if there is a network issue to a single server? This would effectively give star topology synchronous replication with very little effort, and no need for any external code. Am I missing something obvious that would prevent this from working? It would give replication capabilities better than MySQL's (which can only handle ring based multi-master replication) for the sake of about 100 lines of code. None of the required functionality required is new to PostgreSQL, either. Is there an existing implementation of this? Perhaps a perl program that creates the required triggers and stored procedures from looking at a schema? What you've described here would be pretty simple to implement. However, I think you've greatly underestimated the performance issues involved. If you need to push data to multiple databases before each transaction commits I think you'll find that pretty slow. That's why most of the available third party solutions are asynchronous. The biggest options are out there are Slony and Londiste (both master- slave, asynchronous) and Bucardo (asynchronous, but supports both master-master and master-slave) which, as you would have it, is written in Perl. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] plpythonu
On Jan 18, 2008, at 7:48 AM, Stuart Bishop wrote: plpython !=3D plpythonu. plpython was the 'secure' sandboxed version. The Python devs gave up supporting any sort of sandboxing feature in Python declaring it impossib= le. Someone should definitely take a look at this: http:// sayspy.blogspot.com/2007/05/i-have-finished-securing-python.html That guy claims he's locked down the python interpreter there. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Forgot to dump old data before re-installing machine
"Dave Page" <[EMAIL PROTECTED]> writes: > Note to the other hackers - is it worth having initdb dump the > architecture details and configure options used into the cluster in a > human readble form so we can pickup on this sort of thing more easily > in the future? That's what pg_control is for. We figured out easily enough that this was an endianness problem; having had "big endian" somewhere in cleartext wouldn't have improved matters. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
"Dave Page" <[EMAIL PROTECTED]> writes: > As for the real problem (on the same hardware), when you rebuilt > Postgres on your new machine did you change any of the configure > options that MacPorts would have used from what would have been used > previously (I assume they can be overridden)? There's not that much that can be overridden that would affect the layout of pg_control. The only other thing I can think of at the moment is that moving from 32-bit to 64-bit time_t can screw things up --- but that shouldn't affect the interpretation of the pg_control version number, which as already noted certainly looks like it's the wrong endianness. Stefan, could you post the actual pg_control file as a binary attachment? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Replication Using Triggers
Hi, Is there any reason why PostgreSQL replication solutions are all add-on 3rd party ones? Is there any reason why replication couldn't be implemented using triggers and a handful of stored procedures? This is what I have in mind: Have a plperl function that creates connections to all servers in the cluster (replication partners), and issues the supplied write query to them, possibly with a tag of some sort to indicated it is a replicated query (to prevent circular replication). Have a post execute trigger that calls the above replication function if the query was issued directly (as opposed to replicated), and passes it the query it just executed if it was successful. If the replication failed on any node, the whole thing gets rolled back. This would effectively give star topology synchronous replication with very little effort, and no need for any external code. Am I missing something obvious that would prevent this from working? It would give replication capabilities better than MySQL's (which can only handle ring based multi-master replication) for the sake of about 100 lines of code. None of the required functionality required is new to PostgreSQL, either. Is there an existing implementation of this? Perhaps a perl program that creates the required triggers and stored procedures from looking at a schema? Thanks. Gordan ---(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: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Stefan Schwarzer <[EMAIL PROTECTED]> wrote: > I don't understand it either, which is why I was wondering if it was > running under some PPC emulation (can you run standard mac software or > do you have to get special Intel versions).] Yes, Apple have an emulation layer called Rosetta - but you said you used MacPorts so you should have a native build. > I have no idea how my old postgres config was. I've contacted already > [EMAIL PROTECTED] to see if he still has the old .dmgs, which is I > think what I used to install postgres with. Aha - previously undisclosed info :-) Yes, then it does seem quite feasible that your old build was not only differing in achitecture, but possibly also other configuration options that would have similar effects. Note to the other hackers - is it worth having initdb dump the architecture details and configure options used into the cluster in a human readble form so we can pickup on this sort of thing more easily in the future? Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom suggested. So, I copied all files to a PPC, but which runs Linux - don't know if this is important. Now, it tells me: "Fatal error: Incorrect checksum on control file" Any way out of this? Thanks for any advice. That's the kind of error I'd expect to see if you try to start an Intel data directory on PPC or vice-versa. You said earlier this was data from your Intel Mac being reloaded on the same Intel Mac. If thats the case, put the PPC away before you confuse yourself :-) As for the real problem (on the same hardware), when you rebuilt Postgres on your new machine did you change any of the configure options that MacPorts would have used from what would have been used previously (I assume they can be overridden)? I don't know if you'd see exactly the same symptoms you have, but changing settings like integer datetimes will break things in a similar way. Here is some input from Martijn: ___ Well, it's clearly byte-swapped. So whatever the database was running on it was on a PPC or some other big-endian machine. The give away is that bytes 16&17 are "20 00" rather than "00 20". You can check a file with: $ od -t x1 -j 16 -N 2 < filename 020 00 20 022 That's for Intel, on the file you sent me it's: 020 20 00 022 I don't understand it either, which is why I was wondering if it was running under some PPC emulation (can you run standard mac software or do you have to get special Intel versions). ___ I have no idea how my old postgres config was. I've contacted already [EMAIL PROTECTED] to see if he still has the old .dmgs, which is I think what I used to install postgres with. Gush, gush, gush ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plpythonu
Alexandre da Silva wrote:
> Hello,
> someone can tell me if is secure to create external python modules and
> import them to functions/procedures/triggers to use?
Its fine as long as you trust the users with write access to your PYTHONP=
ATH.
> Another question is that I have read in some discussion list (old
> message year 2003) the possibility of plpython be removed from
> postgresql, this information is valid yet?
plpython !=3D plpythonu.
plpython was the 'secure' sandboxed version. The Python devs gave up
supporting any sort of sandboxing feature in Python declaring it impossib=
le.
plpythonu is unrestricted, so if you have the ability to create plpythonu=
stored procedures you effectively have full filesystem access on your
database server as the user your database is running as. So don't put
open('/etc/passwd','w') in your plpythonu code.
--=20
Stuart Bishop <[EMAIL PROTECTED]>
http://www.stuartbishop.net/
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Stefan Schwarzer <[EMAIL PROTECTED]> wrote: > Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom > suggested. > > So, I copied all files to a PPC, but which runs Linux - don't know if > this is important. Now, it tells me: > > "Fatal error: Incorrect checksum on control file" > > Any way out of this? Thanks for any advice. That's the kind of error I'd expect to see if you try to start an Intel data directory on PPC or vice-versa. You said earlier this was data from your Intel Mac being reloaded on the same Intel Mac. If thats the case, put the PPC away before you confuse yourself :-) As for the real problem (on the same hardware), when you rebuilt Postgres on your new machine did you change any of the configure options that MacPorts would have used from what would have been used previously (I assume they can be overridden)? I don't know if you'd see exactly the same symptoms you have, but changing settings like integer datetimes will break things in a similar way. Regards, Dave ---(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: [GENERAL] Forgot to dump old data before re-installing machine
On Jan 18, 2008 7:25 AM, Stefan Schwarzer <[EMAIL PROTECTED]> wrote: > > > >>> Did you just move from a PPC-based Mac to an Intel-based one? > >>> If so, you're out of luck --- you need to go back to the PPC > >>> to make a dump of those files. > >>> > >> > >> No, I just re-installed my Intel Mac. First I just upgraded from > >> Tiger to Leopard (without getting my database to run; but I didn't > >> put much effort into it); and then I completely erased the disk and > >> installed Leopard from scratch. > > > > H. Can't be that I am standing now there having lost my > > data, no? Please, any faintest idea what I can try? > > Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom > suggested. > > So, I copied all files to a PPC, but which runs Linux - don't know if > this is important. Now, it tells me: > > "Fatal error: Incorrect checksum on control file" > > Any way out of this? Thanks for any advice. Yes, you need to set up a machine running the same OS and pgsql version and build as before. ---(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: [GENERAL] Forgot to dump old data before re-installing machine
Did you just move from a PPC-based Mac to an Intel-based one? If so, you're out of luck --- you need to go back to the PPC to make a dump of those files. No, I just re-installed my Intel Mac. First I just upgraded from Tiger to Leopard (without getting my database to run; but I didn't put much effort into it); and then I completely erased the disk and installed Leopard from scratch. H. Can't be that I am standing now there having lost my data, no? Please, any faintest idea what I can try? Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom suggested. So, I copied all files to a PPC, but which runs Linux - don't know if this is important. Now, it tells me: "Fatal error: Incorrect checksum on control file" Any way out of this? Thanks for any advice. Stef ---(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: [GENERAL] Online Oracle to Postgresql data migration
On Jan 18, 2008 7:45 AM, Gregory Williamson < [EMAIL PROTECTED]> wrote: > Mayuresh Nirhali wrote: > > > > Josh Harrison wrote: > > > > > > > > > Thanks . > > > We have around 3TB of data now running in Oracle. I have done > > > replication in postgresql but not much in Oracle. Is there a way you > > > can replicate between Oracle and Postgresql. For writing the custom > > > codes do you suggest any preferred language ...like java, perl etc? > > See, if this can help, > > https://daffodilreplicator.dev.java.net/ > > > > ... and do let us know if you find it useful. > > Rgds > > Mayuresh > > At least from my browser the links to documentation, comparisons, FAQ and > download all fail ... > The same for me. I couldn't open anything there josh
Re: [GENERAL] Online Oracle to Postgresql data migration
Mayuresh Nirhali wrote: > > Josh Harrison wrote: > > > > > > Thanks . > > We have around 3TB of data now running in Oracle. I have done > > replication in postgresql but not much in Oracle. Is there a way you > > can replicate between Oracle and Postgresql. For writing the custom > > codes do you suggest any preferred language ...like java, perl etc? > See, if this can help, > https://daffodilreplicator.dev.java.net/ > > ... and do let us know if you find it useful. > Rgds > Mayuresh At least from my browser the links to documentation, comparisons, FAQ and download all fail ... Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [GENERAL] case dumbiness in return from functions
Nico Sabbi wrote: > yet I find disturbing that Postgres doesn't make the effort > to respect the case specified by the user. It does -- if you quote the names. > If I created a field > called "REF" why should Postgres call it "ref" in the output of queries > if the standard doesn't specify any obligation to convert the name ? Actually I think the standard mandates case-folding (though to upper case rather than lower, i.e. the other way around) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] case dumbiness in return from functions
On Jan 18, 2008, at 1:14 PM, Nico Sabbi wrote: Tom Lane ha scritto: The SQL standard specifies that unquoted identifiers are case- insensitive. You're welcome to spell them as camelCase in your source code if you feel like it, but don't expect that PG, or any other SQL-compliant database, will pay attention. regards, tom lane yet I find disturbing that Postgres doesn't make the effort to respect the case specified by the user. If I created a field called "REF" why should Postgres call it "ref" in the output of queries if the standard doesn't specify any obligation to convert the name ? If you want to use case sensitive identifiers, then quote them! It's not that hard. In your example above you're doing just that, so your statement does not even apply ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47909a669491882451502! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] case dumbiness in return from functions
Nico Sabbi wrote: > > Tom Lane ha scritto: > > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > > >> After discovering that pg_get_serial_sequence behaves in a bit > >> strange way[1] when it deals to case sensitiveness > >> > > > > The SQL standard specifies that unquoted identifiers are case-insensitive. > > You're welcome to spell them as camelCase in your source code if you > > feel like it, but don't expect that PG, or any other SQL-compliant > > database, will pay attention. > > > > regards, tom lane > > yet I find disturbing that Postgres doesn't make the effort > to respect the case specified by the user. If I created a field > called "REF" why should Postgres call it "ref" in the output of queries > if the standard doesn't specify any obligation to convert the name ? > I'd like to have the possibility to enable this feature in future releases. Why should it PostgreSQL "make the effort" ? Tom was _very_ clear in the SQL standard. Oracle and Informix also ignore your capitals, although they behave slightly differently in forcing things to upper or lower case. In Informix: create table FOO (FooId SERIAL PRIMARY KEY); INFO - foo: Columns Indexes Privileges References Status ... Display column names and data types for a table. --- [EMAIL PROTECTED] -- Press CTRL-W for Help Column name TypeNulls fooidserial no Bottom line: well documented in all databases I have seen. Not very important -- if you really care (why on earth would you?) then double quote things like table and column names. I would _far_ rather have developers work on resolving subtle issues, or adding *useful* features than this sort of basura. Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [GENERAL] pg_dumpall
Erik Jones wrote: On Jan 17, 2008, at 1:08 PM, Greg Smith wrote: On Thu, 17 Jan 2008, Tom Lane wrote: There isn't any good way to guarantee time coherence of dumps across two databases. Whether there's a good way depends on what you're already doing. If you're going to the trouble of making a backup using PITR anyway, it's not hard to stop applying new logs to that replica and dump from it to get a point in time backup across all the databases. That's kind of painful now because you have to start the server to run pg_dumpall, so resuming recovery is difficult, but you can play filesystem tricks to make that easier. Actually, this exact scenario brings up a question I was thinking of last night. If you stop a PITR standby server and bring it up to dump from, will all of the database file have something written to them at some point during the dump? Transactional information is what I'd assume would be written, if so, but I'm not really sure of the low level details there. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com Thanks for everyone that replied to my query about pg_dumpall. Now another question/issue - anytime I usr createdb the resulting db ends up with UTF-8 encoding unless I use the -E switch. Is there a way to make the default be sql_ascii? postgres version is 8.2.5 Thanks again Steve ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] case dumbiness in return from functions
On Fri, 18 Jan 2008 13:14:33 +0100 Nico Sabbi <[EMAIL PROTECTED]> wrote: > yet I find disturbing that Postgres doesn't make the effort > to respect the case specified by the user. If I created a field > called "REF" why should Postgres call it "ref" in the output of > queries if the standard doesn't specify any obligation to convert > the name ? I'd like to have the possibility to enable this feature > in future releases. http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php There are pro and cons. In my dreams I'd like a good Genius to fix all the issues even the one I can barely understand and have a case preserving pg. In reality I'll take more care with quotations and never post after midnight. -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] case dumbiness in return from functions
Tom Lane ha scritto: Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: After discovering that pg_get_serial_sequence behaves in a bit strange way[1] when it deals to case sensitiveness The SQL standard specifies that unquoted identifiers are case-insensitive. You're welcome to spell them as camelCase in your source code if you feel like it, but don't expect that PG, or any other SQL-compliant database, will pay attention. regards, tom lane yet I find disturbing that Postgres doesn't make the effort to respect the case specified by the user. If I created a field called "REF" why should Postgres call it "ref" in the output of queries if the standard doesn't specify any obligation to convert the name ? I'd like to have the possibility to enable this feature in future releases. As for portability: it may not be a concern when you have at disposal the best DB around :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Online Oracle to Postgresql data migration
Josh Harrison wrote: Thanks . We have around 3TB of data now running in Oracle. I have done replication in postgresql but not much in Oracle. Is there a way you can replicate between Oracle and Postgresql. For writing the custom codes do you suggest any preferred language ...like java, perl etc? See, if this can help, https://daffodilreplicator.dev.java.net/ ... and do let us know if you find it useful. Rgds Mayuresh Thanks Josh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Trouble with UTF-8 data
Tom Lane wrote: >> But I'm still getting this error when loading the data into the new >> database: > >> ERROR: invalid byte sequence for encoding "UTF8": 0xeda7a1 > > The reason PG doesn't like this sequence is that it corresponds to > a Unicode "surrogate pair" code point, which is not supposed to > ever appear in UTF-8 representation --- surrogate pairs are a kluge for > UTF-16 to deal with Unicode code points of more than 16 bits. 0xEDA7A1 (UTF-8) corresponds to UNICODE code point 0xD9E1, which, when interpreted as a high surrogare and followed by a low surrogate, would correspond to the UTF-16 encoding of a code point between 0x88400 and 0x887FF (depending on the value of the low surrogate). These code points do not correspond to any valid character. So - unless there is a flaw in my reasoning - there's something fishy with these data anyway. Janine, could you give us a hex dump of that line from the copy statement? Yours, Laurenz Albe ---(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
