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?
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@snowman.net> wrote:
Greetings,
* Sri Linux (srilinux09@gmail.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.