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

SQL

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