Re: Need help with PITR for PostgreSQL 9.4.5

Hi All

I have to start Postgres 9.4.5 vacuum for our production environment. Got
interrupted with the Linux session, is there a way I can monitor if the
vacuum is progressing while I reconnect to the Linux box?

Thanks
Sri

On Thu, Jun 25, 2020 at 1:16 PM Sri Linux <srilinux09(at)gmail(dot)com> wrote:

> 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
>>
>