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?

why not use fabric which is ideal for this sort of thing?

e4c5
– e4c5

2015-09-05 01:32:02 +00:00
Commented Sep 5, 2015 at 1:32 — e4c5
– e4c5, Commented Sep 5, 2015 at 1:32

Craig Ringer · Accepted Answer · 2015-09-05 02:55:56Z

4

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.

answered Sep 5, 2015 at 2:55

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

geotheory Over a year ago

Should've mentioned I'm calling the script as root, but I'll check this out

Yordan Georgiev · Accepted Answer · 2017-09-16 19:45:25Z

#!/bin/bash

# run as root
[ "$USER" = "root" ] || exec sudo "$0" "$@"

echo "=== $BASH_SOURCE on $(hostname -f) at $(date)" >&2
sudo passwd postgres

echo start the postgres
sudo /etc/init.d/postgresql start


sudo su - postgres -c \
"psql <<__END__

SELECT 'crate the same user' ;
    CREATE USER $USER ;
    ALTER USER $USER CREATEDB;

SELECT 'grant him the priviledges' ;
    grant all privileges on database postgres to $USER ;
    alter user postgres password 'secret';

SELECT 'AND VERIFY' ;
    select * from information_schema.role_table_grants
    where grantee='""$USER""' ;

SELECT 'INSTALL EXTENSIONS' ;
    CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";
    CREATE EXTENSION IF NOT EXISTS \"pgcrypto\";
    CREATE EXTENSION IF NOT EXISTS \"dblink\";

__END__
"

sudo /etc/init.d/postgresql status
sudo netstat -tulntp | grep -i postgres

Collectives™ on Stack Overflow

Scripting automated postgres setup

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related