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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
name: local-storage
provisioner: kubernetes.io/no-provisioner
volumeBindingMode: WaitForFirstConsumer
---
apiVersion: v1
kind: PersistentVolume
metadata:
name: pgdata-pv
spec:
capacity:
storage: 1Gi
accessModes:
- ReadWriteOnce
storageClassName: local-storage
local:
path: /mnt/data/postgres
nodeAffinity:
required:
nodeSelectorTerms:
- matchExpressions:
- key: kubernetes.io/hostname
operator: In
values:
- cas-sanluca1
---
apiVersion: v1
kind: Secret
metadata:
name: postgres-credentials
type: Opaque
stringData:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: password
POSTGRES_DB: gis
---
apiVersion: v1
kind: ConfigMap
metadata:
name: postgres-init-scripts
data:
init.sql: |
CREATE TABLE users(
id SERIAL NOT NULL,
email text NOT NULL,
password text NOT NULL,
name text NULL,
address text NULL,
notification_token text NULL,
is_admin boolean default false,
PRIMARY KEY (id)
);
CREATE TYPE moving_activity AS ENUM ('InVehicle', 'Running', 'Walking', 'Still');
CREATE TABLE positions(
id SERIAL NOT NULL,
user_id INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
location GEOGRAPHY(Point, 4326) NOT NULL,
activity moving_activity NOT NULL,
PRIMARY KEY(id),
CONSTRAINT fk_users_id
FOREIGN KEY(user_id) REFERENCES users(id)
ON DELETE CASCADE,
CONSTRAINT unique_user_position UNIQUE(user_id)
);
CREATE TYPE level_alert AS ENUM ('One', 'Two', 'Three');
CREATE TABLE alerts(
id SERIAL NOT NULL,
user_id INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
area GEOMETRY(Polygon, 4326),
text1 text NOT NULL,
text2 text NOT NULL,
text3 text NOT NULL,
audio1 bytea NOT NULL,
audio2 bytea NOT NULL,
audio3 bytea NOT NULL,
reached_users INTEGER DEFAULT 0 NOT NULL,
PRIMARY KEY(id),
CONSTRAINT fk_users_id
FOREIGN KEY(user_id) REFERENCES users(id)
ON DELETE CASCADE
);
CREATE TABLE notifications(
id SERIAL NOT NULL,
alert_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
location GEOGRAPHY(Point, 4326) NOT NULL,
activity moving_activity NOT NULL,
seen BOOLEAN DEFAULT false,
level level_alert NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY(id),
CONSTRAINT fk_alerts_id
FOREIGN KEY(alert_id) REFERENCES alerts(id)
ON DELETE CASCADE,
CONSTRAINT fk_users_id
FOREIGN KEY(user_id) REFERENCES users(id)
ON DELETE CASCADE
);
---
apiVersion: apps/v1
kind: Deployment
metadata:
name: postgres-deployment
spec:
replicas: 1
selector:
matchLabels:
app: postgres
template:
metadata:
labels:
app: postgres
spec:
tolerations:
- key: node-role.kubernetes.io/control-plane
operator: Exists
effect: NoSchedule
securityContext:
fsGroup: 999
containers:
- name: postgres
image: postgis/postgis:16-3.4
envFrom:
- secretRef:
name: postgres-credentials
ports:
- containerPort: 5432
volumeMounts:
- mountPath: /var/lib/postgresql/data
name: pgdata
- mountPath: /docker-entrypoint-initdb.d
name: init-scripts
resources:
requests:
cpu: 250m
memory: 512Mi
limits:
cpu: 500m
memory: 1Gi
volumes:
- name: pgdata
persistentVolumeClaim:
claimName: pgdata-pvc
- name: init-scripts
configMap:
name: postgres-init-scripts
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: pgdata-pvc
spec:
storageClassName: local-storage
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
---
apiVersion: v1
kind: Service
metadata:
name: postgres-service
spec:
ports:
- port: 5432
targetPort: 5432
selector:
app: postgres
type: ClusterIP
---
apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
name: allow-cas-postgres
spec:
podSelector:
matchLabels:
app: cas
policyTypes:
- Ingress
ingress:
- from:
- podSelector:
matchLabels:
app: postgres
ports:
- protocol: TCP
port: 5432
|
