Re: [bareos-users] Number of Postgres connections

[Douglas K. Rand]

I definitely think that I'm leaking Postgres connections.  Just after the
backup on Sunday finished I had these idle queries hanging around:
usename |  pid  | state  |  query 

--------+-------+--------+--------------------------------------------------------------------------
bareos  | 21482 | idle   | SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 
FROM Job WHERE JobId=30114
 bareos  | 21484 | idle   | SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 
FROM Job WHERE JobId=30206
 bareos  | 21488 | idle   | SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 
FROM Job WHERE JobId=30207
 bareos  | 21490 | idle   | SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 
FROM Job WHERE JobId=30211
 bareos  | 21492 | idle   | SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 
FROM Job WHERE JobId=30222
 bareos  | 21495 | idle   | SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 
FROM Job WHERE JobId=28802
 bareos  | 21593 | idle   | SELECT 
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 
FROM Job WHERE JobId=28976
 bareos  | 31341 | idle   | SELECT 
MediaId,VolumeName,VolStatus,Enabled,VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten,Name 
AS Storage FROM Media LEFT JOIN Storage USING(StorageId) WHERE PoolId=4 ORDER 
BY MediaId

And eight queries associated with those PIDs never changes. (I have a little 
cron that records active connections to a set of files each minute.)  Then the 
next day I add another 62 connections that are still there with the same 
queries. The next day I added 70 more.   And then the backups start failing 
consuming all 250 Postgres connections.




On 09/08/17 07:28, Douglas K. Rand wrote:
Of course.  Thanks for the help.

catalog {
   name = catalog
   db driver = "pgsql"
   db name = "bareos"
   db user = "bareos"
   db password = "**************"
   db address = "localhost"
   db port = 5432
}

I'm building it from FreeBSD ports, here is the configure line:

./configure \
         --build=amd64-portbld-freebsd11.1 \
         --disable-conio \
         --enable-batch-insert \
         --enable-libtool \
         --enable-ndmp=no \
         --enable-nls \
         --enable-smartalloc \
         --infodir=/usr/local/info/ \
         --localstatedir=/var \
         --mandir=/usr/local/man \
         --prefix=/usr/local \
         --sysconfdir=/usr/local/etc \
         --with-baseport=9101 \
         --with-db-name=bareos \
         --with-db-user=bareos \
         --with-dir-group=bareos \
         --with-dir-user=bareos \
         --with-dump-email=root@localhost \
         --with-jansson=/usr/local \
         --with-job-email=root@localhost \
         --with-logdir=/var/log/bareos \
         --with-openssl= \
         --with-pid-dir=/var/run/bareos \
         --with-plugindir=/usr/local/lib/bareos/plugins \
         --with-postgresql=yes \
         --with-python=no \
         --with-readline=yes \
         --with-sbin-perm=755 \
         --with-scriptdir=/usr/local/lib/bareos/scripts \
         --with-scriptdir=/usr/local/share/bareos \
         --with-sd-group=operator \
         --with-sd-user=bareos \
         --with-tcp-wrappers=/usr/lib \
         --with-working-dir=/var/db/bareos

And the configure summary:

Configuration on Fri Sep  8 07:20:28 CDT 2017:

    Host:                         amd64-portbld-freebsd11.1 -- freebsd 
11.1-RELEASE-p1
    Bareos version:               Bareos 15.2.2 (16 November 2015)
    Distribution:                 freebsd
    Source code location:         .
    Modify package list:
    Install binaries:             /usr/local/bin
    Install system binaries:      /usr/local/sbin
    Install libraries:            /usr/local/lib
    Install system config files:  /usr/local/etc
    Install Bareos config files:  /usr/local/etc/bareos
    Scripts directory:            /usr/local/lib/bareos/scripts
    Archive directory:            /tmp
    Working directory:            /var/db/bareos
    PID directory:                /var/run/bareos
    Subsys directory:             /var/run/subsys
    Man directory:                /usr/local/man
    Data directory:               /usr/local/share
    Backend directory:            /usr/local/lib
    Plugin directory:             /usr/local/lib/bareos/plugins
    C Compiler:                   cc version
    C++ Compiler:                 /usr/bin/c++ version
    Compiler flags:                -O2 -pipe  -fstack-protector 
