[GENERAL] Postgres connectivity problem.
Hi All, Postgres 'psql' client is getting hang in 'stat' call while connecting to postgres server. Pstack output: 11017: ./bin/psql -U postgres configdb stat (ff3f5640, ffbff578) My machine details: SunOS my_machine 5.10 Generic_120011-14 sun4u sparc SUNW,Sun-Fire-V245 Postgres version: PostgreSQL 8.2.0 on sparc-sun-solaris2.8, compiled by /export/home/uxapps/studio6/SUNWspro/bin/cc -Xa Please give your valuable suggestion and solution for above problem. Thanks in advance, Santosh. This email and any attachments may contain legally privileged and/or confidential information of Starent Networks, Corp. and is intended only for the individual or entity named in the message. The information transmitted may not be used to create or change any contractual obligations of Starent Networks, Corp. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this e-mail and its attachments by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient, please notify the sender immediately -- by replying to this message or by sending an email to postmas...@starentnetworks.com -- and destroy all copies of this message and any attachments without reading or disclosing their contents. Thank you.
Re: [GENERAL] Postgres connectivity problem.
Bhujbal, Santosh wrote: Hi All, Postgres ‘psql’ client is getting hang in ‘stat’ call while connecting to postgres server. Pstack output: 11017: ./bin/psql -U postgres configdb stat (ff3f5640, ffbff578) My machine details: SunOS my_machine 5.10 Generic_120011-14 sun4u sparc SUNW,Sun-Fire-V245 Postgres version: PostgreSQL 8.2.0 on sparc-sun-solaris2.8, compiled by /export/home/uxapps/studio6/SUNWspro/bin/cc –Xa ancient version of postgres, complied with a rather old sun compiler on a fairly old solaris version, where do we start? The 8.2 family is up to 8.2.13, and the .0 release of any major.minor version tends to be on the buggy side. did this server work right in the past and just stopped workinig, or was it just built, or what? solaris 10 as of the last couple incremental releases comes with postgres 8.2 32 and 64bit precompiled and configured to run from SMF I would use those, not this old old 8.2.0 build you seem to have. -- 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] WITH RECURSIVE clause -- all full and partial paths
Dear David and Harald, Thanks both for your help. Good day. Aryé.
[GENERAL] Trigger Function and backup
Hello every one, I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in few days) on windows platform. I had tried using Slony-I for replication and was not able to create a cluster. After struggling for some time i decide to implement a way around to take differential backup. As the tables getting changed were very less. Here is what i intend to do: - Write a trigger for each of the tables in concern - Some how write a function which can copy / execute the same query in another temp Db on the same physical system (I have no idea how to do that) - Take a backup of temp DB which will be the differential backup of DB (We need to clear temp db after backup) Am i going in the right direction? Is there any way i can implement it. Any help will be really of great help Regards Nishkarsh%-| -- View this message in context: http://www.nabble.com/Trigger-Function-and-backup-tp24030638p24030638.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Trigger Function and backup
Hi, I have found the following strangeness on Windows: create table round_test (id int primary key, value double precision); insert into round_test(id, value) values(1, 1.5); insert into round_test(id, value) values(2, -1.5); insert into round_test(id, value) values(3, 3.5); select round(value) from round_test; psql 8.2.13 returns 2 -2 4 But psql 8.3.3 returns 1 -1 3 What does cause this? How can I avoid this incompatibility or perhaps bug? Thanks, Otto
[GENERAL] Rounding incompatibility
Hi, I have found the following strangeness on Windows versions: create table round_test (id int primary key, value double precision); insert into round_test(id, value) values(1, 1.5); insert into round_test(id, value) values(2, -1.5); insert into round_test(id, value) values(3, 3.5); select round(value) from round_test; psql 8.2.13 returns 2 -2 4 But psql 8.3.3 returns 1 -1 3 Trying more values it seems that 8.2 rounding works according to banker's rounding rules. Can you confirm this? How can I avoid this incompatibility or perhaps bug? Thanks, Otto
[GENERAL] Postgres 8.2 Memory Tuning
Hi , I have installed Postgres 8.2 in windows with default configuration.The windows machine has dual processor(2 GHZ,1GHZ) with 1 GB RAM.I use my java application with postgres and my database connection pool has some 25 connections.I understand from the documentation that postgres starts a seperate process for each of the connection.Now, my application and the database will be installed on the same system.Even under moderate load in my application, each of the postgres process occupies some 25 MB memory leading to total postgres memory usage of 500+ MB. Now, how do i limit the memory used by each of the postgres process to bring down the over all memory usage ? Thanks
Re: [GENERAL] Postgres 8.2 Memory Tuning
CMJ, same system.Even under moderate load in my application, each of the postgres process occupies some 25 MB memory leading to total postgres memory usage of 500+ MB. Now, how do i limit the memory used by each how did you measure those 25MB and those 500+MB MEMORY usage? I guess you are running windows task manager and looking into the default memory column. That is adress space used, rather then memory used. So Postgres is using 500MB of adress space; share_memory of those processes is accounted for every process. Look within additional columns of taskmanager to find the one giving the private memory of the process. Especially on windows you should update to the latest 8.3.x PostgreSQL. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad?
Re: [GENERAL] cygwin and postgresql
On 2009-06-14, James B. Byrne byrn...@harte-lyne.ca wrote: On Sun, June 14, 2009 15:45, Scott Marlowe wrote: why it would not form part of the cygwin installation? Is there a reason you're not using the native windows postgresql packages? Because for some reason, processes running in the cygwin environment could not create databases in the postgresql instance running in windows. that's odd, were you specifying host=localhost, if not it may be that the cygwin processes were trying to use local sockets. (oner difference of the native windows postgres to linux is no local sockets) As I develop for Linux and use cygwin to (mostly) replicate that environment on my MS-Win laptop I thought that moving everything having to do with the development environment under cygwin would be best. I develop for windows on a linux desktop (exact opposite of what you do!) -- 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] running pg_dump from python
On 2009-06-14, Garry Saddington ga...@schoolteachers.co.uk wrote:
I ahve the following python file that I am running as an external method
in Zope.
def backup():
import os
os.popen(c:/scholarpack/postgres/bin/pg_dump scholarpack
c:/scholarpack/ancillary/scholarpack.sql)
are you sure you're using os.popen correctly?
you don't appear to be waiting for the pg_dump process to finish.
data=open('c:/scholarpack/ancillary/scholarpack.sql','r')
r=data.read()
data.close
return r
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 10 TB database
Hi! We are thinking to create some stocks related search engine. It is experimental project just for fun. The problem is that we expect to have more than 250 GB of data every month. This data would be in two tables. About 50.000.000 new rows every month. We want to have access to all the date mostly for generating user requesting reports (aggregating). We would have about 10TB of data in three years. Do you think is it possible to build this with postgresql and have any idea how to start? :) Thanks in advance, Artur -- 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] 10 TB database
On Mon, Jun 15, 2009 at 1:00 PM, Artura_wron...@gazeta.pl wrote: Hi! We are thinking to create some stocks related search engine. It is experimental project just for fun. The problem is that we expect to have more than 250 GB of data every month. This data would be in two tables. About 50.000.000 new rows every month. Well, obviously you need to decrease size of it, by doing some normalization than. If some information is the same across table, stick it into separate table, and assign id to it. If you can send me sample of that data, I could tell you where to cut size. I have that big databases under my wings, and that's where normalization starts to make sens, to save space (and hence speed things up). We want to have access to all the date mostly for generating user requesting reports (aggregating). We would have about 10TB of data in three years. For that sort of database you will need partitioning for sure. Napisz do mnie, to moge pomoc prywatnie, moze za niewielka danina ;) -- GJ -- 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] 10 TB database
I have a 300GB database, and I would like to look at partitioning as a possible way to speed it up a bit. I see the partitioning examples from the documentation: http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html Is anyone aware of additional examples or tutorials on partitioning? Thanks, Whit 2009/6/15 Grzegorz Jaśkiewicz gryz...@gmail.com: On Mon, Jun 15, 2009 at 1:00 PM, Artura_wron...@gazeta.pl wrote: Hi! We are thinking to create some stocks related search engine. It is experimental project just for fun. The problem is that we expect to have more than 250 GB of data every month. This data would be in two tables. About 50.000.000 new rows every month. Well, obviously you need to decrease size of it, by doing some normalization than. If some information is the same across table, stick it into separate table, and assign id to it. If you can send me sample of that data, I could tell you where to cut size. I have that big databases under my wings, and that's where normalization starts to make sens, to save space (and hence speed things up). We want to have access to all the date mostly for generating user requesting reports (aggregating). We would have about 10TB of data in three years. For that sort of database you will need partitioning for sure. Napisz do mnie, to moge pomoc prywatnie, moze za niewielka danina ;) -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] help with data recovery from injected UPDATE
On Sun, Jun 14, 2009 at 10:32 AM, Gus Gutoskishared.entanglem...@gmail.com wrote: Merlin Moncure wrote: postgresql 8.1 supports pitr archiving. you can do continuous backups and restore the database to just before the bad data. I tried using point-in-time-recovery to restore the state of the database immediately before the corruption. It didn't work, but it was quite a show. Here's the story. yes, I'm sorry...you misunderstood my suggestion. the database supports continuous *archiving* from which a recovery can be made. No archives, no recovery :-). Here is what I'd do if I in your shoes: From a copy of your filesystem backup, set up the database to run and attempt pg_resetxlog before starting it up. Log in and see if your data is there...if it is, you hit the jackpot...if not...the next step is to determine if the data is actually _in_ the table. There are a couple of ways to do this..tinkering around with transaction visibility is one...simply dumping the heap file for the table and inspecting it is another. merlin -- 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] cygwin and postgresql
On: 15 Jun 2009 12:08:22 GMT, Jasen Betts ja...@xnet.co.nz On Sun, June 14, 2009 15:45, Scott Marlowe wrote: Is there a reason you're not using the native windows postgresql packages? Because for some reason, processes running in the cygwin environment could not create databases in the postgresql instance running in windows. that's odd, were you specifying host=localhost, if not it may be that the cygwin processes were trying to use local sockets. (oner difference of the native windows postgres to linux is no local sockets) The cygwin processes had no trouble connecting to and operating on existing databases. They could even drop a database. They just could not recreate one after dropping it. As the database in question is the one used for integration testing and as it gets dropped and recreated on the fly at various points in the test suite this was a major annoyance. It could very well be that this behaviour is caused by some other issue but I have a nagging suspicion that NTFS security issues are at the root of it. As I have no other need to access pgsql on my laptop, I thought it best to bring everything under one roof, so to speak, and begin checking things out from that initial point. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Rounding incompatibility
=?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= havasvolgyi.o...@gmail.com writes: I have found the following strangeness on Windows versions: Were your two versions built different ways (perhaps with different compilers)? This comes down to what the system-supplied rint() function does. 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] Rounding incompatibility
On Mon, Jun 15, 2009 at 4:28 PM, Havasvölgyi Ottó
havasvolgyi.o...@gmail.com wrote:
Hi,
I have found the following strangeness on Windows versions:
create table round_test (id int primary key, value double precision);
insert into round_test(id, value) values(1, 1.5);
insert into round_test(id, value) values(2, -1.5);
insert into round_test(id, value) values(3, 3.5);
select round(value) from round_test;
psql 8.2.13 returns
2
-2
4
But psql 8.3.3 returns
1
-1
3
Trying more values it seems that 8.2 rounding works according to banker's
rounding rules.
Can you confirm this?
How can I avoid this incompatibility or perhaps bug?
Posting the output of 'select version()' from both the databases will help
blaming some distribution.
Best regards,
--
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device
[GENERAL] horizontal sharding
Hey guys what is a good way to horizontal shard in postgresql 1. pgpool 2 2. gridsql which is a better way to use sharding also is it possible to paritition without changing client code thanks -- Bidegg worlds best auction site http://bidegg.com
[GENERAL] Custom Fields Database Architecture
Hi, I'm designing a database schema in which I should allow user to create custom fields at the application level. My application is a web-based system and it has multiple companies in a single database. So this means that each company can create their own custom fields. A custom field created in a company should not be visibile to the other company. Also, we don't want to restrict the number of fields allowed to create. I also read some article which talks about the type of patterns: 1. Meta-database 2. Mutating 3. Fixed 4. LOB My question here is, what is the best approach to define the architecture for custom fields. Performance should not be compromised. Thank you in advance. Regards, Gnanam. -- View this message in context: http://www.nabble.com/Custom-Fields-Database-Architecture-tp24034270p24034270.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Rounding incompatibility
On Mon, Jun 15, 2009 at 3:33 PM, Tom Lanet...@sss.pgh.pa.us wrote: =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= havasvolgyi.o...@gmail.com writes: I have found the following strangeness on Windows versions: Were your two versions built different ways (perhaps with different compilers)? This comes down to what the system-supplied rint() function does. If they are our packages, then 8.2 was built with mingw/msys, and 8.3 with VC++. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Custom Fields Database Architecture
On Mon, Jun 15, 2009 at 06:04:25AM -0700, Gnanam wrote: Hi, I'm designing a database schema in which I should allow user to create custom fields at the application level. This is called EAV (Entity-Attribute-Value), and it's a multi-decade-old mistake. Re-think your design. http://archives.postgresql.org/pgsql-general/2008-02/msg00075.php http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/ http://en.wikipedia.org/wiki/Inner-Platform_Effect Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] horizontal sharding
On Mon, Jun 15, 2009 at 12:32:15AM -0700, mobiledream...@gmail.com wrote: Hey guys what is a good way to horizontal shard in postgresql Sharding is not really a technical term, so it's not really possible to answer this question meaningfully as posed. What is it that you actually want to accomplish here exactly? What are your goals, and what are your priorities on those goals? What will you trade off to achieve them? 1. pgpool 2 2. gridsql which is a better way to use sharding also is it possible to paritition without changing client code Almost certainly not. This would be one of the trade-offs mentioned above. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Trigger Function and backup
On Mon, Jun 15, 2009 at 5:50 AM, Havasvölgyi Ottóhavasvolgyi.o...@gmail.com wrote: Hi, I have found the following strangeness on Windows: create table round_test (id int primary key, value double precision); insert into round_test(id, value) values(1, 1.5); insert into round_test(id, value) values(2, -1.5); insert into round_test(id, value) values(3, 3.5); Firstly, I'm not sure what this message has to do with the thread Trigger Function and backup. Secondly, please show your table definition. And Finally, the round() mathematical function is ill-defined. People have been arguing over this for decades. Choose either ceil() or floor() to get a more precise result with appropriate addition or subtraction of 0.5 to get the rounding effect you seem to desire. -- 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] Custom Fields Database Architecture
On Monday 15 June 2009, Gnanam wrote: Hi, I'm designing a database schema in which I should allow user to create custom fields at the application level. My application is a web-based system and it has multiple companies in a single database. So this means that each company can create their own custom fields. A custom field created in a company should not be visibile to the other company. Also, we don't want to restrict the number of fields allowed to create. I also read some article which talks about the type of patterns: 1. Meta-database 2. Mutating 3. Fixed 4. LOB My question here is, what is the best approach to define the architecture for custom fields. Performance should not be compromised. Thank you in advance. Regards, Gnanam. -- View this message in context: http://www.nabble.com/Custom-Fields-Database-Architecture-tp24034270p240342 70.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. It depends a bit how you want to use the data. If you are not wedded to the RDMS model, you might look at CouchDB which is a schema-less DB. But do not expect to run SQL against it - it takes a rather different approach. There are others around, some of them proprietary, Lotus Notes/Domino is probably the best know of these. David -- 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] String Manipulation
Alban,
That was exactly what I was looking for.
Thanks
Christine
At 03:45 AM 13/06/2009, you wrote:
On Jun 13, 2009, at 12:35 AM, Christine Penner wrote:
Sam,
The problem with making it a numeric field is that I have seen
things like A123, #123a or 23-233. This is only here to make most
sorting work better, not perfect. It all depends on how they enter
the data. Wont the different formats make it harder to convert to a
number?
I tried your suggestion and haven't had any luck. For a quick test I
did this:
select b_lot_or_st_no, substring('1a','^[0-9]+') as TEST from
F_BUILDINGS
With this I tried using b_lot_or_st_no instead of 1a, I also
replaced the , with for like they do in the manual. I looked through
the manual but I'm still stuck.
The above regular expression assumes values start with a number, so it
won't return anything useful for values like 'A123' or '#123a' and
will just return '23' for '23-233'. I don't think Sam intended it to
be used with the values in your database but just to illustrate how a
regular expression could be used.
I think what you want is something like:
select regex_replace(b_lot_or_st_no, '[^0-9]', '', 'g')
This globally replaces everything that's not a number by '',
effectively removing it from the text.
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,4a33833c759151518024860!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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] cygwin and postgresql
On Mon, Jun 15, 2009 at 8:22 AM, James B. Byrnebyrn...@harte-lyne.ca wrote: On: 15 Jun 2009 12:08:22 GMT, Jasen Betts ja...@xnet.co.nz On Sun, June 14, 2009 15:45, Scott Marlowe wrote: Is there a reason you're not using the native windows postgresql packages? Because for some reason, processes running in the cygwin environment could not create databases in the postgresql instance running in windows. that's odd, were you specifying host=localhost, if not it may be that the cygwin processes were trying to use local sockets. (oner difference of the native windows postgres to linux is no local sockets) The cygwin processes had no trouble connecting to and operating on existing databases. They could even drop a database. They just could not recreate one after dropping it. As the database in question is the one used for integration testing and as it gets dropped and recreated on the fly at various points in the test suite this was a major annoyance. It could very well be that this behaviour is caused by some other issue but I have a nagging suspicion that NTFS security issues are at the root of it. As I have no other need to access pgsql on my laptop, I thought it best to bring everything under one roof, so to speak, and begin checking things out from that initial point. So, for sure something like: alter user dbuser with createdb; didn't fix the problem? -- 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] Trigger Function and backup
On Mon, Jun 15, 2009 at 4:29 AM, Nishkarshnishkars...@rediffmail.com wrote: Hello every one, I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in few days) on windows platform. I had tried using Slony-I for replication and was not able to create a cluster. After struggling for some time i decide to implement a way around to take differential backup. As the tables getting changed were very less. Here is what i intend to do: - Write a trigger for each of the tables in concern - Some how write a function which can copy / execute the same query in another temp Db on the same physical system (I have no idea how to do that) - Take a backup of temp DB which will be the differential backup of DB (We need to clear temp db after backup) Am i going in the right direction? Is there any way i can implement it. Any help will be really of great help Generating a full trigger based replication system on your own is IMNSHO crazy. Slony is the best solution to this problem (trigger replication with postgres) that I know of, and is probably better than any one person to come up with in a reasonable amount of time. Probably, your best course of action if you need to get things running right now is to give slony another go (why did you not succeed?). Hand written trigger replication is ok if you need to copy, say, a couple of tables or you have some other very specific requirement. In particular, copying an insert to a mirror database with trigger function wrapping dblink is a snap (updates are more problematic, but doable). Of course, you need to figure out how to deal with schema updates and other issues that plague replication systems such as volatile data in cascading triggers (just to name one). General purpose trigger replication is a huge project... It sounds to me that what you really want is the 'hot standby' feature that unfortunately missed the cut for 8.4. Hot standby is probably the easiest way to mirror a database for purposes of read only querying. There are no triggers to worry about, just a few .conf settings and some other setup to get going (more or less, it isn't finalized yet). So maybe, waiting for hot standby (or even, digging up a hot standby patch and trying to apply it vs. 8.4 if your adventurous) is the answer. Another possibility is to look at statement level replication, like pgpool. merlin -- 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] horizontal sharding
what is a good way to horizontal shard in postgresql 1. pgpool 2 2. gridsql which is a better way to use sharding Both are good methods of sharding, but it depends on your goals. GridSQL is better in reporting applications where as PG Pool2 is better in transactional situations. also is it possible to paritition without changing client code Yes, but it depends on the SQL in your client code. If you are just using simple SQL with no stored functions, you should be able to slip in either solution without changing the client code. -- Jim Mlodgenski EnterpriseDB (http://www.enterprisedb.com)
Re: [GENERAL] horizontal sharding
Jim Mlodgenski wrote: also is it possible to paritition without changing client code Yes, but it depends on the SQL in your client code. If you are just using simple SQL with no stored functions, you should be able to slip in either solution without changing the client code. what about queries that need to do joins or aggregate reporting across the partitions?!? I can't see how that could be done transparently short of something like Oracle RAC. -- 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] horizontal sharding
what about queries that need to do joins or aggregate reporting across the partitions?!? I can't see how that could be done transparently short of something like Oracle RAC. GridSQL actually does a nice job of breaking up the query and optimizing it appropriately to handle cross node joins. There are agents running on each of the nodes that handles the inter-node communication allowing it to be transparent to the calling application. -- Jim Mlodgenski EnterpriseDB (http://www.enterprisedb.com)
Re: [GENERAL] 10 TB database
Artur wrote: Hi! We are thinking to create some stocks related search engine. It is experimental project just for fun. The problem is that we expect to have more than 250 GB of data every month. This data would be in two tables. About 50.000.000 new rows every month. Sounds a bit like what Truviso does ... -- Alvaro Herrera -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Amazon EC2 | Any recent developments
Hello All, I am investigating the possibility of hosting pgsql 8.3 on Amazon EC2 implementing a simple HA solution. My search of postgresql amazon cloud has produced little result. Just wondering if there has been any recent development with EBS etc. and anybody would care to share their experiences. Thank you very much. aj -- 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] horizontal sharding
On Mon, Jun 15, 2009 at 11:36 AM, John R Piercepie...@hogranch.com wrote: Jim Mlodgenski wrote: also is it possible to paritition without changing client code Yes, but it depends on the SQL in your client code. If you are just using simple SQL with no stored functions, you should be able to slip in either solution without changing the client code. what about queries that need to do joins or aggregate reporting across the partitions?!? I can't see how that could be done transparently short of something like Oracle RAC. If you use inheritance and therefore put the child tables on the same server, it's invisible to the client that the table is partitioned. Make sure you've got constraint_exclusion turned on. -- 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] Amazon EC2 | Any recent developments
On Mon, Jun 15, 2009 at 11:12:32AM -0700, AJAY A wrote: - Hello All, - - I am investigating the possibility of hosting pgsql 8.3 on Amazon EC2 - implementing a simple HA solution. My search of postgresql amazon - cloud has produced little result. Just wondering if there has been - any recent development with EBS etc. and anybody would care to share - their experiences. We've been researching Cloud/EC2 as well. The consensus so far from RightScale, and IBM (the first 2 we've spoken with) is that if I/O performance is important to you, then stay away from the cloud. (it's just VMs afterall) Dave -- 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] 10 TB database
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Artur Sent: Monday, June 15, 2009 5:00 AM To: pgsql-general@postgresql.org Subject: [GENERAL] 10 TB database Hi! We are thinking to create some stocks related search engine. It is experimental project just for fun. The problem is that we expect to have more than 250 GB of data every month. This data would be in two tables. About 50.000.000 new rows every month. We want to have access to all the date mostly for generating user requesting reports (aggregating). We would have about 10TB of data in three years. Do you think is it possible to build this with postgresql and have any idea how to start? :) Consider summarization of this data into a data warehouse. Most of the data will be historical and therefore the vast majority of the data will be read-mostly (with the rare write operations probably consisting mostly of corrections). You won't want to scan the whole 10TB every time you make a summarization query. I have an idea that might make an interesting experiment: Create tables that are a combination of year and month. Create views that combine all 12 months into one yearly table. Create a view that combines all the yearly views into one global view. The reason that I think this suggestion may have some merit is that the historical trends will not need to be recalculated on a daily basis (but it would be nice if you could perform calculations against the whole pile at will on rare occasions). By maintaining separate tables by month, it will reduce the average depth of the b-trees. I guess that for the most part, the active calculations will be only against recent data (e.g. the past 6 months to one year or so). It could also be interesting to create a view that combines the N most recent months of data, where N is supplied on the fly (I do not know how difficult it would be to create this view or even if it is possible). If you are going to collect a terrific volume of data like this, I suggest that a mathematics package might be coupled with the data like SAS, R, Octave, SciLab, Maxima, etc. so that you can support decisions derived from the data effectively. You are also going to need high-end hardware to support a database like this. Just some ideas you might like to test when you start fooling around with this data. IMO-YMMV -- 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] Amazon EC2 | Any recent developments
Hi, I have more than a few Postgres instances on EC2. For reliability I use EBS, and take regular snapshots while also streaming the WAL files to S3. So far, the few times that my machine died, I had no issue with getting it back from EBS or the EBS volume. I also take tar backups every day, and I keep a few days back of tar, snapshots and WAL log files. If you require high performance you might have to look into the large or XL instances, as their networking is a lot faster, and EBS is accessed through the network. They also have a lot more memory. I actually think that Postgres has a big advantage on the cloud, and that's the ability to easily recover from crashes. Because of the way Postgres commits to disk and work with WALs, there is much higher chance of recovering the DB than most other DB servers out there. Bye, Guy. On Mon, Jun 15, 2009 at 11:12 AM, AJAY Aaagrawa...@gmail.com wrote: Hello All, I am investigating the possibility of hosting pgsql 8.3 on Amazon EC2 implementing a simple HA solution. My search of postgresql amazon cloud has produced little result. Just wondering if there has been any recent development with EBS etc. and anybody would care to share their experiences. Thank you very much. aj -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Family management on rails: http://www.famundo.com My development related blog: http://devblog.famundo.com -- Family management on rails: http://www.famundo.com My development related blog: http://devblog.famundo.com -- 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] cygwin and postgresql
On Mon, June 15, 2009 13:02, Scott Marlowe wrote: So, for sure something like: alter user dbuser with createdb; didn't fix the problem? I have removed the windows installation and can no longer check this. I maintained the pg roles via pgadmin3 and, to the best of my ability to recollect, the owner of the development and test databases had dbcreate privileges. If the problem persists under the cygwin environment then I will revisit the native pg installation for testing. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Select ranges based on sequential breaks
Hi, I'm having difficulty constructing a query that will find breaks where data change in a time-series. I've done some searching for this too, but I haven't found anything. Here is my example situation, consider my source table: datebin 2009-01-01 red 2009-01-02 red 2009-01-03 blue 2009-01-04 blue 2009-01-05 blue 2009-01-06 red 2009-01-07 blue 2009-01-08 blue 2009-01-09 red 2009-01-10 red I would like to get the first and last of each consecutive series based on column bin. My result for the table would look like: first lastbin 2009-01-01 2009-01-02 red 2009-01-03 2009-01-05 blue 2009-01-06 2009-01-06 red 2009-01-07 2009-01-08 blue 2009-01-09 2009-01-10 red This is easy to compute using a spreadsheet or in R, but how would I do this with SQL? I'm using 8.3. Advice is appreciated. Thanks, -Mike -- 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] interval is ignored
On Jun 11, 10:14 am, rodeored in...@reenie.org wrote: SELECT INTERVAL '1' MONTH = 00:00:00 Therefore now=onemonthago SELECT NOW(),NOW()- INTERVAL '1' MONTH as onemonthago How do I get the timestamp for one month ago? Never mind, its SELECT NOW(),NOW()- INTERVAL '1 MONTH' as onemonthago -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] interval is ignored
SELECT INTERVAL '1' MONTH = 00:00:00 Therefore now=onemonthago SELECT NOW(),NOW()- INTERVAL '1' MONTH as onemonthago How do I get the timestamp for one month ago? -- 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] interval is ignored
rodeored in...@reenie.org writes: On Jun 11, 10:14 am, rodeored in...@reenie.org wrote: SELECT INTERVAL '1' MONTH = 00:00:00 Never mind, its SELECT NOW(),NOW()- INTERVAL '1 MONTH' as onemonthago Just for completeness --- the INTERVAL '1' MONTH syntax is supported as of 8.4. 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] Select ranges based on sequential breaks
On Mon, Jun 15, 2009 at 2:23 PM, Mike Toewsmwto...@sfu.ca wrote: Hi, I'm having difficulty constructing a query that will find breaks where data change in a time-series. I've done some searching for this too, but I haven't found anything. Here is my example situation, consider my source table: date bin 2009-01-01 red 2009-01-02 red 2009-01-03 blue 2009-01-04 blue 2009-01-05 blue 2009-01-06 red 2009-01-07 blue 2009-01-08 blue 2009-01-09 red 2009-01-10 red I would like to get the first and last of each consecutive series based on column bin. My result for the table would look like: first last bin 2009-01-01 2009-01-02 red 2009-01-03 2009-01-05 blue 2009-01-06 2009-01-06 red 2009-01-07 2009-01-08 blue 2009-01-09 2009-01-10 red This is easy to compute using a spreadsheet or in R, but how would I do this with SQL? I'm using 8.3. Advice is appreciated. (Written in email and untested- also, someone will probably provide a better way, I hope, but this should at least work) select date as first, (select date from table t3 where t3.date(select date from table t5 where t5.datet1.date and t5.bint1.bin order by date asc limit 1) order by date desc limit 1) as last, bin from table t1 where (select bin from table t2 where t2.datet1.order order by date desc limit 1)t1.bin; Ugly, and I'm pretty sure there's a much better way, but my brain is failing me right now- hopefully this'll at least get you started, though. -- - David T. Wilson david.t.wil...@gmail.com -- 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] Custom Fields Database Architecture
@David: You wrote in the links cited The flexibility stems from fear of making a design decision.. That's an important note. Nevertheless, there are use cases where you *can not* know in advance what the name is of the attribute! To me that's not fear but adaptiveness, modesty and knowing when to break the rules! An apparent successful example of this EAV design is OpenStreetMap (http://wiki.openstreetmap.org/wiki/Database_schema). @Gnanam: Look also at hstore which is an indexable(!) PostgreSQL data type for storing sets of (key,value) pairs within a single data field: http://www.postgresql.org/docs/8.3/static/hstore.html -S. 2009/6/15 David Fetter da...@fetter.org: On Mon, Jun 15, 2009 at 06:04:25AM -0700, Gnanam wrote: Hi, I'm designing a database schema in which I should allow user to create custom fields at the application level. This is called EAV (Entity-Attribute-Value), and it's a multi-decade-old mistake. Re-think your design. http://archives.postgresql.org/pgsql-general/2008-02/msg00075.php http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/ http://en.wikipedia.org/wiki/Inner-Platform_Effect Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] 10 TB database
Hi Artur, Some general comments: I'd look at partitioning and tablespaces to better manage the files where the data is stored, but also look at some efficiently parallelised disks behind the filesystems. You might also look at optimising the filesystem OS parameters to increase efficiency as well, so it is a mix of hardware/OS/filesystem db setup to optimise for such a situation. For data retrieval, clustered indexes may help, but as this requires a physical reordering of the data on disk, it may be impractical. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand Artur a_wron...@gazeta.pl 06/16/09 3:30 AM Hi! We are thinking to create some stocks related search engine. It is experimental project just for fun. The problem is that we expect to have more than 250 GB of data every month. This data would be in two tables. About 50.000.000 new rows every month. We want to have access to all the date mostly for generating user requesting reports (aggregating). We would have about 10TB of data in three years. Do you think is it possible to build this with postgresql and have any idea how to start? :) Thanks in advance, Artur -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water Atmospheric Research Ltd. -- 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] Custom Fields Database Architecture
On Mon, Jun 15, 2009 at 10:37:04PM +0200, Stefan Keller wrote: @David: You wrote in the links cited The flexibility stems from fear of making a design decision.. That's an important note. Nevertheless, there are use cases where you *can not* know in advance what the name is of the attribute! Those cases are extremely rare, and they don't fit with an RDBMS. To me that's not fear but adaptiveness, modesty and knowing when to break the rules! An apparent successful example of this EAV design is OpenStreetMap (http://wiki.openstreetmap.org/wiki/Database_schema). Funny you should mention that. The OpenStreetMap people are hitting exactly the issues I named, and they're ruing the day they decided on that schema. I don't recommend that anybody re-make their mistake. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DB Migration 8.4 - 8.3
I unintentionally installed 8.4beta2 on a server (using yum), while I run 8.3.7 on my dev machine. The 8.3 version of pg_dump does not work with the server, even with the -i option: 8.3: pg_dump: Error message from server: ERROR: column reltriggers does not exist the 8.4 pg_dump works okay, but then the 8.3 version of pg_restore is not able to restore the resultant databases: 8.3: pg_restore: [archiver] input file does not appear to be a valid archive How can I migrate a database from 8.4 to 8.3? I can't see any versioning options in pg_dump: http://www.postgresql.org/docs/8.4/static/app-pgdump.html Thanks! -- 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] DB Migration 8.4 - 8.3
--- On Mon, 15/6/09, Eoghan Murray eoghanomur...@gmail.com wrote: From: Eoghan Murray eoghanomur...@gmail.com Subject: [GENERAL] DB Migration 8.4 - 8.3 To: pgsql-general@postgresql.org Date: Monday, 15 June, 2009, 10:19 PM I unintentionally installed 8.4beta2 on a server (using yum), while I run 8.3.7 on my dev machine. The 8.3 version of pg_dump does not work with the server, even with the -i option: 8.3: pg_dump: Error message from server: ERROR: column reltriggers does not exist the 8.4 pg_dump works okay, but then the 8.3 version of pg_restore is not able to restore the resultant databases: 8.3: pg_restore: [archiver] input file does not appear to be a valid archive Try the 8.4 pg_restore against the 8.3 server -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How can I interpolate psql variables in function bodies?
Hi dear colleagues, I'm trying to pull some platform-specific constants out of my code by using psql variables, e.g.: $ psql -v TypeLength=4 # CREATE TYPE tref ( INTERNALLENGTH = :TRefTypeLength, INPUT = tref_in, OUTPUT = tref_out, PASSEDBYVALUE ); which works fine, but when I need such a constant in a function it is not substituted. A simplified example: $ psql -v foo=10 # select :foo; ?column? -- 10 (1 row) # create function foo() returns integer as 'select '(:foo) language sql; ERROR: syntax error at or near ( at character 51 I'm sure that I could do something horrible by using EXECUTE inside of a plpgsql function, and I'm hoping that someone will have a simpler alternative. For example, is there some kind of quoting mechanism I can use which will not impede psql from doing substitutions? Thanks, _Greg -- 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] Amazon EC2 | Any recent developments
On Mon, Jun 15, 2009 at 12:11:54PM -0700, Just Someone wrote: - Hi, - - I have more than a few Postgres instances on EC2. For reliability I - use EBS, and take regular snapshots while also streaming the WAL files - to S3. So far, the few times that my machine died, I had no issue with - getting it back from EBS or the EBS volume. I also take tar backups - every day, and I keep a few days back of tar, snapshots and WAL log - files. Your machine died? Was it the cloud's fault or something else? Dave -- 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] DB Migration 8.4 - 8.3
Eoghan Murray eoghanomur...@gmail.com writes: How can I migrate a database from 8.4 to 8.3? The only way is to dump to text (no -Fc or -Ft) with 8.4's pg_dump, and then manually edit the file until 8.3 will take it. 8.3: pg_restore: [archiver] input file does not appear to be a valid archive It's possible here that your only mistake was using -Fc. That format isn't necessarily backwards-compatible. 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] DB Migration 8.4 - 8.3
2009/6/15 Glyn Astill gl...@yahoo.co.uk: Try the 8.4 pg_restore against the 8.3 server Thanks!, that worked! FWIW I set up an ssh connection with the reverse/remote forwarding option -R Also the error message on pg_restore should have read: 8.3: pg_restore: [archiver] unsupported version (1.11) in file header -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pl/sql resources for pl/pgsql?
I'm fairly new to PostgreSQL and completely new to using pl/pgsql though I've used MySQL's procedural language a little. I heard pl/pgsql is similar to Oracle's pl/sql so would it be possible, given that pl/pgsql literature is a bit thin on the ground, to use books on pl/sql for developing pl/pgsql code? gvim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Perf differences between timestamp and timestamp with timezone
Hi all - I'm considering changing all my timestamp columns to timestamp with timezone columns instead. The reason is I want to use UTC time for everything in the DB and on the web server, and only ever convert to local time on the client itself. I could use a timestamp and just know that the timezone is UTC, but npgsql seems to handle this a lot better (like set the Kind property to UTC, etc) if you have a timestamptz column type. I'm curious if there's any perf drawbacks to doing this, or would the only perf hit be if I were comparing timestamp with timestamptz and doing all sorts of casts and stuff. Thanks! Mike PS - If there's any way to make npgsql just manufacture UTC DateTime objects to begin with, that would be nice too.. Digging through the code I coudn't find an easy way.
Re: [GENERAL] Perf differences between timestamp and timestamp with timezone
Mike Christensen wrote: Hi all - I'm considering changing all my timestamp columns to timestamp with timezone columns instead. The reason is I want to use UTC time for everything in the DB and on the web server, and only ever convert to local time on the client itself. I could use a timestamp and just know that the timezone is UTC, but npgsql seems to handle this a lot better (like set the Kind property to UTC, etc) if you have a timestamptz column type. I'm curious if there's any perf drawbacks to doing this, or would the only perf hit be if I were comparing timestamp with timestamptz and doing all sorts of casts and stuff. Thanks! I don't think there is any measurable difference in the two data types, and timestamptz is usually superior, as you have found. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Amazon EC2 | Any recent developments
Hi, I've seen both - some unknown reason for it to die (mostly related to the underlying hardware having issues). We also see instance failure from time to time with advanced notice. Just like a regular machine dies from time to time, so do cloud instances. I'd say it's bit more common on the cloud, but not by a big margin. I might see it more because I have hundreds of instances running. Bye, Guy. On Mon, Jun 15, 2009 at 3:46 PM, David Kerrd...@mr-paradox.net wrote: On Mon, Jun 15, 2009 at 12:11:54PM -0700, Just Someone wrote: - Hi, - - I have more than a few Postgres instances on EC2. For reliability I - use EBS, and take regular snapshots while also streaming the WAL files - to S3. So far, the few times that my machine died, I had no issue with - getting it back from EBS or the EBS volume. I also take tar backups - every day, and I keep a few days back of tar, snapshots and WAL log - files. Your machine died? Was it the cloud's fault or something else? Dave -- Family management on rails: http://www.famundo.com My development related blog: http://devblog.famundo.com -- 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] Perf differences between timestamp and timestamp with timezone
Awesome! One more followup question.. If I modify an existing table from timestamp to timestamptz, will it use the current system timezone? If so, how can I modify all the rows to convert to UTC time (basically add 8 hrs to everything).. On Mon, Jun 15, 2009 at 6:52 PM, Bruce Momjian br...@momjian.us wrote: Mike Christensen wrote: Hi all - I'm considering changing all my timestamp columns to timestamp with timezone columns instead. The reason is I want to use UTC time for everything in the DB and on the web server, and only ever convert to local time on the client itself. I could use a timestamp and just know that the timezone is UTC, but npgsql seems to handle this a lot better (like set the Kind property to UTC, etc) if you have a timestamptz column type. I'm curious if there's any perf drawbacks to doing this, or would the only perf hit be if I were comparing timestamp with timestamptz and doing all sorts of casts and stuff. Thanks! I don't think there is any measurable difference in the two data types, and timestamptz is usually superior, as you have found. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Re: [GENERAL] Perf differences between timestamp and timestamp with timezone
Mike Christensen wrote: Awesome! One more followup question.. If I modify an existing table from timestamp to timestamptz, will it use the current system timezone? If so, how can I modify all the rows to convert to UTC time (basically add 8 hrs to everything).. I think you just cast it to timestamp with time zone and it works: test= create table test(x timestamp without time zone); CREATE TABLE test= insert into test values (current_timestamp); INSERT 0 1 test= select * from test; x 2009-06-15 22:47:30.608331 (1 row) test= alter table test alter column x type timestamp with time zone; ALTER TABLE test= select * from test; x --- 2009-06-15 22:47:30.608331-04 (1 row) test= \d test Table public.test Column | Type | Modifiers +--+--- x | timestamp with time zone | The beauty of the with time zone data type is the fact it changes as your timezone changes, rather than being a static date/time: test= show timezone; TimeZone US/Eastern (1 row) test= set timezone = 'US/Pacific'; SET test= select * from test; x --- 2009-06-15 19:47:30.608331-07 (1 row) Internally it is now UTC but it changes based on your timezone setting. FYI, we would have liked TIMESTAMP to default to WITH TIMEZONE, but the SQL standard says the default is WITHOUT TIMEZONE. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] pl/sql resources for pl/pgsql?
Hello documentation is very good http://www.postgresql.org/docs/8.3/static/plpgsql.html and some articles: http://www.postgres.cz/index.php/PL/pgSQL_%28en%29 regards Pavel Stehule 2009/6/16 gvimrc gvi...@googlemail.com: I'm fairly new to PostgreSQL and completely new to using pl/pgsql though I've used MySQL's procedural language a little. I heard pl/pgsql is similar to Oracle's pl/sql so would it be possible, given that pl/pgsql literature is a bit thin on the ground, to use books on pl/sql for developing pl/pgsql code? gvim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Custom Fields Database Architecture
Custom fields are a fact of life, and used in many, many business critical applications. EAV sucks, as you mentioned, but that doesn't take away from the requirement to build that kind of system. From the user's perspective: If you design an application for me and I want to add a new data field or a new form, should I have to call you back and pay your exorbitant consulting fees? I would prefer to pay a little bit more at the beginning and be able to add what I want into the framework that was already built. We handled this at one client by actually generating the ddl statements and actually building the table/fields, including relationships (user chooses a related object from a list and that is generated as a foreign key). This was after we threw out their EAV system, which sucked. This can lead to design inefficiencies and not-normalized structure, will will lead to reporting havoc, but it depends on the requirements of the user. Gnanam's problem is exasperated by having multiple customers adding multiple fields that only they can see. I don't know your situation, so this might be off-base for your needs, but I would try a similar approach to what I suggested above. Have base fields in one table, with a customerid, indicating who can see the row, and then create a custom table per client who wants to add fields. The tablename can start with their customerid and can have security rights automatically assigned to it. Problems with this approach that I have seen is when the user adds 10 numeric fields, that should be normalized and then wants to generate an aggregate query from all of them. For most data gathering, this should be fine. Sim David Fetter wrote: On Mon, Jun 15, 2009 at 06:04:25AM -0700, Gnanam wrote: Hi, I'm designing a database schema in which I should allow user to create custom fields at the application level. This is called EAV (Entity-Attribute-Value), and it's a multi-decade-old mistake. Re-think your design. http://archives.postgresql.org/pgsql-general/2008-02/msg00075.php http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/ http://en.wikipedia.org/wiki/Inner-Platform_Effect Cheers, David. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] integer only sposix/regex
I'm trying to search phone numbers for digits. Unfortunately, the validation has been sloppy and the numbers are not just numbers, they also have dashes and probably other stuff. I would like the search to ignore anything but integers WHERE (a.phone1 ~* '.*626.*' OR a.phone2 ~* '.*626.*' OR a.phone2 ~* '.*626.*') -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
