Re: [GENERAL] Miidpoint between two long/lat points? (earthdistance?)

On Wed, May 25, 2011 at 12:47 PM, Carlo Stonebanks wrote: > I need to calculate the long/lat values between a line demarcated by two > long/lat points. > > > > The points will be very close, but there is the 180 degree problem to > consider, so a simple average won’t work. > > > > Does anyone know

[GENERAL] OHI to Sponsor PG West 2011

May 24, 2011 Open Hosting to Sponsor PG West 2011 Conference PRESS RELEASE – Open Hosting Inc., Vienna, VA: Open Hosting Inc. (“OHI”) announces the sponsorship of PG West 2011. The PostgreSQL Conference West will be held on September 27-30th at the San Jose (CA) Convention Center. PostgreSQL, w

Re: [GENERAL] temp files getting me down

On May 25, 2011, at 7:36 PM, Merlin Moncure wrote: > On Wed, May 25, 2011 at 6:44 PM, Ben Chobot wrote: >> >> >> Well, the query itself was calling a plpgsql function, and the function >> itself was doing: >> >> DECLARE >>row formatted_replication_queue%ROWTYPE; >> BEGIN >>fo

Re: [GENERAL] max_connections proposal

On Wed, May 25, 2011 at 10:58 PM, Craig Ringer wrote: > There might be a very cheap and simple way to help reduce the number of > people running into problems because they set massive max_connections values > that their server cannot cope with instead of using pooling. > > In the default postgresq

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

On Wed, May 25, 2011 at 2:40 PM, Thom Brown wrote: > On 25 May 2011 18:58, Alexander Farber wrote: >> >> Hello fellow PostgreSQL-users, >> >> I run a Drupal 7 (+Facebook app) website >> with a multiplayer flash game and use >> postgresql-server-8.4.8-1PGDG.rhel5 + >> CentOS 5.6 64 bit on a Quad-C

[GENERAL] max_connections proposal

There might be a very cheap and simple way to help reduce the number of people running into problems because they set massive max_connections values that their server cannot cope with instead of using pooling. In the default postgresql.conf, change: max_connections = 100 # (c

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

On 05/26/2011 02:53 AM, t...@fuzzy.cz wrote: Decrease the max_connections, use connection pooling if possible (e.g. pgbouncer). Each connection represents a separate postgres process, so you may get up to 512 processes. And that many active processes kills the performance. ... and this is why

Re: [GENERAL] temp files getting me down

On Wed, May 25, 2011 at 6:44 PM, Ben Chobot wrote: > On May 25, 2011, at 2:57 PM, Merlin Moncure wrote: > >> On Wed, May 25, 2011 at 2:14 PM, Ben Chobot wrote: >>> On May 25, 2011, at 9:31 AM, Ben Chobot wrote: >>> I'm running 9.0.3, and recently started getting temp files being created. >>

Re: [GENERAL] Connecting to Postgres using Windows 7

On 05/25/2011 09:49 PM, Geoffrey Becker wrote: When I try to connect, all I get is a "Server does not exist or access is denied" error. I've tried configuring ODBC using odbcad32.exe as it seems that is necessary on a 64 bit OS, but I'm not sure if that even the right way to go. *which* odbcad

Re: [GENERAL] General Postgres performance tips when using ARRAY

On Wed, May 25, 2011 at 9:17 PM, bubba postgres wrote: > > So, what are the gotcha's around manipulating Arrays in stored procs? > It seems reasonable that an array_cat /etc would cause the creation of a new > array, but does mutating an existing array also create a copy? Never, ever, if at all p

[GENERAL] General Postgres performance tips when using ARRAY

So, what are the gotcha's around manipulating Arrays in stored procs? It seems reasonable that an array_cat /etc would cause the creation of a new array, but does mutating an existing array also create a copy?

Re: [GENERAL] Access to postgres conversion

