Database Management

Warning

This page is a stub

For most product builds, we use our persistent Postgres DB cluster named edm-data. Each product has it's own DB.

To create a new DB for a product using DBeaver:

1. Right click on edm-data in the Navigator panel and select Connection View -> Advanced

2. Right click on edm-data and select Create -> Database

3. For database name, use our convention of db-product-name

4. Change the Tablespace selection from pg_default to Default

5. Click OK

6. Add the postgres extensions we typically use in a build:

   CREATE EXTENSION postgis;
   CREATE EXTENSION fuzzystrmatch;

Handy code

-- Create a database cluster role
SELECT * FROM pg_roles;
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE db-pluto TO readonly;
GRANT CONNECT ON DATABASE db-colp TO readonly;
GRANT CONNECT ON DATABASE db-devdb TO readonly;

-- Grant privileges to a role
-- Queries about schemas will be applied to the database of the current conenction
GRANT USAGE ON SCHEMA nightly_qa TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA nightly_qa TO readonly;

-- Create a user assigned to a role
SELECT * FROM pg_users;
CREATE USER first_last WITH PASSWORD 'A_GOOD_PASSWORD';
GRANT readonly TO first_last;

-- Query to see the size of all schemas in a database
SELECT schema_name,
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as percent_of_db
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;