Shallow Thoughts : : Feb

Automatic Plant Watering with a Raspberry Pi

An automatic plant watering system is a project that's been on my back burner for years. I'd like to be able to go on vacation and not worry about whatever houseplant I'm fruitlessly nursing at the moment. (I have the opposite of a green thumb -- I have very little luck growing plants -- but I keep trying, and if nothing else, I can make sure lack of watering isn't the problem.)

I've had all the parts sitting around for quite some time, and had tried them all individually, but never seemed to make the time to put them all together. Today's "Raspberry Pi Jam" at Los Alamos Makers seemed like the ideal excuse.

Sensing Soil Moisture

First step: the moisture sensor. I used a little moisture sensor that I found on eBay. It says "YL-38" on it. It has the typical forked thingie you stick into the soil, connected to a little sensor board.

The board has four pins: power, ground, analog and digital outputs. The digital output would be the easiest: there's a potentiometer on the board that you can turn to adjust sensitivity, then you can read the digital output pin directly from the Raspberry Pi.

But I had bigger plans: in addition to watering, I wanted to keep track of how fast the soil dries out, and update a web page so that I could check my plant's status from anywhere. For that, I needed to read the analog pin.

Raspberry Pis don't have a way to read an analog input. (An Arduino would have made this easier, but then reporting to a web page would have been much more difficult.) So I used an ADS1115 16-bit I²sup>C Analog to Digital Converter board from Adafruit, along with Adafruit's ADS1x15 library. It's written for CircuitPython, but it works fine in normal Python on Raspbian.

It's simple to use. Wire power, ground, SDA and SDC to the appropriate Raspberry Pi pins (1, 6, 3 and 5 respectively). Connect the soil sensor's analog output pin with A0 on the ADC. Then

# Initialize the ADC
i2c = busio.I2C(board.SCL, board.SDA)
ads = ADS.ADS1015(i2c)
adc0 = AnalogIn(ads, ADS.P0)

# Read a value
value = adc0.value
voltage = adc0.voltage

With the probe stuck into dry soil, it read around 26,500 value, 3.3 volts. Damp soil was more like 14528, 1.816V. Suspended in water, it was more like 11,000 value, 1.3V.

Driving a Water Pump

The pump also came from eBay. They're under $5; search for terms like "Mini Submersible Water Pump 5V to 12V DC Aquarium Fountain Pump Micro Pump". As far as driving it is concerned, treat it as a motor. Which means you can't drive it directly from a Raspberry Pi pin: they don't generate enough current to run a motor, and you risk damaging the Pi with back-EMF when the motor stops.

Instead, my go-to motor driver for small microcontroller projects is a SN754410 SN754410 H-bridge chip. I've used them before for driving little cars with a Raspberry Pi or with an Arduino. In this case the wiring would be much simpler, because there's only one motor and I only need to drive it in one direction. That means I could hardwire the two motor direction pins, and the only pin I needed to control from the Pi was the PWM motor speed pin. The chip also needs a bunch of ground wires (which it uses as heat sinks), a line to logic voltage (the Pi's 3.3V pin) and motor voltage (since it's such a tiny motor, I'm driving it from the Pi's 5v power pin).

Here's the full wiring diagram.

Driving a single PWM pin is a lot simpler than the dual bidirectional motor controllers I've used in other motor projects.

GPIO.setmode(GPIO.BCM)
GPIO.setup(23, GPIO.OUT)
pump = GPIO.PWM(PUMP_PIN, 50)
pump.start(0)

# Run the motor at 30% for 2 seconds, then stop.
pump.ChangeDutyCycle(30)
time.sleep(2)
pump.ChangeDutyCycle(0)

The rest was just putting together some logic: check the sensor, and if it's too dry, pump some water -- but only a little, then wait a while for the water to soak in -- and repeat. Here's the full plantwater.py script. I haven't added the website part yet, but the basic plant waterer is ready to use -- and ready to demo at tonight's Raspberry Pi Jam.

Tags: raspberry pi, programming, python
[ 13:50 Feb 27, 2020    More hardware | permalink to this entry | ]

