Ambari postgres数据库操作

1.先切换用户

[root@anode1 ~]# su postgresbash-4.2$

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

默认的用户和数据库是postgres

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

3.进入ambari库

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

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

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

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

5.退出

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

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

bash-4.2$ psql -U ambaricould 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 | postgresambari | 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 | postgresambari | 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 varyingIndexes:    "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 nullIndexes:    "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=> \qYou have new mail in /var/spool/mail/rootbash-4.2$ exitexitYou have new mail in /var/spool/mail/root[root@anode1 ~]# 

命令小结

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