Databases contain the long-term memory of your app server. They can be tricky, but don't have to be scary! Out-of-the-box, Django gets you started with a SQLite database, which works great for local development and small web apps in production. However, when it's time to scale up your app, you may find yourself wondering whether it's time to move to something more sophisticated, such as AWS RDS.

How do you know when it's time to move to RDS? Well, for one thing, if your website is receiving more than 5-10 simultaneous visitors, your single EC2 machine may struggle to handle all the traffic. In that case, you should consider enabling EC2 auto-scaling, which allows AWS to automatically spin up and down additional EC2 instances to keep up with demand. But in order to enable EC2 auto-scaling, you'll need to have a centralized database for all the EC2 instances to talk to. When this happens, it's time to move to RDS.

The purpose of this blog post is to guide you through a migration from your SQLite database to a PostgreSQL database hosted in AWS RDS. If that sounds like what you need, read on!

Part 1: Launching an RDS Instance

1. Navigate to https://us-west-1.console.aws.amazon.com/rds/.
2. Scroll down to "Create database" and click "Create database."
3. Select:
   • Engine options: PostgreSQL
   • Master username: postgres
   • Templates: Free tier
   • DB instance identifier: my-database-name
   • Master username: postgres
   • Master password: some_secure_password
   • Connectivity: Don't connect to an EC2 compute resource
   • Public access: Yes
   • Database authentication: Password authentication
   • Additional configuration: Initial database name: tutorial_db
4. Select 'Create database.'
5. Once created, copy the endpoint hostname (ends in .us-west-1.rds.amazonaws.com) and port number from the console and store them as environment variables in your terminal:

   export RDS_NAME="my-database-name"
   export RDS_USER="postgres"
   export RDS_PASSWORD="some_secure_password"
   export RDS_HOST="<your hostname>"
   export RDS_PORT="5432"

Next, we need to edit the security rules for this database to allow incoming connections from our EC2 machine.

6. From the RDS console, select the security group corresponding to your RDS instance.
7. Select 'Edit inbound rules'.
8. Add a rule that allows connections of type PostgreSQL from the security group of your EC2 instance.
9. Add another rule that allows connections of type SS from the security group of your EC2 instance.
10. Select 'Save'

Part 2: Connecting the RDS Instance to EC2

Ideally, we want to continue testing our app locally using SQLite while allowing the production EC2 server to communicate with RDS. We can enable this in our settings.py file by replacing the default values for DATABASES with:

...

if DEBUG:
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': BASE_DIR / 'db.sqlite3',
        }
    }
else:
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.postgresql',
            'NAME': os.environ['RDS_NAME'],
            'USER': os.environ['RDS_USER'],
            'PASSWORD': os.environ['RDS_PASSWORD'],
            'HOST': os.environ['RDS_HOST'],
            'PORT': os.environ['RDS_PORT'],
        }
    }
...

This code reads values from our environment variables and uses them to connect to our RDS machine during production. Next, run:

pip install psycopg2 # or psycopg2-binary

We can test the connection is working by using:

python manage.py dbshell

If everything worked, we should get a PostgreSQL prompt allowing us to query the newly created SQL database. But hang on, there's no tables yet!

Part 3: Loading Tables and Data into RDS

Now that everything's connected, the final step is initializing our database with the tables it needs to get started. If we want to start fresh with an empty database, simply run:

python manage.py migrate

Otherwise, if we want to bring data over from our old SQLite database, run these commands:

1. For this step, temporarily point the terminal to the old SQLite database (e.g. by fudging the if statement we made in settings.py). Then, run:

   python manage.py dumpdata > data.json

2. Now, point the server back to the new RDS machine. Then, run:

   python manage.py migrate --run-syncdb # Creates all the tables without filling out the django_migrations table

3. We also need to manually delete some default data in our newly created RDS instance. Run:

   python manage.py shell
   > from django.contrib.conttentypes.models import ContentType
   > ContentType.objects.all().delete()

4. Finally, we can load the data from our data.json file into our new database. From the terminal, run:

   python manage.py loaddata data.json

To confirm our data has made it into the database, we can run:

python manage.py dbshell
> \dt

If all is well, we should get a list of all our tables. Feel free to query them using SQL statements to confirm all your data is there, such as: select * from django_migrations;

If everything looks good, you're done with your database migration! Congratulations, and happy coding.