-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathMakefile
More file actions
132 lines (109 loc) · 5.26 KB
/
Copy pathMakefile
File metadata and controls
132 lines (109 loc) · 5.26 KB
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
# relies on presence of 'POSTGRES_LOCAL_MANAGEMENT_DIR' env var
PG_MANAGEMENT_WORK_DIR := $(POSTGRES_LOCAL_MANAGEMENT_DIR)/postgres
PG_LOCAL_DB_PATH := $(PG_MANAGEMENT_WORK_DIR)/pgdata
# points where a PID file of a running Postgres instance could be found
PG_PROCESS_PATH := $(PG_LOCAL_DB_PATH)/postmaster.pid
# we need a separate ssl-generated file to avoid re-triggering the command
# when key files are touched by external processes
PG_SSL_GENERATED := $(POSTGRES_LOCAL_MANAGEMENT_DIR)/ssl_generated
# this is a marker we choose to use to indicate that users are created for the given cluster
PG_USERS_INIT_PATH := $(POSTGRES_LOCAL_MANAGEMENT_DIR)/users_initialised
PG_DB_INSTALLED := $(POSTGRES_LOCAL_MANAGEMENT_DIR)/db_initialised
PG_SSL_CERT_FILE := $(POSTGRES_LOCAL_MANAGEMENT_DIR)/local_nonsecret_ssl.crt
PG_SSL_KEY_FILE := $(POSTGRES_LOCAL_MANAGEMENT_DIR)/local_nonsecret_ssl.key
PF_DUMP_FILENAME := "pg_backup.custom"
PG_DB_DUMP_PATH := $(POSTGRES_LOCAL_MANAGEMENT_DIR)/$(PF_DUMP_FILENAME)
# Default postgres values
POSTGRES_HOST := $(if $(POSTGRES_LOCAL_MANAGEMENT_DB_HOST),$(POSTGRES_LOCAL_MANAGEMENT_DB_HOST),localhost)
POSTGRES_PORT := $(if $(POSTGRES_LOCAL_MANAGEMENT_DB_PORT),$(POSTGRES_LOCAL_MANAGEMENT_DB_PORT),5432)
POSTGRES_USER := $(if $(POSTGRES_LOCAL_MANAGEMENT_DB_USER),$(POSTGRES_LOCAL_MANAGEMENT_DB_USER),$(USER))
POSTGRES_DB_NAME := $(if $(POSTGRES_LOCAL_MANAGEMENT_DB_NAME),$(POSTGRES_LOCAL_MANAGEMENT_DB_NAME),$(USER))
POSTGRES_DB_PASSWORD := $(if $(POSTGRES_LOCAL_MANAGEMENT_DB_PASS),$(POSTGRES_LOCAL_MANAGEMENT_DB_PASS),$(USER))
# https://pgdash.io/blog/pgctl-tips-tricks.html
POSTGRES_DOMAIN_SOCKETS_DIR = /tmp
$(PG_MANAGEMENT_WORK_DIR):
install -d $(PG_MANAGEMENT_WORK_DIR)
# https://www.postgresql.org/docs/14/ssl-tcp.html#SSL-CERTIFICATE-CREATION
# Generates SSL keypair for SSL client verification
$(PG_SSL_GENERATED): $(PG_MANAGEMENT_WORK_DIR)
openssl req \
-nodes -new -x509 -days 365000 \
-subj "/CN=$(POSTGRES_HOST)" \
-keyout $(PG_SSL_KEY_FILE) \
-out $(PG_SSL_CERT_FILE)
chmod 0600 $(PG_SSL_KEY_FILE)
touch $(PG_SSL_GENERATED)
# local DB depends on the untrack dir, it creates a directory structure inside.
# the instance will be available from all networks
$(PG_DB_INSTALLED): $(PG_SSL_GENERATED)
touch $(PG_DB_INSTALLED)
pg_ctl initdb -D $(PG_LOCAL_DB_PATH) -o "--locale=$(LANG)"
printf "\nlisten_addresses = '*'" >> $(PG_LOCAL_DB_PATH)/postgresql.conf
printf "\nhost all all all trust" >> $(PG_LOCAL_DB_PATH)/pg_hba.conf
# A running postgres instance will generate this PID file.
# -o command flags https://www.postgresql.org/docs/12/app-postgres.html
# because git doesn't save the full file permissions, we have to chmod keys here
$(PG_PROCESS_PATH): $(PG_DB_INSTALLED)
pg_ctl start -D $(PG_LOCAL_DB_PATH) --wait --timeout=30 \
--log=$(PG_LOCAL_DB_PATH)/logs.log -o \
"-p $(POSTGRES_PORT) \
-k /tmp \
-l \
--ssl_cert_file=$(PG_SSL_CERT_FILE) \
--ssl_key_file=$(PG_SSL_KEY_FILE) \
";
tail --lines 20 $(PG_LOCAL_DB_PATH)/logs.log
# users can be created only when PG process exists (indicated by PID file)
$(PG_USERS_INIT_PATH): $(PG_PROCESS_PATH)
createuser --superuser $(POSTGRES_USER) -h $(POSTGRES_HOST) -p $(POSTGRES_PORT) || true
createdb --owner $(POSTGRES_USER) -h $(POSTGRES_HOST) -p $(POSTGRES_PORT) --encoding utf8 $(POSTGRES_DB_NAME) || true
psql -U $(POSTGRES_USER) -p $(POSTGRES_PORT) -h $(POSTGRES_HOST) $(POSTGRES_DB_NAME) -c "CREATE EXTENSION IF NOT EXISTS intarray"
# indicate that we've completed the task
touch $(PG_USERS_INIT_PATH)
.PHONY: postgres-init
postgres-init: $(PG_USERS_INIT_PATH)
pg_ctl status -D $(PG_LOCAL_DB_PATH)
.PHONY: postgres-stop
postgres-stop:
@if ! [ -z "$(shell lsof -nti:$(POSTGRES_PORT))" ]; then pg_ctl stop -D $(PG_LOCAL_DB_PATH) -o "-p $(POSTGRES_PORT)"; fi;
.PHONY: postgres-purge
postgres-purge: postgres-stop
rm -rf $(PG_MANAGEMENT_WORK_DIR)
rm $(POSTGRES_LOCAL_MANAGEMENT_DIR)/users_initialised
rm $(POSTGRES_LOCAL_MANAGEMENT_DIR)/db_initialised
.PHONY: postgres-shell
postgres-shell:
psql -U $(POSTGRES_USER) -p $(POSTGRES_PORT) -h $(POSTGRES_HOST) $(POSTGRES_DB_NAME)
# we use connection string format below because there's no CLI flag alternative
# for specifying 'sslrootcert' setting
.PHONY: postgres-dump-local
postgres-dump-local:
PGPASSWORD=$(POSTGRES_DB_PASSWORD) \
PGDATABASE=$(POSTGRES_DB_NAME) \
pg_dump \
--format custom \
--verbose \
"host=$(POSTGRES_HOST) \
port=$(POSTGRES_PORT) \
user=$(POSTGRES_USER) \
sslrootcert=$(PG_SSL_CERT_FILE)" \
> $(PG_DB_DUMP_PATH)
# we use connection string format below because there's no CLI flag alternative
# for specifying 'sslrootcert' setting
.PHONY: postgres-restore-local
postgres-restore-local: FILENAME ?= $(PG_DB_DUMP_PATH)
postgres-restore-local:
PGPASSWORD=$(POSTGRES_DB_PASSWORD) \
PGDATABASE=$(POSTGRES_DB_NAME) \
pg_restore \
--single-transaction \
--format custom \
--verbose \
--no-owner \
--no-privileges \
--schema=public \
-d "host=$(POSTGRES_HOST) \
port=$(POSTGRES_PORT) \
user=$(POSTGRES_USER) \
sslrootcert=$(PG_SSL_CERT_FILE)"\
$(FILENAME)