Ambari postgres数据库操作

1.先切换用户

[root@anode1 ~]# su postgres
bash-4.2$

2.用psql命令登录PostgreSQL控制台

默认的用户和数据库是postgres

bash-4.2$ psql
could not change directory to "/root"
psql (9.2.18)
Type "help" for help.
postgres=# 

3.进入ambari库

切换数据库,相当于mysql的use dbname

postgres=# \c ambari
You are now connected to database "ambari" as user "postgres".

4.授予ambari用户对ambari库的权限

ambari=# grant postgres to ambari;
GRANT ROLE
ambari=# grant all privileges on database ambari to ambari;
GRANT

5.退出

ambari=# \q
You have new mail in /var/spool/mail/root

6.重新使用amabri用户进入postgresql(默认密码bigdata)

bash-4.2$ psql -U ambari
could not change directory to "/root"
Password for user ambari: 
psql (9.2.18)
Type "help" for help.

7.列举数据库

列举数据库，相当于mysql的show databases

ambari=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 ambari    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | ambari=CTc/postgres
 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/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

8.列举表

列举表，相当于mysql的show tables(当前已经在ambari数据库)

ambari=> \dt
                     List of relations
 Schema |             Name              | Type  |  Owner   
--------+-------------------------------+-------+----------
 ambari | adminpermission               | table | postgres
 ambari | adminprincipal                | table | postgres
 ambari | adminprincipaltype            | table | postgres
 ambari | adminprivilege                | table | postgres
 ambari | adminresource                 | table | postgres
 ambari | adminresourcetype             | table | postgres
 ambari | alert_current                 | table | postgres
 ambari | alert_definition              | table | postgres
 ambari | alert_group                   | table | postgres
 ambari | alert_group_target            | table | postgres
 ambari | alert_grouping                | table | postgres
 ambari | alert_history                 | table | postgres
 ambari | alert_notice                  | table | postgres
 ambari | alert_target                  | table | postgres
 ambari | alert_target_states           | table | postgres
 ambari | ambari_operation_history      | table | postgres
 ambari | ambari_sequences              | table | postgres
 ambari | artifact                      | table | postgres
 ambari | blueprint                     | table | postgres
 ambari | blueprint_configuration       | table | postgres
 ambari | blueprint_setting             | table | postgres
 ambari | cluster_version               | table | postgres
 ambari | clusterconfig                 | table | postgres
 ambari | clusterconfigmapping          | table | postgres
 ambari | clusterhostmapping            | table | postgres
 ambari | clusters                      | table | postgres
 ambari | clusterservices               | table | postgres
 ambari | clusterstate                  | table | postgres
ambari | confgroupclusterconfigmapping | table | postgres
 ambari | configgroup                   | table | postgres
 ambari | configgrouphostmapping        | table | postgres
 ambari | execution_command             | table | postgres
 ambari | extension                     | table | postgres
 ambari | extensionlink                 | table | postgres
 ambari | groups                        | table | postgres
 ambari | host_role_command             | table | postgres
 ambari | host_version                  | table | postgres
 ambari | hostcomponentdesiredstate     | table | postgres
 ambari | hostcomponentstate            | table | postgres
 ambari | hostconfigmapping             | table | postgres
 ambari | hostgroup                     | table | postgres
 ambari | hostgroup_component           | table | postgres
 ambari | hostgroup_configuration       | table | postgres
 ambari | hosts                         | table | postgres
 ambari | hoststate                     | table | postgres
 ambari | kerberos_descriptor           | table | postgres
 ambari | kerberos_principal            | table | postgres
 ambari | kerberos_principal_host       | table | postgres
 ambari | key_value_store               | table | postgres
 ambari | members                       | table | postgres
 ambari | metainfo                      | table | postgres
 ambari | permission_roleauthorization  | table | postgres
 ambari | qrtz_blob_triggers            | table | postgres
 ambari | qrtz_calendars                | table | postgres
 ambari | qrtz_cron_triggers            | table | postgres
 ambari | qrtz_fired_triggers           | table | postgres
 ambari | qrtz_job_details              | table | postgres
 ambari | qrtz_locks                    | table | postgres
 ambari | qrtz_paused_trigger_grps      | table | postgres
 ambari | qrtz_scheduler_state          | table | postgres
 ambari | qrtz_simple_triggers          | table | postgres
 ambari | qrtz_simprop_triggers         | table | postgres
 ambari | qrtz_triggers                 | table | postgres
 ambari | remoteambaricluster           | table | postgres
 ambari | remoteambariclusterservice    | table | postgres
 ambari | repo_version                  | table | postgres
