Skip to content

Commit f8b4c37

Browse files
agneumagneum
committed
Merge branch '141-basic-postgres-configuration' into 'master'
feat: basic Postgres configuration (physical & logical) - set up the default `postgresql.conf` - set up the default `pg_hba.conf` - override all params required for Database Lab: logging connections, etc - allow applying user configuration - adjust recovery configuration See merge request postgres-ai/database-lab!133
2 parents 4ded7b9 + 192237e commit f8b4c37

File tree

18 files changed

+564
-220
lines changed

18 files changed

+564
-220
lines changed

‎cmd/database-lab/main.go

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,10 @@ func main() {
6868
cfg.Provision.ModeLocal.DockerImage = opts.DockerImage
6969
}
7070

71+
if cfg.Provision.ModeLocal.MountDir != "" {
72+
cfg.Global.MountDir = cfg.Provision.ModeLocal.MountDir
73+
}
74+
7175
ctx, cancel := context.WithCancel(context.Background())
7276
defer cancel()
7377

‎configs/config.sample.yml

Lines changed: 25 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -182,13 +182,34 @@ retrieval:
182182

183183
- name: logical-snapshot
184184
options:
185-
# It is possible to define a pre-precessing script.
186-
# preprocessingScript: "/tmp/scripts/custom.sh"
185+
# It is possible to define a pre-precessing script. For example, /tmp/scripts/custom.sh
186+
preprocessingScript: ""
187+
188+
# Section for adding custom postgresql.conf parameters.
189+
configs:
190+
# In order to match production plans with Database Lab plans set parameters related to Query Planning as on production.
191+
192+
# Be careful with memory consumption: besides shared_buffers (immediately allocated when the clone is started),
193+
# memory is consumed by Postgres backends directly (depends on work_mem and maintenance_work_mem values),
194+
# and by other applications.
195+
# If there is not enough memory to run more clones, then, depending on OS settings,
196+
# you may experience either OOM killer or swapping activity.
197+
# The "free" memory is automatically used for the file cache which is shared among clones.
198+
shared_buffers: 1GB
199+
# effective_cache_size: 64MB
187200

188201
- name: physical-snapshot
189202
options:
203+
# Promote PGDATA after data fetching.
190204
promote: true
191-
# It is possible to define a pre-precessing script.
192-
# preprocessingScript: "/tmp/scripts/custom.sh"
205+
206+
# It is possible to define a pre-precessing script. For example, /tmp/scripts/custom.sh
207+
preprocessingScript: ""
208+
209+
# Section for adding custom postgresql.conf parameters.
210+
configs:
211+
# In order to match production plans with Database Lab plans set parameters related to Query Planning as on production.
212+
shared_buffers: 1GB
213+
# effective_cache_size: 64MB
193214

194215
debug: true

‎configs/postgres/postgresql.conf

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22
## Commented params will be commented in postgresql.conf
33
## (empty and not specified params treated by different ways by Postgres).
44

5+
#include
56
#data_directory
67
#external_pid_file
78
#hba_file
@@ -14,6 +15,10 @@
1415
#logging_collector
1516
#log_directory
1617

18+
## Turn off the replication.
19+
#restore_command
20+
#recovery_target_timeline
21+
1722
# Load pg_stat_statements and auto_explain for query analysis
1823
shared_preload_libraries = 'pg_stat_statements, auto_explain'
1924

@@ -26,9 +31,10 @@ log_destination = 'stderr'
2631
log_line_prefix = '%m [%p]: [%l-1] db=%d,user=%u (%a,%h) '
2732
log_connections = on
2833

29-
## Theoretical number of clones cannot exceed RAM / shared_buffers.
30-
## If you want to have more clones use a lower value.
31-
shared_buffers = '1GB'
34+
min_wal_size = '1GB'
35+
max_wal_size = '16GB'
36+
checkpoint_timeout = '15min'
37+
checkpoint_completion_target = 0.9
3238

