Re: [GENERAL] 10 missing features

On 04/25/2011 04:54 PM, Nicholson, Brad (Toronto, ON, CA) wrote: The problem is that there is a lot of noise in the add-on space. There are lots of things out there that are no longer supported or partially supported. There is a fairly high barrier of entry into figuring out which tools to use,

Re: [GENERAL] Partitioning an existing table

> > so now when your running application goes to query the table, it gets > doubles? if you do it in transactions, then how long are you going to cause > the master table to be locked when doing such a bulk delete? > > my point is to minimize service interruption, and that means moving small > hun

Re: [GENERAL] 10 missing features

On Tue, Apr 26, 2011 at 06:21:21AM -0400, Greg Smith wrote: > addressed doesn't start with "how can PostgreSQL duplicate the > Oracle solution to this problem", which is how many of these > incoming requests for features start. The alternate question of > "how do you provide something with the sa

Re: [GENERAL] Partitioning an existing table

On 04/25/2011 10:10 AM, Vick Khera wrote: Basically, you create your partitions and set up the necessary triggers you want (I re-write the app to insert directly into the correct partition). Then all new data starts going into the partitions. Next, write a program that loops over the current

Re: [GENERAL] 10 missing features

On Monday 25 April 2011 10:41:36 Linos wrote: > Hi all, > only want to link this blog post > http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-features , > i think he may have any good points. > > > Miguel Angel. Maybe the best point is the one between the lines: That Postgr

Re: [GENERAL] Help - corruption issue?

On Mon, Apr 25, 2011 at 8:50 PM, Phoenix Kiula wrote: >> On Tuesday, April 26, 2011, Tomas Vondra wrote: >>> Dne 25.4.2011 18:16, Phoenix Kiula napsal(a): Sorry, spoke too soon. I can COPY individual chunks to files. Did that by year, and at least the dumping worked.

Re: [GENERAL] Help - corruption issue?

On Tue, Apr 26, 2011 at 3:24 PM, Scott Marlowe wrote: > On Mon, Apr 25, 2011 at 8:50 PM, Phoenix Kiula > wrote: >>> On Tuesday, April 26, 2011, Tomas Vondra wrote: Dne 25.4.2011 18:16, Phoenix Kiula napsal(a): > Sorry, spoke too soon. > > I can COPY individual chunks to files.

Re: [GENERAL] 10 missing features

On Tue, 26 Apr 2011 14:30:19 +0200, Leif Biberg Kristensen wrote: On Monday 25 April 2011 10:41:36 Linos wrote: Hi all, only want to link this blog post http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-features , i think he may have any good points. Miguel Angel. M

Re: [GENERAL] 10 missing features

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Radoslaw Smogura > Sent: Tuesday, April 26, 2011 9:55 AM > To: Leif Biberg Kristensen > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] 10 missing features >

Re: [GENERAL] 10 missing features

On Mon, Apr 25, 2011 at 3:41 AM, Linos wrote: > Hi all, >        only want to link this blog post > http://blog.kimiensoftware.com/2011/04/top-10-missing-postgresql-features , > i think he may have any good points. my take: 1. Query progress Seen a couple of near miss proposals -- good feature,

Re: [GENERAL] SSDs with Postgresql?

- Original Message - > From: "Greg Smith" > To: pgsql-general@postgresql.org > Sent: Friday, 22 April, 2011 12:49:28 AM > Subject: Re: [GENERAL] SSDs with Postgresql? > On 04/20/2011 01:50 AM, Toby Corkindale wrote: > > Also, the number of erase cycles you can get, over the whole disk, > >

[GENERAL] optimizing a cpu-heavy query

Folks, I'm trying to optimize the following query that performs KL Divergence [1]. As you can see the distance function operates on vectors of 150 floats. The query takes 12 minutes to run on an idle (apart from pgsql) EC2 m1 large instance with 2 million documents in the docs table. The CPU i

Re: [GENERAL] 10 missing features

On Tue, Apr 26, 2011 at 5:39 AM, Andrew Sullivan wrote: > In commercial development, this is where product development managers > live.  They identify the meaning of the feature request, and then > identify how the actual need (rather than the requested feature) can > be addressed. But there are

[GENERAL] tuning on ec2

I'm running pgsql on an m1.large EC2 instance with 7.5gb available memory. The free command shows 7gb of free+cached. My understand from the docs is that I should dedicate 1.75gb to shared_buffers (25%) and set effective_cache_size to 7gb. Is this correct? I'm running 64-bit Ubuntu 10.10, e.g

Re: [GENERAL] tuning on ec2

