How to customize PostgreSQL parameters in EDB containers in MiniShift/OpenShift

In the last two posts, we deployed an EDB database container and two pgpool instances and then scaled that up to include a read only replica. In this post will use a ConfigMap to adjust parameters in postgresql.conf as you will probably need to do that when you start using the EDB containers in your environment.

A ConfigMap is an object that can be used to provide parameter/values pairs to the container which then will be added to postgresql.conf file of the database containers. Creating a  ConfigMap is quite easy, all you need to do is to create a file called “postgresql.conf.in” which lists all the parameters you want to get adjusted:

1

2

3

dwe@dwe:~$ cat /opt/ConfigMaps/postgresql.conf.in

work_mem='12MB'

shared_buffers='56MB'

 

In that case we want to adjust work_mem and shared_buffers, that’s it. To load that into OpenShift by using the oc command line utility:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

dwe@dwe:~$ oc create configmap postgres-map --from-file=/opt/ConfigMaps/postgresql.conf.in

configmap "postgres-map" created

11:01:22 dwe@dwe:~$ oc get configmaps postgres-map

NAME           DATA      AGE

postgres-map   1         12m

dwe@dwe:~$ oc get configmaps postgres-map -o yaml

apiVersion: v1

data:

  postgresql.conf.in: |+

    work_mem='12MB'

    shared_buffers='56MB'

 

kind: ConfigMap

metadata:

  creationTimestamp: 2018-05-18T08:49:35Z

  name: postgres-map

  namespace: myproject

  resourceVersion: "16618"

  selfLink: /api/v1/namespaces/myproject/configmaps/postgres-map

  uid: 63c3a154-5a78-11e8-992f-ca15bcd30222

 

The issue is now that our current template does not know anything about that ConfigMap. So either adjust it or create a new one like this (changes are highlighted):

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

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

cat edb-as10-0-edb-cust.yaml

apiVersion: v1

kind: Template

metadata:

   name: edb-as10-custom

   annotations:

    description: "Custom EDB Postgres Advanced Server 10.0 Deployment Config"

    tags: "database,epas,postgres,postgresql"

    iconClass: "icon-postgresql"

objects:

- apiVersion: v1

  kind: Service

  metadata:

    name: ${DATABASE_NAME}-service

    labels:

      role: loadbalancer

      cluster: ${DATABASE_NAME}

  spec:

    selector:                 

      lb: ${DATABASE_NAME}-pgpool

    ports:

    name: lb

      port: ${PGPORT}

      targetPort: 9999

    sessionAffinity: None

    type: LoadBalancer

- apiVersion: v1

  kind: DeploymentConfig

  metadata:

    name: ${DATABASE_NAME}-pgpool

  spec:

    replicas: 2

    selector:

      lb: ${DATABASE_NAME}-pgpool

    strategy:

      resources: {}

      rollingParams:

        intervalSeconds: 1

        maxSurge: 25%

        maxUnavailable: 25%

        timeoutSeconds: 600

        updatePeriodSeconds: 1

      type: Rolling

    template:

      metadata:

        labels:

          lb: ${DATABASE_NAME}-pgpool

          role: queryrouter

          cluster: ${DATABASE_NAME}

      spec:

        containers:

        name: edb-pgpool

          env:

          name: DATABASE_NAME

            value: ${DATABASE_NAME}

          name: PGPORT

            value: ${PGPORT}

          name: REPL_USER

            value: ${REPL_USER}

          name: ENTERPRISEDB_PASSWORD

            value: 'postgres'

          name: REPL_PASSWORD

            value: 'postgres'

          name: ACCEPT_EULA

            value: ${ACCEPT_EULA}

          image: containers.enterprisedb.com/edb/edb-pgpool:v3.5

          imagePullPolicy: IfNotPresent

          readinessProbe:

            exec:

              command:

              - /var/lib/edb/testIsReady.sh

            initialDelaySeconds: 60

            timeoutSeconds: 5

    triggers:

    - type: ConfigChange

- apiVersion: v1

  kind: DeploymentConfig

  metadata:

    name: ${DATABASE_NAME}-as10-0

  spec:

    replicas: 1

    selector:

      db: ${DATABASE_NAME}-as10-0

    strategy:

      resources: {}

      rollingParams:

        intervalSeconds: 1

        maxSurge: 25%

        maxUnavailable: 25%

        timeoutSeconds: 600

        updatePeriodSeconds: 1

      type: Rolling

    template:

      metadata:

        creationTimestamp: null

        labels:

          db: ${DATABASE_NAME}-as10-0

          cluster: ${DATABASE_NAME}

      spec:

        containers:

        name: edb-as10

          env:

          name: DATABASE_NAME

            value: ${DATABASE_NAME}

          name: DATABASE_USER

            value: ${DATABASE_USER}

          name: DATABASE_USER_PASSWORD

            value: 'postgres'

          name: ENTERPRISEDB_PASSWORD

            value: 'postgres'

          name: REPL_USER

            value: ${REPL_USER}

          name: REPL_PASSWORD

            value: 'postgres'

          name: PGPORT

            value: ${PGPORT}

          name: RESTORE_FILE

            value: ${RESTORE_FILE}

          name: LOCALEPARAMETER

            value: ${LOCALEPARAMETER}

          name: CLEANUP_SCHEDULE

            value: ${CLEANUP_SCHEDULE}

          name: EFM_EMAIL

            value: ${EFM_EMAIL}

          name: NAMESERVER

            value: ${NAMESERVER}

          name: POD_NAMESPACE

            valueFrom:

              fieldRef:

                fieldPath: metadata.namespace

          name: POD_NODE

            valueFrom:

              fieldRef:

                fieldPath: spec.nodeName

          name: POD_IP

            valueFrom:

              fieldRef:

                fieldPath: status.podIP

          name: ACCEPT_EULA

            value: ${ACCEPT_EULA}

          image: containers.enterprisedb.com/edb/edb-as:v10.3

          imagePullPolicy: IfNotPresent

          readinessProbe:

            exec:

              command:

              - /var/lib/edb/testIsReady.sh

            initialDelaySeconds: 60

            timeoutSeconds: 5

          livenessProbe:

            exec:

              command:

              - /var/lib/edb/testIsHealthy.sh

            initialDelaySeconds: 600

            timeoutSeconds: 60

          ports:

          - containerPort: ${PGPORT}

          volumeMounts:

          name: ${PERSISTENT_VOLUME}

            mountPath: /edbvolume

          name: pg-initconf

            mountPath: /initconf

        dnsPolicy: ClusterFirst

        restartPolicy: Always

        volumes:

        name: ${PERSISTENT_VOLUME}

          persistentVolumeClaim:

            claimName: ${PERSISTENT_VOLUME_CLAIM}

        name: pg-initconf

          configMap:

            name: postgres-map

              

    triggers:

    - type: ConfigChange

