Re: [GENERAL] view row-level locks
On Jul 11, 2008, at 4:24 AM, Richard Huxton wrote: If you just want to see if a lock has been taken (e.g. SELECT FOR UPDATE) then that shows in pg_locks. If you want details on the actual rows involved, then you probably want "pgrowlocks" mentioned in Appendix F. Additional Supplied Modules. pg_locks tells you the page/tuple so you can select it with those values. Assuming they are page=132 and tuple=44 and relation=99 you can find the tuple thusly: select relname from pg_class where oid=99; then given that relname=mytable, select * from mytable where ctid='(132,44)'; and there you have the row. What I need to see is which locks my other queries are waiting on. If pg_locks would show me which process is also blocking on this lock, I'd be a much happier man today (actually, last tuesday, when this was a problem for me to debug something). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stripping out slony after / before / during pg_restore?
Here's how you do it on restore step from a pg_dump in -Fc format. pg_restore -l dumpfile > list edit the file "list" to remove references to slony objects pg_restore -L list dumpfile -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] close database, nomount state
On Apr 28, 2008, at 6:50 PM, [EMAIL PROTECTED] wrote: I want to ask if there is something like nomount state or close database state in which I can acces postgresql to drop database or to do some other stuff. change the permissions on the DB so nobody can log in. you really should find all applications that are trying to login and shut them down, else they'll start spewing errors once you delete the DB. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Master-master replication with PostgreSQL
On Apr 16, 2008, at 8:47 AM, Julio Cesar Sánchez González wrote:
From what I've read, Slony-I does only master-slave replication and
Slony-II is not being actively developed. Is this right? Are there
any
viable master-master replication tools for PostgreSQL. (They could be
commercial/paid for if necessary.)
Rob
Try with bucardo ("http://bucardo.org/";) may be help you :).
Doesn't bucardo handle conflicts with "whichever one I apply last
wins"? That doesn't seem safe in all situations (or any, IMO).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL Replication with read-only access to standby DB
On Mar 25, 2008, at 4:28 PM, Jeff Davis wrote: This obviously does not work in real time, but it may be useful. It does not require a lot of additional space to do this because of the ZFS copy-on-write implementation. But what benefit does it give you if you're pounding on the same set of physical disks? You might as well run it on the original since you're limit is the disk I/O. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with async notifications of table updates
On Mar 17, 2008, at 10:58 PM, Tyler, Mark wrote: I suggest rethinking your dislike of NOTIFY. I have thought very hard about using NOTIFY for this but it has two large problems (from my point of view). The first is that it forces me Wait a while and you will learn to detest Spread, too. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recomendations on raid controllers raid 1+0
On Mar 13, 2008, at 7:50 AM, Glyn Astill wrote: I'm looking at switching out the perc5i (lsi megaraid) cards from our Dell 2950s for something else as they're crap at raid 10. Use an actual LSI branded card instead of the Dell "improved" version. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pain of postgres upgrade with extensions
On Mar 12, 2008, at 3:19 PM, Tom Lane wrote: - restore dump, ignoring "object already exists" errors Couldn't one use the dump listing feature of pg_restore and comment out the extensions when restoring? Not likely to be a big improvement over "ignore" errors :-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to convert postgres timestamp to date: yyyy-mm-dd
On Mar 11, 2008, at 2:50 PM, A. Kretschmer wrote: i.e ... WHERE pt.created_date >= '2008-01-21' You can't compare a date or timestamp to a varchar or text. For your example, cast the date-string to a real date like: Since which version of Pg? Queries like the above have worked for me from 7.3 up thru 8.1, which is my current production environment. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuous errors in pg_dump ... | pg_restore pipeline
On Feb 20, 2008, at 2:12 PM, Douglas McNaught wrote: Alternatively, is there a better way to streamline the duplication of a database? How about: CREATE DATABASE newdb TEMPLATE olddb; The template DB has to have no open connections during the entire copy process, so it is not always possible when the DB is big and/or busy. But it is a wonderful way to snapshot development databases before potentially breaking them with new features. :-) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Working with huge amount of data.
On Feb 11, 2008, at 10:37 AM, Mario Lopez wrote: The problem arises with the second type of queries, where there are no possible partitions and that the search keywords are not known, I have tried making indexes on the letter it ends with, or indexes that specify that it contains the letter specified but none of them work the planifier only make sequential scans over the table. Postgres doesn't use indexes on prefix-wildcard searches like your '%word' type search. It will always sequential scan the table. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is PG a moving target?
On Feb 9, 2008, at 12:20 PM, Ken Johanson wrote: But given the recent and dramatic example of 8.3's on-by-default stricter typing in functions (now not-autocasting), I worry that kind of change could happen in every minor version (8.4 etc). You need to *know* your software if you're using it production. 8.4 is *not* a minor version upgrade; it is a major upgrade. The Postgres "guarantee" is that nothing will change in behavior on the 8.x branch for a given x. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
On Feb 4, 2008, at 11:31 AM, Joshua D. Drake wrote: I don't agree in the least, I was actually going to suggest we add a new one for relational design questions. I like many lists that are contextually specific. IMO, general should be removed for example. I think this makes sense for a web-based forum, not for mailing lists to which you need to subscribe (and in my case set up auto-filers to move the stuff out of my inbox). Joshua D. Draek Is this your alternete evil twin? ;-) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Lets get the 8.3 Announcement on the front page of Digg
On Feb 5, 2008, at 12:29 PM, Tony Caduto wrote: So this Stonebraker guy is the Postgres Architect? That doesn't imply Postgres == PostgreSQL :-) The original Postgres wasn't even SQL, was 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] REINDEX on large DB vs. DROP INDEX/CREATE INDEX
On Feb 4, 2008, at 10:00 AM, Wes wrote: Just a follow-up on this... The REINDEX took about 2 1/2 days. I didn't gain much disk space back - a full backup takes just as long as before, but the vacuum time dropped from 30 hours to 3 hours. what you need to do is compare the relpages from the pg_class table for that index before and after. if you didn't get much disk space back, make sure you have no long running transactions that may have kept some older files open. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Log file permissions?
On Jan 31, 2008, at 10:21 AM, Alvaro Herrera wrote: Glyn Astill wrote: I'm not piping it to a file, postgres is managing the logs. Is there any way to manage the permissions, or do I just need to create a script to change the permissions? I think you should be able to chmod the files after they have been created. The postmaster changes its umask to 0077, so no file is group-readable. I don't think is configurable either. just move the logs into a subdir which has permissions applied to it, then not worry about the files inside, since nobody can break through the directory anyhow. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql book - practical or something newer?
On Jan 31, 2008, at 10:14 AM, Erik Jones wrote: That's an interesting idea. Is there a general audience/ participation wiki for Postgres? I know the developers have one, but a user-oriented sister wiki would probably be a good way to get lots of different people involved. I'm of the opinion that the documentation should provide guidance like best practices in addition to just being a reference. To that end, the "interactive" online docs seem like a great place for people to make suggestions and recommendations like this, and these comments can be folded into the next release of the docs. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How can I avoid PGPool as a single point of failure?
On Jan 31, 2008, at 4:28 AM, Aaron Glenn wrote: CARP *and* pfsync. this late at night off the top of my head I can't see any blatantly obvious reason this wouldn't work (with at least pgpool that is, dunno about your data) we use CARP to balance and failover some webserver pairs. We also use it to balance our internal DNS caches to our internal clients. The only drawback is that once you pass a router, all traffic from that router will go to a single CARP host -- ie, you get failover but no balance. Other than that, it makes upgrading systems nearly invisible to the other servers. For critical things like DNS, this is a big win. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] OT - pg perl DBI question
On Jan 29, 2008, at 7:24 AM, Glyn Astill wrote: I'm trying yo run a perl script that uses DBI (Slonys psql_replication_check.pl to be precise) and I'm getting the error: Can't locate Pg.pm in @INC It doesn't use DBI, it uses Pg. At some point I posted patches to convert it to DBI and DBD::Pg, but I don't know where those are anymore as I don't use that code. ---(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] Tips for upgrading from 7.4
On Jan 23, 2008, at 10:26 AM, Tom Lane wrote: Reading the release notes is good, but you really really should test the application(s) against a test 8.1 installation before you go live ... be sure to run *every* query your system uses through 8.1. the most common problems you will run into are issues relating to using strings as integers which tended to work in most cases in 7.x but not in 8.x. the release notes cover such changes. pay particular attention to changes in auto typecasts. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Restoring 8.0 db to 8.1
On Dec 21, 2007, at 11:09 AM, Martijn van Oosterhout wrote: The usual answer is use slony. You can use it to replicate the 8.0 server onto an 8.1 server. This may take weeks/months/years/whatever to synchronise. When the slave is up to date, you pull the plug on the 8.0 server and get everyone to use the 8.1 server... No downtime... except he has large objects, which slony can't replicate. ---(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] partitioned table query question
On Dec 11, 2007, at 10:44 AM, Gregory Stark wrote: The problem Tom's tried to explain is that the function may or may not preserve the bin. So for example if you wanted to bin based on the final digit of a numeric number, so you had a constraint like I, along with at least Erik, was thinking that the constraint expression would be evaluated to determine whether to include the partition in the final plan. Based on Tom's description, it is not the case: the planner basically proves that the constraint will be false. Until this was clarified, Tom's points totally confused the heck out of me. It would be amazingly wonderful if this distinction could be posted to the online docs. It will surely help future generations :-) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] partitioned table query question
On Dec 11, 2007, at 10:08 AM, Erik Jones wrote: b.) precomputing the bin and directly accessing the child table will be the only options we have for now. This is where I'm headed I have only one or two queries that don't specify the partitioned ID, and those need a full table scan anyhow. :-( ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SQL design pattern for a delta trigger?
On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote: For what it's worth, the real algorithm would be as follows. I hadn't had enough coffee yet, and I forgot the UPDATE bit. IF (a query matching your old data returns rows) THEN UPDATE with your new data ELSE INSERT your new data Still exists race condition. Your race comes from testing existence, then creating/modifying data afterwards. You need to make the test/ set atomic else you have race. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Script to reset all sequence values in the a given DB?
please don't hijack old threads ("partitioned table query question" in
this case) and change the subject line to start your new question. it
messes up threaded mail readers.
thanks.
On Dec 10, 2007, at 3:00 PM, Nathan Wilhelmi wrote:
Hello - Does anyone happen to have a SQL script or function that can
reset all the sequence values found in a given DB? When we rebuild
the DB it would be handy to be able to set all the sequence back to
a known starting place.
Thanks!
-Nate
---(end of
broadcast)---
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] partitioned table query question
On Dec 10, 2007, at 1:21 PM, Erik Jones wrote: You beat me to the punch on this one. I was wanting to use modulo operations for bin style partitioning as well, but this makes things pretty awkward as well as unintuitive. So, to the postgres gurus: What are the limitations of check constraints when used with constraint exclusion? Is this really the intended behavior? /me too! I have vague recollection of reading that the constraints on the child tables needed to be free of computation (ie, just straight comparison ranges) but I can't find that reference now. But in my case, I can almost always pick the appropriate sub-table from the application level anyway. ---(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] SQL design pattern for a delta trigger?
On Dec 7, 2007, at 11:42 AM, Colin Wetherbee wrote: You can do this with a conditional. Something like the following should work. IF NOT (a query matching your data returns rows) THEN INSERT (your new data) There exists a race condition here unless you've locked your tables. ---(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] Slony replication
On Dec 8, 2007, at 9:21 AM, Geoffrey wrote: I am quite new to Slony as well, but one of the first requirements the docs state is: Thus, examples of cases where Slony-I probably won't work out well would include: * Sites where connectivity is really "flakey" * Replication to nodes that are unpredictably connected. So I suspect Slony is not a solution for your effort. See: If your DB doesn't change very much (like a few hundred or thousand update/insert/delete per day), then slony can work just fine in such a batch mode. Things break down when you accumulate several hundred thousand or more changes between times when you're connected. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Error compiling Slony I
On Nov 28, 2007, at 11:17 AM, Glyn Astill wrote: I've already tried removing and re-installing bison, but I shall try again as you suggest. I recommended uninstalling bison, not re-installing it. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Select all fields except one
On Nov 28, 2007, at 11:06 AM, Matt Doughty wrote: Is there a way of selecting all fields except for one in particular? I'd like to create a query that says something like: select * except fieldx For best practices, you should never use select * in your queries. You will inevitably end up with code that cannot deal with a schema change, and for any live system, you will have a schema change at some point... It is best to explicitly list the field names your code is expecting. Besides, I don't think you can do what you want to do with just SQL.
Re: [GENERAL] Error compiling Slony I
On Nov 28, 2007, at 8:50 AM, Glyn Astill wrote: Hi people, When I try to compile Slony 1.2 I get the following error:P parser.y:1090:18: error: scan.c: No such file or directory make[2]: *** [parser.o] Error 1 make[2]: Leaving directory `/tmp/slony1-1.2.12/src/slony_logshipper' make[1]: *** [all] Error 2 make[1]: Leaving directory `/tmp/slony1-1.2.12/src' I've installed bison. Anyone got any idea what I may be doing wrong? Slony mailing list will be more helpful... but I ran into this. the solution for me was to uninstall bison and re-build. for some reason if the slony configure script finds bison, it forces it to rebuild the parser. i think the parser requires a certain version of bison. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL DB split
On Nov 28, 2007, at 8:18 AM, Richard Huxton wrote: I can read that I can create a PostgreSQL DB on the RAMDisk partion, but I'm wondering if is it possible to create one DB with two schemas in two different memory location (RAM and flash)? See the manuals for "tablespaces". but postgres will not like it when you restart after a failure and the ramdisk tablespace is missing the data postgres expects to be there. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Config settings for large restore
On Nov 27, 2007, at 3:30 PM, Erik Jones wrote: I'm just wondering what is considered the general wisdom on config setting for large pg_restore runs. I know to increase maintenance_work_mem and turn off autovacuum and stats collection. Shoule should checkpoint_segments and checkpoint_timeout be increased? Would twiddling shared_buffers help? What about At least with 8.0 testing I did a while back, I found that bumping checkpoint segments was the biggest benefit. I use 256 segments as a matter of course now, even for normal operations. =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.MailerMailer, LLC Rockville, MD http://www.MailerMailer.com/ +1-301-869-4449 x806 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] replication in Postgres
On Nov 26, 2007, at 10:14 AM, Jeff Larsen wrote: Yes, but I'd like something better than "near real time" as the above page describes. Or maybe someone could clarify that Besides, EnterpriseDB does not save me enough money. In my current commercial DB, if a transaction is committed on the master, it is guaranteed to be committed to the secondary. In our business, losing one customer order could lose us the customer for good. So you want synchronous replication. Search on that term in the archives for possible solutions (or lack thereof) in postgres. If you don't specify your requirements clearly, don't expect useful advice ;-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Migrating from 32 to 64 bit
On Nov 24, 2007, at 6:18 PM, Laurent CARON wrote: Question: I'd like to know if it is possible (and wise) to just keep the /var/lib/postgres.. directories from the old 32Bit server to use on the 64Bit version. This is just as a personal interest since I can also just dump and restore the database in about 2.5 hrs. No, you must dump/reload. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql storage and performance questions
On Nov 20, 2007, at 1:04 PM, Josh Harrison wrote: I ran vacuum full on this table already. I haven't re-indexed it. But this will not affect the table size...right...since indexes are stored separately? Yes, but your indexes are probably bloated at this point, so to reduce the space they use run a reindex. ---(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] Sharing database handles across forked child processes
On Nov 13, 2007, at 1:18 PM, [EMAIL PROTECTED] wrote:
Yep, this is a fork without exec. And the child processes often aren't
even doing any database access -- the database connection's opened and
held, then a child is forked off, and the child 'helpfully' closes the
handle during the child's global destruction phase.
What's your programming language? If it is perl using the DBI, you
*must* close the handle on the child else perl's object destroy will
try to close the handle by doing a shutdown on the connection, which
will muck up your parent. The voodoo to make this happen is this:
$dbh->{InactiveDestroy} = 1;
$dbh = undef;
Also note that for some reason, this invalidates any prepared
statements in the parent DBI object, so you need to make sure you
don't have any, or just re-open the handle on the parent too.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] FreeBSD portupgrade of 8.1 -> 8.2
On Nov 12, 2007, at 8:55 PM, Steve Manes wrote: Steve Manes wrote: What's the portupgrade process in FreeBSD?? (Fixed. The answer is to use pg_delete -f on the old package to force the delete) more elegantly, portupgrade -Rrv -f -o databases/postgresql82-client postgresql-client but you have to account for having to do the dump/restore in there manually. This will also force-upgrade all software depending on the postgresql-client port (of which the server is one). ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Nov 12, 2007, at 12:01 PM, Greg Smith wrote: Not the Mac OS BSD. Last time I looked into this OS X was still dramatically slower than Linux on things like process creation. On MacOS X, that's the Mach kernel doing process creation, not anything BSD-ish at all. The BSD flavor of MacOS X is mostly just the userland experience. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Nov 12, 2007, at 12:29 PM, Sam Mason wrote: You only need a 64bit address space when each process wants to see more than ~3GB of RAM. And how exactly do you get that on a 32-bit CPU? Even with PAE (shudders from memories of expanded/extended RAM in the DOS days), you still have a 32-bit address space per-process. ---(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] Calculation for Max_FSM_pages : Any rules of thumb?
On Nov 1, 2007, at 8:51 PM, Ow Mun Heng wrote: Another question is, based on what I've read in the archives (in my laptop.. No-Inet conn @ work) Since I've overran my max_FSM, I'm basically screwed and will have to do a vacuum verbose FULL on the entire DB. Crap.. I've seen this repeated many times as well, and I can't think of a really good reason why this should be true. Once you increase max fsm pages, won't the very next regular vacuum find all the free space in pages and add them to the map anyway? Ie, you've not "lost" any free space once the next regular vacuum runs. At worst, you've got a slightly bloated table because you allocated more pages rather than re- using some, but is that worth a full vacuum? I don't think it will be unless you're *way* under the fsm pages needed and have been for a long time. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] time penalties on triggers?
On Oct 5, 2007, at 9:10 AM, Kenneth Downs wrote: I also found it very hard to pin down the penalty of the trigger, but came up with rough figures of 30-50% overhead. The complexity of the trigger did not matter. in which language did you write your triggers? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] question about pg_dump -a
On Sep 28, 2007, at 9:07 AM, Ottavio Campana wrote: But why does pg_dump does not already exports data such that previous tables do not depend on successive ones? Because you can't always sort your tables that way. The restore procedure is responsible for either sorting or disabling the FK checks during bulk load. The latter is more efficient, especially if there are no indexes yet, as in a full restore from dump. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Debian problem...
On Sep 28, 2007, at 5:09 AM, Tom Allison wrote: I know reiserfs does better performance wise, but there's no point in going fast if you can't steer. I recently had to replace 16 Western Digital 10kRPM SATA drives with Hitachi 7.2kRPM drives because the WD drives kept randomly (and falsely) reporting failure to the RAID system. The performance loss was noticeable, but the reliability was more important. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
On Sep 24, 2007, at 12:00 PM, Phoenix Kiula wrote: I feel your pain. But I seem to have (mostly) solved my problem in three ways: My particular usage pattern (add data continuously, purge *some* of the data once per week or every other week. The purge is what seems to kill it. Last time I reindexed, I reclaimed over 20Gb of disk space. That was after 6 months from the prior reindex. Recommending I run vacuum intermixed with the data purge is a non- starter; the vacuum on these tables takes a couple of hours. I'd never finish purging my data with that kind of delay. Recommending splitting my tables with inheritance is not going to work since my purges don't happen across any lines which would make sense for splitting with inheritance. I will investigate the fill-factor. That seems like it may make some sense the way I do inserts and updates... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
On Sep 18, 2007, at 1:14 AM, Joshua D. Drake wrote: Q: To get rid of index bloat, is a VACUUM ANALYZE enough? Or must I reindex/cluster indexes? If you overrun your max_fsm_pages, no: else yes; my algorithm is: if (true) then yes; my FSM is way bigger than I ever use (vacuum never reports shortage) and I still get bloat that needs to be purged out with a reindex on occasion. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] importing pgsql schema into visio (for diagramming)
On Sep 12, 2007, at 7:32 PM, Andrew Hammond wrote: Does anyone know where I could find a tool which allows importing schema information from a postgres database into visio? The boss guys want some pretty pictures... See SQLFairy. it can generate pretty pictures directly from the schemas. I also have some script somewhere that generates graphviz output which makes optimal graphs. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] accessing PG using Perl:DBI
On Aug 30, 2007, at 4:03 AM, Ow Mun Heng wrote: 2. how do I perform a list of SQL using transactions. eg: like above, but wrap it into a transaction. assuming $dbh is your open handle to the database via DBI, then you do something like this: $dbh->begin_work() or die; $sth = $dbh->prepare(...) or die; $sth->execute() or die; ... more queries as needed... $dbh->commit() or die; Did you read the DBI manuals at all? It has examples. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Bigtime scaling of Postgresql (cluster and stuff I suppose)
On Aug 27, 2007, at 11:04 AM, Andrew Sullivan wrote: It was a way to scale many small systems for certain kinds of workloads. My impression is that in most cases, it's a SQL-ish solution to a problem where someone decided to use the SQL nail because that's the hammer they had. I can think of ways you could The underlying table type can be used directly, similarly to how the "DB" table type is built on the DB library. The issue is whether you can do that without some strange licensing problems. I wouldn't use it for "permanent" data. It would be awesome for a cache of the "hot" data in your app, and perhaps even for realized views that speed up your interactive app. ---(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] Bigtime scaling of Postgresql (cluster and stuff I suppose)
On Aug 25, 2007, at 8:12 AM, Phoenix Kiula wrote: The sentence that caught my attention is "Nokia, Alcatel and Nortel are all building real-time network nodes on top of MySQL Cluster." My experiences with MySQL so far have been less than exhilerating (only tried it for our web stuff, which is not much so far but increasingly rapidly) but I have seen a lot of talk about MySQL clusters. Is there something similar in the PG world? PG Clusters? MySQL Cluster is a specific product. It is not just mysql databases lumped together to make a big mysql DB. It is a memory-based database that requires at least 2 machines to run, and is not usefully redundant and distributed until you have many machines. The telco's use it for their logging and switching infrastructure where you need 100% uptime and wicked fast response (thus memory based.) And you'll note it was developed by Ericsson... There is no equivalent in Postgres. There are several replication choices for Postgres. Google will find them for you, or just look on the postgres.org pages for the list. We use slony1 and it works very well for our need. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [PERFORM] Partioning tsearch2 a table into chunks and accessing via views
On Aug 25, 2007, at 1:34 AM, Benjamin Arai wrote: There has to be another way to do incremental indexing without loosing that much performance. This is the killer feature that prevents us from using the tsearch2 full text indexer on postgres. we're investigating making a foreign table from a SOLR full text index so our app only talks to Pg but the text search is held in a good index. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
On Aug 24, 2007, at 4:09 AM, Alban Hertroys wrote: I'm not entirely sure what makes multi-threading be advantageous on a specific operating system, but I think FreeBSD should be added to that list as well... They've been bench marking their threading support using multi-threading in MySQL (not for the db, mind you - just for load ;), and it performs really well. Maybe only for FreeBSD >= 6.0. Prior to that, the threading was rather lackluster. I still think the separate process model is superior, in that you get private data spaces with them. ---(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] Best practice for: ERROR: invalid byte sequence for encoding "UTF8"
On Aug 15, 2007, at 7:41 AM, Ivan Zolotukhin wrote: What is the best practice to process such a broken strings before passing them to PostgreSQL? Iconv from utf-8 to utf-8 dropping bad characters? This rings of GIGO... if your user enters garbage, how do you know what they wanted? You don't. You tell them they entered garbage, else the result will be garbage. Data validation... learn from microsoft's mistakes :-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Time for Vacuum vs. Vacuum Full
On Aug 9, 2007, at 9:38 AM, Brad Nicholson wrote: I have the times that it takes to to do a regular vacuum on the clusters, will vacuum full take longer? almost certainly it will, since it has to move data to compact pages rather than just tagging the rows as reusable. you can speed things up by dropping your indexes first, then running vacuum full, then re-creating your indexes. this will make for better (more compact) indexes too. as for how much longer, I don't know how to estimate that. ---(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] How do I connect postgres table structures and view structures to an existing svn repository?
On Aug 1, 2007, at 10:56 AM, Richard Huxton wrote: You could write a small cron-script that dumped the schema once every 5 minutes so it could be picked up by svn. I think most people have a separate collection of schema-creation/ update scripts that they keep under version control. All changes are then through running these. You would have to do it via polling, since schema changes cause no events to be generated (ie, you can't attach a trigger to a schema change.) But the *right* way is to make schema change scripts as "delta" files, add them to your repo, test them on your staging environment, then apply them to your production environment. That way you can reconstruct your DB at any time and *know* it will work. ---(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] upgrade to 8.2.? or implement Slony, which first?
On Jul 27, 2007, at 8:29 PM, Jim Nasby wrote: Double-check with the Slony guys, but ISTR that there's an issue going all the way from 7.4 to 8.2 in a single shot. I don't think that's a slony-specific issue. Moving from 7.4 to 8.0 introduces a fair number of incompatibilities one must address (mostly quoting and implied type casts as I recall from when we did the transition). ---(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] pg_dump without blobs
On Jul 16, 2007, at 9:26 AM, Francisco Reyes wrote: I guess the next question is 'what does postgresql considers a blob'? bytea fields? How about a large text with megabytes worth of data? bytea and text fields are NOT blobs. they are what you access via the 'large object' functions. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] growing disk usage problem: alternative solution?
On Jun 26, 2007, at 3:31 PM, Bill Moran wrote: VACUUM FULL and REINDEX are not required to maintain disk usage. Good old- fashoned VACUUM will do this as long as your FSM settings are high enough. I find this true for the data but not necessarily for indexes. The other week I reindexed a couple of O(100,000,000) row tables and shaved about 20Gb of index bloat. Those tables are vacuumed regularly, but we do a large data purge every few weeks. I think that causes some issues. I'm running 8.1. To mitigate the downtime, we make use of the fact that we have live replica of the database on similarly capable hardware so we bring the replica offline, reindex it, bring it back up, move the "master" to it, then reindex the other server, and move the master back. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_restore out of memory
On Jun 25, 2007, at 10:32 PM, Francisco Reyes wrote: Hm... now I am really confused. The same settings on AMD64 work. So how are "more resources available" when I have the same amount of memory and the same settings? you set your maxdsize to the same as on i386? on even my smallest amd64 boxes I see a data size limit of 33Mb per process. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_restore out of memory
On Jun 25, 2007, at 9:33 PM, Francisco Reyes wrote: Therefore, the problem is only with the i386 version. Should I report this as a bug or is this "nornal" and expected? i wouldn't call it a bug to need more resources than you've got available :-) obviously the limits on the i386 version of FreeBSD are less than that on the amd64 version, but I've not got any "big" i386 machines (ie, more than 1Gb of RAM) to compare. All my big boxes are amd64. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_restore out of memory
On Jun 18, 2007, at 2:10 PM, Francisco Reyes wrote: Also the error is about running out of memory when trying to allocate 84MB. The default FreeBSD limit is 512MB so 84MB is well below that. Try being less stingy than 128Mb for your stack. The default stack is 512Mb. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_restore out of memory
On Jun 15, 2007, at 8:24 AM, Francisco Reyes wrote: Understood. But at least it shows that the program was already above the default of 512MB limit of the operating system. But that is a false assertion that the limit is 512Mb. On a random system of mine running FreeBSD/i386 it shows the default data limit as 1Gb, and on a random FreeBSD/amd64 box I see it at about 32Gb. I do no global tweaking of the size limits. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Using the power of the GPU
On Jun 8, 2007, at 3:33 PM, Guy Rouillier wrote: Well, I'm not one of the developers, and one of them may have this particular scratch, but in my opinion just about any available fish has to be bigger than this one. Until someone comes out with a standardized approach for utilizing whatever extra processing power exists on a GPU in a generic fashion, I can't see much payback for writing special code for the NVIDIA 8800. And I can state unequivocally that none of my high-end DB serves will every have such a high-end graphics card in it... so what's the point? ---(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] why postgresql over other RDBMS
On May 25, 2007, at 5:28 PM, Tom Lane wrote: That's true at the level of DDL operations, but AFAIK we could parallelize table-loading and index-creation steps pretty effectively --- and that's where all the time goes. I would be happy with parallel builds of the indexes of a given table. That way you have just one scan of the whole table to build all its indexes. ---(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] Performance
On May 23, 2007, at 1:12 PM, Donald Laurine wrote: Now my question. The performance of each of these databases is decreasing. I measure the average insert time to the database. This metric has decreased by about 300 percent over the last year. I run vacuum analyze and vacuum analyze full on a daily bases. I also run vacuum 4 time a day. Still the performance continues to drop. Are there some other performance activities I may try?. Are there other monitoring options I can use to indicate where the slow down is occurring? I am of the fragmented index camp. re-index your table that gets the large number of inserts and deletes. compare the index sizes before and after... something like select relpages,relname from pg_class where relname like 'mybigtable%'; should tell you the sizes. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance issues of one vs. two split tables.
On May 15, 2007, at 10:35 AM, Bill Moseley wrote: For some value of "large", is there a time when one might consider using a single column in the user or user_prefs table to represent their color choices instead of a link table? We use bitfields on our large user table. It is becoming unworkable to scan for matches, since overall most people have very few selections made. We are moving it to a model like your favorite_colors table which just links the option and the user. We find that doing joins on large tables which can be indexed to avoid full table scans are very fast in postgres, since the index can do much of your culling of potential matching rows. With bitfields, you are more or less forced into doing a sequence scan to find everyone who likes the color red. Of course, if you're playing with only a few thousand users, either approach works well. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Performance issues of one vs. two split tables.
On May 14, 2007, at 4:37 PM, Bill Moseley wrote: Say that there's also about 10 columns of settings or preferences for each user. Are there any cases or reasons to have a separate "user_preferences" table vs. just placing all the columns together in one table? when you have, say 65 million users, it makes sense to push the ancillary info to another table to keep from having to copy too much data when you update the main info (like last access time). ---(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] Postgres Printed Manuals
On May 13, 2007, at 10:43 PM, Tom Lane wrote: It's hard to make any money that way :-(. Rich Morin used to run a business called "Prime Time Freeware" that published hardcopy versions of our manuals along with much other open-source documentation. He gave up on it some years ago, though, and I doubt that the market has improved. Seems like an ideal use of those print-on-demand services like lulu.com among others. The project could post an official PDF of the docs, and take a cut every time someone ordered a printed copy. The question is who would take the time to make a professional looking PDF. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is there a shortage of postgresql skilled ops people
On Apr 9, 2007, at 10:09 AM, [EMAIL PROTECTED] wrote: It would be a really great service to this community if you would capture those issues and publish documentation (but feel free to change or omit the names to protect the incompetent^w innocent!). There's no incompetence involved... the guy is clearly not a Postgres person, but overall an excellent DB designer. He works on extremely large databases at a large public university. The main one that comes to mind is that he suggested adding multi- part primary indexes to keep the data ordered. Apparently Oracle and/ or DB2 keep the data sorted by primary key index. Since the only reason was to keep the data sorted, the index would be useless under Pg. Also, he recommended the use of 'index-only' tables -- eg, when the table is just two or three integers, and the PK is a multi-part key of all fields, it makes sense not to store the data twice. However, in Pg you can't do that since visibility is only stored in the data, not the index. One thing that was really counter-intuitive to me from a guy who runs really large databases, was to get rid of some of the FK's and manage them in the application layer. This one scares me since I've had my behind saved at least a couple of times by having the extra layer in the DB to protect me... the data integrity would be managed by some external program that sweeps the DB every so often and purges out data that should no longer be there (ie stuff that would have been CASCADE DELETEd). smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Is there a shortage of postgresql skilled ops people
On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote: Another thing is this, how hard could it possibly be for a MS SQL DBA or Oracle DBA to pick up using PostgreSQL? I don't think it would take a decent admin of any database to come up to speed in a very short time as long as they were interested in doing so. We've been working with a consultant to re-design/optimize some existing DB systems we have running, and his background is mostly Oracle and DB/2. Some of the optimizations -- actually operationally related choices on how to do things -- are remarkably off-base for Postgres. There is a *lot* to learn about a system before one can truly "know" it. ---(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] best way to kill long running query?
On Mar 21, 2007, at 3:09 PM, Bill Eaton wrote:
I want to allow some queries for my users to run for a prescribed
period
of
time and kill them if they go over time. Is there a good way to
do this?
set statement_timeout perhaps?
Ooh. I like that. It would be absolutely brilliant if I could
figure out how
to get it to work with ADO and the Windoze ODBC driver. I've tried
appending
statement_timeout to my connection string i.e.
ConnString = "DRIVER={PostgreSQL
Unicode};SERVER=MYSERVER;DATABASE=MYDB;UID=client;set_timeout=1"
but it has no effect on a SELECT statement that takes at least 3 or 4
seconds to execute and only returns 184 (out of 600,000) records.
I've also tried different syntaxes to pass the parameter
set_timeout=1
set_timeout='1'
set_timeout=(1)
set_timeout=('1')
that doesn't look like "statement_timeout" to me, but then my glasses
might be out of date.
try this as postgres superuser for your user:
alter user foobar set statement_timeout=1;
where foobar is the user you connect as.
then this user's default statement_timeout is set he can override
it at will, though.
smime.p7s
Description: S/MIME cryptographic signature
Re: [GENERAL] cache - timing
On Mar 19, 2007, at 1:58 AM, ab wrote: I am trying to measure the time taken for a number of queries using \timing . All queries on my table other than the first one are pretty fast. This is likely due to caching. Is there any way to clear the internal cache of Postgres. Should I be worried about the entire OS cache also? restarting postgres ought to do it. however, also note that your OS's disk cache plays a big role, and generally the only way to flush that is to reboot and/or do a lot of other random IO. smime.p7s Description: S/MIME cryptographic signature
[GENERAL] anyone heard of/use SurfRAID Triton product with postgres?
Does anyone here run Pg on a SurfRAID Triton RAID array? If so, please let me know how satisfied you are with the performance, and what kind of performance you get (operations/second, data transfer/ second, etc.) I'm looking at their fibre attached 16 sata disk solution. Thanks! =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Lifecycle of PostgreSQL releases
On Mar 15, 2007, at 10:22 AM, Alvaro Herrera wrote: He could wait for 8.4 as well, as it will be probably faster and have more features than 8.3. Following your reasoning, one could wait essentially forever. H... precisely the reason my cell phone hasn't been replaced in a long time :-) I'm evaluating whether to upgrade from 8.1 to 8.2 still... but the jump from a 7.4 to 8.2 is to me a no-brainer once you've ironed out the minor issues with syntax pickyness that 8.x imposes on some sloppy queries that worked with 7.4 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] DST failing on 8.1.3
On Mar 14, 2007, at 2:00 PM, Tom Lane wrote: Should work to just copy over the timezone directory tree from a correct installation on the same machine architecture (I can't recall right now if the file format is machine-dependent or not). You might have to restart the postmaster too, before it starts behaving entirely sanely. The compiled zone info files are compatible across time and space. I just copied the correct US Eastern time zone file from an amd64 FreeBSD 6.1 system onto a NSLU2 running a hacked embedded linux using an arm chip (I use it as an NFS file server) and it did the right thing. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] daylight savings patches needed?
On Mar 12, 2007, at 6:08 PM, Martijn van Oosterhout wrote: On Mon, Mar 12, 2007 at 01:13:42PM -0600, Ed L. wrote: Would I be correct in understanding that every pre-8.0 cluster must be restarted in order for the OS changes to take affect?!? Possibly, I imagine many C libraries would cache the timezone data over a fork and might not motice the changes... You also need to restart cron, possibly syslog, etc. Much easier to reboot than chase down every long running app just to update the date library's cached zone info. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] grant permissions to set variable?
On Mar 14, 2007, at 11:36 AM, Richard Huxton wrote: Vivek Khera wrote: I want to do some debugging on an app, and I'd like to set on a per-connection basis "set log_min_duration_statement = 10;" Obviously since I'm not super user I get permission denied. Is there some GRANT I can grant to the user in question to allow this? I don't really want to do it globally or for all connections by that user, both of which are easy to do Could you handle it with a security=definer function? Good call. However, the following complains about the $ in $1. My guess is that the SET command doesn't like anything but an integer to be there. If I make it a string, the function gets defined, but at runtime it complains that it is not an integer. If I try to cast the string to '$1'::integer the function definition again fails with syntax error. CREATE OR REPLACE FUNCTION setlogtime(integer) RETURNS void AS $$ SET log_min_duration_statement = $1; SHOW log_min_duration_statement; $$ LANGUAGE SQL SECURITY DEFINER; I tried variants '$1' and '$1'::integer as noted above. How can I write this function? smime.p7s Description: S/MIME cryptographic signature
[GENERAL] grant permissions to set variable?
I want to do some debugging on an app, and I'd like to set on a per- connection basis "set log_min_duration_statement = 10;" Obviously since I'm not super user I get permission denied. Is there some GRANT I can grant to the user in question to allow this? I don't really want to do it globally or for all connections by that user, both of which are easy to do smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] FreeBSD kernel configuration
On Mar 6, 2007, at 3:42 AM, veejar wrote: Hi! I have server such configuration: 2 x Xeon LV DualCore 1.66GHz MEM 4Gb DDR2-400 2 x 250Gb SATA HDD how are you using the drives? software mirror? I have 20 databases on PostgreSQL 8. 2 of them are more than 1GB. I have ~50 requests per second. Nice small DB's... :-) What kernel setting can you recommend me? in sysctl.conf: # Following for Postgres to use more memory and semaphores # see also kern.ipcs.sem* in /boot/loader.conf kern.ipc.shm_use_phys=1 kern.ipc.shmmax=1073741824 kern.ipc.shmall=262144 kern.ipc.semmsl=512 kern.ipc.semmap=256 in loader.conf: # IPC settings for big postgres # see also /etc/sysctl.conf for other kern.ipc settings kern.ipc.semmni=32 kern.ipc.semmns=512 But your most bang for the buck will be optimizing your postgresql.conf file. This is a black art :-( and takes much experimentation. I'm considering increasing SHMMAX and SHMALL given recent postgres developments, and having more than 4Gb of RAM on some of my servers... But it suits me so far. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] How often do I need to reindex tables?
On Feb 28, 2007, at 5:35 PM, Bill Moran wrote: Just an FYI ... I remembered what prompted the cron job. We were seeing significant performance degradation. I never did actual measurements, but it was on the order of "Bill, why is restoring taking such a long time?" from other systems people. At the time, I poked around and tried some stuff here and there and found that reindex restored performance. I didn't look at actual size at that time. I have two huge tables (one tracks messages sent, one tracks URL click-throughs from said messages) from which I purge old data every few weeks. The primary key indexes on these get bloated after a few months and performance goes way down like you observe. A reindex fixes up the performance issues pretty well on those tables, and often shaves off a few gigs of disk space too. We have to manually run the reindex because it has to be timed such that the service is not impacted (ie, run on major holiday weekends) and we have to take down part of the service and point other parts to backup servers, etc. Not an easy chore... This is on Pg 8.1. Don't even ask me how it was in the 7.4 days when we have maybe 10% of the data! :-) smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] open source - content management system - that uses PostGreSQL
On Feb 16, 2007, at 4:13 PM, Andrew Kirkness wrote: I am currently setting up a website and have PostGreSQL database I'm using for the backend. I'm researching an open source Content Management System that uses PostGreSQL. Do you have any recommendations? You need to define what you want your CMS to do, before you get good recommendations. We've investigated a bunch of systems for publishing a magazine-type site, http://www.morebusiness.com/ and have discovered that pretty much all of them like to work with mysql :-( I think this is because many of the designers of the free, lower-end, software don't truly appreciate the relational SQL model and treat the DB as a dumb store. Once you move higher-up in the chain, you start to see better data models, and they lean toward using Pg instead... I can't figure out what you want to do with customer data and the CMS. Without knowing that, nobody can really say anything meaningful to you. Are your customers providing the content? Despite this lack of clarity, I can recommend that you first define the features you want and then evaluate the systems based on those features being available. Then all else being equal, use the preferred DB as your tie breaker. I wouldn't rule out some good software just because it uses mysql on the back-end. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Have anyone this man e-mail ?
On Feb 16, 2007, at 12:46 PM, Ezequias Rodrigues da Rocha wrote: Hi list, I am looking for this guy for some help with Slony-I. Then why don't you send Robert a direct email? He's not that hard to find with google. Or perhaps ask your question here; there are lots of smart folks here, some of which may even do windows. Obviously last month when you posted this exact same query you didn't get a response... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Converting 7.x to 8.x
On Jan 27, 2007, at 10:45 AM, Ron Johnson wrote: Using slony or "piped pg_dump" requires that you have *double* the amount of disk space. Having a *very large* database and double capacity of SCSI disks (including storage controllers, shelves, etc, etc) is expensive, and might not be available. Then one must decide which costs more: 20+ hours of downtime or some disks and a computer... smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Index bloat of 4x
On Jan 17, 2007, at 11:56 AM, Tom Lane wrote: So the above doesn't sound too unlikely. Perhaps we should recommend vac full + reindex as standard cleanup procedure. Longer term, maybe teach vac full to do an automatic reindex if it's moved more than X % of a vac full + reindex is a waste of effort. whenever i need a big cleanup, I drop indexes (other than PK), vac full, re-create indexes. however, usually a reindex does a sufficient job if vacuum has been run with any sort of regularity. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] queueing via database table?
On Jan 3, 2007, at 2:00 AM, Steve Atkins wrote: Holding a lock while generating the thumbnail doesn't sound like a great idea, and I think that the select for update will end up serialising the requests. I'd add a "rendering" field, text, defaulting to an empty string. Then do a "select for update where ... and rendering = '' limit 1", update the rendering field to the hostname of the box doing the work and commit. Render the thumbnail. Delete the record. That'll also give you an easy way to show status of which box is rendering which scene. Depending on what else you're putting into the where clause a partial index on something for records where rendering='' might be helpful. this is more or less how we do it, so i second this. we also use NOTIFY/LISTEN to "wake up" the job processors when new work is added. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] FreeBSD 6.0 PostgreSQL upgrade
On Dec 19, 2006, at 3:17 AM, Jeff Amiel wrote: We have a production FreeBSD 6.0 system with Postgresql 8.1 where we have avoided upgrading/updating the ports to avoid compatability and other unknown issues. We have our supfile default date set on our production, test and development environments to ensure that they all have the same versions. Makes sense for the OS (we use the -RELEASE versions ourselves). For ports, what you probably want to do is have a staging environment on which you can install and upgrade ports at will, and verify that they work with your application. Once verified, build packages from those ports and then use the packages to install on your production server(s). smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.2 Now Available
On Dec 5, 2006, at 5:07 PM, Josh Berkus wrote: Ragnar, Now that this has been announced, should not http://www.postgresql.org/docs/current/ and co be redirected to http://www.postgresql.org/docs/8.1/ instead of http://www.postgresql.org/docs/8.2/ in particular, the press release's link to the Release Notes brought me to http://www.postgresql.org/docs/current/static/release.html which showed the 8.1 Release Notes. Fixing, thanks. http://www.postgresql.org/docs/whatsnew tells about 8.1 still, as well. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] HELP: Urgent, Vacuum problem
On Dec 5, 2006, at 4:09 PM, Scott Marlowe wrote: I recently tossed 8.1 on my workstation which runs a little reporting application here. I pointed the app from 7.4 to 8.1 and got a visit within about an hour from a user, asking if I'd done anything to my database. Worrying that I'd made some mistake and he was getting an I think you got lucky. We had some issues with the transition from 7.4 to 8.0 due to more strictness of some queries, and some changes in how strings were interpreted as numbers. They were minor issues, but you really need to regression test your app against new major releases of Pg. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Stripping kernel FreeBSD - postgres
On Nov 30, 2006, at 8:50 AM, Enrico wrote: I already read your link and it is not specific for Postgres, I'm searching for a more specific document. Just remove any devices you don't have on your machine, and remove any "extras" like linux compat, older version compat, etc. You probably also want to disable kernel module loading (and not build any modules). ... but the kernel is demand paged so it won't really load up the code that's not used. Personally I have a pseudo-custom configuration that covers all of my various machines and removes devices and modules I don't need. I load up USB on computers that have only USB keyboards as a module for example. You should spend more time tuning postgres itself. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Postgresql data integrity during RAID10 drive rebuild
On Nov 29, 2006, at 2:39 PM, Scott Marlowe wrote: Sounds good. According to LSI, the drive will take 8 hrs to rebuild a 146GB disc (at a 30% rebuild rate), so doing this in the middle of the day is not ideal. The rebuild time also tends to depend on how full the array is. If you're only using 5% or so, it won't take the full 8 hours they're projecting. But how does the RAID card know what is and what is not "full" in the unix file system stored on it? It has to rebuild the entire drive. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Postgresql data integrity during RAID10 drive rebuild
On Nov 29, 2006, at 11:56 AM, Steve Poe wrote: I've never had to replace a disc in an array with Postgresql running on it. LSI says I can replace the disc and do a rebuild while everything is running. I am of course concerned about data integrity/corruption. This is the whole entire complete purpose you have a RAID card and hot-swap drives: To make it transparent to the layers above the disk interface. Has anyone had to rebuild one of their disc in an array of their database? Yes. The OS (let alone an application such as the DB) has no clue other than possibly slower response from the mirrored pair being rebuilt. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] How to increace nightly backup speed
On Nov 28, 2006, at 11:11 AM, Andrus wrote: 1. My database size seems to be appox 1 GB and download speed is approx 600 kb/s. Your solution requires 4.5 hours download time since 1 GB of data must be downloaded. If you're running pg_dump on a remote host, you're transferring the data over the pipe and compressing locally, since the pg wire protocol is not compressed. The compression time is probably not causing any slowness unless your local CPU is incredibly slow and can't keep up with the data streaming in at that low speed. I don't see how you can improve your download speed without doing compression at the other end to reduce the number of bits you have to push through your network. SSH seems to be a resonable solution to this (run dump + compress on remote host, then copy data over), but if you rule out anything that doesn't go over port 5432 then I think you're out of luck... Well, one thing... is there another host on the remote LAN to which you can ssh? If so, then use SSH port-forwarding and enable compression on the ssh connection to that host, then connect to postgres via the forwarded port to do your dump locally. The data will be compressed on the wire. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] vacuum: out of memory error
On Nov 28, 2006, at 8:40 AM, Jakub Ouhrabka wrote: There are 4G of RAM and 4G swap. and what is the per-process resource limit imposed by your OS? Just because your box has that much RAM doesn't mean your process is allowed to use it. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Monitoring Postgres - Get the SQL queries which are sent to postgres
On Oct 25, 2006, at 10:11 AM, A. Kretschmer wrote: Set this in your postgresql.conf: log_statement = all Reload the server, and then you can find all your statements in the log. or, preferably, on a per-connection basis, execute this SQL statement: set log_min_duration_statement = 0 then only those queries for that connection will be logged. otherwise you get *way* too much stuff to sort out. Another useful setting which I always enable (in my postgresql.conf file) is log_min_error_statement = error so that any statement that generates an error will be appended to the error log entry. otherwise you just see the error notice and have no clue what caused it. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Fast backup/restore
On Oct 17, 2006, at 2:35 PM, Steve Poe wrote: Vivek, What methods of backup do you recommend for medium to large databases? In our example, we have a 20GB database and it takes 2 hrs to load from a pg_dump file. my largest db is about 60Gb with indexes. reloading the data (about 30Gb) takes 1 hour from compressed format pg_dump, and another two to reindex. for increasing reload size, bump your checkpoint_segments to something big, like 128 or 256 depending on how much disk space you can spare for it. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Fast backup/restore
On Oct 17, 2006, at 10:43 AM, [EMAIL PROTECTED] wrote:We just tar/gzip the entire data directory. It takes all of 20 sec. We've successfully restored from that also. The machine you are restoring to *must* be running the save version of postgresql you backed up from. If you successfully backed up in 20 seconds, then you have a tiny DB. Also, if you successfully restored from that style backup, your DB must not be written to much, or you were extremely lucky to get a consistent state. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] old Pg interface
On Oct 16, 2006, at 1:15 PM, Brandon Metcalf wrote: Does anyone know of any issues with continuing to use the old Pg interface with newer versions of PostgreSQL? it is just a rather thin glue layer on top of the libpq interface, so it should continue to work just as any libpq app would continue to work. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] RES: Dates rejected
On Oct 16, 2006, at 1:08 PM, Carlos H. Reimer wrote: How can we explain the 01:00:00 hour that the to_date function returns for date 15/10/2006? does your timezone change from summer time to winter time (daylight savings, etc.) on that date? smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] [Slony1-general] Using slony with many schema's
On Oct 11, 2006, at 2:55 PM, snacktime wrote: So by putting all the data into one schema, every report query now gets run against a million or more rows instead of just a few hundred or thousand. So all clients will see a drop in query performance instead of just the clients with large amounts of data. Indexes on the customer_id field of the combined data tables helps a lot. That and big hardware with big RAM. :-) We store data for all our customers in the same tables. some have several hundred thousand of their own customers, and millions of transactions from them; others have a few hundred. The responsiveness of postgres is still great. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] postgresql rising
On Sep 22, 2006, at 1:03 PM, Jim C. Nasby wrote: Berkus doesn't count??! He's got long hair! What more do you want?! Well, then based on volume he should count as two :-) No offense intended, Josh... *I'd* count as two, too. smime.p7s Description: S/MIME cryptographic signature
