Re: [GENERAL] Queries seldomly take >4s while normally take <1ms?

2013-04-09 Thread Greg Williamson
Christian -- postgres version ? type of replication ? changes from postgres config defaults ? Do they happen more at peak usage, semi regularly or sporadically ? Possibly some sporadic postgres process such as checkpoints of autovac processes kicking off. Do your logs show anything ? HTH,

Re: [GENERAL] ERROR: syntax error at or near ":"

2013-03-06 Thread Greg Williamson
Thanks for the link / explanation -- hadn't seen this use before. GW - Original Message - > From: Adrian Klaver > To: Greg Williamson > Cc: "pgsql-general@postgresql.org" > Sent: Wednesday, March 6, 2013 3:13 PM > Subject: Re: [GENERAL] ERROR: synta

Re: [GENERAL] ERROR: syntax error at or near ":"

2013-03-06 Thread Greg Williamson
Graham -- > > From: Graham Leggett >To: "pgsql-general@postgresql.org" >Sent: Wednesday, March 6, 2013 2:41 PM >Subject: [GENERAL] ERROR:  syntax error at or near ":" > >Hi all, > >I have a text file, and I need to update the value of an element in a table >w

Re: [GENERAL] Database schema

2012-12-04 Thread Greg Williamson
in that one, and then create your new databases using the "WITH TEMPLATE = " option in CREATE DATABASE. So if you have need of different schemas with different databases, that would be more extensible by making new templates, onjhe3 for each type./. HTH, Greg Williamson -- Sent via p

Re: [GENERAL] Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)

2012-11-12 Thread Greg Williamson
e tanks; when the site has issues we sometimes see table bloat but it seems to be dealt with by autovac. This is a postgres 9.1 instance w/ SSDs, lots of RAM (24 gigs) and relatively small tables (maybe a few thousands of rows in most cases, total footprint of the database is 240 megs) and being

Re: [GENERAL] Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum)

2012-11-09 Thread Greg Williamson
ase cost limit.  If you get >to something in the 5000 to 1 range, and its still not keeping up >then start bumping the thread count > Thanks for outlining a strategy on this -- useful advice. Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

Re: [GENERAL] dropdb breaks replication?

2012-10-31 Thread Greg Williamson
Edson -- >I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. >They are replicated asynchronously. > >Yesterday, I've dropped a database of 20Gb, and then replication has broken, >requiring me to manually synchronize both servers again. > >It is expected that dropdb (or, perhaps, created

Re: [GENERAL] PostgreSQL and WMS/WFS Service

2012-10-09 Thread Greg Williamson
You might look at the GIS extension, PostGIS: <http://postgis.refractions.net/> Not sure how much yu need to do, but a company I used to work for ran a WMS service off of an earlier version of postGIS. Buena Suerte! Greg Williamson > > From: José

Re: [GENERAL] PGBouncer Connection Using Perl DBI

2012-08-27 Thread Greg Williamson
Prashant -- I haven't tried this -- we don't use DBI currently -- but wouldn't it get treated as any other connection if you point the DBI connection to the pgbouncer host / port / database ? pgbouncer should redirect the query according the rules in its .ini file. HTH,

Re: [GENERAL] Run external SQL file via Perl dbh

2012-06-11 Thread Greg Williamson
Toby -- A small point: <...> >How about something like > >use File::Slurp; >use Try::Tiny; > >try { >  $dbh->being_work; $dbh-begin_work; <...> Clarification for any who might be unfamiliar with the perl tools. Greg Williamson -- Sent via pgsql

Re: [GENERAL] Populate Table From Two Other Tables

2012-06-05 Thread Greg Williamson
ERROR:  operator does not exist: character varying == character varying > >and provides the hint to add explicit type casts. Since the string length of >site/siteid varies I don't see how to cast both to a working type. > >  Please suggest how I can populate this table while av

Re: [GENERAL] Does Postgres compress data?

2012-05-23 Thread Greg Williamson
It may vary from version of postgres to version, but perhaps you are seeing the effects of TOAST kicking in ? Do a search in the documentation for your specific version (8.3, 9.1 etc.) HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] trimming a column

2012-05-03 Thread Greg Williamson
The trim function needs to be told what sort of trim to do -- Following the 9.1 manual (you did not specify which version of postgres you are using) try:   UPDATE mytable SET id = trim(both ' ' from id). (untested) See <http://www.postgresql.org/docs/9.1/static/functions-s

Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

2012-03-16 Thread Greg Williamson
> >Of course events destined to this table will be queued by Slony while the >table is locked. > I've not much recent experience with Slony, but possibly pg_reorg, found at:   <http://pgfoundry.org/projects/reorg/> would be of use ... Greg Williamson -- Sent vi

Re: [GENERAL] version controlling postgresql code

