Filters in pgAdmin

I use PostgreSQL in most of my Django projects. PostgreSQL has a tool called pgAdmin that is invaluable for debugging. pgAdmin allows the user to manually enter filters. The problem is the filter does not naturally handle column names that are capitalized, while Django often creates column names that are capitalized. What to do?

The solution is to put the column name in double quotes:

Job='xyz'    - is Not valid

"Job"='xyz'   - is valid

 

Copying Postgresql to Another Server on Webfaction

Here is how I copied a postgresql database from one Webfaction server to another. My original thought was to do this using Fabric. But on Webfaction, they do not recommend creating or dropping databases from the command line. Instead they recommend using the Webfaction Control Panel.

There are posts on Stackoverflow for copying a database without writing it to an intermediate file. I chose to not do that. The command for dumping the database was:

pg_dump -C -U username database_name > output_file

Next, I copied the file to the other server. After that, I created the new database on the new server using the Webfaction Control Panel. I named the new database the same name as the old. I am not sure if this is necessary. I did not run the Django command syncdb.

Then I ran:

psql -U username -d database_name -f file_name

Fabric

Getting it to work from Fabric was a little tricky because you need to get Fabric to talk to two different servers. The trick involved using the Fabric hosts decorator:

@hosts('webxxx.webfaction.com')
def copy_from():
    your code here

Then run that function using “execute”:

def copy_db():
    r = execute(copy_from)    # execute returns a dict
    return_value = r['webxxx.webfaction.com']
    your code here