Oracle DBA Forum  

Вернуться   Oracle DBA Forum > Установка и настройка > Инсталляция PostgreSQL > Инсталляция PostgreSQL

Ответ
 
Опции темы Опции просмотра
  #1  
Старый 11.03.2012, 11:38
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Команды psql

PSQL

# su - postgres
$ createdb mydatabase
$ psql mydatabase

Код:
psql (9.1.3)
Type "help" for help.

mydatabase=#
mydatabase-# \h

Код:
Available help:
  ABORT                            DECLARE
  ALTER AGGREGATE                  DELETE
  ALTER COLLATION                  DISCARD
  ALTER CONVERSION                 DO
  ALTER DATABASE                   DROP AGGREGATE
  ALTER DEFAULT PRIVILEGES         DROP CAST
  ALTER DOMAIN                     DROP COLLATION
  ALTER EXTENSION                  DROP CONVERSION
  ALTER FOREIGN DATA WRAPPER       DROP DATABASE
  ALTER FOREIGN TABLE              DROP DOMAIN
  ALTER FUNCTION                   DROP EXTENSION
  ALTER GROUP                      DROP FOREIGN DATA WRAPPER
  ALTER INDEX                      DROP FOREIGN TABLE
  ALTER LANGUAGE                   DROP FUNCTION
  ALTER LARGE OBJECT               DROP GROUP
  ALTER OPERATOR                   DROP INDEX
  ALTER OPERATOR CLASS             DROP LANGUAGE
  ALTER OPERATOR FAMILY            DROP OPERATOR
  ALTER ROLE                       DROP OPERATOR CLASS
  ALTER SCHEMA                     DROP OPERATOR FAMILY
  ALTER SEQUENCE                   DROP OWNED
  ALTER SERVER                     DROP ROLE

mydatabase-# \h create database

Код:
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace ]
           [ CONNECTION LIMIT [=] connlimit ] ]


mydatabase-# \?

Код:
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \h [NAME]              help on syntax of SQL commands, * for all commands
  \q                     quit psql

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

Informational
  (options: S = show system objects, + = additional detail)
mydatabase-#
mydatabase-# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \h [NAME]              help on syntax of SQL commands, * for all commands
  \q                     quit psql

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

// Покажет представления и таблицы
mydatabase-# \d

// Покажет структуру таблицы, PK и FK
mydatabase-# \d table_name


// Покажет представления
mydatabase-# \dv


// Показать таблицы
mydatabase-# \dt



mydatabase-# \conninfo
You are connected to database "mydatabase" as user "postgres" via socket in "/tmp" at port "5432".


\pset format html
\pset format aligned

\pset border 0

// Отображать null как null
\pset null null

// Указать заголовок к таблице.
\pset title employee
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 12.03.2012 в 10:51.
Ответить с цитированием
  #2  
Старый 11.03.2012, 11:58
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию

$ psql mydatabase

-- CREATE USER scott PASSWORD 'tiger' NOINHERIT LOGIN;
CREATE USER scott WITH PASSWORD 'tiger';

GRANT ALL PRIVILEGES ON DATABASE mydatabase to scott;

CREATE SCHEMA mySchema AUTHORIZATION scott;

quit

psql -U scott -d mydatabase

CREATE TABLE mySchema.records (id INT, txt TEXT);

INSERT INTO mySchema.records VALUES(1, 'This is mySchema');



==========================

// Показать имеющиеся базы данных
mydatabase=> SELECT datname FROM pg_database;

Код:
  datname
------------
 template1
 template0
 postgres
 mydatabase
(4 rows)

mydatabase=> \l

Код:
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privile
ges
------------+----------+----------+-------------+-------------+-----------------
------
 mydatabase | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
     +
            |          |          |             |             | postgres=CTc/pos
tgres+
            |          |          |             |             | scott=CTc/postgr
es
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
     +
            |          |          |             |             | postgres=CTc/pos
tgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
     +
            |          |          |             |             | postgres=CTc/pos
tgres
(4 rows)

// Показать имеющиеся схемы и их владельцев.
mydatabase=> \dn

Код:
   List of schemas
   Name   |  Owner
----------+----------
 myschema | scott
 public   | postgres
(2 rows)


mydatabase=> select * from pg_user;

Код:
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  |
 valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+
----------+-----------
 postgres |       10 | t           | t        | t         | t       | ******** |
          |
 scott    |    16385 | f           | f        | f         | f       | ******** |
          |
(2 rows)


mydatabase=# CREATE GROUP myGroup;
CREATE ROLE


mydatabase=# select * from pg_group;

Код:
 groname | grosysid | grolist
---------+----------+---------
 mygroup |    16393 | {}
(1 row)
mydatabase=# GRANT SELECT, INSERT ON TABLE mytable TO scott;

mydatabase=# ALTER GROUP myGroup ADD USER scott;




// SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE, and ALL PRIVILEGES
GRANT SELECT ON mySchema.records TO GROUP myGroup;





mydatabase=> \d myschema.*

Код:
   Table "myschema.records"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 txt    | text    |

mydatabase=> SHOW search_path;
search_path
----------------
"$user",public
(1 row)



SET search_path TO myschema;

mydatabase=> \d

Код:
         List of relations
  Schema  |  Name   | Type  | Owner
----------+---------+-------+-------
 myschema | records | table | scott
(1 row)

To permanently alter the search path set on each connection, use

ALTER USER scott SET search_path TO myschema;

This change will only take effect after reconnecting to the database.


// Привилегии
mydatabase-# \dp

Код:
                              Access privileges
  Schema  |  Name   | Type  |  Access privileges  | Column access privileges
----------+---------+-------+---------------------+--------------------------
 myschema | records | table | scott=arwdDxt/scott+|
          |         |       | mygroup=r/scott     |
(1 row)
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 13.03.2012 в 16:09.
Ответить с цитированием
  #3  
Старый 11.03.2012, 16:45
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию

Привилегии:

r - SELECT (Can read data from the object).
a - INSERT (Can insert data into the object).
w - UPDATE (Can change data in the object).
d - DELETE (Can delete data from the object).
R - RULE (Can create a rule on the table).
x - REFERENCES (Can create a foreign key to a table. Need this on both sides of the key).
t - TRIGGER (Can create a trigger on the table).
T - TEMPORARY (Can create a temporary table).
X - EXECUTE ( Can run the function).
U - USAGE (Can use the procedural language).
ALL - (All appropriate privileges. For tables, this equates to arwdRxt)
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 12.03.2012 в 10:52.
Ответить с цитированием
  #4  
Старый 12.03.2012, 10:32
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию

phppgadmin
http://phppgadmin.sourceforge.net/doku.php?id=start



http://www.davidpashley.com/articles...istration.html
http://sql-info.de/postgresql/schemas.html
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 12.03.2012 в 10:52.
Ответить с цитированием
Ответ

Опции темы
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.

Быстрый переход


Текущее время: 02:52. Часовой пояс GMT +3.


Powered by vBulletin®