tutorials/lessons/239/migration.sql at main · HaikAsatryan/tutorials · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
--
-- Create application users.
--
CREATE USER fastapi_app WITH PASSWORD 'devops123' SUPERUSER CREATEDB CREATEROLE LOGIN;
CREATE USER node_app WITH PASSWORD 'devops123' SUPERUSER CREATEDB CREATEROLE LOGIN;
CREATE USER go_app WITH PASSWORD 'devops123' SUPERUSER CREATEDB CREATEROLE LOGIN;

--
-- Clean up idle connections.
--
WITH inactive_connections AS (SELECT pid, usename FROM pg_stat_activity WHERE usename = 'fastapi_app') SELECT pg_terminate_backend(pid) FROM inactive_connections;
WITH inactive_connections AS (SELECT pid, usename FROM pg_stat_activity WHERE usename = 'node_app') SELECT pg_terminate_backend(pid) FROM inactive_connections;
WITH inactive_connections AS (SELECT pid, usename FROM pg_stat_activity WHERE usename = 'go_app') SELECT pg_terminate_backend(pid) FROM inactive_connections;

BEGIN;

--
-- Drop tables.
--
DROP TABLE IF EXISTS fastapi_device;
DROP TABLE IF EXISTS node_device;
DROP TABLE IF EXISTS go_device;

--
-- Create device tables.
--
CREATE TABLE IF NOT EXISTS fastapi_device (id SERIAL PRIMARY KEY, uuid UUID DEFAULT NULL, mac VARCHAR(255) DEFAULT NULL, firmware VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS node_device (id SERIAL PRIMARY KEY, uuid UUID DEFAULT NULL, mac VARCHAR(255) DEFAULT NULL, firmware VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS go_device (id SERIAL PRIMARY KEY, uuid UUID DEFAULT NULL, mac VARCHAR(255) DEFAULT NULL, firmware VARCHAR(255) DEFAULT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);

--
-- Create indexes.
--
CREATE INDEX IF NOT EXISTS idx_fastapi_device_uuid ON fastapi_device (uuid);
CREATE INDEX IF NOT EXISTS idx_node_device_uuid ON node_device (uuid);
CREATE INDEX IF NOT EXISTS idx_go_device_uuid ON go_device (uuid);

COMMIT;

--
-- Garbage-collect (reclaims storage occupied by dead tuples)
--
VACUUM full;