postgresql常用命令

mac2024-04-22 32

环境：Ubuntu 16.04 LTS 数据库版本：9.6.6

注意：PostgreSQL中的不同类型的权限有SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,CREATE,CONNECT,TEMPORARY,EXECUTE 和 USAGE。

1. 登录PG数据库

以管理员身份 postgres 登陆，然后通过

#psql -U postgres #sudo -i -u postgres $ psql xc_hzh_linan #登录xc_hzh_linan数据库 $ psql -U postgres test #以postgres用户身份，登录test数据库 psql -h localhost -p 5432 -U postgress testdb

界面效果为:

postgres@ubuntu:~$ psql psql (9.5.19) Type "help" for help. postgres=# 简单说一下pgsql 的相关命令

使用反斜线作为命令前缀.

退出 \q 列出所有的数据库 \l 列出所有的数据库的大小 \l+ 更改当前连接的数据库 \c 列出当前数据库的连接信息 \connect 列出当前数据库和连接的详细信息 \conninfo 查看当前数据库里面的表和拥有者和表大小 \dt + 展示所有用户 \dg 查看所有表名的列表 \d 获取表结构 \d a 展示所有用户 \du 查看t_sms表的结构 \d t_sms 展示数据库里面的所有的表 \dt 列出所有的数据库的详细信息（包括数据库大小和字符格式） \l+ 显示用户访问权限。 \z或\dp 显示所有可设置的访问权限 \h GRAN 显示用户的对所有数据库表的详细访问权限 \dp或者\z 确认当前连接的用户为超级用户postgres，且该用户后创建角色和数据库的权限等 #select current_user; 在超级用户连接postgres后，设置不允许普通用户a连接数据库 #alter role a nologin; 使用普通用户a连接数据库正常 #\c highgo a 查看当前连接的用户名： foo=#select * from current_user; 或 foo=#select user; 查看所有用户名： foo=#\du 数据库表的备份与恢复： pg_dump -h 192.168.2.242 -U postgres -p 5432 -c --if-exists -t t_sms dev_huishishuju > t_sms.db 远程备份

使用案例：

查看每个表的大小

2、创建用户以及相关权限设置

创建用户

create role zhaobsh; create user wzq with password '123456'; #设置用户，并设置密码

更改用户密码

alter role zhaobsh with password 'Test6530'; or \password zhaobsh ALTER USER postgres WITH PASSWORD 'postgres';

注：

密码postgres要用引号引起来命令最后有分号

修改数据库属主

alter database "GSCloud1903" owner to zhaobsh; ALTER ROLE user_4 WITH CREATEROLE; /*赋予创建角色的权限*/ alter database "GSCloud1903" rename to zhaobsh; --修改schema的名称

授权给用户 PostgreSQL中预定义了许多不同类型的内置权限，如：SELECT、INSERT、UPDATE、DELETE、RULE、REFERENCES、TRIGGER、CREATE、TEMPORARY、EXECUTE和USAGE。

grant all on database "GSCloud1903" to zhaobsh; #赋予用户zhaobsh数据库所有权限 grant UPDATE ON database "GSCloud1903" to zhaobsh; # 赋予用户zhaobsh数据库更新权限 /* 赋给用户表的所有权限 */ GRANT ALL ON tablename TO user; /* 赋给用户数据库的所有权限 */ GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser; /* 撤销用户权限 */ REVOKE privileges ON tablename FROM user;

连接用户和数据库使用创建的用户能登录查看相应数据库，则可进行下一步。

[root@localhost ~]# psql -h localhost -U monitoring -p 54321 wiseucmsg

3、撤销用户权限

撤销用户 runoob权限：

=# REVOKE ALL ON COMPANY FROM runoob;

删除用户：

runoobdb=# drop user runoob;

设置是 supseruser 以及登录权限

alter user zhaobsh superuser login

修改指定用户指定权限

