PostgreSQL create a schema under a different database using a script

Question

So what I want to do here is to run a script while connected to a database I already had using pgAdmin3. The script contains a create role, tablespace, database and a create schema and several tables under that schema.

The problem here is that when I run the script it creates the new role, tablespace and database correctly. It also creates the schema and the tables correctly but with a problem, the schema is created under the database, from which I ran the script, instead of the newly created database. The script is more or less like this.

CREATE ROLE "new_role" ... ;

CREATE TABLESPACE "new_space"
  OWNER "new_role"
  LOCATION '/home/...';


CREATE DATABASE "new_db"
    WITH OWNER = "new_role"
            TABLESPACE = "new_space";

CREATE SCHEMA "schema" AUTHORIZATION "new_role" ;

CREATE TABLE IF NOT EXISTS "schema"."new_table"( 

...  
) TABLESPACE "new_space";...

...

I already saw a solution with a \connect foo; but that is not what I wanted, I wanted it to somehow connect within the script without running things separately and running \connect foo in the terminal.

Can anyone tell me if there is anyway to do this and help me come out with a solution to this problem?

khampson · Accepted Answer · 2015-03-22 19:04:05Z

2

Use psql and split it up into two scripts . You can save the scripts in .sql files, and then run psql to connect to the DB you want to run each script against all on the same command line (with && in between each command). The two psql commands could be combined into one bash script so it's only one command that you need to run.

Something like this, if the script were named foo.sql:

psql -X -h <host> -U <user> -p <port> -f foo.sql <db_name>

The first script could have the create role, create tablespace and create database commands, connecting to the postgres db or a template DB, and the second script could have the rest of the commands.

You could also use createdb from the bash script instead of CREATE DATABASE.

answered Mar 22, 2015 at 19:04

khampson

15.5k4 gold badges45 silver badges44 bronze badges

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

Comments

Jorge · Accepted Answer · 2021-01-22 15:45:36Z

0

Using pgAdminIV:
1- right click on default database "postgres"
2- select create database, give a name f.e. "newdatabase"
3- click on "newdatabase" (to establish connection)
4- open the query tool
5- import, write or paste your code
6- run your code f.e.: CREATE SCHEMA newschema;

It works for me...

edited Jan 22, 2021 at 15:45

answered Jan 22, 2021 at 15:07

Jorge

1015 bronze badges

Collectives™ on Stack Overflow

PostgreSQL create a schema under a different database using a script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related