creating database in remote server

Question

I want to install a postgresql database on a remote linux server. In order to create the database on the remote server, I wrote a script in a python that executes the commands on the server. I am running the commands in sequential order in like this:

cmd = "wget http://yum.pgrpms.org/reporpms/8.4/pgdg-centos-8.4-2.noarch.rpm"
execute cmd

cmd = "rpm -Uvh http://yum.pgrpms.org/reporpms/8.4/pgdg-centos-8.4-2.noarch.rpm"
execute cmd


cmd = "yum install postgresql postgresql-server"
execute cmd

...and so on.

Everthing works fine utill the installation of postgres and starting the database. The problem arises when I want to create a database in postgreql. I found that I have to switch to the 'postgres' user and run psql in order to execute any database related commands. For example:

# su postgres
# psql
# create database test;

...but since I am passing all of the commnads sequentially through the remote python code (as shown in the first example), it seems quite impossible to run these three commands together (su postgres, and psql and create) from the client. Is there any way to run all three commands together?

I run following command:

# su postgres && psql && create database test;

...but only the first comamnd (su postgres) is executed.

Answer 1

try this, I did not test it

su - postgres -c 'psql -c "create database test"'

So you are suing as the postgres user and running the psql command with the create database command.

Answer 2

Try to use psql in non-interactive mode. For example:

psql -c "create database test"

Another way is to use createdb wrapper. Check http://www.postgresql.org/docs/8.4/static/app-createdb.html

As Mike wrote to run command as another user (postgres in this case) use su with -c option.

Answer 3

The other answers deal very well with the solution to the problem you had with this particular command line, but if you're doing work on remote hosts from Python perhaps you should take a look at Paramiko. Here's a howto.

Another alternative would be Fabric.