Hi. You could try /Access to PostgreSQL /from Bullzip.com. I liked the dump file that this creates. Small neat and it is free! Tommy. It's 10 tables. that's all. No reports. I will follow your suggestion. Thanks for the help On Wed, May 25, 2011 at 4:25 PM, John R Pierce

Re: [GENERAL] "full_page_writes" makes no difference?

On Wed, 2011-05-04 at 00:17 -0400, Tian Luo wrote: > So, "nbytes" should always be multiples of XLOG_BLCKSZ, which in the > default case, is 8192. > > My question is, if it always writes full pages no matter > "full_page_writes" is on or off, what is the difference? Most I/O systems and filesyste

Re: [GENERAL] temp files getting me down

On May 25, 2011, at 2:57 PM, Merlin Moncure wrote: > On Wed, May 25, 2011 at 2:14 PM, Ben Chobot wrote: >> On May 25, 2011, at 9:31 AM, Ben Chobot wrote: >> >>> I'm running 9.0.3, and recently started getting temp files being created. >>> This is a problem because it's making a bunch of dirty b

Re: [GENERAL] temp files getting me down

On Wed, May 25, 2011 at 2:14 PM, Ben Chobot wrote: > On May 25, 2011, at 9:31 AM, Ben Chobot wrote: > >> I'm running 9.0.3, and recently started getting temp files being created. >> This is a problem because it's making a bunch of dirty buffers that have to >> be flushed to disk and my poor litt

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

Dne 25.5.2011 21:54, Alexander Farber napsal(a): > Thank you for your replies, > > I've reverted httpd.conf to > > StartServers 8 > MinSpareServers5 > MaxSpareServers 20 > ServerLimit 256 > MaxClients 256 > > and have changed postgresql.conf to: > > shared_buf

Re: [GENERAL] Access to postgres conversion

It's 10 tables. that's all. No reports. I will follow your suggestion. Thanks for the help On Wed, May 25, 2011 at 4:25 PM, John R Pierce wrote: > On 05/25/11 12:42 PM, akp geek wrote: > >> Dear all - >> >>I would like to know if any one has migrated database from MS >> access to Po

Re: [GENERAL] Access to postgres conversion

On 05/25/11 12:42 PM, akp geek wrote: Dear all - I would like to know if any one has migrated database from MS access to Postgres . We use postgres 9.0.2 on solaris . Are there any open source tools that you have used to do this task. Can you please share your experiences ? how

Re: [GENERAL] Dumping schemas using pg_dump without extensions (9.1 Beta)

Adrian Schreyer writes: > I am trying to backup a single schema only, without any other database > objects such as extensions. pg_dump however always includes > extensions, even with the --schema=schema option specified (see below > for example). Is there a workaround for this? I've applied a pat

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

# sysctl kernel.shmmax kernel.shmmax = 68719476736 # sysctl kernel.shmall kernel.shmall = 4294967296 On Wed, May 25, 2011 at 9:54 PM, Alexander Farber wrote: >  shared_buffers = 512MB > > Do you think I need to reconfigure CentOS 5.6 > for the bigger shared memory too or > will it adapt by itsel

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

Thank you for your replies, I've reverted httpd.conf to StartServers 8 MinSpareServers5 MaxSpareServers 20 ServerLimit 256 MaxClients 256 and have changed postgresql.conf to: shared_buffers = 512MB # for Apache + my game daemon + cron jobs max_connections

[GENERAL] Access to postgres conversion

Dear all - I would like to know if any one has migrated database from MS access to Postgres . We use postgres 9.0.2 on solaris . Are there any open source tools that you have used to do this task. Can you please share your experiences ? Regards

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

On 05/25/2011 10:58 AM, Alexander Farber wrote: Hello fellow PostgreSQL-users, I run a Drupal 7 (+Facebook app) website with a multiplayer flash game and use postgresql-server-8.4.8-1PGDG.rhel5 + CentOS 5.6 64 bit on a Quad-Core/4GB machine. I generally like using PostgreSQL eventhough I'm not

