Re: Setting up Postgresql on Linux

phil campaigne wrote:

> Tom Lane wrote:
>
>> phil campaigne <pcampaigne(at)charter(dot)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(at)hardwoodthunder phil]$ which psql
> /usr/bin/psql
>
> [phil(at)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> and Ant
<http://lanfear.syscor.priv/installs/ant/index.html> 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