Re: [Bucardo-general] Possible bug?
Hi there and thanks for the quick answer: On Mon, 3 Jun 2013 10:18:19 -0400 Greg Sabino Mullane g...@endpoint.com wrote: On Mon, Jun 03, 2013 at 12:35:25PM +0200, Olaf Rühenbeck wrote: ... 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 Make sure the bucardo account exists on all databases involved, and that it is a superuser. Then make sure that's the account name used inside the bucardo.db table: bucardo list dbs root@maguro:~# bucardo list dbs Database: m_baculadir Status: active Conn: psql -p 5432 -U baculadir -d baculadir -h localhost Database: v_baculadir Status: active Conn: psql -p 5432 -U maguro -d baculadir -h XXX Postgres on XXX: http://pastebin.com/CbE9ye7z (So the user is a superuser and bucardo actually connected as this one) http://pastebin.com/VxmqApwF (same here) sadly same error: root@maguro:/home/rueheo4v# bucardo 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. maguro and bucardo respectively have been granted all on bucardo and baculadir databases on both hosts. Greetings, Olaf Rühenbeck signature.asc Description: PGP signature ___ Bucardo-general mailing list Bucardo-general@bucardo.org https://mail.endcrypt.com/mailman/listinfo/bucardo-general
Re: [Bucardo-general] Possible bug?
On Tue, Jun 04, 2013 at 12:01:21PM +0200, Olaf Rühenbeck wrote: ... root@maguro:~# bucardo list dbs Database: m_baculadir Status: active Conn: psql -p 5432 -U baculadir -d baculadir -h localhost Looks like the user here is baculadir, not maguro. Perhaps a type? Or baculadir needs to be a superuser? -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 signature.asc Description: Digital signature ___ Bucardo-general mailing list Bucardo-general@bucardo.org https://mail.endcrypt.com/mailman/listinfo/bucardo-general
Re: [Bucardo-general] Possible bug?
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
Re: [Bucardo-general] Possible bug?
On Mon, Jun 03, 2013 at 12:35:25PM +0200, Olaf Rühenbeck wrote: ... 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 Make sure the bucardo account exists on all databases involved, and that it is a superuser. Then make sure that's the account name used inside the bucardo.db table: bucardo list dbs -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 signature.asc Description: Digital signature ___ Bucardo-general mailing list Bucardo-general@bucardo.org https://mail.endcrypt.com/mailman/listinfo/bucardo-general
Re: [Bucardo-general] Possible bug?
On Tue, May 14, 2013 at 10:17:40AM +0200, Olaf Rühenbeck wrote: 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: A onetimecopy=1 will not remove the need for a primary key. Also keep in mind that a primary key is not strictly needed - just a unique index (which is all a primary key really is). # 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. Are you sure the new primary key is on all databases? As Adam points out, the primary key information is added to the internal Bucardo tables when the table is first added. You may be able to do it on the fly, but make sure that all the pkey related fields get updated! To view things: bucardo list table foobar -vv Then to update the pkey field: bucardo update table foobar pkey=id -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 signature.asc Description: Digital signature ___ Bucardo-general mailing list Bucardo-general@bucardo.org https://mail.endcrypt.com/mailman/listinfo/bucardo-general
Re: [Bucardo-general] Possible bug?
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 ___ Bucardo-general mailing list Bucardo-general@bucardo.org https://mail.endcrypt.com/mailman/listinfo/bucardo-general