A Couple of Nice Hikes

We've had some wild weather recently. Two weeks ago, our weekly hiking group was sscheduled to go on a hike in sunny White Rock that Dave and I had proposed, a few miles from home. Then the night before the hike, we got our heaviest snowstorm of the year so far.

Sounded like a great opportunity to test those new ice spikes (for shoes) I'd ordered on eBay. We went down Lion Cave Canyon, around the mesa and up Water Canyon, then climbed up to the top of the mesa and went out to the end to a lunch spot with a panoramic view of Water Canyon and the Sangre de Cristos.

Then back across the narrow neck of the mesa. The temperature was just about perfect for hiking with the sun and the snow. The ice spikes worked perfectly -- the snow wasn't deep enough to need snowshoes, but there were plenty of places where it would have been slippery without the spikes.

We also had fun speculating on the cause of the "snow bumps" that formed around the grama grass stems.

Now, two weeks later, most of the snow is gone and it's a beautiful day with a high of 60. We headed out for a short exploration in Pueblo Canyon, looking for the old airport that some folks in the R/C flying club thought might make a good flying site.

Some clouds moved in while we were walking, making for dramatic views of the cliffs. I just never get tired of the way the changing light plays on the mesas and canyons.

We didn't find the old airport -- more exploration needed! -- but we did find the new connector to the Tent Rocks Trail, where the Youth Conservation Corps has been busy with trailwork in Pueblo Canyon. And we explored the remains of an old road -- below Anderson Overlook: possibly the original horse/mule road that they used in the Ranch School days before the Manhattan Project.

Another beautiful day in Los Alamos.

Tags: nature, hiking
[ 19:58 Feb 17, 2020    More nature | permalink to this entry | ]

A Raspberry Pi Kiosk

After writing a simple kiosk of rotating quotes and images, I wanted to set up a Raspberry Pi to run the kiosk automatically, without needing a keyboard or any on-site configuration.

The Raspbian Desktop: Too Hard to Configure

Unlike my usual Raspberry Pi hacks, the kiosk would need a monitor and a window system. So instead of my usual Raspbian Lite install, I opted for a full Raspbian desktop image.

Mistake. First, the Raspbian desktop is very slow. I intended to use a Pi Zero W for the kiosk, but even on a Pi 3 the desktop was sluggish.

More important, the desktop is difficult to configure. For instance, a kiosk needs to keep the screen on, so I needed to disable the automatic screen blanking. There are threads all over the web asking how to disable screen blanking, with lots of solutions that no longer apply because Raspbian keeps changing where desktop configuration files are stored.

Incredibly, the official Raspbian answer for how to disable screen blanking in the desktop — I can hardly type, I'm laughing so hard — is: install xscreensaver, which will then add a configuration option to turn off the screensaver. (I actually tried that just to see if it would work, but changed my mind when I saw the long list of dependencies xscreensaver was going to pull in.)

I never did find a way to disable screen blanking, and after a few hours of fighting with it, I decided it wasn't worth it. Setting up Raspbian Lite is so much easier and I already knew how to do it. If I didn't, Die Antwort has a nice guide, Setup a Raspberry Pi to run a Web Browser in Kiosk Mode, that uses my preferred window manager, Openbox. Here are my steps, starting with a freshly burned Raspbian Lite SD card.

Set Up Raspbian Lite with Network and SSH

I wanted to use ssh on my home network while debugging, even though the final kiosk won't need a network. The easiest way to do that is to mount the first partition:

sudo mount /dev/sdX1 /mnt

(sdX is wherever the card shows up on your machine, e.g. sdB) and create two files. First, an empty file named ssh

touch /mnt/ssh

Second, create a file named wpa_supplicant.conf with the settings for your local network:

ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev
update_config=1

network={
	ssid="MY-WIFI-SSID"
	psk="MY-WIFI-PASSWORD"
	priority=10
}

Then unmount that partition:

sudo umount /mnt

Copy the Kiosk Files into /home/pi

