[Bucardo-general] Possible bug?

Olaf Rühenbeck some.individuum at gmail.com
Mon Jun 3 10:35:25 UTC 2013


This did the trick, thanks! :) 

I've been working on the issue for the past two weeks but can't get an
understanding of the following issue with the same command and would
really appreciate some more help:

root at maguro:~# bucardo --verbose add sync m_baculadir_v
herd=maguro dbs=maguro onetimecopy=2 WARNING:  Issuing rollback() due
to DESTROY without explicit disconnect() of DBD::Pg::db handle
dbname=baculadir;host=localhost;port=5432 at line 273. CONTEXT:
PL/Perl function "validate_sync" SQL statement "SELECT
validate_sync('m_baculadir_v')" PL/Perl function "validate_sync" Failed
to add sync: DBD::Pg::st execute failed: ERROR:  DBD::Pg::db do failed:
ERROR:  permission denied for database baculadir at line 274. at line
30. CONTEXT:  PL/Perl function "validate_sync" at /usr/bin/bucardo line
4113.

I started by checking the database logs for the issued statements,
logged in as the user bucardo via psql and issued them by hand:
Everything worked fine. 
Next I started reading more on database rights, procedural languages in
postgres and added some more rights to allow my user to execute
functions, but if I read this right, this was already working as the
error seems to be inside the context of the validate_sync function when
it issues a DO statement.

At line 274 in the stored procedure I find the following block:

        ## Perform the check for plpgsql
        $SQL = q{SELECT count(*) FROM pg_language WHERE lanname =
        'plpgsql'}; my $count = $dbh->selectall_arrayref($SQL)->[0][0];
        if ($count < 1) {
            $dbh->do('CREATE LANGUAGE plpgsql');
            $dbh->commit();
            warn "Created language plpgsql on database $dbname\n";
        }

        ## Create the bucardo schema as needed
        $sth = $sth{hazschema};
        $count = $sth->execute('bucardo');
        $sth->finish();
        if ($count < 1) {
            $dbh->do('CREATE SCHEMA bucardo');
        }

So I tried to execute those statements by hand: 

root at maguro:/var/log/bucardo# psql -U bucardo -d baculadir
Password for user bucardo: 
psql (9.1.9)
Type "help" for help.

baculadir=# SELECT count(*) FROM pg_language WHERE lanname = 'plpgsql';
 count 
-------
     1
(1 row)

baculadir=# CREATE LANGUAGE plpgsql;
ERROR:  language "plpgsql" already exists

The second one wouldn't be executed as its count is not < 1 but
anyways, the statement in the next block succeeds too:

baculadir=# CREATE schema bucardo;
CREATE SCHEMA

The postgresql log states the following:

2013-06-03 12:33:13 CEST LOG:  Starting validate_sync for m_baculadir_v
2013-06-03 12:33:13 CEST CONTEXT:  PL/Perl function "validate_sync"
        SQL statement "SELECT validate_sync('m_baculadir_v')"
        PL/Perl function "validate_sync"
2013-06-03 12:33:13 CEST STATEMENT:  INSERT INTO bucardo.sync
(dbs,herd,name,onetimecopy) VALUES ($1,$2,$3,$4) 2013-06-03 12:33:13
CEST ERROR:  permission denied for database baculadir 2013-06-03
12:33:13 CEST STATEMENT:  CREATE SCHEMA bucardo 2013-06-03 12:33:13
CEST WARNING:  Issuing rollback() due to DESTROY without explicit
disconnect() of DBD::Pg::db handle
dbname=baculadir;host=localhost;port=5432 at line 273. 2013-06-03
12:33:13 CEST CONTEXT:  PL/Perl function "validate_sync" SQL statement
"SELECT validate_sync('m_baculadir_v')" PL/Perl function
"validate_sync" 2013-06-03 12:33:13 CEST ERROR:  DBD::Pg::db do failed:
ERROR:  permission denied for database baculadir at line 274. at line
30. 2013-06-03 12:33:13 CEST CONTEXT:  PL/Perl function "validate_sync"
2013-06-03 12:33:13 CEST STATEMENT:  INSERT INTO bucardo.sync
(dbs,herd,name,onetimecopy) VALUES ($1,$2,$3,$4)

