can't get PITR working - is my understanding even correct?

[dulh...@mailbox.org]

I mange to take backups and recover them to my postgres-server. Now I want to see what PITR is able to do with these steps

1. create a backup (20220111T062908)
2. wait a minute
3. create a new database (at 06:34:11)
4. run a barman recover operation
   1. pg_ctl stop (on postgres-server)
      
   2. :~> barman recover vm-51150-0196 20220111T062908 --remote-ssh-command 'ssh postgres@[postgres-server]' --target-time 20220111T063500 /opt/db/data/postgres/data
      Starting remote restore for server vm-51150-0196 using backup 20220111T062908
      Destination directory: /opt/db/data/postgres/data
      Remote command: ssh postgres@[postgres-server]
      Doing PITR. Recovery target time: '2022-01-11 06:35:00+01:00'
      Using safe horizon time for smart rsync copy: 2022-01-11 06:29:08.521311+01:00
      Copying the base backup.
      Copying required WAL segments.
      Generating recovery configuration
      Identify dangerous settings in destination directory.
      IMPORTANT
      These settings have been modified to prevent data losses
      postgresql.conf line 242: archive_command = false
      postgresql.auto.conf line 5: recovery_target_time = None
      WARNING
      You are required to review the following options as potentially dangerous
      postgresql.conf line 760: include_if_exists = 'postgresql.conf.d/01_postgres_barman.conf' # include file only if it exists
      Recovery completed (start time: 2022-01-11 07:02:20.425453, elapsed time: 7 seconds)
      Your PostgreSQL server has been successfully prepared for recovery!
      
   3. pg_ctl start (on postgres-server)
5. check for my database from 06:34:11 which is not there

It looks like the recovery is getting me to the point of the backup (06:29:08) but not the the --target-time (06:35:00). Or do I understand something really wrong about the PITR logic?

Even though my gut tells me it can't be ... do I need another backup after 06:34 and then be able to do a PITR to a PIT between those 2 backups or am I missing something along the way?

[Luca Ferrari]

On Tue, Jan 11, 2022 at 7:06 AM dulhaver via Barman, Backup and
Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:
> It looks like the recovery is getting me to the point of the backup (06:29:08) but not the the --target-time (06:35:00). Or do I understand something really wrong about the PITR logic?

You probably did not get any activity between the "waiting a minute"
and "creating a database".
Remember that databases work on transaction boundaries, not time
boundaries. Therefore, if there were not any transactions between 6:29
and 6:34, it is perfectly fine to have the database at 6:29 because it
is the last "known" status.

>
> Even though my gut tells me it can't be ... do I need another backup after 06:34 and then be able to do a PITR to a PIT between those 2 backups or am I missing something along the way?
>

No, you need a backup _before_ the PITR you want to achieve, than you
could theoretically live with no other backups at all (if archiving is
working fine).

Luca

[dulh...@mailbox.org]

On 01/11/2022 9:48 AM Luca Ferrari <fluc...@gmail.com> wrote:

On Tue, Jan 11, 2022 at 7:06 AM dulhaver via Barman, Backup and

Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:

It looks like the recovery is getting me to the point of the backup (06:29:08) but not the the --target-time (06:35:00). Or do I understand something really wrong about the PITR logic?

You probably did not get any activity between the "waiting a minute"

and "creating a database".

correct. No other activity between those 2 events.

However I would have assumed that CREATE DATABASE is a valid transaction on a postgres cluster that should be written into a wal file.

apparently my basic understanding insufficient yet.

Remember that databases work on transaction boundaries, not time boundaries. Therefore, if there were not any transactions between 6:29

and 6:34, it is perfectly fine to have the database at 6:29 because it is the last "known" status.

are you implying database writes (like INSERT INTO on an existing db) would have had more chance to be recreated of they would have occured?

or is --target-time the entire wrong approach?

[Luca Ferrari]

On Tue, Jan 11, 2022 at 11:10 AM dulhaver via Barman, Backup and
Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:
> correct. No other activity between those 2 events.
>

I could have misunderstood your goal: you want to return to 6:35 that
is the last time the database had acitivity. This is equivalent to
restore without any target time, as far as I understand your
experiment.
Now, what is strange is that your database is not there, and this
could be due to a rush in starting the recovery. Could it be the
system did not archive the last WAL, thus your backup is incomplete?