3339
# To be able to detect idle clones, we need to log all queries.
3440
# We are going to do so with duration.

‎pkg/config/config.go

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -34,8 +34,9 @@ type Config struct {
3434

3535
// Global contains global Database Lab configurations.
3636
type Global struct {
37-
Engine string `yaml:"engine"`
38-
DataDir string `yaml:"dataDir"`
37+
Engine string `yaml:"engine"`
38+
DataDir string `yaml:"dataDir"`
39+
MountDir string // TODO (akartasov): Use MountDir for the ModeLocalConfig of a Provision service.
3940
}
4041

4142
// LoadConfig instances a new Config by configuration filename.

‎pkg/retrieval/engine/postgres/initialize/physical/custom.go

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,3 +42,8 @@ func (c *custom) GetMounts() []mount.Mount {
4242
func (c *custom) GetRestoreCommand() []string {
4343
return strings.Split(c.options.Command, " ")
4444
}
45+
46+
// GetRecoveryConfig returns a recovery config to restore data.
47+
func (c *custom) GetRecoveryConfig() []byte {
48+
return []byte{}
49+
}

‎pkg/retrieval/engine/postgres/initialize/physical/physical.go

Lines changed: 124 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -6,10 +6,15 @@
66
package physical
77

88
import (
9+
"bufio"
910
"context"
1011
"fmt"
1112
"io"
1213
"os"
14+
"path"
15+
"strconv"
16+
"strings"
17+
"time"
1318

1419
"github.com/docker/docker/api/types"
1520
"github.com/docker/docker/api/types/container"
@@ -23,7 +28,9 @@ import (
2328
"gitlab.com/postgres-ai/database-lab/pkg/retrieval/config"
2429
"gitlab.com/postgres-ai/database-lab/pkg/retrieval/dbmarker"
2530
"gitlab.com/postgres-ai/database-lab/pkg/retrieval/engine/postgres/initialize/tools"
31+
"gitlab.com/postgres-ai/database-lab/pkg/retrieval/engine/postgres/initialize/tools/defaults"
2632
"gitlab.com/postgres-ai/database-lab/pkg/retrieval/options"
33+
"gitlab.com/postgres-ai/database-lab/pkg/services/provision/databases/postgres/configuration"
2734
)
2835

2936
const (
@@ -32,6 +39,8 @@ const (
3239

3340
restoreContainerName = "retriever_physical_restore"
3441
restoreContainerPath = "/var/lib/postgresql/dblabdata"
42+
43+
readyLogLine = "database system is ready to accept"
3544
)
3645

3746
// RestoreJob describes a job for physical restoring.
@@ -63,6 +72,9 @@ type restorer interface {
6372

6473
// GetRestoreCommand returns a command to restore data.
6574
GetRestoreCommand() []string
75+
76+
// GetRecoveryConfig returns a recovery config to restore data.
77+
GetRecoveryConfig() []byte
6678
}
6779

6880
// NewJob creates a new physical restore job.
@@ -147,48 +159,106 @@ func (r *RestoreJob) Run(ctx context.Context) error {
147159
log.Msg(fmt.Sprintf("Stop container: %s. ID: %v", restoreContainerName, cont.ID))
148160
}()
149161

162+
defer tools.RemoveContainer(ctx, r.dockerClient, cont.ID, tools.StopTimeout)
163+
164+
log.Msg(fmt.Sprintf("Running container: %s. ID: %v", restoreContainerName, cont.ID))
165+
150166
if err = r.dockerClient.ContainerStart(ctx, cont.ID, types.ContainerStartOptions{}); err != nil {
151167
return errors.Wrap(err, "failed to start container")
152168
}
153169

154-
log.Msg(fmt.Sprintf("Running container: %s. ID: %v", restoreContainerName, cont.ID))
170+
log.Msg("Running restore command")
171+
172+
if err := tools.ExecCommand(ctx, r.dockerClient, cont.ID, types.ExecConfig{
173+
Cmd: r.restorer.GetRestoreCommand(),
174+
}); err != nil {
175+
return errors.Wrap(err, "failed to restore data")
176+
}
177+
178+
log.Msg("Restoring job has been finished")
179+
180+
if err := r.markDatabaseData(); err != nil {
181+
log.Err("Failed to mark database data: ", err)
182+
}
155183

156-
execCommand, err := r.dockerClient.ContainerExecCreate(ctx, cont.ID, types.ExecConfig{
157-
AttachStdin: false,
184+
pgVersion, err := tools.DetectPGVersion(r.globalCfg.DataDir)
185+
if err != nil {
186+
return errors.Wrap(err, "failed to detect the Postgres version")
187+
}
188+
189+
if err := configuration.Run(r.globalCfg.DataDir); err != nil {
190+
return errors.Wrap(err, "failed to configure")
191+
}
192+
193+
if err := r.adjustRecoveryConfiguration(pgVersion, r.globalCfg.DataDir); err != nil {
194+
return err
195+
}
196+
197+
// Set permissions.
198+
if err := tools.ExecCommand(ctx, r.dockerClient, cont.ID, types.ExecConfig{
199+
Cmd: []string{"chown", "-R", "postgres", restoreContainerPath},
200+
}); err != nil {
201+
return errors.Wrap(err, "failed to set permissions")
202+
}
203+
204+
// Start PostgreSQL instance.
205+
startCommand, err := r.dockerClient.ContainerExecCreate(ctx, cont.ID, types.ExecConfig{
158206
AttachStdout: true,
159207
AttachStderr: true,
160208
Tty: true,
161-
Cmd: r.restorer.GetRestoreCommand(),
209+
Cmd: []string{"postgres", "-D", restoreContainerPath},
210+
User: defaults.Username,
162211
})
163212

164213
if err != nil {
165214
return errors.Wrap(err, "failed to create an exec command")
166215
}
167216

168-
log.Msg("Running restore command")
217+
log.Msg("Running refresh command")
169218

170-
attachResponse, err := r.dockerClient.ContainerExecAttach(ctx, execCommand.ID, types.ExecStartCheck{Tty: true})
219+
attachResponse, err := r.dockerClient.ContainerExecAttach(ctx, startCommand.ID, types.ExecStartCheck{Tty: true})
171220
if err != nil {
172221
return errors.Wrap(err, "failed to attach to the exec command")
173222
}
174223

175224
defer attachResponse.Close()
176225

177-
if err := waitForCommandResponse(ctx, attachResponse); err != nil {
178-
return errors.Wrap(err, "failed to exec the command")
226+
if err := isDatabaseReady(attachResponse.Reader); err != nil {
227+
return errors.Wrap(err, "failed to refresh data")
179228
}
180229

181-
if err := tools.InspectCommandResponse(ctx, r.dockerClient, cont.ID, execCommand.ID); err != nil {
182-
return errors.Wrap(err, "failed to exec the restore command")
183-
}
230+
log.Msg("Running restore command")
184231

185-
log.Msg("Restoring job has been finished")
232+
return nil
233+
}
186234

187-
if err := r.markDatabaseData(); err != nil {
188-
log.Err("Failed to mark database data: ", err)
235+
func isDatabaseReady(input io.Reader) error {
236+
scanner := bufio.NewScanner(input)
237+
238+
timer := time.NewTimer(time.Minute)
239+
defer timer.Stop()
240+
241+
for scanner.Scan() {
242+
select {
243+
case <-timer.C:
244+
return errors.New("timeout exceeded")
245+
default:
246+
}
247+
248+
text := scanner.Text()
249+
250+
if strings.Contains(text, readyLogLine) {
251+
return nil
252+
}
253+
254+
fmt.Println(text)
189255
}
190256

191-
return nil
257+
if err := scanner.Err(); err != nil {
258+
return err
259+
}
260+
261+
return errors.New("not found")
192262
}
193263

194264
func (r *RestoreJob) getEnvironmentVariables() []string {
@@ -226,22 +296,50 @@ func (r *RestoreJob) markDatabaseData() error {
226296
return r.dbMarker.SaveConfig(&dbmarker.Config{DataType: dbmarker.PhysicalDataType})
227297
}
228298

229-
func waitForCommandResponse(ctx context.Context, attachResponse types.HijackedResponse) error {
230-
waitCommandCh := make(chan struct{})
299+
func (r *RestoreJob) adjustRecoveryConfiguration(pgVersion, pgDataDir string) error {
300+
// Remove postmaster.pid.
301+
if err := os.Remove(path.Join(pgDataDir, "postmaster.pid")); err != nil && !errors.Is(err, os.ErrNotExist) {
302+
return errors.Wrap(err, "failed to remove postmaster.pid")
303+
}
304+
305+
// Truncate pg_ident.conf.
306+
if err := tools.TouchFile(path.Join(pgDataDir, "pg_ident.conf")); err != nil {
307+
return errors.Wrap(err, "failed to truncate pg_ident.conf")
308+
}
309+
310+
// Replication mode.
311+
var recoveryFilename string
312+
313+
if len(r.restorer.GetRecoveryConfig()) == 0 {
314+
return nil
315+
}
316+
317+
version, err := strconv.Atoi(pgVersion)
318+
if err != nil {
319+
return errors.Wrap(err, "failed to parse PostgreSQL version")
320+
}
321+
322+
const pgVersion12 = 12
231323

232-
go func() {
233-
if _, err := io.Copy(os.Stdout, attachResponse.Reader); err != nil {
234-
log.Err("failed to get command output:", err)
324+
if version >= pgVersion12 {
325+
if err := tools.TouchFile(path.Join(pgDataDir, "standby.signal")); err != nil {
326+
return err
235327
}
236328

237-
waitCommandCh <- struct{}{}
238-
}()
329+
recoveryFilename = "postgresql.conf"
330+
} else {
331+
recoveryFilename = "recovery.conf"
332+
}
333+
334+
recoveryFile, err := os.OpenFile(path.Join(pgDataDir, recoveryFilename), os.O_APPEND|os.O_CREATE|os.O_WRONLY, 0644)
335+
if err != nil {
336+
return err
337+
}
239338

240-
select {
241-
case <-ctx.Done():
242-
return ctx.Err()
339+
defer func() { _ = recoveryFile.Close() }()
243340

244-
case <-waitCommandCh:
341+
if _, err := recoveryFile.Write(r.restorer.GetRecoveryConfig()); err != nil {
342+
return err
245343
}
246344

247345
return nil

‎pkg/retrieval/engine/postgres/initialize/physical/wal_g.go

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,10 @@
55
package physical
66

77
import (
8+
"bytes"
9+
"fmt"
10+
"time"
11+
812
"github.com/docker/docker/api/types/mount"
913
)
1014

@@ -63,3 +67,14 @@ func (w *walg) GetMounts() []mount.Mount {
6367
func (w *walg) GetRestoreCommand() []string {
6468
return []string{"wal-g", "backup-fetch", restoreContainerPath, w.options.BackupName}
6569
}
70+
71+
// GetRecoveryConfig returns a recovery config to restore data.
72+
func (w *walg) GetRecoveryConfig() []byte {
73+
buffer := bytes.Buffer{}
74+
75+
buffer.WriteString("standby_mode = 'on'\n")
76+
buffer.WriteString(fmt.Sprintf("recovery_target_time = '%s'\n", time.Now().Format("2006-02-01 15:04:05")))
77+
buffer.WriteString("restore_command = 'wal-g wal-fetch %f %p'\n")
78+
79+
return buffer.Bytes()
80+
}

0 commit comments

Comments
 (0)