Well as I said, I cant figure out why bucardo would get a permission
denied as I granted all on database baculadir to bucardo and can do
everything "by hand" :x

Maybe I have overlooked something. Thanks for looking at it in
advance! :)

On Tue, 14 May 2013 19:40:05 +0200
Adam Kozłowski <tronix.ak at gmail.com> wrote:

> Hi,
> 
> Try to remove table then add table once again and add sync.
> #bucardo_ctl remove table ...
> #bucardo_ctl add table …
> #bucardo_ctl add sync ...
> 
> I can't check right now, but bucardo probable gather information about
> tables while adding tables...
> 
> 
> /Adam
> 
> 
> 2013/5/14 Olaf Rühenbeck <some.individuum at gmail.com>:
> > Hey there,
> >
> > I try to use bucardo do replicate a few databases between 2 hosts
> > to have a always up to date backup copy of our database in another
> > host. In this case the bacula director catalog database.
> >
> > I try to add a sync:
> >
> > # bucardo add sync m_baculadir_v herd=maguro dbs=maguro
> > onetimecopy=2
> >
> > and get:
> >
> > Failed to add sync: DBD::Pg::st execute failed: ERROR:  Table
> > "public.jobhisto" must specify a primary key! at line 117. at line
> > 30. CONTEXT:  PL/Perl function "validate_sync" at /usr/bin/bucardo
> > line 4113.
> >
> > As its the first sync I used onetimecopy=2 to get the target
> > database fully populated.
> >
> > I read in the archives of this list that bucardo always requires a
> > primary key on every table it tries to sync, this is kinda annoying
> > and I will have to write a script to take care of this after every
> > major bacula database schema upgrade, but also if I try to set
> > onetimecopy=1 to avoid the necessity of the primary key it comes up
> > with the same message. So I added a primary key to the table in the
> > source database and I still get the same error:
> >
> >
> > baculadir=# alter table jobhisto add primary key (jobid);
> > NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
> > "jobhisto_pkey" for table "jobhisto"
> > ALTER TABLE
> > baculadir=# \d jobhisto
> >                   Table "public.jobhisto"
> >      Column      |            Type             | Modifiers
> > -----------------+-----------------------------+-----------
> >  jobid           | integer                     | not null
> >  job             | text                        | not null
> >  name            | text                        | not null
> >  type            | character(1)                | not null
> >  level           | character(1)                | not null
> >  clientid        | integer                     |
> >  jobstatus       | character(1)                | not null
> >  schedtime       | timestamp without time zone |
> >  starttime       | timestamp without time zone |
> >  endtime         | timestamp without time zone |
> >  realendtime     | timestamp without time zone |
> >  jobtdate        | bigint                      |
> >  volsessionid    | integer                     |
> >  volsessiontime  | integer                     |
> >  jobfiles        | integer                     |
> >  jobbytes        | bigint                      |
> >  readbytes       | bigint                      |
> >  joberrors       | integer                     |
> >  jobmissingfiles | integer                     |
> >  poolid          | integer                     |
> >  filesetid       | integer                     |
> >  priorjobid      | integer                     |
> >  purgedfiles     | smallint                    |
> >  hasbase         | smallint                    |
> >  hascache        | smallint                    |
> >  reviewed        | smallint                    |
> >  comment         | text                        |
> > Indexes:
> >     "jobhisto_pkey" PRIMARY KEY, btree (jobid)
> >     "jobhisto_idx" btree (starttime)
> >
> > baculadir=# \q
> >
> > And still get the same error:
> >
> > # bucardo add sync m_baculadir_v herd=maguro dbs=maguro
> > onetimecopy=1 Failed to add sync: DBD::Pg::st execute failed:
> > ERROR:  Table "public.jobhisto" must specify a primary key! at line
> > 117. at line 30. CONTEXT:  PL/Perl function "validate_sync"
> > at /usr/bin/bucardo line 4113.
> >
> > Any ideas on this? Would appreciate some help :)
> >
> > thanks,
> >
> > Olaf
> >
> > _______________________________________________
> > Bucardo-general mailing list
> > Bucardo-general at bucardo.org
> > https://mail.endcrypt.com/mailman/listinfo/bucardo-general
> >

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 836 bytes
Desc: not available
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20130603/11704c32/attachment.sig>


More information about the Bucardo-general mailing list