在sql postgres中切换数据库

Question

我正在尝试创建一个POSTGRES db镜像，在启动时初始化我所有的数据库和模式，如下所示。

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"

在这里的第二个sql文件中，我正在尝试创建另一个数据库，并初始化所有需要的表，如下所示，

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
);

这里的问题是，即使正在创建第二个数据库，我也不能在其中指定表，并且这个表TB1是在第一个数据库'PGIAM‘中创建的。有没有办法解决这个问题？

Answer 1

我将sql文件更改为shell，并且可以运行\c命令来切换数据库，如下所示：

    #!/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