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_pass' SUPERUSER;

Create a backup user

On modern postgresql, you can create an user with read access to all databases:

CREATE ROLE backup WITH IN ROLE pg_read_all_data LOGIN PASSWORD 'backup_pass';

Create readonly user

root@host:~# su - postgres
postgres@host:~$ psql -U postgres postgres
postgres=# CREATE ROLE readonly_user LOGIN PASSWORD 'readonly_pass';
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

from http://www.linuxinsight.com/optimize_postgresql_database_size.html

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

apgdiff

Liquibase (Cross DBMS)

WbSchemaDiff (Cross DBMS)

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

Postgresql

Contents

Install

Notes

System query

List users

List databases

Show database size

Show all databases sizes

List db tables

Show table size

Show all tables sizes

List table columns

Show connections

List current queries by time

List blocking query and kill them!

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

To find blocked processes, working for Postgresql version < 9.2

Kill a postgresql process by PID

Administration

Create user

Create super user

Create a backup user

Create readonly user

Create database

Test user

Backup and restore

A single database

Just the database users/roles

Backup and Recovery all databases

A database table

Maintenance

Tools

Diff tools

Model tools

Pgmodeler

Links

Navigation menu

Postgresql

Install

Notes

System query

List users

List databases

Show database size

Show all databases sizes

List db tables

Show table size

Show all tables sizes

List table columns

Show connections

List current queries by time

List blocking query and kill them!

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

To find blocked processes, working for Postgresql version < 9.2

Kill a postgresql process by PID

Administration

Create user

Create super user

Create a backup user

Create readonly user

Create database

Test user

Backup and restore

A single database

Just the database users/roles

Backup and Recovery all databases

A database table

Maintenance

Tools

Diff tools

Model tools

Pgmodeler

Links

Navigation menu

Search