On Wed, May 23, 2007 at 04:05:17PM +0200, Dspam wrote: > Le Wed, 23 May 2007 07:37:53 -0500, > Kenneth Marshall <[EMAIL PROTECTED]> a ??crit : > > > On Wed, May 23, 2007 at 10:03:26AM +0200, Dspam wrote: > > > Le Tue, 22 May 2007 13:17:15 -0500, > > > Kenneth Marshall <[EMAIL PROTECTED]> a ??crit : > > > > > > > On Tue, May 22, 2007 at 06:22:21PM +0200, Pea wrote: > > > > > Hello, > > > > > > > > > > It is possible to switch dspam backend from mysql to postgresql. > > > > > I've a huge database (~ 20Go) and for some reasons i want to > > > > > have postgresql backend. A dump from mysql to postgresql > > > > > doesn't work... Any ideas ? > > > > > > > > > > Thank > > > > > > > > > Yes, it is possible. You need to use the BIGINT token table > > > > definition for the PostgreSQL database and adjust the 64-bit > > > > hash from the unsigned usage in MySQL to the signed usage > > > > of PostgreSQL by subtracting 9223372036854775808 (2**63) > > > > from the MySQL export before importing it into PostgreSQL. > > > > > > > > Ken > > > > > > > Thanks Ken for you answer. But how can i do that ? There is an way > > > to automate this process ? > > > Pea > > > > We used a perl script with DBI/DBD-Pg and DBD-MySQL to move our data > > and avoid having to deal with the size of the export files. If you > > have the disk space for the MySQL dump, you can post-process the dump > > before loading it into PostgreSQL. > > > > You need to benchmark your DSPAM with the PostgreSQL backend to ensure > > that it meets your performance needs. We have been trying to move to a > > PostgreSQL backend since 8.0 was released. Neither 8.0 or 8.1 could > > provide the performance we needed on our current hardware. 8.2 can > > handle the transactions/sec but with too little headroom for us to use > > in our current system. 8.3 with the HOT update patch should allow us > > to move from our multiple MySQL backends to a single PostgreSQL > > backend. We had to split our DB because of locking problems with > > MYISAM tables once there were more that a couple thousand users. > > > > Good luck. > > > > Ken > > > Yes i wait 8.3 version too. But i want to do some tests before this > release. But my perl is very very poor. So could you give me an > example or some lines codes ? > I have the disk place for the dump. Any help greatly appreciated. > Thanks
The easiest thing for you to do if that amount of perl is too involved is to import the MySQL data into PostgreSQL first using a token type of NUMERIC(20) instead of BIGINT. Once the data has been loaded, use the program dspam_pg2int8 from the tools.pgsql_drv directory to convert your database to the PostgreSQL BIGINT version with the expected increase in performance. Good luck. Ken
