Thread: Need help with PITR for PostgreSQL 9.4.5

Need help with PITR for PostgreSQL 9.4.5

From
Sri Linux
Date:
Hi All,

I am trying to do PINT backup and recovery for the standalone database which is not in a cluster. 

recovery.done is created, after restart database is having errors to start "

< 2020-06-22 16:34:08.280 CDT >LOG:  starting archive recovery
< 2020-06-22 16:34:08.456 CDT >LOG:  restored log file "000000010000000000000001" from archive
< 2020-06-22 16:34:08.487 CDT >LOG:  WAL file is from different database system: WAL file database system identifier is 6840038030519879233, pg_control database system identifier is 6841285371464745049.
< 2020-06-22 16:34:08.487 CDT >LOG:  invalid primary checkpoint record


Please suggest me right method to backup and restore for RHEL OS.


Thanks and Regards
Deepthi


Re: Need help with PITR for PostgreSQL 9.4.5

From
"David G. Johnston"
Date:
The version you are running is neither up-to-date for its major version (9.4) nor is the major version being supported.


Thoug a functioning backup is good to have before upgrading, especially major versions.

On Wednesday, June 24, 2020, Sri Linux <srilinux09@gmail.com> wrote:
I am trying to do PINT backup and recovery for the standalone database which is not in a cluster. 

That is not possible.  Its unclear what exactly you mead/did though.  Consider sharing actual commands/scripts.

< 2020-06-22 16:34:08.487 CDT >LOG:  WAL file is from different database system: WAL file database system identifier is 6840038030519879233, pg_control database system identifier is 6841285371464745049.

You cannot PITR if the WAL doesn’t match the base backup for the cluster you are trying to restore.


Or, probably better, consider using a third-party system.

David J.

Re: Need help with PITR for PostgreSQL 9.4.5

From
Sri Linux
Date:
Thank you all for the response,

Please find the method used. Please recommend me if I have done something wrong...


Thanks and Regards,
sree

On Wed, Jun 24, 2020 at 11:36 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
The version you are running is neither up-to-date for its major version (9.4) nor is the major version being supported.


Thoug a functioning backup is good to have before upgrading, especially major versions.

On Wednesday, June 24, 2020, Sri Linux <srilinux09@gmail.com> wrote:
I am trying to do PINT backup and recovery for the standalone database which is not in a cluster. 

That is not possible.  Its unclear what exactly you mead/did though.  Consider sharing actual commands/scripts.

< 2020-06-22 16:34:08.487 CDT >LOG:  WAL file is from different database system: WAL file database system identifier is 6840038030519879233, pg_control database system identifier is 6841285371464745049.

You cannot PITR if the WAL doesn’t match the base backup for the cluster you are trying to restore.


Or, probably better, consider using a third-party system.

David J.

Attachment

Re: Need help with PITR for PostgreSQL 9.4.5

From
Stephen Frost
Date:
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
folderto 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

Attachment

Re: Need help with PITR for PostgreSQL 9.4.5

From
Sri Linux
Date:
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@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.

Thanks,

Stephen

Re: Need help with PITR for PostgreSQL 9.4.5

From
Sri Linux
Date:
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@gmail.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@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.

Thanks,

Stephen

Re: Need help with PITR for PostgreSQL 9.4.5

From
"David G. Johnston"
Date:
On Friday, November 6, 2020, Sri Linux <srilinux09@gmail.com> wrote:
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?


Please start new email threads when you have new questions.

If you run vacuum manually in a session, and then disconnect that session, the vacuum stops just like any other command.

David J.

Re: Need help with PITR for PostgreSQL 9.4.5

From
Sri Linux
Date:
Thank you very much David



On Fri, Nov 6, 2020 at 2:11 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Friday, November 6, 2020, Sri Linux <srilinux09@gmail.com> wrote:
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?


Please start new email threads when you have new questions.

If you run vacuum manually in a session, and then disconnect that session, the vacuum stops just like any other command.

David J.

Re: Need help with PITR for PostgreSQL 9.4.5

From
Sri Linux
Date:
David

Could you please provide me the link to start new questions?

Thanks and Regards
Sri 

On Fri, Nov 6, 2020 at 3:27 PM Sri Linux <srilinux09@gmail.com> wrote:
Thank you very much David



On Fri, Nov 6, 2020 at 2:11 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Friday, November 6, 2020, Sri Linux <srilinux09@gmail.com> wrote:
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?


Please start new email threads when you have new questions.

If you run vacuum manually in a session, and then disconnect that session, the vacuum stops just like any other command.

David J.

Re: Need help with PITR for PostgreSQL 9.4.5

From
"David G. Johnston"
Date:
On Fri, Nov 6, 2020 at 2:34 PM Sri Linux <srilinux09@gmail.com> wrote:
Could you please provide me the link to start new questions?


Assuming you are using Gmail...


David J.