Обсуждение: Re. Backup
Hello Friends,
Looking for some help/input regarding backup issue we are having.
Env : PG => 12.11
Ubuntu => 20.04.4
We take regular backup along with WALs everyday no issues, but one day of
the week regularly backup fails with the following error.
ERROR: requested WAL segment 0000000C000008A10000000B has already been removed
We know the reason there is heavy database activity going during this period. But
trying to avoid addition of more WAL files, is there any way to modify our backup
command below which will take just the snapshot and not worry about the WALs?
playing with diff. options but no luck.
Thanks in advance,
Sushil...
On Thu, 2023-08-03 at 17:20 +0000, Sushil Shirodkar wrote:
> We take regular backup along with WALs everyday no issues, but one day of
> the week regularly backup fails with the following error.
>
> ERROR: requested WAL segment 0000000C000008A10000000B has already been removed
>
> We know the reason there is heavy database activity going during this period. But
> trying to avoid addition of more WAL files, is there any way to modify our backup
> command below which will take just the snapshot and not worry about the WALs?
> playing with diff. options but no luck.
>
> pg_basebackup -h ${MASTER_TCP_ADDRESS} -U ${REP_USER} -D ${BACKUP_DIR} -X fetch -Ft -zPw >>$LOG
You need the WAL, otherwise you cannot recover the backup.
Use "-X stream" instead of "-X fetch", and the problem will go away.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Hello Laurenz
If you see from your backup command you are using the fetch option so wals needed to make your backup consistent( can startup an instance) are backup after base backup is complete. So by the time wals are to be fetch, some required segments in the pg_wal directory has been recycled so it’s trying to fetch but cannot find the wals as it’s removed . So either you use the stream option which the wals needed will be copied along with the base backup or you increase your wal_keep_segment parameter to make sure enough wals are retained so during fetch , there should still be available in the pg_wal directory. I had same error before and solve it by increasing my wal_keep_segment .
On Thu, Aug 3, 2023 at 3:01 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2023-08-03 at 17:20 +0000, Sushil Shirodkar wrote:
> We take regular backup along with WALs everyday no issues, but one day of
> the week regularly backup fails with the following error.
>
> ERROR: requested WAL segment 0000000C000008A10000000B has already been removed
>
> We know the reason there is heavy database activity going during this period. But
> trying to avoid addition of more WAL files, is there any way to modify our backup
> command below which will take just the snapshot and not worry about the WALs?
> playing with diff. options but no luck.
>
> pg_basebackup -h ${MASTER_TCP_ADDRESS} -U ${REP_USER} -D ${BACKUP_DIR} -X fetch -Ft -zPw >>$LOG
You need the WAL, otherwise you cannot recover the backup.
Use "-X stream" instead of "-X fetch", and the problem will go away.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com