but as soon as you change the dal uri with the same model the first request
will create the database tables. then you can import the data with a
csv......
On Friday, January 18, 2013 12:20:29 AM UTC+1, Daniel Gonzalez wrote:
>
> 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'), (selectmax
> (id) from auth_user) );
> select setval(pg_get_serial_sequence('fulluser' , 'id'), (selectmax
> (id) from fulluser) );
> select setval(pg_get_serial_sequence('auth_group' , 'id'), (selectmax
> (id) from auth_group) );
> select setval(pg_get_serial_sequence('auth_membership' , 'id'), (selectmax
> (id) from auth_membership) );
> select setval(pg_get_serial_sequence('auth_permission' , 'id'), (selectmax
> (id) from auth_permission) );
> select setval(pg_get_serial_sequence('auth_event' , 'id'), (selectmax
> (id) from auth_event) );
> select setval(pg_get_serial_sequence('auth_cas' , 'id'), (selectmax
> (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
>>>>
>>>
--
