most important conf files
they are located on different folders, according to the OS (this are also the standard storage location for postgres)
Restore a dump
psql -U username dbname < mydumpfile.sql
create a dump
pg_dump -U username dbname > mydumpfile.sql
Rename Database:
ALTER DATABASE screenfood RENAME TO screenfood_old;
Change password
ALTER USER Postgres WITH PASSWORD '<newpassword>';
get the size of a database
select pg_size_pretty(pg_database_size('dbname'));
get the size of all databases sorted
source: https://wiki.postgresql.org/wiki/Disk_Usage
SELECT
d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS Size
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
LIMIT 20
get the size of a table
select pg_size_pretty(pg_relation_size('tablename'));
get the size of all table sorted
source: https://wiki-bsse.ethz.ch/
SELECT
relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) As "Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
get size of index (where table name like 't_playback_%')
source: https://wiki.postgresql.org/wiki/Index_Maintenance
SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
CASE WHEN indisunique THEN 'Y'
ELSE 'N'
END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname='public' and relname like 't_playback_%'
ORDER BY 1,2;
basically all tables can be showd by\d
if you want to select only some table names, this will help you and does nearly the same as >\d
SELECT table_schema,table_name,table_catalog,table_type FROM information_schema.tables WHERE table_schema = 'public';
SELECT datname,usename,pid,client_addr,waiting,state,query_start,query FROM pg_stat_activity ORDER BY query_start;
SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity ORDER BY query_start;
Watching queries from ps If you want, you can also make the queries being run show up in process titles, by setting the following:
update_process_title = on
listen_addresses = '*'
An example, how it could work. With trust a logged in user can connect to the database with any user without password.
# "local" is for Unix domain socket connections only
local all postgres peer
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.190.0/24 md5
# IPv6 local connections:
host all all ::1/128 md5
show configured settings:
SELECT name, setting, reset_val, source from pg_settings;
show non default settings:
SELECT Name, source, setting, unit from pg_settings where source != 'default' AND source != 'override' ORDER by 2,1;
change settings during runtime
SET work_mem='16MB';
Log all modifications:
posgresql.conf -> set log_statement (mod)
-> http://www.postgresql.org/docs/9.3/static/runtime-config-logging.html
select only uniqe values
SELECT DISTINCT myColumd FROM myTable;
set timing for summarize query time
# \timing
Create an index
CREATE INDEX t_playback_log_event_idx ON t_playback_log_event(c_playback_log_id,c_date);
Copy a table in a CSV file
psql -U screenfood -c "copy t_client to '/tmp/t_client.csv' delimiter ';' CSV HEADER;"