bareos-fd-plugin: new python PostgreSQL by bruno-at-bareos · Pull Request #1541 · bareos/bareos

bruno-at-bareos · 2023-08-31T15:15:01Z

This PR propose a new PostgreSQL plugin for Bareos. It use python 3 (>=3.6), pg8000 (>=1.16) and use the non exclusive backup mode available upstream since version 9.6.
PostgreSQL cluster version >= 10 are supported.

The plugin after the backup create 3 virtual files backup_label, recovery.signal (or recovery.conf) and tablespace_map if needed, which are restored with the cluster. This allow user to just setup the recovery_command after the restore.

Documentation has been updated (see all new config option)

This plugin will be packaged into the same package name as the old one,
(which will be still distributed to allow restore of previous backup during lifetime of Bareos 23 version).

Breaking changes: (need to be noticed) the plugin name has changed (postgres-> postgresql) and most of the options have been renamed and need a manual review of existing fileset to make new backup successful.

Please check

Short description and the purpose of this PR is present above this paragraph
Your name is present in the AUTHORS file (optional)
Deprecated old plugin postgres with either settings warning.

If you have any questions or problems, please give a comment in the PR.

Helpful documentation and best practices

Checklist for the reviewer of the PR (will be processed by the Bareos team)

Make sure you check/merge the PR using devtools/pr-tool to have some simple automated checks run and a proper changelog record added.

General

Is the PR title usable as CHANGELOG entry?
Purpose of the PR is understood
Commit descriptions are understandable and well formatted
Check backport line
Required backport PRs have been created

Source code quality

Source code changes are understandable
Variable and function names are meaningful
Code comments are correct (logically and spelling)
Required documentation changes are present and part of the PR

Tests

Decision taken that a test is required (if not, then remove this paragraph)
The choice of the type of test (unit test or systemtest) is reasonable
Testname matches exactly what is being tested
On a fail, output of the test leads quickly to the origin of the fault

systemtests/tests/py3plug-fd-postgres/testrunner-default

core/src/plugins/filed/python/postgresql/bareos-fd-postgresql.py

pstorz

Good work! Please see comments.

core/src/filed/fd_plugins.cc

core/src/plugins/filed/python/postgresql/bareos-fd-postgresql.py

docs/manuals/source/TasksAndConcepts/Plugins/FileDaemonPlugins/PostgreSQLPlugin.rst.inc

systemtests/tests/py3plug-fd-postgres/CMakeLists.txt

pstorz

See comments

docs/manuals/source/TasksAndConcepts/Plugins/FileDaemonPlugins/PostgreSQLPlugin.rst.inc

core/src/plugins/filed/python/postgresql/bareos-fd-postgresql.py

HOSTED-POWER · 2023-09-18T14:26:23Z

Hi , is there any easy way for us to test it? Did the name indeed change to bareos-fd-postgresql (instead of using bareos-fd-postgres) ?

bruno-at-bareos · 2023-09-18T14:38:11Z

@HOSTED-POWER

Hi , is there any easy way for us to test it? Did the name indeed change to bareos-fd-postgresql (instead of using bareos-fd-postgres) ?

I believe the easiest way is to start the bareos-fd 23 with the bareos-fd-postgresql plugin setup see packages here
https://download.bareos.org/experimental/PR-1541/

and documentation

Doing it that way will give you next build of the plugin when changes will occurs during the last steps of development.

core/src/plugins/filed/python/postgresql/bareos-fd-postgresql.py

sebsura

Here is how one could fix the ret naming issue.

core/src/filed/fd_plugins.cc

pstorz

Good work. Please see comments!

core/src/plugins/filed/python/postgresql/bareos-fd-postgresql.py

docs/manuals/source/TasksAndConcepts/Plugins/FileDaemonPlugins/PostgreSQLPlugin.rst.inc

systemtests/tests/py3plug-fd-postgresql/database/make_cluster_activity.sql

systemtests/tests/py3plug-fd-postgresql/testrunner-default

systemtests/tests/py3plug-fd-postgresql/testrunner-tablespace

systemtests/tests/py3plug-fd-postgresql/CMakeLists.txt

- pg8000 may return Decimal('0') when lsn are identical, we now check if we can remove quote from string before int() conversion. - improve job log message - remove no more used functions format_lsn and lsn_to_int signed-off-by: Bruno Friedmann <bruno.friedmann@bareos.com>

