Re: Using pg_start_backup() and pg_stop_backup() - using 9.1.2.2 - Mailing list pgsql-general

-----Original Message-----
From: Kevin Grittner [mailto:kgrittn@ymail.com]
Sent: Saturday, June 14, 2014 8:16 PM
To: Khangelani Gama; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using pg_start_backup() and pg_stop_backup() -
using 9.1.2.2

Khangelani Gama <kgama@argility.com> 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.