Regarding concurrent backup from slave

[abhilas...@gmail.com]

Hey dear guys,

I've got an issue while taking backup from a standby server using pgbarman. I've already enabled pgespresso extension and archived wal logs from Master to a directory named 'wals' in the slave server's data directory. However, while taking backup I could see that the pg_xlog which are yet to be archived are not being backed up by barman. 

When I started postgresql from a local restored location, I got the following error : 

=====

postgres@139:/var/lib/pgsql$ /usr/lib/postgresql/9.2/bin/pg_ctl start -D /var/lib/pgsql/ 

server starting

postgres@139:/var/lib/pgsql$ 2014-05-07 07:40:25 EDT LOG:  database system was interrupted while in recovery at log time 2014-05-06 14:37:23 EDT

2014-05-07 07:40:25 EDT HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.

2014-05-07 07:40:25 EDT LOG:  could not open file "pg_xlog/000000010000000000000010" (log file 0, segment 16): No such file or directory

2014-05-07 07:40:25 EDT LOG:  invalid checkpoint record

2014-05-07 07:40:25 EDT FATAL:  could not locate required checkpoint record

2014-05-07 07:40:25 EDT HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.2/main/backup_label".

2014-05-07 07:40:25 EDT LOG:  startup process (PID 14238) exited with exit code 1

2014-05-07 07:40:25 EDT LOG:  aborting startup due to startup process failure

=====

I have attached wal log details and barman configurations along with this mail. Could anyone please help me with this concern ?

Thank you,

Abhilash

[Gabriele Bartolini]

Hello Abhilash,

I guess this is the issue: "I've already enabled pgespresso

extension and archived wal logs from Master to a directory named
'wals' in the slave server's data directory".

You should archive WAL files from the master to the backup server
(in the incoming directory), rather than on the standby (unless you
transfer on both servers, but in my view that is an unnecessary
precaution). You should have streaming replication in place between
the master and the standby, is that right?

For completeness, I am cutting/pasting a section from the
documentation you find at
http://docs.pgbarman.org/#concurrent_backup_and_backup_from_a_standby
:

> When backing up from a standby server, continuous archiving of WAL files must be configured on the master to ship files to the Barman server (as outlined in the "Continuous WAL archiving" section above).
> [ In case of concurrent backup, currently Barman does not have a way to determine that the closing WAL file of a full backup has actually been shipped - opposite to the case of an exclusive backup where it is Postgres itself that makes sure that the WAL file is correctly archived. Be aware that the full backup cannot be considered consistent until that WAL file has been received and archived by Barman. We encourage Barman users to wait to delete the previous backup - at least until that moment. ]

Please pay also attention to the last paragraph, as it is very important.

Let me know if I have correctly understood your problem and answered
your question.

Could you also send the output of "barman diagnose"?

Thanks,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

> --
> --
> You received this message because you are subscribed to the "Barman for
> PostgreSQL" group.
> To post to this group, send email to pgba...@googlegroups.com
> To unsubscribe from this group, send email to
> pgbarman+u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/pgbarman?hl=en?hl=en-GB
>
> ---
> You received this message because you are subscribed to the Google Groups
> "Barman, Backup and Recovery Manager for PostgreSQL" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to pgbarman+u...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Abhilash MB]

Dear Gabriele,

Thanks a lot for your update. 

In our case, we would like to have the backup (base backup & wal logs) taken from the Standby server instead of master. This is to reduce the load in the Primary server. That is why I archived wal logs initially to the slave server and then tried to ship it to the barman server. 

Could you please confirm if that method really works ? OR Is it impossible to take both the backups (base backup+wal logs)

from the standby ?

>>You should have streaming replication in place between the master and the standby, is that right?

Yes, we have enabled streaming replication between the master and the standby. 

If there is any other alternative methods for taking both the backups from slave server, please advice me on that. I'm very much eager to know more about it!

>>Could you also send the output of "barman diagnose"?

I have attached barman diagnose results and a few other command outputs along with this mail. Could you please have a look and share your thoughts ?

Thank you,

Abhilash

You received this message because you are subscribed to a topic in the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/pgbarman/En16uLfkS9Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to pgbarman+u...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
Regards,
Abhilash.MB

[Gabriele Bartolini]

Hi Abhilash,

I am asking you a question: do you have proof and clear evidence
that shipping WAL files from the master is really slowing down the
backup procedure and loading the server?

My question is purely based on our experience, which says that it is
extremely rare (I am not saying impossible) that WAL shipping causes
load on a server.

In any case, WAL streaming support is in the TODO list of Barman
(this would allow you to setup full cascading backup architectures, as
well as introducing "0 data loss" to a backup solution).

If you are interested, it could be a good chance for your company to
fund the open source development of that feature by us, like others
have done in the past. Think of Adyen, for example, a company that
funded a large part of the development of version 1.3.2 and
pgespresso, which brought backup from standby.

