PostgreSQL

Windows and Linux Binaries: http://www.postgresql.org/ftp/binary/

Installation on Windows

Follow the instructions here.

NOTE: Do not install the PostGIS extensions from the PostgreSQL installer. Follow the instructions at PostGIS for installing PostGIS functionality.

Installation on Linux

This version of the install is the install based on using the .rpms from the PostgreSQL site.

Step 1:

At minimum, you will want to have the following .rpms:

  • postgresql-libs-#.#.#-1PGDG.i686.rpm
  • postgresql-#.#.#-1PGDG.i686.rpm
  • postgresql-server-#.#.#-1PGDG.i686.rpm

Download them from the link at the top of this page for the your distribution.

Step 2:

Install the .rpms using the following commands. Make certain to install them in the correct order:

#> rpm -ivh postgresql-libs-#.#.#-1PGDG.i686.rpm
#> rpm -ivh postgresql-#.#.#-1PGDG.i686.rpm
#> rpm -ivh postgresql-server-#.#.#-1PGDG.i686.rpm

Step 3:

Issue the following commands to set the password on the postgresuser, and to initialize the database space:

#> service postgresql initdb
#> passwd postgres

Step 4:

Change the following lines in /var/lib/pgsql/data/pg_hba.conf:

From:

local    all    all    ident sameuser
host     all    all    127.0.0.1/32    ident sameuser

To:

local    all    all    trust
host     all    all    127.0.0.1/32    trust

Step 5:

Uncomment the following lines in /var/lib/pgsql/data/postgresql.conf to allow JDBC connections over port 5432

listen_addresses = 'localhost'
port = 5432

Step 6:

Lastly, we should also run the following to have postgres start on startup:

#> chkconfig postgresql on

Caching Passwords to Allow Command-Line Scripts to Run Without Requiring Manual User Intervention

We have had to do this only in Windows so far, so this is the Windows implementation of caching of the passwords.

Step 1:

Go to the user’s directory whom you are logged in as, and go to C:\Documents and Settings\<username>\Application Data, and create a directory called postgresql

Step 2:

Add a file called pgpass.conf with the following entries (as many entries as you have databases):

localhost:5432:<databaseName>:<databaseUserName>:<password>

Step 3:

Change the permissions to 600 using cygwin:

#> chmod 600 pgpass.conf

NOTE: If you do not do this step, it will notread this file. If you get prompted for a password while running a script, it likely means that this file has the wrong permissions or is on the wrong place.

Now any scripts that you run (ant, maven, etc.) can connect to the database without being prompted for the password for the database. For example:

<target name="importDatabase" description="Import the contents of the local database." >
    <echo>Importing data...</echo>
    <exec executable="C:\Program Files\PostgreSQL\8.2\bin\psql.exe" input="${basedir}/${db_dump_local}" >
        <arg line="-U ${user}" />
        <arg line="${database_name}" />
    </exec>
    <echo>Finished.</echo>
</target>

should run without prompting for a password in order to connect to the database.

Installing a database language on Linux

The Linux install of Postgres doesn’t include plpgsql support by default, I had to add it to my instance of Postgres.

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
    '$libdir/plpgsql' LANGUAGE C;

CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS
    '$libdir/plpgsql' LANGUAGE C;

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
    HANDLER plpgsql_call_handler
    VALIDATOR plpgsql_validator;

I swiped the code from this reference.

There are other ways to do this same thing, such as the createlang command.

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply