CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Gary Sharpe
Gary Sharpe

Reputation: 2431

How do I write my ddl in a single file for quickly creating database, schema, table, role objects in postgres?

I'm definitely new to postgres. I'm using version 9.2. I'm trying to write a script to create a few roles, a database, a schema and a basic table. I tried using the sql editor in pgAdminIII but keep running into errors like this one:

ERROR:  CREATE DATABASE cannot be executed from a function or multi-command string

I understand from other posts that this is simply not how to do this in postgres, but what I haven't figured out is how someone can organize their ddl in a single file to build a collection of dbs, schemas, roles, tables, etc.

Here's a sample of the basic db layout I'm trying to create at the moment:

CREATE ROLE "edus-db-admins-dev"
  NOSUPERUSER INHERIT CREATEROLE;

CREATE ROLE "edus-db-admin-dev" LOGIN
PASSWORD 'blahblahblah'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT "edus-db-admins-dev" TO "edus-db-admin-dev";

CREATE ROLE "edus-esb-loggers"
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE ROLE "edus-esb-logger" LOGIN
  PASSWORD 'blahblahblah'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;



CREATE DATABASE edus
  WITH OWNER = "edus-db-admin-dev"
     ENCODING = 'UTF8'
     TABLESPACE = pg_default
     LC_COLLATE = 'English_United States.1252'
     LC_CTYPE = 'English_United States.1252'
     CONNECTION LIMIT = -1;

CREATE SCHEMA eduspubservice
  AUTHORIZATION "edus-db-admin-dev";



CREATE TABLE eduspubservice.logs
(
  date date NOT NULL,
  level character varying(10),
  category character varying(100),
  logger character varying(100),
  method character varying(100),
  message character varying(10000)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE eduspubservice.logs
  OWNER TO "edus-esb-logger";

Upvotes: 0

Views: 3529

Answers (1)

bma
bma

Reputation: 9756

I don't use pgAdmin so I can't speak to it, but from the command line you would load your script into the db like so:

psql -d postgres -U postgres -f your_file_name.sql

I assume pgAdmin allows you to connect to the postgres admin database and run a SQL script against it.

Upvotes: 1

Related Questions