主页 / dba / 问题 / 280339
Accepted
ConanTheGerbil
Asked: 2020-11-26 10:40:31 +0800 CST

自动创建 postgres 数据库

  • 772

我正在尝试自动创建在 Windows 10 上运行的 postgres 数据库。我希望能够从 postgres 的默认安装开始并自动执行以下步骤（不一定按此顺序）

  • 删除并重新创建新数据库
  • 删除并重新创建新表空间
  • 删除并重新创建新用户。
  • 构建数据库对象（表、视图、过程、触发器等）
  • 授予用户所需的权限。
  • 用初始数据填充表。

将其简化为一系列数据库命令（只要它们以正确的顺序执行）并不难，问题是对于少数命令，我一直遇到错误“无法在事务块内发出”。这意味着一些命令，如 DROP DATABASE IF EXISTS 和 CREATE TABLESPACE 只能通过手动单步执行（据我所知）运行，而我可以将我的表创建脚本作为一个块调用并重新创建一个100 张桌子在几分之一秒内完成。

我不太担心单步执行一些单独的命令会浪费额外的几秒钟，但是通过手动执行其中的一些操作，我冒着丢失步骤和出错的风险。

如何使整个过程自动化（即编写脚本）？

更新，脚本看起来有点像这样......

DROP DATABASE IF EXISTS ????

DROP TABLESPACE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????

ALTER DEFAULT privileges IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM ????;
ALTER DEFAULT privileges IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM ????;

DROP USER IF EXISTS ????;
DROP USER IF EXISTS ????;
DROP USER IF EXISTS ????;

CREATE USER ???? PASSWORD '????';
CREATE USER ???? PASSWORD '????';
CREATE USER ???? PASSWORD '????';

CREATE TABLESPACE fastspace OWNER ???? LOCATION 'c:/????/????';
CREATE TABLESPACE slowspace OWNER ???? LOCATION 'd:/????/????';

DROP DATABASE ????;

CREATE DATABASE ????
  WITH OWNER = ????
       ENCODING = 'UTF8'
       TABLESPACE = fastspace
       LC_COLLATE = 'English_United Kingdom.1252'
       LC_CTYPE = 'English_United Kingdom.1252'
       CONNECTION LIMIT = -1;

GRANT CREATE ON DATABASE ???? TO ????;

COMMENT ON DATABASE ????
  IS '????database';

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO ????;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ????;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ????;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ????;

ALTER DEFAULT privileges IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ????;

上述许多（大多数）命令可以作为事务块的一部分运行，但少数不能（例如删除和创建数据库）。

postgresql

1 个回答

  1. Best Answer

    只需在启用自动提交的情况下运行脚本（默认情况下psql），并为那些应该在单个事务中运行的语句使用显式事务块：

    DROP DATABASE IF EXISTS ????

DROP TABLESPACE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????

begin;
  ALTER DEFAULT privileges IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM ????;
  ALTER DEFAULT privileges IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM ????;

  DROP USER IF EXISTS ????;
  DROP USER IF EXISTS ????;
  DROP USER IF EXISTS ????;

  CREATE USER ???? PASSWORD '????';
  CREATE USER ???? PASSWORD '????';
  CREATE USER ???? PASSWORD '????';
commit;

CREATE TABLESPACE fastspace OWNER ???? LOCATION 'c:/????/????';
CREATE TABLESPACE slowspace OWNER ???? LOCATION 'd:/????/????';

DROP DATABASE ????;

CREATE DATABASE ????
  WITH OWNER = ????
       ENCODING = 'UTF8'
       TABLESPACE = fastspace
       LC_COLLATE = 'English_United Kingdom.1252'
       LC_CTYPE = 'English_United Kingdom.1252'
       CONNECTION LIMIT = -1;

begin;
  GRANT CREATE ON DATABASE ???? TO ????;

  COMMENT ON DATABASE ???? IS '????database';

  GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO ????;
  GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ????;
  GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ????;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO ????;

  ALTER DEFAULT privileges IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ????;
commit;
    • 2

相关问题