Re: [GENERAL] pg_dump compress

2011/9/23 Gregg Jaskiewicz > can you pipe things on windows ? > Yes you can. It surprised me positively several years ago. http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/redirection.mspx?mfr=true > It's a desktop system after all, :-) what a nice dose of condesc

Re: [GENERAL] pg_dump compress

On Friday, September 23, 2011 7:26:19 am Roger Niederland wrote: > On 9/23/2011 6:46 AM, hubert depesz lubaczewski wrote: > > On Thu, Sep 22, 2011 at 11:00:10PM -0700, Roger Niederland wrote: > >> Using pg_dump from the command line with the exe included in windows > >> 32 bit install in postgresql

Re: [GENERAL] /usr/local/pgsql/data permissions

On Fri, 23 Sep 2011, John R Pierce wrote: did the system run some sort of fsck autorepair when it restarted? thats about the only thing I could think of that might have messed with the permissions. The file system is ext3 so it did restore from the journals. Anyway, now I know if I shou

Re: [GENERAL] /usr/local/pgsql/data permissions

On 09/23/11 2:14 PM, Rich Shepard wrote: When it started and worked I assumed that was the case. But, I've not before had directory permissions change when a system crashed. Cue the "Twilight Zone" theme. did the system run some sort of fsck autorepair when it restarted? thats about th

Re: [GENERAL] /usr/local/pgsql/data permissions

On Fri, 23 Sep 2011, John R Pierce wrote: Indeed, 700 are the correct privs. John, When it started and worked I assumed that was the case. But, I've not before had directory permissions change when a system crashed. Cue the "Twilight Zone" theme. Thanks, Rich -- Sent via pgsql-general

Re: [GENERAL] /usr/local/pgsql/data permissions

On 09/23/11 1:48 PM, Rich Shepard wrote: My server just crashed because a CPU-intensive build threatened to overheat the processor so the system shut down. When I rebooted and tried to start postgres the attempt failed because `data directory "/usr/local/pgsql/data" has group or world access'

[GENERAL] /usr/local/pgsql/data permissions

My server just crashed because a CPU-intensive build threatened to overheat the processor so the system shut down. When I rebooted and tried to start postgres the attempt failed because `data directory "/usr/local/pgsql/data" has group or world access'. As far as I can recall, it's always been 7

Re: [GENERAL] how to install 9.1 on non-graphical server?

On Thu, 2011-09-22 at 22:11 -0300, Dickson S. Guedes wrote: > 2011/9/22 Andreas : > > Hi, > > is there a way to install the EnterpriseDB V9.1 release on a server without > > X-Windows? > > I've got an OpenSuse 11.4 and the have only V9.0.4 in their repository so > > I'd need the binary of Enterpris

Re: [GENERAL] Speed of lo_unlink vs. DELETE on BYTEA

Reuven M. Lerner wrote: > 1.1 1,000 records > == > > DeleteDump > ---+-+ > Empty content 0.172s0.057s > bytea 0.488s0.066s > large object30.833s 9.275s How much bytea are you dum

Re: [GENERAL] Materialized views in Oracle

On Fri, Sep 23, 2011 at 3:03 AM, Mike Christensen wrote: > 1) Though I might have given the impression that a “manual complete > refresh” is not useful, I definitely see value in this especially for > data warehousing scenarios.  However, I’d almost want to call this a > “snapshot” and not a “mate

Re: [GENERAL] looking for a faster way to do that

