-----Original Message----- From: Kevin Grittner [mailto:kgrittn@xxxxxxxxx] Sent: Saturday, June 14, 2014 8:16 PM To: Khangelani Gama; pgsql-general@xxxxxxxxxxxxxx Subject: Re: [GENERAL] Using pg_start_backup() and pg_stop_backup() - using 9.1.2.2 Khangelani Gama <kgama@xxxxxxxxxxxx> wrote: > I am doing the following between master and the backup server using > archive_command, I'm not sure what that means. Do you mean that while performing the steps you describe, an archive command was active, copying WAL files to an archive directory? There is standby servers drawing from one master server. The 1st standby server doesn't give problems because it's on the same network and same physical location as the master server. But the other standby server is located in a total different physical location so that when the master and 2nd standby fails due to things like power failures we able to fail over to it. So it means while performing the steps, archive command is active in the master server as below. I hope I understood your statement above. postgresql.conf file wal_level = archive # - Archiving - archive_mode = on > On backup or secondary server I did the following: > > 1. Stopped postgres in backup server 2. Removed all > walfiles from /walfiles directory 3. Removed all files from > pg_xlog/ > > Went to master/source server: > > 1. Issue pg_start_backup('label') 2. Performed rsync of >cluster directory (nohup rsync -avz > /pgsql2/data backupserver:/pgsql2/ > /tmp/rsynccopy.out > 2> /tmp/rsynccopy.err & ) > 3. Step 2 takes a long time because the database is huge. > But during the rsync process many files are getting > copied to the backup server landing in /pgsql2/walfiles/ > directory which I cleaned up before starting backup in > the master server. > 4. Once step 3, rsync is done I will run Issue > pg_stop_backup(). So far OK, although I would have told rsync to exclude the postmaster.pid file and the contents of the pg_xlog directory. > Then connect to secondary server and remove recovery.done and create > recovery.conf file, and remove pid file which got copied from master > server. If you don't remove the files underneath pg_xlog, too, you will have problems. There is no telling at what point during the rsync these were copied, so they are likely to be incomplete. You can only trust WAL files from the archive directory or taken while the server which generated them is stopped. Thanks - I will remove files underneath pg_xlog as well. > Question I have is After doing step 4, what do I with the files that > have been copying as I mentioned in step 3 above or that have been > copying during the rsync command. Do I have to copy any walfiles > manually after doing step 4 above in the master server? Your recovery.conf file should have a restore_command that copied from the archive directory (walfiles) into the path indicated by %p. Yes the archive command is as follows : standby_mode = 'on' restore_command = '/usr/local/PostgresPlus/9.1AS/bin/pg_standby -l -d -k 255 -r 2 -s 2 -w 0 -t /tmp/recovery.pgsql.trigger.5432 /pgsql2/walfiles %f %p %r 2 >> standby.log' trigger_file = '/tmp/recovery.pgsql.trigger.5432' Yes Adrian Klaver , you are right by your comments: This is a follow up to a thread the OP started last week: -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
