时间戳

首页 > 解决方案 > 在sql postgres中切换数据库

postgresql - 在sql postgres中切换数据库

问题描述

我正在尝试创建一个 POSTGRES 数据库图像，其中我的所有数据库和模式在启动时都被初始化如下，

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”中创建。有没有办法解决这个问题？

标签： postgresqldocker

解决方案

我将我的 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

推荐阅读