The second partition on a Raspbian card is the root filesystem, including /home/pi, the pi user's home dictory. Mount /dev/sdX2, copy your kiosk code into /home/pi, and chown the code to the pi user. If you don't know what that means or how to do that, you can skip this step and load the code onto the Pi later once it's up and running, over the network or via a USB stick.

Unmount the SD card and move it to the Raspberry Pi.

Raspbian First-boot Configuration

Boot the Pi with a monitor attached, log in as the pi user, run sudo raspi-config, and:

• set the locale and keyboard,
• change the password for user Pi,
• in Boot Options, choose “Desktop / CLI” and “Console Autologin” so the pi user will be logged in automatically.

So the installation won't become too bloated, I like to create the file /etc/apt/apt.conf containing:

APT::Install-Recommends "false";
APT::Install-Suggests "false";

(That's the equivalent of the --no-install-recommends in the Die Antwort guide.)

Update the OS, and install the packages needed to run X, the Openbox window manager, a terminal (I used xterm), and a text editor (I used vim; if you're not familiar with Linux text editors, pico is more beginner-friendly). If you're in a hurry, you can skip the update and dist-upgrade steps.

$ sudo apt update
$ sudo apt dist-upgrade
$ sudo apt install xserver-xorg x11-xserver-utils xinit openbox xterm vim

I was surprised how little time this took: even with all of the X dependencies, the whole thing took less than twenty minutes, compared to the several hours it had taken to dist-upgrade all the packages on the full Raspbian desktop.

Install any Kiosk-specific Packages

Install any packages you need to run your kiosk. My kiosk was based on Python 3 and GTK 3:

sudo apt install python3-cairo python3-gi python3-gi-cairo \
         libgirepository-1.0-1 gir1.2-glib-2.0 python3-html2text

(This also pulled in gir1.2-atk-1.0, gir1.2-freedesktop, gir1.2-gdkpixbuf-2.0, gir1.2-pango-1.0, and gir1.2-gtk-3.0, but I don't think I had to specify any of them explicitly.)

Configure Openbox

Create the Openbox configuration directory:

mkdir -p .config/openbox

Create .config/openbox/autostart containing:

# Disable screen saver/screen blanking/power management
xset s off
xset s noblank
xset -dpms

# Start a terminal
xterm &

Save the file, and test to make sure you can run X:

$ startx

You should see a black screen, a mouse pointer, and after a few seconds, a small xterm window in the center of the screen. You can use the xterm to fiddle with things you want to change, or you can right-click anywhere outside the xterm window to get a menu that will let you exit X and go back to the bare console.

Test Your Kiosk

With X running, you can run your kiosk command. Don't change directories first; the pi user will be /home/pi ($HOME) after automatically logging in, so make sure you can run from there. For instance, I can run my kiosk with:

$HOME/src/scripts/quotekiosk.py $HOME/100-min-kiosk/slideshow/* $HOME/100-min-kiosk/quotes/*.html

Once the command works, edit .config/openbox/autostart and add your command at the end, after the xterm line, with an ampersand (&) after it. Keep the xterm line in place so you'll have a way to recover if things go wrong.

Configure X to Start When the Pi User Logs In

You've already set up the Pi user to be logged in automatically when the machine boots, but pi needs to start X upon login. Create the file .bash_profile containing:

[[ -z $DISPLAY && $XDG_VTNR -eq 1 ]] && startx

You should be ready to go. Reboot, and the Pi should boot up in kiosk mode.

Run in a Loop

Everything working? For extra security, you might want to tweak the autostart file to run your kiosk in a loop. That way, even if the kiosk code crashes for some reason, it will be restarted.

while :
do
    $HOME/src/scripts/quotekiosk.py $HOME/100-min-kiosk/slideshow/* $HOME/100-min-kiosk/quotes/*.html
done

Don't do this until after you've tested everything else; it's hard to debug with the kiosk constantly popping up on top of other windows.

Get Rid of that Pesky Cursor

You might also want to remove that annoying mouse pointer arrow in the middle of the screen. Editing that startx line you just added to .bash_profile:

[[ -z $DISPLAY && $XDG_VTNR -eq 1 ]] && startx -- -nocursor

This step comes last — because once you've disabled the cursor, it will be difficult to use the machine interactively since you won't be able to see where your mouse is. (If you need to make changes later, you can ssh in from another machine, mount the Raspbian SD card on another machine, or use Ctrl-Alt-F2 to switch to a console window where you can edit files.)

... But It's Still Not Quite Hands-Off

The Pi is now set up to work automatically: just plug it in. The problem was the monitor. Someone contributed a TV, but it turned out to be a "smart TV", and it had its own ideas about what it would connect to. Sometimes the HDMI ports worked, sometimes it refused to display anything, and even when it worked, it randomly brightened and dimmed so that the screen was often too dim to see.

So I contributed my old 20" monitor. Everything worked fine at the demo the night before, and I handed it off to the people who were going to be there early for setup. When I arrived at the Roundhouse the next day, there was my monitor, displaying "No Signal". Apparently, while setting it up, someone had bumped the monitor's "Input Source" button; and of course no one there was up to the task of diagnosing that difficult problem. And no one bothered to call me and ask.

Once I arrived, I pressed the Source button a couple of times and the kiosk display was up and running for the rest of the day. Sigh. I can write kiosk software and set up Raspberry Pis; but predicting potential issues non-technical users might encounter is still beyond me.

Tags: raspberry pi
[ 11:08 Feb 12, 2020    More tech | permalink to this entry | ]

Displaying Quotes on a Kiosk -- and Javascript Memory Leaks

The LWV had a 100th anniversary celebration earlier this week. In New Mexico, that included a big celebration at the Roundhouse. One of our members has collected a series of fun facts that she calls "100-Year Minutes". You can see them at lwvnm.org. She asked me if it would be possible to have them displayed somehow during our display at the Roundhouse.

Of course! I said. "Easy, no problem!" I said.

Famous last words.

There are two parts: first, display randomly (or sequentially) chosen quotes with large text in a fullscreen window. Second, set up a computer (the obvious choice is a Raspberry Pi) run the kiosk automatically. This article only covers the first part; I'll write about the Raspberry Pi setup separately.

A Simple Plaintext Kiosk Python Script

When I said "easy" and "no problem", I was imagining writing a little Python program: get text, scale it to the screen, loop. I figured the only hard part would be the scaling. the quotes aren't all the same length, but I want them to be easy to read, so I wanted each quote displayed in the largest font that would let the quote fill the screen.

Indeed, for plaintext it was easy. Using GTK3 in Python, first you set up a PangoCairo layout (Cairo is the way you draw in GTK3, Pango is the font/text rendering library, and a layout is Pango's term for a bunch of text to be rendered). Start with a really big font size, ask PangoCairo how large the layout would render, and if it's so big that it doesn't fit in the available space, reduce the font size and try again. It's not super elegant, but it's easy and it's fast enough. It only took an hour or two for a working script, which you can see at quotekiosk.py.

But some of the quotes had minor HTML formatting. GtkWebkit was orphaned several years ago and was never available for Python 3; the only Python 3 option I know of for displaying HTML is Qt5's QtWebEngine, which is essentially a fully functioning browser window.

Which meant that it seeming made more sense to write the whole kiosk as a web page, with the resizing code in JavaScript. I say "seemingly"; it didn't turn out that way.

JavaScript: Resizing Text to Fit Available Space

The hard part about using JavaScript was the text resizing, since I couldn't use my PangoCairo resizing code.

Much web searching found lots of solutions that resize a single line to fit the width of the screen, plus a lot of hand-waving suggestions that didn't work. I finally found a working solution in a StackOverflow thread: Fit text perfectly inside a div (height and width) without affecting the size of the div. The only one of the three solutions there that actually worked was the jQuery one. It basically does the same thing my original Python script did: check element.scrollHeight and if it overflows, reduce the font size and try again.

I used the jquery version for a little while, but eventually rewrote it to pure javascript so I wouldn't have to keep copying jquery-min.js around.

JS Timers on Slow Machines

There are two types of timers in Javascript: setTimeout, which schedules something to run once N seconds from now, and setInterval, which schedules something to run repeatedly every N seconds. At first I thought I wanted setInterval, since I want the kiosk to keep running, changing its quote every so often.

I coded that, and it worked okay on my laptop, but failed miserably on the Raspberry Pi Zero W. The Pi, even with a lightweight browser like gpreso (let alone chromium), takes so long to load a page and go through the resize-and-check-height loop that by the time it has finally displayed, it's about ready for the timer to fire again. And because it takes longer to scale a big quote than a small one, the longest quotes give you the shortest time to read them.

So I switched to setTimeout instead. Choose a quote (since JavaScript makes it hard to read local files, I used Python to read all the quotes in, turn them into a JSON list and write them out to a file that I included in my JavaScript code), set the text color to the background color so you can't see all the hacky resizing, run the resize loop, set the color back to the foreground color, and only then call setTimeout again:

function newquote() {
    // ... resizing and other slow stuff here

    setTimeout(newquote, 30000);
}

// Display the first page:
newquote();

That worked much better on the Raspberry Pi Zero W, so I added code to resize images in a similar fashion, and added some fancy CSS fade effects that it turned out the Pi was too slow to run, but it looks nice on a modern x86 machine. The full working kiosk code is quotekioska>).

Memory Leaks in JavaScript's innerHTML

I ran it for several hours on my development machine and it looked great. But when I copied it to the Pi, even after I turned off the fades (which looked jerky and terrible on the slow processor), it only ran for ten or fifteen minutes, then crashed. Every time. I tried it in several browsers, but they all crashed after running a while.

The obvious culprit, since it ran fine for a while then crashed, was a memory leak. The next step was to make a minimal test case.

I'm using innerHTML to change the kiosk content, because it's the only way I know of to parse and insert a snippet of HTML that may or may not contain paragraphs and other nodes. This little test page was enough to show the effect:

<h1>innerHTML Leak</h1>

<p id="thecontent">
</p>

<script type="text/javascript">
var i = 0;
function changeContent() {
    var s = "Now we're at number " + i;
    document.getElementById("thecontent").innerHTML = s;
    i += 1;

    setTimeout(changeContent, 2000);
}

changeContent();
</script>

Chromium has a nice performance recording tool that can show you memory leaks. (Firefox doesn't seem to have an equivalent, alas.)

To test a leak, go to More Tools > Developer Tools and choose the Performance tab. Load your test page, then click the Record button. Run it for a while, like a couple of minutes, then stop it and you'll see a graph like this (click on the image for a full-size version).

Both the green line, Nodes, and the blue line, JS Heap, are going up. But if you run it for longer, say, ten minutes, the garbage collector eventually runs and the JS Heap line drops back down. The Nodes line never does: the node count just continues going up and up and up no matter how long you run it.

So it looks like that's the culprit: setting innerHTML adds a new node (or several) each time you call it, and those nodes are never garbage collected. No wonder it couldn't run for long on the poor Raspberry Pi Zero with 512Gb RAM (the Pi 3 with 1Gb didn't fare much better).

It's weird that all browsers would have the same memory leak; maybe something about the definition of innerHTML causes it. I'm not enough of a Javascript expert to know, and the experts I was able to find didn't seem to know anything about either why it happened, or how to work around it.

Python html2text

So I gave up on JavaScript and went back to my original Python text kiosk program. After reading in an HTML snippet, I used the Python html2text module to convert the snippet to text, then displayed it. I added image resizing using GdkPixbuf and I was good to go.

quotekiosk.py ran just fine throughout the centennial party, and no one complained about the formatting not being fancy enough. A happy ending, complete with cake and lemonade. But I'm still curious about that JavaScript leak, and whether there's a way to work around it. Anybody know?

Tags: programming, raspberry pi, python, javascript
[ 18:48 Feb 08, 2020    More tech/web | permalink to this entry | ]

Migrate a sqlite3 Flask App to Postgresql

The New Mexico legislature is in session again, which means the New Mexico Bill Tracker I wrote last year is back in season. But I guess the word has gotten out, because this year, I started seeing a few database errors. Specifically, "sqlite3.OperationalError: database is locked".

It turns out that even read queries on an sqlite3 database inside flask and sqlalchemy can sometimes keep the database open indefinitely. Consider something like:

    userbills = user.get_bills()    # this does a read query

    # Do some slow operations that don't involve the database at all
    for bill in userbills:
        slow_update_involving_web_scraping(bill)

    # Now bills are all updated; add and commit them.
    # Here's where the write operations start.
    for bill in userbills:
        db.session.add(bill)
    db.session.commit()

I knew better than to open a write query that might keep the database open during all those long running operations. But apparently, when using sqlite3, even the initial query of the database to get the user's bill list opens the database and keeps it open ... until when? Can you close it manually, then reopen it when you're ready? Does it help to call db.session.commit() after the read query? No one seems to know, and it's not obvious how to test to find out.

I've suspected for a long time that sqlite was only a temporary solution. While developing the billtracker, I hit quite a few difficulties where the answer turned out to be "well, this would be easy in a real database, but sqlite doesn't support that". I figured I'd eventually migrate to postgresql. But I'm such a database newbie that I'd been putting it off.

And rightly so. It turns out that migrating an existing database from sqlite3 to postgresql isn't something that gets written about much; I really couldn't find any guides on it. Apparently everybody but me just chooses the right database to begin with? Anyway, here are the steps on Debian. Obviously, install postgresql first.

Create a User and a Database

Postgresql has its own notion of users, which you need to create. At least on Debian, the default is that if you create a postgres user named martha, then the Linux user martha on the same machine can access databases that the postgres user martha has access to. This is controlled by the "peer" auth method, which you can read about in the postgresql documentation on pg_hba.conf.

First su to the postgres Linux user and run psql:

$ sudo su - postgres
$ psql

Inside psql, create a postgresql user with the same name as your flask user, and create a database for that user:

CREATE USER myflaskuser WITH PASSWORD 'password';
ALTER ROLE myflaskuser SET client_encoding TO 'utf8';
ALTER ROLE myflaskuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE myflaskuser SET timezone TO 'UTC';

CREATE DATABASE dbname;
GRANT ALL PRIVILEGES ON DATABASE dbname TO myflaskuser;

If you like, you can also create a user yourusername and give it access to the same database, to make debugging easier.

With the database created, the next step is to migrate the old data from the sqlite database.

pgloader (if you have a very recent pgloader)

Using sqlalchemy in my flask app meant that I could use flask db upgrade to create the database schema in any database I chose. It does a lovely job of creating an empty database. Unfortunately, that's no help if you already have an existing database full of user accounts.

Some people suggested exporting data in either SQL or CSV format, then importing it into postgresql. Bad idea. There are many incompatibilities between the two databases: identifiers that work in sqlite but not in postgresql (like "user", which is a reserved word in postgres but a common table name in flask-based apps), capitalization of column names, incompatible date formats, and probably many more.

A program called pgloader takes care of many (but not all) of the incompatibilities. Create a file -- I'll call it migrate.pgloader -- like this:

load database
    from 'latest-sqlite-file.db'
    into postgresql:///new_db_name

with include drop, quote identifiers, create tables, create indexes, reset sequences

set work_mem to '16MB', maintenance_work_mem to '512 MB';

Then, from a Linux user who has access to the database (e.g. the myflaskuser you created earlier), run pgloader migrate.pgloader.

That worked nicely on my Ubuntu 19.10 desktop, which has pgloader 3.6.1. It failed utterly on the server, which is running Debian stable and pgloader 3.3.2. Building the latest pgloader from source didn't work on Debian either; it's based on Common Lisp, and the older CL on Debian dumped me into some kind of assembly debugger when I tried to build pgloader. Rather than build CL from source too, I looked for another option.

On an Older OS: Use pgloader Remotely

Postgresql can take commands from remote machines. So you can configure postgresql to accept remote connections, then run the migration from a machine with a new enough pgloader version.

There are two files to edit. The location of postgresql's configuration directory varies with version, so do a locate pg_hba.conf to find it. In that directory, first edit pg_hba.conf and add these lines to the end to allow net socket connections from IP4 and IP6:

host  all     all   0.0.0.0/0     md5
host  all     all   ::/0          md5

In the same directory, edit postgresql.conf and search for listen_addr. Comment out the localhost line if it's uncommented, and add this to allow connections from anywhere, not just localhost:

listen_addresses = '*'

Then restart the database with

service postgresql restart

Modify the migrate.pgloader file from the previous section so the "into" line looks like

    into postgresql://username:password@host/dbname

The username there is the postgres username, if you made that different from the Unix username. You need to use a password because postgres is no longer using peer auth (see that postgres documentation file I linked earlier).

Assuming this You're done with the remote connection part. If you don't need remote database connections for your app, you can now edit postgresql.conf, comment out that listen_addresses = '*' line, and restart the database again with service postgresql restart. Don't remove the two lines you added in pg_hba.conf; flask apparently needs them.

You're ready for the migration. Make sure you have the latest copy of the server's sqlite database, then, from your desktop, run:

pgloader migrate.pgloader

Migrate Autoincrements to Sequences

But that's not enough. If you're using any integer primary keys that autoincrement -- a pretty common database model -- postgresql doesn't understand that. Instead, it has sequence objects. You need to define a sequence, tie it to a table, and tell postgresql that when it adds a new object to the table, the default value of id is the maximum number in the corresponding sequence. Here's how to do that for the table named "user":

CREATE SEQUENCE user_id_seq OWNED by "user".id;
ALTER TABLE "user" ALTER COLUMN id SET default nextval('user_id_seq');
SELECT setval(pg_get_serial_sequence('user', 'id'), coalesce(max(id)+1,1), false) FROM "user";

Note the quotes around "user" because otherwise user is a postgresql reserved word. Repeat these three lines for every table in your database, except that you don't need the quotes around any table name except user.

Incidentally, I've been told that using autoincrement/sequence primary keys isn't best practice, because it can be a bottleneck if lots of different objects are being created at once. I used it because all the models I was following when I started with flask worked that way, but eventually I plan try to switch to using some other unique primary key.

Update: Turns out there was another problem with the sequences, and it was pretty annoying. I ended up with a bunch of indices with names like "idx_15517_ix_user_email" when they should have been "ix_user_email". The database superficially worked fine, but it havoc ensues if you ever need to do a flask/sqlalchemy/alembic migration, since sqlalchemy doesn't know anything about those indices with the funny numeric names. It's apparently possible to rename indices in postgresql, but it's a tricky operation that has to be done by hand for each index.

Now the database should be ready to test.

Test

Your flask app probably has something like this in config.py:

    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'sqlite:///' + os.path.join(basedir, 'dbname.db')

If so, you can export DATABSE_URL=postgresql:///dbname and then test it as you usually would. If you normally test on a local machine and not on the server, remember you can tell flask's test server to accept connections from remote machines with flask run --host=0.0.0.0

Database Backups

You're backing up your database, right? That's easier in sqlite where you can just copy the db file.

From the command line, you can back up a postgresql database with: pg_dump dbname > dbname-backup.pg You can do that from Python in a subprocess:

    with open(backup_file, 'w') as fp:
        subprocess.call(["pg_dump", dbname], stdout=fp)

Verify You're Using The New Database

I had some problems with that DATABASE_URL setting; I'd never used it so I didn't realize that it wasn't in the right place and didn't actually work. So I ran through my migration steps, changed DATABASE_URL, thought I was done, and realized later that the app was still running off sqlite3.

It's better to know for sure what your app is running. For instance, you can add a route to routes.py that prints details like that.

You can print app.config["SQLALCHEMY_DATABASE_URI"]. That's enough in theory, but I wanted to know for sure. Turns out str(db.session.get_bind()) will print the connection the flask app's database is actually using. So I added a route that prints both, plus some other information about the running app.

Whew! I was a bit surprised that migrating was as tricky as it was, and that there wasn't more documentation for it. Happy migrations, everyone.

Tags: programming, flask, python, database, sqlite, postgresql
[ 12:34 Feb 01, 2020    More tech/web | permalink to this entry | ]