alter user postgres superuser createrole createdb replication; 注意：如果出现 FATAL： role '...' is not permitted to log in. 的错误 alter user postgres superuser createrole createdb replication login;

4、查看用户权限

1、查看某用户的表权限

select * from information_schema.table_privileges where grantee='user_name';

2、查看usage权限表

select * from information_schema.usage_privileges where grantee='user_name';

3、查看存储过程函数相关权限表

select * from information_schema.routine_privileges where grantee='user_name';

4、建用户授权

create user user_name; alter user user_name with password ''; alter user user_name with CONNECTION LIMIT 20;#连接数限制

创建数据库

CREATE DATABASE dbname; postgres=# create database dbtest owner username; -- 创建数据库指定所属者 CREATE DATABASE

或者

$ createdb -h localhost -p 5432 -U postgres runoobdb password ******

将数据库得权限，全部赋给某个用户

postgres=# grant all on database dbtest to username; -- 将dbtest所有权限赋值给username GRANT 设置是 supseruser 以及登录权限 alter user zhaobsh superuser login

5、修改linux系统中postgres用户的密码

PostgreSQL会创建一个默认的linux用户postgres，修改该用户密码的方法如下：步骤一：删除用户postgres的密码

sudo passwd -d postgres

步骤二：设置用户postgres的密码

sudo -u postgres passwd

系统提示输入新的密码

Enter new UNIX password: Retype new UNIX password: passwd: password updated successfully

6、使用pgAdmin客户端工具创建数据库

pgAdmin 工具提供了完整操作数据库的功能：

7、数据库的导入导出

导入整个数据库

psql -U username databasename < /data/dum.sql -- 用户名和数据库名 pg_dump ga_zj_taizhou.sql | psql wang_wang

或者

进入具体的数据库导入 postgres@ubuntu:~$ psql psql (9.6.6) Type "help" for help. postgres=# \c wang_wang You are now connected to database "wang_wang" as user "postgres". wang_wang=# \i ga_zj_taizhou.sql

注意：因为是从其他地方备份出来的sql文件，从一个数据库导入另一台数据库可能会报下面的错误

\i /root/test.sql #导入数据库数据 psql:ga_zj_taizhou.sql:1408597: ERROR: role "pm" does not exist psql:ga_zj_taizhou.sql:1408598: ERROR: role "laoyw" does not exist

解决办法：格式：create user 自定义用户名称 superuser;

postgres=# create user root superuser ;

然后\q退出数据库，再实现psql -d your_db -f sql_path 例如：

# psql -d mz -f /usr/mydownloads/proj_llmrs.sql

8、忘记postgres用户密码怎么办？

环境：

Ubuntu 16.04 postgres : apt-get install postgresql 9.5版本

sudo vim /etc/postgresql/9.5/main/pg_hba.conf

local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5

重启postgressql服务。

~$ systemctl restart postgresq

免密码登录

localhost:~$ psql -U postgres -h 127.0.0.1 psql (9.5.12) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. postgres=#

修改密码并退出

postgres=# alter user postgres with password '123'; ALTER ROLE postgres=# \q

修改回认证并退出： trust改为md5

sudo vim /etc/postgresql/9.5/main/pg_hba.conf

# IPv4 local connections: host all all 127.0.0.1/32 trust

重启并登陆：

parallels@parallels-vm:~$ systemctl restart postgresql parallels@parallels-vm:~$ psql -U postgres -h 127.0.0.1 Password for user postgres: psql (9.5.12) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help.

Ubuntu系统

创建新用户

root@ubuntu:~# useradd wang root@ubuntu:~# passwd wang Enter new UNIX password: Retype new UNIX password: passwd: password updated successfully

常见报错：没有家目录,新用户登陆错误

root@ubuntu:~# su - wang No directory, logging in with HOME=/ $

Linux授予某个用户对某个文件夹的读写权限

$ sudo chmod -R 755 /data/data1 $ chown -R user1.user1 1.txt 查看目录的权限 data# ls -l

