[ Home ]

Recommended .psqlrc

Always commit manually. At the prompt, show the db username at the database hostname, at the database, at the port; show if we are in a transaction, and show either > or # at the prompt depending on whether or not our database user has admin-like powers. Instead of having nulls show up as blank space, which is indistinguishable from, for instance, character/string values that are '', show nulls as <<NULL>>. Finally, show how long it takes to run each query. 

\set AUTOCOMMIT off
\set PROMPT1 '%n@%m:%>/%/%x%# '
\pset null '<<NULL>>'
\timing

Create a new database

I create new dbs just infrequently enough that I never remember the flags I use when creating the db. In this example, I create a database using the SQL_ASCII character encoding (which is sort of the non-character-encoding character encoding, as opposed to, let's say, UTF8 or LATIN1) and collating/sorting based on the C locale, which is the "no locale" (as opposed to, let's say, en_US or fr_CA):

createdb \
    -h myhost \
    -U myuser \
    --echo \
    --owner=myuser \
    --locale=C \  # only psql 9.0 and above
    --encoding=SQL_ASCII \
    my_new_database

database startup script for /etc/init.d

#!/bin/bash

# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS

# where to find commands like su, echo, etc...
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

DB_ENCODING=SQL_ASCII

DB_LOCALE=C

PG_INSTALL_DIR=/u99/local/pgsql-9.0

PG_DATA_DIR="${PG_INSTALL_DIR}/data"

PG_SERVER_LOG="$PG_DATA_DIR/serverlog"

PG_UNIX_USER=postgres

POSTGRES="$PG_INSTALL_DIR/bin/postgres"

PG_CTL="$PG_INSTALL_DIR/bin/pg_ctl"

INITDB="$PG_INSTALL_DIR/bin/initdb"

# die on first failure; do not keep trucking
set -e

if [ $# -ne 1 ]; then
    echo "please enter start/stop/restart etc..." 1>&2
    exit 1
fi

# Only start if we can find postgres and pg_ctl.
if [ ! -x $PG_CTL ]; then
    echo "$PG_CTL not found" 1>&2
    exit 1
fi

if [ ! -x $POSTGRES ]; then
    echo "$POSTGRES not found" 1>&2
    exit 1
fi

case $1 in
  init)
	su - $PG_UNIX_USER -c "$INITDB --pgdata='$PG_DATA_DIR' --encoding=$DB_ENCODING --locale=$DB_LOCALE"
	;;
  start)
	echo -n "Starting PostgreSQL: "
	su - $PG_UNIX_USER -c "$PG_CTL start -D '$PG_DATA_DIR' -l $PG_SERVER_LOG &"
	echo "ok"
	;;
  stop)
	echo -n "Stopping PostgreSQL: "
	su - $PG_UNIX_USER -c "$PG_CTL stop -D '$PG_DATA_DIR' -s -m fast"
	echo "ok"
	;;
  restart)
	echo -n "Restarting PostgreSQL: "
	su - $PG_UNIX_USER -c "$PG_CTL stop -D '$PG_DATA_DIR' -s -m fast -w"
	su - $PG_UNIX_USER -c "$PG_CTL start -D '$PG_DATA_DIR' -l $PG_SERVER_LOG &"
	echo "ok"
	;;
  reload)
        echo -n "Reload PostgreSQL: "
        su - $PG_UNIX_USER -c "$PG_CTL reload -D '$PG_DATA_DIR' -s"
        echo "ok"
        ;;
  status)
	su - $PG_UNIX_USER -c "$PG_CTL status -D '$PG_DATA_DIR'"
	;;
  *)
	# Print help
	echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
	exit 1
	;;
esac

exit 0

copy a database

When you don't specify --template for createdb, the template1 database will get used. However, a fun trick to copy a database is to just specify the database you want copied using the --template flag:

createdb \
    -h myhost \
    -U myuser \
    --echo \
    --owner=myuser \
    --template=my_old_database \
    my_new_database

Get sizes of objects using built-in functions

Get the size of a database:

select pg_size_pretty(pg_database_size('my_db'));

Get the size of a table and its indices and toast tables:

select pg_size_pretty(pg_total_relation_size('my_table'));

Get the size of a just a table and not its indices:

select pg_size_pretty(pg_relation_size('my_table'));

