Postgresql

From campisano.org
Jump to navigation Jump to search

Install

apt-get install postgresql

Notes

  • To connect locally using the system user:
root@host:~# su - postgres
postgres@host:~$ psql -U postgres
postgres=# -- DO YOUR QUERIES
postgres=# \q
  • To connect remotely with a password protected instance:

--password (or -W) tell the command to ask for a password 

psql --host=127.0.0.1 --port=5432 --username=postgres --dbname=postgres --password

PGPASSWORD env var tell the command to use the var content as password 

PGPASSWORD=pass1234 psql --host=127.0.0.1 --port=5432 --username=postgres --dbname=postgres --no-password
  • To connect to the read-write instance of a postgres cluster:
psql 'postgresql://HOST1:PORT1,HOST2:PORT2,HOST3:PORT3/DATABASE?target_session_attrs=read-write' -U USER
  • To setup 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

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 AS database, table_schema || '.' || table_name AS name, pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS size FROM information_schema.tables ORDER BY pg_total_relation_size(table_schema || '.' || table_name) DESC;
postgres=# \q

List table columns

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

Show connections

  • show connection info
SELECT * FROM
(SELECT count(*) used FROM pg_stat_activity) q1,
(SELECT setting::int res_for_super FROM pg_settings WHERE name='superuser_reserved_connections') q2,
(SELECT setting::int max_conn FROM pg_settings WHERE name='max_connections') q3;
  • count connections by user
SELECT usename, count(*) AS count FROM pg_stat_activity GROUP BY usename ORDER BY count DESC;

List current queries by time

SELECT pid, datname, usename, EXTRACT(epoch FROM now() - xact_start) AS seconds, state, query
  FROM pg_stat_activity
 WHERE pid <> pg_backend_pid()
   AND state IN ('idle in transaction', 'active')
 ORDER BY xact_start ASC;

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 readonly user

root@host:~# su - postgres
postgres@host:~$ psql -U postgres postgres
postgres=# CREATE ROLE readonly_user LOGIN PASSWORD XXX;
postgres=# GRANT CONNECT ON DATABASE database_to_query TO readonly_user;
postgres=# \q
postgres@host:~$ psql -U postgres postgres database_to_query
database_to_query=# GRANT USAGE ON SCHEMA public TO readonly_user;
database_to_query=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
database_to_query=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
postgres=# quit=# \q

Create database

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

NOTE: the default template used to create a new database is template1, which can contain customized data admins want to be added in any new database. But if you dump a db created from template1 and restore in a new db created from template1, the customization can be done twice leading to conflicts, so the empty db to use as a destination for the restore should be created from template0. Source https://www.postgresql.org/docs/current/manage-ag-templatedbs.html .

Test user

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

Backup and restore

The simplest mode is to skip backup of database permissions. The follow scripts can help:

  • A database full backup
#!/bin/bash
#

show_usage()
{
    echo "Usage:    "`basename $0`" <host> <port> <user> <database>"
    echo "Example:  "`basename $0`" 127.0.0.1 5432 postgres postgres"
}

if test -z "$1" -o -z "$2" -o -z "$3" -o -z "$4"
then
    show_usage >&2
    exit 1
fi

HOST="$1"
PORT="$2"
USER="$3"
DB="$4"

pg_dump --clean --if-exists --no-owner --no-acl -h "${HOST}" -p "${PORT}" -U "${USER}" "${DB}" > "${DB}_all_`date +%Y-%m-%d_%H-%M-%S`.sql"



# End
  • A database structure only:
#!/bin/bash
#

show_usage()
{
    echo "Usage:    "`basename $0`" <host> <port> <user> <database>"
    echo "Example:  "`basename $0`" 127.0.0.1 5432 postgres postgres"
}

if test -z "$1" -o -z "$2" -o -z "$3" -o -z "$4"
then
    show_usage >&2
    exit 1
fi

HOST="$1"
PORT="$2"
USER="$3"
DB="$4"

pg_dump --schema-only --no-owner --no-acl -h "${HOST}" -p "${PORT}" -U "${USER}" "${DB}" > "${DB}_structure_`date +%Y-%m-%d_%H-%M-%S`.sql"



# End
  • A database single table data only:
#!/bin/bash
#

show_usage()
{
    echo "Usage:    "`basename $0`" <host> <port> <user> <database> <table>"
    echo "Example:  "`basename $0`" 127.0.0.1 5432 postgres postgres pg_catalog.pg_type"
}

if test -z "$1" -o -z "$2" -o -z "$3" -o -z "$4" -o -z "$5"
then
    show_usage >&2
    exit 1
fi

HOST="$1"
PORT="$2"
USER="$3"
DB="$4"
TABLE="$5"

pg_dump --data-only --no-owner --no-acl -h "${HOST}" -p "${PORT}" -U "${USER}" --table="${TABLE}" "${DB}" > "${DB}_table_${TABLE}_`date +%Y-%m-%d_%H-%M-%S`.sql"



# End
  • You can restore such backups without database permission simply using psql tool, specifying the destination user and database (that should be empty or just created) like in the follow example:
# database creation
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
# database restore
postgres@host_dst:~$ psql -h localhost -U db_user name_db < dbname-dump.sql

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


Just the database users/roles

on source host 

postgres@host_src:~$ pg_dumpall -h localhost -U postgres name_db --globals-only --clean --if-exists > dumpall_globalsonly.sql

note: it contains the postgres user permission too, you can avoid changes to such user removing the related lines in the dump: 

sed -i '/^DROP ROLE.*postgres/d;/^CREATE ROLE.*postgres/d;/^ALTER ROLE.*postgres/d' dumpall_globalsonly.sql

on destination host 

postgres@host_dst:~$ psql -E --set ON_ERROR_STOP=on -h localhost -U postgres -d postgres -f dumpall_globalsonly.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

Retrieved from "https://www.campisano.org/mediawiki/index.php?title=Postgresql&oldid=456"