1.先切换用户
代码语言：javascript复制[root@anode1 ~]# su postgres
bash-4.2$2.用psql命令登录PostgreSQL控制台
默认的用户和数据库是postgres
代码语言：javascript复制bash-4.2$ psql
could not change directory to "/root"
psql (9.2.18)
Type "help" for help.
postgres=# 3.进入ambari库
切换数据库,相当于mysql的use dbname
代码语言：javascript复制postgres=# c ambari
You are now connected to database "ambari" as user "postgres".4.授予ambari用户对ambari库的权限
代码语言：javascript复制ambari=# grant postgres to ambari;
GRANT ROLE
ambari=# grant all privileges on database ambari to ambari;
GRANT5.退出
代码语言：javascript复制ambari=# q
You have new mail in /var/spool/mail/root6.重新使用amabri用户进入postgresql(默认密码bigdata)
代码语言：javascript复制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
代码语言：javascript复制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数据库)
代码语言：javascript复制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
代码语言：javascript复制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查询
代码语言：javascript复制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.退出
代码语言：javascript复制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
