[GENERAL] Partitioning V schema

2013-09-19 Thread Dave Potts
Hi List I am looking for some general advice about the best was of splitting a large data table,I have 2 different choices, partitioning or different schemas. The data table refers to the number of houses that can be include in a city, as such there are large number of records. I am won

Re: [GENERAL] Using ODBC and VBA to pull data from a large object

2013-09-19 Thread Vincent Veyron
Le mardi 17 septembre 2013 à 12:25 -0400, Adam C Falkenberg a écrit : > Sorry about that. Here's the driver information and some code. > Driver Name: PostgreSQL ANSI > Version: 9.02.01.00 > > constr = "Driver={PostgreSQL ANSI}; Server=servername; Port=5432; > Database=databasename; Uid=usern

[GENERAL] the new checksum feature

2013-09-19 Thread Torsten Förtsch
Hi, is it possible to turn on checksums in an existing database? Or do I have to dump/initdb/restore? Thanks, Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Partitioning table with billion row

2013-09-19 Thread nuklea
I have a big table referenced by other tables and need to split the table on several parts. 1. I create table by CREATE TABLE product_part0 () INHERITS (product); 2. Move some rows to product_part0 by INSERT INTO product_part0 SELECT * FROM ONLY product ORDER BY id LIMIT 200; 3. Now I

Re: [GENERAL] the new checksum feature

2013-09-19 Thread Patrick Dung
Hi Torsten, According to Postgresql 9.3 Wiki: http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#Data_Checksums The checksum feature should be enabled during initdb. Regards, Patrick Dung From: Torsten Förtsch To: pgsql-general@postgresql.org S

Re: [GENERAL] Using ODBC and VBA to pull data from a large object

2013-09-19 Thread Adam C Falkenberg
I'm using Excel. I needed to set the MAXVARCHARSIZE parameter in the connection string to take care of my issue (MAXVARCHARSIZE=2048 for me). That allowed the defined size of the field to equal the actual size. Thanks everyone for your help! Adam From: Vincent Veyron To: Adam C Falke

Re: [GENERAL] Partitioning V schema

2013-09-19 Thread Agustin Larreinegabe
If I were you I will use partitioning. In my experience, partitioning is easier and transparent. I just have to set it up and then refers just to one table and done. About speed, if you have the value "constraint_exclusion" = partition, postgres will examine constraints only for inheritance child t

[GENERAL] reading cvs logs with pgadmin queries

2013-09-19 Thread Dave Cramer
pgadmin uses multi line queries and copy from 'cvslogs' cannot read them. Does anyone have a work around or other solution ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca

Re: [GENERAL] Something Weird Going on with VACUUM ANALYZE

