- Log in to post comments
-- enable formated output
\x on
-- show databass
\l
SELECT datname FROM pg_database WHERE datistemplate = false;
-- show tables
\dt
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
-- show user
\du
-- drop database
drop database foo;
echo "drop owned by zentras" | psql
-- exit
\q
-- Display tables structure
\d+
-- Display tables size
\dT+
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;
-- quite output without header
psql -P t -c "SELECT table_name FROM information_schema.tables"
-- change database
\c dbname
-- import script?
\i /path/to/file.sql
-- login to db
sudo -u postgres psql
-- delete database
drop database bar;
-- delete user
drop role foo;
-- delete all tables
psql USER -t -c "select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname='public'" | psql
# dump database
pg_dump -Fc -c db1 > /tmp/$(hostname -f).postgresql.db1.$(date +%Y-%m-%d_%H%M%S).dmp.gz
pg_dump -c db1 > /tmp/db1-postgres.sql
zip -9mq /tmp/postgres.sql.zip /tmp/postgres.sql
pg_dump -c foo | zip -9q > /tmp/$(hostname -f).foo.sql.zip
# allow login from any host
/etc/postgresql/9.5/main/postgresql.conf
listen_addresses = '*'
/etc/postgresql/9.5/main/pg_hba.conf
host all all 0.0.0.0/0 md5
# drop all
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
# Ansible
https://opensource.com/article/17/6/ansible-postgresql-operations
# Backup
https://www.postgresql.org/docs/9.3/static/app-pgdump.html
# configuration
https://www.postgresql.org/docs/9.5/static/runtime-config-wal.html
# change user password
ALTER USER user_name WITH PASSWORD 'pass1234';
# optimization
sudo apt-get install -y libdbd-pg-perl libdbi-perl
wget https://raw.githubusercontent.com/jfcoz/postgresqltuner/master/postgresqltuner.pl
chmod +x postgresqltuner.pl
./postgresqltuner.pl --database=postgres --user=postgres --password=pass1234
https://pganalyze.com/docs/install/01_enabling_pg_stat_statements
select * from pg_stat_statements;
# export shema only
pg_dump -d db1 --schema-only
# db restore
pg_restore -c -d db1 db1.dmp
# snippets
DO $$
DECLARE
foo integer := 10;
bar varchar := "bar124";
BEGIN
select count(id) into MYCOUNT from table1 where foo ='bar';
RAISE NOTICE 'Count MYCOUNT (%)', MYCOUNT;
IF MYCOUNT = 1 THEN
RAISE NOTICE 'MYCOUNT is 1';
END IF;
END $$;
DO $$
DECLARE
a integer := 10;
b integer := 20;
BEGIN
select id into b from coma_communication;
IF a > b THEN
RAISE NOTICE 'a is greater than b';
ELSE
RAISE NOTICE 'a is not greater than b';
END IF;
END $$;
# print message
RAISE EXCEPTION 'user_id is %', user_id;
# vacuumdb / cleanup
https://www.postgresql.org/docs/9.5/static/app-vacuumdb.html
# tune
https://github.com/jfcoz/postgresqltuner
# Links
https://www3.ntu.edu.sg/home/ehchua/programming/sql/PostgreSQL_GetStarted.html
https://www.niwi.nz/2013/02/17/postgresql-database-table-indexes-size/ - SHOW DATABASE, TABLE AND INDEXES SIZE ON POSTGRESQL
https://www.postgresql.org/docs/9.0/static/app-vacuumdb.html
https://www.postgresql.org/docs/9.1/static/app-reindexdb.html