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