Thread: Create schema with in a specific database from a script file

Create schema with in a specific database from a script file

From

Abhra Kar

Date:

02 February 2018, 08:09:00

Hi,

I have to write script for psql in a xyz.sh file which should create a schema in a specific data.In psql prompt I achieve it like --

postgres=# \c ABC

ABC=# create schema authorization myschema

In xyz.sh I executed the following script ---

su -c "psql -c \"\c ABC \"" postgres

su -c "psql -c \"create schema authorization myschema\"" postgres

In the terminal got message “connected to ABC database”. But schema created with in postgres database not with in ABC database.

What should be the proper script ?

Thanks and Regards,

Abhra

Re: Create schema with in a specific database from a script file

From

Melvin Davidson

Date:

02 February 2018, 08:18:06

On Thu, Feb 1, 2018 at 9:09 PM, Abhra Kar <abhra.kar@gmail.com> wrote:

Hi,
I have to write script for psql in a xyz.sh file which should create a schema in a specific data.In psql prompt I achieve it like --

postgres=# \c ABC
ABC=# create schema authorization myschema

In xyz.sh I executed the following script ---
su -c "psql -c \"\c ABC \"" postgres
su -c "psql -c \"create schema authorization myschema\"" postgres

In the terminal got message “connected to ABC database”. But schema created with in postgres database not with in ABC database.
What should be the proper script ?

Thanks and Regards,
Abhra

> But schema created with in postgres database not with in ABC database.

Just specify the database in the command line:

EG: su -c "psql -d ABC"

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: Create schema with in a specific database from a script file

From

Abhra Kar

Date:

02 February 2018, 08:42:33

sudo -u postgres createdb ABC

su -c "psql -d ABC"

if [ $3 == 'xxxxx' ]

then

su -c "psql -c \"grant all privileges on database ABC to xxxx\"" postgres

su -c "psql -c \"create schema authorization xxxx\"" postgres

else

su -c "psql -c \"grant all privileges on database ABC to yyyy\"" postgres

su -c "psql -c \"create schema authorization xxxx\"" postgres

Hi Melvin,

I executed the above script but xxxx schema created with in postgres db not with in ABC db. And in terminal didn't show the message connected to ABC database. What is wrong I am doing here?

Regards,

Abhra

On Fri, Feb 2, 2018 at 7:48 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Thu, Feb 1, 2018 at 9:09 PM, Abhra Kar <abhra.kar@gmail.com> wrote:
Hi,
I have to write script for psql in a xyz.sh file which should create a schema in a specific data.In psql prompt I achieve it like --

postgres=# \c ABC
ABC=# create schema authorization myschema

In xyz.sh I executed the following script ---
su -c "psql -c \"\c ABC \"" postgres
su -c "psql -c \"create schema authorization myschema\"" postgres

In the terminal got message “connected to ABC database”. But schema created with in postgres database not with in ABC database.
What should be the proper script ?

Thanks and Regards,
Abhra

> But schema created with in postgres database not with in ABC database.
Just specify the database in the command line:
EG: su -c "psql -d ABC"

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: Create schema with in a specific database from a script file

From

Melvin Davidson

Date:

02 February 2018, 08:53:16

On Thu, Feb 1, 2018 at 9:42 PM, Abhra Kar <abhra.kar@gmail.com> wrote:

sudo -u postgres createdb ABC
su -c "psql -d ABC"
if [ $3 == 'xxxxx' ]
then
su -c "psql -c \"grant all privileges on database ABC to xxxx\"" postgres
su -c "psql -c \"create schema authorization xxxx\"" postgres
else
su -c "psql -c \"grant all privileges on database ABC to yyyy\"" postgres
su -c "psql -c \"create schema authorization xxxx\"" postgres
fi

Hi Melvin,
I executed the above script but xxxx schema created with in postgres db not with in ABC db. And in terminal didn't show the message connected to ABC database. What is wrong I am doing here?

Regards,
Abhra

On Fri, Feb 2, 2018 at 7:48 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Thu, Feb 1, 2018 at 9:09 PM, Abhra Kar <abhra.kar@gmail.com> wrote:
Hi,
I have to write script for psql in a xyz.sh file which should create a schema in a specific data.In psql prompt I achieve it like --

postgres=# \c ABC
ABC=# create schema authorization myschema

In xyz.sh I executed the following script ---
su -c "psql -c \"\c ABC \"" postgres
su -c "psql -c \"create schema authorization myschema\"" postgres

In the terminal got message “connected to ABC database”. But schema created with in postgres database not with in ABC database.
What should be the proper script ?

Thanks and Regards,
Abhra

> But schema created with in postgres database not with in ABC database.
Just specify the database in the command line:
EG: su -c "psql -d ABC"

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

> I executed the above script but xxxx schema created with in postgres db not with in ABC db ...

1. Please do not top post. The convention is to bottom post in this list.

2. You code is not a proper bash script. I suggest you google how to write bash scripts as this list is

is for PostgreSQL help, not bash

3. You are just submitting MULTIPLE psql commands. You need to just start the psql session, then

submit all additional commands inside the psql session.

EG: psql -d somedb
grant all privileges on database ABC to xxxx ;
....

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Create schema with in a specific database from a script file

From

"David G. Johnston"

Date:

02 February 2018, 08:56:45

On Thursday, February 1, 2018, Abhra Kar <abhra.kar@gmail.com> wrote:

In xyz.sh I executed the following script ---
su -c "psql -c \"\c ABC \"" postgres
su -c "psql -c \"create schema authorization myschema\"" postgres

In the terminal got message “connected to ABC database”. But schema created with in postgres database not with in ABC database.

Right. psql connected to the Postgres database, executed \c ABC which caused the session to connect to ABC. Then, seeing no other commands, that psql session disconnected. Then a brand new session was opened to the postgres database and in that new session created the schema.

Every psql command in a script is independent of all others, there is no state maintained.

For psql scripts in bash I find:

psql <<SQL

SQL

To be the better form than trying to use -c. Or put the psql code in its own file and use -f (really preferred though more files and navigation to deal with).

And you should probably try to avoid using "su" as a crutch to make things work. Invoking commands as root is dangerous.

David J.