1 .. -*- coding: utf-8; -*-

     2 

     3 ==========

     4  Postgre.

     5 ==========

     6 .. contents::

     7    :local:

     8 

     9 Installing on Debian.

    10 =====================

    11 

    12 Install and create new user and database::

    13 

    14   $ sudo apt-get install postgresql postgresql-client

    15   $ sudo su - postgres

    16   % psql

    17   postgres=# CREATE USER "mypguser" WITH PASSWORD 'mypguserpass';

    18   postgres=# CREATE DATABASE "mypgdatabase" OWNER "mypguser";

    19   postgres=# \q

    20 

    21 Connect as user ``mypguser`` to new database::

    22 

    23   $ su - mypguser

    24   $ psql mypgdatabase

    25 

    26 In order to create local host superuser::

    27 

    28   $ sudo su - postgres

    29   $ createuser --superuser USER

    30   $ exit

    31   $ sudo -u USER psql

    32 

    33 ..

    34 

    35 https://wiki.debian.org/PostgreSql

    36   Debian wiki instructions.

    37 

    38 List databases, schemas and tables.

    39 ===================================

    40 

    41 Default database is ``postgres``.

    42 

    43 To list databases and database locales::

    44 

    45   $ psql -U pgadmin -l

    46 

    47 or::

    48 

    49   => SELECT datname FROM pg_database WHERE datistemplate = false;

    50   => \l

    51 

    52 To switch databases::

    53 

    54   => \connect NAME

    55 

    56 Schemas::

    57 

    58   => select schema_name from information_schema.schemata;

    59   => select nspname from pg_catalog.pg_namespace;

    60   => \dn *

    61 

    62 To list all tables in the current database::

    63 

    64   => SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

    65   => \dt

    66 

    67 Set default schema.

    68 ===================

    69 ::

    70 

    71   set search_path to NAME;

    72 

    73 Database, table, index size.

    74 ============================

    75 

    76 Database size::

    77 

    78   SELECT pg_database_size('geekdb');  -- in bytes

    79   SELECT pg_size_pretty(pg_database_size('dbname'));

    80 

    81 List of databases sizes::

    82 

    83   \l+

    84 

    85 List tables sizes::

    86 

    87   \d+

    88 

    89 Table total size (with indexes)::

    90 

    91   SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename'));

    92 

    93 Sole table size (without indexes and other)::

    94 

    95   SELECT pg_size_pretty(pg_relation_size('schemaname.tablename'));

    96 

    97 Largest table in the PostgreSQL database::

    98 

    99   SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

   100 

   101 Using psql client.

   102 ==================

   103 

   104 Using password file ``~/.pgpass``::

   105 

   106   # comment

   107   hostname:port:database:username:password

   108   hostname:port:*:username:password

   109   hostname:*:*:username:password

   110 

   111 Connect by::

   112 

   113   $ psql -U $USER -h $HOST  $SCHEMA

   114 

   115 How to view execution plan::

   116 

   117   EXPLAIN query;

   118   EXPLAIN ANALYZE query;

   119 

   120 How to redirect the output of query to a file::

   121 

   122   \o output_file

   123   SELECT * FROM pg_class;

   124