Setting up Postgresql on Linux

phil campaigne wrote:

Tom Lane wrote:

phil campaigne <pcampaigne@charter.net> writes:

when I login to linux and check the env's I see:
PATH=/usr/local/pgsql/bin:/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin:/home/postgres/bin:/opt/IBMJava2-14/bin:/opt/IBMJava2-14/jre/bin:/usr/local/pgsql/bin

LD_LIBRARY_PATH=/usr/local/pgsql/lib

What are you checking exactly? If it's not the output of "env" then
I would say you are looking at the wrong thing. Possibly you're looking
at a profile file that neglects to "export LD_LIBRARY_PATH", or some
such.

I cannot execute the following:
psql testdb
psql: relocation error: psql: undefined symbol: PQgetssl

This is clearly picking up an incompatible version of libpq.so
(specifically, psql was built with SSL support but libpq.so wasn't).
I'd suggest trying "which psql" and "ldd" on the psql executable
to figure out exactly what's what.

regards, tom lane

I'm not sure how to interpret this,

[phil@hardwoodthunder phil]$ which psql
/usr/bin/psql

[phil@hardwoodthunder phil]$ ldd /usr/bin/psql
libpq.so.3 => /usr/local/pgsql/lib/libpq.so.3 (0x40013000)
libpam.so.0 => /lib/libpam.so.0 (0x4002f000)
libssl.so.2 => /lib/libssl.so.2 (0x40037000)
libcrypto.so.2 => /lib/libcrypto.so.2 (0x40067000)
libkrb5.so.3 => /usr/kerberos/lib/libkrb5.so.3 (0x4013e000)
libz.so.1 => /usr/lib/libz.so.1 (0x4019b000)
libreadline.so.4 => /usr/lib/libreadline.so.4 (0x401a9000)
libtermcap.so.2 => /lib/libtermcap.so.2 (0x401d6000)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x401da000)
libresolv.so.2 => /lib/libresolv.so.2 (0x40207000)
libnsl.so.1 => /lib/libnsl.so.1 (0x40217000)
libdl.so.2 => /lib/libdl.so.2 (0x4022b000)
libm.so.6 => /lib/libm.so.6 (0x4022e000)
libc.so.6 => /lib/libc.so.6 (0x40250000)
libk5crypto.so.3 => /usr/kerberos/lib/libk5crypto.so.3 (0x4036e000)
libcom_err.so.3 => /usr/kerberos/lib/libcom_err.so.3 (0x4037e000)
/lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)

thanks,
Phil

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Do you have two copies of postgres installed on your pc? If your distro
(what is it BTW?) has the following commands, try these (without the
quotes): "updatedb" and then "locate pgsql" to see if you have multiple
copies installed. And/or check out /usr/bin/psql. Is this a file or a
directory?

What is the home directory of postgres? Is it /home/postgres or
/usr/local/pgsql? It should be /usr/local/pgsql.

Final resort, copy your .bashrc and .bash_profile from your home
directory somewhere else for backup, remove postgres and re-install it.
Here is my *complete* install list (open to constructive comments),
(slightly) modified of course from "Practical PostgreSQL":

--------------------------------------------------------------------------------------------------------------------------------

Installing PostgreSQL

1. Ensure that Java
<http://lanfear.syscor.priv/installs/java/index.html&gt; and Ant
<http://lanfear.syscor.priv/installs/ant/index.html&gt; and readline
(ftp://ftp.gnu.org/pub/gnu/readline) are installed first and working
correctly.

2. Install the following packages:
zliblg-dev
libreadline4-dev
libgcrypt-dev
openssl
libssl-dev
3. Create postgres group and user (-m option creates the directory):

# groupadd postgres
# useradd -d /usr/local/pgsql -m postgres
# gpasswd -a postgres postgres

4. Download the following file into /usr/local/src, extract it as root,
and update permissions:

# cd /usr/local/src/
# tar xvzf postgresql-7.x.tar.gz
# chown -R postgres.postgres postgresql-7.x/

5. Ensure the following exists in /usr/local/pgsql/.bash_profile:

PATH=$PATH:/usr/local/java/bin:/usr/local/ant/bin:/usr/local/pgsql/bin:/usr/local/scripts
export JAVA_HOME=/usr/local/java
export ANT_HOME=/usr/local/ant

6. Configure the source tree with the following options and then compile:

# cd postgresql-7.3
# ./configure --enable-multibyte \
--enable-odbc \
--with-java \
--with-maxbackends=96 \
--with-openssl="/usr/bin/openssl" \
--enable-syslog

# make
# cd /usr/local/src/
# chown -R postgres.postgres postgresql-7.x/

7. Regression Testing. This builds a test installation of PostgreSQL and
checks whether all parts of the source are properly compiled.

# su - postgres
$ cd /usr/local/src/postgresql-7.x
$ make check

8. Install (as root):

# cd /usr/local/postgresql-7.x
# make install
# make install-all-headers
# cd /usr/local
# chown -R postgres.postgres pgsql

9. Initialize the database as user postgres:

# su - postgres
# initdb -E UNICODE -D /usr/local/pgsql/data

10. Configure (as root) the PostgreSQL SysV Script. This script is
useful for starting, stopping, and checking the status of PostgreSQL.

# cd /usr/local/src/postgresql-7.x
# cp contrib/start-scripts/linux /etc/init.d/postgres
# chmod 755 /etc/init.d/postgres

To have PostgreSQL start automatically when the computer boots add
symbolic links from the correct /etc/rc*.d/ directories to
/etc/init.d/postgres. If the normal runlevel is 3 then you really only
need to add it to rc3.d:

# ln -s /etc/init.d/postgres /etc/rc2.d/S85postgres
# ln -s /etc/init.d/postgres /etc/rc3.d/S85postgres

etc ...

11. Start PostgreSQL for the first time:

# /etc/init.d/postgres start

12. Create a new db:

# su - postgres
$ createdb testdb
$ psql testdb

13. To shutdown:

# /etc/init.d/postgres stop

Configuration files and the server log are found in /usr/local/pgsql/data

------------------------------------------------------------------------

Server Configuration

Edit file /usr/local/pgsql/data/pg_hba.conf:

#TYPE DATABASE IP_ADDRESS NETMASK AUTHTYPE MAP
host all 127.0.0.1 255.255.255.255 trust
local all trust

Edit file /usr/local/pgsql/data/postgresql.conf:

tcpip_socket = true
shared_buffers = 2048
sort_mem = 32168
syslog=1

------------------------------------------------------------------------

System Logging

Edit the /etc/syslog.conf file to capture PostgreSQL sysloggin calls, by
adding:
local0.* /var/log/postgresql