Wolfgang, > Dear Thomas, > > In message <52d555c5.9070...@mtl.mit.edu> you wrote: >> My guess is that during the migration from MySQL to Postgres, the >> sequences in Bacula did not get seeded right and probably are starting >> with a seed value of 1. > > Do you have any idea why this would happen? Is this something I can > influence? > Are there any other variables that might hit by similar issues?
I can't say exactly why it happened to you but my guess would be that this problem could hit anyone porting from mysql to postgres. I'm not familiar with the Bacula procedure for doing that (if you used one) but any Postgres sequence creations during the Postgres DB setup would more than likely be created with a default starting value of 1 - but if you've already got data in your database (migrated over from Mysql) then all sequences would need to be seeded properly. The bad news for you may be that almost all of the Bacula tables have sequences to generate their id fields. client file filename path job jobmedia fileset media pool I believe in each case, the 'id' field is the primary key which means it will be unique - thus any inserts should fail with an error and thus ensure that your database doesn't get into a strange funky state with multiple records having the same id. It may also be that you get lucky and avoid that for tables such as file, job, filename because if your database had been around awhile, it may be that re-starting those counters back to 1 may not overlap with any existing/current data (e.g. if the newest job before migration had an id of 10000 and all old jobs have been purged then restarting at 1 shouldn't cause problems depending on your configuration of course). With that said, if it was me, I'd re-seed all the sequences to where the id left off for each of the tables to avoid possible future insert errors/conflicts. >> select max(filesetid) from fileset; >> >> select * from fileset_filesetid_seq; > > This is what I get: > > Enter SQL query: select max(filesetid) from fileset; > +------+ > | max | > +------+ > | 75 | > +------+ > Enter SQL query: select * from fileset_filesetid_seq; > +-----------------------+------------+-------------+--------------+---------------------------+-----------+-------------+---------+-----------+-----------+ > | sequence_name | last_value | start_value | increment_by | max_value > | min_value | cache_value | log_cnt | is_cycled | is_called | > +-----------------------+------------+-------------+--------------+---------------------------+-----------+-------------+---------+-----------+-----------+ > | fileset_filesetid_seq | 4 | 1 | 1 | > 9,223,372,036,854,775,807 | 1 | 1 | 32 | f | t > | > +-----------------------+------------+-------------+--------------+---------------------------+-----------+-------------+---------+-----------+-----------+ > Enter SQL query: > > > Sorry, my DB / sql knowledge is somewhat limited (read: non-existient). > Could you please be so kind and tell me how I could fix that? Well, if your DB knowledge is limited then you may want to consult someone in your location who may be able to assist. Given that, I'll say the next part with the usual "use at your own risk" disclaimer. To change the last_value field of a Postgres sequence, you need to use the Postgres alter sequence command e.g. alter sequence fileset_filesetid_seq restart with 76; After that, the next fileset record created should be created with an id value of 76. This may be dependent on your version of Postgres. I am using 9.1.x and am looking at the following documentation: http://www.postgresql.org/docs/9.1/static/sql-altersequence.html I would then redo that above procedure for each of the sequences for each of the Bacula tables (querying to get the max value currently used and then resetting the last_value field to <max value> + 1). hope this helps and good luck, --tom ------------------------------------------------------------------------------ CenturyLink Cloud: The Leader in Enterprise Cloud Services. Learn Why More Businesses Are Choosing CenturyLink Cloud For Critical Workloads, Development Environments & Everything In Between. Get a Quote or Start a Free Trial Today. http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