ambari | request                       | table | postgres
 ambari | requestoperationlevel         | table | postgres
 ambari | requestresourcefilter         | table | postgres
 ambari | requestschedule               | table | postgres
 ambari | requestschedulebatchrequest   | table | postgres
 ambari | role_success_criteria         | table | postgres
 ambari | roleauthorization             | table | postgres
 ambari | servicecomponent_history      | table | postgres
 ambari | servicecomponentdesiredstate  | table | postgres
 ambari | serviceconfig                 | table | postgres
 ambari | serviceconfighosts            | table | postgres
 ambari | serviceconfigmapping          | table | postgres
 ambari | servicedesiredstate           | table | postgres
 ambari | setting                       | table | postgres
 ambari | stack                         | table | postgres
 ambari | stage                         | table | postgres
 ambari | topology_host_info            | table | postgres
 ambari | topology_host_request         | table | postgres
 ambari | topology_host_task            | table | postgres
 ambari | topology_hostgroup            | table | postgres
 ambari | topology_logical_request      | table | postgres
 ambari | topology_logical_task         | table | postgres
 ambari | topology_request              | table | postgres
 ambari | upgrade                       | table | postgres
 ambari | upgrade_group                 | table | postgres
 ambari | upgrade_item                  | table | postgres
 ambari | users                         | table | postgres
 ambari | viewentity                    | table | postgres
 ambari | viewinstance                  | table | postgres
 ambari | viewinstancedata              | table | postgres
 ambari | viewinstanceproperty          | table | postgres
 ambari | viewmain                      | table | postgres
 ambari | viewparameter                 | table | postgres
 ambari | viewresource                  | table | postgres
 ambari | viewurl                       | table | postgres
 ambari | widget                        | table | postgres
 ambari | widget_layout                 | table | postgres
 ambari | widget_layout_user_widget     | table | postgres
(104 rows)

9.查看表结构

相当于desc tblname

ambari=> \d users
                                       Table "ambari.users"
        Column         |            Type             |                  Modifiers                  
-----------------------+-----------------------------+---------------------------------------------
 user_id               | integer                     | not null
 principal_id          | bigint                      | not null
 ldap_user             | integer                     | not null default 0
 user_name             | character varying(255)      | not null
 user_type             | character varying(255)      | not null default 'LOCAL'::character varying
 create_time           | timestamp without time zone | default now()
 user_password         | character varying(255)      | 
 active                | integer                     | not null default 1
 active_widget_layouts | character varying(1024)     | default NULL::character varying
Indexes:
    "pk_users" PRIMARY KEY, btree (user_id)
    "unq_users_0" UNIQUE CONSTRAINT, btree (user_name, user_type)
Foreign-key constraints:
    "fk_users_principal_id" FOREIGN KEY (principal_id) REFERENCES adminprincipal(principal_id)
Referenced by:
    TABLE "members" CONSTRAINT "fk_members_user_id" FOREIGN KEY (user_id) REFERENCES users(user_id)

ambari=> \d host_version
             Table "ambari.host_version"
     Column      |         Type          | Modifiers 
-----------------+-----------------------+-----------
 id              | bigint                | not null
 repo_version_id | bigint                | not null
 host_id         | bigint                | not null
 state           | character varying(32) | not null
Indexes:
    "pk_host_version" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_host_version_host_id" FOREIGN KEY (host_id) REFERENCES hosts(host_id)
    "fk_host_version_repovers_id" FOREIGN KEY (repo_version_id) REFERENCES repo_version(repo_version_id)

10.Select查询

ambari=> select * from host_version;
 id | repo_version_id | host_id |  state  
----+-----------------+---------+---------
  1 |               1 |       1 | CURRENT
  3 |               1 |       3 | CURRENT
  2 |               1 |       2 | CURRENT
(3 rows)

11.退出

ambari=> \q
You have new mail in /var/spool/mail/root
bash-4.2$ exit
exit
You have new mail in /var/spool/mail/root
[root@anode1 ~]# 

命令小结

• 查看所有库：\l
• 查看所有表：\dt
• 查看一个表的结构： \d 表名
• 换数据库 \c dbName