Pgbackrest backup is too slow
Hello, I have a Centos 7 server which runs Postgresql 10.7. I am using pgbackrest to take db backup. Problem is backup is too slow. My data dir size is 9.6G and full backup runtime is 22 mins I also tried using process-max=3, full backup runtime = 21 mins Following are the backrest configurations: backrest.conf for pg-primary: [data] pg1-path=/var/lib/pgsql/10/data [global] log-level-file=detail repo1-path=/var/lib/pgbackrest repo1-retention-full=2 start-fast=y process-max=3 Am I doing something wrong here? In production we have a database of size more than 500GB. It would not be feasible for me to use it as it is.pgbackrest version is 2.18-1.rhel7.x86_64. installed from pgdg yum repo. Also I need to take a backup from the remote machine which would also increase the overall runtime of backup/restore. Thanks for the help in advance Regards, Ajay pratap -- *Disclaimer:* The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.
Re: Pgbackrest backup is too slow
Hello Stephen, Thanks for your prompt response. As recommended by you, I will try to isolate the problem at system level, Meanwhile, Is there any system recommendation by pgbackrest, (how much process-max should be configured) should I set affinity to pgbackrest process, how many core should be kept for backup /restore. Or any other configuration? My DB size is 500GB and increasing . Regards, Ajay On Fri, Oct 11, 2019 at 6:20 PM Stephen Frost wrote: > Greetings, > > * Ajay Pratap (ajaypra...@ameyo.com) wrote: > > I have a Centos 7 server which runs Postgresql 10.7. I am using > pgbackrest > > to take db backup. > > Problem is backup is too slow. > > Have you tried running 'top' to see what's going on? > > > My data dir size is 9.6G and full backup runtime is 22 mins > > I also tried using process-max=3, full backup runtime = 21 mins > > Erm, those numbers don't make any sense to me- as an example, we > regularly back up a 12GB database, from Dallas to San Fran, in 5 > minutes. > > I see you're requesting a checkpoint to start immediately, but how long > does the checkpoint actually take to complete? Have you looked at your > PG logs..? > > Have you tried running 'top' and then running the backup and looked to > see what's going on? Is the CPU at 100%? Are all the processes stuck > in 'D' state all the time (meaning you don't have enough i/o resources), > or...? > > > In production we have a database of size more than 500GB. It would not be > > feasible for me to use it as it is.pgbackrest version is > > 2.18-1.rhel7.x86_64. > > installed from pgdg yum repo. > > Given sufficient resources, we've seen pgbackrest push over 1TB/hour, so > there is definitely something strange going on here. > > Thanks, > > Stephen > -- *Disclaimer:* The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.
pgbackrest with PAF(corosync and pacmaker)
##Arch OS: Centos 7 PostgreSQL 10 pgbackrest 2.18 PAF: two nodes(primary and secondary ) with corosync and pacemaker ##Query I am using pacemaker and corosync to setup two nodes High availability cluster for postgreSQL 10. In the scenario I want to setup timely backup with pgbackrest. Using the _backup from standby_ feature I could able to take backup from my secondary postgres. But if secondary postgres is down, it does not take backup from primary. What I want? I want to take backup from secondary postgres (--backup-standby), If secondary is unavailable, take backup from primary postgres. Or if Primary postgres goes down backup should still continues from secondary (which would have been promoted to master now) All backups are with remote repository pgbackrest.conf for repo: *[data]pg1-host=pgprimarypg1-path=/var/lib/pgsql/10/datapg2-host=pgsecpg2-path=/var/lib/pgsql/10/data[global]backup-standby=yprocess-max=3repo1-path=/var/lib/pgbackrestrepo1-retention-full=2start-fast=y* pgbackrest for primary and secondary postgres: *[data]pg1-host=mainpg1-path=/var/lib/pgsql/10/datapg2-host=secpg2-path=/var/lib/pgsql/10/data[global]backup-standby=nprocess-max=3repo1-path=/var/lib/pgbackrestrepo1-retention-full=2start-fast=y* Is there any way to achieve this? This sounds like a generic problem? Or am I doing something wrong Regards, ajay -- *Disclaimer:* The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.
Re: Pgbackrest backup is too slow
Thanks for your previous replies. It turns out my machine was having kernel Issues, which made it slow. I setup another cluster in different machine now I could able to backup 42G in 6 min(s). On Fri, Oct 11, 2019 at 7:23 PM Brad Nicholson wrote: > Stephen Frost wrote on 2019/10/11 08:50:53 AM: > > > From: Stephen Frost > > To: Ajay Pratap > > Cc: Postgres General > > Date: 2019/10/11 08:51 AM > > Subject: [EXTERNAL] Re: Pgbackrest backup is too slow > > > > Greetings, > > > > * Ajay Pratap (ajaypra...@ameyo.com) wrote: > > > I have a Centos 7 server which runs Postgresql 10.7. I am using > pgbackrest > > > to take db backup. > > > Problem is backup is too slow. > > > > Have you tried running 'top' to see what's going on? > > > > > My data dir size is 9.6G and full backup runtime is 22 mins > > > I also tried using process-max=3, full backup runtime = 21 mins > > > > Erm, those numbers don't make any sense to me- as an example, we > > regularly back up a 12GB database, from Dallas to San Fran, in 5 > > minutes. > > > How many database objects do you have? For databases with a lot of tables > and/or indexes (think tens of thousands), backup performance will slow > down. This has improved a lot in newer versions, but still impacts > performance of the backups. > > Brad. > -- *Disclaimer:* The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.
pgbackrest concerns and doubts.
Hello, I am using Postgresql 10 and pgbackrest 2.18 version on centos 7.6 I have few doubts regard pgbackrest. a) what is the log rotation policy for pgbackrest logs. I see it logs on default path /var/log/pgbackrest/-.log what is the log rotation policy of each logs. use case: if i am writing a script to parse the logs and gatter some info, i should be aware of log rotation or if logs doesn't rotate a single file could be huge to parse. Or should I simply use *logrotate* b) since pgbackrest takes physical backup, what are the impact if I upgrades minor postgres version(10.5 to 10.10) and impact on postgres major version(10.10 to 12.X) Thanks and regards, -- *Disclaimer:* The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.
PostgreSQL ping/pong to client
Hello there, I am using PostgreSQL 10.7 as the database, and have Java web app. My app takes a lock on the database using the following command whenever my server is starting. *select pg_try_advisory_lock(100)* To make sure only one instance of the App is using the database at a time. There have been several instances when my server crashes(or stops/ or kill), but this lock still remained on the PostgreSQL. Ideally, this should not happen because TCP connection breaks if one of the peers dies, but it happens otherwise. To solve this problem PostgreSQL needs to break the connection if the client dies. One of the methods is to configure TCP timeout but since this is very impactful and hence unrealistic. I was wounding how can out of the box PostgreSQL help me solve this. May be like a ping pong service to check if client is still alive. Thanks and regards, Ajay Pratap, Software Engineer, Drishti-Soft -- *Disclaimer:* The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.
Re: PostgreSQL ping/pong to client
Correction: I meant when my java application dies postgres should break all the connections that were associated with that peer. On Wed, Apr 17, 2019, 19:59 Adrian Klaver wrote: > On 4/17/19 5:39 AM, Ajay Pratap wrote: > > Hello there, > > I am using PostgreSQL 10.7 as the database, and have Java web app. My > > app takes a lock on the database using the following command whenever my > > server is starting. > > /select /pg_try_advisory_lock/(100)/ > > To make sure only one instance of the App is using the database at a > time. > > There have been several instances when my server crashes(or stops/ or > > kill), but this lock still remained on the PostgreSQL. Ideally, this > > should not happen because TCP connection breaks if one of the peers > > dies, but it happens otherwise. > > If the server is exiting uncleanly I am not sure how it will gracefully > close connections. > > > To solve this problem PostgreSQL needs to break the connection if the > > client dies. > > But the problem as you describe it is that the server dies not the client. > > > One of the methods is to configure TCP timeout but since this is very > > impactful and hence unrealistic. > > I was wounding how can out of the box PostgreSQL help me solve this. > > May be like a ping pong service to check if client is still alive. > > > > Thanks and regards, > > Ajay Pratap, > > Software Engineer, > > Drishti-Soft > > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- *Disclaimer:* The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.
Re: PostgreSQL ping/pong to client
Maybe postgresql (application) layer ping pong. Enabling system wise timeouts can impact my whole setup including my web app, thats why it is hard for me to configure them. On Wed, Apr 17, 2019, 22:12 Francisco Olarte wrote: > On Wed, Apr 17, 2019 at 4:49 PM Ajay Pratap > wrote: > > Correction: I meant when my java application dies postgres should break > all the connections that were associated with that peer. > > And how is the server supposed to detect that without keepalives? TCP > is dessigned to survice for extended period of times without traffic, > I used that a lot in the dial up times. > > And what makes you think keepalives are impactful and unrealistic? I > use them a lot, they do not impact my workloads measurably. > > Francisco Olarte. > -- *Disclaimer:* The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.
