PostgreSQL Note

PostgreSQL_Note.md

PostgreSQL Note

Commands

https://www.postgresql.org/docs/9.5/static/reference-client.html

Show work memory

SHOW work_mem;
SHOW maintenance_work_mem;

Create Database with UTF-8 encoding

createdb -E UTF8 -T template0 --locale=en_US.utf8 <dbname> -O owner

Connect to database

psql
psql -U user
psql -U postgres
psql -U postgres -h locahlost -d db

backup database

pg_dump db_name > file

restore

psql db_name < file

backup and restore to another server

pg_dump db_name -h localhost -p 15432 | psql -h localhost -p 15433
pg_dump db_name --data-only -h localhost -p 15432 | psql -h localhost -p 15433

-T exclude table

backup & recovery with compression

$ pg_dump dbname | gzip > filename.gz
$ gunzip -c filename.gz | psql dbname

Monitoring

htop -u postgres

Force kill hanging query

# check locked
SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

SELECT * FROM pg_stat_activity WHERE status = 'active';
SELECT pg_terminate_backend(PID);
SELECT pg_cancel_backend(PID);

Dump all databases and reload databases

$ pg_dumpall > db.out
$ psql -f db.out postgres

Database

• \l - list all databases

• \c db_name - connect to database

• create database

CREATE DATABASE newdb WITH OWNER demorole ENCODING 'UTF8';

• change owner

ALTER DATABASE h2 OWNER TO h2;

• grant access

GRANT CONNECT ON DATABASE database_name TO username;

Table

• \dt - list tables

• list columns on tables

\d table_name

• copy table to another db

pg_dump -t table_to_copy source_db | psql target_db

reassign all tables owner

https://gist.github.com/hanksudo/f1365f3a764d3c8519cc

Permission

• \z table_name - Permission of table

Grant Permission

GRANT SELECT, UPDATE, INSERT ON table_name TO role_name;
GRANT SELECT ON table_name TO ROLE_NAME;

multiple tables

GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;

Revoke Permission

REVOKE ALL ON table_name FROM role_name;

Role

• \du - list roles

• create role

CREATE ROLE demorole WITH LOGIN ENCRYPTED PASSWORD 'password' CREATEDB;
CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'password';

• alter role

ALTER ROLE demorole CREATEROLE CREATEDB REPLICATION SUPERUSER;
ALTER ROLE demorole WITH ENCRYPTED PASSWORD '123456';

-- drop role
DROP ROLE demorole;

Index

• list all indexes

SELECT * FROM pg_stat_all_indexes WHERE schemaname = 'public' ORDER BY indexrelname;

• list index of table

\d table_name

• create index

CREATE INDEX CONCURRENTLY diary_user_idx ON diary(user_id, id);

• drop index

DROP INDEX diary_user_idx;

Other

• Show config file path

SHOW config_file;

• Show current setting

\set

• show the value of a run-time parameter

SHOW ALL;
SHOW SERVER_VERSION;

SQLs

• alter foreign key

ALTER TABLE diary ADD FOREIGN KEY (meter_id) REFERENCES user_meter(id);

• list tables name

SELECT table_name FROM information_schema.tables
WHERE table_schema='public' AND table_type='BASE TABLE';

• update datetime

UPDATE diary SET recorded_at = recorded_at + INTERVAL '17 days'

• Distinct ON Multi columns

SELECT DISTINCT ON(glucose_value, recorded_at) glucose_value, recorded_at FROM diary WHERE user_id = 1 ORDER BY recorded_at;

• Regular Expression

SELECT * FROM product WHERE serial_number !~ '\w{15}';
SELECT * FROM product WHERE serial_number ~ '\w{15}';

• Explain Analyze

EXPLAIN ANALYZE select * from api_log;

• Find Largest table in the db

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

• Sql function that returns a table with I/O infomation (obtained from pg_statio_user_indexes view) and filtered by current database and "public" schema

CREATE OR REPLACE FUNCTION indexes_io_for_current_database()
RETURNS TABLE(indexname name, idx_blks_read bigint, idx_blks_hit bigint) AS $$
    SELECT i.indexrelname, i.idx_blks_read, i.idx_blks_hit
    FROM pg_statio_user_indexes AS i
    WHERE i.relname IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public');