2012-02-28 Thread Greg Williamson
runtime. I've had unpleasant experiences with commercial tools; CVS works well for simple environments and git can be used for more tangled development work. HTH, Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.

Re: [GENERAL] Measuring replication lag time

2012-02-22 Thread Greg Williamson
Stuart Bishop shaped the aether to ask: > Hi. > > I need to measure how far in the past a hot standby is, async > streaming replication. > Not sure if this will help, but we are using repmgr ; it sets up a monitoring schema which we poll )see the "M

Re: [GENERAL] pg_dump schma while excluding specific table

2011-11-03 Thread Greg Williamson
-v product_feed_data_ COMMENT ON TABLE product_key IS 'A temporary table used to sync product_feed_data.does_exist_in_product.  ... HTH, Greg Williamson -- 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] Add quto increment to existing column

2011-10-06 Thread Greg Williamson
hist_id_seq'); ?? (untested) You may need to to update the sequence to reflect your current highest value for hist_id (or whatever the column is -- set the sequence using select setval('hist_id_seq', maxid)  so that the next value you get from the sequence doesn't collide w

Re: [GENERAL] SPAM Alert !

2011-08-25 Thread Greg Williamson
This is clearly spam and I would suggest that the sender be blacklisted for sending it. Sure, they might be a victim too, but one whose mail system has been compromised. > >From: Chris Barnes >To: chr...@bitheads.com; lindsay.d...@btconnect.com; s.li...@sympa

Re: [GENERAL] Using Postgresql as application server

2011-08-13 Thread Greg Williamson
> >Dear Postgres users, How about sending these to just one mailing list -- when you cross post everybody gets two copies of each response. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-gen

Re: [GENERAL] Backup & Restore a database in PostgreSQL

2011-08-08 Thread Greg Williamson
Siva -- > > Thanks a lot for your reply. As usual Backup worked perfectly. When I tried > restore using the command you provided, I got the below list of errors! > Please help me > out on this. > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error fro

Re: [GENERAL] I need your help to get opinions about this situation

2011-03-03 Thread Greg Williamson
pgPool to get the same number of connections. This was actually a migration -- from Sun Solaris to Linux so comparing the two directly wasn't easy. We moved "chunks" on the application and tested a lot; spatial data first and the bookkeeping and accounting functions and finally th

Re: [GENERAL] Importing/Appending to Existing Table

2011-02-02 Thread Greg Williamson
It will not overwrite any existing data; if the table has constraints that will prevent duplicates then the entire load will fail if any item fails (it is a single transaction). If the table doesn't have such constraints then duplicate data in the copy file will result in duplicate rows.

Re: [GENERAL] Comparison of Oracle and PostgreSQL full text search

2010-07-28 Thread Greg Williamson
s -- very much oriented at postGIS found Oracle to be between 5 and 15% _faster_ depending on the specifics of the task. We decided to go with postgres given the price difference (several hundred thousand dollars for Oracle in the configuration we needed vs. zip for postgres -- we already had trained

Re: [GENERAL] How to auto-increment?

2009-12-02 Thread Greg Williamson
See the data type "SERIAL" in the PostgreSQL manual for whatever flavor of the database you are using ... Apologies for top-posting -- challenged mail client. HTH, Greg W. From: Andre Lopes To: pgsql-general@postgresql.org Sent: Wed, December 2, 2009 2:52:5

Re: [GENERAL] Server Backup: pg_dump vs pg_dumpall

2009-07-20 Thread Greg Williamson
ser defined stuff, views etc. are all in a given database so they will be in the pg_dump. HTH, Greg Williamson -- 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] Is there a meaningful benchmark?

2009-03-19 Thread Greg Williamson
But when you consider the speed with which Oracle produces patches vs. the Postgres folks the winner is clearly the latter. Greg Williamson -- 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] High cpu usage after many inserts

2009-02-24 Thread Greg Williamson
stance <http://www.miracleas.com/BAARF/RAID5_versus_RAID10.txt>. HTH, Greg Williamson -- 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] What makes a Postgres DBA?

2007-11-04 Thread Greg Williamson
d issues * backups and safety nets I particularly like your last points, Merlin, on developers. I've been on both sides of the fence and databases are a specialized animal that usually needs specialized care. Being able to work well with designers and developers is crucial in deliverin

Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

2007-09-18 Thread Greg Williamson
Richard Broersma Jr wrote: --- Gregory Williamson <[EMAIL PROTECTED]> wrote: A very low fill factor means that pages are "sparse" and so inserts and updates are less likely to trigger massive b-tree rebalancings. I take it that "massive b-tree rebalancings" could cause a problem with

Re: [GENERAL] Transactional DDL

2007-08-15 Thread Greg Williamson
yes QED. It's a strong point in PostgreSQL's favor that it behaves in what I regard as a sane manner. That Oracle stuff makes me shudder -- it's unclean. Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, incl