-fno-strict-aliasing
    Linker flags:                   -L/usr/local/lib -L/usr/local/lib 
-fstack-protector
    Libraries:                    -lpthread  -lintl
    Statically Linked Tools:      no
    Statically Linked FD:         no
    Statically Linked SD:         no
    Statically Linked DIR:        no
    Statically Linked CONS:       no
    Database backends:            PostgreSQL
    Database port:
    Database name:                bareos
    Database user:                bareos
    Database version:             2004

    Job Output Email:             root@localhost
    Traceback Email:              root@localhost
    SMTP Host Address:            localhost

    Director Port:                9101
    File daemon Port:             9102
    Storage daemon Port:          9103

    Director User:                bareos
    Director Group:               bareos
    Storage Daemon User:          bareos
    Storage DaemonGroup:          operator
    File Daemon User:
    File Daemon Group:

    Large file support:           yes
    Bareos conio support:         no
    readline support:             no
    TCP Wrappers support:         yes -lwrap
    TLS support:                  yes
    Encryption support:           yes
    OpenSSL support:              yes
    GNUTLS support:               no
    ZLIB support:                 yes
    LZO support:                  yes
    FASTLZ support:               no
    JANSSON support:              yes
    LMDB support:                 no
    NDMP support:                 no
    enable-smartalloc:            yes
    enable-lockmgr:               no
    bat support:                  no
    tray-monitor support:         no
    client-only:                  no
    build-dird:                   yes
    build-stored:                 yes
    Plugin support:               yes
    AFS support:                  no
    ACL support:                  yes
    XATTR support:                yes
    SCSI Crypto support:          no
    GLUSTERFS support:            no
    DROPLET support:              no
    CEPH RADOS support:           no
    RADOS striping support:       no
    CEPHFS support:               no
    ELASTO support:               no
    Python support:               no
    systemd support:              no
    Batch insert enabled:         PostgreSQL
    cmocka support:               no


On 9/8/17 7:01 AM, Stephan Duehr wrote:
Hi Douglas,

normally, there should be only one DB connection open if no jobs are running.
As you are using FreeBSD, may it be that the Bareos binaries are built with
the experimental database pooling functionality? Could you please post your
catalog config (Catalog { ... }), and check if you are using any of the
parameters, look for "experimental database pooling functionality" in
http://doc.bareos.org/master/html/bareos-manual-main-reference.html#CatalogResource 


Regards,

Stephan

On 09/07/2017 07:16 PM, Douglas K. Rand wrote:
Hey Stephan.   I have Maximum Concurrent Jobs set to 10 for the director
and also set to 10 in my common Job Defs. And also set to 10 in my
on-disk storage daemon; and set to 1 in my LTO-6 SD.

I have 86 jobs that start at 21:00 each night: 84 always incremental
jobs at priority 10, one consolidation job at priority 20, and one
catalog job at priority 50.

Yesterday we consumed all of the Postgres connections yet again, so I
re-started the Bareos director. After that re-start the established
Postgres connections were two (one for the query to get the status):

  usename |  pid  | state  |                                query
---------+-------+--------+---------------------------------------------------------------------- 

  bareos  | 72370 | idle   | SET client_encoding TO 'SQL_ASCII'
  bareos  | 90620 | active | SELECT usename, pid, state, query FROM
pg_stat_activity ORDER BY pid
(2 rows)

But just after 21:00 there were 98 Postgres connections:

  usename |  pid  | state  | query
---------+-------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 


  bareos  | 72370 | idle   | SET client_encoding TO 'SQL_ASCII'
  bareos  | 90698 | idle   | UPDATE Media SET
VolJobs=10,VolFiles=1,VolBlocks=130413,VolBytes=8412907724,VolMounts=10,VolErrors=0,VolWrites=130414,MaxVolBytes=16000000000,VolStatus='Append',Slot=0,InChanger=0,VolReadTime=0,VolWriteTime=7065975,LabelType=0,StorageId=1,PoolId=6,VolRetention=31536000,VolUseDuration=0,MaxVolJobs=0,MaxVolFiles=0,Enabled=1,LocationId=0,ScratchPoolId=0,RecyclePoolId=0,RecycleCount=0,Recycle=1,ActionOnPurge=0,MinBlocksize=0,MaxBlocksize=0 