> However I would have assumed that CREATE DATABASE is a valid transaction on a postgres cluster that should be written into a wal file.
> apparently my basic understanding insufficient yet.

Yes and no. Surely create database is written to the WALs, but AFAIK
you cannot rollback such operation, so it is not a transaction per-se.

> are you implying database writes (like INSERT INTO on an existing db) would have had more chance to be recreated of they would have occured?
> or is --target-time the entire wrong approach?

The recovery target time/transaction depends on what you are going to achieve.
I would repeat the experiment with either traffic after the create
database (e.g., a bulk set of inserts) or adding a wal_timemout option
to your PostgreSQL configuration.
Since you did not specify your setup and what you are trying to
achieve, it is hard to guess what did not match your expectetions.

Luca

[dulh...@mailbox.org]

On 01/11/2022 11:21 AM Luca Ferrari <fluc...@gmail.com> wrote:

On Tue, Jan 11, 2022 at 11:10 AM dulhaver via Barman, Backup and

Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:

correct. No other activity between those 2 events.

I could have misunderstood your goal: you want to return to 6:35 that is the last time the database had acitivity. This is equivalent to

restore without any target time, as far as I understand your experiment.

not quite. in chronological order

• (PIT1) backup
  
• (PIT2) create a database after (so it could not be included in the backup) and then
• recover the backup (PIT1) which is older then the new database with a target time (after PIT2) that should be catching the new database
  

I hope this is clearer now & sorry if it wasn't the last time

Now, what is strange is that your database is not there, and this could be due to a rush in starting the recovery. Could it be the

system did not archive the last WAL, thus your backup is incomplete?

What I can confirm is that the backup was not in WAITING FOR WALS (or however it is named) mode any longer when I did the recover (I am certain of that because it WAS in that stat and I waiting until the backup was not marked as such any longer.

However I did not wait any particular long time prior recovery due to this being an experiement. How can I detect now in retrospect, whether what you suspect was the case or not?

what would you suggest to be a minimal period that should have passed between a backup and it's recovery?

now talking about it ... is it mandatory to delete all contents of the $PGDATA directory before executing a backup?

However I would have assumed that CREATE DATABASE is a valid transaction on a postgres cluster that should be written into a wal file.

apparently my basic understanding insufficient yet.

Yes and no. Surely create database is written to the WALs, but AFAIK

you cannot rollback such operation, so it is not a transaction per-se.

are you implying database writes (like INSERT INTO on an existing db) would have had more chance to be recreated of they would have occured?

or is --target-time the entire wrong approach?

The recovery target time/transaction depends on what you are going to achieve.

I would repeat the experiment with either traffic after the create

database (e.g., a bulk set of inserts) or adding a wal_timemout option

to your PostgreSQL configuration.

Since you did not specify your setup and what you are trying to

achieve, it is hard to guess what did not match your expectetions.

Luca

--

--

You received this message because you are subscribed to the "Barman for PostgreSQL" group.

To post to this group, send email to pgba...@googlegroups.com

To unsubscribe from this group, send email to

pgbarman+u...@googlegroups.com

For more options, visit this group at

http://groups.google.com/group/pgbarman?hl=en?hl=en-GB

---

You received this message because you are subscribed to the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.

To unsubscribe from this group and stop receiving emails from it, send an email to pgbarman+u...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/pgbarman/CAKoxK%2B5bXw%3DfGbo1zGJ26O_evxHoX3fbtr9jqQEO24Q_HN0-ug%40mail.gmail.com.

[Luca Ferrari]

On Tue, Jan 11, 2022 at 1:00 PM dulhaver via Barman, Backup and
Recovery Manager for PostgreSQL <pgba...@googlegroups.com> wrote:
> However I did not wait any particular long time prior recovery due to this being an experiement. How can I detect now in retrospect, whether what you suspect was the case or not?
>

Depends on the traffic on the database, that in turns determines how
fast a new WAL will be filled and thus archived, plus the latency to
push the WAL on the other side.

> what would you suggest to be a minimal period that should have passed between a backup and it's recovery?
>

Being an experiment, place a low wal_timemout (10 seconds) to your
PostgreSQL server, and redo.

> now talking about it ... is it mandatory to delete all contents of the $PGDATA directory before executing a backup?

I suspect you were referring to "executing a restore". AFAIK barman
does not implement a delta restore as other tools, so the quick answer
is yes. Or just restore side-by-side your original PGDATA.

Luca