Re: Do results of pg_start_backup work without WAL segments created during backup?

Поиск
Список
Период
Сортировка
От Thorsten Schöning
Тема Re: Do results of pg_start_backup work without WAL segments created during backup?
Дата
Msg-id 16210428054.20190708125624@am-soft.de
обсуждение исходный текст
Ответ на Re: Do results of pg_start_backup work without WAL segments createdduring backup?  (Magnus Hagander <magnus@hagander.net>)
Ответы Re: Do results of pg_start_backup work without WAL segments createdduring backup?  (David Steele <david@pgmasters.net>)
Список pgsql-admin
Guten Tag Magnus Hagander,
am Montag, 8. Juli 2019 um 12:02 schrieben Sie:

> Then you have a corrupt and unusable backup.

But corrupt and unusable in what way? It obviously can't be that
corrupt that Postgres doesn't start at all or it wouldn't even be able
to replay WAL-archives, if present. But that's how the process works.
So if Postgres is designed to start with the copied files only, what's
the internal state it has? Why is it not a fully functional state with
only the data available when pg_start_backup ran?

> The pg_start_backup/pg_stop_backup method for backups can *only* be used
> together with a working log archive.

PITR is defined by having some arbitrary past data directory and
applying WAL-changes up until the point of interest. So not applying
WAL-archives is part of the whole design. Why are the few WALs created
during backup any special?

> No, without the WAL generated betweens tart and stop backup, the cluster
> will be incomplete and corrupt. In your description you are for example not
> counting for activity that happens *during* the checkpoint.

But why do I need to care? From my understanding, Postgres does
checkpointing to flush buffers and persist data into actual table
files to get a consistent state of its data. Exactly that's what
pg_start_backup guarantees as well, it only doesn't guarantee that no
changes are applied afterwards, but there is one consistent state.

If it was otherwise, how could crash recovery of Postgres work at all
if things always depend on WAL-segments? Don't failures to write
WAL-segements only result in missing data instead of a completely
broken cluster? From my understanding, what gets copied after
pg_start_backup is a cluster in somewhat the same state like after a
crash of Postgres.

> [...]If there are any doubts whatsoever on how they interact in your
> environment, you should *really* be looking at one of those higher level
> tools.

I am, something like barman on my NAS would be great. But currently
I'm left with a backup script only doing pg_start|stop_backup, copying
(parts of) the data directory and not taking the WAL-archives created
during the backup into account. So I'm trying to understand what I
have there, if the backup is completely useless that way or not.

From my point of view it looks like some usable full backup simply
missing incremental parts, which are the WAL-archives. So adding some
download of all WAL-archives available at some point in time without
caring if they have been created during the full backup or afterwards,
would make the backup more current easily.

If the full backup works at all... :-) And if it doesn't, I would like
to know what I don't understand yet. So in any case thanks for your
arguments!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: Thorsten.Schoening@AM-SoFT.de
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow




В списке pgsql-admin по дате отправления:

Предыдущее
От: Magnus Hagander
Дата:
Сообщение: Re: Do results of pg_start_backup work without WAL segments createdduring backup?
Следующее
От: David Steele
Дата:
Сообщение: Re: Do results of pg_start_backup work without WAL segments createdduring backup?