PostgreSQL and ListManager (Linux)

In order to use ListManager with PostgreSQL, you will first need to download it from one of the postgresql.org mirror sites and install it.

Note: Although ListManager may use PostgreSQL, Lyris does not support the PostgreSQL database itself. These instructions are provided for the convenience of those using ListManager with PostgreSQL. For complete PostgreSQL documentation, go to http://www.postgres.org.

IMPORTANT NOTICE

ListManager will NOT work with versions of PostgreSQL older than 7.4, and we do not recommend using it with newer versions.

If you are upgrading from an older version of ListManager, you will need to upgrade your PostgreSQL server to version 7.4. You may find the Backup and Restore chapter (particularly the Migration Between Releases section) of the PostgreSQL Administrator's Guide helpful.

If you are upgrading to a version 7.4.x version of PostgreSQL, see Upgrading to PostgreSQL 7.4 for more information.

The instructions here are a general guide to installing PostgreSQL both with RPM and without RPM. They assume that the required GNU compiler, tools, and libraries are already present on your system. Users of Red Hat Linux may find it easier to follow our instructions for installing PostgreSQL using RPM.

The main USA download location for PostgreSQL is http://www.postgresql.org/

Mirror sites (with greater capacity) are listed at http://www.postgresql.org/mirrors-ftp.html .

Documentation for PostgreSQL can be found at http://www.postgresql.org/docs/ .

You may also find our tuning tips helpful.

Installing PostgreSQL Without RPM

After downloading PostgreSQL, log in as root and execute the following commands to install PostgreSQL:

1. As the root user, untar PostgreSQL, compile the program, and install it:

gunzip postgresql-7.4.tar.gz
tar xf postgresql-7.4.tar
cd postgresql-7.4
./configure && make && make install

2. Add the following lines to the end of /etc/profile :

POSTGRESHOME=/usr/local/pgsql
MANPATH=$POSTGRESHOME/man:$MANPATH
PATH=$POSTGRESHOME/bin:$PATH
PGDATA=$POSTGRESHOME/data
LD_LIBRARY_PATH=$POSTGRESHOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH MANPATH PATH PGDATA

3. Create a data directory, and a new user postgres who will own it:

mkdir /usr/local/pgsql/data
useradd -m postgres
chown postgres /usr/local/pgsql/data

4. Become the postgres user:

su - postgres

5. Make sure your environment variables are set:

env | grep -i pg

You should see that PGDATA and other variables now include /usr/local/pgsql.)

6. Initialize the data directory:

initdb --lc-collate=C

The option shown here favors optimal database performance, rather than locale-specific sorting rules. If you are working with languages other than English, you may want to consult the Localization chapter of the PostgreSQL Administrator's Guide.)

7. Replace or add the following lines in /usr/local/pgsql/data/postgresql.conf :

syslog = 2
tcpip_socket = true

(The options shown here send log messages to the syslog facility, and allow database connections over TCP/IP sockets. Note that if you want the database to be accessible over the network, or if you have untrusted users on the computer that runs PostgreSQL, editing pg_hba.conf may be necessary. Consult the PostgreSQL Administrator's Guide for detailed configuration options.)

8. Start the database server:

pg_ctl start

9. Create a PostgreSQL user for ListManager to use, and assign it a password. We use "lmuser"; please select your own user name:

createuser -A -D -P lmuser
Enter password for user "lmuser":

10. Create a database for ListManager to use. We use "lmdatabase"; please select your own database name:

createdb --owner lmuser lmdatabase

11. Leave your postgres user session, and leave your root session, so that your new settings will take effect next time you log in:

exit
exit

12. Log back in as root, and run the installer for ListManager. The user, password, and database will be those which you chose in the steps above. We recommend you select a user, password, and database different from those used in the examples.

Installing PostgreSQL Using RPM

After downloading PostgreSQL, log in as root and execute the following commands to install PostgreSQL:

1. As the root user, install the RPM files that were downloaded from postgresql.org:

rpm -ih postgresql-libs-7.4-1PGDG.i386.rpm
rpm -ih postgresql-7.4-1PGDG.i386.rpm
rpm -ih postgresql-server-7.4-1PGDG.i386.rpm

The file names shown here represent those that were current at the time of this writing. The files you download may have slightly different names.

2. Become the postgres user:

su - postgres

3. Initialize the data directory:

initdb --lc-collate=C

4. Add or replace the following lines in /var/lib/pgsql/data/postgresql.conf :

syslog = 2
tcpip_socket = true

The options shown here send log messages to the syslog facility, and allow database connections over TCP/IP sockets. Note that if you want the database to be accessible over the network, or if you have untrusted users on the computer that runs PostgreSQL, editing pg_hba.conf may be necessary. Consult the PostgreSQL Administrator's Guide for detailed configuration options.

5. Start the database server:

pg_ctl start

6. Create a PostgreSQL user for ListManager to use, and assign it a password. We use "lmuser"; please select your own user name:

createuser -A -D -P lmuser
Enter password for user "lmuser":

7. Create a database for ListManager to use. We use "lmdatabase"; please select your own database name:

createdb --owner lmuser lmdatabase

8. Leave your postgres user session, becoming root again:

exit

9. Run the installer for ListManager. The user, password, and database will be those which you chose in the steps above. We recommend you select a user, password, and database different from those used in the examples.

In order to use ListManager with PostgreSQL you'll need to first download it from http://www.ca.postgresql.org/ftpsite/source/. We do not recommend using ListManager with higher versions of PostgreSQL. The instructions here are a general guide to installing PostgreSQL.

Installing PostgreSQL

After downloading PostgreSQL, log in as root and execute the following commands to install PostgreSQL:

1. As root, untar PostgreSQL, compile the program and create a user postgres:

tar xzf postgresql-7.4.tar.gz
cd postgresql-7.4
./configure && make && make install
adduser postgres
cd /usr/local/pgsql/
mkdir data logs
chown postgres data logs

2. Then, put the following items into ~postgres/.bash_profile :

POSTGRESHOME=/usr/local/pgsql
export MANPATH=$MANPATH:$POSTGRESHOME/man
export PATH=$PATH:$POSTGRESHOME/bin
export LD_LIBRARY_PATH=$POSTGRESHOME/lib
export PGDATA=$POSTGRESHOME/data

Tip: PGHOST and PGPORT can be set to override defaults (localhost)

3. Next, add this line to /etc/profile:

export LD_LIBRARY_PATH=/usr/local/pgsql/lib

4. Change to user postgres

su - postgres

5. Make sure your environmental variables are set by running:

env | grep -i pg

You should see that PGDATA and other variables have the /usr/local/pgsql

6. Initialize and start the database by running the following:

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -i 1> /usr/local/pgsql/logs/log.txt 2>&1 &

(The -i option specifies using TCP/IP sockets, rather than Unix domain sockets)

7. Now, create a user for ListManager to use. We use "lmpg"; please select your own user name:

../bin/createuser --adduser
Enter name of user to add: lmpg
Shall the new user be allowed to create databases? (y/n) y

8. Run psql, and create a database for Listmanager to use. We use "lmpg"; please select your own database name:

psql -U lmpg template1
create database lmpg;
\q

9. Run psql again, and remove database creation rights from user lmpg (use a different password):

psql template1
alter user lmpg with password 'lmpg' nocreatedb;
\q

10. Run the installer for ListManager. With the settings above, the database, user, and password will all be "lmpg". We recommend you select a different database, user and password for your installation.