ubuntu16.04下postgresql常用命令，及图形化管理界面pgadmin3

快来打我* 2022-04-12 14:12 1698阅读 0赞

1.创建用户

create user jiraadmin with password '123456';     创建jiraadmin用户并设置密码为123456

alter role jiraadmin with superuser;        将用户赋予超级管理员权限

create database jira;      创建jira数据库

2.登录数据库

psql -U jiraadmin -W jira

\-U:以哪个用户登录

\-W：登录哪个数据库；

psql -U postgres -W transaction

直接回车以postgres身份登录到transaction数据库

psql -U postgres

以postgres身份登录到默认数据库（即postgres数据库）

3.常用命令

连接数据库, 默认的用户和数据库是postgres  
psql -U user -d dbname

切换数据库,相当于mysql的use dbname  
\\c dbname  
列举数据库，相当于mysql的show databases  
\\l  
列举表，相当于mysql的show tables  
\\dt  
查看表结构，相当于desc tblname,show columns from tbname  
\\d tblname

\\di 查看索引

创建数据库：   
create database \[数据库名\];   
删除数据库：   
drop database \[数据库名\];    
\*重命名一个表：   
alter table \[表名A\] rename to \[表名B\];   
\*删除一个表：   
drop table \[表名\];

\*在已有的表里添加字段：   
alter table \[表名\] add column \[字段名\] \[类型\];   
\*删除表中的字段：   
alter table \[表名\] drop column \[字段名\];   
\*重命名一个字段：    
alter table \[表名\] rename column \[字段名A\] to \[字段名B\];   
\*给一个字段设置缺省值：    
alter table \[表名\] alter column \[字段名\] set default \[新的默认值\];  
\*去除缺省值：    
alter table \[表名\] alter column \[字段名\] drop default;   
在表中插入数据：   
insert into 表名 (\[字段名m\],\[字段名n\],......) values (\[列m的值\],\[列n的值\],......);   
修改表中的某行某列的数据：   
update \[表名\] set \[目标字段名\]=\[目标值\] where \[该行特征\];   
删除表中某行数据：   
delete from \[表名\] where \[该行特征\];   
delete from \[表名\];--删空整个表   
创建表：   
create table (\[字段名1\] \[类型1\] ;,\[字段名2\] \[类型2\],......<,primary key (字段名m,字段名n,...)>;);

\\copyright     显示 PostgreSQL 的使用和发行条款  
\\encoding \[字元编码名称\]  
                 显示或设定用户端字元编码  
\\h \[名称\]      SQL 命令语法上的说明，用 \* 显示全部命令  
\\prompt \[文本\] 名称  
                 提示用户设定内部变数  
\\password \[USERNAME\]  
                 securely change the password for a user  
\\q             退出 psql

可以使用pg\_dump和pg\_dumpall来完成。比如备份sales数据库：   
pg\_dump drupal>/opt/Postgresql/backup/1.bak

\------------------------------------------------------分割线----------------------------------------------------------

psql 访问数据库涉及5个要素：  
\-U 用户名  
\-W 交互方式输入密码  
\-h 主机名  
\-p 端口号  
\-d 数据库名

一般客户端访问需要指定以上5个要素，类似于Oracle客户端 tnsnames.ora文件中的相关设定。

服务端通过psql访问数据库如果不指定，通常会查找用户环境变量的相关设置（譬如.bash\_profile文件或创建的.mylocalenv文件）。

当然验证过程要受到pg\_hba.conf文件的限制；要不要输入密码关联 method字段值md5或trust（可信的不需要密码验证）。

另外，需要说明的一点是：-W 选项的含义是必须以交互的方式输入密码，而不是在选项后边直接输入明文密码，区别于Oracle 或DB2的输入密码明文，这一点应该是出于安全性的考虑。

示例：

\[postgres@hgdb01 data\]$ vi pg\_hba.conf  
\# TYPE  DATABASE        USER            ADDRESS                 METHOD  
\# "local" is for Unix domain socket connections only  
local   all             all                                    md5  
host    all             postgres        192.168.137.0/24        md5  
host    all             all             ::1/128                 md5

\[postgres@hgdb01 data\]$ psql -U postgres -W -h localhost -d testdb -p 1921  
Password for user postgres:   \#此处必须是交互式输入密码  
psql (9.5.7)  
Type "help" for help.

testdb=\# \\q

这里的-u -p -d选项都可以省略，因为环境变量有所设置！