2013-09-19 Thread Shaun Thomas
On 09/18/2013 11:50 AM, Jeff Janes wrote: That line is the final line of a multi-line log entry. To know what it is about, you need to look at the lines before it in the logfile. Perhaps it is failing to obtain a lock or something. Thanks for the input, Jeff. The full chunk for that PID (2207

Re: [GENERAL] How to evaluate if a query is correct?

2013-09-19 Thread David Johnston
Juan Daniel Santana Rodés wrote > Hi my friends... > I wrote in the last post a question similiar to this. But in this post I > clarify better the previous question. > I need know how to evaluated if a query is correct without execute it. > When I say if a query is correct, is that if I run the q

Re: [GENERAL] Something Weird Going on with VACUUM ANALYZE

2013-09-19 Thread Kevin Grittner
Shaun Thomas wrote: > The first line also seems extremely misleading. a manually > launched vacuum is not an autovacuum, so why is it complaining > about an autovacuum? Shouldn't the PID doing the autovacuum emit > the log message? This all seems a little sketchy. The table truncation in autovac

Re: [GENERAL] Something Weird Going on with VACUUM ANALYZE

2013-09-19 Thread Shaun Thomas
On 09/19/2013 08:42 AM, Kevin Grittner wrote: The fix accidentally introduced some messages which are confusing, and caused the statistics from autovacuum to fail to be generated more often than had previously been the case. These problems should be fixed in the next minor release. Until then,

Re: [GENERAL] reading cvs logs with pgadmin queries

2013-09-19 Thread Adrian Klaver
On 09/19/2013 05:47 AM, Dave Cramer wrote: pgadmin uses multi line queries and copy from 'cvslogs' cannot read them. Does anyone have a work around or other solution ? It worked here for me: test=# INSERT INTO big_int_test VALUES (6.7); COPY postgres_log FROM '/usr/local/pgsql/da

Re: [GENERAL] How to evaluate if a query is correct?

2013-09-19 Thread Rob Sargentg
On 09/19/2013 12:13 AM, Juan Daniel Santana Rodés wrote: Hi my friends... I wrote in the last post a question similiar to this. But in this post I clarify better the previous question. I need know how to evaluated if a query is correct without execute it. When I say if a query is correct, is th

Re: [GENERAL] How to evaluate if a query is correct?

2013-09-19 Thread David Johnston
lup wrote > On 09/19/2013 12:13 AM, Juan Daniel Santana Rodés wrote: >> Hi my friends... >> I wrote in the last post a question similiar to this. But in this post >> I clarify better the previous question. >> I need know how to evaluated if a query is correct without execute it. >> When I say if

Re: [GENERAL] How to evaluate if a query is correct?

2013-09-19 Thread Pavel Stehule
Hello you can use a PREPARE statement. Pavel 2013/9/19 Rob Sargentg > On 09/19/2013 12:13 AM, Juan Daniel Santana Rodés wrote: > >> Hi my friends... >> I wrote in the last post a question similiar to this. But in this post I >> clarify better the previous question. >> I need know how to evalu

[GENERAL] How to failover from Primary to Standby and Set the old Primary as a new Standby

2013-09-19 Thread ascot.m...@gmail.com
Hi, I use PG 9.2.4 with streaming replication. What will be the manual procedure to failover from Primary to Standby and Set the old Primary as a new standby? step 1: standby's recovery.conf : # Specifies a trigger file whose presence should cause streaming replication to end (i.e., failover

[GENERAL] Upgrading from postgreSQL 9.1.6 to 9.3

2013-09-19 Thread fburgess
Are there any showstoppers/recommendations/experiences with upgrading from Postgres 9.1.6 Postgis 1.5.3 to PostGres 9.3 and associated PostGIS version XX on rhel 6.4 that will help the process go smoothly.thanks

Re: [GENERAL] reading cvs logs with pgadmin queries

2013-09-19 Thread Dave Cramer
The errors are extra lines after with a query like this 2013-09-12 10:33:19.145 WST,"user","dbname",14581,"192.168.1.22:58840",523126d3.38f5,1,"SELECT",2013-09-12 10:28:35 WST,6/503023,0,ERROR,42P01,"relation ""dds_stores"" does not exist",,"WITH RECURSIVE recursive_stores(id, name) AS (

Re: [GENERAL] Unary Operators

2013-09-19 Thread Andreas Ulbrich
On 18.09.2013 02:17, David Johnston wrote: Andreas Ulbrich wrote create operator ^- (leftarg = float, procedure = reciproce); works too, but create operator / (leftarg = float, procedure = reciproce); not. Do you mean the "^" operator or the "^-" operator? Rowan claims that "^" does not in fac

Re: [GENERAL] Upgrading from postgreSQL 9.1.6 to 9.3

2013-09-19 Thread Paul Ramsey
Recommendation: do one at a time. First postgis 1.5 to 2.1, then pg 9.1 to 9.3. P. -- Paul Ramsey http://cleverelephant.ca http://postgis.net On Thursday, September 19, 2013 at 4:34 PM, fburg...@radiantblue.com wrote: > > > Are there any showstoppers/recommendations/experiences with upgradi

Re: [GENERAL] How to failover from Primary to Standby and Set the old Primary as a new Standby

2013-09-19 Thread Vick Khera
On Thu, Sep 19, 2013 at 11:31 AM, ascot.m...@gmail.com wrote: > I use PG 9.2.4 with streaming replication. What will be the manual > procedure to failover from Primary to Standby and Set the old Primary as a > new standby? > >From what I understand, you start over by setting up the old primary

Re: [GENERAL] Upgrading from postgreSQL 9.1.6 to 9.3

2013-09-19 Thread fburgess
1.) We have to upgrade four 9.1 database instances, so is using pg_upgrade still the way to go? Our prior upgrade methodology when we moved from pg 8.4.3 to 9.1.6 was to use the hard links install option. We also have our data spread across storage mediums; fiber, nas. Do these things factor in?2.)