Re: [GENERAL] temp files getting me down

On May 25, 2011, at 9:31 AM, Ben Chobot wrote: > I'm running 9.0.3, and recently started getting temp files being created. > This is a problem because it's making a bunch of dirty buffers that have to > be flushed to disk and my poor little disk isn't up to the task. I'm not sure > why though,

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

> Hello fellow PostgreSQL-users, > > I run a Drupal 7 (+Facebook app) website > with a multiplayer flash game and use > postgresql-server-8.4.8-1PGDG.rhel5 + > CentOS 5.6 64 bit on a Quad-Core/4GB machine. > > I generally like using PostgreSQL eventhough > I'm not an experienced DB-user, but in the

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

On 25 May 2011 18:58, Alexander Farber wrote: > Hello fellow PostgreSQL-users, > > I run a Drupal 7 (+Facebook app) website > with a multiplayer flash game and use > postgresql-server-8.4.8-1PGDG.rhel5 + > CentOS 5.6 64 bit on a Quad-Core/4GB machine. > > I generally like using PostgreSQL eventho

Re: [GENERAL] Preventing OOM kills

On 05/25/2011 03:01 AM, John R Pierce wrote: On 05/24/11 5:50 PM, Andrej wrote: Add more RAM? Look at tunables for other processes on the machine? At the end of the day making the kernel shoot anything out of despair shouldn't be the done thing. somehow, 'real' unix has neither a OOMkiller nor

[GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

Hello fellow PostgreSQL-users, I run a Drupal 7 (+Facebook app) website with a multiplayer flash game and use postgresql-server-8.4.8-1PGDG.rhel5 + CentOS 5.6 64 bit on a Quad-Core/4GB machine. I generally like using PostgreSQL eventhough I'm not an experienced DB-user, but in the recent weeks it

[GENERAL] Connecting to Postgres using Windows 7

I'm relatively new to postgres. I've got a Visual Basic (VB) application that i would like to connect to a Postgres database using ODBC . Both the VB application and postgres are on my laptop and both work beautifully independent of each other. Trouble is, I have a windows 7 64bit OS and the

Re: [GENERAL] Miidpoint between two long/lat points? (earthdistance?)

On Wed, May 25, 2011 at 9:47 AM, Carlo Stonebanks < stonec.regis...@sympatico.ca> wrote: > I need to calculate the long/lat values between a line demarcated by two > long/lat points. > > > > The points will be very close, but there is the 180 degree problem to > consider, so a simple average won’

[GENERAL] Miidpoint between two long/lat points? (earthdistance?)

I need to calculate the long/lat values between a line demarcated by two long/lat points. The points will be very close, but there is the 180 degree problem to consider, so a simple average won't work. Does anyone know of a function or have a formula that will work using geo long/lat values

Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs

Leif Jensen wrote: > > Is it really not possible to use 2 separate connection within 1 thread > at the same time ? or is it an error in the ecpg library ? It should be entirely possible to run multiple connections in a single thread as long as you manage the 'AT connName' clauses properly. Thoug

[GENERAL] temp files getting me down

I'm running 9.0.3, and recently started getting temp files being created. This is a problem because it's making a bunch of dirty buffers that have to be flushed to disk and my poor little disk isn't up to the task. I'm not sure why though, because this is the explain verbose for the queries that

Re: [GENERAL] How to store and load images in PostgreSQL db?

It may be as simple as making sure that: bytea_output = escape is present in postgresql.conf 9.0 changed the default to hex No idea on the Table/Query aspect but at least in JDBC query parameters are generic (i.e., "?") and you tell the API what type of value is being sent - letting the API do

Re: [GENERAL] No control over max.num. WAL files

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/25/2011 02:47 PM, Andrew Sullivan wrote: > On Wed, May 25, 2011 at 01:37:47PM +0100, Simon Riggs wrote: >> >> That's the way SQLServer and Oracle work, but not PostgreSQL. We can >> clear down WAL files even during a long running transaction. >>

Re: [GENERAL] No control over max.num. WAL files

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/25/2011 02:55 PM, Craig Ringer wrote: >> These are the relevant parameters we have changed in postgresql.conf: >> >> archive_mode | off >> checkpoint_segments | 128 >> default_statistics_target | 100 >> maintenance_work

Re: [GENERAL] No control over max.num. WAL files

These are the relevant parameters we have changed in postgresql.conf: archive_mode | off checkpoint_segments | 128 default_statistics_target | 100 maintenance_work_mem | 512MB max_fsm_pages | 80 max_fsm_relations | 8000 shared_buffers

Re: [GENERAL] No control over max.num. WAL files

On Wed, May 25, 2011 at 6:47 AM, Andrew Sullivan wrote: > On Wed, May 25, 2011 at 01:37:47PM +0100, Simon Riggs wrote: >> >> That's the way SQLServer and Oracle work, but not PostgreSQL. We can >> clear down WAL files even during a long running transaction. >> >> For us, "unneeded" means prior to

Re: [GENERAL] No control over max.num. WAL files

On Wed, May 25, 2011 at 01:37:47PM +0100, Simon Riggs wrote: > > That's the way SQLServer and Oracle work, but not PostgreSQL. We can > clear down WAL files even during a long running transaction. > > For us, "unneeded" means prior to the second-to-last checkpoint record. Well, they're obviously

Re: [GENERAL] No control over max.num. WAL files

On Wed, May 25, 2011 at 6:37 AM, Simon Riggs wrote: > On Wed, May 25, 2011 at 1:08 PM, Andrew Sullivan wrote: > >> Note that "unneeded".  Obviously, you need more than that, probably >> because you're restoring the database in one transaction (so none of >> the files can be flushed). > > That's t

Re: [GENERAL] No control over max.num. WAL files

On Wed, May 25, 2011 at 1:08 PM, Andrew Sullivan wrote: > Note that "unneeded".  Obviously, you need more than that, probably > because you're restoring the database in one transaction (so none of > the files can be flushed). That's the way SQLServer and Oracle work, but not PostgreSQL. We can c

Re: [GENERAL] No control over max.num. WAL files

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/25/2011 02:08 PM, Andrew Sullivan wrote: > On Wed, May 25, 2011 at 01:30:35PM +0200, Rafael Martinez wrote: Thanks for your answer. >> >> According to the documentation [1] we can expect a maximum of >> (3 * checkpoint_segments + 1 segment file

Re: [GENERAL] No control over max.num. WAL files

On Wed, May 25, 2011 at 01:30:35PM +0200, Rafael Martinez wrote: > I am using 'pg_dumpall | psql' in the process and everything works ok > until our pg_xlog partition gets full. > > According to the documentation [1] we can expect a maximum of > (3 * checkpoint_segments + 1 segment files) WAL file

Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs

Hello, Thank you for the suggestion, seems the way to go. I have implemented this using both variable descriptor and prepared statement (execquery) in my program and it works nicely, except in one specific situation. What I didn't mention previously is that we are sometimes using 2 co

[GENERAL] No control over max.num. WAL files

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello I am trying to move a postgres cluster with 90 databases and around 140GB of data between two servers (8.3.12 -> 8.3.15). I am using 'pg_dumpall | psql' in the process and everything works ok until our pg_xlog partition gets full. According to

[GENERAL] How to store and load images in PostgreSQL db?

Hello, I recently started using PostgresDAC 9.0 and had no trouble getting things to work until I started to try to find out if it is possible to store (and load) images in the Postgresql db from Delphi 7. I read about it and in my opinion it is the best option to use a Bytea field. Now I am stru