Dear Thomas,
In message <52d59d74.6000...@mtl.mit.edu> you wrote:
>
> 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
It seems so. Now that I'm through all this I found a link [1] which
desribes exactly this problem. Can't say why my searches didn;tfind
that earlier.
> 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
There are more:
bacula=# SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
relname
-----------------------------------
filename_filenameid_seq
job_jobid_seq
location_locationid_seq
restoreobject_restoreobjectid_seq
fileset_filesetid_seq
client_clientid_seq
media_mediaid_seq
jobmedia_jobmediaid_seq
mediatype_mediatypeid_seq
device_deviceid_seq
basefiles_baseid_seq
locationlog_loclogid_seq
log_logid_seq
path_pathid_seq
pool_poolid_seq
file_fileid_seq
storage_storageid_seq
(17 rows)
> 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.
Indeed this appears what needs to be done...
> alter sequence fileset_filesetid_seq restart with 76;
This is what I did, i. e. for example:
...
bacula=# select max(fileid) from file;
max
------------
4350001202
(1 row)
bacula=# select * from file_fileid_seq;
sequence_name | last_value | start_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called
-----------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
file_fileid_seq | 1 | 1 | 1 |
9223372036854775807 | 1 | 1 | 0 | f | f
(1 row)
bacula=# alter sequence file_fileid_seq restart with 4350001203;
ALTER SEQUENCE
...
Accoding to [1] this could have been simplified, but I didn't know
this then yet (and I hope I will never have to do this again).
> hope this helps and good luck,
It did. Thanks a lot for your help - I highly appreciate it.
If we ever should run into each other in real life please remember me
that I owe you some beer...
[1]
http://mtu.net/~jpschewe/blog/2010/06/migrating-bacula-from-mysql-to-postgresql/
Best regards,
Wolfgang Denk
--
DENX Software Engineering GmbH, MD: Wolfgang Denk & Detlev Zundel
HRB 165235 Munich, Office: Kirchenstr.5, D-82194 Groebenzell, Germany
Phone: (+49)-8142-66989-10 Fax: (+49)-8142-66989-80 Email: w...@denx.de
Don't panic.
------------------------------------------------------------------------------
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
