Executing postgres script using jenkins

Question

I am trying to execute postgres script using jenkis on remote server. Below are content of shell script. I am using publish over SSH plugin.

sudo -u postgres psql

CREATE USER sonar WITH PASSWORD 'sonar';
\du
CREATE DATABASE sonar WITH OWNER sonar encoding 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE sonar TO sonar;
\l
\q

1) I can see contents are printed in terminal but not executed.

Solution I tried:

I created my scripts like

#!/bin/bash
sudo -h myip -u postgres bash -c "psql -c \"CREATE USER sonar WITH PASSWORD 'sonar';\""
sudo -h myip -u postgres bash -c "psql -c \"CREATE DATABASE sonar WITH 
OWNER sonar encoding 'UTF8';\""
sudo -h myip -u postgres bash -c "psql -c \"GRANT ALL PRIVILEGES ON DATABASE sonar TO sonar;\""

This script is working fine but I have to connect remote server multiple times. I want script to connect once and execute all psql queries.

2) I am also looking for dev and staging environment executed 1st and then production. Can we manage Plugin in this way?

YuriR · Accepted Answer · 2017-09-26 13:04:55Z

1

you can create file with all queries, lets say my_queries.sql and then run it like this:

sudo -h myip -u postgres bash -f my_queries.sql

my_queries.sql content:

CREATE USER sonar WITH PASSWORD 'sonar';
CREATE DATABASE sonar WITH OWNER sonar encoding 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE sonar TO sonar;

answered Sep 26, 2017 at 13:04

YuriR

1,3213 gold badges14 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Executing postgres script using jenkins

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related