How to connect to postgresql database using shell script

Question

I want to write a shell script to run these commands. I usually connect from terminal using commands as below

    //first go to the directory 
     cd /opt/novell/sentinel/3rdparty/postgresql/bin/
    // then type following
     export LD_LIBRARY_PATH=/opt/novell/sentinel/3rdparty/postgresql/lib/
    // then fire following command 
     ./psql --host 127.0.0.1 --port 5432 --dbname=SIEM --username=dbauser
     Password for user dbauser: ****

Answer 1

Why don't you update your PATH and export LD_LIBRARY_PATH permanently, by adding to your .profile these lines:

PATH=/opt/novell/sentinel/3rdparty/postgresql/bin/:$PATH
export LD_LIBRARY_PATH=/opt/novell/sentinel/3rdparty/postgresql/lib/

Then use the script to connect DB as simple as following

#!/bin/sh
psql --host=127.0.0.1 --port=5432 --dbname=SIEM --username=dbauser

After you run the script, you will be asked about the password.

If you would like not to enter password every time, you can use the password file .pgpass (see documentation for details), just add to your ~/.pgpass the following line:

127.0.0.1:5432:SIEM:dbauser:your_password

Be safe, dissallow any access to world or group:

chmod 0600 ~/.pgpass.

After this, you can connect to your db by using script above without password prompt.