-
Notifications
You must be signed in to change notification settings - Fork 5
/
create-monthly-partition.bash
executable file
·44 lines (37 loc) · 1.24 KB
/
create-monthly-partition.bash
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
#!/bin/bash
set -e
set -u
set -o pipefail
if [[ $# != 2 ]]; then
echo "usage: ${0} TABLE COLUMN
Creates a partition for the named table assuming it has
the default partition with '_default' suffix and it may
contain rows that would violate the created partition
constraints.
environment variables:
DATE overrides current date, so a new partition
will be created for the following month,
format YYYY-MM-DD.
" >&2
exit 2
fi
table=$1
column=$2
date=${DATE:-$(date -I)}
from=$(date -d "${date} + 1 month" +%Y-%m-01)
upto=$(date -d "${date} + 2 month" +%Y-%m-01)
name=$(date -d ${from} +%Y_%m)
echo "BEGIN;"
echo " ALTER TABLE ${table} DETACH PARTITION ${table}_default;"
echo " CREATE TABLE IF NOT EXISTS ${table}_${name} PARTITION OF ${table}"
echo " FOR VALUES FROM ('${from} 00:00:00') TO ('${upto} 00:00:00');"
echo " INSERT INTO ${table}_${name} ("
echo " SELECT * FROM ${table}_default"
echo " WHERE ${column} >= '${from} 00:00:00'"
echo " AND ${column} < '${upto} 00:00:00'"
echo " );"
echo " DELETE FROM ${table}_default "
echo " WHERE ${column} >= '${from} 00:00:00'"
echo " AND ${column} < '${upto} 00:00:00';"
echo " ALTER TABLE ${table} ATTACH PARTITION ${table}_default DEFAULT;"
echo "COMMIT;"