Re: Need help with PITR for PostgreSQL 9.4.5

Thanks for your support.

I will try to restore and provide results shortly without restoring pg_xlog
file

Regards,
Sri

On Thu, Jun 25, 2020 at 1:09 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Sri Linux (srilinux09(at)gmail(dot)com) wrote:
> > Please find the method used. Please recommend me if I have done
> > something wrong...
>
> Yes, you are using 'cp' which is *not* recommended for an archive
> command.
>
> > Performing a hot backup using pg_basebackup:
> > Create a new folder as the postgres user
> > pg_basebackup --xlog --format=t -D /server01/pgbackup/`date %Y%m%d`
>
> Note that your pg_basebackup is going to be copying WAL also, in
> addition to the archive_command you've configured.
>
> > Restoring from Backup:
> > Extract the contents of base.tar from the backed up folder on top of the
> PostgreSQL installation folder:
> > tar -xf base.tar -C /var/lib/pgsql/9.4/data (RedHat/CentOS)
> > Assuming that there is a single database tar file (named with a number)
> in the backup, extract the contents of this folder to the /server01 folder:
> > tar -xf <number>.tar -C /server01
> > Copy any unarchived WAL log files saved from the first step back into
> the pg_xlog folder appropriate for the OS
>
> Not sure what "first step" means here, but you are configuring PostgreSQL
> with a recovery.conf later with a restore command to fetch the WAL it needs
> from your archive, so you shouldn't be needing to copy files from one
> pg_xlog to another (which is just generally a bad idea..).
>
> Further, the error you're getting, as mentioned, is actually that you've
> somehow ended up with WAL for some other cluster in your archive and
> when this instance tries to restore it, it complains (quite
> understandably). A sensible tool would prevent this from being able to
> happen by checking that the WAL that's being archived to a given
> location matches the database that the WAL is for.
>
> As mentioned, you should really be considering using a purpose-built
> tool which manages this for you, such as pgbackrest, which has such
> checks and provides you with backup/restore commands.
>
> Thanks,
>
> Stephen
>