PostgreSQL – Automating Database Creation

postgresql

I'm trying to automate the creation of a postgres database running on windows 10.
I'd like to be able to start from a default installation of postgres and automate the following steps (not necessarily in this order)

  • drop and re-create new database
  • drop and re-create new tablespaces
  • drop and re-create new users.
  • build database objects (tables, views, procedures, triggers etc)
  • grant users required permissions.
  • populate tables with initial data.

It's not too difficult to reduce this to a series of database commands (so long as they're executed in the correct order) the problem is that for few commands I keep encountering the error 'cannot be issued inside a transaction block'. This means that some commands like DROP DATABASE IF EXISTS and CREATE TABLESPACE can only be run (as far as I can tell) by manually single-stepping them, whereas I can call my table-creation scripts as a single block and re-create a 100 tables in a fraction of a second.

I'm not too worried about the extra few seconds I'm wasting by single-stepping some of the individual commands, but by doing some of it manually I run the risk of missing steps and making mistakes.

How can I automate (i.e. write scripts for) the whole process?

Update, script looks a bit like this…

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

Many (most) of the above commands can be run as part of a transaction block, but a few can't (dropping and creating the database for example).

Best Answer

Just run the script with autocommit turned on (the default in psql) and use an explicit transaction block for those statements that should run in a single transaction:

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;
Related Question