Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 192237e

Browse filesBrowse files
agneumagneum
committed
feat: basic Postgres configuration (physical & logical) (#141)
1 parent 4ded7b9 commit 192237e
Copy full SHA for 192237e

File tree

Expand file treeCollapse file tree

18 files changed

+564
-220
lines changed
Open diff view settings
Filter options
Expand file treeCollapse file tree

18 files changed

+564
-220
lines changed
Open diff view settings
Collapse file

‎cmd/database-lab/main.go‎

Copy file name to clipboardExpand all lines: cmd/database-lab/main.go
+4Lines 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

Collapse file

‎configs/config.sample.yml‎

Copy file name to clipboardExpand all lines: configs/config.sample.yml
+25-4Lines 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
Collapse file

‎configs/postgres/postgresql.conf‎

Copy file name to clipboardExpand all lines: configs/postgres/postgresql.conf
+9-3Lines 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.
Collapse file

‎pkg/config/config.go‎

Copy file name to clipboardExpand all lines: pkg/config/config.go
+3-2Lines 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.
Collapse file

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

Copy file name to clipboardExpand all lines: pkg/retrieval/engine/postgres/initialize/physical/custom.go
+5Lines 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+
}
Collapse file

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

Copy file name to clipboardExpand all lines: pkg/retrieval/engine/postgres/initialize/physical/physical.go
+124-26Lines 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
Collapse file

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

Copy file name to clipboardExpand all lines: pkg/retrieval/engine/postgres/initialize/physical/wal_g.go
+15Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.