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
Earlier versions of ListManager supported PostgreSQL 7.1.3 only. To upgrade ListManager and have it run properly, you will need to upgrade your version of PostgreSQL.

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

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.

Note: The database must use the SQL_ASCII character set. By default, PostgreSQL uses UNICODE. Please be sure ListManager's database is set to use the correct character set.

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-x.x.tar.gz
tar xf postgresql-x.x.tar
cd postgresql-x.x
./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 -E SQL_ASCII --owner lmuser lmdatabase

11. ListManager 9.x requires the plpgsql procedural language to be enabled on the PostgreSQL database. If the language has not yet been enabled please run the following command:

createlang plpgsql DBNAME

12. 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

13. 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-x.x-1PGDG.i386.rpm
rpm -ih postgresql-x.x-1PGDG.i386.rpm
rpm -ih postgresql-server-x.x-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. ListManager 9.x requires the plpgsql procedural language to be enabled on the PostgreSQL database. If the language has not yet been enabled please run the following command:

createlang plpgsql DBNAME

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

exit

10. 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.

Database Server Password Security

As the database password is saved unencrypted in a file (lmcfg.txt), you may later remove the password saved in this file and then specify that the password be provided on startup by altering this file.