Thread: Setting up Postgresql on Linux

Setting up Postgresql on Linux

From
phil campaigne
Date:
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


Re: Setting up Postgresql on Linux

From
Ron St-Pierre
Date:
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> 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