Enjoy your weekend.

Thanks,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

[Abhilash MB]

Hi Gabriele,

Thank you for your response.

>>do you have proof and clear evidence that shipping WAL files from the master is really slowing down the backup 

>>procedure and loading the server?

No. In our case database sizes are pretty large and we put forward this concern as a precaution.

>>In any case, WAL streaming support is in the TODO list of Barman >>(this would allow you to setup full cascading backup >>architectures, as 
>>well as introducing "0 data loss" to a backup solution).

Could you please elaborate this ? You mean right now barman doesn't have any option to take full backup (base backup + archive backup) from the standby ?

We're on a discussion with our management in this regard. We'll let you know how we are proceeding ahead.

Thanks & Regards,
Abhilash

[Gabriele Bartolini]

Hello Abhilash,

> No. In our case database sizes are pretty large and we put forward this
> concern as a precaution.

I see. In any case, I am referring about multi-terabyte databases with
quite high concurrency level.

> Could you please elaborate this ? You mean right now barman doesn't have any
> option to take full backup (base backup + archive backup) from the standby ?

Barman has the option to take a full backup from the standby, while
continuous archiving is in place from the master, as outlined in the
documentation.

We can add support for using WAL streaming as differential backup (now
it is done through archive_command from the master only) and this will
allow to implement synchronous backup solutions (bringing, as I was
mentioning yesterday, RPO=0 and therefore "0 data loss"). Quite cool,
isn't it? ;)

The largest chunk of load is generated by physical copy of database
pages, which interferes with the OS cache and might generate I/O
problems. With Barman 1.3.2 you can off-load this on the standby.

Continuous archiving from the master has rarely been a problem for us, so far.

However, if you require that option, it is feasible and can be added
in future versions.

> We're on a discussion with our management in this regard. We'll let you know
> how we are proceeding ahead.

Sure. Please feel free to ask for more information.

Thanks,
Gabriele

[Abhilash MB]

Hi Gabriele,

Thanks a lot for your explanation. May I ask a few more doubts.. 

I have enabled archive mode & archive command in master to dump logs into the default barman incoming wal directory. However, when I executed  barman check main (server), it shows error in the status. I know that its picking the slave's status. 

==========

root@139:/var/lib/barman/main/archives# barman check main

Server main:

        ssh: OK

        PostgreSQL: OK

        pgespresso extension: OK

        archive_mode: FAILED (please set it to 'on')

        archive_command: FAILED (please set it accordingly to documentation)

        directories: OK

        retention policy settings: OK

        compression settings: OK

        minimum redundancy requirements: OK (have 5 backups, expected at least 0)

========== 

1. When I'm only archiving from the master, do I need to enable archive mode and command in slave ? What is the logic here ? I believe that there is no way to archive from the slave ?

2. How can barman identify the Master & Slave servers ? Is it required ? Do we need to specify master server anywhere in the barman server configurations ?

3. Suppose if the Master went down, what will happen if I swapped my Master and Slave servers ?

4.  How barman ensures that all the latest updates in pg_xlog are being backed up ? What will happen if replication is not is not finished and barman started taking backup ? 

Thanks & Regards,

Abhilash

--
--
You received this message because you are subscribed to the "Barman for PostgreSQL" group.
To post to this group, send email to pgba...@googlegroups.com
To unsubscribe from this group, send email to
pgbarman+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/pgbarman?hl=en?hl=en-GB

---
You received this message because you are subscribed to a topic in the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/pgbarman/En16uLfkS9Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to pgbarman+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Regards,
Abhilash.MB

[Abhilash MB]

Hi Gabriele,

Thanks a lot for your explanation. May I ask a few more doubts.. 

I have enabled archive mode & archive command in master to dump logs into the default barman incoming wal directory. However, when I executed  barman check main (server), it shows error in the status. I know that its picking the slave's status. 

==========

root@139:/var/lib/barman/main/archives# barman check main

Server main:

        ssh: OK

        PostgreSQL: OK

        pgespresso extension: OK

        archive_mode: FAILED (please set it to 'on')

        archive_command: FAILED (please set it accordingly to documentation)

        directories: OK

        retention policy settings: OK

        compression settings: OK

        minimum redundancy requirements: OK (have 5 backups, expected at least 0)

========== 

1. When I'm only archiving from the master, do I need to enable archive mode and command in slave ? What is the logic here ? I believe that there is no way to archive from the slave ?

2. How can barman identify the Master & Slave servers ? Is it required ? Do we need to specify master server anywhere in the barman server configurations ?

3. Suppose if the Master went down, what will happen if I swapped my Master and Slave servers ?

4.  How barman ensures that all the latest updates in pg_xlog are being backed up ? What will happen if replication is not finished and barman started taking backup ? 

Thanks & Regards,

Abhilash

--
Regards,
Abhilash.MB