parameters:

name: DATABASE_NAME

  displayName: Database Name

  description: Name of Postgres database (leave edb for default)

  value: 'edb'

name: DATABASE_USER

  displayName: Default database user (leave enterprisedb for default)

  description: Default database user

  value: 'enterprisedb'

name: REPL_USER

  displayName: Repl user

  description: repl database user

  value: 'repl'

name: PGPORT

  displayName: Database Port

  description: Database Port (leave 5444 for default)

  value: "5444"

name: LOCALEPARAMETER

  displayName: Locale

  description: Locale of database

  value: ''

name: CLEANUP_SCHEDULE

  displayName: Host Cleanup Schedule

  description: Standard cron schedule - min (0 - 59), hour (0 - 23), day of month (1 - 31), month (1 - 12), day of week (0 - 6) (0 to 6 are Sunday to Saturday, or use names; 7 is Sunday, the same as 0). Leave it empty if you dont want to cleanup.

  value: '0:0:*:*:*'

name: EFM_EMAIL

  displayName: Email

  description: Email for EFM

  value: 'none@none.com'

name: NAMESERVER

  displayName: Name Server for Email

  description: Name Server for Email

  value: '8.8.8.8'

name: PERSISTENT_VOLUME

  displayName: Persistent Volume

  description: Persistent volume name

  value: ''

  required: true

name: PERSISTENT_VOLUME_CLAIM

  displayName: Persistent Volume Claim

  description: Persistent volume claim name

  value: ''

  required: true

name: RESTORE_FILE

  displayName: Restore File

  description: Restore file location

  value: ''

name: ACCEPT_EULA

  displayName: Accept end-user license agreement (leave 'Yes' for default)

  description: Indicates whether user accepts the end-user license agreement

  value: 'Yes'

  required: true

 

Once you imported that into OpenShift (check here if you don’t know how to do that) you get a new template you can deploy from:

Selection_025

 

When you create a new deployment of that one (again, check  here if you don’t know how to do that) you will notice several things when you login to the container once it is up and running:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

dwe@dwe:~$ oc rsh edb-as10-0-1-gk8dt

sh-4.2$ psql postgres

psql.bin (10.3.8)

Type "help" for help.

 

postgres=# show work_mem;

 work_mem

----------

 12MB

(1 row)

 

postgres=# show shared_buffers ;

 shared_buffers

----------------

 56MB

(1 row)

 

First of all and this is what we wanted: The PostgreSQL instance came up with the parameters we specified in the ConfigMap. When you look at the volumes present in the container there is a new one named after what we specified in the template:

1

2

3

4

5

6

7

8

sh-4.2$ df -h

Filesystem      Size  Used Avail Use% Mounted on

none             18G  4.0G   14G  24% /

tmpfs          1002M     0 1002M   0% /dev

tmpfs          1002M     0 1002M   0% /sys/fs/cgroup

/dev/sda1        18G  4.0G   14G  24% /initconf

shm              64M   12K   64M   1% /dev/shm

tmpfs          1002M   16K 1002M   1% /run/secrets/kubernetes.io/serviceaccount

 

Inside that volume there is the postgresql.conf.in file we also specified in the template and that is linked to $PGDATA:

1

2

3

4

5

6

7

sh-4.2$ ls -la /initconf

total 12

drwxrwsrwx  3 root 1000070000 4096 May 18 09:55 .

drwxr-xr-x 85 root root       4096 May 18 09:55 ..

drwxr-sr-x  2 root 1000070000 4096 May 18 09:55 ..2018_05_18_09_55_19.162613490

lrwxrwxrwx  1 root root         31 May 18 09:55 ..data -> ..2018_05_18_09_55_19.162613490

lrwxrwxrwx  1 root root         25 May 18 09:55 postgresql.conf.in -> ..data/postgresql.conf.in

 

And finally we can confirm the content of that file:

1

2

3

sh-4.2$ cat /initconf/postgresql.conf.in

work_mem='12MB'

shared_buffers='56MB'

 

You can do the same for pg_hba.conf by creating a new ConfigMap for pg_hba.conf.in. In the next post we’ll look at how EDB Failover Manager is configured inside the containers.

 

(Article originally published - May 18, 2018 @ https://blog.dbi-services.com/customizing-postgresql-parameters-in-edb-containers-in-minishiftopensh...)