备份数据从外网拷贝到本地 scp -r *.sql zhaobsh@192.187.100.1:/data/data1

导入数据登录数据库 postgres@dbmaser:~$ psql -U wsgjjkzg -d t_gj_tzy < /data/data1/t_gj_tzy.20191029235826.sql

查看导入的数据 su - postgresl \c GSCloud1903 #切换到GSCloud1903 postgres=# \c t_gj_tzy You are now connected to database “t_gj_tzy” as user “postgres”.

\c dt+ #查看当前数据库里面的表和拥有者和表大小

清空数据库中所有数据

//删除public模式以及模式里面所有的对象 DROP SCHEMA public CASCADE; //创建public模式 CREATE SCHEMA public;

清除全表，建议使用truncate

2种方法都只删数据，保留表结构

方法一，适用数据量较小的情况

delete from tablename;

方法二，适合删除大量数据，速度快

TRUNCATE TABLE tablename; 若该表有外键，要用级联方式删所有关联的数据 TRUNCATE TABLE tablename CASCADE; test03=# select * from test; id | info | crt_time ----+------+---------- (0 rows)

注意：

//当表没有其他关系时 TRUNCATE TABLE tablename; //当表中有外键时，要用级联方式删所有关联的数据 TRUNCATE TABLE tablename CASCADE;

postgres数据库中删除指定的数据的sql语句

查询数据库中default_table表中的name字段为空的条件

SELECT * from default_table WHERE name is null;

删除数据库中default_table表的name字段为空的数据

DELETE FROM default_table WHERE name is null;

创建索引语句

create index i_jdbc_sql_record_zh01 on t_jdbc_sql_record(c_bh_group,dt_zxsj,N_CHECKSTATUS,C_PSSQL_HASH); alter table t_jdbc_sql_content add constraint t_jdbc_sql_content_pkey primary key (C_BH);

异步提交和unlogged table

-- 异步提交,更改完重启数据库 alter system set synchronous_commit to off; -- unlogged table create unlogged table t_jdbc_sql_record ... create unlogged table t_jdbc_sql_content

POSTGRESQL 数据库导入导出

导入整个数据库 psql -U postgres(用户名) 数据库名(缺省时同用户名) < /data/dum.sql 导出整个数据库 1.导出数据库：方式一：pg_dump -U postgres -f c:\db.sqlpostgis 方式二：pg_dump -Upostgres postgis > c:\db.sql pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) >/data/dum.sql 导出某个表 pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) -t table(表名) >/data/dum.sql 导入具体表： psql -d postgis -f c:\ dump.sqlpostgres 压缩方法一般用dump导出数据会比较大，推荐使用xz压缩压缩方法 xz dum.sql 会生成 dum.sql.xz 的文件 xz压缩数据倒数数据库方法 xzcat /data/dum.sql.xz | psql -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)

常见问题：

1、

postgres@ubuntu:~$ psql -U wzq -d template0 psql: FATAL: Peer authentication failed for user "wzq"

其中peer authentication 比较注目，很明显，错误和权限有关联。

参考链接PostgreSQL 修改设置数据库的默认用户以及权限. ：http://www.cnblogs.com/jinanxiaolaohu/p/11452472.html：https://blog.csdn.net/weixin_30265103/article/details/101908371

PostgreSQL清空表并保留表结构、清空数据库还原数据库为新建时的状态的方法：https://blog.csdn.net/u013719339/article/details/79627010

PostgreSQL 如何实现批量更新、删除、插入：https://yq.aliyun.com/articles/74420

postgresql 删除单个表的数据：https://blog.csdn.net/kmust20093211/article/details/47734975

postgresql批量删除表：https://blog.csdn.net/weixin_30384217/article/details/98387285

修改postgres密码：https://blog.csdn.net/qiuyoujie/article/details/89853029

postgres安装和创建用户和创建数据库： https://www.cnblogs.com/weihengblog/p/10082570.html

最新回复(0)