switch database in sql postgres

Question

I am trying to create a POSTGRES db image with all my databases and schemas being intialized on start up as follows,

version: '3.8'
services:
  iamstorepg:
    image: library/postgres:10
    environment:
      - POSTGRES_DB=pgiam
      - POSTGRES_USER=iam
      - POSTGRES_PASSWORD=iam
    ports:
      - 5432:5432
    restart: always
    volumes:
      - "./schema1.sql:/docker-entrypoint-initdb.d/1-schema.sql"
      - "./schema2-db.sql:/docker-entrypoint-initdb.d/2-schema.sql"

here in 2nd sql file i am trying to create another DB and intialize it with all the needed table as follows,

CREATE DATABASE DB1
    WITH
    OWNER = iam
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

GRANT ALL PRIVILEGES ON DATABASE DB1 to iam;
/*\connect DB1;

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO iam;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO iam;*/
---

-- Drop table
DROP TABLE IF exists DB1.public.user_involvements;
----- New proposition user-rol -> role-resource relationship one to many
-- Create User-roles table (authorisations or involvements , however you would like to call it)
CREATE TABLE pgiamrolestore.public.tb1(
    id uuid NOT NULL,
    path varchar(255) NOT NULL,
    code varchar(255) not null,
    is_active bool NOT null default true
);

problem here is that even though second database is being created, i am not able to specified table in it and this table TB1 is being created in 1st DB 'PGIAM'. is there a way i can resolve this?

You need to connect to the new database. Are you running your commands using psql? Then give the name of new database on command line or use command \connect — Vesa Karjalainen
– Vesa Karjalainen, Commented Aug 12, 2020 at 9:29

Rajesh Rai · Accepted Answer · 2020-08-12 11:03:29Z

I changed my sql file to shell and i am able to run \c command to switch database as follows,

    #!/bin/bash
    set -e
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
        CREATE DATABASE DB1
            WITH
            OWNER = iam
            ENCODING = 'UTF8'
            CONNECTION LIMIT = -1;
        
        GRANT ALL PRIVILEGES ON DATABASE DB1 to iam;
        /*\connect DB1;
        
        DROP SCHEMA public CASCADE;
        CREATE SCHEMA public;
        GRANT ALL ON SCHEMA public TO iam;
        GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO iam;*/
        ---
        
        -- Drop table
        DROP TABLE IF exists DB1.public.user_involvements;
        ----- New proposition user-rol -> role-resource relationship one to many
        -- Create User-roles table (authorisations or involvements , however you would like to call it)
        CREATE TABLE pgiamrolestore.public.tb1(
            id uuid NOT NULL,
            path varchar(255) NOT NULL,
            code varchar(255) not null,
            is_active bool NOT null default true
        );
    EOSQL

Collectives™ on Stack Overflow

switch database in sql postgres

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