Re: Backup's from standby

What is the basic reason for restricting backup at standby
server(pg_start_backup)?

We are doing the following steps to take backup from standby which(backups)
might be used for standalone startups.

1.Create file system snapshot(LVM)
2.take the back up of data directory including wal files
3.Drop the file system snapshot

Will there be(foresee) any issues while using these backups?

-Senthil

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Backup-s-from-standby-tp4688344p4688344.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Thu, Aug 11, 2011 at 1:02 AM, senthilnathan
<senthilnathan(dot)t(at)gmail(dot)com> wrote:
> What is the basic reason for restricting backup at standby
> server(pg_start_backup)?

It performs a checkpoint.

> We are doing the following steps to take backup from standby which(backups)
> might be used for standalone startups.
>
> 1.Create file system snapshot(LVM)
> 2.take the back up of data directory including wal files
> 3.Drop the file system snapshot
>
> Will there be(foresee) any issues while using these backups?

Assuming that step #2 is backing up an instantaneous snapshot, rather
than the live data directory, I think that will work. Upon starting
up the server, it will enter recovery, but the WAL present in the data
directory at the time of the snapshot should be sufficient to reach
the minimum recovery point.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Thanks for your reply.

What will happen if you issue *checkpoint* at STANDBY. I presume that it
will flush the data to the disk.

Will there be any conflict with the master WAL.(like checkpoint location...)

Senthil

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Backup-s-from-standby-tp4688344p4703469.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Tue, Aug 16, 2011 at 2:34 AM, senthilnathan
<senthilnathan(dot)t(at)gmail(dot)com> wrote:
> Thanks for your reply.
>
> What will happen if you issue *checkpoint* at STANDBY. I presume that it
> will flush the data to the disk.

It will perform a restartpoint.

http://www.postgresql.org/docs/9.0/static/wal-configuration.html

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Aug 16, 2011 at 7:34 AM, senthilnathan
<senthilnathan(dot)t(at)gmail(dot)com> wrote:
> Thanks for your reply.
>
> What will happen if you issue *checkpoint* at STANDBY. I presume that it
> will flush the data to the disk.

Yes. On the standby that is known as a restartpoint.

> Will there be any conflict with the master WAL.(like checkpoint location...)

No, there is no effect on the master. The restartpoint happens as a
background process on the standby, so replay will continue while we
restart.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Thanks for your reply.,

@ Robert.,

What issue we may face if you take a backups(includes data dir + wal files)
at standby without LVM snapshot?

-Senthil

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Backup-s-from-standby-tp4688344p4706899.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Wed, Aug 17, 2011 at 1:39 AM, senthilnathan
<senthilnathan(dot)t(at)gmail(dot)com> wrote:
> Thanks for your reply.,
>
> @ Robert.,
>
> What issue we may face if you take a backups(includes data dir + wal files)
> at standby without LVM snapshot?

The backup might be corrupted in arbitrary ways.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> > What issue we may face if you take a backups(includes data dir + wal
files)
> > at standby without LVM snapshot?
>
> The backup might be corrupted in arbitrary ways.
>

And what will happen, if one issues a pg_start_backup() on the master, then
takes a file-backup on slave, and issues pg_stop_backup() on master again?
As far as I remember this approach was working for me, considering, that all
needed WAL files are transferred to the newly created DB copy as well.

-- Valentine Gogichashvili

On Fri, Aug 19, 2011 at 9:38 AM, Valentine Gogichashvili
<valgog(at)gmail(dot)com> wrote:
>> > What issue we may face if you take a backups(includes data dir + wal
>> > files)
>> > at standby without LVM snapshot?
>>
>> The backup might be corrupted in arbitrary ways.
>
> And what will happen, if one issues a pg_start_backup() on the master, then
> takes a file-backup on slave, and issues pg_stop_backup() on master again?
> As far as I remember this approach was working for me, considering, that all
> needed WAL files are transferred to the newly created DB copy as well.

Well, I think you would need to verify a few more things:

- The pg_start_backup() will need to have been replayed on the standby
before you start the file copy.
- You will need all the WAL segments beginning at the position where
pg_start_backup() was executed on the master and ending after
pg_stop_backup() was executed on the master.

Assuming you do that, it seems like it ought to work, although I have
not tested it and am not at all sure I'm not missing something.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company