Blog / Database

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

Migrating WordPress from a shared hosting environment to a virtual private server

November 9, 2011

I was migrating this WordPress blog early this morning from Bluehost to a dedicated Rackspace Cloud Server that I’m already using for a web application I’m writing. It was almost painless! Just ran into a few minor post-migration issues.

The reason I’m migrating is mainly because I want more control, like being able to use my own self-signed SSL certificate without paying extra, for example. I figured since I’m getting very ...

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

How to import data from a CSV file into a PostgreSQL database

April 19, 2011

I was working on my personal project last week and needed to import some data in CSV format to a PostgreSQL database. I wanted only certain fields to be imported so I had to specify this in the command. I’m new to PostgreSQL so it took me quite a bit of Googling to figure out exactly how to do this.

Here’s how I did it on Ubuntu 10.04 LTS, ‘glucosetracker’ is the ...

How to check the progress of the ‘Shrink Database’ task in SQL Server 2005

August 15, 2009

My first blog post in a long time!

So I archived some data from our database yesterday and I needed to shrink the database file so I ran the “Shrink Database” task in Management Studio. The database is over 500GB in size and I checked 7 hours later and the database shrink operation is still going. I got a little worried because someone needs to use this database today and I’m not sure if ...

Older