\[postgres@hgdb01 data\]$ cat  ~/.bash\_profile  
PATH=$PATH:$HOME/.local/bin:$HOME/bin  
export PATH  
\#add  
export PGPORT=1921  
export PGDATA=/pgdata/data  
export LANG=en\_US.utf8  
export PGHOME=/usr/local/pgsql957  
export LD\_LIBRARY\_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD\_LIBRARY\_PATH  
export DATE=\`date +"%Y%m%d%H%M"\`  
export PATH=$PGHOME/bin:$PATH:.  
export MANPATH=$PGHOME/share/man:$MANPATH  
export PGUSER=postgres  
export PGHOST=$PGDATA  
export PGDATABASE=testdb  
alias rm='rm -i'  
alias ll='ls -lh'

\[postgres@hgdb01 data\]$ psql -U postgres  
Password for user postgres:   \#此处必须是交互式输入密码  
psql (9.5.7)  
Type "help" for help.  
\-----------------------------------------------------------------分割线---------------------------------------------------------------------

**一、安装并配置，并设置远程登陆的用户名和密码**

1、安装postgreSQL

**sudo apt-get update**

**sudo apt-get install postgresql-9.4**

*  在Ubuntu下安装Postgresql后，会自动注册为服务，并随操作系统自动启动。
 *  在Ubuntu下安装Postgresql后，会自动添加一个名为postgres的操作系统用户，密码是随机的。并且会自动生成一个名字为postgres的数据库，用户名也为postgres，密码也是随机的。

2、修改postgres数据库用户的密码为123456

打开客户端工具（psql）

**sudo -u postgres psql**

*  其中，sudo -u postgres 是使用postgres 用户登录的意思
 *  PostgreSQL数据默认会创建一个postgres的数据库用户作为数据库的管理员，密码是随机的

**postgres=\# ALTER USER postgres WITH PASSWORD '123456'; **

*  postgres=\#为PostgreSQL下的命令提示符，--注意最后的分号；

3、退出PostgreSQL psql客户端

**postgres=\# \\q**

4、修改ubuntu操作系统的postgres用户的密码（密码要与数据库用户postgres的密码相同）

切换到root用户

su root

删除PostgreSQL用户密码

**sudo passwd -d postgres**

*  passwd -d 是清空指定用户密码的意思

设置PostgreSQL系统用户的密码

**sudo -u postgres passwd**

按照提示，输入两次新密码

*  输入新的 UNIX 密码
 *  重新输入新的 UNIX 密码
 *  passwd：已成功更新密码

5、修改PostgresSQL数据库配置实现远程访问

**vi /etc/postgresql/9.4/main/postgresql.conf**

1.监听任何地址访问，修改连接权限

**\#listen\_addresses = 'localhost' 改为 listen\_addresses = '\*'**

2.启用密码验证

**\#password\_encryption = on 改为 password\_encryption = on**

**vi /etc/postgresql/9.4/main/pg\_hba.conf**

在文档末尾加上以下内容

**host all all 0.0.0.0 0.0.0.0 md5**

6、重启服务

**/etc/init.d/postgresql restart**

7、5432端口的防火墙设置

5432为postgreSQL默认的端口

**iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT**

**二、内部登录，管理数据库、新建数据库、用户和密码**

1、登录postgre SQL数据库

**psql -U postgres -h 127.0.0.1**

2、创建新用户zhangps，但不给建数据库的权限

**postgres=\# create user "zhangps" with password '123456' nocreatedb;**

*  用户名处是双引号

3、建立数据库，并指定所有者

**postgres=\#create database "testdb" with owner = "zhangps";**

**三、外部登录，管理数据库、新建数据库、用户和密码**

1、在外部命令行的管理命令，创建用户pencil

**sudo -u postgres createuser -D -P pencil**

*  输入新的密码:
 *  再次输入新的密码:

2、建立数据库(tempdb)，并指定所有者为（pencil）

**sudo -u postgres createdb -O pencil tempdb**

*  \-O设定所有者为pencil

\--------------------------------------------------------分割线-----------------------------------------------------------------

连接数据库, 默认的用户和数据库是postgres  
psql -U user -d dbname

\\di 查看索引

可以使用pg\_dump和pg\_dumpall来完成。比如备份sales数据库：   
pg\_dump drupal>/opt/Postgresql/backup/1.bak

附带一些指定给postgresql用户的常用命令:

###  默认用户 ###

postgres安装完成后，会自动在[操作系统][Link 1]和postgres[数据库][Link 2]中分别创建一个名为`postgres`的用户以及一个同样名为`postgres`的数据库。

###  登录 ###

*  方式1:指定参数登录

psql -U username -d database_name -h host -W

*  1

> 参数含义: `-U`指定用户 `-d`要连接的数据库 `-h`要连接的主机 `-W`提示输入密码。

*  方式2:切换到postgres同名用户后登录

1.  `su username`
2.  `psql`

*  1
 *  2

> 当不指定参数时`psql`使用操作系统当前用户的用户名作为postgres的登录用户名和要连接的数据库名。所以在PostgreSQL安装完成后可以通过以上方式登录。

###  创建用户 ###

*  方式1:在系统命令行中使用`createuser`命令中创建

createuser username

*  1

*  方式2:在PostgresSQL命令行中使用`CREATE ROLE`指令创建

CREATE ROLE rolename;

*  1

*  方式3:在PostgresSQL命令行中使用`CREATE USER`指令创建

CREATE USER username;

*  1

> `CREATE USER`和`CREATE ROLE`的区别在于，`CREATE USER`指令创建的用户默认是有登录权限的，而`CREATE ROLE`没有。

*  `\du` 指令显示用户和用户的用户属性

###  创建用户时设定用户属性 ###

*  基本语法格式

CREATE ROLE role_name WITH optional_permissions;

*  1

*  示例:在创建用户时设定登录权限。

CREATE ROLE username WITH LOGIN;

*  1

*  可以通过`\h CREATE ROLE`指令查看全部可设置的管理权限

###  修改用户属性 ###

*  修改权限的命令格式

ALTER ROLE username WITH attribute_options;

*  1

*  例如:可通过以下方式禁止用户登录

ALTER ROLE username WITH NOLOGIN;

*  1

###  设置访问权限 ###

*  语法格式如下:

GRANT permission_type ON table_name TO role_name;

*  1

*  实例:

1.  `GRANT UPDATE ON demo TO demo_role; --赋予demo_role demo表的update权限`
2.  `GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role; --赋予demo_role所有表的SELECT权限`

*  1
 *  2

*  特殊符号:`ALL`代表所访问权限，`PUBLIC`代表所有用户

1.  `GRANT ALL ON demo TO demo_role; --赋给用户所有权限`
2.  `GRANT SELECT ON demo TO PUBLIC; --将SELECT权限赋给所有用户`

*  1
 *  2

*  `\z`或`\dp`指令显示用户访问权限。
 *  `\h GRANT`显示所有可设置的访问权限

###  撤销用户访问权限 ###

*  语法格式如下:

REVOKE permission_type ON table_name FROM user_name;

*  1

> 其中`permission_type`和`table_name`含义与`GRANT`指令中相同。

###  用户组 ###

在postgres中用户实际上是`role`，同时组也是`role`。 包含其他`role`的`role`就是组。

*  创建组示例:

1.  `CREATE ROLE temporary_users;`
2.  `GRANT temporary_users TO demo_role;`
3.  `GRANT temporary_users TO test_user;`

*  1
 *  2
 *  3

*  切换ROLE

1.  `SET ROLE role_name; --切换到role_name用户`
2.  `RESET ROLE; --切换回最初的role`

*  1
 *  2

*  `INHERIT`权限：该属性使组成员拥有组的所有权限

ALTER ROLE test_user INHERIT;

*  1

###  删除用户和组 ###

删除用户和组很简单:

1.  `DROP ROLE role_name;`
2.  `DROP ROLE IF EXISTS role_name;`

*  1
 *  2

> 删除组role只会删除组的role本身，组的成员并不会被删除

\----------------------------------------------------------分割线-----------------------------------------------------------------

apt安装postgresql-9.5和图形管理工具pgadmin3

### 主程序 ###

sudo apt install postgresql-9.5

### 数据库图形界面 ###

sudo apt install pgadmin3

##    Ubuntu终端命令创建数据库 ##

###    修改默认用户postgres的密码 ###

postgres用户还没设置密码, 需要修改密码, 在终端输入  
sudo -u postgres psql template1  
继续输入  
ALTER USER postgres with encrypted password 'your\_password';  
\\q 退出  
就修改好了, 重启postgresql  
sudo systemctl restart postgresql.service

###    使用postgre官方的图形化管理工具pgadmin3 ###

pgadmin3  
点击左上角的插头图标(连接数据库)  
输入如下参数:  
属性     属性值  
名称     随便  
主机名     localhost  
服务     空  
维护数据库     postgres  
用户     postgres  
密码     你刚刚的密码

右键”服务器组–>服务器–>数据库”,如图

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MzkyNTg5_size_16_color_FFFFFF_t_70][]      Java程序链接postgreSQL ###

网上很多资料了, 也可以参考鄙人的另一篇文章  
http://blog.csdn.net/caib1109/article/details/51576384

\---------------------  
作者：程序员小蔡  
来源：CSDN  
原文：https://blog.csdn.net/caib1109/article/details/51582663  
版权声明：本文为博主原创文章，转载请附上博文链接！

[Link 1]: http://lib.csdn.net/base/operatingsystem
[Link 2]: http://lib.csdn.net/base/mysql
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MzkyNTg5_size_16_color_FFFFFF_t_70]: /images/20220412/e06ad5ecd95547af815b9cb4699ce1c3.png