author | Oleksandr Gavenko <gavenkoa@gmail.com> |
Fri, 03 Apr 2015 10:05:03 +0300 | |
changeset 1708 | f1924a82b246 |
parent 1703 | a85cc266dbdf |
child 1709 | f34c6bc973cb |
permissions | -rw-r--r-- |
1683 | 1 |
.. -*- coding: utf-8; -*- |
2 |
.. include:: HEADER.rst |
|
3 |
||
4 |
========== |
|
5 |
Postgre. |
|
6 |
========== |
|
7 |
.. contents:: |
|
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 |
|
1708
f1924a82b246
Update instruction.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1703
diff
changeset
|
16 |
% psql |
1683 | 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 |
.. |
|
27 |
||
28 |
https://wiki.debian.org/PostgreSql |
|
29 |
Debian wiki instructions. |
|
30 |
||
1701
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
31 |
List databases, schemas and tables. |
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 |
|
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
34 |
Default database is ``postgres``. |
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
35 |
|
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
36 |
To list databases:: |
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
37 |
|
1702 | 38 |
$ psql -U pgadmin -l |
39 |
||
40 |
or:: |
|
41 |
||
42 |
=> SELECT datname FROM pg_database WHERE datistemplate = false; |
|
1701
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
43 |
=> \l |
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
44 |
|
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
45 |
To switch databases:: |
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
46 |
|
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
47 |
=> \connect NAME |
1684 | 48 |
|
49 |
Schemas:: |
|
50 |
||
51 |
=> select schema_name from information_schema.schemata; |
|
52 |
=> select nspname from pg_catalog.pg_namespace; |
|
53 |
=> \dn * |
|
54 |
||
1701
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
55 |
To list all tables in the current database:: |
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
56 |
|
1702 | 57 |
=> 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
|
58 |
=> \dt |
6163ba5907d4
To switch databases.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1700
diff
changeset
|
59 |
|
1703
a85cc266dbdf
Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1702
diff
changeset
|
60 |
Set default schema. |
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 |
|
a85cc266dbdf
Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1702
diff
changeset
|
64 |
set search_path to NAME; |
a85cc266dbdf
Set default schema.
Oleksandr Gavenko <gavenkoa@gmail.com>
parents:
1702
diff
changeset
|
65 |
|
1700 | 66 |
Using psql client. |
67 |
================== |
|
68 |
||
69 |
Using password file ``~/.pgpass``:: |
|
70 |
||
71 |
# comment |
|
72 |
hostname:port:database:username:password |
|
73 |
hostname:port:*:username:password |
|
74 |
hostname:*:*:username:password |
|
75 |
||
76 |
Connect by:: |
|
77 |
||
78 |
$ psql -U $USER -h $HOST $SCHEMA |
|
79 |