Scripting automated postgres setup

Question

I'm scripting a system setup and already have postgres installed. Here is a test script (run as root) to try and report the working directory in postgres. Calling pwd as postgres gives /var/lib/postgresql. But the test..

#!/bin/bash
su - postgres
pwd > /home/me/postgres_report
exit

.. fails (obviously) and reports the original working directory. And afterwards the bash shell is stuck in postgres, suggesting the commands are not being called in right order. I understand the bash environmental issues here. I don't have a clue how to do what I need to do, which is automate a postgres process that I can easily do interactively (i.e. step into postgres, execute a command, and exit). Any pointers?

Answer 1

Use sudo.

Use one of:

• Passing a one line command to psql

  sudo -u postgres psql -c "SELECT ..."`
  

• A here document:

  sudo -u postgres psql <<"__END__"
  SELECT ...;
  SELECT ...;
  __END__
  

  (If you want to be able to substitute in shell variables leave out the ", e.g. <<__END__, and backslash escape $ signs you don't want to be variables)

• Pass a file to psql

   sudo -u postgres psql -f /path/to/file
  

The sudo -u postgres is of course only required if you need to become the postgres system user to run tasks as the postgres database user via peer authentication. Otherwise you can use psql -U username, a .pgpass file, etc.