WHERE VolumeName='incr-0686'
  bareos  | 90699 | idle   | UPDATE Media SET
VolJobs=7,VolFiles=2,VolBlocks=164480,VolBytes=10610777273,VolMounts=7,VolErrors=0,VolWrites=164481,MaxVolBytes=16000000000,VolStatus='Append',Slot=0,InChanger=0,VolReadTime=0,VolWriteTime=8158808,LabelType=0,StorageId=1,PoolId=6,VolRetention=31536000,VolUseDuration=0,MaxVolJobs=0,MaxVolFiles=0,Enabled=1,LocationId=0,ScratchPoolId=0,RecyclePoolId=0,RecycleCount=0,Recycle=1,ActionOnPurge=0,MinBlocksize=0,MaxBlocksize=0 


WHERE VolumeName='incr-0683'
  bareos  | 90700 | idle   | UPDATE Media SET
VolJobs=2,VolFiles=0,VolBlocks=19062,VolBytes=1229720082,VolMounts=2,VolErrors=0,VolWrites=19063,MaxVolBytes=16000000000,VolStatus='Append',Slot=0,InChanger=0,VolReadTime=0,VolWriteTime=1101701,LabelType=0,StorageId=1,PoolId=6,VolRetention=31536000,VolUseDuration=0,MaxVolJobs=0,MaxVolFiles=0,Enabled=1,LocationId=0,ScratchPoolId=0,RecyclePoolId=0,RecycleCount=0,Recycle=1,ActionOnPurge=0,MinBlocksize=0,MaxBlocksize=0 


WHERE VolumeName='incr-0687'
  bareos  | 90701 | idle   | UPDATE Media SET
VolJobs=30,VolFiles=3,VolBlocks=242410,VolBytes=15637398352,VolMounts=30,VolErrors=0,VolWrites=242411,MaxVolBytes=16000000000,VolStatus='Append',Slot=0,InChanger=0,VolReadTime=0,VolWriteTime=11139499,LabelType=0,StorageId=1,PoolId=6,VolRetention=31536000,VolUseDuration=0,MaxVolJobs=0,MaxVolFiles=0,Enabled=1,LocationId=0,ScratchPoolId=0,RecyclePoolId=0,RecycleCount=0,Recycle=1,ActionOnPurge=0,MinBlocksize=0,MaxBlocksize=0 


WHERE VolumeName='incr-0670'
  bareos  | 90702 | idle   | UPDATE Media SET
VolJobs=5,VolFiles=1,VolBlocks=90985,VolBytes=5869432460,VolMounts=5,VolErrors=0,VolWrites=90986,MaxVolBytes=16000000000,VolStatus='Append',Slot=0,InChanger=0,VolReadTime=0,VolWriteTime=4620587,LabelType=0,StorageId=1,PoolId=6,VolRetention=31536000,VolUseDuration=0,MaxVolJobs=0,MaxVolFiles=0,Enabled=1,LocationId=0,ScratchPoolId=0,RecyclePoolId=0,RecycleCount=0,Recycle=1,ActionOnPurge=0,MinBlocksize=0,MaxBlocksize=0 


WHERE VolumeName='incr-0684'
  bareos  | 90703 | idle   | UPDATE Media SET
VolJobs=1,VolFiles=0,VolBlocks=0,VolBytes=218,VolMounts=1,VolErrors=0,VolWrites=1,MaxVolBytes=16000000000,VolStatus='Append',Slot=0,InChanger=0,VolReadTime=0,VolWriteTime=22,LabelType=0,StorageId=1,PoolId=6,VolRetention=31536000,VolUseDuration=0,MaxVolJobs=0,MaxVolFiles=0,Enabled=1,LocationId=0,ScratchPoolId=0,RecyclePoolId=0,RecycleCount=0,Recycle=1,ActionOnPurge=0,MinBlocksize=0,MaxBlocksize=0 


WHERE VolumeName='incr-0689'
  bareos  | 90704 | idle   | UPDATE Media SET
