regexp_matches(string, pattern)
-------------------------------

Unforunately this returns a setof text[] with *all* the matches found. If you just want one, you can do this:

    SELECT (regexp_matches(col, 'foo(bar)'))[1] FROM baz


Find the worst tables for full table scans:

    SELECT  relname, 
            100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
            n_live_tup rows_in_table
    FROM    pg_stat_user_tables
    WHERE   seq_scan + idx_scan > 0 
    ORDER BY n_live_tup DESC;


Find the worst tables for missed cache rate:

    SELECT  relname, heap_blks_read, heap_blks_hit,
            round(heap_blks_hit::numeric/(heap_blks_hit + heap_blks_read), 3)
    FROM    pg_statio_user_tables
    WHERE   heap_blks_read > 0
    ORDER BY 4
    LIMIT   25;

Find the worst tables for missed cache rate on *indexes*:

    SELECT  relname, idx_blks_read, idx_blks_hit,
            round(idx_blks_hit::numeric/(idx_blks_hit + idx_blks_read), 3)
    FROM    pg_statio_user_tables
    WHERE   idx_blks_read > 0
    ORDER BY 4
    LIMIT   25;

Find the overall cache hit rate (should be 99%+):

    SELECT  sum(heap_blks_read) as heap_read,
            sum(heap_blks_hit)  as heap_hit,
            sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
    FROM    pg_statio_user_tables;


Show the current transaction isolation level:

    SELECT current_setting('transaction_isolation');

To start a new Rails project:

    sudo su - postgres -c psql <<-EOQ
    CREATE USER foo WITH PASSWORD 'bar';
    ALTER USER foo CREATEDB;
    EOQ
    rake db:create db:migrate

To write to a file:

    \o filename

Like a <> b but accounting for nulls:

    a IS DISTINCT FROM b

Find the current queries:

    SELECT datname,procpid,current_query FROM pg_stat_activity;

or in 9.3:

    SELECT datname, pid, query FROM pg_stat_activity ORDER BY pid;

Kill a query:

   SELECT pg_cancel_backend(12345);


To get the number of years between two dates:

  SELECT  extract(year from age(now(), '2014-01-01'));

NOT:

  SELECT  extract(year from now() - '2014-01-01');



Set or overwrite an hstore key/value:

  UPDATE hstore_test SET data = data || '"key4"=>"some value"'::hstore

  UPDATE hstore_test SET data = data || hstore(ARRAY['key4', 'some value'])



replication
===========

master (10.0.1.1)
-----------------

In postgresql.conf:

    wal_level = hot_standby
    max_wal_senders = 3
    checkpoint_segments = 8
    wal_keep_segments = 8
    archive_mode = on
    archive_command = 'rsync -aq -e "ssh -o StrictHostKeyChecking=no" %p 10.0.1.2:/var/lib/postgresql/9.3/archive/%f'
    archive_timeout = 3600


slave (10.0.1.2)
----------------

In postgresql.conf:

    hot_standby = on

Has a recovery.conf file at e.g. /var/lib/postgresql/9.3/main/recovery.conf:

    standby_mode = 'on'
    primary_conninfo = 'host=10.0.1.1 port=5432 user=postgres'
    trigger_file = '/var/lib/postgresql/9.3/main/trigger'

    # Shell command to execute an archived segment of WAL file series.
    # Required for archive recovery if streaming replication falls behind too far.
    restore_command = 'cp /var/lib/postgresql/9.3/archive/%f %p'
    archive_cleanup_command = '/usr/lib/postgresql/9.3/bin/pg_archivecleanup /var/lib/postgresql/9.3/archive/ %r'
    

failover
--------

Just create a trigger file on the slave, e.g.:

    touch /var/lib/postgresql/9.3/main/trigger