DB Strike: July 2017

Postgres SQL: pg_dump / pg_restore

Backup a specific table:

pg_dump --table TABLENAME -U postgres dbName -f /home/postgres/TABLENAME.sql

If there is a table with the same name in another schema, use the --schema SCHEMANAME option

Postgres SQL: User management

alter user POSTGRES with password 'newPassword';

Postgres SQL: Database admin

DB size

select pg_size_pretty(pg_database_size('DB1'));

pg_size_pretty
----------------
48 GB

Non default parameters

This shows all of the server configuration changes made via updates to the postgresql.conf file, from a running server:

SELECT version();
SELECT name, current_setting(name), SOURCE
FROM pg_settings
WHERE SOURCE NOT IN ('default', 'override');

Postgres SQL: Functions

You can find basic information regarding a function with the command \df

You can see the source code with this query:

testdb=> select prosrc from pg_proc where proname='function_or_procedure_name';<br />

Execute a VOID function:

testdb=> select functionName();

Execute a VOID function with named parameters:

testdb=> select functionName(param1:='valueText', param2:=valueInt);

If you need more information, here is the pg_proc table:

Postgres SQL: Tables

Table size

with index:

select pg_size_pretty(pg_total_relation_size('TABLENAME'));
pg_size_pretty
----------------
26 GB

without index:

select pg_size_pretty(pg_relation_size('TABLENAME'));
pg_size_pretty
----------------
11 GB

Tables number of lines:

select schemaname, relname, n_live_tup from pg_stat_all_tables where n_live_tup > 0 order by n_live_tup desc;

Postgres SQL: Indexes

invalid indexes:

select count(*) from pg_index where indisvalid = false

number of indexes per tables:

Index usage

SELECT
i.idx_scan,
i.idx_tup_read,
i.idx_tup_fetch,
i.indexrelname AS index,
it.spcname AS index_tablespace,
i.relname AS table,
tt.spcname AS table_tablespace,
pg_size_pretty(pg_relation_size(i.indexrelname::text)) as index_size
FROM pg_stat_all_indexes i
INNER JOIN pg_class ic ON (i.indexrelid = ic.oid)
LEFT OUTER JOIN pg_tablespace it ON (ic.reltablespace = it.oid)
INNER JOIN pg_class tc ON (i.relid = tc.oid)
LEFT OUTER JOIN pg_tablespace tt ON (tc.reltablespace = tt.oid)
ORDER BY 1 desc, 2 desc, 3 desc

Index not used

select * from pg_stat_all_indexes
where schemaname <> 'pg_catalog'
and schemaname <> 'pg_toast'
and idx_scan = 0
and idx_tup_read = 0
and idx_tup_fetch = 0
and indexrelid not in (select indexrelid from pg_index where indisunique = true or indisprimary = true
);

Postgres SQL: Sessions

Global overview:

v8.3

SELECT
    sum(CASE WHEN waiting THEN 1 ELSE 0 END) AS waiting,
    sum(CASE WHEN current_query='' THEN 1 ELSE 0 END) AS idle,
    sum(CASE WHEN current_query=' in transaction' THEN 1 ELSE 0 END) AS idletransaction,
    sum(CASE WHEN current_query='' THEN 1 ELSE 0 END) as unknown,
    sum(CASE WHEN NOT waiting AND current_query NOT IN ('', ' in transaction', '') THEN 1 ELSE 0 END) AS active
FROM pg_stat_activity WHERE procpid != pg_backend_pid() and datname = current_database();

v9.3

all sessions (full)

select * from pg_stat_activity

all sessions (lite)

select datname, usename, state, waiting, query from pg_stat_activity;

Sessions state per DB

select datname, state, count(*) from pg_stat_activity group by datname, state order by 1, 2;

nb sessions waiting

select waiting, count(*) from pg_stat_activity group by waiting;

nb sessions waiting per DB

select datname, waiting, count(*) from pg_stat_activity group by datname, waiting order by 1, 2;

querries per DB

select datname, query, count(*) from pg_stat_activity group by datname, query order by 1,3 desc

DB Strike

Search This Blog

Friday, July 21, 2017