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