VolJobs=1,VolFiles=0,VolBlocks=0,VolBytes=218,VolMounts=1,VolErrors=0,VolWrites=1,MaxVolBytes=16000000000,VolStatus='Append',Slot=0,InChanger=0,VolReadTime=0,VolWriteTime=21,LabelType=0,StorageId=1,PoolId=6,VolRetention=31536000,VolUseDuration=0,MaxVolJobs=0,MaxVolFiles=0,Enabled=1,LocationId=0,ScratchPoolId=0,RecyclePoolId=0,RecycleCount=0,Recycle=1,ActionOnPurge=0,MinBlocksize=0,MaxBlocksize=0 


WHERE VolumeName='incr-0690'
  bareos  | 90705 | idle   | SELECT sum(JobFiles) FROM Job WHERE JobId
IN
(30121,30972,31148,31234,31320,31410,31498,31588,31677,31849,31937,32023,32110,32197,32285,32372,32543,32659,32745,32858,32957,33045,33155,33327,33413,33499,33612) 


  bareos  | 90706 | idle   | UPDATE Media SET
VolJobs=1,VolFiles=0,VolBlocks=0,VolBytes=218,VolMounts=1,VolErrors=0,VolWrites=1,MaxVolBytes=16000000000,VolStatus='Append',Slot=0,InChanger=0,VolReadTime=0,VolWriteTime=17,LabelType=0,StorageId=1,PoolId=6,VolRetention=31536000,VolUseDuration=0,MaxVolJobs=0,MaxVolFiles=0,Enabled=1,LocationId=0,ScratchPoolId=0,RecyclePoolId=0,RecycleCount=0,Recycle=1,ActionOnPurge=0,MinBlocksize=0,MaxBlocksize=0 


WHERE VolumeName='incr-0692'
  bareos  | 90707 | idle   | UPDATE Media SET
VolJobs=1,VolFiles=0,VolBlocks=0,VolBytes=218,VolMounts=1,VolErrors=0,VolWrites=1,MaxVolBytes=16000000000,VolStatus='Append',Slot=0,InChanger=0,VolReadTime=0,VolWriteTime=16,LabelType=0,StorageId=1,PoolId=6,VolRetention=31536000,VolUseDuration=0,MaxVolJobs=0,MaxVolFiles=0,Enabled=1,LocationId=0,ScratchPoolId=0,RecyclePoolId=0,RecycleCount=0,Recycle=1,ActionOnPurge=0,MinBlocksize=0,MaxBlocksize=0 


WHERE VolumeName='incr-0693'
  bareos  | 90708 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90709 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90710 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90711 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90712 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90713 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90714 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90715 | idle   | UPDATE Pool SET
NumVols=143,MaxVols=1024,UseOnce=0,UseCatalog=1,AcceptAnyVolume=0,VolRetention='31536000',VolUseDuration='0',MaxVolJobs=0,MaxVolFiles=0,MaxVolBytes=16000000000,Recycle=1,AutoPrune=0,LabelType=0,LabelFormat='incr-',RecyclePoolId=0,ScratchPoolId=0,ActionOnPurge=0,MinBlockSize=0,MaxBlockSize=0 


WHERE PoolId=6
  bareos  | 90716 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90717 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90718 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90719 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90720 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90721 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90722 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90723 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90724 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90725 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90726 | idle   | COMMIT
  bareos  | 90727 | idle   | COMMIT
  bareos  | 90728 | idle   | COMMIT
  bareos  | 90729 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90730 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90731 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90732 | idle   | COMMIT
  bareos  | 90733 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90734 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90735 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90736 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90737 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90738 | idle   | COMMIT
  bareos  | 90739 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90740 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90741 | idle   | COMMIT
  bareos  | 90742 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90743 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90744 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90745 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90746 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90747 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90748 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90749 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90750 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90751 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90752 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90753 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90754 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90755 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90756 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90757 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90758 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90759 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90760 | idle   | UPDATE Pool SET
NumVols=144,MaxVols=1024,UseOnce=0,UseCatalog=1,AcceptAnyVolume=0,VolRetention='31536000',VolUseDuration='0',MaxVolJobs=0,MaxVolFiles=0,MaxVolBytes=16000000000,Recycle=1,AutoPrune=0,LabelType=0,LabelFormat='incr-',RecyclePoolId=0,ScratchPoolId=0,ActionOnPurge=0,MinBlockSize=0,MaxBlockSize=0 


