[GENERAL] Installing plpython3u
Hello, I'm having problems installing plpython3u, this is my situation: I have installed postgresql-9.3.5 in my home directory, from source. I used (from my memory, it might not be exact) ./configure --prefix=$HOME; make; make install Now I need to upload a database which requires plpython3u, and this is what happens: pg_restore -U berald01 -d sblab -h localhost -1 current_pg_sblab.backup.tar pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1590; 2612 24721 PROCEDURAL LANGUAGE plpython3u dberaldi pg_restore: [archiver (db)] could not execute query: ERROR: could not access file "$libdir/plpython3": No such file or directory Command was: CREATE OR REPLACE PROCEDURAL LANGUAGE plpython3u; If I try to create plpython3u I get: createlang plpython3u sblab createlang: language installation failed: ERROR: could not open extension control file "/data/sblab-home/berald01/share/postgresql/extension/plpython3u.control": No such file or directory I'm a bit at a loss, how do I add plpython3u? My OS is CentOS release 6. Thanks! Dario
Re: [GENERAL] Installing plpython3u
On 15 October 2015 at 16:23, Tom Lane <t...@sss.pgh.pa.us> wrote: > Dario Beraldi <dario.bera...@gmail.com> writes: > >> It might be worth cd'ing into the src/pl/plpython subdirectory and > >> manually doing "make install" there to see what it prints. > > > Here we go: > > cd > > > /data/sblab-home/berald01/applications/postgresql/postgresql-9.3.5/src/pl/plpython > > make install > > make: Nothing to be done for `install'. > > That, and the fact that your "ls" shows no derived files, means that the > Makefile is choosing not to do anything, which a look at the Makefile > says must be because shared_libpython isn't getting set. (As of 9.5 > we've changed that to not fail silently, but in 9.3 this is what it does.) > > There are two possibilities here: either your python3 installation does > not include a shared-library version of libpython, or it does but the > configure+Make process is failing to detect that. Probably should > establish which of those it is before going further. > > regards, tom lane > Ahh, I guess this answers the question then: cd /data/sblab-home/berald01/applications/postgresql/postgresql-9.3.5/src/pl/plpython/ make *** Cannot build PL/Python because libpython is not a shared library. *** You might have to rebuild your Python installation. Refer to *** the documentation for details. Right, it looks like I have to rebuild python then. Thanks guys! Dario
Re: [GENERAL] Installing plpython3u
On 15 October 2015 at 16:29, Dario Beraldi <dario.bera...@gmail.com> wrote: > > > > > > On 15 October 2015 at 16:23, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Dario Beraldi <dario.bera...@gmail.com> writes: >> >> It might be worth cd'ing into the src/pl/plpython subdirectory and >> >> manually doing "make install" there to see what it prints. >> >> > Here we go: >> > cd >> > >> /data/sblab-home/berald01/applications/postgresql/postgresql-9.3.5/src/pl/plpython >> > make install >> > make: Nothing to be done for `install'. >> >> That, and the fact that your "ls" shows no derived files, means that the >> Makefile is choosing not to do anything, which a look at the Makefile >> says must be because shared_libpython isn't getting set. (As of 9.5 >> we've changed that to not fail silently, but in 9.3 this is what it does.) >> >> There are two possibilities here: either your python3 installation does >> not include a shared-library version of libpython, or it does but the >> configure+Make process is failing to detect that. Probably should >> establish which of those it is before going further. >> >> regards, tom lane >> > > Ahh, I guess this answers the question then: > > cd > /data/sblab-home/berald01/applications/postgresql/postgresql-9.3.5/src/pl/plpython/ > make > > *** Cannot build PL/Python because libpython is not a shared library. > *** You might have to rebuild your Python installation. Refer to > *** the documentation for details. > Ok, this seems to have done the trick: # Get python and install as shared library: wget http://python.org/ftp/python/3.5.0/Python-3.5.0.tar.xz tar xf Python-3.5.0.tar.xz cd Python-3.5.0 ./configure --enable-shared \ --prefix=$HOME \ LDFLAGS="-Wl,--rpath=$HOME/lib" make make altinstall # Re-configure postgres cd /Users/berald01/applications/postgresql/postgresql-9.3.5/ ./configure --prefix=$HOME --with-python PYTHON=~/bin/python3.5 make make install # Create python3 lang: createlang plpython3u sblab
Re: [GENERAL] Installing plpython3u
> createlang plpython3u sblab >>> ERROR: could not open extension control file >>> >>> "/data/sblab-home/berald01/share/postgresql/extension/plpython3u.control": >>> No such file or directory >>> STATEMENT: CREATE EXTENSION "plpython3u"; >>> >> >> Hmm, what files *do* you have in that directory > > Here's what I see: ls -l /data/sblab-home/berald01/share/postgresql/extension/ total 12 -rw-r--r-- 1 berald01 sblab 332 Oct 15 15:30 plpgsql--1.0.sql -rw-r--r-- 1 berald01 sblab 179 Oct 15 15:30 plpgsql.control -rw-r--r-- 1 berald01 sblab 381 Oct 15 15:30 plpgsql--unpackaged--1.0.sql > There seems to be a discrepancy in paths: > > ERROR: could not open extension control file > "/data/sblab-home/berald01/share/postgresql/extension/plpython3u.control" > > configure_args=' '\''--prefix=/Users/berald01'\'' > > So is there something mapping /Users/berald01 to /data/sblab-home/ ? > It *should* be fine /Users/berald01 and /data/sblab-home/berald01 point to the same space. I.e. "ls /Users/berald01" is the same as "ls /data/sblab-home/berald01" > >> It might be worth cd'ing into the src/pl/plpython subdirectory and >> manually doing "make install" there to see what it prints. >> > Here we go: cd /data/sblab-home/berald01/applications/postgresql/postgresql-9.3.5/src/pl/plpython make install make: Nothing to be done for `install'. Any clue? (Thanks a ton for your assistance!) > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: [GENERAL] Installing plpython3u
> > It might be worth cd'ing into the src/pl/plpython subdirectory and >> manually doing "make install" there to see what it prints. >> >> >> By the way, that's what I see in src/pl/plpython: ls -l /data/sblab-home/berald01/applications/postgresql/postgresql-9.3.5/src/pl/plpython total 292 drwxrwx--- 2 berald01 sblab 4096 Jul 21 2014 expected -rw-r- 1 berald01 sblab 1002 Jul 21 2014 generate-spiexceptions.pl -rw-r- 1 berald01 sblab 6154 Jul 21 2014 Makefile -rw-r- 1 berald01 sblab 648 Jul 21 2014 nls.mk -rw-r- 1 berald01 sblab 10623 Jul 21 2014 plpy_cursorobject.c -rw-r- 1 berald01 sblab 394 Jul 21 2014 plpy_cursorobject.h -rw-r- 1 berald01 sblab 10841 Jul 21 2014 plpy_elog.c -rw-r- 1 berald01 sblab 699 Jul 21 2014 plpy_elog.h -rw-r- 1 berald01 sblab 22176 Jul 21 2014 plpy_exec.c -rw-r- 1 berald01 sblab 294 Jul 21 2014 plpy_exec.h -rw-r- 1 berald01 sblab 10407 Jul 21 2014 plpy_main.c -rw-r- 1 berald01 sblab 789 Jul 21 2014 plpy_main.h -rw-r- 1 berald01 sblab 2476 Jul 21 2014 plpy_planobject.c -rw-r- 1 berald01 sblab 456 Jul 21 2014 plpy_planobject.h -rw-r- 1 berald01 sblab 9942 Jul 21 2014 plpy_plpymodule.c -rw-r- 1 berald01 sblab 365 Jul 21 2014 plpy_plpymodule.h -rw-r- 1 berald01 sblab 13374 Jul 21 2014 plpy_procedure.c -rw-r- 1 berald01 sblab 1596 Jul 21 2014 plpy_procedure.h -rw-r- 1 berald01 sblab 6980 Jul 21 2014 plpy_resultobject.c -rw-r- 1 berald01 sblab 573 Jul 21 2014 plpy_resultobject.h -rw-r- 1 berald01 sblab 13793 Jul 21 2014 plpy_spi.c -rw-r- 1 berald01 sblab 780 Jul 21 2014 plpy_spi.h -rw-r- 1 berald01 sblab 5490 Jul 21 2014 plpy_subxactobject.c -rw-r- 1 berald01 sblab 673 Jul 21 2014 plpy_subxactobject.h -rw-r- 1 berald01 sblab 351 Jul 21 2014 plpython2u--1.0.sql -rw-r- 1 berald01 sblab 196 Jul 21 2014 plpython2u.control -rw-r- 1 berald01 sblab 402 Jul 21 2014 plpython2u--unpackaged--1.0.sql -rw-r- 1 berald01 sblab 351 Jul 21 2014 plpython3u--1.0.sql -rw-r- 1 berald01 sblab 196 Jul 21 2014 plpython3u.control -rw-r- 1 berald01 sblab 402 Jul 21 2014 plpython3u--unpackaged--1.0.sql -rw-r- 1 berald01 sblab 4071 Jul 21 2014 plpython.h -rw-r- 1 berald01 sblab 347 Jul 21 2014 plpythonu--1.0.sql -rw-r- 1 berald01 sblab 194 Jul 21 2014 plpythonu.control -rw-r- 1 berald01 sblab 393 Jul 21 2014 plpythonu--unpackaged--1.0.sql -rw-r- 1 berald01 sblab 27349 Jul 21 2014 plpy_typeio.c -rw-r- 1 berald01 sblab 2659 Jul 21 2014 plpy_typeio.h -rw-r- 1 berald01 sblab 3548 Jul 21 2014 plpy_util.c -rw-r- 1 berald01 sblab 511 Jul 21 2014 plpy_util.h drwxrwx--- 2 berald01 sblab 144 Jul 21 2014 po -rw-r- 1 berald01 sblab 22857 Jul 21 2014 spiexceptions.h drwxrwx--- 2 berald01 sblab 4096 Jul 21 2014 sql
Re: [GENERAL] Installing plpython3u
Hi Adrian, Thanks for your answer. Just checking before I screw things up... About "the source tree has to be configured and built twice", does it mean that I have to execute again ./configure --prefix=$HOME; make; make install And should I enable any particular option in ./configure? I see there is a "--with-python" option (not specific to python3 though). If it matters, my python 3 is in /usr/local/bin/python3. On 15 October 2015 at 14:20, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/15/2015 03:21 AM, Dario Beraldi wrote: > >> Hello, >> >> I'm having problems installing plpython3u, this is my situation: >> I have installed postgresql-9.3.5 in my home directory, from source. I >> used (from my memory, it might not be exact) >> >> ./configure --prefix=$HOME; >> make; >> make install >> >> Now I need to upload a database which requires plpython3u, and this is >> what happens: >> >> pg_restore -U berald01 -d sblab -h localhost -1 >> current_pg_sblab.backup.tar >> >> pg_restore: [archiver (db)] Error while PROCESSING TOC: >> pg_restore: [archiver (db)] Error from TOC entry 1590; 2612 24721 >> PROCEDURAL LANGUAGE plpython3u dberaldi >> pg_restore: [archiver (db)] could not execute query: ERROR: could not >> access file "$libdir/plpython3": No such file or directory >> Command was: CREATE OR REPLACE PROCEDURAL LANGUAGE plpython3u; >> >> If I try to create plpython3u I get: >> >> createlang plpython3u sblab >> createlang: language installation failed: ERROR: could not open >> extension control file >> "/data/sblab-home/berald01/share/postgresql/extension/plpython3u.control": >> No such file or directory >> >> I'm a bit at a loss, how do I add plpython3u? >> > > See here: > > http://www.postgresql.org/docs/9.3/interactive/plpython-python23.html > > "Tip: The built variant depends on which Python version was found during > the installation or which version was explicitly set using the PYTHON > environment variable; see Section 15.4. To make both variants of PL/Python > available in one installation, the source tree has to be configured and > built twice." > > >> My OS is CentOS release 6. >> >> Thanks! >> Dario >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: [GENERAL] Installing plpython3u
On 15 October 2015 at 14:46, Tom Lane <t...@sss.pgh.pa.us> wrote: > Dario Beraldi <dario.bera...@gmail.com> writes: > > Thanks for your answer. Just checking before I screw things up... About > > "the source tree has to be configured and built twice", does it mean > that I > > have to execute again > > > ./configure --prefix=$HOME; > > make; > > make install > > > And should I enable any particular option in ./configure? I see there is > a > > "--with-python" option (not specific to python3 though). > > Indeed --- you have not built any version of plpython here. You need > --with-python, and you need to make sure the PYTHON environment variable > is set (else you'll get whatever version is invoked by "python", which is > most likely python2). See the build instructions in the documentation. > Also watch the output from configure, which will show you which python > it selected. > > regards, tom lane > Sorry guys... I executed ./configure --prefix=$HOME --with-python PYTHON=/usr/local/bin/python3 make make install and it completed fine (see also below output from 'grep -i 'PYTHON' config.log'). Still after restarting postgres I get: createlang plpython3u sblab ERROR: could not open extension control file "/data/sblab-home/berald01/share/postgresql/extension/plpython3u.control": No such file or directory STATEMENT: CREATE EXTENSION "plpython3u"; ## From config.log grep -i 'PYTHON' config.log $ ./configure --prefix=/Users/berald01 --with-python PYTHON=/usr/local/bin/python3 PATH: /opt/rh/python27/root/usr/bin configure:5399: checking whether to build Python modules configure:7499: checking for python configure:7529: result: /usr/local/bin/python3 configure:7544: checking for Python distutils module configure:7557: checking Python configuration directory configure:7562: result: /usr/local/lib/python3.4/config-3.4m configure:7565: checking Python include directories configure:7575: result: -I/usr/local/include/python3.4m configure:7580: checking how to link an embedded Python application configure:7607: result: -L/usr/local/lib/python3.4/config-3.4m -lpython3.4m -lpthread -ldl -lutil -lm configure:7612: checking whether Python is compiled with thread support configure:29636: checking Python.h usability configure:29653: gcc -c -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -I/usr/local/include/python3.4m -D_GNU_SOURCE conftest.c >&5 configure:29678: checking Python.h presence configure:29693: gcc -E -I/usr/local/include/python3.4m -D_GNU_SOURCE conftest.c configure:29747: checking for Python.h ac_cv_header_Python_h=yes ac_cv_path_PYTHON=/usr/local/bin/python3 PYTHON='/usr/local/bin/python3' configure_args=' '\''--prefix=/Users/berald01'\'' '\''--with-python'\'' '\''PYTHON=/usr/local/bin/python3'\''' python_additional_libs='-lpthread -ldl -lutil -lm' python_enable_shared='0' python_includespec='-I/usr/local/include/python3.4m' python_libdir='/usr/local/lib/python3.4/config-3.4m' python_libspec='-L/usr/local/lib/python3.4/config-3.4m -lpython3.4m' python_majorversion='3' python_version='3.4' with_python='yes'
[GENERAL] Visualize database schema
Hello, Apologies if this question has been asked before, but I couldn't come up with a decent solution... Can anyone advice about a tool to visualize a database schema? Ideally, I would like something that takes the SQL definition of a schema or database (essentially the output of pg_dump) and produces a graphical representation of the tables, constraints and indexes which can be moved around for ease of visualization (something like the Graphical query builder that comes with pgAdmin =1.14). I don't care (and don't want) to modify or query the database with such tool, I just want to visualize it. (In fact, I tried to use pgAdmin for this but the graphical queries can only be saved as images and so they cannot be reloaded). Thanks! Dario
Re: [GENERAL] Visualize database schema
Many thanks to everybody for advice! I'll give it a go to the tools you suggested. Dario On 14 August 2012 09:54, Dario Beraldi dario.bera...@gmail.com wrote: Hello, Apologies if this question has been asked before, but I couldn't come up with a decent solution... Can anyone advice about a tool to visualize a database schema? Ideally, I would like something that takes the SQL definition of a schema or database (essentially the output of pg_dump) and produces a graphical representation of the tables, constraints and indexes which can be moved around for ease of visualization (something like the Graphical query builder that comes with pgAdmin =1.14). I don't care (and don't want) to modify or query the database with such tool, I just want to visualize it. (In fact, I tried to use pgAdmin for this but the graphical queries can only be saved as images and so they cannot be reloaded). Thanks! Dario
[GENERAL] plpython on postgresql 9.1
Hello, I have installed postgresql on a mac using the 'one click' installer (postgresql-9.1.1-1-osx.dmg). I then tried to import the python language but I get the error: create language plpythonu; ERROR: could not access file $libdir/plpython2: No such file or directory Can you help me in figuring out what is wrong? (I.e. where can I get plpython2?) These are some settings that might be relevant: Mac OS X 10.6.8 PostgreSQL 9.1.1 python 2.6.1 pg_config --pkglibdir /Library/PostgreSQL/9.1/lib/postgresql pg_config --libdir /Library/PostgreSQL/9.1/lib Many thanks! Dario
[GENERAL] postgres for OLAP data mining
Hello, I'm looking for some information (guidelines, docs, tutorials, etc...) about using postgres for OLAP, data mining, data warehousing. The sort of questions I'm trying to answer are on the lines of - What tools/SQL commands are available? - How should postgres be configured? - How suitable is postgres for these tasks (compared to other databases)? I have done some Google search but I would appreciate any advice/hint from more experienced users. Many thanks in advance! Dario
Re: [GENERAL] postgres for OLAP data mining
On 30 September 2011 18:41, Henry Drexler alonup...@gmail.com wrote: Are you looking for stuff like this? http://www.postgresql.org/docs/9.0/static/functions-window.html http://www.postgresql.org/docs/9.0/static/functions-string.html Thanks for reply. Yes, I knew these functions. In fact, I was rather wondering whether there are dedicated packages for OLAP and some guidelines for configuring postgres (and whether postgres is a good option for OLAP). Best Dario On Fri, Sep 30, 2011 at 10:12 AM, Dario Beraldi dario.bera...@gmail.comwrote: Hello, I'm looking for some information (guidelines, docs, tutorials, etc...) about using postgres for OLAP, data mining, data warehousing. The sort of questions I'm trying to answer are on the lines of - What tools/SQL commands are available? - How should postgres be configured? - How suitable is postgres for these tasks (compared to other databases)? I have done some Google search but I would appreciate any advice/hint from more experienced users. Many thanks in advance! Dario
[GENERAL] Cluster table and order information
Hello, From the documentation of CLUSTER table (http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I understand that clustering can be achieved by re-creating the table like this: CREATE TABLE newtable AS SELECT * FROM table ORDER BY columnlist; My question is: If I upload with COPY a datafile which is already correctly sorted, can I inform postgres of such order, so that no clustering is necessary after the import? In other words, how can I tell postgres that my file is order by this and that column? Many thanks! Dario -- Dr. Dario Beraldi Institute of Evolutionary Biology University of Edinburgh West Mains Road Edinburgh EH9 3JT Scotland, UK -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. -- 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] Cluster table and order information
Quoting Andy Colson a...@squeakycode.net: On 2/1/2011 9:08 AM, Dario Beraldi wrote: Hello, From the documentation of CLUSTER table (http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I understand that clustering can be achieved by re-creating the table like this: CREATE TABLE newtable AS SELECT * FROM table ORDER BY columnlist; My question is: If I upload with COPY a datafile which is already correctly sorted, can I inform postgres of such order, so that no clustering is necessary after the import? In other words, how can I tell postgres that my file is order by this and that column? Many thanks! Dario The planner has no knowledge of cluster. Meaning PG will query a clustered and unclustered table exactly the same way. A table is not marked or anything as clustered. And in fact, during usage of a table it'll become unclustered. Clustering is only useful when you are going to read multiple records in the same order as an index. It turns more random seeks into more sequential reads. If your COPY loads data in indexed order, then just dont run the cluster. -Andy Thanks very much Andy, this clarifies my doubts. I was misled by the docs saying When a table is clustered, PostgreSQL remembers which index it was clustered by which made me think that the order information is stored somewhere. All the best Dario -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can the query planner create indexes?
Hello, This question is just for my curiosity... When an index is available for a query, the planner decides whether to use it or not depending on whether it would make the query perform better, right? However if an index, which does not exist, would make the query run better the planner is not able (allowed?) to create such index, use it, and drop it once the query is done. Why is it so? Why is the query planner not allowed to create indexes, but only allowed to use or not use what's available? Am I misunderstanding the way the planner works? Any explanation clarification much appreciated! All the best and Christmas wishes! Dario -- Dr. Dario Beraldi Institute of Evolutionary Biology University of Edinburgh West Mains Road Edinburgh EH9 3JT Scotland, UK -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. -- 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] Can the query planner create indexes?
Ok, thanks a lot to all of you for your answers! (Always impressed by the prompt feedback you get on this list!) Quoting Gabriele Bartolini gabriele.bartol...@2ndquadrant.it: Ciao Dario, On Tue, 21 Dec 2010 09:14:36 +, Dario Beraldi dario.bera...@ed.ac.uk wrote: the query run better the planner is not able (allowed?) to create such index, use it, and drop it once the query is done. Why is it so? Because it is not its responsibility. This is the simplest and most rational answer. I would reply with some questions that hopefully will give you the answer. How can you define a 'good' query from a 'bad' query? Consider the case when an user launches a wrong query and the planner you propose starts creating an index. I believe that would be a mess. Then ... how could you project this scenario in a concurrent context where multiple users launch queries that 'need' an index? I suggest that you look at the documentation for more information. Otherwise, I strongly suggest that you read the chapter on the planner from Greg's book on High Performance (which you can find from here: http://www.postgresql.org/docs/books/) Merry Christmas to you too! Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it -- Dr. Dario Beraldi Institute of Evolutionary Biology University of Edinburgh West Mains Road Edinburgh EH9 3JT Scotland, UK -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. -- 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] postgres.conf settings
Thanks Martijn and to the others who replied. Here's some more detail... Quoting Martijn van Oosterhout klep...@svana.org: Not a bad start, but to be sure you really need to provide a bit more info, like: - How many simultaneous clients you're expecting? - Lots of updates, or is it read only? - Lots of simple queries, or fewer but more complex queries? Basically, what's the workload? About the workload, I'll try to be a bit more specific: - Number of clients: I am the only person using my database. Usually I run one or two queries at the same time but I can have more sessions open at the same time (but typically 10). Hence I set max_connections = 20. - INSERT, UPDATE, DELETE queries are rare. - However, I use a lot of CREATE [TEMP] TABLE, CREATE INDEX - Also frequent is the use of COPY TO/FROM to import/export datafiles that will be handled by other applications (R, Python etc.) - SELECT queries: usually simple in design but handling large datasets which require JOIN and GROUP BY (e.g. SELECT a, b FROM t1 INNER JOIN t2 ON t1.c = t2.c ORDER BY a; where t1 and t2 could have millions of rows). - Background information: I'm a geneticist/bioinformatician using postgresql to manage the data I or others produce. I'm not a proper database programmer who run a web server. Many thanks for all and any feedback! Dario -- Dr. Dario Beraldi Institute of Evolutionary Biology University of Edinburgh West Mains Road Edinburgh EH9 3JT Scotland, UK -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres.conf settings
Hello, I'm trying to tune the parameters in postgres.conf to improve the performance of my database (where performance = 'make queries run faster'). I would be very grateful if you could give me some comments about my choice of configuration settings below (did I do anything very silly? Am I missing something relevant?). This my situation: 1. I installed postgresql (8.4) on my own machine to manage my own data. So I'm its only user. Queries are not complicated but should handle tables with (tens of) millions of rows. 2. Hardware: Laptop (DELL Latitude E6500) with 3.48 GB of RAM; Intel Core 2 Duo Mobile Processor P8600 2.40 GHz; running Windows XP. 160 GB hard disk (+ an external one of 640 GB). 3. Size of database: The /data directory is c.ca 37 GB, 88 tables in the main schema. 4. Raw data is backed-up elsewhere so I'm not terribly worried about risks of losing data. And these are the entries in postgres.conf that I changed from default (after reading some literature/docs): max_connections = 20 # Default was 100 shared_buffers = 512MB # min 128kB # Default was 32MB work_mem = 256MB # min 64kB default was 1MB wal_buffers = 1MB# min 32kB default was 64kb checkpoint_segments = 30 # in logfile segments, min 1, 16MB each; default was 3 checkpoint_timeout = 30min # range 30s-1h; default was 3 effective_cache_size = 1GB # Default 128MB I understand that tuning is a very database specific issue, but even some general pointers would help me... Many thanks! Dario -- Dr. Dario Beraldi Institute of Evolutionary Biology University of Edinburgh West Mains Road Edinburgh EH9 3JT Scotland, UK -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
