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@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@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...@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.individ...@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@bucardo.org
> > https://mail.endcrypt.com/mailman/listinfo/bucardo-general
> >
signature.asc
Description: PGP signature
_______________________________________________ Bucardo-general mailing list Bucardo-general@bucardo.org https://mail.endcrypt.com/mailman/listinfo/bucardo-general