WHERE PoolId=6
  bareos  | 90761 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90762 | idle   | UPDATE Pool SET
NumVols=145,MaxVols=1024,UseOnce=0,UseCatalog=1,AcceptAnyVolume=0,VolRetention='31536000',VolUseDuration='0',MaxVolJobs=0,MaxVolFiles=0,MaxVolBytes=16000000000,Recycle=1,AutoPrune=0,LabelType=0,LabelFormat='incr-',RecyclePoolId=0,ScratchPoolId=0,ActionOnPurge=0,MinBlockSize=0,MaxBlockSize=0 


WHERE PoolId=6
  bareos  | 90763 | idle   | UPDATE Pool SET
NumVols=146,MaxVols=1024,UseOnce=0,UseCatalog=1,AcceptAnyVolume=0,VolRetention='31536000',VolUseDuration='0',MaxVolJobs=0,MaxVolFiles=0,MaxVolBytes=16000000000,Recycle=1,AutoPrune=0,LabelType=0,LabelFormat='incr-',RecyclePoolId=0,ScratchPoolId=0,ActionOnPurge=0,MinBlockSize=0,MaxBlockSize=0 


WHERE PoolId=6
  bareos  | 90764 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90765 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90766 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90767 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90768 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90769 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90770 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90771 | idle   | COMMIT
  bareos  | 90772 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90773 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90774 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90775 | active | FETCH 100 FROM _bac_cursor
  bareos  | 90776 | idle   | COMMIT
  bareos  | 90777 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90782 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90783 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90784 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90785 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90786 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90787 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90788 | idle   | UPDATE Pool SET
NumVols=147,MaxVols=1024,UseOnce=0,UseCatalog=1,AcceptAnyVolume=0,VolRetention='31536000',VolUseDuration='0',MaxVolJobs=0,MaxVolFiles=0,MaxVolBytes=16000000000,Recycle=1,AutoPrune=0,LabelType=0,LabelFormat='incr-',RecyclePoolId=0,ScratchPoolId=0,ActionOnPurge=0,MinBlockSize=0,MaxBlockSize=0 


WHERE PoolId=6
  bareos  | 90789 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90790 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90791 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90792 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90793 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90794 | idle   | SELECT count(*) from Media WHERE PoolId=6
  bareos  | 90795 | idle   | SELECT currval('Job_Jobid_seq')
  bareos  | 90796 | idle   | SELECT count(*) from Media WHERE PoolId=1
  bareos  | 90799 | idle   | COMMIT
  bareos  | 90864 | active | SELECT usename, pid, state, query FROM
pg_stat_activity ORDER BY pid
(98 rows)

What is interesting is that after all of the backups are done and the
director is quiet with no running jobs that I still have 28 Postgres
database connections:

  usename |  pid  | state  | query
---------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 


  bareos  | 50283 | active | SELECT usename, pid, state, query FROM 
pg_stat_activity ORDER BY pid
  bareos  | 72370 | idle   | SET client_encoding TO 'SQL_ASCII'
  bareos  | 90795 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30114
  bareos  | 98119 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30115
  bareos  | 98121 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30117
  bareos  | 98123 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30121
  bareos  | 98128 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30122
  bareos  | 98130 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30125
  bareos  | 98132 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30126
  bareos  | 98134 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30127
  bareos  | 98136 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30128
  bareos  | 98140 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30129
  bareos  | 98142 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30130
  bareos  | 98144 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30132
  bareos  | 98146 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30222
  bareos  | 98150 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30139
  bareos  | 98152 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30142
  bareos  | 98154 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30144
  bareos  | 98156 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30146
  bareos  | 98158 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30147
  bareos  | 98161 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30151
  bareos  | 98163 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30163
  bareos  | 98167 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30164
  bareos  | 98169 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30171
  bareos  | 98173 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30173
  bareos  | 98175 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30174
  bareos  | 98177 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30175
  bareos  | 98179 | idle   | SELECT
VolSessionId,VolSessionTime,PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles 

FROM Job WHERE JobId=30176
(28 rows)


On 09/07/17 06:07, Stephan Duehr wrote:
Hi Douglas,

