Initial PostgreSQL Setup (Ubuntu 12.04)

  • I change the access control porton of the /etc/postgresql/9.1/main/pg_hba.conf to the following:

    # Database administrative login by Unix domain socket
    local   all             postgres                                peer
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    # "local" is for Unix domain socket connections only
    #local   all             all                                     peer
    # IPv4 local connections:
    #host    all             all               md5
    # IPv6 local connections:
    #host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres            md5
    #host    replication     postgres        ::1/128                 md5
    local  all        all       md5
    host   all        all md5h

  • and then restart the postgres service

    service postgresql restart

Create User and Database

  • Using Shell
    • This creates a user called "appdba" and a database called "appdb".
    • The suggested password to use is "appdba11".
    • Execute the following (as user root):

      sudo su - postgres -c "createuser -s -d -P appdba"
      sudo su - postgres -c "createdb -O appdba appdb"
  • Using PSQL:
      • Run psql as postgres user:

        sudo su - postgres -c psql 
      • Create user:

        create user awsdba with password 'mypassword';
      • Change user password:

        alter user awsdba with password 'newpassword';
      • Create database:

        create database awsdb with owner=awsdba;

psql Pager

  • I find setting the pager with the following settings helps a lot:
    • in ~/.bashrc, append

      # .sha
      export PATH=$PATH:~/.bin:/usr/local/bin
      export PAGER=less
      export LESS="-iMSx4 -FX"
    • in ~/.psqlrc:

      # \pset pager always
  • I commented the last line as I'd rather the pager only be used when the output overflows the screen

Database Migration

Originally posted here:

Assuming you have database on hostA and want to copy the entire schema over, but only the data for certain tables:

  • Create the database on hostB

    sudo su - postgres "createuser --pwprompt --superuser --createdb newdba -h hostB"
    createdb -U newdba newdb kj dddddeteletethis
  • Migrate the entire schema (without data) across:

    pg_dump -C -s -U olddba -h hostA olddb | psql -U newdba newdb
  • Migrate data in certain tables (i.e. NOT climate* and forecast* tables) only:

    pg_dump -c -Tforecast\* -Tclimate\* -U olddba -h hostA olddb | psql -U newdba newdb


  • You'll need to have the databases accessible from the other host for this to work, tweak your pg_hba.conf.
  • you'll also need to set listen_addresses='*' inside postgresql.conf
  • you may need to type "reset" after the pg_dump is complete to reset your terminal to echo mode; having two password prompts confuses it a bit.

SQL Tips and Tricks

Timestamps and Timezones

  • I have a field of type timestamp with time zone and need to insert a value into it:
    • Format your data in to "YYYY-MM-DD HH24:MI:SS TZ".
    • If you insert it as just a single string value, postgres knows what to do
      • INSERT INTO table (data_timestamp) VALUES ('2015-11-15 14:23:00 UTC');
    • If you're assembling the string, you need a cast:
      • INSERT INTO table (data_timestamp) VALUES ( ('2015-11-15 ' || '14:23:00' || ' UTC') :: timestamp with time zone);

Conditional Inserts

  • Conditional Inserts can be achieved as follows:
    • INSERT INTO table (field1, field2) SELECT value1, value2 WHERE condition
    • Example:
      • INSERT INTO queue_send_to_archive (
        ) SELECT

        WHERE EXISTS (
        SELECT * FROM datalogger_properties WHERE property_name='send1MinDataToArchive' AND property_value='Y'