Tuesday 7 August 2012

Postgres from bash

So I do most of my database stuff in MySQL these days... but every now and then I'm forced to use PostgreSQL for the GIS functionality.

First thing to do when using pg from bash is to make your ".pgpass" file which is a one liner in this format:

hostname:port:database:username:password

and set the permissions:

echo "pgserver.example.com:*:db:user:YourPassord" > ~/.pgpass
chmod 0600 ~/.pgpass

Then these commands are at your finger tips:

psql -h pgserver.example.com -d db -U user
pgsql2shp -h pgserver.example.com -u user db $t1
pg_dump -h pgserver.example.com -U user -t $t1

1 comment:

  1. Defining these variables (in, for example, your .bashrc file) save remembering them:

    PGDATABASE
    PGHOST
    PGOPTIONS
    PGPORT
    PGUSER

    ReplyDelete