Force use of indexes by query planner

set enable_seqscan = off;
commit;

Set a sequence to the max value of a table column

select setval('my_sequence', max(my_column)) from my_table;
commit;

Set work_mem higher for this session.

If you are going to build a large index or do some other large query that can benefit from access to more RAM, set work_mem larger for your session

set work_mem = '56GB';
commit;
-- Do your work here.
-- Then be a good neighbour.
set work_mem = default;
commit;

Get a partial database dump that can be restored to a different user

How to dump a PostgreSQL database in such a way that restoring to a slightly different version of PostgreSQL on a different machine using perhaps a different user will not be a problem:

pg_dump \
    --file=/path/to/dump/file.dump \
    --format=custom \
    --ignore-version \
    --schema=public \
    --schema=otherschema \
    --no-owner \
    --verbose \
    --no-privileges \
    -U user -h myhost mydatabase

How to dump only particular tables from particular schemas

pg_dump \
    --file=/path/to/dump/file.dump \
    --format=custom \
    --ignore-version \
    --table=myschema.mytable \
    --table=myschema.myothertable \
    --table=myotherschema.mythirdtable \
    --no-owner \
    --verbose \
    --no-privileges \
    -U user -h myhost mydatabase

How to restore a dump flexibly

Restore a dump, not caring if the user matches nor if the exact point version of PostgreSQL matches. 

pg_restore \
    --format=custom \
    --ignore-version \
    --no-owner \
    --no-privileges \
    --verbose \
    -U user -h myhost -d mydatabase \
    /path/to/dump/file.dump

How to restore only some items from a dump file

If you have a dump file that has more stuff in it than you want, or that has commands to build indexes and you don't want to build indices at restore time, you can do this:

First, get a list of all the items in the dump file, directing that list to a text file. 

pg_restore \
    -l /path/to/dump/file.dump \
    > /path/to/dump.list

Now edit dump.list, removing anything you do not want to restore (particularly indexes).

Now feed that edited list to pg_restore.

pg_restore \
    --format=custom \
    --ignore-version \
    --no-owner \
    --use-list /path/to/dump.list \
    --verbose \
    --no-privileges \
    -U user -h myhost -d mydatabase \
    /path/to/dump/file.dump

How to copy a table from one database to another

There are many ways to do this, but the psql client's \copy command can be used to copy data from one table to another. Let's say you have two databases, olddb and newdb. olddb has a table called users, and newdb has a new, empty table called users that is currently empty but needs the data from olddb's users. Here's how to accomplish this on the command line, piping the output of one psql session into a second psql session:

psql \
    -X \
    -U user \
    -h oldhost \
    -c "\\copy users to stdout" \
    olddb \
| \
psql \
    -X \
    -U user \
    -h $DBHOST \
    -c "\\copy users from stdin" \
    newdb \

Let's say your users table in olddb has three columns:

But your users table in newdb has two columns:

You can still use psql's copy command to get what you want: 

psql \
    -X \
    -U user \
    -h oldhost \
    -c "\\copy (select first_name, last_name from users) to stdout" \
    olddb \
| \
psql \
    -X \
    -U user \
    -h $DBHOST \
    -c "\\copy users from stdin" \
    newdb \

Best way to execute sql scripts using psql

Usually, the most desired way to run an sql script through psql is 1) with the ability to feed it variables that get evaluated by the script, and 2) with the expectation that the script die after the first error that gets encountered (rather than continuing on, as is the default).

Here's a sample that shows these two traits in action.

First, make a wrapper shell script to control how you feed your sql script to psql. In this example, we assume that there are two exactly two required arguments we want to give our shell script that we want to feed to our sql script. 

#!/bin/sh

