Blog / Postgresql

How to reset the primary key sequence in PostgreSQL with Django

March 19, 2019

I was working on some fairly big features involving new Django models for one of our apps and when I pushed to our staging environment, I got this error during deployment at the database migration step:

duplicate key value violates unique constraint "auth_permission_pkey" DETAIL: Key (id)=(55) already exists

It looks like new records were getting added to the auth_permission table. I was puzzled as I didn't make any changes related to the Django auth app. …

PostgreSQL Database Restore Commands

June 11, 2018

I've been finding myself doing restores from db backups to my local development more often recently as I've been building dashboard pages for a client and I want to have real data locally so I can get a better idea of what it will look like in production.

I also have a bunch of projects in production with real users and it's good to test-restore their backups at least once a month to make sure …

How to add full-text search to your Django app (with a PostgreSQL backend)

September 25, 2017

In Django 1.10, the django.contrib.postgres.search module was added to make it really easy to use PostgreSQL's full text search engine with a Django app.

I currently use it for this blog, Remote Python's job and developer search, and our knowledge base for Highview Apps. I'm really impressed with how good the search is and how little code is needed to get it working. Unless you have huge amounts of data/traffic and need really …

How to figure out your month-over-month revenue growth with an SQL query (PostgreSQL)

September 10, 2017

These last couple of days, I've been working on a dashboard for one of our Shopify apps so we can get a quick overview of how our app is doing revenue-wise.

One of the metrics we'd like to be able to see instantly is the month-over-month revenue growth rate. For a subscription-based SaaS (software as a service) product, this is a very important metric to track as it tells you if your business is growing …

Migrating a Django app from MySQL to PostgreSQL

November 6, 2014

We just finished migrating the database for our Django 1.6 app from MySQL to PostgreSQL. If you have a clean environment this process is as simple as running syncdb and/or migrate to create the tables, truncating the data in those tables, and running the dumpdata and loaddata management commands.

Here are the steps to do this:

Step 1: Create an empty database in your PostgreSQL instance

CREATE DATABASE dbname OWNER rolename;

Step 2: Create a …

Older