Am 05.07.2015 um 18:16 schrieb Kern Sibbald: > On 05.07.2015 15:37, Christian Manal wrote: >> Hi Kern, >> >>> It appears to me that for some reason you have duplicate filename >>> entries in the Filename table. Perhaps you have a filename of NULL and >>> a filename of "", which perhaps PostgreSQL treats as equal in certain >>> operations (e.g. during an import). >>> >>> The Bacula SQL statement is supposed to filter out duplicate names. You >>> might list the Filename table and take a look if there are two entries >>> that seem to be identical (it seems like you already tried this). >> I tried querying for the values that were indicated as duplicates by the >> error messages. I either got only one entry back or none at all. I also >> tried looking for duplicates in general with something along these >> lines, with no result: >> >> select name,filenameid from filename group by name,filenameid having >> count(name) > 1; >> >> >>> Another possibility for correcting it is to run the dbcheck program. I >>> think it has an option to check for duplicate filenames, but I am not >>> 100% certain since I have not used it in a long time. >> Running that now, but I kinda doubt it'll turn anything up if manual >> querying doesn't find duplicate rows. >> >> >>> Did you recently change the backend from MySQL to PostgreSQL or make >>> some other such modification or did your OS crash or PostgreSQL crash? >>> Normally, the table should never get messed up as it appears to be. >> The last big thing I did with the catalog was moving it to another host >> and upgrading the Postgres version from 8.3 to 9.3 in the process, but >> everything worked fine until the upgrade to Bacula 7.0.5 a few days ago. > > When you did that PostgreSQL upgrade, did you export the database in > .sql format before the upgrade then reload it back after the upgrade? > > If not, that is probably where things went wrong. With MySQL, you just > upgrade the program and all works. With earlier PostgreSQL especially > before 8.4 or 9.x you have to export them then reimport them.
I dumped it in Postgres' "custom" format on the old host, with "pg_dump -Fc", and then loaded it with pg_restore on the new host. But I just realized that I might'be messed up creating the new database. I used the SQL_ASCII encoding, but didn't set LC_COLLATE and LC_CTYPE to C. Could that be the problem? Regards, Christian Manal >>> Since reindexing the Filename table fails, most likely you really have >>> an PostgreSQL table corruption problem that probably can only be fixed >>> by some PostgreSQL repair command. >> I figured as much when the reindexing failed, but since this only >> started after the upgrade, just fixing the DB now would seem to be >> treating the symptons when I'd rather find and fix the cause. >> >> >> Regards, >> Christian Manal >> >> >>> On 05.07.2015 12:43, Christian Manal wrote: >>>> Hi list, >>>> >>>> I recently upgraded my Bacula director and storage to 7.0.5 from 5.2.x >>>> and now some jobs fail with the following error: >>>> >>>> Fatal error: sql_create.c:851 Fill Filename table Query failed: INSERT >>>> INTO Filename (Name) SELECT a.Name FROM (SELECT DISTINCT Name FROM >>>> batch) as a WHERE NOT EXISTS (SELECT Name FROM Filename WHERE Name = >>>> a.Name): ERR=ERROR: duplicate key value violates unique constraint >>>> "filename_name_idx" >>>> >>>> Platform is Solaris 10, database PostgreSQL 9.3. >>>> >>>> I tried reindexing the Filename table, which fails with a similar error, >>>> yet when I query the table for duplicate entries, I can't find any. >>>> >>>> >>>> Any help with this issue would be appreciated. >>>> >>>> >>>> Regards, >>>> Christian Manal >>>> >>>> ------------------------------------------------------------------------------ >>>> Don't Limit Your Business. Reach for the Cloud. >>>> GigeNET's Cloud Solutions provide you with the tools and support that >>>> you need to offload your IT needs and focus on growing your business. >>>> Configured For All Businesses. Start Your Cloud Today. >>>> https://www.gigenetcloud.com/ >>>> _______________________________________________ >>>> Bacula-users mailing list >>>> Bacula-users@lists.sourceforge.net >>>> https://lists.sourceforge.net/lists/listinfo/bacula-users >>>> >>> > > ------------------------------------------------------------------------------ Don't Limit Your Business. Reach for the Cloud. GigeNET's Cloud Solutions provide you with the tools and support that you need to offload your IT needs and focus on growing your business. Configured For All Businesses. Start Your Cloud Today. https://www.gigenetcloud.com/ _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
