LexDbPsqlInitialize

HOW TO initialize PostgreSQL server

• [http://www.postgresql.org/download/ PostgreSQL server] version 8.0 or above must be running either on the local machine or remotely over a TCP/IP port. You should set the database locale to C; eg. use the --locale=C option when initializing the server with initdb, or select the C locale when prompted by the M$ Windows installer.

• Create user accounts.

• Configure access privileges.

• By default the server will run on port 5432. To use another port set the PSQL [http://www.postgresql.org/docs/7.4/interactive/libpq-envars.html environment variable] PGPORT.

• Restart PostgreSQL server.

Notes:

• Selecting a non-C locale hurts performance by disabling index optimizations for certain operators -- see http://www.postgresql.org/docs/8.0/static/charset.html. The environment variables LC_COLLATE and LC_CTYPE are recorded during server initialization and automatically adopted at server startup. It is perfectly safe to later select a different (non-C) locale when creating a database within the server.

Create User Accounts

• Ensure there exists a database user postgres with superuser privileges.

• As the database superuser, create a database user lexdb who will manage the database:

  • {{{ $ createuser -U postgres --createdb --no-adduser lexdb

CREATE USER}}}

• [Optional] If you wish to use password authentication add the -P option.

• As the database superuser, create a user account for yourself (substitute your shell login for USERNAME below):

  • {{{ $ createuser -U postgres --no-createdb --no-adduser USERNAME

CREATE USER}}}

• [Optional] If you wish to use password authentication add the -P option.

Configure Access Privileges

PostgreSQL server running on local machine

• Ensure the [http://www.postgresql.org/docs/8.1/interactive/client-authentication.html#AUTH-PG-HBA-CONF access privilege configuration file] pg_hba.conf is set appropriately. E.g.

  • {{{# TYPE DATABASE USER CIDR-ADDRESS METHOD

local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust}}}

• [Optional] If you wish to use password authentication replace trust with md5.

PostgreSQL server on remote machine

• Allow remote access to the server by adding lines of the following form to your pg_hba.conf file (replace IP_ADDRESS with the actual numbers):

  • host    all         all         IP_ADDRESS/32          md5

  • It is advisable to require password authentication (ie. md5 instead of trust) for remote access.

• On a PostgreSQL 8 server, remote machines will not be able to connect unless you modify listen_addresses in the postgresql.conf file. (The value takes the form of a comma-separated list of host names and/or numeric IP addresses.)