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