- applied all review suggestions - do formating too long lines - describe difference between ignore_subdirs, and mandatory_subdirs Signed-off-by: Bruno Friedmann <bruno.friedmann@bareos.com>

- normalize sql to lowercase - improve message logging in _close_db_connection

- As the old "postgres" plugin was replaced with the new "postresql" plugin, we want to warn everybody using the old plugin to switch to the new plugin.

- do mtime comparision only for WAL in Incremental - replace anxious warning job message by registering a debug message for skipped files - do try to set attribute on symlink in python (tablespace restore case)

Also remove trailing spaces in multiple lines.

Signed-off-by: Bruno Friedmann <bruno.friedmann@bareos.com>

HOSTED-POWER · 2023-10-30T17:53:05Z

We just tested but there seems to be a problem with symlinks at first sight...

Since the real location of the postgresql data dir can vary from version to version, we had to symlink postgresql data dir to some other location. We then backup the installation trough the symlink. E.g.

ln -s /var/lib/postgresql/15/main /var/lib/pgsql_data

The backup configuration uses: postgresql_data_dir=/var/lib/pgsql_data

Then the backup runs fine , and I believe it takes all data. However when we restore, we end up with just a symlink to pgsql_data

In the restore window we see the data should be there, but we cannot find it when restoring.

HOSTED-POWER · 2023-10-30T17:55:42Z

I checked postgresql restart and it's very well possible all data on the target system inside /var/lib/postgresql/15/main was just overwritten when restoring, even when we restored to /tmp/restore :/

HOSTED-POWER · 2023-10-30T18:10:24Z

OK finally I moved the original postgresql data dir, I see indeed it will overwrite the postgres folder immediately, I don't think this is normally what should happen?

Also I see some strange errors when effectively restoring the data


2023-10-30 19:06:33.391 CET [2241201] LOG:  starting PostgreSQL 15.4 - Percona Distribution on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-10-30 19:06:33.391 CET [2241201] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-10-30 19:06:33.393 CET [2241201] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-10-30 19:06:33.399 CET [2241204] LOG:  database system was interrupted; last known up at 2023-10-30 18:26:50 CET
2023-10-30 19:06:33.399 CET [2241204] LOG:  creating missing WAL directory "pg_wal/archive_status"
2023-10-30 19:06:33.586 CET [2241204] LOG:  restored log file "00000002.history" from archive
cp: cannot stat '/var/lib/pgsql_archive/00000003.history': No such file or directory
2023-10-30 19:06:33.588 CET [2241204] LOG:  starting archive recovery
2023-10-30 19:06:33.590 CET [2241204] LOG:  restored log file "00000002.history" from archive
2023-10-30 19:06:33.611 CET [2241204] LOG:  restored log file "000000010000002600000059" from archive
2023-10-30 19:06:33.631 CET [2241204] LOG:  redo starts at 26/59000028
2023-10-30 19:06:33.664 CET [2241204] LOG:  restored log file "00000001000000260000005A" from archive
2023-10-30 19:06:33.702 CET [2241204] LOG:  restored log file "00000002000000260000005B" from archive
2023-10-30 19:06:33.723 CET [2241204] LOG:  consistent recovery state reached at 26/590027C8
2023-10-30 19:06:33.723 CET [2241201] LOG:  database system is ready to accept read-only connections
2023-10-30 19:06:33.759 CET [2241204] LOG:  restored log file "00000002000000260000005C" from archive
cp: cannot stat '/var/lib/pgsql_archive/00000002000000260000005D': No such file or directory
cp: cannot stat '/var/lib/pgsql_archive/00000002000000260000005D': No such file or directory
2023-10-30 19:06:33.784 CET [2241204] LOG:  redo done at 26/5C0000D8 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.15 s
2023-10-30 19:06:33.784 CET [2241204] LOG:  last completed transaction was at log time 2023-10-30 18:53:49.861901+01
2023-10-30 19:06:33.805 CET [2241204] LOG:  restored log file "00000002000000260000005C" from archive
cp: cannot stat '/var/lib/pgsql_archive/00000003.history': No such file or directory
2023-10-30 19:06:33.836 CET [2241204] LOG:  selected new timeline ID: 3
2023-10-30 19:06:33.872 CET [2241204] LOG:  restored log file "00000002.history" from archive
2023-10-30 19:06:33.873 CET [2241204] LOG:  archive recovery complete
2023-10-30 19:06:33.876 CET [2241202] LOG:  checkpoint starting: end-of-recovery immediate wait
2023-10-30 19:06:33.900 CET [2241202] LOG:  checkpoint complete: wrote 12 buffers (0.0%); 0 WAL file(s) added, 0 removed, 4 recycled; write=0.016 s, sync=0.003 s, total=0.025 s; sync files=12, longest=0.001 s, average=0.001 s; distance=65536 kB, estimate=65536 kB
2023-10-30 19:06:33.906 CET [2241201] LOG:  database system is ready to accept connections

