Mmmmm, not really: I still need to create the database. Maybe web2py can do
that for me if I start with a blank postgres database, but I am not
familiar with that, so this was an easier path for me.
By the way, I found out another problem: we need "serial PRIMARY KEY", so
the sed script must be changed to include:
s/INTEGER PRIMARY KEY AUTOINCREMENT/serial PRIMARY KEY/
And the postgres sequences must be initialized after import:
select setval(pg_get_serial_sequence('auth_user' , 'id'), (select max(
id) from auth_user) );
select setval(pg_get_serial_sequence('fulluser' , 'id'), (select max(
id) from fulluser) );
select setval(pg_get_serial_sequence('auth_group' , 'id'), (select max(
id) from auth_group) );
select setval(pg_get_serial_sequence('auth_membership' , 'id'), (select max(
id) from auth_membership) );
select setval(pg_get_serial_sequence('auth_permission' , 'id'), (select max(
id) from auth_permission) );
select setval(pg_get_serial_sequence('auth_event' , 'id'), (select max(
id) from auth_event) );
select setval(pg_get_serial_sequence('auth_cas' , 'id'), (select max(
id) from auth_cas) );
On Thursday, January 17, 2013 8:52:03 PM UTC+1, Niphlod wrote:
>
> yep, probably exporting all to csv and reimporting would have you saved a
> bit of hassle.
>
> On Wednesday, January 16, 2013 5:43:09 PM UTC+1, Daniel Gonzalez wrote:
>>
>> I found the problem: CHAR fields in postgres (the default that the .dump
>> command in sqlite is generating) will be extended with spaces to fill the
>> available length in postgres, so that username/password do not match
>> anymore.
>>
>> I have modified my import script like this:
>>
>> import_from_sqlite_ ( ) {
>> # Set the following:
>> # WEB2PY_PG_DB : the name of the database in postgres to import to
>> # SQLITE_FILE : the location of the sqlite file
>> # PG_USER : the postgres user
>> # PG_PWD : the potgres password
>> #
>> sql_dump="/tmp/from_sqlite.sql"
>> # Dump the data:
>> # - PRAGMA and AUTOINCREMENT are not supported in postgres
>> # - sqlite_sequence is specific to sqlite
>> # - CHAR will have fixed size in postgres, so it will be filled
>> with empty spaces. Use VARCHAR instead.
>> sqlite3 $SQLITE_FILE .dump | sed -e
>> '/^PRAGMA/d;/sqlite_sequence/d;s/AUTOINCREMENT//;s/
>> CHAR/ VARCHAR/' > $sql_dump
>> # Drop / recreate database
>> sudo -u postgres dropdb $WEB2PY_PG_DB
>> sudo -u postgres createdb -O $PG_USER -E UTF8 $WEB2PY_PG_DB
>> # Import the data
>> sudo -u postgres PGPASSWORD=$PG_PWD psql -h localhost -U $PG_USER -d
>> $WEB2PY_PG_DB < $sql_dump
>> # Show some data, to see if the import worked fine
>> sudo -u postgres psql -l
>> echo "\dt" | sudo -u postgres psql -d $WEB2PY_PG_DB
>> echo "select * from auth_user;" | sudo -u postgres psql -d
>> $WEB2PY_PG_DB
>> rm -f $sql_dump
>> }
>>
>>
>> Be careful with this, since it will drop and recreate the database. Also,
>> the changes to the sqlite dump are very specific to my situation. YMMV.
>>
>> On Wednesday, January 16, 2013 4:06:23 PM UTC+1, Daniel Gonzalez wrote:
>>>
>>> Hi,
>>>
>>> The following code was working when using SQLite, and now that I have
>>> moved to postgres it is failing:
>>>
>>> auth.login_bare(request.vars.email, request.vars.password)
>>>
>>> I have the passwords for the time being in plaintext, both in postgres
>>> and SQLite. I have imported the data to postgres like this:
>>>
>>> # PRAGMA and AUTOINCREMENT are not supported in postgres
>>> # sqlite_sequence is specific to sqlite
>>> sqlite3 storage.sqlite .dump | sed -e
>>> '/^PRAGMA/d;/sqlite_sequence/d;s/AUTOINCREMENT//' > /tmp/from_sqlite.sql
>>> sudo -u postgres PGPASSWORD=mypass psql -h localhost -U myuser -d
>>> web2py_wavportal < /tmp/from_sqlite.sql
>>>
>>> I have verified that the data in postgres looks fine:
>>>
>>> web2py_wavportal=# \dt
>>> List of relations
>>> Schema | Name | Type | Owner
>>> --------+-----------------+-------+-----------
>>> public | auth_cas | table | wavportal
>>> public | auth_event | table | wavportal
>>> public | auth_group | table | wavportal
>>> public | auth_membership | table | wavportal
>>> public | auth_permission | table | wavportal
>>> public | auth_user | table | wavportal
>>> public | fulluser | table | wavportal
>>> (7 rows)
>>>
>>> The credentials in auth_user are as expected, but auth.login_bare is
>>> failing. Why could that be?
>>>
>>> Thanks,
>>> Daniel
>>>
>>
--