explain analyze select num, n.wantcode from items, n where items.code ~ n.wantcode; Nested Loop (cost=20.00..216502.14 rows=48070 width=36) (actual time=148.479..336280.488 rows=2871 loops=1) Join Filter: (("outer".code)::text ~ "inner".wantcode) -> Seq Scan on items (cost=0.00..167.14 row

Re: [GENERAL] pg_dump compress

can you pipe things on windows ? It's a desktop system after all, but dos had that sort of a feature - I seem to remember. -- 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] pg_dump compress

On 9/23/2011 6:46 AM, hubert depesz lubaczewski wrote: On Thu, Sep 22, 2011 at 11:00:10PM -0700, Roger Niederland wrote: Using pg_dump from the command line with the exe included in windows 32 bit install in postgresql 9.1. pg_dump -U username -f somefile.sql.gz -Z 9 -i dbname please note th

Re: [GENERAL] Replication between 64/32bit systems?

OK, thank you for clarification, I'll "resign" ;-) and accept that mixing 32/64 bits is not possible. I continued by experiments, and would like to share my results. First of all, I'm an idiot because I had logging_collector set to on and wondered why the terminals did not give any meaningful o

Re: [GENERAL] : Checksum ERROR when restoring Online Backup

Thanks Richard ! I realized that, I was restoring on an 32 bit server. Regards, Venkat On Fri, Sep 23, 2011 at 6:59 PM, Richard Huxton wrote: > On 23/09/11 13:53, Venkat Balaji wrote: > >> Linux *prod-server* 2.6.18-194.17.1.el5 #1 SMP Mon Sep 20 07:12:06 EDT >> 2010 x86_64 x86_64 x86_64 GNU/L

Re: [GENERAL] pg_dump compress

On Thursday, September 22, 2011 11:00:10 pm Roger Niederland wrote: > Using pg_dump from the command line with the exe included in windows 32 > bit install in postgresql 9.1. > pg_dump -U username -f somefile.sql.gz -Z 9 -i dbname > > outputs a file that is in plain text. In previous versions of

Re: [GENERAL] looking for a faster way to do that

>> > >> > Hi Alban, >> > >> > I already did that - the test set is just all records from the real table= >> (about a million >> > entries) that match the common 'ABC' prefix >> >> I think you misunderstood what I wrote. Notice the difference between >> "which strings match the pattern" and "which

Re: [GENERAL] pg_dump compress

On Thursday, September 22, 2011 11:00:10 pm Roger Niederland wrote: > Using pg_dump from the command line with the exe included in windows 32 > bit install in postgresql 9.1. > pg_dump -U username -f somefile.sql.gz -Z 9 -i dbname > Ignore my previous post. I just realized that I was using the 9

Re: [GENERAL] pg_dump compress

On Friday, September 23, 2011 6:46:49 am hubert depesz lubaczewski wrote: > On Thu, Sep 22, 2011 at 11:00:10PM -0700, Roger Niederland wrote: > > Using pg_dump from the command line with the exe included in windows > > 32 bit install in postgresql 9.1. > > pg_dump -U username -f somefile.sql.gz -Z

Re: [GENERAL] pg_dump compress

On Thursday, September 22, 2011 11:00:10 pm Roger Niederland wrote: > Using pg_dump from the command line with the exe included in windows 32 > bit install in postgresql 9.1. > pg_dump -U username -f somefile.sql.gz -Z 9 -i dbname > > outputs a file that is in plain text. In previous versions of

Re: [GENERAL] pg_dump compress

On Thu, Sep 22, 2011 at 11:00:10PM -0700, Roger Niederland wrote: > Using pg_dump from the command line with the exe included in windows > 32 bit install in postgresql 9.1. > pg_dump -U username -f somefile.sql.gz -Z 9 -i dbname please note that pg_dump --help shows: -Z, --compress=0-9

[GENERAL] pg_dump compress

Using pg_dump from the command line with the exe included in windows 32 bit install in postgresql 9.1. pg_dump -U username -f somefile.sql.gz -Z 9 -i dbname outputs a file that is in plain text. In previous versions of postgresql, this output a gzipped file. Also tried: pg_dump -U username

Re: [GENERAL] Speed of lo_unlink vs. DELETE on BYTEA

Hi, everyone. Albe wrote: Could you try with a trigger instead of a rule and see if the performance is better? Yours, Laurenz Albe Great idea.  I did that, and here are the results for 10,000 records: |   | Delete    |

Re: [GENERAL] : Checksum ERROR when restoring Online Backup

On 23/09/11 13:53, Venkat Balaji wrote: Linux *prod-server* 2.6.18-194.17.1.el5 #1 SMP Mon Sep 20 07:12:06 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux The server version where I restored the production backup is as follows - Linux *backup-server* 2.6.18-194.3.1.el5PAE #1 SMP Sun May 2 04:42:25 EDT 2

Re: [GENERAL] looking for a faster way to do that

On 23 September 2011 14:29, wrote: > > Alban Hertroys wrote: > > >> So you're comparing a variable field value to a variable pattern - yeah, > >> that's going to hurt. There's no way you could index exactly that. > >> > >> Perhaps there's some way you can transform the problem so that you get > >>

Re: [GENERAL] : Checksum ERROR when restoring Online Backup

Apologies - I did not mention complete details of PG and OS - Postgres 9.0.1 Production Operating System version where Postgres is running is as follows - Linux *prod-server* 2.6.18-194.17.1.el5 #1 SMP Mon Sep 20 07:12:06 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux The server version where I restore

[GENERAL] : Checksum ERROR when restoring Online Backup

Hello Everyone, I am testing the Online Backups of our production databases ( this is part of our disaster recovery plan ). After restoring the Online Backup, we tried to bring up the cluster and ended up with the following error - 2011-09-23 07:29:04 CDT [24092]: [1-1] FATAL: incorrect checksu

Re: [GENERAL] looking for a faster way to do that

Alban Hertroys wrote: >> So you're comparing a variable field value to a variable pattern - yeah, >> that's going to hurt. There's no way you could index exactly that. >> >> Perhaps there's some way you can transform the problem so that you get >> something indexable? >> For example, if your matc

Re: [GENERAL] looking for a faster way to do that

Eduardo Morras wrote: >> You can try these, i doubt they will use any index but its a >> different approach: >> >> select * from items where >> length(items.code)<>length(rtrim(items.code,'ABC')); >> >> select * from items where strpos(items.code,'ABC')=0 or >> strpos(items.code,'any_substrin

Re: [GENERAL] Date time value error in Ms Access using pass through queries

I found the solution. Earlier for 9.0 database I have changed a line as Datestyle= ' ISO, mdy' to DateStyle= "ISO, dmy'. So it was working fine. For 9.1 I forgot to make this change in postgresql.conf. After making the change everything is working fine. Regards, C P Kulkarni On Fri, Sep 23, 2011

Re: [GENERAL] Problem with pg_upgrade 9.0 -> 9.1

Thomas Kellerer, 17.09.2011 12:32: I was trying to upgrade my Postgres 9.0 installation using pg_upgrade. Running it first with --check revealed no problems. The when I did the actual migration, the following happened: Mismatch of relation id: database "dellstore", old relid 83613, new relid

Re: [GENERAL] Relative performance of prefix and suffix string matching

Le 23/09/2011 12:30, Alban Hertroys a écrit : > On 23 September 2011 11:47, Andrew Rose > wrote: > > Basic Question: In text fields, is prefix matching significantly faster > than suffix matching? > > > It does depend on what type of index you use. BTr

Re: [GENERAL] Speed of lo_unlink vs. DELETE on BYTEA

Reuven M. Lerner wrote: > When a record in the main table is deleted, there is a rule (yes a rule -- > not a trigger) in the > referencing table that performs a lo_unlink on the associated object. > I just want to check that my intuition is correct: Wouldn't it be way faster > and more efficien

Re: [GENERAL] Query performs badly with materialize node

On 23 September 2011 12:31, Ingmar Brouns wrote: > > > > In short: if you want to get useful commentary on your problem, you > > need to provide a more complete picture. > > > >regards, tom lane > > > > I've posted the query plans now several times to this list, but they >

Re: [GENERAL] looking for a faster way to do that

On 23 September 2011 09:45, wrote: > Alban Hertroys wrote: > > >> What is the output of explain? > >> > >> You say 'the other table', so presumably we're dealing with a foreign > key > >> here. Is there an index on that column? > > Albe Laurenz wrote: > > >> Is the index used for "where code ~ '

[GENERAL] get number and names of processes connected to postgresql

Basically, I got bunch of local processes connecting to postgresql, need to aggregate some sort of report about number of connections and its origin every so often. pg version is 8.3 Any ideas if there's tools to gather that info on linux ? Netstat is the only one I know, but I have to parse/awk i

Re: [GENERAL] Query performs badly with materialize node

> > In short: if you want to get useful commentary on your problem, you > need to provide a more complete picture. > >                        regards, tom lane > I've posted the query plans now several times to this list, but they do not show up, almost as if being caught by a spam filter or somet

Re: [GENERAL] Relative performance of prefix and suffix string matching

On 23 September 2011 11:47, Andrew Rose wrote: > Basic Question: In text fields, is prefix matching significantly faster > than suffix matching? > It does depend on what type of index you use. BTrees split off text strings, from left to right, halving the number of records you need to scan at ev

Re: [GENERAL] looking for a faster way to do that

At 09:45 23/09/2011, haman...@t-online.de wrote: A single anchored query select * from items where code ~ '^ABC'; does indeed use the index to retrieve data. So I wonder whether there might be a different approach to this problem rather than pattern matching. I recall I had a similar problem

Re: [GENERAL] Relative performance of prefix and suffix string matching

On Fri, Sep 23, 2011 at 11:47 AM, Andrew Rose wrote: > Basic Question: In text fields, is prefix matching significantly faster > than suffix matching? > If you are using text_pattern_ops, then yes. > 2. Alternatively, I could store column 'rev_str' as a reversed version of > column 'str' and h

Re: [GENERAL] Date time value error in Ms Access using pass through queries

As I have connected to postgres from MS Access, it thrown the error. Now I have updated ODBC driver, still same problems comes for 9.1 and not for 9.0. There should be some thing that has changed in 9.1 release which prevents from auto-conversion of format of date from client to server. >From packa

[GENERAL] Relative performance of prefix and suffix string matching

Basic Question: In text fields, is prefix matching significantly faster than suffix matching? Background: I'm designing a database schema where a common operation will be "search for substring x either at the beginning or end of column 'str'". 1. I could have the client issue... SELECT * FROM

Re: [GENERAL] looking for a faster way to do that

Alban Hertroys wrote: >> What is the output of explain? >> >> You say 'the other table', so presumably we're dealing with a foreign key >> here. Is there an index on that column? Albe Laurenz wrote: >> Is the index used for "where code ~ '^ABC3563'"? >> >> If not, then the result is fast only

Re: [GENERAL] Materialized views in Oracle

On Wed, Sep 21, 2011 at 11:34 PM, Craig Ringer wrote: [snip] > This can get complicated when you have triggers acting recursively on a > table and it isn't always that easy to understand exactly what a trigger > will see. I do agree with most all your points. The value I got out of this experien