postgre.rst
author Oleksandr Gavenko <gavenkoa@gmail.com>
Tue, 24 Mar 2015 17:23:15 +0200
changeset 1703 a85cc266dbdf
parent 1702 d2b977ccbf4f
child 1708 f1924a82b246
permissions -rw-r--r--
Set default schema.
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
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    16
  postgres=# CREATE USER "mypguser" WITH PASSWORD 'mypguserpass';
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    17
  postgres=# CREATE DATABASE "mypgdatabase" OWNER "mypguser";
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    18
  postgres=# \q
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    19
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    20
Connect as user ``mypguser`` to new database::
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    21
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    22
  $ su - mypguser
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    23
  $ psql mypgdatabase
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    24
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    25
..
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    26
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    27
  https://wiki.debian.org/PostgreSql
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    28
    Debian wiki instructions.
d99b4a277679 Installing on Debian.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
diff changeset
    29
1701
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    30
List databases, schemas and tables.
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    31
===================================
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    32
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    33
Default database is ``postgres``.
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    34
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    35
To list databases::
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    36
1702
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    37
  $ psql -U pgadmin -l
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    38
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    39
or::
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    40
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    41
  => SELECT datname FROM pg_database WHERE datistemplate = false;
1701
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    42
  => \l
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    43
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    44
To switch databases::
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    45
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    46
  => \connect NAME
1684
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    47
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    48
Schemas::
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    49
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    50
  => select schema_name from information_schema.schemata;
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    51
  => select nspname from pg_catalog.pg_namespace;
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    52
  => \dn *
88f1d11d2adf List all schemas.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1683
diff changeset
    53
1701
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    54
To list all tables in the current database::
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    55
1702
d2b977ccbf4f List databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1701
diff changeset
    56
  => 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
    57
  => \dt
6163ba5907d4 To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1700
diff changeset
    58
1703
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    59
Set default schema.
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    60
===================
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    61
::
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    62
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    63
  set search_path to NAME;
a85cc266dbdf Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1702
diff changeset
    64
1700
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    65
Using psql client.
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    66
==================
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    67
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    68
Using password file ``~/.pgpass``::
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    69
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    70
  # comment
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    71
  hostname:port:database:username:password
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    72
  hostname:port:*:username:password
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    73
  hostname:*:*:username:password
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    74
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    75
Connect by::
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    76
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    77
  $ psql -U $USER -h $HOST  $SCHEMA
f987c277f760 Using psql client.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents: 1684
diff changeset
    78