I would expect one DB connection per running job as normal.
How do you have Maximum Concurrent Jobs configured?
I'm currently not sure if or why the DB Connection is already opened at 
scheduled time, when
Maximum Concurrent Jobs is less then the number of scheduled jobs.

Regards,

Stephan

On 08/09/2017 05:20 PM, Douglas K. Rand wrote:
I'm having a problem where Bareos consumes all 150 of my Postgres 
connections and then backup jobs start to fail with
the error:

06-Aug 21:00 bareos JobId 0: Fatal error: sql_pooling.c:83 Could not open 
database "bareos": ERR=postgresql.c:246 Unable
to connect to PostgreSQL server. Database=bareos User=bareos
Possible causes: SQL server not running; password incorrect; 
max_connections exceeded.

I can keep bumping max_connections in Postgres, but there seems to be a 
problem here. I'm using Bareos 16.2.4 on FreeBSD
10.2 with Postgres 9.5.2.

At a minute before the backups start there are two connections to Postgres:

   usename |  pid  | state  |                          query
---------+-------+--------+--------------------------------------------------------- 

   bareos  |  6542 | idle   | SET client_encoding TO 'SQL_ASCII'
   bareos  | 43277 | active | SELECT usename, pid, state, query FROM 
pg_stat_activity

But a minute after backups start there are 96 connections to Postgres, 
I've attached the status to the email, which is a
result of:

psql --pset=pager=off --user=bareos --dbname=bareos --command="SELECT 
usename, pid, state, query FROM pg_stat_activity"

lsof -i :postgresql

ps -axlwwH -U bareos

As you can see there are 9 UPDATE Media statements running, all via 
different connections each from a different Bareos
thread; and 74 SELECT count(*) from Media statements also each from 
different threads; and then a handful of other
statements. Each connection is from the Bareos director process, but from 
what seems to be different threads.

It almost seems that each scheduled spawns a thread for when that job is 
scheduled to run (21:00 in my case) and then as
the backups finish the threads go away, along with the Postgres 
connection. Here are the number of Postgres connections
over time:

2017-08-08/21:00:00:(2 rows)
2017-08-08/21:01:00:(96 rows)
2017-08-08/21:02:00:(92 rows)
2017-08-08/21:03:00:(94 rows)
2017-08-08/21:05:00:(93 rows)
2017-08-08/21:06:00:(91 rows)
2017-08-08/21:07:00:(89 rows)
2017-08-08/21:08:00:(87 rows)
2017-08-08/21:09:00:(84 rows)
2017-08-08/21:10:00:(78 rows)
2017-08-08/21:11:00:(77 rows)
2017-08-08/21:12:00:(75 rows)
2017-08-08/21:13:00:(72 rows)
2017-08-08/21:14:00:(71 rows)
2017-08-08/21:15:00:(64 rows)
2017-08-08/21:16:00:(63 rows)
2017-08-08/21:17:00:(60 rows)
2017-08-08/21:18:00:(58 rows)
2017-08-08/21:19:00:(56 rows)
2017-08-08/21:20:00:(53 rows)
2017-08-08/21:21:00:(51 rows)
2017-08-08/21:23:00:(48 rows)
2017-08-08/21:24:00:(46 rows)
2017-08-08/21:25:00:(44 rows)
2017-08-08/21:26:00:(43 rows)
2017-08-08/21:27:00:(42 rows)
2017-08-08/21:28:00:(41 rows)
2017-08-08/21:31:00:(40 rows)
2017-08-08/21:32:00:(34 rows)
2017-08-08/21:33:00:(28 rows)
2017-08-08/21:34:00:(24 rows)
2017-08-08/21:35:00:(21 rows)
2017-08-08/21:36:00:(19 rows)
2017-08-08/21:37:00:(13 rows)
2017-08-08/21:44:00:(11 rows)
2017-08-08/21:51:00:(9 rows)
2017-08-08/21:53:00:(7 rows)
2017-08-08/21:56:00:(6 rows)
2017-08-08/22:47:00:(5 rows)
2017-08-09/02:13:00:(4 rows)
2017-08-09/02:16:00:(3 rows)

Backup completes at 02:15






--
You received this message because you are subscribed to the Google Groups 
"bareos-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to bareos-users+unsubscr...@googlegroups.com.
To post to this group, send email to bareos-users@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.