Blog / Postgresql

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 ...

A simple Python script for backing up a PostgreSQL database and uploading it to Amazon S3

January 10, 2014

Here’s a very simple Python script I currently use to create a compressed PostgreSQL database backup for my Django app. Since my database is very small and I don’t see it becoming big anytime soon, I create the backup locally and send a copy to Amazon S3.

To keep it very simple, I have it set to do hourly backups for 24 hours and daily backups for 1 year (365 days). For the ...

Error Message: “Peer authentication failed for user ‘username’” when connecting to a PostgreSQL database from a Django application

December 4, 2012

Error Message:

psycopg2.OperationalError: FATAL: Peer authentication failed for user “username”

I was setting up a friend’s project on my Ubuntu 12.04 laptop last night and ran into this database connection problem when I tried to ran his Django application.

There are actually 2 solutions to this problem:

Solution 1:

In your settings.py file, in the database settings, set the ‘HOST’ to ‘localhost’ instead of an empty string.

Solution 2:

Modify the ...

How to allow remote connections to your PostgreSQL 9.0 database server on Ubuntu 10.04 LTS

September 8, 2011

Change directory to /etc/postgresql/9.0/main and modify the following configs (I know you’re sick of hearing this, but I recommend you back up your original configs before making any changes):

postgresql.conf

In “Connections and Authentication” section:

From

#listen_addresses = 'localhost'
#password_encryption = on

To

listen_addresses = '*'
password_encryption = on

pg_hba.conf

From

host    all    all    127.0.0.1/32    md5
host    all    all    ::1/128         md5

To

host    all    all    0.0.0 ...