postgre.rst
author Oleksandr Gavenko <gavenkoa@gmail.com>
Mon, 22 Feb 2016 12:41:52 +0200
changeset 1903 901e7394849f
parent 1711 379096db1a5b
child 1905 fba288d59662
permissions -rw-r--r--
Decrease intent to increase space usage on mobile.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
1683
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     1
.. -*- coding: utf-8; -*-
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     2
.. include:: HEADER.rst
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     3
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     4
==========
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     5
 Postgre.
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     6
==========
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     7
.. contents::
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     8
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
     9
Installing on Debian.
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    10
=====================
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    11
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    12
Install and create new user and database::
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    13
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    14
  $ sudo apt-get install postgresql postgresql-client
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    15
  $ sudo su - postgres
1708
f1924a82b246 Update instruction.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1703
diff changeset
    16
  % psql
1683
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    17
  postgres=# CREATE USER "mypguser" WITH PASSWORD 'mypguserpass';
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    18
  postgres=# CREATE DATABASE "mypgdatabase" OWNER "mypguser";
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    19
  postgres=# \q
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    20
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    21
Connect as user ``mypguser`` to new database::
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    22
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    23
  $ su - mypguser
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    24
  $ psql mypgdatabase
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    25
1709
f34c6bc973cb Create local host superuser.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1708
diff changeset
    26
In order to create local host superuser::
f34c6bc973cb Create local host superuser.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1708
diff changeset
    27
f34c6bc973cb Create local host superuser.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1708
diff changeset
    28
  $ sudo su - postgres
f34c6bc973cb Create local host superuser.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1708
diff changeset
    29
  $ createuser --superuser USER
f34c6bc973cb Create local host superuser.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1708
diff changeset
    30
  $ exit
f34c6bc973cb Create local host superuser.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1708
diff changeset
    31
  $ sudo -u USER psql
f34c6bc973cb Create local host superuser.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1708
diff changeset
    32
1683
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    33
..
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    34
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    35
  https://wiki.debian.org/PostgreSql
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    36
    Debian wiki instructions.
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    37
1701
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    38
List databases, schemas and tables.
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    39
===================================
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    40
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    41
Default database is ``postgres``.
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    42
1709
f34c6bc973cb Create local host superuser.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1708
diff changeset
    43
To list databases and database locales::
1701
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    44
1702
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    45
  $ psql -U pgadmin -l
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    46
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    47
or::
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    48
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    49
  => SELECT datname FROM pg_database WHERE datistemplate = false;
1701
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    50
  => \l
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    51
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    52
To switch databases::
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    53
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    54
  => \connect NAME
1684
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    55
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    56
Schemas::
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    57
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    58
  => select schema_name from information_schema.schemata;
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    59
  => select nspname from pg_catalog.pg_namespace;
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    60
  => \dn *
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    61
1701
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    62
To list all tables in the current database::
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    63
1702
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    64
  => SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
1701
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    65
  => \dt
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    66
1703
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    67
Set default schema.
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    68
===================
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    69
::
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    70
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    71
  set search_path to NAME;
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    72
1710
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    73
Database, table, index size.
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    74
============================
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    75
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    76
Database size::
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    77
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    78
  SELECT pg_database_size('geekdb');  -- in bytes
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    79
  SELECT pg_size_pretty(pg_database_size('dbname'));
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    80
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    81
List of databases sizes::
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    82
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    83
  \l+
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    84
1711
379096db1a5b List tables sizes
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1710
diff changeset
    85
List tables sizes::
379096db1a5b List tables sizes
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1710
diff changeset
    86
379096db1a5b List tables sizes
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1710
diff changeset
    87
  \d+
379096db1a5b List tables sizes
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1710
diff changeset
    88
1710
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    89
Table total size (with indexes)::
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    90
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    91
  SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename'));
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    92
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    93
Sole table size (without indexes and other)::
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    94
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    95
  SELECT pg_size_pretty(pg_relation_size('schemaname.tablename'));
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    96
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    97
Largest table in the PostgreSQL database::
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    98
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
    99
  SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   100
1700
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   101
Using psql client.
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   102
==================
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   103
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   104
Using password file ``~/.pgpass``::
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   105
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   106
  # comment
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   107
  hostname:port:database:username:password
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   108
  hostname:port:*:username:password
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   109
  hostname:*:*:username:password
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   110
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   111
Connect by::
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   112
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   113
  $ psql -U $USER -h $HOST  $SCHEMA
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
   114
1710
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   115
How to view execution plan::
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   116
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   117
  EXPLAIN query;
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   118
  EXPLAIN ANALYZE query;
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   119
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   120
How to redirect the output of query to a file::
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   121
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   122
  \o output_file
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   123
  SELECT * FROM pg_class;
601c31753686 Database, table, index size.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1709
diff changeset
   124