Postgresql

From campisano.org
Jump to navigation Jump to search

Vademecum

apt-get install postgresql
ln -s /etc/init.d/postgresql /srv/config/init.d/
root@host:~# ln -s ../../../init.d/postgresql /srv/config/this/init.d/S50_postgresql
root@host:~# ln -s ../../../init.d/postgresql /srv/config/this/init.d/K50_postgresql


Set the client to show text-wrapped query results

root@host:~# su - postgres
postgres@host:~$ psql -U postgres
postgres=# \pset format wrapped
postgres=# -- DO YOUR QUERIES
postgres=# \q

System query

List users

root@host:~# su - postgres
postgres@host:~$ psql
postgres=# SELECT usename FROM pg_user; # or \dg
postgres=# \q

List databases

root@host:~# su - postgres
postgres@host:~$ psql -U postgres -l

or

root@host:~# su - postgres
postgres@host:~$ psql -U postgres
postgres=# SELECT datname FROM pg_database; # or \l
postgres=# \q

Show database size

root@host:~# su - postgres
postgres@host:~$ psql -U postgres
postgres=# SELECT pg_size_pretty(pg_database_size('NAME_DB'));
postgres=# \q

Show all databases sizes

root@host:~# su - postgres
postgres@host:~$ psql -U postgres
postgres=# SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;
postgres=# \q

List db tables

root@host:~# su - postgres
postgres@host:~$ psql -U postgres -d name_db
postgres=# SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; # or \d
postgres=# \q

List table columns

root@host:~# su - postgres
postgres@host:~$ psql -U postgres -d name_db
postgres=# SELECT column_name FROM information_schema.columns WHERE table_name ='db_table'; # or \d db_table
postgres=# \q

Show table size

root@host:~# su - postgres
postgres@host:~$ psql -U postgres
postgres=# SELECT pg_size_pretty(pg_total_relation_size('db_table'));
postgres=# \q

Show all tables sizes

root@host:~# su - postgres
postgres@host:~$ psql -U postgres
postgres=# SELECT table_catalog || '.' || table_schema || '.' || table_name, pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) FROM information_schema.tables ORDER BY pg_total_relation_size(table_schema || '.' || table_name) DESC;
postgres=# \q

List blocking query and kill them!

  • From

- https://wiki.postgresql.org/wiki/Lock_Monitoring

- http://stackoverflow.com/questions/11291456/heroku-postgres-terminate-hung-query-idle-in-transaction


To find blocked processes, working for Postgresql version >= 9.2

root@host:~# su - postgres
postgres@host:~$ psql -U postgres -d name_db
postgres=# \pset format wrapped
postgres=# SELECT DISTINCT
       blocking_locks.pid                   AS blocking_pid,
       blocking_activity.usename            AS b_user,
       blocking_activity.query_start        AS b_start,
       blocking_locks.relation::regclass    AS b_relation,
       blocking_activity.query              AS b_statement,
       blocking_activity.waiting            AS b_wait,
       blocking_activity.state              AS b_state,
       waiting_locks.pid                    AS waiting_pid,
       waiting_activity.usename             AS w_user,
       waiting_activity.query_start         AS w_start,
       waiting_locks.relation::regclass     AS w_relation,
       waiting_activity.query               AS w_statement,
       waiting_activity.waiting             AS w_wait,
       waiting_activity.state               AS w_state
  FROM pg_catalog.pg_locks          waiting_locks
  JOIN pg_catalog.pg_stat_activity  waiting_activity    ON waiting_activity.pid = waiting_locks.pid
  JOIN pg_catalog.pg_locks          blocking_locks      ON blocking_locks.locktype = waiting_locks.locktype
   AND blocking_locks.DATABASE IS NOT DISTINCT FROM waiting_locks.DATABASE
   AND blocking_locks.relation IS NOT DISTINCT FROM waiting_locks.relation
   AND blocking_locks.page IS NOT DISTINCT FROM waiting_locks.page
   AND blocking_locks.tuple IS NOT DISTINCT FROM waiting_locks.tuple
   AND blocking_locks.virtualxid IS NOT DISTINCT FROM waiting_locks.virtualxid
   AND blocking_locks.transactionid IS NOT DISTINCT FROM waiting_locks.transactionid
   AND blocking_locks.classid IS NOT DISTINCT FROM waiting_locks.classid
   AND blocking_locks.objid IS NOT DISTINCT FROM waiting_locks.objid
   AND blocking_locks.objsubid IS NOT DISTINCT FROM waiting_locks.objsubid
   AND blocking_locks.pid != waiting_locks.pid
 JOIN pg_catalog.pg_stat_activity   blocking_activity   ON blocking_activity.pid = blocking_locks.pid
WHERE NOT waiting_locks.GRANTED
ORDER BY b_start DESC;
postgres=# \q
postgres@host:~$ exit
root@host:~#

To find blocked processes, working for Postgresql version < 9.2

