Обсуждение: Using pg_start_backup() and pg_stop_backup() - using 9.1.2.2
Hi all
Please help me here.
I am doing the following between master and the backup server using archive_command,
I am using O/S - Red Hat Enterprise Linux Server release 5.3 (Tikanga)
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().
Then connect to secondary server and remove recovery.done and create recovery.conf file, and remove pid file which got copied from master server.
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?
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.
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?
> 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.
> 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.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 06/14/2014 11:15 AM, Kevin Grittner wrote: > 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? This is a follow up to a thread the OP started last week: http://www.postgresql.org/message-id/ce3ab4298e3cc6f2751653d6f50f0342@mail.gmail.com The relevant portion for the above is: http://www.postgresql.org/message-id/36e864716fcb063194f5f95e5fc0b35c@mail.gmail.com where the archive_command is: while [ $test = "false" ] do rsync -a /pgsql2/data/${src} postgres(at)10(dot)58(dot)101(dot)10:/pgsql2/walfiles/${dest} >> /tmp/run_replication.sh.out 2>> /tmp/run_replication.sh.out test=`ssh AB_CDS3 "if [ -f /pgsql2/walfiles/${dest} ];then echo 'true' ;else echo 'false';fi"` if [ ${test} = "false" ] then echo "Test is false for CDS3, sleeping 10" >> /tmp/run_replication.sh.out sleep 10 cnt=$(( $cnt + 1 )) if [ ${cnt} -ge 60 ] then message="Replication ERROR: Unable to send WAL file(${desc}) from CDS to CDS3" echo "`date` : ${message}" >> /tmp/run_replication.sh.out sendsms fi fi done So yes it would seem the OP has two rsync processes going on at the same time. > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > -- Adrian Klaver adrian.klaver@aklaver.com
On 06/14/2014 06:44 AM, Khangelani Gama wrote: > Hi all > > Please help me here. > I would say the best way to help is to figure out what it is you want out of the master/standby/archive setup before getting into the actual configuration. What do you want to do with the standby/archive? 1) Do you want streaming replication? 2) Have backup database to query/backup from? In other words a Hot Standby. 3) Have an archive to do Point in Time Recovery(PITR)? 4) Have an archive as a backup in case streaming replication goes down? 5) Do you even need an archive or is streaming WAL files enough? -- Adrian Klaver adrian.klaver@aklaver.com
-----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.