On Tue, Apr 26, 2011 at 8:59 AM, Joel Reymont wrote: > I'm running pgsql on an m1.large EC2 instance with 7.5gb available memory. > > The free command shows 7gb of free+cached. My understand from the docs is > that I should dedicate 1.75gb to shared_buffers (25%) and set > effective_cache_size t

Re: [GENERAL] tuning on ec2

On Apr 26, 2011, at 4:31 PM, Scott Marlowe wrote: > It's a reasonable start. However, if you consistently using less than > that in aggregate then lowering it is fine. Is there a way to tell if I consistently use less than that in aggregate? > What's your work_mem and max_connections set to?

[GENERAL]auto vacuum during restore

Hi, i've tested on 8.3 and 8.4 and i found that autovacuum works during restore uin my test scenario, which is: a new database wit two tables. tab1 has 1M recs, tab2 has 10 recs. When i drop the database and restore, pg_stat_all tables tells me good estimations for the number of live tuples. But

Re: [GENERAL] tuning on ec2

On Tue, Apr 26, 2011 at 9:34 AM, Joel Reymont wrote: > > On Apr 26, 2011, at 4:31 PM, Scott Marlowe wrote: > >> It's a reasonable start.  However, if you consistently using less than >> that in aggregate then lowering it is fine. > > Is there a way to tell if I consistently use less than that in a

[GENERAL] Recover database from binary files

Hi, I can't boot the postgres server anymore. I just have access to the files, like the /var/lib/postgresql/8.3 path, for example. I use debian and postgresql-8.3 . I don't have any sql dump. How can I recover my databases with just the binary files there? I tried copy the /var/lib/postgresql/8.

Re: [GENERAL] Recover database from binary files

On Tue, Apr 26, 2011 at 9:43 AM, Diogo Melo wrote: > Hi, > > I can't boot the postgres server anymore. I just have access to the files, > like the /var/lib/postgresql/8.3 path, for example. I use debian and > postgresql-8.3 . > > I don't have any sql dump. How can I recover my databases with just

[GENERAL] Fwd: Recover database from binary files

Diogo Oliveira de Melo Ciência da Computação ICMC - USP São Carlos -- Forwarded message -- From: Diogo Melo Date: Tue, Apr 26, 2011 at 12:43 PM Subject: Recover database from binary files To: pgsql-general@postgresql.org Hi, I can't boot the postgres server anymore. I just h

Re: [GENERAL] Recover database from binary files

just tar -zcvf 8.3.tar.gz /mnt/sdf/var/lib/postgresql/8.3/ since i found a way to mount the partition on /mnt/sdf. first I installed postgres on a virtual machine and then I extracted the 8.3.tar.gz at /var/lib/postgresql . when i try to sh /etc/init.d/postgresql-8.3 start it runs the command ins

Re: [GENERAL] Recover database from binary files

On Tue, Apr 26, 2011 at 9:50 AM, Diogo Melo wrote: > just tar -zcvf 8.3.tar.gz /mnt/sdf/var/lib/postgresql/8.3/ > since i found a way to mount the partition on /mnt/sdf. > first I installed postgres on a virtual machine and then I extracted the > 8.3.tar.gz at /var/lib/postgresql . when i try to s

Re: [GENERAL] optimizing a cpu-heavy query

Joel Reymont writes: > I'm trying to optimize the following query that performs KL Divergence [1]. > As you can see the distance function operates on vectors of 150 floats. > CREATE OR REPLACE FUNCTION docs_within_distance(vec topics, threshold float) > RETURNS TABLE(id doc_id, distance float)

Re: [GENERAL] SSDs with Postgresql?

On Thu, Apr 21, 2011 at 10:33 AM, Florian Weimer wrote: > * Greg Smith: > > > The fact that every row update can temporarily use more than 8K means > > that actual write throughput on the WAL can be shockingly large. The > > smallest customer I work with regularly has a 50GB database, yet they >

Re: [GENERAL]auto vacuum during restore

On Tue, Apr 26, 2011 at 11:36 AM, Willy-Bas Loos wrote: > I don't get it, they should have been analyzed at restore, should they not? > I do not believe analyze is part of the restore process. You must explicitly run it.

[GENERAL] "pg_control version number" after 9.0.4 installation

This is just a notice: After installing 9.0.4 I noticed that I get “pg_control version number = 903” when pg_controldata is run. Thanks, Carlos Sotto Maior +55 11 8244-7899 cso...@sistemassim.com.br Sistemas Sim Serviços e Tecnologia Ltda. +55 11

Re: [GENERAL] Recover database from binary files