$$ LANGUAGE SQL;
SELECT * FROM indexes_io_for_current_database() ORDER BY idx_blks_hit DESC LIMIT 5;

• Sql function that returns a table with index usage infomation (obtained from pg_stat_user_indexes view) and filtered by current database and "public" schema:

CREATE OR REPLACE FUNCTION indexes_stat_for_current_database()
RETURNS TABLE(indexname name, idx_scan bigint, idx_tup_read bigint, idx_tup_fetch bigint) AS $$
    SELECT i.indexrelname, i.idx_scan, i.idx_tup_read, i.idx_tup_fetch
    FROM pg_stat_user_indexes AS i
    WHERE i.relname IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public');
$$ LANGUAGE SQL;

SELECT * FROM indexes_stat_for_current_database() ORDER BY idx_scan DESC LIMIT 5;

• Finding duplicate rows

SELECT id from (
  SELECT id,
  ROW_NUMBER() OVER(
    PARTITION BY first_name,
                 last_name,
                 email
              ORDER BY id
    ) AS user_row_number
  FROM users
) duplicates
WHERE
duplicates.user_row_number > 1

list all tables and indexes size

SELECT TABLE_NAME,
       pg_size_pretty(pg_relation_size(TABLE_NAME)) AS relation_size,
       pg_size_pretty(pg_total_relation_size(TABLE_NAME)) AS total_relation_size,
       pg_size_pretty(pg_indexes_size(TABLE_NAME)) AS indexes_size
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_relation_size(TABLE_NAME) DESC;

Find unused index

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';

Extensions

pg_stat_statements

add into /etc/postgresql/9.4/main/postgresql.conf

shared_preload_libraries = 'pg_stat_statements'    # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

sudo su - postgres
psql

\x
CREATE extension pg_stat_statements;
\dx
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

pgpass

127.0.0.1:5432:*:username:password

compare two databases

sudo -u postgres pg_dump -f original.sql db_name
sudo -u postgres pg_dump -h 192.168.33.32 -f new.sql db_name
java -jar apgdiff-2.4.jar --ignore-start-with original.sql new.sql

Remote query execution

psql <REMOTE POSTGRESQL URL> \
-c "copy (
      SELECT *
      FROM users
      WHERE date_trunc('month', created_at) = '2016-01-01'
    ) to stdout" \
| psql <LOCAL POSTGRESQL DATABASE> \
-c "copy users from stdin"

Other References

• PostgreSQL - Memory
• PostgreSQL: Documentation: 9.4: Transaction Isolation
• PostgreSQL: Documentation: 9.4: Performance Tips
• PostgreSQL: Documentation: 9.4: Replication
• PostgreSQL: Documentation: 9.4: The Statistics Collector
• Lock Monitoring - PostgreSQL wiki
• PgTune
• Improving PostgreSQL performance on AWS EC2
• Number Of Database Connections - PostgreSQL wiki
• PgBouncer - lightweight connection pooler for PostgreSQL
• How much RAM is PostgreSQL using?
• Database Isolation Levels And Their Effects on Performance and Scalability - High Scalability -
• PostgreSQL: Documentation: 9.4: Continuous Archiving and Point-in-Time Recovery (PITR)
• Zero to PostgreSQL streaming replication in 10 mins | Greg Reinacker's Weblog
• 12-Step Program for Scaling Web Applications on PostgreSQL
• omniti-labs/pgtreats · GitHub

Trouble shooting

• Re: How to monitor locks (max_pred_locks_per_transaction)?

out of shared memory
HINT:  You might need to increase max_pred_locks_per_transaction.

The total 'SIReadLock' count must be less than max_pred_locks_per_transaction * max_connections.

SELECT * FROM pg_locks WHERE mode = 'SIReadLock';

watch -n 3 "psql -c \"SELECT * FROM pg_locks WHERE mode='SIReadLock';\" -L SIReadLock.log"
grep -nEo '([0-9]+) rows' SIReadLock.log | sort -rn | head -n 1

remaining connection slots are reserved for non-replication superuser connections

select usename, client_addr, count(*) from pg_stat_activity 
group by client_addr, usename;