Posted by: bjb

The ways that psql can be configured to connect to a different port:

  1. compiled in default
  2. PGPORT environment variable
  3. —port or -p option
  4. .pgpass setting

If you are running more than one version of PostgreSQL, you might wonder which one the psql client will talk to by default.

(DJANGO-1-3)bjb@spidy:~$ bash
bjb@spidy:~$ echo $PGPORT

bjb@spidy:~$ unset PGPORT
bjb@spidy:~$ ls -la ~/.pgpass
-rw------- 1 bjb bjb 0 May 20 17:33 /home/bjb/.pgpass
bjb@spidy:~$ locate psql | egrep bin
bjb@spidy:~$ /usr/lib/postgresql/8.4/bin/psql template1
psql (8.4.8)
Type "help" for help.

template1=> \echo :PORT
template1=> \q
bjb@spidy:~$ exit

Voila. The psql in /usr/bin is a perl script wrapper for the real psql. To find the “compiled-in” default port number, you can run the real psql without the command-line arg to change the port number, --port 5555 or -p 5555, and you also need to unset PGPORT (if it’s set). I have a .pgpass, but it’s empty so I didn’t have to do anything special for that. If you have a non-empty .pgpass, you might copy it aside before running psql if you want to try this test. Don’t forget to put it back when you’re done.

On my work machine, I had two versions of PostgreSQL running: 8.4 and 8.3. 8.3 was listening on 5432 and 8.4 was listening on 5433. psql was configured to go to port 5432 by default (and therefore PostgreSQL 8.3).

Categories: , ,
Posted by: bjb

I found this very helpful. It worked great with Django 1.3, PostgreSQL 8.3 in 2011/06:

And the penultimate step (filling in the primary key in existing rows) took around half a minute for almost 200,000 records on a not-particularly well-endowed laptop.


CREATE SEQUENCE rcvh7_id_seq;
UPDATE rcvh7 SET id = NEXTVAL('rcvh7_id_seq');
Posted by: bjb

If you want to do some unattended operations on your postgres database, and if you haven’t specified that the user who will do those unattended operations has access to that database using ident or sameuser authorization in /etc/postgresql/M.N/main/pg_hba.conf, then you will have to give a password upon invocation. But, postgres commands generally don’t let you specify a password on the command line (and there is a good reason for this).

There are two ways to configure your admin user to be able to work on your postgres database. One is with an environment variable and the other is with a postgres password config file in the admin user’s home directory.

The environment variable to set is PGPASSWORD, for example export PGPASSWORD=sekrit; pg_dump mydatabase.

The config file method means writing lines like hostname:port:database:username:password into a file called .pgpass in the admin user’s home directory. Don’t forget to set the permissions on ~/.pgpass to 0400, or -r--------.

The reason why postgres strongly discourages specifying the password on the command line is that it is easy for other users on the system to see that password with a simple invocation of the ps command.

Categories: ,
Posted by: bjb

To get rid of all the data in your database (but keep the tables):

./ flush

That is pretty much equivalent to:

sudo -u postgres dropdb appdb
sudo -u postgres createdb -O me appdb
./ syncdb

I even got asked the questions about creating the superuser after running the ./ flush command.

Categories: , ,
Posted by: bjb

I made a new django app. First I made a few models, then some unit tests for the models. I could not save newly created model instances in the database, the error was something about there not being an attribute id.

It turned out that although I inherited the models.Model class into each of my models, I was overriding the __init__ function. I should have called super from the derived class’s __init__ method … This is what the model class should have looked like:

from django.db import models

class MyModel (models.Model):

    name = CharField (max_length = 64)
    description = CharField (max_length = 512)

    def __unicode__ (self):

and if i was going to declare an __init__ method, it should have looked like this:

    def __init__(self, *args, **kwargs):
        super(MyClass, self).__init__(self, *args, **kwargs)
        # my own customization code goes here