Need help with PITR for PostgreSQL 9.4.5
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 "00010001" 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
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 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 postgrestrust 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 00010001 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 .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
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 wrote: > Greetings, > > * Sri Linux (srilinu...@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 .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
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 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 wrote: > >> Greetings, >> >> * Sri Linux (srilinu...@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 .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
Thank you very much David On Fri, Nov 6, 2020 at 2:11 PM David G. Johnston wrote: > On Friday, November 6, 2020, Sri Linux 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
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 wrote: > Thank you very much David > > > > On Fri, Nov 6, 2020 at 2:11 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Friday, November 6, 2020, Sri Linux 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. >> >>
Single user model vaccum
Hi All How can I monitor and make sure that the vacuum is still running if I get lost with the session to Linux putty? I would need to check if the vacuum is still running from Linux OS. Please advise. Regards Sri
Re: Single user model vaccum
Added'' when I log in to the Linux terminal and grep process, I can only see the single-user mode process running but not sure about vacuum status. I can't run a pg_stat commands from pgsql as the db is in single-user mode. ps -ef|grep -i sql root 25412 15199 0 14:59 pts/100:00:00 su postgres -c /usr/pgsql-9.4/bin/postgres --single -O -D /pgsql/9.4/data postgres postgres 25413 25412 53 14:59 ?02:50:00 /usr/pgsql-9.4/bin/postgres --single -O -D /pgsql/9.4/data postgres Thanks, Sri On Fri, Nov 6, 2020 at 8:17 PM Sri Linux wrote: > Hi All > > How can I monitor and make sure that the vacuum is still running if I get > lost with the session to Linux putty? I would need to check if the > vacuum is still running from Linux OS. > > Please advise. > > Regards > Sri >
Re: Single user model vaccum
Thank you David vacuum is done but the application is very slow, is this normal behavior? Will it run any background process after vacuum causing this slowness? Thanks Sri On Fri, Nov 6, 2020 at 9:15 PM David G. Johnston wrote: > > On Friday, November 6, 2020, Sri Linux wrote: > >> Added'' >> >> when I log in to the Linux terminal and grep process, I can only see the >> single-user mode process running but not sure about vacuum status. I can't >> run a pg_stat commands from pgsql as the db is in single-user mode. >> >> ps -ef|grep -i sql >> root 25412 15199 0 14:59 pts/100:00:00 su postgres -c >> /usr/pgsql-9.4/bin/postgres --single -O -D /pgsql/9.4/data postgres >> postgres 25413 25412 53 14:59 ?02:50:00 >> /usr/pgsql-9.4/bin/postgres --single -O -D /pgsql/9.4/data postgres >> >> On Fri, Nov 6, 2020 at 8:17 PM Sri Linux wrote: >> >>> Hi All >>> >>> How can I monitor and make sure that the vacuum is still running if I >>> get lost with the session to Linux putty? I would need to check if the >>> vacuum is still running from Linux OS. >>> >> > Don’t really know but a quick read suggests you will need to basically > debug the running process. > > In the future use something like “screen” or “tmux” so you can just > re-attach to the terminal container. > > I’m not sure if there is anything special about single-user mode (way to > omit critical info on your first two emails) that would discourage you from > just killing the server and starting a new vacuum. > > David J. > >
Re: Single user model vaccum
Thank you very much David, No, vacuum activity wiht single-mode user is completed. I restarted all services and the application is online but pretty slow.I will open a new case but confused about how to do a new post in the Postgres community. is this common for an application to run pretty slow after vacuum without any other option like full etc. Regards Sri On Fri, Nov 6, 2020 at 9:55 PM David G. Johnston wrote: > On Friday, November 6, 2020, Sri Linux wrote: > >> Thank you David >> >> vacuum is done but the application is very slow, is this normal behavior? >> Will it run any background process after vacuum causing this slowness? >> > > Please don’t top-post. > > Are you still running under single-user? > > David J. >
Re: Single user model vaccum
Ok, thank you. On Fri, Nov 6, 2020 at 10:13 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Friday, November 6, 2020, Sri Linux wrote: > >> Thank you very much David, >> No, vacuum activity wiht single-mode user is completed. I restarted all >> services and the application is online but pretty slow.I will open a new >> case but confused about how to do a new post in the Postgres community. >> >> is this common for an application to run pretty slow after vacuum without >> any other option like full etc. >> > > There isn’t a process for “opening a new case”. You just send an email. > > David J. >
After vacuum application runs very slow ? is this common behavior ?
Hi All, Our production database size is about 2TB and we had run into issues and Postgres log did recommend running the vacuum in single-user mode. We have successfully completed running a single user mode vacuum without any options. .. services were online after vacuum. Application load time and response times are very poor and don't load sometimes. Is this common behavior after a vacuum? DO we need to consider any post activities or give some time for the database to settle down? Did full os restart for the DB server? Thanks Sri.
Re: After vacuum application runs very slow ? is this common behavior ?
Adding version info Postgres 9.4.5 Linux - rhel 7.3 Thanks Sri On Fri, Nov 6, 2020 at 10:20 PM Sri Linux wrote: > Hi All, > > Our production database size is about 2TB and we had run into issues and > Postgres log did recommend running the vacuum in single-user mode. We have > successfully completed running a single user mode vacuum without any > options. .. services were online after vacuum. Application load time and > response times are very poor and don't load sometimes. Is this common > behavior after a vacuum? DO we need to consider any post activities or give > some time for the database to settle down? > > Did full os restart for the DB server? > > Thanks > Sri. >
Re: After vacuum application runs very slow ? is this common behavior ?
Thank you for your response. On Fri, Nov 6, 2020 at 11:14 PM Adrian Klaver wrote: > On 11/6/20 8:20 PM, Sri Linux wrote: > > Hi All, > > > > Our production database size is about 2TB and we had run into issues and > > Postgres log did recommend running the vacuum in single-user mode. We > > have successfully completed running a single user mode vacuum without > > any options. .. services were online after vacuum. Application load time > > and response times are very poor and don't load sometimes. Is this > > common behavior after a vacuum? DO we need to consider any post > > activities or give some time for the database to settle down? > > Given that there is no actual useful/hard data in the above the chances > of there being a relevant answer in return is doubtful. > > Also given your subsequent post the major version you are using is > nearing 1 year past EOL and is 21 minor releases behind the last release > in the series. At the very least you need to bring it up to the last > minor release. > > > > > Did full os restart for the DB server? > > > > Thanks > > Sri. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