Which is correct, those files were misssing, e.g. this for example was not found in the backup:

cp: cannot stat '/var/lib/pgsql_archive/00000003.history': No such file or directory

bruno-at-bareos · 2023-10-31T09:01:21Z

We just tested but there seems to be a problem with symlinks at first sight...

Since the real location of the postgresql data dir can vary from version to version, we had to symlink postgresql data dir to some other location. We then backup the installation trough the symlink. E.g.

ln -s /var/lib/postgresql/15/main /var/lib/pgsql_data

The backup configuration uses: postgresql_data_dir=/var/lib/pgsql_data

Then the backup runs fine , and I believe it takes all data. However when we restore, we end up with just a symlink to pgsql_data

In the restore window we see the data should be there, but we cannot find it when restoring.

That test case was not used, and unfortunately, your report appear after the merge window.
So nothing else will be changed to the code in this PR.

Please open a issue in mantis with those information, joblog and configuration.
You can also extend the systemtest used to cover this particular case

bruno-at-bareos added the draft label Aug 31, 2023

sebsura reviewed Sep 1, 2023

View reviewed changes

systemtests/tests/py3plug-fd-postgres/testrunner-default Show resolved Hide resolved

bruno-at-bareos force-pushed the dev/bruno/master/bareosfdpluginpostgresql branch from ec25570 to 5c701d5 Compare September 4, 2023 08:53

bruno-at-bareos removed the draft label Sep 4, 2023

bruno-at-bareos force-pushed the dev/bruno/master/bareosfdpluginpostgresql branch from 5c701d5 to 1611e63 Compare September 4, 2023 11:29

pstorz reviewed Sep 5, 2023

View reviewed changes

core/src/plugins/filed/python/postgresql/bareos-fd-postgresql.py Outdated Show resolved Hide resolved

arogge reviewed Sep 12, 2023

View reviewed changes

core/src/plugins/filed/python/postgresql/bareos-fd-postgresql.py Outdated Show resolved Hide resolved

pstorz self-requested a review September 14, 2023 10:20

pstorz reviewed Sep 15, 2023

View reviewed changes

pstorz self-requested a review September 15, 2023 10:00

pstorz requested changes Sep 15, 2023

View reviewed changes

bruno-at-bareos commented Sep 18, 2023

View reviewed changes

docs/manuals/source/TasksAndConcepts/Plugins/FileDaemonPlugins/PostgreSQLPlugin.rst.inc Outdated Show resolved Hide resolved

bruno-at-bareos commented Sep 18, 2023

View reviewed changes

core/src/plugins/filed/python/postgresql/bareos-fd-postgresql.py Outdated Show resolved Hide resolved

bruno-at-bareos commented Sep 18, 2023

View reviewed changes

core/src/plugins/filed/python/postgresql/bareos-fd-postgresql.py Outdated Show resolved Hide resolved

sebsura reviewed Sep 20, 2023

View reviewed changes

core/src/filed/fd_plugins.cc Outdated Show resolved Hide resolved

core/src/filed/fd_plugins.cc Outdated Show resolved Hide resolved

core/src/filed/fd_plugins.cc Show resolved Hide resolved

pstorz assigned bruno-at-bareos Sep 28, 2023

bruno-at-bareos force-pushed the dev/bruno/master/bareosfdpluginpostgresql branch 2 times, most recently from ecb4e9d to 31b4089 Compare October 5, 2023 15:31

bruno-at-bareos mentioned this pull request Oct 10, 2023

BareosFdPluginPostgres.py: use environment variables for db connection #1570

Closed

15 tasks