root@host:~# su - postgres
postgres@host:~$ psql -U postgres -d name_db
postgres=# \pset format wrapped
postgres=# SELECT DISTINCT
       blocking_locks.pid                   AS blocking_pid,
       blocking_activity.usename            AS b_user,
       blocking_activity.query_start        AS b_start,
       blocking_locks.relation::regclass    AS b_relation,
       blocking_activity.current_query      AS b_statement,
       blocking_activity.waiting            AS b_wait,
       --blocking_activity.state              AS b_state,
       waiting_locks.pid                    AS waiting_pid,
       waiting_activity.usename             AS w_user,
       waiting_activity.query_start         AS w_start,
       waiting_locks.relation::regclass     AS w_relation,
       waiting_activity.current_query       AS w_statement,
       waiting_activity.waiting             AS w_wait
       --waiting_activity.state              AS w_state
  FROM pg_catalog.pg_locks          waiting_locks
  JOIN pg_catalog.pg_stat_activity  waiting_activity    ON waiting_activity.procpid = waiting_locks.pid
  JOIN pg_catalog.pg_locks          blocking_locks      ON blocking_locks.transactionid = waiting_locks.transactionid AND blocking_locks.pid != waiting_locks.pid
  JOIN pg_catalog.pg_stat_activity  blocking_activity                  ON blocking_activity.procpid = blocking_locks.pid
 WHERE NOT waiting_locks.GRANTED
 ORDER BY b_start DESC;
postgres=# \q
postgres@host:~$ exit
root@host:~#

Kill a postgresql process by PID

root@host:~# su - postgres
postgres@host:~$ psql -U postgres -d name_db
postgres=# SELECT pg_terminate_backend(PID);
postgres=# \q
postgres@host:~$ exit
root@host:~#

Administration

Create user

root@host:~# su - postgres
postgres@host:~$ psql -U postgres
postgres=# CREATE ROLE db_user LOGIN PASSWORD 'db_pass';


Create super user

root@host:~# su - postgres
postgres@host:~$ psql -U postgres
postgres@host:~$ psql -U postgres
postgres=# CREATE ROLE superuser WITH LOGIN PASSWORD 'superuser' SUPERUSER;


Create database

root@host:~# su - postgres
postgres@host:~$ psql -U postgres
postgres=# CREATE DATABASE name_db WITH ENCODING 'UTF8' OWNER "db_user" TEMPLATE template0;


Test user

root@host:~# psql -h 127.0.0.1 -U db_user -W -d name_db


Backup and restore

A single database

  • At host
root@host_src:~# su - postgres
postgres@host_src:~# pg_dump -h localhost -U postgres name_db > dbname-dump.sql
  • At destination
root@host_des:~# su - postgres
postgres@host_des:~# psql -h localhost -U postgres -d name_db -E < dbname-dump.sql

The database name_db will not be created by this command, so you must create it yourself from template0 before executing psql (e.g., with createdb -T template0 name_db).


  • Example:

on source host

postgres@host_src:~$ pg_dump -h localhost -U postgres name_db > dbname-dump.sql

on destination host

postgres@host_dst:~$ psql -h localhost -U postgres
postgres=# CREATE ROLE db_user LOGIN PASSWORD 'db_pass';
CREATE ROLE
postgres=# CREATE DATABASE name_db WITH ENCODING 'UTF8' OWNER "db_user" TEMPLATE template0;
CREATE DATABASE
postgres=# \q
postgres@host_dst:~$ psql -h localhost -U postgres -E name_db < dbname-dump.sql


  • Note: a better (secure) way is use the owner of database

on source host

postgres@host_src:~$ pg_dump -U postgres name_db --no-owner > dbname-dump.dump.sql

on destination host

postgres@host_dst:~$ psql -h localhost -U db_user -d name_db -E < dbname-dump.dump.sql

Backup and Recovery all databases

From PostgreSQL: Documentation: Manuals: Backup and Restore


  • Backup
root@host:~# su - postgres
postgres@host:~$ pg_dumpall > all-dump.sql

or

root@host:~# su - postgres
postgres@host:~$ pg_dumpall | gzip > all-dump.sql.gz


  • Recovery
root@host:~# su - postgres
postgres@host:~$ psql -E < all-dump.sql


A database table

  • Dump
pg_dump -U db_user -t table_name -f dbname-tabledump.sql name_db


  • Restore
psql -E -U db_user -d name_db < dbname-tabledump.sql


Maintenance

postgres@108:~$psql -U postgres
psql (8.4.21)
Type "help" for help.

postgres=# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
   datname    |  size   
--------------+---------
 template0    | 5192 kB
 postgres     | 5288 kB
 template1    | 5280 kB
(3 rows)

postgres=# \q
postgres@108:~$ vacuumdb --analyze --verbose template1
[...]
postgres@108:~$ reindexdb template1
[...]
postgres@108:~$ psql -U postgres
psql (8.4.21)
Type "help" for help.

postgres=# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
   datname    |  size   
--------------+---------
 template1    | 5280 kB
 template0    | 5192 kB
 postgres     | 5288 kB
(3 rows)

postgres=# \q

postgres@108:~$ 



Tools

Diff tools

from http://stackoverflow.com/questions/4804779/how-to-check-difference-between-two-databases-in-postgressql


Model tools

Pgmodeler

From http://pgmodeler.com.br/

  • Features:

- Extract diagram model from database connection

- Diff betweeen two databases

- Apply changes (and diff changes)


  • Installation:
sudo apt-get install g++ pkg-config qt5-qmake qt5-default qtchooser libpq-dev libxml2-dev
wget https://github.com/pgmodeler/pgmodeler/archive/v0.8.1.tar.gz
tar -xzvf v0.8.1.tar.gz
cd pgmodeler-0.8.1/
qmake PREFIX=$HOME/pgmodeler pgmodeler.pro
make
make install
$HOME/pgmodeler/bin/pgmodeler


Links