You were right. It was just a matter of set chown -R postgres.postgres /var/lib/postgresql/8.3 :D :D :D Case solved :D Thanks a lot dude :D Diogo Oliveira de Melo Ciência da Computação ICMC - USP São Carlos On Tue, Apr 26, 2011 at 12:52 PM, Scott Marlowe wrote: > On Tue, Apr 26, 2011 at 9:5

Re: [GENERAL]auto vacuum during restore

Le 26/04/2011 18:38, Vick Khera a écrit : > On Tue, Apr 26, 2011 at 11:36 AM, Willy-Bas Loos wrote: > >> I don't get it, they should have been analyzed at restore, should they not? >> > > I do not believe analyze is part of the restore process. You must > explicitly run it. > That's almost tr

Re: [GENERAL]auto vacuum during restore

On Tue, Apr 26, 2011 at 12:59 PM, Guillaume Lelarge wrote: > > I do not believe analyze is part of the restore process. You must > > explicitly run it. > > > > That's almost true. If autovacuum is on, it can start an ANALYZE on the > big tables. > > So that just means you need some luck of timing

[GENERAL] PG 9.0 - Default postgresql.conf?

Hi, Is there any place I can download the default postgresql.conf that comes with 9.0? 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] PG 9.0 - Default postgresql.conf?

On Tue, Apr 26, 2011 at 19:11, Phoenix Kiula wrote: > Hi, > > Is there any place I can download the default postgresql.conf that > comes with 9.0? http://git.postgresql.org/gitweb?p=postgresql.git;a=blob_plain;f=src/backend/utils/misc/postgresql.conf.sample;h=a3880da7dac5d8ff55dc8f5f7a3f1fc805262

[GENERAL] Switching Database Engines

We've been using a Wiki server at the office for years. It was originally configured to use MySQL and finally after 8+ years we're moving the Wiki to a new platform of hardware. My question is the Wiki software (MediaWiki) is the only thing still tied to and using MySQL which we want to decommissio

Re: [GENERAL] Switching Database Engines

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL > > Can you guys tell me if this is something that will work? I don't mean > the exact link above but just in general taking a database from MySQL > and successfully migrating it for

Re: [GENERAL] PG 9.0 - Default postgresql.conf?

On Wed, Apr 27, 2011 at 1:18 AM, Magnus Hagander wrote: > On Tue, Apr 26, 2011 at 19:11, Phoenix Kiula wrote: >> Hi, >> >> Is there any place I can download the default postgresql.conf that >> comes with 9.0? > > http://git.postgresql.org/gitweb?p=postgresql.git;a=blob_plain;f=src/backend/utils/m

Re: [GENERAL] PG 9.0 - Default postgresql.conf?

On Wed, Apr 27, 2011 at 1:53 AM, Phoenix Kiula wrote: > On Wed, Apr 27, 2011 at 1:18 AM, Magnus Hagander wrote: >> On Tue, Apr 26, 2011 at 19:11, Phoenix Kiula wrote: >>> Hi, >>> >>> Is there any place I can download the default postgresql.conf that >>> comes with 9.0? >> >> http://git.postgresq

Re: [GENERAL] Switching Database Engines

On Apr 26, 2011, at 10:24 AM, Carlos Mennens wrote: > We've been using a Wiki server at the office for years. It was > originally configured to use MySQL and finally after 8+ years we're > moving the Wiki to a new platform of hardware. My question is the Wiki > software (MediaWiki) is the only th

Re: [GENERAL] PG 9.0 - Default postgresql.conf?

On Wed, Apr 27, 2011 at 01:59:10AM +0800, Phoenix Kiula wrote: How can I check what the problem is? I don't see any errors at least > on the command line.All I see is: > > > > service postgresql restart > Stopping postgresql service: [ OK ] > Starting postgresql s

Re: [GENERAL] Switching Database Engines

On Tue, Apr 26, 2011 at 1:44 PM, Greg Sabino Mullane wrote: > In general, yes. For your specific use case, it might be best to use > MediaWiki's XML dump and restore. You could also use the conversion > script that comes with MediaWiki, at: > > maintenance/postgres/mediawiki_mysql2postgres.pl > >

Re: [GENERAL] PG 9.0 - Default postgresql.conf?

On 04/26/11 10:59 AM, Phoenix Kiula wrote: How can I check what the problem is? I don't see any errors at least on the command line.All I see is: > service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service:

Re: [GENERAL] "pg_control version number" after 9.0.4 installation

"Carlos Sotto Maior \(SIM\)" writes: > After installing 9.0.4 I noticed that I get “pg_control version number = > 903” when pg_controldata is run. Those numbers are unrelated. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To mak

