Bacula Errors and Other Fun Stuff

So I recently upgraded PostgreSQL from 8.3 to 8.4 and more or less at the same time upgraded Bacula to 5.2.14 from some earlier version, I don't remember what can I say.

Anyway in the process I ran into this error when opening bconsole:

JobId 0: Fatal error: Pool Default not in database. sql_create.c:189 pool record Default already exists

I couldn't for the life of me figure this out.

So I eventually got into pgsql as bacula and did a select statement


bacula=> select * from pool
;
 poolid |  name   | numvols | maxvols | useonce | usecatalog | acceptanyvolume | volretention | voluseduration | maxvoljobs | maxvolfiles | maxvolbytes | autoprune | recycle | actiononpurge | pooltype | labeltype | labelformat | enabled | scratchpoolid | recyclepoolid | nextpoolid | migrationhighbytes | migrationlowbytes | migrationtime 
--------+---------+---------+---------+---------+------------+-----------------+--------------+----------------+------------+-------------+-------------+-----------+---------+---------------+----------+-----------+-------------+---------+---------------+---------------+------------+--------------------+-------------------+---------------
      1 | Default |      44 |     800 |       0 |          1 |               0 |      2592000 |              0 |         40 |           0 |           0 |         1 |       1 |             1 | Backup   |         0 | stor2-      |       1 |             0 |             0 |          0 |                  0 |                 0 |             0
      1 | Default |      44 |     800 |       0 |          1 |               0 |      2592000 |              0 |         40 |           0 |           0 |         1 |       1 |             1 | Backup   |         0 | stor2-      |       1 |             0 |             0 |          0 |                  0 |                 0 |             0
(2 rows)

As you can see my default pool is listed twice, oh man...

So I couldn't think of a clever way to fix that so I did a Google Search which led me to Stack Overflow 

http://stackoverflow.com/questions/1746213/how-to-delete-duplicate-entries-in-postgresql

and on to this function

CREATE OR REPLACE FUNCTION remove_duplicates(text, text) RETURNS void AS $$
DECLARE
  tablename ALIAS FOR $1;
  duplicate_column ALIAS FOR $2;
BEGIN
  EXECUTE 'CREATE TEMPORARY TABLE _DISTINCT_' || tablename || ' AS (SELECT DISTINCT ON (' || duplicate_column || ') * FROM ' || tablename || ' ORDER BY ' || duplicate_column || ' ASC);';
  EXECUTE 'DELETE FROM ' || tablename || ';';
  EXECUTE 'INSERT INTO ' || tablename || ' (SELECT * FROM _DISTINCT_' || tablename || ');';
  EXECUTE 'DROP TABLE _DISTINCT_' || tablename || ';';
  RETURN;
END;
$$ LANGUAGE plpgsql;

Of course I also had to run CREATE LANGUAGE plpgsql; first

Then I ran 

bacula=> SELECT remove_duplicates('pool','poolid');
 remove_duplicates 
-------------------
 
(1 row)

bacula=> select * from pool
;
 poolid |  name   | numvols | maxvols | useonce | usecatalog | acceptanyvolume | volretention | voluseduration | maxvoljobs | maxvolfiles | maxvolbytes | autoprune | recycle | actiononpurge | pooltype | labeltype | labelformat | enabled | scratchpoolid | recyclepoolid | nextpoolid | migrationhighbytes | migrationlowbytes | migrationtime 
--------+---------+---------+---------+---------+------------+-----------------+--------------+----------------+------------+-------------+-------------+-----------+---------+---------------+----------+-----------+-------------+---------+---------------+---------------+------------+--------------------+-------------------+---------------
      1 | Default |      44 |     800 |       0 |          1 |               0 |      2592000 |              0 |         40 |           0 |           0 |         1 |       1 |             1 | Backup   |         0 | stor2-      |       1 |             0 |             0 |          0 |                  0 |                 0 |             0
(1 row)

Now there are no more messages in bconsole and no more errors!