if [ $# != 2 ]; then
    echo "please enter a db host and a table suffix"
    exit 1
fi

export DBHOST=$1
export TSUFF=$2

psql \
    -X \
    -U user \
    -h $DBHOST \
    -f /path/to/sql/file.sql \
    --echo-all \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --set TSUFF=$TSUFF \
    --set QTSTUFF=\'$TSUFF\' \
    mydatabase

psql_exit_status = $?

if [ $psql_exit_status != 0 ]; then
    echo "psql failed while trying to run this sql script" 1>&2
    exit $psql_exit_status
fi

echo "sql script successful"
exit 0

A few notes on the above shell script:

Here are the contents of /path/to/sql/file.sql: 

begin;
drop index this_index_:TSUFF;
commit;

begin;
create table new_table_:TSUFF (
    greeting text not null default '');
commit;

begin;
insert into new_table_:TSUFF (greeting)
values ('Hello from table ' || :QTSUFF);
commit;

Run a bunch of sql commands in a single transaction.

Often times, you will have a text file filled with SQL commands that you want to execute as a single transaction, and it's easy to forget to put the "commit;" as the last command in the file. There's a way around this, using the --single-transaction flag:

psql \
    -X \
    -U myuser \
    -h myhost \
    -f /path/to/sql/file.sql \
    --echo-all \
    --single-transaction \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    mydatabase

And so the contents of file.sql could be:

insert into foo (bar) values ('baz');
insert into yikes (mycol) values ('hello');

and both inserts will be wrapped in a begin/commit.

How to get a single select value into a shell variable and out of a shell variable

CURRENT_ID=`$PSQL -X -U $PROD_USER -h myhost -P t -P format=unaligned $PROD_DB -c "select max(id) from users"`
let NEXT_ID=CURRENT_ID+1
echo "next user.id is $NEXT_ID"

echo "about to reset user id sequence on other database"
$PSQL -X -U $DEV_USER $DEV_DB -c "alter sequence user_ids restart with $NEXT_ID"

How to list all of a table's indices

Remember, the easy way, when you are in psql, is to just do 

\d mytable
and look for the "Indexes" portion of the table description. Otherwise... 

create type idx_func_return_type as (
    index_name text);

create or replace function table_indexes(schmname text, tblname text) returns setof idx_func_return_type as
$body$
declare
    stmt text;
    tblcount integer;
    result idx_func_return_type%rowtype;
begin
    stmt := 'select count(*) '
          ||   'from pg_class as tbl '
          ||   'join pg_namespace as schm '
          ||     'on tbl.relnamespace = schm.oid '
          ||  'where schm.nspname = ''' || schmname || ''' '
          ||    'and tbl.relname = ''' || tblname || ''' ';
    execute stmt into tblcount;
    if ( tblcount = 0 ) then
        raise exception 'schema/table does not exist';
    end if;

    stmt := 'select idx_info.relname as index_name '
          ||  'from pg_index as idx '
          ||  'join pg_class as tbl on tbl.oid = idx.indrelid '
          ||  'join pg_namespace as schm on tbl.relnamespace = schm.oid '
          ||  'join pg_class as idx_info on idx.indexrelid = idx_info.oid '
          || 'where schm.nspname = ''' || schmname || ''' '
          ||   'and tbl.relname = ''' || tblname || ''' ';
    for result in execute stmt loop
        return next result;
    end loop;
    return;
end;
$body$ language 'plpgsql';
commit;

Then, at the psql prompt, type: 

select index_name from table_indexes('public', 'my_table');

How to drop all of a table's indices

create or replace function drop_indexes_on_table(a_schema text, a_table text)
returns int
language 'plpgsql'
as $$
declare
    sql_to_run text;
    idx_rec record;
    num_idxs_dropped int;
begin
    num_idxs_dropped := 0;

    raise notice 'looking for indexes for table %.%', a_schema, a_table;

    for idx_rec in
    select idx_info.relname as index_name
      from pg_index as idx
      join pg_class as tbl
        on tbl.oid = idx.indrelid
      join pg_namespace as schm
        on tbl.relnamespace = schm.oid
      join pg_class as idx_info
        on idx.indexrelid = idx_info.oid
     where schm.nspname = a_schema
       and tbl.relname = a_table
    loop
        num_idxs_dropped := num_idxs_dropped + 1;
        raise notice 'about to drop index % for table %.%', idx_rec.index_name, a_schema, a_table;
        sql_to_run := 'drop index if exists ' || a_schema || '.' || idx_rec.index_name;
        execute sql_to_run;
    end loop;
    return num_idxs_dropped;
end;
$$;
commit;

Get the size of a database

  select relname as relname, 
         pg_size_pretty(sum(relpages) * 8192) as size
    from pg_class
group by relname;

Find all tables with a certain column name

    select n.nspname, 
           c.relname, 
           c.relnamespace, 
           c.relkind, 
           a.attname, 
           a.attrelid 
      from pg_attribute as a 
      join pg_catalog.pg_class as c 
        on a.attrelid = c.oid 
      join pg_catalog.pg_namespace as n 
        on c.relnamespace = n.oid 
     where a.attname = 'my_column' 
       and n.nspname = 'my_schema' 
       and c.relkind = 'r';

Get the columns that an index operates on

First get the oid of the table, the oid of the indexes, and the column numbers used by those indexes. The column numbers will be something like "1 3 4", which means the first, third, and fourth columns of the searched-for table are used by the index. (Usually, there's just one number because many indexes are made for one column.) 

select tbl.oid as table_oid,
       idx.indexrelid as index_oid,
       idx.indkey as column_numbers,
       idx_info.relname as index_name
  from pg_index as idx
  join pg_class as tbl on tbl.oid = idx.indrelid
  join pg_namespace as schm on tbl.relnamespace = schm.oid 
  join pg_class as idx_info on idx.indexrelid = idx_info.oid
 where schm.nspname = 'my_schema'
   and tbl.relname = 'my_table';


 table_oid | index_oid | column_numbers |           index_name            
-----------+-----------+----------------+---------------------------------
  48645160 | 215366881 | 3              | my_table_my_col_idx
  48645160 |  48877631 | 1              | my_table_my_other_col_idx

Now you can get the names of the columns operated on by each index. For each row (representing one index) from the above query, split i.indkey by space into column numbers. For each column number, run the following query to get the column name. 

select c.attname as column_name
  from pg_attribute as c
 where c.attrelid = $table_oid  -- from previous query
   and c.attnum = $column_number  -- from previous query

Find schemas associated with a particular user

Select schemas in current db associated with user myuser, even if there are no tables in the schema, including public schema: 

select 'public' as nspname
 union all
select nspname
  from pg_catalog.pg_namespace as nsp
  join pg_catalog.pg_roles as rls
    on nsp.nspowner = rls.oid
 where rls.rolname = 'myuser';

Get IO stats for a particular table

select relname         as "table",
       heap_blks_read  as "heap from disc",
       heap_blks_hit   as "heap from cache",
       idx_blks_read   as "index from disc",
       idx_blks_hit    as "index from cache",
       toast_blks_read as "toast from disc",
       toast_blks_hit  as "toast from cache",
       tidx_blks_read  as "toast index disc",
       tidx_blks_hit   as "toast index cache"
  from pg_statio_user_tables
 where relname = 'my_table';

Read activity stats for a particular table

select relname         as "table",
       seq_scan        as "table scans",
       idx_scan        as "index lookups"
  from pg_stat_user_tables
 where relname = 'my_table';

Read enhanced activity stats for a particular table

select relname         as "table",
       seq_scan        as "table scans",
       seq_tup_read    as "tuples scanned",
       idx_scan        as "index lookups",
       idx_tup_fetch   as "tuples fetched via index"
  from pg_stat_user_tables
 where relname = 'my_table';

Find the size of a table

In bytes:

select relpages * 8192 as size_in_bytes
  from pg_class where relname = 'mytable';

In human-readable form:

select pg_size_pretty(relpages * 8192) as size
  from pg_class where relname = 'mytable';

Find out how many pages and tuples are used by a table

select relname         as "table",
       reltuples       as "number of tuples",
       relpages        as "number of 8kb pages"
  from pg_class
 where relname = 'my_table';

Find live vs. dead tuples of a table

select relname         as "table",
       n_live_tup      as "live tuples",
       n_dead_tup      as "dead tuples"
  from pg_stat_user_tables
 where relname = 'my_table';

Find out when a table was last analyzed or vacuumed

select relname         as "table",
       last_vacuum     as "last manual vacuum",
       last_autovacuum as "last auto vacuum",
       last_analyze    as "last manual analyze",
       last_autoanalyze as "last auto analyze"
  from pg_stat_user_tables
 where relname = 'my_table';

Find the number of connections to your database

select datname,
       procpid,
       usename,
       client_addr,
       client_port
  from pg_catalog.pg_stat_activity;

Kill a user's session based on procpid from pg_stat_activity

pg_terminate_backend(procpid);

Cancel a user's query based on procpid from pg_stat_activity

pg_cancel_backend(procpid);