Can't start test Postgres server after barman-cloud-restore, missing checkpoint

[Aleksey Tsalolikhin]

Hello,

I'm still working on a proof of concept of using Barman to backup and restore our Postgres databases.  I got it working with a Barman server and am now trying the cloud scripts (instead of using a Barman server), so that we can store the backups in cloud storage.

I ran "barman-cloud-backup" which uploaded a backup to my bucket. I can list and show the backup. But after I restored the backup and tried to start Postgres, it complained about  a missing checkpoint record:

postgres@recovery-test:~$ barman-cloud-restore --cloud-provider google-cloud-storage gs://bucket-name/my-backups/ pg-primary 20240802T001302 /data/pg
postgres@recovery-test:~$ logout
ubuntu@recovery-test:~$ sudo tail -f /var/log/postgresql/postgresql-13-main.log &
[1] 28157
ubuntu@recovery-test:~$ 2024-08-01 18:53:08.971 UTC [20955] LOG:  database system was interrupted; last known up at 2024-08-01 18:40:50 UTC
...
ubuntu@recovery-test:~$ sudo systemctl start postgresql
2024-08-02 00:36:51.781 UTC [28183] LOG:  starting PostgreSQL ...
2024-08-02 00:36:51.781 UTC [28183] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-08-02 00:36:51.781 UTC [28183] LOG:  listening on IPv6 address "::", port 5432
2024-08-02 00:36:51.784 UTC [28183] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-08-02 00:36:51.790 UTC [28184] LOG:  database system was interrupted; last known up at 2024-08-02 00:13:02 UTC
2024-08-02 00:36:51.790 UTC [28184] LOG:  creating missing WAL directory "pg_wal/archive_status"
2024-08-02 00:36:51.807 UTC [28184] LOG:  invalid checkpoint record
2024-08-02 00:36:51.807 UTC [28184] FATAL:  could not locate required checkpoint record
2024-08-02 00:36:51.807 UTC [28184] HINT:  If you are restoring from a backup, touch "/data/pg/recovery.signal" and add required recovery options.
        If you are not restoring from a backup, try removing the file "/data/pg/backup_label".
        Be careful: removing "/data/pg/backup_label" will result in a corrupt cluster if restoring from a backup.
2024-08-02 00:36:51.808 UTC [28183] LOG:  startup process (PID 28184) exited with exit code 1
2024-08-02 00:36:51.808 UTC [28183] LOG:  aborting startup due to startup process failure
2024-08-02 00:36:51.894 UTC [28183] LOG:  database system is shut down
pg_ctl: could not start server

I tried backing up again, with the --immediate-checkpoint switch.  I have two backups now:

$ barman-cloud-backup-list  --cloud-provider google-cloud-storage gs://my-bucket/my-backups/ pg-primary
Backup ID           End Time                 Begin Wal                     Archival Status  Name
20240802T001302     2024-08-02 00:13:03      000000010000000000000016
20240802T004849     2024-08-02 00:48:50      000000010000000000000018
$

Is the Archival Status column supposed to be blank?

I still couldn't start the server, same error about missing checkpoint.

Gratefully,

Aleksey

[Aleksey Tsalolikhin]

The Barman FAQ <https://pgbarman.org/faq/> says:

        I do not want to manage backup space and want my disaster recovery solution to be scalable and elastic in terms of disk resources. Do you support storage on Amazon EC2 facilities?

        Currently, no. However barman has been designed to one day integrate WAL shipping and backup archiving with Amazon S3 storage. We are looking for sponsors willing to fund the development of this feature and add it to barman‘s open-source version.

Is that up to date?  From my reading, checkpoint records are stored in WAL files?  And it looks like my Postgres on the recovery instance is complaining about missing checkpoint records?   I just feel like I'm missing something. I'm not sure if it's because the documentation is out of date or my understanding is incomplete.  I am not a Postgres expert.

Best,

-at

[Aleksey Tsalolikhin]

Of course, I was missing the WAL archiving/recovery.  Fixed.  I was able to backup to and recover from Google Cloud Storage.

Thanks to Rob Aleck for his "Notes from the field":   https://blog.cynexia.com/how-to-restore-backups-of-postgresql-from-s3-using-barman/#step-5-configure-wal-recovery