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.johns...@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. > > https://www.postgresql.org/support/versioning/ > > Thoug a functioning backup is good to have before upgrading, especially > major versions. > > On Wednesday, June 24, 2020, Sri Linux <srilinu...@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. > > https://www.postgresql.org/docs/9.4/continuous-archiving.html > > Or, probably better, consider using a third-party system. > > David J. > >
# mkdir /server01/pgarchives # chmod 700 /server01/pgarchives # chown postgres:postgres /server01/pgarchives Enable archiving by modifying postgresql.conf: File is generally located at /var/lib/pgsql/9.4/data/postgresql.conf # WRITE AHEAD LOG # - Settings - wal_level = hot_standby # - Archiving archive_mode = on archive_command = 'cp %p /server01/pgarchives/%f' # REPLICATION # - Sending Server(s) - max_wal_senders = 3 Add the postgres user to the replication role and grant permissions in pg_hba.conf by adding the following line to the end of the file: local replication postgres trust Restart the postgresql server Verify that Write Ahead Logging is enabled by checking the contents of the pg_xlog folder for files with names similar to 000000010000000000000001 This folder is located at /var/lib/pgsql/9.4/data/pg_xlog 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` 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 Ensure that both the PostgreSQL installation folder and the /server01 folder are owned by the postgres user: # chown -R postgres:postgres /var/lib/pgsql/9.4/data (RedHat/CentOS) # chown -R postgres:postgres /server01 (All) Create a recovery.conf file in the installation folder (/var/lib/postgresql/9.4/main/recovery.conf) with the following content and make sure it is owned by the postgres user: restore_command = 'cp /server01/pgarchive/%f %p' The restore_command value should be the functionally opposite command used to archive the WAL files as configured in postgresql.conf In the backup configuration above, WAL files are copied from "%p" to "/server01/pgarchive/%f" The restore_command, therefore, needs to copy from "/server01/pgarchive/%f" to "%p" In addition, it is possible to specify a specific date/time to restore to in this file for a point in time recovery by adding the following line to recovery.conf: recovery_target_time = '2020-0-25 15:32:24 EST' If the recovery_target_time parameter is omitted, the database will be recovered to the most recent transaction Start the PostgreSQL database If all goes well, the recovery.conf file will be renamed to recovery.done upon completion Check the syslog (/var/log/syslog) for information on any errors that might have occurred during startup Confirm that the backup was successfully restored
