Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[Feature]: disable WAL archiving during import bootstrap #3741

Closed
2 tasks done
gbartolini opened this issue Jan 29, 2024 · 1 comment · Fixed by #3743
Closed
2 tasks done

[Feature]: disable WAL archiving during import bootstrap #3741

gbartolini opened this issue Jan 29, 2024 · 1 comment · Fixed by #3743
Assignees
@gbartolini
Labels
triage Pending triage

Comments

@gbartolini
Copy link
Contributor

Is there an existing issue already for this feature request/idea?

  • I have searched for an existing issue, and could not find anything. I believe this is a new feature request to be evaluated.

What problem is this feature going to solve? Why should it be added?

When using the import facility of the initdb bootstrap method in a job, the PostgreSQL instance has the following values:

  • archive_mode is set to on (fixed)
  • archive_command is left empty
  • wal_level is set to logical (fixed)

The empty archive_command is fine with both standard initdb and recovery bootstraps, as it will have the effect to accumulate WAL files for the short duration of the instance run time.

However, in the import phase, the Postgres instance may run for quite some time and accumulate a lot of WAL files, increasing the risk of running out of disk in the WAL partition with subsequent failure of the cluster deployment.

Describe the solution you'd like

In the specific import phase, we should set, just for the job duration:

  • archive_mode is set to off
  • wal_level is set to minimal

This will also improve the performance of the loading phase.

We should also consider setting full_page_writes to off in this phase.

From a HA & disaster point of view, once the job is completed and the database is imported, the pod starts with WAL archiving, replicas are created from there, and - if requested - the first backup is taken. We should inform users that, depending on the size of the database, the time taken for the first backup and for replica cloning might take longer.

Describe alternatives you've considered

Look at #3731

Additional context

N/A

Backport?

Yes

Are you willing to actively contribute to this feature?

Yes

Code of Conduct

  • I agree to follow this project's Code of Conduct
@gbartolini gbartolini added the triage Pending triage label Jan 29, 2024
@gbartolini gbartolini self-assigned this Jan 29, 2024
@gbartolini gbartolini mentioned this issue Jan 29, 2024
Closed
2 tasks
@gbartolini
Copy link
Contributor Author

Thinking more about this, we should definitely:

  • set fsync to off
  • set full_page_writes to off

And then, before we conclude the job, run:

initdb -S

See the initdb -S option for details.

gbartolini added a commit that referenced this issue Jan 29, 2024
@gbartolini
feat: disable WAL archiving during import bootstrap
51af28f 
When using the import facility of the initdb bootstrap method in a job,
optimize the PostgreSQL instance for the sole duration of the job, by setting
the following GUCs:

- `archive_mode` to `off`
- `fsync` to `off`
- `full_page_writes` to `off`
- `wal_level` to `minimal`

Then, restore the configuration and invoke `initdb --sync-only` before the end
of the import job.

Closes #3741

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
@gbartolini gbartolini mentioned this issue Jan 29, 2024
Merged
armru pushed a commit that referenced this issue Jan 29, 2024
@gbartolini @armru
feat: disable WAL archiving during import bootstrap
606560f 
When using the import facility of the initdb bootstrap method in a job,
optimize the PostgreSQL instance for the sole duration of the job, by setting
the following GUCs:

- `archive_mode` to `off`
- `fsync` to `off`
- `full_page_writes` to `off`
- `wal_level` to `minimal`

Then, restore the configuration and invoke `initdb --sync-only` before the end
of the import job.

Closes #3741

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
gbartolini added a commit that referenced this issue Jan 30, 2024
@gbartolini
feat: disable WAL archiving during import bootstrap
83ff6ea 
When using the import facility of the initdb bootstrap method in a job,
optimize the PostgreSQL instance for the sole duration of the job, by setting
the following GUCs:

- `archive_mode` to `off`
- `fsync` to `off`
- `full_page_writes` to `off`
- `wal_level` to `minimal`

Then, restore the configuration and invoke `initdb --sync-only` before the end
of the import job.

Closes #3741

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
mnencia pushed a commit that referenced this issue Jan 30, 2024
@gbartolini @mnencia
feat: disable WAL archiving during import bootstrap
17ff00e 
When using the import facility of the initdb bootstrap method in a job,
optimize the PostgreSQL instance for the sole duration of the job, by setting
the following GUCs:

- `archive_mode` to `off`
- `fsync` to `off`
- `full_page_writes` to `off`
- `wal_level` to `minimal`

Then, restore the configuration and invoke `initdb --sync-only` before the end
of the import job.