Re: [GENERAL] Help - corruption issue?

Dne 26.4.2011 04:50, Phoenix Kiula napsal(a): > Tomas, the line where it crashed, here are the 10 or so lines around it: > >> > head -15272350 /backup/links/links_all.txt | tail -20 No, those lines are before the one that causes problems - line number is 15272357, and you've printed just 15272350

Re: [GENERAL] Help - corruption issue?

Dne 26.4.2011 14:41, Phoenix Kiula napsal(a): > On Tue, Apr 26, 2011 at 3:24 PM, Scott Marlowe > wrote: >> Are you sure you're getting all the data out of the source (broken) >> database you think you are? Are you sure those rows are in the dump? > > > > Actually I am not. Some rows are missi

[GENERAL] Partial Index [WHERE col IN ('A','B')] Planner Anomalies

Hi, I have the following query: EXPLAIN SELECT * FROM taskinstance WHERE ti_status IN ('ACTIVE', 'DISPATCHED', 'FAILURE'); With two indices: ti_active. partial index over (ti_status) WHERE ti_status IN ('ACTIVE','DISPATCHED','FAILURE') ti_sortedstatus. full index over (ti_status, ti_c

Re: [GENERAL] Switching Database Engines

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Just to be clear and make sure I understand correctly, I can export > the Wiki info using the MediaWiki XML export tool (which I found) or I > can try to use the MediaWiki tool referenced as > 'mediawiki_mysql2postgres.pl', right? I think from

Re: [GENERAL] Switching Database Engines

On Tue, Apr 26, 2011 at 3:06 PM, Greg Sabino Mullane wrote: > Correct. Keep in mind I don't think the XML route will convert the users > table, just the wiki data itself. As someone else mentioned, the > wiki itself will work fine, but support for any MediaWiki extensions > is hit or miss because

Re: [GENERAL] Switching Database Engines

On 04/26/11 12:24 PM, Carlos Mennens wrote: I tried running the tool as suggested before and unless I'm doing something wrong, I have no idea why it didn't work: [root@db_old postgres]# pwd /var/www/html/int/main/wiki/maintenance/postgres [root@db_old postgres]# ls -l total 60 drwxr-xr-x 2 roo

Re: [GENERAL] Switching Database Engines

On 26 Apr 2011, at 19:24, Carlos Mennens wrote: > We've been using a Wiki server at the office for years. It was > originally configured to use MySQL and finally after 8+ years we're > moving the Wiki to a new platform of hardware. My question is the Wiki > software (MediaWiki) is the only thing s

Re: [GENERAL] Switching Database Engines

On Tue, Apr 26, 2011 at 4:34 PM, Alban Hertroys wrote: > I don't know much about mediawiki (except for how to use it), but it's not > unusual for modern web-apps to have some functionality to dump their contents > in a consistently formatted file (often XML) that it can subsequently import > in

Re: [GENERAL] Partial Index [WHERE col IN ('A','B')] Planner Anomalies

"David Johnston" writes: > If I explain the above query multiple times (without any data changes) I get > either of the two query plans / the Bitmap Heap one more often. If I > analyze the table I immediately get the "Index Scan" plan first but > subsequent explains revert to switching between th

[GENERAL] Feature Request, aggregate functions distinct on

I have used and it is quite useful the 'distinct' keyword within aggregate functions. I am not an expert on the official sql spec, but I think it would be useful if distinct on() could be used within an aggregate when supplied with another column name. For example: select sum(distinct on(id) ord

Re: [GENERAL] Feature Request, aggregate functions distinct on

luda posch writes: > I am not an expert on the official sql spec, but I think it would be useful > if distinct on() could be used within an aggregate when supplied with > another column name. For example: > select sum(distinct on(id) order_price) from order_table; > select sum(distinct on(email_

Re: [GENERAL] Feature Request, aggregate functions distinct on

On 04/26/2011 04:33 PM, Tom Lane wrote: luda posch writes: I am not an expert on the official sql spec, but I think it would be useful if distinct on() could be used within an aggregate when supplied with another column name. For example: select sum(distinct on(id) order_price) from order

Re: [GENERAL] Poor performance of btrfs with Postgresql

On 22/04/11 12:39, mark wrote: (Tested on Ubuntu Server - Maverick - Kernel 2.6.35-28) Don't take this the wrong way - I applaud you asking for feedback. BTW -> Have you seen Greg Smiths PG 9.0 high performance book ? it's got some chapters dedicated to benchmarking. I do have the book, actu