Postgresql Advanced Commands and Queries

Recommended .psqlrc

Always commit manually. At the prompt, show the db username at the database hostname, at the database; 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

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 \
--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

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 ON_ERROR_STOP=on \
--set TSUFF=$TSUFF \
--set QTSTUFF=\'$TSUFF\' \
mydatabase

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

echo "sql script successful"
exit 0

A few notes on the above shell script:

  • TNUM is fed to psql as both a bare value and a value wrapped in single quotes, so that we can use the value in table or schema names (TSUFF, unquoted) or in strings (QTSUFF, quoted)
  • We use -X to ensure we do not use the current unix users’s .psqlrc file
  • We echo all messages to the console, so we know what sql is being executed (handy in the face of failures)
  • we use ON_ERROR_STOP to stop our sql script as soon as something goes wrong

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;

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;

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 blackduck, 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 = 'blackduck';

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;

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s