pgbackrest bug on postgresql 15

[waqas7pk]

Please provide the following information when submitting an issue (feature requests or general comments can skip this):

1. pgBackRest version: pgBackRest 2.51

2. PostgreSQL version: 15

3. Operating system/version - if you have more than one server (for example, a database server, a repository host server, one or more standbys), please specify each: Rocky Linux

4. Did you install pgBackRest from source or from a package? NO

5. Please attach the following as applicable:

   • pgbackrest.conf file(s)
   • postgresql.conf settings applicable to pgBackRest (archive_command, archive_mode, listen_addresses, max_wal_senders, wal_level, port)
   • errors in the postgresql log file before or during the time you experienced the issue
   • log file in /var/log/pgbackrest for the commands run (e.g. /var/log/pgbackrest/mystanza_backup.log)

6. Describe the issue:
   We found

unable to execute query 'select lsn::text as lsn,
pg_catalog.pg_walfile_name(lsn)::text as wal_segment_name
from pg_catalog.pg_backup_start('pgBackRest backup started at ' || current_timestamp, false) as lsn': ERROR: invalid input syntax for type timestamp with time zone: "pgBackRest backup started at "
LINE 3: from pg_catalog.pg_backup_start('pgBackRest backup started...

[pgstef]

Hi,

Could you provide more details? I mean I can't reproduce that issue with PG 15 + pgBR 2.51 installed using PGDG packages on Rocky 9: the backup command works correctly.

• what flavor of PostgreSQL did you install and how?
• how did you install pgBackRest?
• where do you get that error exactly?

Regards

[waqas7pk]

what flavor of PostgreSQL did you install and how?
postgresql 15 with agedb

how did you install pgBackRest?
by yum repo

where do you get that error exactly?
when i am executing backup command

command
pgbackrest --stanza=agens --type=full backup --pg-version-force=15

when backup execute start it is immediately fail
select lsn::text as lsn,
pg_catalog.pg_walfile_name(lsn)::text as wal_segment_name
from pg_catalog.pg_backup_start('pgBackRest backup started at ' || current_timestamp, false) as lsn': ERROR: invalid input syntax for type timestamp with time zone: "pgBackRest backup started at "
LINE 3: from pg_catalog.pg_backup_start('pgBackRest backup started...
^
2024-04-19 16:51:48.717 P00 INFO: backup command end: aborted with exception [057]

[pgstef]

what flavor of PostgreSQL did you install and how? postgresql 15 with agedb

Then you're not using official community PostgreSQL, which we only support. You'll need to ask your vendor (agedb) what backup tools they are supporting.

command pgbackrest --stanza=agens --type=full backup --pg-version-force=15

The --pg-version-force option is a "maintainer" option and should be used with caution. It is asking pgBackRest to act "as if the target server is an official community PostgreSQL" but then it is up to the maintainer to make sure they don't mess with any internals and are supporting this software. We can't build-in support for any Postgres fork out there...

Kind Regards

[waqas7pk]

Issue is pertaining to syntax of pg_catalog.pg_backup_start function calling from pgbackrest i guess.

It should be

select lsn::text as lsn,
pg_catalog.pg_walfile_name(lsn)::text as wal_segment_name
from pg_catalog.pg_backup_start('pgBackRest backup started at ' || current_timestamp::text, false) as lsn

Instead of

select lsn::text as lsn,
pg_catalog.pg_walfile_name(lsn)::text as wal_segment_name
from pg_catalog.pg_backup_start('pgBackRest backup started at ' || current_timestamp, false) as lsn

change:

adding ::text

[pgstef]

Issue is pertaining to syntax of pg_catalog.pg_backup_start function calling from pgbackrest i guess.

This syntax works perfectly fine with community version of PostgreSQL.

Re-opening the issue to see what @dwsteele thinks about this but imho it doesn't worth changing the query used aiming at a single fork without their maintainers explaining why they would need by i.e. submitting a PR to ask for a change.

IMO, my last comment about "ask your vendor support what's wrong with that select 'text ' || current_timestamp syntax which is working on official PG and not on their fork.

[dwsteele]

I'm not against adding a cast here since we add them explicitly in many places, but I worry that the fix would be fragile and it may not be the only place that needs changing.

It seems like it would be good to find out from the agedb maintainers why this doesn't work.

Also -- I worry about just pointing pgbackrest at a fork and expecting it to work. Unless agedb lists pgbackrest as supported and tested backup software, I'd be pretty nervous about using it.

[waqas7pk]

Response awaited