How to convert from SQLite to PostgreSQL

Roadmap

The easiest method for doing this shift contains two steps:

1. Installing PostgreSQL
2. Setting up username and password.

Usually, to make this switch, you only need to change the connection string, but there are some exceptions.

Installing Postgres

In Linux, you would use apt. In Mac, you can do this with Homebrew:

$ brew install postgresql@11
$ brew services start PostgreSQL

To remain compatible with Heroku, we will install Version 11 (rather than the newest, Version 12). The version is specified differently by different package managers. Also, we’ll start the server if it is not already started.

This runs Postgres from your own user account, which will have Postgres store its files in a Library subdirectory of your user account. This will be fine for our purposes. In Windows, you can run Chocolatey from a PowerShell, running as Administrator:

PS C:> choco install postgresql11
PS C:> choco services start postgresql

A warning that you did not specify a password will come up.

WARNING: You did not specify a password for the postgres user, so an insecure one has been generated for you. Please change it immediately.
WARNING: Generated password: 68e68124b68d449aae87b9416f5d1108

Setting up Postgres user and password

By default, Postgres on Mac, installed with Homebrew, will allow connections without a password. In Windows, the default is to set the password. You can turn that off by changing the file C:\Program Files\PostgreSQL\11\data\pg_hba.conf by replacing instances of md5 with trust. The connection string used later in the config file assumes this has been done.

It would be best if you considered changing the auto-generated password to something better. The password entered must match the generated password from the install warning. Here, we change it to dbpass:

PS C:> psql -U postgres
Password: 68e68124b68d449aae87b9416f5d1108
psql (11.6)
Type "help" for help.

postgres=# alter user postgres with password 'dbpass';
ALTER ROLE
postgres=# \q
PS C:>

In Windows, additionally, you will need to create a Postgres user account, which must match your Windows username. If the username on the machine is socra, the declaration would be as follows:

PS C:> createuser -U postgres --interactive --pwprompt
Enter the name of the role to add: socra
Enter password for new role: <enter password>
Enter it again: <repeat password>
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
PS C:>

At this point, you should be able to create a database using createdb from the command line.

$ createdb books 

Using PostgreSQL in Python

Whereas SQLite is built into Python, Postgres requires an additional package.

$ pipenv install psycopg2-binary

To run all of the same code, we only need to replace the connection string for SQLite with the connection string for PostgresSQL, connecting to the books database. My new connection string is:

connection_string = 'postgresql://{user}:{pswd}@localhost:5432/books'

Where {user} and {pswd} is your database username and password, respectively. If you’re on a Mac or Linux machine, or if you’ve followed the steps above for Windows, then there should be no username or password required when you run on the same machine the database server is running on. As a result, the connection string becomes:

connection_string = 'postgresql://localhost:5432/books'

The /books at the end is the name of the database.

By replacing the SQLite connection string with the Postgres connection string, you can repeat all the examples in this chapter using a Postgres server rather than an SQLite server. The main added complication is making sure the Postgres server is running.