Install Postgresql (Ubuntu 20.04)
  • sudo apt install postgresql postgresql-contrib
  • sudo systemctl enable postgresql.service



Initial PostgreSQL Setup (Ubuntu 20.04)

  • I change the access control porton of the /etc/postgresql/12/main/pg_hba.conf to the following:
    • # Database administrative login by Unix domain socketlocal 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 127.0.0.1/32 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 127.0.0.1/32 md5
    • #host replication postgres ::1/128 md5
    • local all postgres peer
    • local all all md5
    • host all all 127.0.0.1/32 md5
    • host all all ::1/128 md5

  • and then restart the postgres service
    • systemctl restart postgresql.service


Entering psql as admin
  • sudo su - postgres -c "psql"

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
      # .shaexport PATH=$PATH:~/.bin:/usr/local/binexport PAGER=lessexport LESS="-iMSx4 -FX"
    • in ~/.psqlrc:
      \timing# \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

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
Note:
  • 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 (

        data_timestamp,

        station_id,

        enqueued_timestamp,

        filename,

        msg

        ) SELECT

        ,

        ,

        current_timestamp,

        ,


        WHERE EXISTS (

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

        )