bruno-at-bareos force-pushed the dev/bruno/master/bareosfdpluginpostgresql branch from 31b4089 to 897a86a Compare October 10, 2023 12:14

bruno-at-bareos requested review from arogge, pstorz and sebsura October 12, 2023 08:53

bruno-at-bareos force-pushed the dev/bruno/master/bareosfdpluginpostgresql branch from 5c84f46 to 1fbf473 Compare October 16, 2023 12:53

pstorz requested changes Oct 17, 2023

View reviewed changes

sebsura reviewed Oct 18, 2023

View reviewed changes

systemtests/tests/py3plug-fd-postgresql/CMakeLists.txt Show resolved Hide resolved

pstorz added the documentation This PR changes only documentation label Oct 23, 2023

bruno-at-bareos removed the documentation This PR changes only documentation label Oct 26, 2023

bruno-at-bareos and others added 23 commits October 30, 2023 17:13

apply bareos-check-sources clang-format/copyright year

29b8201

plugin postgresql: documentation adjustments

e737e25

- applied all review suggestions - do formating too long lines - describe difference between ignore_subdirs, and mandatory_subdirs Signed-off-by: Bruno Friedmann <bruno.friedmann@bareos.com>

remove used comments

7439451

add review suggestions

811a18a

add reviews suggestions

5b2d983

- normalize sql to lowercase - improve message logging in _close_db_connection

normalize sql to lowercase

8c56a84

JobMessages: add newline where required

074d243

BareosFdPluginPostgres: Issue deprecation warning

9c12b75

- As the old "postgres" plugin was replaced with the new "postresql" plugin, we want to warn everybody using the old plugin to switch to the new plugin.

plugin postgresql: file selection and logging improvements

1cb17aa

- do mtime comparision only for WAL in Incremental - replace anxious warning job message by registering a debug message for skipped files - do try to set attribute on symlink in python (tablespace restore case)

BareosFdPluginPostgres: apply python black formating

a86a388

PostgreSQLPlugin.rst.inc:45: fix sphinx indentation

4bd70b3

Also remove trailing spaces in multiple lines.

doc: fix redirected links

3b3fec0

Signed-off-by: Bruno Friedmann <bruno.friedmann@bareos.com>

postgresql test cmake remove uneeded test

afbab09

PostgreSQLPlugin.rst.inc: introduce uml diagram

0e1155c

Fixup: use better icons for database and data directories

e285b6a

PostgreSQLPlugin.rst.inc: add restore figure

d3d2fe4

PostgreSQLPlugin.rst.inc: add inc/diff backup figure

8f53134

PostgreSQLPlugin.rst.inc: adapt participant icons

bfc8153

PostgreSQLPlugin.rst.inc: add feedback from review

985b7d8

postgresql plugin: explicitly remove differential

b8acc00

Signed-off-by: Bruno Friedmann <bruno.friedmann@bareos.com>

postgresql: add breaking changes

7980d53

Update CHANGELOG.md

ae2a24a

BareosBot force-pushed the dev/bruno/master/bareosfdpluginpostgresql branch from 733e561 to ae2a24a Compare October 30, 2023 17:13

BareosBot merged commit cab4ac6 into bareos:master Oct 30, 2023

bruno-at-bareos deleted the dev/bruno/master/bareosfdpluginpostgresql branch October 31, 2023 16:50

Conversation

bruno-at-bareos commented Aug 31, 2023 • edited by pstorz Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Please check

Helpful documentation and best practices

Checklist for the reviewer of the PR (will be processed by the Bareos team)

General

Source code quality

Tests

Uh oh!

Uh oh!

Uh oh!

Uh oh!

pstorz left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

pstorz left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

HOSTED-POWER commented Sep 18, 2023

Uh oh!

bruno-at-bareos commented Sep 18, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Uh oh!

sebsura left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

pstorz left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

HOSTED-POWER commented Oct 30, 2023

Uh oh!

HOSTED-POWER commented Oct 30, 2023

Uh oh!

HOSTED-POWER commented Oct 30, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

bruno-at-bareos commented Oct 31, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

bruno-at-bareos commented Aug 31, 2023 •

edited by pstorz

Loading

bruno-at-bareos commented Sep 18, 2023 •

edited

Loading

HOSTED-POWER commented Oct 30, 2023 •

edited

Loading

bruno-at-bareos commented Oct 31, 2023 •

edited

Loading