PostgreSQL tips

Database backup and testing

1. Login to WebFaction’s Control Panel and go to the Databases section.
2. Click on the phpPgAdmin link.
3. Login to phpPgAdmin.
4. In the left pane of the phpPgAdmin window, expand Servers, expand PostgreSQL, and then click the name of the database that you want to export.
5. On the top menu bar, click “Export” (far right).
6. Under Format, click Structure and data.
7. Under Options, in the Format list box, select SQL.
8. Under Options, click Download.
9. Click “Export”.
10. On your local machine, start psql and open pgAdmin3.
11. In pgAdmin3, right-click on the database and select Delete/Drop…
12. Re-create the database by right-clicking on "Databases" icon and choosing "New Database…"; just input the name and and click “OK”.
13. Click on Plugins > PSQL Console.
14. From the command line, run:

\i /path/to/your-dumped-phpPgAdmin.sql

Tip: From the command line, you can type \i and then drag/drop the .sql file into the the terminal window and hit return key.

ALTERing existing table

To enlarge an existing column:

ALTER TABLE epd_incident ALTER COLUMN ofc TYPE varchar(200);

... where the ofc column had been a varchar(35), now it's 200.

Also:

ALTER TABLE epd_incident ALTER COLUMN priority DROP NOT NULL;

Also:

ALTER TABLE epd_incident ALTER COLUMN pd_id TYPE bigint;

To add columns to an existing table (this avoids having to do the dumpdata, make change(s), loaddata routine):

BEGIN;
ALTER TABLE turin_image 
    ADD "cms_picture_id" int,
    ADD "priority_id" int,
    ADD "fileheader_id" int;
COMMIT;

The above creates what adding

    cms_picture_id = models.IntegerField(null=True, blank=True)
    priority_id = models.IntegerField(null=True, blank=True)
    fileheader_id = models.IntegerField(null=True, blank=True)

to the model.py would do (and you have to add the above to the model.py to keep the ORM happy).

Two examples of adding a column to an existing table:

BEGIN;
ALTER TABLE turin_story
    ADD "freelance" boolean not null default false;
COMMIT;

This one is a URLField named image_url with default length of 200:

BEGIN;
ALTER TABLE lights_location ADD "image_url" varchar(200) not null default '';
COMMIT;

If your ALTER hangs, your table could be locked. To see what's locking it (where ballot_contest is the table in question):

SELECT * 
    FROM pg_locks 
    WHERE relation = (
                        SELECT oid 
                            FROM pg_class 
                            WHERE relname='ballot_contest'
);