Обсуждение: What's the dfifference between pg_start_backup+copy+pg_stop_backup+WAL vs. pg_start_backup+pg_stop_backup+copy+WAL?
Hi everyone,
there's the following example in the docs about hot backups:
> touch /var/lib/pgsql/backup_in_progress
> psql -c "select pg_start_backup('hot_backup');"
> tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
> psql -c "select pg_stop_backup();"
> rm /var/lib/pgsql/backup_in_progress
> tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
https://www.postgresql.org/docs/11/continuous-archiving.html#BACKUP-TIPS
These are the important things to keep in mind from my understanding:
1. The data directory of Postgres is designed to be crash safe[1] and
unless files are forcefully deleted or otherwise damaged, PG should
recover from a crash. It does so with the help of its WAL files, so
those define to which point PG recovers, which data is actually
available, which state databases have in the end etc.
2. Copying the data directory might take a long time, during which PG
is free to write to any file. Though, "pg_start_backup" guarantees
crash safety again by creating a CHECKPOINT[2] for the data directory
and additional WALs are used to record any changes during copying.
"pg_stop_backup" tells about the last WAL necessary to replay all
changes during the backup, regardless of how the data dir changed at
the same time. Things are based on crash safety of "pg_start_backup".
When having atomic file system snapshots available, the advice is to
simply use those[1], because PG has crash safety. State is frozen this
way and when actually copying files, changes during how long the copy
takes are NOT taken into account anymore because of the snapshot.
Without those snapshots pg_start_backup+copy+pg_stop_backup+WAL is
suggested instead[1]. WAL-archiving assures that all changes during
the copy up until the logical end of the backup issued by executing
"pg_stop_backup" are available.
But what if I'm not interested in the changes during copying the data
directory? This is exactly the same use case like when using file
system snapshots, for which I don't get changes during the backup
process itself as well.
So a process of "pg_start_backup", directly followed by a
"pg_stop_backup" with assuring that the WALs during both calls are
preserved, should result in a valid backup, shouldn't it?
An afterwards copied data directory is crash safe again up to the
point "pg_start_backup" has been called and can recover using the WALs
up to the point "pg_stop_backup" has been called. Regardless how long
copying the data directory takes and which changes Postgres applies.
Those changes are simply discarded during recovery, aren't they?
So one might lose changes during the backup process by not following
the order of the docs, but again, I might not care, like with file
system snapshots.
Or what's the difference when looking at consistency/recovery compared
to some point in time only between FS level snapshots and using
pg_start|stop_backup+WALs in different order?
From my understanding, pg_start_backup+pg_stop_backup+FS-snapshot
should even be the most correct approach, as long as the created WALs
are contained in the snapshot. Other thoughts?
Thanks!
[1]: https://dba.stackexchange.com/a/44262/99683
[2]: https://www.postgresql.org/docs/11/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
Mit freundlichen Grüßen
Thorsten Schöning
--
AM-SoFT IT-Service - Bitstore Hameln GmbH
Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK
E-Mail: Thorsten.Schoening@AM-SoFT.de
Web: http://www.AM-SoFT.de/
Tel: 05151- 9468- 0
Tel: 05151- 9468-55
Fax: 05151- 9468-88
Mobil: 0178-8 9468-04
AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska
Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung.
Mit freundlichen Grüßen
Thorsten Schöning
Tel: 05151 9468 0
Fax: 05151 9468 88
Mobil:
Webseite: https://www.am-soft.de
AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister
fürIT und TK
AM-Soft IT-Service - Bitstore Hameln GmbH i.G.
Brandenburger Str. 7c
31789 Hameln
Tel: 05151 9468 0
Bitstore IT-Consulting GmbH
Zentrale - Berlin Lichtenberg
Frankfurter Allee 285
10317 Berlin
Tel: 030 453 087 80
CBS IT-Service - Bitstore Kaulsdorf UG
Tel: 030 453 087 880 1
Büro Dallgow-Döberitz
Tel: 03322 507 020
Büro Kloster Lehnin
Tel: 033207 566 530
PCE IT-Service - Bitstore Darmstadt UG
Darmstadt
Tel: 06151 392 973 0
Büro Neuruppin
Tel: 033932 606 090
ACI EDV Systemhaus - Bitstore Dresden GmbH
Dresden
Tel: 0351 254 410
Das Systemhaus - Bitstore Magdeburg GmbH
Magdeburg
Tel: 0391 636 651 0
Allerdata.IT - Bitstore Wittenberg GmbH
Wittenberg
Tel: 03491 876 735 7
Büro Liebenwalde
Tel: 033054 810 00
HSA - das Büro - Bitstore Altenburg UG
Altenburg
Tel: 0344 784 390 97
Bitstore IT – Consulting GmbH
NL Piesteritz
Piesteritz
Tel: 03491 644 868 6
Solltec IT-Services - Bitstore Braunschweig UG
Braunschweig
Tel: 0531 206 068 0
MF Computer Service - Bitstore Gütersloh GmbH
Gütersloh
Tel: 05245 920 809 3
Firmensitz: AM-Soft IT-Service - Bitstore Hameln GmbH i.G. , Brandenburger Str. 7c , 31789 Hameln
Geschäftsführer Janine Galonska
Guten Tag Laurenz Albe, am Freitag, 23. Juli 2021 um 12:41 schrieben Sie: > The only way to cope with that is to replay > WAL to a point in time *after* the last data file was backed up. > But with your proposed backup method, you have no way to determine > that point in time, so this is not safe at all. I think your example is bad: If both rows are added in independent transactions, possibly inconsistent table data simply needs to be accepted. No backup mechanism will change anything on that, because the second row might always be inserted "too late". If both rows are inserted in the same transaction, things depend on when that got committed: If it's before pg_stop_backup, it's at least contained in the last WAL and Postgres can recover to that point. If it's committed after pg_stop_backup, the change is lost regardless how files have been copied before. > This is exactly the purpose of pg_stop_backup: it does not only > archive the WAL segment that completes the backup, but it also adds > a BACKUP_END record to the WAL stream. With having pg_start_backup+pg_stop_backup that record is available. > That ensures that recovery > cannot stop too early: any attempt to end recovery before reaching > BACKUP_END will cause a fatal error. And here's the point: File system snapshots don't have that record at all, but are considered safe because of the crash safety guarantees built into Postgres. So as long as the data directory makes somewhat sense, Postgres obviously is able to start and does some recovering. That recovering is the only difference: In case of file system snapshots it recovers beginning from the last CHECKPOINT, as that is considered safe, and applies additional WALs as available. With pg_start_backup+copy+pg_stop_backup, the state of the finally copied data directory is unknown as well. It might be the same checkpoint when pg_start_backup finished or might be a newer one, because copying data took so long or whatever. For recovery, Postgres can only rely on the currently available checkpoint of the data directory OR what is stored in the backup_label file: > START WAL LOCATION: 428/40000060 (file 000000010000042800000040) > CHECKPOINT LOCATION: 428/40000098 > BACKUP METHOD: pg_start_backup > BACKUP FROM: master > START TIME: 2021-07-23 08:05:25 CEST > LABEL: postgres_files_full_on_ext4 > START TIMELINE: 1 So what does it do? From my understanding it starts from the checkpoint location of the file and recovers using the available WALs. Because that is the only way to not rely on the state of the copied data directory too much. Or does it take a more current checkpoint available in the data directory into account? From my understanding it can't: The files declaring that more current checkpoint might have been copied after all other files have been copied already, but without the data necessary for the checkpoint in the end. To prevent that, Postgres starts in the past by using CHECKPOINT LOCATION. And here's the point again: When it always starts from the checkpoint stored in backup_label, from a crash safety perspective it doesn't make any difference if files are copied before or after pg_stop_backup. The only difference would be that all data during the copying process itself would need to be considered lost. But that might easily be acceptable, backups become outdated all the time. > As soon as BACKUP_END is > processed, the startup process knows that the database is now > consistent (and logs a message to that extent). BACKUP_END will still be processed when copying after pg_stop_backup. > You can contine > recovering to any later point in time, but you need not > (recovery_target = immediate). Because things depend on the available WALs and not the files in the data directory. This would still be the case when copying after pg_stop_backup. > With your proposed method, you run the danger of stopping recovery too > early, with the consequence of ending up with a corrupted database. But BACKUP_END would be available in both cases. Mit freundlichen Grüßen Thorsten Schöning -- AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK E-Mail: Thorsten.Schoening@AM-SoFT.de Web: http://www.AM-SoFT.de/ Tel: 05151- 9468- 0 Tel: 05151- 9468-55 Fax: 05151- 9468-88 Mobil: 0178-8 9468-04 AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung. Mit freundlichen Grüßen Thorsten Schöning Tel: 05151 9468 0 Fax: 05151 9468 88 Mobil: Webseite: https://www.am-soft.de AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister fürIT und TK AM-Soft IT-Service - Bitstore Hameln GmbH i.G. Brandenburger Str. 7c 31789 Hameln Tel: 05151 9468 0 Bitstore IT-Consulting GmbH Zentrale - Berlin Lichtenberg Frankfurter Allee 285 10317 Berlin Tel: 030 453 087 80 CBS IT-Service - Bitstore Kaulsdorf UG Tel: 030 453 087 880 1 Büro Dallgow-Döberitz Tel: 03322 507 020 Büro Kloster Lehnin Tel: 033207 566 530 PCE IT-Service - Bitstore Darmstadt UG Darmstadt Tel: 06151 392 973 0 Büro Neuruppin Tel: 033932 606 090 ACI EDV Systemhaus - Bitstore Dresden GmbH Dresden Tel: 0351 254 410 Das Systemhaus - Bitstore Magdeburg GmbH Magdeburg Tel: 0391 636 651 0 Allerdata.IT - Bitstore Wittenberg GmbH Wittenberg Tel: 03491 876 735 7 Büro Liebenwalde Tel: 033054 810 00 HSA - das Büro - Bitstore Altenburg UG Altenburg Tel: 0344 784 390 97 Bitstore IT – Consulting GmbH NL Piesteritz Piesteritz Tel: 03491 644 868 6 Solltec IT-Services - Bitstore Braunschweig UG Braunschweig Tel: 0531 206 068 0 MF Computer Service - Bitstore Gütersloh GmbH Gütersloh Tel: 05245 920 809 3 Firmensitz: AM-Soft IT-Service - Bitstore Hameln GmbH i.G. , Brandenburger Str. 7c , 31789 Hameln Geschäftsführer Janine Galonska
Greetings, * Thorsten Schöning (tschoening@am-soft.de) wrote: > Guten Tag Laurenz Albe, > am Freitag, 23. Juli 2021 um 12:41 schrieben Sie: > > > The only way to cope with that is to replay > > WAL to a point in time *after* the last data file was backed up. > > But with your proposed backup method, you have no way to determine > > that point in time, so this is not safe at all. > > I think your example is bad: If both rows are added in independent > transactions, possibly inconsistent table data simply needs to be > accepted. No backup mechanism will change anything on that, because > the second row might always be inserted "too late". > > If both rows are inserted in the same transaction, things depend on > when that got committed: If it's before pg_stop_backup, it's at least > contained in the last WAL and Postgres can recover to that point. If > it's committed after pg_stop_backup, the change is lost regardless how > files have been copied before. > > > This is exactly the purpose of pg_stop_backup: it does not only > > archive the WAL segment that completes the backup, but it also adds > > a BACKUP_END record to the WAL stream. > > With having pg_start_backup+pg_stop_backup that record is available. Sure it's available, but it will be wrong because the mark in the WAL where BACKUP_END is isn't actually the point you have to replay WAL until to make sure your system is consistent. > > That ensures that recovery > > cannot stop too early: any attempt to end recovery before reaching > > BACKUP_END will cause a fatal error. > > And here's the point: File system snapshots don't have that record at > all, but are considered safe because of the crash safety guarantees > built into Postgres. So as long as the data directory makes somewhat > sense, Postgres obviously is able to start and does some recovering. Atomic filesystem snapshots work because we can figure out what the ending point of the WAL is by simply replaying the WAL that existed at the time of the snapshot. Once we hit the end of the valid WAL, we know that we're done. > That recovering is the only difference: In case of file system > snapshots it recovers beginning from the last CHECKPOINT, as that is > considered safe, and applies additional WALs as available. Yes, it's understood that everything written to the heap prior to the latest checkpoint was sync'd, and trustworthy, and everything written after that point is in the WAL, and we know the end of that WAL because we read through the WAL that existed at the time and discover what the end point was. > With pg_start_backup+copy+pg_stop_backup, the state of the finally > copied data directory is unknown as well. It might be the same > checkpoint when pg_start_backup finished or might be a newer one, > because copying data took so long or whatever. For recovery, Postgres > can only rely on the currently available checkpoint of the data > directory OR what is stored in the backup_label file: > > > START WAL LOCATION: 428/40000060 (file 000000010000042800000040) > > CHECKPOINT LOCATION: 428/40000098 > > BACKUP METHOD: pg_start_backup > > BACKUP FROM: master > > START TIME: 2021-07-23 08:05:25 CEST > > LABEL: postgres_files_full_on_ext4 > > START TIMELINE: 1 > > So what does it do? From my understanding it starts from the > checkpoint location of the file and recovers using the available WALs. > Because that is the only way to not rely on the state of the copied > data directory too much. > > Or does it take a more current checkpoint available in the data > directory into account? From my understanding it can't: The files > declaring that more current checkpoint might have been copied after > all other files have been copied already, but without the data > necessary for the checkpoint in the end. To prevent that, Postgres > starts in the past by using CHECKPOINT LOCATION. > > And here's the point again: When it always starts from the checkpoint > stored in backup_label, from a crash safety perspective it doesn't > make any difference if files are copied before or after > pg_stop_backup. The only difference would be that all data during the > copying process itself would need to be considered lost. But that > might easily be acceptable, backups become outdated all the time. What matters is making sure to play all of the outstanding WAL because until that's done, the database isn't consistent. When it comes to atomic filesystem snapshots, that can be done by replaying from the last checkpoint all of the WAL in the pg_wal directory until it ends- once you get to the end, you know that you've replayed everything and the system is consistent. Similar, once you reach the BACKUP_END record of a backup you know the database is consistent- but only if all of the files that PG was writing to were copied between the start checkpoint of the backup and the BACKUP_END record in the WAL. > > As soon as BACKUP_END is > > processed, the startup process knows that the database is now > > consistent (and logs a message to that extent). > > BACKUP_END will still be processed when copying after pg_stop_backup. Sure, but, again, it'll be wrong and therefore the database would be opened up for reads or could even be promoted before reaching consistency, leading to a corrupt system. It's absolutely critical that all of the WAL be replayed in order for the system to be consistent. > > With your proposed method, you run the danger of stopping recovery too > > early, with the consequence of ending up with a corrupted database. > > But BACKUP_END would be available in both cases. Doesn't matter that it's available if it's in the wrong place. As is often brought up elsewhere, if we don't have to be correct then things are a lot easier and can be made a lot faster. :) Thanks, Stephen
Вложения
Guten Tag Stephen Frost, am Freitag, 23. Juli 2021 um 17:24 schrieben Sie: > [...]Similar, once you reach the BACKUP_END record of > a backup you know the database is consistent- but only if all of the > files that PG was writing to were copied between the start checkpoint > of the backup and the BACKUP_END record in the WAL. So I am correct that the recovery process starts at what is documented as CHECKPOINT LOCATION in the file backup_label? If the data directory looks like a more current checkpoint might be available, that is NOT used? Instead, all available WALs are replayed? But how come the state of other data files into the recovery process? that's what Laurenz Albe's example is about: A data file which is more current than expected because that file is only copied, but the associated changes are stored in WALs after pg_stop_backup and therefore need to be considered lost. Why is the data file with that additional row in B used at all? The additional row is associated with a transaction number not available when restoring based on CHECKPOINT LOCATION + WALs of pg_stop_backup. So why is that row used at all instead of simply ignored? That would actually revert the cluster to the state of pg_stop_backup, but as said, backup outdate anyway. I still don't get why the result is not as consistent as e.g. a crash at the same point in time instead of doing a backup. Mit freundlichen Grüßen Thorsten Schöning -- AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK E-Mail: Thorsten.Schoening@AM-SoFT.de Web: http://www.AM-SoFT.de/ Tel: 05151- 9468- 0 Tel: 05151- 9468-55 Fax: 05151- 9468-88 Mobil: 0178-8 9468-04 AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung. Mit freundlichen Grüßen Thorsten Schöning Tel: 05151 9468 0 Fax: 05151 9468 88 Mobil: Webseite: https://www.am-soft.de AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister fürIT und TK AM-Soft IT-Service - Bitstore Hameln GmbH i.G. Brandenburger Str. 7c 31789 Hameln Tel: 05151 9468 0 Bitstore IT-Consulting GmbH Zentrale - Berlin Lichtenberg Frankfurter Allee 285 10317 Berlin Tel: 030 453 087 80 CBS IT-Service - Bitstore Kaulsdorf UG Tel: 030 453 087 880 1 Büro Dallgow-Döberitz Tel: 03322 507 020 Büro Kloster Lehnin Tel: 033207 566 530 PCE IT-Service - Bitstore Darmstadt UG Darmstadt Tel: 06151 392 973 0 Büro Neuruppin Tel: 033932 606 090 ACI EDV Systemhaus - Bitstore Dresden GmbH Dresden Tel: 0351 254 410 Das Systemhaus - Bitstore Magdeburg GmbH Magdeburg Tel: 0391 636 651 0 Allerdata.IT - Bitstore Wittenberg GmbH Wittenberg Tel: 03491 876 735 7 Büro Liebenwalde Tel: 033054 810 00 HSA - das Büro - Bitstore Altenburg UG Altenburg Tel: 0344 784 390 97 Bitstore IT - Consulting GmbH NL Piesteritz Piesteritz Tel: 03491 644 868 6 Solltec IT-Services - Bitstore Braunschweig UG Braunschweig Tel: 0531 206 068 0 MF Computer Service - Bitstore Gütersloh GmbH Gütersloh Tel: 05245 920 809 3 Firmensitz: AM-Soft IT-Service - Bitstore Hameln GmbH i.G. , Brandenburger Str. 7c , 31789 Hameln Geschäftsführer Janine Galonska
> On Jul 23, 2021, at 11:44 AM, Thorsten Schöning <tschoening@am-soft.de> wrote: > > So why is that row used at all instead of simply ignored? Because it is in the file. WAL can re-do changes, it cannot undo them.
Greetings, * Thorsten Schöning (tschoening@am-soft.de) wrote: > Guten Tag Stephen Frost, > am Freitag, 23. Juli 2021 um 17:24 schrieben Sie: > > > [...]Similar, once you reach the BACKUP_END record of > > a backup you know the database is consistent- but only if all of the > > files that PG was writing to were copied between the start checkpoint > > of the backup and the BACKUP_END record in the WAL. > > So I am correct that the recovery process starts at what is documented > as CHECKPOINT LOCATION in the file backup_label? If the data directory > looks like a more current checkpoint might be available, that is NOT > used? Instead, all available WALs are replayed? Yes, the recovery has to start from the checkpoint in the backup label. The fact that pg_control might have a more recent checkpoint is exactly the problem- any files which were copied before the checkpoint in pg_control wouldn't have the correct contents and wouldn't be fixed by the recovery process. Furthermore, if PG were to stop replaying before getting to the correct backup-end location then the database might not be consistent at that point because some records had been written into the WAL as committed but hadn't made it to the heap yet. There's risk on both sides. > But how come the state of other data files into the recovery process? > that's what Laurenz Albe's example is about: A data file which is more > current than expected because that file is only copied, but the > associated changes are stored in WALs after pg_stop_backup and > therefore need to be considered lost. I'm not sure that you're quite understanding the example because we always write to WAL first and sync that before writing to the heap, so there isn't a case of a "more recent" file which is somehow ahead of what's in the WAL. > Why is the data file with that additional row in B used at all? The > additional row is associated with a transaction number not available > when restoring based on CHECKPOINT LOCATION + WALs of pg_stop_backup. The data file is copied because it's part of the database..? If you didn't copy it, then you'd be missing a lot of data. As for the question about if that row would be visible, it certainly might be- but it depends on when the transaction log files were copied, or if perhaps the tuple ended up being frozen somehow. > So why is that row used at all instead of simply ignored? That would > actually revert the cluster to the state of pg_stop_backup, but as > said, backup outdate anyway. I still don't get why the result is not > as consistent as e.g. a crash at the same point in time instead of > doing a backup. How would you know to ignore it if that tuple was frozen? A crashed PG cluster isn't consistent until WAL replay from the last checkpoint to the ending point of WAL has completed. That's exactly the same with a backup, but the checkpoint you have to start from is the one stored in the backup label and the point in the WAL that has to be reached for consistency is the one that's at the backup-end location in the WAL. Otherwise they're both the same. The reason you have to start from the start-backup checkpoint is exactly because the data files are being copied over a period of time and not all at once- unlike with a crash where everything stops at the same time (and if that doesn't happen, you definitely do have the risk of having corruption from such a partial-crash, though we've tried our best to detect such cases and deal with them, but we need the kernel to actually tell us when there's a problem and that's been something of an interesting challenge..). > Mit freundlichen Grüßen > > Thorsten Schöning Seriously, when your signature is twice the size of your email, it's too much. Please cut it down when posting to these lists, we certainly don't need hundreds of copied of it in our archives or everyone's mail boxes. Thanks, Stephen
Вложения
On 7/23/21 11:24 AM, Stephen Frost wrote: > * Thorsten Schöning (tschoening@am-soft.de) wrote: >> Guten Tag Laurenz Albe, >> am Freitag, 23. Juli 2021 um 12:41 schrieben Sie: > >> That recovering is the only difference: In case of file system >> snapshots it recovers beginning from the last CHECKPOINT, as that is >> considered safe, and applies additional WALs as available. > > Yes, it's understood that everything written to the heap prior to the > latest checkpoint was sync'd, and trustworthy, and everything written > after that point is in the WAL, and we know the end of that WAL because > we read through the WAL that existed at the time and discover what the > end point was. There is another wrinkle here. If you copy files outside of pg_start/stop_backup() then postgres will not replay over any torn (and in some cases zero) pages, even if a full page write is available. Torn pages are expected during crash recovery and backup recovery before the end-of-backup WAL record. Outside of those cases you'll get a fatal error. In that case your backup would be entirely useless, even if you knew exactly where to replay to. Regards, -- -David david@pgmasters.net
Guten Tag Stephen Frost, am Freitag, 23. Juli 2021 um 20:10 schrieben Sie: > Seriously, when your signature is twice the size of your email, it's too > much. Please cut it down when posting to these lists[...] I would love to, but I can't, because that bloat is added by the companies mail server forcefully automatically. Yes, I hate it as well, especially because it's even added without "-- ". So my apologies, but I already tried to get rid of that multiple times. Mit freundlichen Grüßen Thorsten Schöning -- AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK E-Mail: Thorsten.Schoening@AM-SoFT.de Web: http://www.AM-SoFT.de/ Tel: 05151- 9468- 0 Tel: 05151- 9468-55 Fax: 05151- 9468-88 Mobil: 0178-8 9468-04 AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung. Mit freundlichen Grüßen Thorsten Schöning Tel: 05151 9468 0 Fax: 05151 9468 88 Mobil: Webseite: https://www.am-soft.de AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister fürIT und TK AM-Soft IT-Service - Bitstore Hameln GmbH i.G. Brandenburger Str. 7c 31789 Hameln Tel: 05151 9468 0 Bitstore IT-Consulting GmbH Zentrale - Berlin Lichtenberg Frankfurter Allee 285 10317 Berlin Tel: 030 453 087 80 CBS IT-Service - Bitstore Kaulsdorf UG Tel: 030 453 087 880 1 Büro Dallgow-Döberitz Tel: 03322 507 020 Büro Kloster Lehnin Tel: 033207 566 530 PCE IT-Service - Bitstore Darmstadt UG Darmstadt Tel: 06151 392 973 0 Büro Neuruppin Tel: 033932 606 090 ACI EDV Systemhaus - Bitstore Dresden GmbH Dresden Tel: 0351 254 410 Das Systemhaus - Bitstore Magdeburg GmbH Magdeburg Tel: 0391 636 651 0 Allerdata.IT - Bitstore Wittenberg GmbH Wittenberg Tel: 03491 876 735 7 Büro Liebenwalde Tel: 033054 810 00 HSA - das Büro - Bitstore Altenburg UG Altenburg Tel: 0344 784 390 97 Bitstore IT - Consulting GmbH NL Piesteritz Piesteritz Tel: 03491 644 868 6 Solltec IT-Services - Bitstore Braunschweig UG Braunschweig Tel: 0531 206 068 0 MF Computer Service - Bitstore Gütersloh GmbH Gütersloh Tel: 05245 920 809 3 Firmensitz: AM-Soft IT-Service - Bitstore Hameln GmbH i.G. , Brandenburger Str. 7c , 31789 Hameln Geschäftsführer Janine Galonska
Guten Tag Scott Ribe, am Freitag, 23. Juli 2021 um 20:07 schrieben Sie: > Because it is in the file. WAL can re-do changes, it cannot undo them. But isn't visibility of rows associated to transaction numbers as well? I would have expected the transaction number for the row in B simply not being available, because it was never part of the WALs up to and including pg_stop_backup when not following the official ordering of commands. So I expected that row to simply not be visible at all and vacuumed at some point or whatever. Mit freundlichen Grüßen Thorsten Schöning -- AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK E-Mail: Thorsten.Schoening@AM-SoFT.de Web: http://www.AM-SoFT.de/ Tel: 05151- 9468- 0 Tel: 05151- 9468-55 Fax: 05151- 9468-88 Mobil: 0178-8 9468-04 AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung. Mit freundlichen Grüßen Thorsten Schöning Tel: 05151 9468 0 Fax: 05151 9468 88 Mobil: Webseite: https://www.am-soft.de AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister fürIT und TK AM-Soft IT-Service - Bitstore Hameln GmbH i.G. Brandenburger Str. 7c 31789 Hameln Tel: 05151 9468 0 Bitstore IT-Consulting GmbH Zentrale - Berlin Lichtenberg Frankfurter Allee 285 10317 Berlin Tel: 030 453 087 80 CBS IT-Service - Bitstore Kaulsdorf UG Tel: 030 453 087 880 1 Büro Dallgow-Döberitz Tel: 03322 507 020 Büro Kloster Lehnin Tel: 033207 566 530 PCE IT-Service - Bitstore Darmstadt UG Darmstadt Tel: 06151 392 973 0 Büro Neuruppin Tel: 033932 606 090 ACI EDV Systemhaus - Bitstore Dresden GmbH Dresden Tel: 0351 254 410 Das Systemhaus - Bitstore Magdeburg GmbH Magdeburg Tel: 0391 636 651 0 Allerdata.IT - Bitstore Wittenberg GmbH Wittenberg Tel: 03491 876 735 7 Büro Liebenwalde Tel: 033054 810 00 HSA - das Büro - Bitstore Altenburg UG Altenburg Tel: 0344 784 390 97 Bitstore IT – Consulting GmbH NL Piesteritz Piesteritz Tel: 03491 644 868 6 Solltec IT-Services - Bitstore Braunschweig UG Braunschweig Tel: 0531 206 068 0 MF Computer Service - Bitstore Gütersloh GmbH Gütersloh Tel: 05245 920 809 3 Firmensitz: AM-Soft IT-Service - Bitstore Hameln GmbH i.G. , Brandenburger Str. 7c , 31789 Hameln Geschäftsführer Janine Galonska
Greetings, * Thorsten Schöning (tschoening@am-soft.de) wrote: > Guten Tag Scott Ribe, > am Freitag, 23. Juli 2021 um 20:07 schrieben Sie: > > > Because it is in the file. WAL can re-do changes, it cannot undo them. > > But isn't visibility of rows associated to transaction numbers as > well? I would have expected the transaction number for the row in B > simply not being available, because it was never part of the WALs up > to and including pg_stop_backup when not following the official > ordering of commands. So I expected that row to simply not be visible > at all and vacuumed at some point or whatever. The information about what transactions have committed and what haven't isn't just in the WAL- it's in the tuple header for frozen tuples, or all visible tuples, and it's in the transaction log files otherwise, so it would depend on when those are copied. Thanks, Stephen
Вложения
Greetings, * Thorsten Schöning (tschoening@am-soft.de) wrote: > Guten Tag Stephen Frost, > am Freitag, 23. Juli 2021 um 20:10 schrieben Sie: > > > Seriously, when your signature is twice the size of your email, it's too > > much. Please cut it down when posting to these lists[...] > > I would love to, but I can't, because that bloat is added by the > companies mail server forcefully automatically. Yes, I hate it as > well, especially because it's even added without "-- ". So my > apologies, but I already tried to get rid of that multiple times. In my view, you really need to go get a free gmail or other account to use when posting to public, archived, mailing lists with hundreds of people on them then. Thanks, Stephen
Вложения
Guten Tag Stephen Frost, am Freitag, 23. Juli 2021 um 21:23 schrieben Sie: > In my view, you really need to go get a free gmail or other account to > use when posting to public, archived, mailing lists with hundreds of > people on them then. OTOH, I was pretty clearly advised to use company mail for all work related communication, and this clearly is such, because of central archiving and stuff. So I can only ask for the patience of people on the lists and that they try to ignore the bloat like I'm trying. Mit freundlichen Grüßen Thorsten Schöning -- AM-SoFT IT-Service - Bitstore Hameln GmbH Mitglied der Bitstore Gruppe - Ihr Full-Service-Dienstleister für IT und TK E-Mail: Thorsten.Schoening@AM-SoFT.de Web: http://www.AM-SoFT.de/ Tel: 05151- 9468- 0 Tel: 05151- 9468-55 Fax: 05151- 9468-88 Mobil: 0178-8 9468-04 AM-SoFT IT-Service - Bitstore Hameln GmbH, Brandenburger Str. 7c, 31789 Hameln AG Hannover HRB 221853 - Geschäftsführer: Janine Galonska