Re: [GENERAL] Minimizing Recovery Time (wal replication)
On Thu, 2009-04-09 at 14:27 -0500, Bryan Murphy wrote: I have two hot-spare databases that use wal archiving and continuous recovery mode. I want to minimize recovery time when we have to fail over to one of our hot spares. Right now, I'm seeing the following behavior which makes a quick recovery seem problematic: (1) hot spare applies 70 to 75 wal files (~1.1g) in 2 to 3 min period (2) hot spare pauses for 15 to 20 minutes, during this period pdflush consumes 99% IO (iotop). Dirty (from /proc/meminfo) spikes to ~760mb, remains at that level for the first 10 minutes, and then slowly ticks down to 0 for the second 10 minutes. (3) goto 1 The database is performing too frequent restartpoints. This has been optimised in PostgreSQL 8.4 by the addition of the bgwriter running during recovery. This will mean that your hot spare will not pause while waiting for restartpoint to complete, so will be significantly faster. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Querying a Large Partitioned DB
On Fri, 2009-04-10 at 09:15 -0500, Justin Funk wrote:
I need to be able to do full text searches on the message field, and
of course, it needs to be reasonably fast.
The table is partitioned daily and has this structure:
My typical query looks like this:
SELECT * FROM SystemEvents WHERE message_index_col @@
to_tsquery('english', 'Term') LIMIT 25 OFFSET 0;
The partitioning does nothing to improve your typical query.
Loop through the tables from first to last until you have returned 25
records. That way you won't have to wait to search every table.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
--
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] No return from trigger function
On Wed, 2009-04-08 at 16:06 -0400, Tom Lane wrote: James B. Byrne byrn...@harte-lyne.ca writes: I just need another set of eyes to see whatever it is that I am overlooking. The RETURN is inside the EXCEPTION clause. You really need two BEGINs here, one for the outer function body and one for the exception block around the INSERT. Many people find it surprising that PL/pgSQL allows this occur. It would be good to have a TODO item relating to improved checking of PL/pgSQL functions to avoid runtime errors. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Connect without specifying a database?
On Apr 11, 2009, at 6:10 PM, li...@mgreg.com wrote: What do you mean when you say the catalogs...are database- specific ? If I'm understanding what you're saying, my whole point is that I don't want to be tied to a database to do any kind of querying about the PG engine itself. Does that make sense? Look at it from the other side; You have a DBMS and you want to store information about what databases and which users are available, who can and can't connect, etc. It makes sense to store that in a database, right? To request that information you need to connect to the database server. Considering that information is stored in a database, having to specify that database to connect to makes sense. Whether that's a named database (with a documented fixed name of course, in this case 'template1' or 'postgres') or an anonymous database doesn't make much difference. You'll still have to specify several other connection parameters (host port at least), so why not also a valid user (quite desirable from a security point of view) and a database name? It may not make as much sense from a user point of view, but it makes a lot of sense from a database point of view. 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,49e1ca98129741055947028! -- 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] Partitioned table and trigger/insert result horribleness
On Mon, 2009-04-06 at 09:57 +0200, Henry wrote: Is this weirdness scheduled to be addressed in 8.4, or is there some other hack I can try (without changing all SQL), or is the *only* solution to mess with existing (working) front-end code to work around this issue? No, not in 8.4 Use Rules is the current answer, though that has other issues also. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Partitioned table and trigger/insert result horribleness
I have to say, that this 'feature' also annoys me. at least ability to update row count would be appreciated. Not to mention proper partitioning, but that requires quite few changes in the guts. Currently there was a guy offering patch that adds partitioning, but it uses built in trigger, which is mid-way solution. (still better than having to write everything manually). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql 8.1.9 autovacuum safe?
Hi, We are running postgresql-8.1.9 and plan to upgrade to 8.2 or even 8.3 but can't just yet. I need to run analyze periodically (like hourly), but before I write a script to loop through the tables in each schema and run analyze, I thought I would try autovacuum. I say one post that said there was a bug with autovacuum in 8.1.x? Is autovacuum under 8.1.9 safe or should I wait until I upgrade? Thanks in advance. -- irene - Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- 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] psql 8.1.9 autovacuum safe?
On 12/04/2009 17:27, Irene Barg wrote: We are running postgresql-8.1.9 and plan to upgrade to 8.2 or even 8.3 but can't just yet. I need to run analyze periodically (like hourly), Well, the current version in that branch is 8.1.17, so you're missing a *lot* of bug fixes. It would be worth your while looking through the release notes for the intervening 8.1.X releases - you may well find the answer to your question there. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] psql 8.1.9 autovacuum safe?
On Sun, 2009-04-12 at 09:27 -0700, Irene Barg wrote: Hi, We are running postgresql-8.1.9 and plan to upgrade to 8.2 or even 8.3 but can't just yet. I need to run analyze periodically (like hourly), but before I write a script to loop through the tables in each schema and run analyze, I thought I would try autovacuum. I say one post that said there was a bug with autovacuum in 8.1.x? Is autovacuum under 8.1.9 safe or should I wait until I upgrade? There was a serious issue in 8.1.4 that, in certain situations, would make VACUUM and AUTOVACUUM totally inefective. But, afaik, it's fixed since 8.1.6. joao Thanks in advance. -- irene - Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- 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] psql 8.1.9 autovacuum safe?
Raymond O'Donnell r...@iol.ie writes: On 12/04/2009 17:27, Irene Barg wrote: We are running postgresql-8.1.9 and plan to upgrade to 8.2 or even 8.3 but can't just yet. I need to run analyze periodically (like hourly), Well, the current version in that branch is 8.1.17, so you're missing a *lot* of bug fixes. It would be worth your while looking through the release notes for the intervening 8.1.X releases - you may well find the answer to your question there. Like, say, here: http://www.postgresql.org/docs/8.1/static/release-8-1-16.html regards, tom lane -- 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] psql 8.1.9 autovacuum safe?
Hi, I found the answer i needed in the HISTORY file that came with the source as well as the online release notes below. I found the answer to a few other questions as well. I will do more digging before posting in the future. Thank you all. --irene Tom Lane wrote: Raymond O'Donnell r...@iol.ie writes: On 12/04/2009 17:27, Irene Barg wrote: We are running postgresql-8.1.9 and plan to upgrade to 8.2 or even 8.3 but can't just yet. I need to run analyze periodically (like hourly), Well, the current version in that branch is 8.1.17, so you're missing a *lot* of bug fixes. It would be worth your while looking through the release notes for the intervening 8.1.X releases - you may well find the answer to your question there. Like, say, here: http://www.postgresql.org/docs/8.1/static/release-8-1-16.html regards, tom lane -- - Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- 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] psql 8.1.9 autovacuum safe?
Good Afternoon Irene Please keep us apprised on how your implementation progresses.. Ta Me Go Maith Martin __ Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Sun, 12 Apr 2009 15:28:57 -0700 From: ib...@noao.edu To: t...@sss.pgh.pa.us CC: r...@iol.ie; pgsql-general@postgresql.org Subject: Re: [GENERAL] psql 8.1.9 autovacuum safe? Hi, I found the answer i needed in the HISTORY file that came with the source as well as the online release notes below. I found the answer to a few other questions as well. I will do more digging before posting in the future. Thank you all. --irene Tom Lane wrote: Raymond O'Donnell r...@iol.ie writes: On 12/04/2009 17:27, Irene Barg wrote: We are running postgresql-8.1.9 and plan to upgrade to 8.2 or even 8.3 but can't just yet. I need to run analyze periodically (like hourly), Well, the current version in that branch is 8.1.17, so you're missing a *lot* of bug fixes. It would be worth your while looking through the release notes for the intervening 8.1.X releases - you may well find the answer to your question there. Like, say, here: http://www.postgresql.org/docs/8.1/static/release-8-1-16.html regards, tom lane -- - Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Windows Live™: Keep your life in sync. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_allup_1a_explore_042009
Re: [GENERAL] psql 8.1.9 autovacuum safe?
On Sun, Apr 12, 2009 at 4:28 PM, Irene Barg ib...@noao.edu wrote: Hi, I found the answer i needed in the HISTORY file that came with the source as well as the online release notes below. I found the answer to a few other questions as well. I will do more digging before posting in the future. Funny thing is, the more you dig, the more questions you'll have. They'll just be that much more detailed. -- 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] In memory Database for postgres
Hello, I created in-memory database but the problem is all the data will be accessed from main memory .when ever the system is restarted the entire data that is in the tables will lost.Is there any way to dump all the data in to local hard disk before restarting the system or any similar method to save the data in to a permanent storage. Thanks, Avin. From: Blazej bl.oleszkiew...@gmail.com To: avin_frie...@yahoo.com Cc: postgresql Forums pgsql-general@postgresql.org Sent: Monday, November 17, 2008 4:26:46 PM Subject: Re: [GENERAL] In memory Database for postgres Sorry I forgot about create tablespace script - this is the SQL script: CREATE TABLESPACE ram_space LOCATION '/mnt/ram0/pgspace'; And then: CREATE TABLE (...) TABLESPACE ram_space; and table is in memory. Regards, Blazej 2008/11/17 Blazej bl.oleszkiew...@gmail.com: In my opinion very nice solution is building part of PostgreSQL database in memory - below it is instruction how to build PostgreSQL schema in memory in Linux. I tested this with my ROLAP solution for recalculation MOLAP cubes in memory and then join with master cube (this speeds up proces about 10 times!!! - but in other solution may be different). In grub (or other bootloader) you must declare ramdisk and then in OS: mke2fs /dev/ram0 mkdir /mnt/ram0 mount /dev/ram0 /mnt/ram0 mkdir /mnt/ram0/pgspace chown postgres:postgres /mnt/ram0/pgsapce The /mnt/ram0/pgsapce folder must be empty (ERROR: directory /mnt/ram0 is not empty) And then you may create tables (in this schema of course) and write to them. Of course you must delete schema before shutdown PostgreSQL and OS - I dont't now how resolve problem with error when the schema was not deleted? - I have no time to think about it maybe anybody know how to restore db when the in memory schema was damaged? Regards, Blazej 2008/11/17 aravind chandu avin_frie...@yahoo.com: Hello, I guess most of you guys heard about In Memory Database.I have a small question regarding it.I need to create an In Memory Database for postgresql through which I have to perform various operations on postgresql database(queries,procedures,programs using pqxx API etc...).I didn't have any idea of how to start and where to start this issue.Please comment on this issue,so that it will be really helpful to me . Thanks, Avin.
Re: [GENERAL] In memory Database for postgres
aravind chandu wrote: I created in-memory database but the problem is all the data will be accessed from main memory .when ever the system is restarted the entire data that is in the tables will lost.Is there any way to dump all the data in to local hard disk before restarting the system or any similar method to save the data in to a permanent storage. memory is volatile, disk is persistent. if you want persistent databases, I recommend storing them on disk. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres: Installing as a service
Hi , I have extracted postgres from postgres-noinstaller.zip file. How do i install postgres as a service from cmd line ? Are there are any binaries provided by postgres to install it as a service ? Please note that i am aware that msi installer automatically does all this. I want to know how to acheive the same from the postgres db extracted from the zip file. Thanks.