Closes #3741

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
mnencia pushed a commit that referenced this issue Jan 30, 2024
@gbartolini @mnencia
feat: disable WAL archiving during import bootstrap
4ea7672 
When using the import facility of the initdb bootstrap method in a job,
optimize the PostgreSQL instance for the sole duration of the job, by setting
the following GUCs:

- `archive_mode` to `off`
- `fsync` to `off`
- `full_page_writes` to `off`
- `wal_level` to `minimal`

Then, restore the configuration and invoke `initdb --sync-only` before the end
of the import job.

Closes #3741

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
mnencia added a commit that referenced this issue Jan 30, 2024
@gbartolini @armru @mnencia
fix: disable WAL archiving during import bootstrap (#3743)
9e58cbd 
When using the import facility of the initdb bootstrap method in a job,
optimize the PostgreSQL instance for the sole duration of the job by
setting the following GUCs:

- `archive_mode` to `off`
- `fsync` to `off`
- `full_page_writes` to `off`
- `max_wal_senders` to `0`
- `wal_level` to `minimal`

Then, restore the configuration and invoke `initdb --sync-only` before
the end of the import job.

This avoids unnecessary and systematic piling of WAL files during any
import job.

Closes #3741

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
Signed-off-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
Signed-off-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
Co-authored-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
Co-authored-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
cnpg-bot pushed a commit that referenced this issue Jan 30, 2024
@gbartolini
fix: disable WAL archiving during import bootstrap (#3743)
29ffa1f 
When using the import facility of the initdb bootstrap method in a job,
optimize the PostgreSQL instance for the sole duration of the job by
setting the following GUCs:

- `archive_mode` to `off`
- `fsync` to `off`
- `full_page_writes` to `off`
- `max_wal_senders` to `0`
- `wal_level` to `minimal`

Then, restore the configuration and invoke `initdb --sync-only` before
the end of the import job.

This avoids unnecessary and systematic piling of WAL files during any
import job.

Closes #3741

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
Signed-off-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
Signed-off-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
Co-authored-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
Co-authored-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
(cherry picked from commit 9e58cbd)
cnpg-bot pushed a commit that referenced this issue Jan 30, 2024
@gbartolini
fix: disable WAL archiving during import bootstrap (#3743)
a488a90 
When using the import facility of the initdb bootstrap method in a job,
optimize the PostgreSQL instance for the sole duration of the job by
setting the following GUCs:

- `archive_mode` to `off`
- `fsync` to `off`
- `full_page_writes` to `off`
- `max_wal_senders` to `0`
- `wal_level` to `minimal`

Then, restore the configuration and invoke `initdb --sync-only` before
the end of the import job.

This avoids unnecessary and systematic piling of WAL files during any
import job.

Closes #3741

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
Signed-off-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
Signed-off-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
Co-authored-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
Co-authored-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
(cherry picked from commit 9e58cbd)
cnpg-bot pushed a commit that referenced this issue Jan 30, 2024
@gbartolini
fix: disable WAL archiving during import bootstrap (#3743)
acd9562 
When using the import facility of the initdb bootstrap method in a job,
optimize the PostgreSQL instance for the sole duration of the job by
setting the following GUCs:

- `archive_mode` to `off`
- `fsync` to `off`
- `full_page_writes` to `off`
- `max_wal_senders` to `0`
- `wal_level` to `minimal`

Then, restore the configuration and invoke `initdb --sync-only` before
the end of the import job.

This avoids unnecessary and systematic piling of WAL files during any
import job.

Closes #3741

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
Signed-off-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
Signed-off-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
Co-authored-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
Co-authored-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
(cherry picked from commit 9e58cbd)
armru added a commit that referenced this issue Feb 1, 2024
@gbartolini @armru @mnencia
fix: disable WAL archiving during import bootstrap (#3743)
6c83432 
When using the import facility of the initdb bootstrap method in a job,
optimize the PostgreSQL instance for the sole duration of the job by
setting the following GUCs:

- `archive_mode` to `off`
- `fsync` to `off`
- `full_page_writes` to `off`
- `max_wal_senders` to `0`
- `wal_level` to `minimal`

Then, restore the configuration and invoke `initdb --sync-only` before
the end of the import job.

This avoids unnecessary and systematic piling of WAL files during any
import job.

Closes #3741

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
Signed-off-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
Signed-off-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
Co-authored-by: Armando Ruocco <armando.ruocco@enterprisedb.com>
Co-authored-by: Marco Nenciarini <marco.nenciarini@enterprisedb.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@gbartolini gbartolini

Labels
triage Pending triage
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

fix: disable WAL archiving during import bootstrap cloudnative-pg/cloudnative-pg
1 participant
@gbartolini