Re: [GENERAL] How to failover from Primary to Standby and Set the old Primary as a new Standby

2013-09-19 Thread John R Pierce
On 9/19/2013 1:29 PM, Vick Khera wrote: On Thu, Sep 19, 2013 at 11:31 AM, ascot.m...@gmail.com > wrote: I use PG 9.2.4 with streaming replication. What will be the manual procedure to failover from Primary to Standby and Set

Re: [GENERAL] How to failover from Primary to Standby and Set the old Primary as a new Standby

2013-09-19 Thread Michael Nolan
Assuming the database hasn't changed much since the failover, doing a fsync from the new primary back to the old primary should be fairly quick. -- Mike Nolan On 9/19/13, Vick Khera wrote: > On Thu, Sep 19, 2013 at 11:31 AM, ascot.m...@gmail.com > > wrote: > >> I use PG 9.2.4 with streaming repli

Re: [GENERAL] How to failover from Primary to Standby and Set the old Primary as a new Standby

2013-09-19 Thread Michael Nolan
On 9/19/13, John R Pierce wrote: > On 9/19/2013 1:29 PM, Vick Khera wrote: >> >> On Thu, Sep 19, 2013 at 11:31 AM, ascot.m...@gmail.com >> > > wrote: >> >> I use PG 9.2.4 with streaming replication. What will be the >> manual proc

Re: [GENERAL] reading cvs logs with pgadmin queries

2013-09-19 Thread Adrian Klaver
On 09/19/2013 08:26 AM, Dave Cramer wrote: The errors are extra lines after with a query like this 2013-09-12 10:33:19.145 WST,"user","dbname",14581,"192.168.1.22:58840 ",523126d3.38f5,1,"SELECT",2013-09-12 10:28:35 WST,6/503023,0,ERROR,42P01,"relation ""dds_sto

Re: [GENERAL] reading cvs logs with pgadmin queries

2013-09-19 Thread Dave Cramer
Adrian, 1) yes I am using the example table CREATE TABLE postgres_log ( log_time timestamp(3) with time zone, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp wi

Re: [GENERAL] Why does this array query fail?

2013-09-19 Thread Ken Tanzer
> > Is this a quarterly report because that is how long it takes to run? It takes about 7 seconds to run. I suppose if I optimized it I could save a minute every couple of years. I usually get concerned about performance issues when they're actually causing problems. I'm generally more concern

Re: [GENERAL] Why does this array query fail?

2013-09-19 Thread David Johnston
Ken Tanzer wrote >> >> Is this a quarterly report because that is how long it takes to run? > > > It takes about 7 seconds to run. I suppose if I optimized it I could save > a minute every couple of years. Was meant to be tongue-in-cheek...though I was curious on the real answer...agree on opti

Re: [GENERAL] Partitioning V schema

2013-09-19 Thread Julian
Hi Dave, How many rows of data are we talking here and how much information? (GiB) Are you able to provide the table definition? (can normalisation partition off some of this data?). Have you addressed dedicated options for lookup data, tune the database appropriately and keep that single table?

Re: [GENERAL] reading cvs logs with pgadmin queries

2013-09-19 Thread Adrian Klaver
On 09/19/2013 06:04 PM, Dave Cramer wrote: Adrian, 1) yes I am using the example table 2) The exact error message is ERROR: extra data after last expected column considering ~39000 lines go in before this line I am fairly certain it is the line. I would tend to agree, I just can't se