Post SQLing

[Dave S]

I've got my AWS linux system ticking along nicely with my heavy table under PostGres [1], but I still have uploads, Auth, and Scheduler under sqlite.  I'd like to move them to PostGres, so I'm double checking that I'm using the appropriate steps to get there:

1. Create uploads table by copying the define_table() call from db (the database from the sqlite URI) to dby (the database from the PostGres URI)
e.g,

db.define_table('uploadf', ...)

gets copied to

dby.define_table('uploadf', ...)

For the table I already used, there was an issue with migration giving the correct resulting table files  until I changed the table name from "tname" to "tname_y" (the error message has been scarred over).  I hadn't seen this when making a backup sqlite database (called 'dbx'), I didn't need to name the backup "tname_x".  [2]
Should I expect to need this dodge in moving uploads?

2.  Do the usual export as CSV from the old table.

3.  Import the CSV in the new table.

(That's essentially the steps I used for the heavy table)

Auth and Scheduler have the define_tables() on the inside, so all I need to do is?

1.  Change the instantiation from

auth = Auth(db)
scheduler = Scheduler(db, migrate=False)

to

auth = Auth(dby)
scheduler = Scheduler(dby, migrate=True)

(and then migrate=False soon after)

2 and 3 as for uploads?

[1] Aside from my issues with switching from pg8000 to psycopg2.
[2]  Before bringing in PostGres, I tried adding a table to db for saving some test results longer term than uwsgi lets me keep log messages.  The "table already exists" errors kept piling up even after I turned off migrates, until I noticed the new table didn't have a UUID in its name.  I "fixed" this by copying the UUID from another table name and renaming the new .table file.  But I have another .table file with no UUID that works fine.  This is 2.15.4, though, so could be something fixed since then.

Thanks for the help.

/dps

[Dave S]

Hmmm, I didn't think this was an unanswerable question, but I seem to have gotten good at such postings lately.

-d

 

[Dave S]

On Tuesday, May 21, 2019 at 6:19:38 PM UTC-7, Dave S wrote:

I've got my AWS linux system ticking along nicely with my heavy table under PostGres [1], but I still have uploads, Auth, and Scheduler under sqlite.  I'd like to move them to PostGres, so I'm double checking that I'm using the appropriate steps to get there:

[...]

Auth and Scheduler have the define_tables() on the inside, so all I need to do is?

1.  Change the instantiation from

auth = Auth(db)
scheduler = Scheduler(db, migrate=False)

to

auth = Auth(dby)
scheduler = Scheduler(dby, migrate=True)

(and then migrate=False soon after)

I've now done this for the Scheduler of one application.  Early on, things look good, but I picked the app where a miss by the scheduler wouldn't be too painful.

It seems I had to remove the old scheduler tables, as well, to help with the migration.
 

Now I've got a new question:  If 2 apps are scheduling, and both are using Postgres, are they both using the same table? 
From psql, it doesn't look like there's anything in the table creation to isolate it from other applications (that is, \dS scheduler_task doesn't show anything to uniquify the table, but there is an "application_name" field, and then the select * for a sample task has app1/default in that field).

Will Auth also be shared?  The .schema auth_user doesn't have application_name.

 

[1] Aside from my issues with switching from pg8000 to psycopg2.

See other thread, but it seems to an issue of where the DAL looks for the driver, and I've gotten it wrong so far.

Thanks

Dave  S
/dps

[Dave S]

On Tuesday, August 13, 2019 at 2:46:31 AM UTC-7, Dave S wrote:

On Tuesday, May 21, 2019 at 6:19:38 PM UTC-7, Dave S wrote:

I've got my AWS linux system ticking along nicely with my heavy table under PostGres [1], but I still have uploads, Auth, and Scheduler under sqlite.  I'd like to move them to PostGres, so I'm double checking that I'm using the appropriate steps to get there:
[...]
Auth and Scheduler have the define_tables() on the inside, so all I need to do is?

1.  Change the instantiation from

auth = Auth(db)
scheduler = Scheduler(db, migrate=False)

to

auth = Auth(dby)
scheduler = Scheduler(dby, migrate=True)

(and then migrate=False soon after)

I've now done this for the Scheduler of one application.  Early on, things look good, but I picked the app where a miss by the scheduler wouldn't be too painful.

 
Oops, I've been having memory issues ... the postgres thread supporting the scheduler seems to grow ... up to 9.6% memory after a couple of hours, and so of course there are a couple times a day when the thread ... and the scheduler with it ... get killed.

[...]

[1] Aside from my issues with switching from pg8000 to psycopg2.

Is this a pg8000 symptom?

/dps
 

[Dave S]

I finally got things working with psycopg2
<URL:https://groups.google.com/d/msg/web2py/lDjyyWYxRt0/qZegtCUyBQAJ>
and am beginning a new round of memory watching.

/dps