As many of us have anticipated, Josh Berkus has, with Shridhar Daithankar's help put together two marvelous documents to help everyone sort out the details of the postgresql.conf file and the GUC (Global User Configuration) options.
The first document, Tuning PostgreSQL for Performance is a set of guidelines and questions to ask when preparing to tune an installation.
In the second document, The Annotated postgresql.conf, the elements of the postgresql.conf file are nicely organized into functional groups. Each item is documented with its range, default value, corresponding variable and -o option and is described in detail. Comments on most items also give you a clearer understanding of how and when to set these variables and what impact should be expected.
Both of these documents are required reading for anyone who plans to set up a production installation of PostgreSQL.
In order to search data ignoring accents, you must convert the data to ASCII.
A table containing the following data and selected with the usual LIKE case, then you would only get "Polo".
-------- Colón Polo -------- SELECT * FROM testtable WHERE testfield like '%olo%';If you selected from the table converting to ASCII you would get both values:
SELECT * FROM testtable WHERE to_ascii(testfield,'LATIN1') LIKE '%olo%'This solution has not been tested against a database with UNICODE encoded and one response warned that it may not work in that case.
For a table with a three part key, the question was raised whether a functional index on the concatenated fields or a multi-part index might be faster. The indexes would be:
create index threepart on table foo (col_one, col_two, col_three); create index catthree on table foo ( cattext3(col_one, col_two, col_three));where cattext3() is a function which concatenates and returns its three arguments.
It is agreed that the three part index is better than the functional index for several reasons. Particularly if the "least non-unique" value is the leftmost in the index definition, the three part index will be faster.
The multi-part indexes in general are much more flexible as well. Partial index searches can use multi-part indexes. For example, if you were to search only on col_one the first part of the multi-part index would be used. The multi-part index would not be used for col_two without being preceded with col_one, but that would not work in the functional index either.
In a related question, if the table had the threepart index and an index on col_one alone, the index on col_one alone would be redundant. There is no need to create or store the second index when the first will do the work.
When installing languages, like plpgsql in a database, the system checks whether the directory $libdir exists. $libdir is a PostgreSQL representation of the directory specified as the path to the shared objects library when PostgreSQL was configured.
If you configured with --prefix=/local/pgsql73, for example,
the string $libdir is expanded to /local/pgsql73/lib.
$libdir's value is usually the result of the cascading defaults
of --prefix=PREFIX --exec-prefix=EPREFIX and --libdir=dir.
The location of $libdir is also pertinent when writing C functions. When you define a C function in SQL, you must specify the location of the shared object (.so) of your function. You can use $libdir literally in the SQL definition to signify that your shared object is in your installation's definition of $libdir
CREATE FUNCTION hello_cstr() RETURNS cstring AS '$libdir/hello_cstr.so' LANGUAGE 'c';
It is not required that $libdir be used for C function shared objects. It is also possible to specify a full path name for development or for storing shared objects in an alternate directory.
Having two separate installations on the same machines is fairly straightforward. With the beta of 7.4 coming up, many people will want to keep their existing installations and still be able to test a 7.4 installation.
There are three key elements in creating separate installations, location of the installation, location of the data directory and client access. The location of the installation is determined at configuration time by setting the prefix of the build to be in a specified directory instead of the default directory. The location of the data directory is determined with initdb. The client access is determined by setting the appropriate PG variables and ensuring the appropriate installation's bin is earlier in your PATH than the other one.
To build a 7.4 installation into /local/pgsql74 one would use:
./configure --prefix=/local/pgsql74 ...When the install is performed all of the pieces of the installation go there.
Next you need to determine where the data directory for the installation will reside. My own preference is the directory PREFIX/data so that it is not in the database superuser's home directory and so that it will not be confused with any other installation. To set up the data directory, then, use:
initdb -D /local/pgsql74/data
In Issue 12 a convention for a user environment set up was described. It consisted of an environment file called pgenv which is sourced in the client environment to set the default PG environment variables. PG_INST is not a regular PG variable; it was created for convenience.
PG_INST=/local/pgsql74 PGDATA=$PG_INST/data PGPORT=5433 PGLIB=$PG_INST/lib PATH=$PG_INST/bin:$PATH PGHOST=cookie PGDATABASE=production export PG_INST PGDATA PGHOST PGPORT PATH PGLIB PGDATABASE
As described previously these variables can be set in a user's .profile or in /etc/profile to set the default installation on a system. Alternatively, two versions of the file can be made available, perhaps called pgenv73 and pgenv74 so that when switching from one to the other is only a matter of sourcing the correct file.
Note that this pgenv file changes your PATH. This is important, particularly if one of your installations is in /usr/local/bin. You must be sure that the executables for the installation of choice come before others on your path.
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009