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
>>
>
--
