postgre安装和psql基础命令

最新推荐文章于 2024-07-25 11:35:55 发布

面对朝阳

最新推荐文章于 2024-07-25 11:35:55 发布

阅读量1.4k

点赞数

文章标签： postgresql

本文链接：https://blog.csdn.net/qq_38697743/article/details/131998259

版权

postgre安装以及相关配置

操作系统: CentOS 7

数据库: PostgreSQL 12

一、yum安装postgresql12

1、安装存储库rpm包

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2、安装客户端软件包

yum install postgresql12
yum install postgresql12-server （服务器软件包）
sudo yum install postgresql12-devel

二、postgre的初始化

1、初始化数据库

sudo /usr/pgsql-12/bin/postgresql-12-setup initdb 
#Initializing database ... OK

2、修改IP绑定

#修改配置文件
vi /var/lib/pgsql/12/data/postgresql.conf

#将监听地址修改为*
#默认listen_addresses配置是注释掉的，所以可以直接在配置文件开头加入该行
listen_addresses='*'

3、初始化数据库并启用自动启动

/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12

4、配置防火墙

firewall-cmd --permanent --add-port=5432/tcp  
firewall-cmd --permanent --add-port=80/tcp  
firewall-cmd --reload

6、修改用户密码

#切换用户，执行后提示符会变为 '-bash-4.2$'
   su - postgres  
#登录数据库，执行后提示符变为 'postgres=#'
   psql -U postgres 
#设置postgres用户密码为postgres
   ALTER USER postgres WITH PASSWORD 'postgres'  
# 退出数据库
    \q

[ 复制代码 ](javascript:void(0)😉

7、开启远程访问

vi /var/lib/pgsql/12/data/postgresql.conf
修改#listen_addresses = 'localhost'  为  listen_addresses='*'
当然，此处‘*’也可以改为任何你想开放的服务器IP

8、信任远程连接

vi /var/lib/pgsql/12/data/pg_hba.conf
修改如下内容，信任指定服务器连接
# IPv4 local connections:
host    all            all      127.0.0.1/32      trust
host    all            all      192.168.1.1/32（需要连接的服务器IP）  trust

9、操作

安装后的数据库data目录

/var/lib/pgsql/12/data

客户端程序目录

/usr/pgsql-12/bin

重启数据库

sudo service postgresql restart

如果重启不能执行，用如下命令

sudo /etc/init.d/postgresql start   # 开启
sudo /etc/init.d/postgresql stop    # 关闭
sudo /etc/init.d/postgresql restart # 重启

./pg_ctl -D /var/lib/pgsql/12/data start 
sudo systemctl stop postgresql-12
sudo systemctl start postgresql-12

查看服务是否启动

操作连接数据库

./psql -Upostgres -dpostgres

PG基础命令

扩展命令

postgres=# select name from pg_available_extensions;
查看当前服务器可用的Extension扩展列表

安装可用的Extension扩展

postgres=# create extension pg_stat_statements ;

删除Extension扩展，查看验证

postgres=# drop extension pg_stat_statements ;
 
postgres=# \dx
List of installed extensions

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

登陆命令

sudo su

[sudo] password for kerwin: #输入当前用户密码
New password: #输入root新密码
Retype new password: #再次输入root密码
passwd: password updated successfully #密码更新成功

sudo -u postgres psql
su postgres # 切换SQL用户登录
psql -U postgres # 空密码登录
alter user postgres with password '新密码';  #修改postgres 用户密码

select count(*) from pg_stat_activity
show max_connections;
select client_addr from pg_stat_activity where pid = pg_backend_pid()

一、建立数据库连接

接入PostgreSQL数据库:

psql -h IP地址 -p 端口 -U 数据库名

之后会要求输入数据库密码

二、访问数据库

1、列举数据库：\l
2、选择数据库：\c 数据库名
3、查看该某个库中的所有表：\dt
4、切换数据库：\c interface
5、查看某个库中的某个表结构：\d 表名
6、查看某个库中某个表的记录：select * from apps limit 1;
7、显示字符集：\encoding
8、退出psgl：\q

\dt

列出表名

SELECT tablename FROM pg_tables;
WHERE tablename NOT LIKE 'pg%'
AND tablename NOT LIKE 'sql_%'
ORDER BY tablename;

列出数据库名

\l
或
SELECT datname FROM pg_database;

切换数据库

\c 数据库名

1、通过命令行查询
\d 数据库 —— 得到所有表的名字
\d 表名 —— 得到表结构

2、通过SQL语句查询
"select * from pg_tables" —— 得到当前db中所有表的信息（这里pg_tables是系统视图）
"select tablename from pg_tables where schemaname='public'" —— 得到所有用户自定义表的名字（这里"tablename"字段是表的名字，"schemaname"是schema的名字。用户自定义的表，如果未经特殊处理，默认都是放在名为public的schema下）

数据库的关键操作:

1. 启动服务 2. 登录 3. 建立数据库 4. 建立表 5. 插入记录到表中 6. 更新/删除/查询/修改操作 7. 退出 8. 停止服务

在windows7中安装的postgresql默认使用GBK字符集,经常不能使用显示中文的数据表,解决办法:
postgres=# \encoding utf-8    // 设置客户端的字元集
postgres=# \encoding         // 显示客户端的字元集
postgres=# show client_encoding;   // 显示客户端的字元集
postgres=# show server_encoding;   // 显示服务器的字元集

启动服务:

net start postgresql-9.5

停止服务:

net stop postgresql-9.5

获取命令帮助:

c:\> psql --help

登录( 注意: postgres 是默认用户即管理员 ):

路径 psql -h 服务器 -U 用户名 -d 数据库 -p 端口地址 // -U 是大写
C:\> psql -h localhost -U postgres -p 5432      // 默认打开postgres数据库
C:\> psql -h 127.0.0.1 -U postgres -d fengdos -p 5432 // 打开fengdos数据库
C:\> psql -U postgres                 // 快速登录(全部使用默认设置)
// 使用某些有密码的用户的情况下, 会提示输入密码.
用户 postgres 的口令: ILoveYou     // 输入时不会显示任何字符
// 成功后显示:
psql (9.5.3)

---------------------------------------------------------

您正在使用psql, 这是一种用于访问PostgreSQL的命令行界面
键入：\copyright 显示发行条款
   \h 显示 SQL 命令的说明
   \? 显示 pgsql 命令的说明 (pgsql内部命令)
   \g 或者以分号(;)结尾以执行查询
   \q 退出注: 数据库名称区分大小写的。

---------------------------------------------------------

postgres=# \help     // 获取SQL命令的帮助,同 \h
postgres=# \quit     // 退出,同 \q
postgres=# \password dlf // 重新设置用户dlf的密码,然后需要 \q退出后才生效
c:\>psql exampledb < user.sql // 将user.sql文件导入到exampled数据库中
postgres=# \h select  // 精细显示SQL命令中的select命令的使用方法
postgres=# \l     // 显示所有数据库
postgres=# \dt     // 显示当前数据库中的所有表
postgres=# \d [table_name] // 显示当前数据库的指定表的表结构
postgres=# \c [database_name] // 切换到指定数据库,相当于use
postgres=# \du         // 显示所有用户
postgres=# \conninfo      // 显示当前数据库和连接信息
postgres=# \e  // 进入记事本sql脚本编辑状态(输入批命令后关闭将自动在命令行中执行)
postgres=# \di // 查看索引(要建立关联)
postgres=# \prompt [文本] 名称  // 提示用户设定内部变数
postgres=# \encoding [字元编码名称] // 显示或设定用户端字元编码
*可以将存储过程写在文本文件中aaa.sql,然后在psql状态下:
postgres=# \i aaa.sql  // 将aaa.sql导入(到当前数据库)
postgres=# \df      // 查看所有存储过程（函数）
postgres=# \df+ name   // 查看某一存储过程
postgres=# select version();      // 获取版本信息
postgres=# select usename from pg_user; // 获取系统用户信息
postgres=# drop User 用户名       // 删除用户

其它SQL命令通用如(标准化SQL语句):

*创建数据库：

create database [数据库名];

*删除数据库：

drop database [数据库名];

*创建表：

create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;);

*在表中插入数据：

insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);

*显示表内容:

select * from student;

*重命名一个表：

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;

*修改表中的某行某列的数据：

update [表名] set [目标字段名]=[目标值] where [该行特征];

*删除表中某行数据：

delete from [表名] where [该行特征];
delete from [表名];  // 删空整个表

*可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库：

pg_dump drupal>/opt/Postgresql/backup/1.bak

===================================================================================================

1.列出所有表名的查询语句

SELECT tablename FROM pg_tablesWHERE tablename NOT LIKE 'pg%'AND tablename NOT LIKE 'sql_%'ORDER BY tablename;

2.列出表中所有的数据

SELECT * FROM someTable;

3.执行外部脚本

#/opt/PostgreSQL/8. 3/bin/psql - Upostgres ;登陆到数据库的控制台界面postgres= # \i /root/db. sql   \i 命令用于执行一个外部的sql脚本文件。

4.导出数据库为外部的脚本

#/opt/PostgreSQL/8. 3/bin/ pg_dump - Upostgres - C - fdb. sql database``-C create  -f 是导出后的文件名

5.postgresql 插入16进制数

INSERT INTO tableAAA VALUES( x'0001f' : : integer, '鉴权' , 'Authority' )

6.使用 TG_RELNAME 报错ERROR: syntax error at or near “$1” at character

[引]http://www.dbmonster.com/Uwe/Forum.aspx/postgresql/2051/TG-RELNAME-problem``Perhaps you will get some idea if you read the document:37. 6. 4. Executing Dynamic Commands``改：执行动态语句`` EXECUTE 'INSERT INTO TG_RELNAME VALUES (NEW.start_time , NEW.id , NEW.end_time)';

psql 常用命令

a. \c tesdb1 - - 将当前连接的testdb数据库改变成 testdb1 。b . \q - - 断开与Postgres服务器的连接c . \l 列出所有数据库的名字  \l+ 列出所有数据库的名字以及字符集编码d. \d [ 名字] 描述表, 索引, 序列, 或者视图         列出表/索引/序列/视图/系统表  \d{t| i| s| v| S} [ 模式] ( 加 "+" 获取更多信息)         - - 列出表/索引/序列/视图/系统表  \d tablename - - 查看表的结构  \dt - - 列出数据库中所有表

8.在PostgreSQL中如何删除重复记录

在PostgreSQL中删除重复记录其实很简单，不论有多少行重复，只要在要删除重复记录的表中table加一列rownum字段( id为table表中的主键) ，类型设置为serial类型即可，然后执行sqldelete from deltest where rownum not in(select max(rownum) from deltest);最后删除列rownum即可

配置允许远程连接

以下10.0版本为例，配置为最大连接权限，若项目中用到请以实际安全策略为准。

修改监听

sudo vi /etc/postgresql/10/main/postgresql.conf

将 #listen_addresses = ‘localhost’ 的注释去掉并改为

listen_addresses = ‘*’

修改可访问用户的IP段

sudo vi /etc/postgresql/10/main/pg_hba.conf

在文件末尾添加： host all all 0.0.0.0 0.0.0.0 md5 ，表示允许任何IP连接

重启数据库

sudo /etc/init.d/postgresql restart

开启5432端口
上述允许远程连接的设置仅是postgre的设置，真正要让外部可连接，OS层也要设置，即开启相应端口，postgre默认用tcp 5432 端口进行连接，故开启5432端口即可：

若 firewalld 未安装，则先进行安装：

sudo apt install firewalld

允许 tcp 5432 端口

sudo firewall-cmd --permanent --add-port=5432/tcp

reload 防火墙，应用规则

sudo systemctl reload firewalld

what’s the pg_stat_activity

pg_stat_activity 是 PostgreSQL 内置的一个系统视图，是 PostgreSQL 实例维护的一个进程相关的视图，是实时变化的。

官方对他的定义是：每一行都表示一个系统进程，显示与当前会话的活动进程的一些信息，比如当前回话的状态和查询等。

主要字段

Column	Type	Description
`datid`	`oid`	后台连接的数据库id（OID）
`datname`	`name`	后台连接的数据库名
`pid`	`integer`	后代进程id（PID）
`usesysid`	`oid`	连接到后台的用户id
`usename`	`name`	连接到后台的用户名称
`application_name`	`text`	通过后台连接的应用程序名称
`client_addr`	`inet`	后台连接的IP地址。当该字段为空时，说明客户端是在服务器主机上通过Unix的socket协议直连，或是数据库内部进程（如autovacuum等）
`client_hostname`	`text`	根据client_addr字段，通过DNS解析获取的对应主机名称. 但仅当log_hostname被开启时才有效，并且只有通过IP协议连接时才能记录到主机名；
`client_port`	`integer`	后台连接使用的TCP端口号。当使用Unix-Socket协议连接时此值为-1。
`backend_start`	`timestamp with time zone`	进程开始的时间，即客户端连接到服务器的时刻。
`xact_start`	`timestamp with time zone`	进程的当前事务开始执行的时间。若当前无活动事务，该值为null。如果当前query是事务中的首次查询，此项值应与`query_start列相同`.
`query_start`	`timestamp with time zone`	当前事务中有在执行中的语句时，此值记录该语句开始执行的时间；若事务state不是active状态，此值记录的是上一次开始执行语句时间。（即每次开始执行新的语句时此值更新）
`state_change`	`timestamp with time zone`	记录进程中事务状态发生改变的时刻；
`waiting`	`boolean`	是否处于等待。当进程在等待解锁时此值为t，否则为false
`state`	`text`	表示后台进程的整体状态。可能的值有: `active（活动）`:进程正在执行某个语句`idle(空闲)`: 进程正在等待客户端的指令.`idle in transaction （事务空闲）`: 进程在处理事务过程中，但当前未执行任何语句.`idle in transaction (aborted)（事务空闲-退出）`:当事务中某个语句执行出错的情况外，其余情况与idle in transaction相同.`fastpath function call（快通道函数调用）`: 后台正在执行某个快通道函数.`disabled（禁用）`: This state is reported if track_activities is disabled in this backend.
`query`	`text`	后台进程最近（当前）执行的语句内容. 当状态是 `active时，它记录的是正在执行的语句内容；否则记录的是上一条语句执行的内容`

-- 查询闲置连接数。注意是否忘记关闭链接。 另外还可以查看连接数是不是过多等问题。
select count(*) from pg_stat_activity where state=‘idle’; 

-- 统计当前有多少活跃的客户端
select 
  count(*) 
from 
  pg_stat_activity 
where 
  not pid = pg_backend_pid();

–查询最大连接数
show max_connections;
– 释放空闲连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state=‘idle’;
-- 查看一个后端进程运行了多久，以及它当前是否在等待
select 
  pid,state,CURRENT_TIMESTAMP - least(query_start,xact_start) AS runtime,substr(query,1,25) AS current_query
from 
  pg_stat_activity
where 
  not pid = pg_backend_pid();

-- 慢查询
select
  pid, datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay, replace(query, chr(10), ' ') as query
from
    (select 
      pgsa.pid as pid, 
      pgsa.datname as datname, 
      pgsa.usename as usename, 
      pgsa.client_addr client_addr, 
      pgsa.application_name as application_name, 
      pgsa.state as state, pgsa.backend_start as backend_start, 
      pgsa.xact_start as xact_start, 
      extract(epoch from (now() - pgsa.xact_start)) as xact_stay, 
      pgsa.query_start as query_start, 
      extract(epoch from (now() - pgsa.query_start)) as query_stay , 
      pgsa.query as query
    from
      pg_stat_activity as pgsa 
    where 
      pgsa.state != 'idle' and pgsa.state != 'idle in transaction' and pgsa.state != 'idle in transaction (aborted)') 
idleconnections 
order by 
  query_stay 
desc 
limit 5;

关于找不到pg_ctl：

$ cd ~
$ vim .profile
PATH=$PATH:/usr/lib/postgresql/{version}/bin
export PATH
$ . ~/.profile

现在，您可以从外壳执行命令：

pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

项目需求中所用到的sql基本命令

update russia_polygon set way_codes=STDG_GriddingPolygon(russia_polygon.way) 
where russia_polygon.id >=27010000 and  russia_polygon.id <27011000;

select id,number from (
	select id as id,way_codes as way_codes,STDG_CodesNumber(russia_polygon.way_codes) as number
FROM russia_polygon 
where russia_polygon.id >=27000000)
as foo
where foo.number is null;

select way from russia_polygon
where russia_polygon.id >=26910000 and  russia_polygon.id <26920000;

select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname
from pg_locks a
join pg_class b on a.relation = b.oid
select * from pg_stat_activity



select * from (
select id as id,way as way,st_area(way) as area
from russia_polygon
where russia_polygon.id =25249838
) as foo order by foo.id ASC;

cd /usr/lib/systemd/system
systemctl stop postgresql-9.5.service
systemctl start postgresql-9.5.service

查看时区

select to_timestamp('2049-11-22 21:21:21.4','YYYY-MM-DD HH24:MI:SS.MS')
SET TIMEZONE='Asia/Shanghai';
show time zone;
show timezone;
select * from pg_timezone_names;
select NOW();
SELECT EXTRACT(epoch FROM NOW());
select to_timestamp(1669880664.837374)
select to_char(to_timestamp(1669882908.864311), 'YYYY-MM-DD HH24:MI:SS')；

总结：

timestamp和timestamptz类型的数据虽然都不保存时区信息，但是timestamptz却通过结合数据库设置的时区，session对应的时区以及timestamptz本身保存的数据，将正确的时间返回给客户端。也就是说从宏观上，timestamptz看起来确实像是保存了时区信息的timestamp数据，只不过这个时区是整个数据库设置的时区。
什么时候使用timestamptz类型，什么时候使用timestamp？

简单说针对简单应用，只有单一客户端（单体应用）或同类型多个应用访问数据库（比如多个微服务），如果数据库保存的时间是由客户端应用程序保证时区正确性，这时就可以使用timestamp。因为这时，不管数据库的时区是Etc/UTC还是’Asia/Shanghai’，数据库都不会对客户端传过来的数据做处理，只是原封不动的保存。select的时候再原封不动的返回。至于应用程序的客户端，比如一个新闻web应用的客户端是浏览器，如果希望根据浏览器所在区域的时区正确显示从数据库返回的时间，这是需要新闻web应用做转换，或者在时间上加上时区信息一起返回给浏览器客户端，由客户端做时间转换。

而如果有多种不类型客户端需要访问同一数据库，这个时候可以考虑使用timestamptz, 这样不管哪个客户端都可以根据当前session的时区正确获取对应的时间。

以上只是个人理解，可能有不准确的地方还望指正。

最后，需要说明的是，针对整个数据库的时区设置，个人认为作用不大。唯一的用处也许是使用postgresql命令行客户端psql连接数据库时，使session默认的时区设置成跟整个数据库时区一致，其他作用并不明显。因为如果你是使用例如DBeaver这种图形化数据库客户端工具，默认他会帮你把session的时区设置成DBeaver当前所在主机的时区，这样他显示timestamptz类型的数据时，也是完全没有问题的。

Postgresql 查看某列(字段)占用空间大小

例如查看表 users 中 email 列每一条记录占用空间大小:

SELECT pg_size_pretty(pg_column_size("email")::bigint) FROM users;

SELECT pg_size_pretty(sum（pg_column_size("email")::bigint)) FROM users;

postgis中面积计算函数


select st_area(st_transform(
ST_SetSRID(ST_GeomFromText(
'POLYGON ((115.440261 33.8547281, 115.4400647 33.8548702, 
115.4403265 33.8549768, 115.4404674 33.8549267, 115.4404397 33.8547365, 
115.440261 33.8547281))'),4326),4527))

shp2pgsql导入shp数据

连接centos服务器后，切换到postgres账户下，输入shp2pgsql命令，详细列出了命令的参数：

su postgres
shp2pgsql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2OTm0lls-1690625127140)(webp.webp)]

Shapfile直接入库：

shp2pgsql -c 路径/shp数据文件名 新建的数据表名 数据库名｜psql -d 数据库名

示例如下:
(例子中我已经上传了文件到centos的tmp文件夹下，参考linux基本命令)

shp2pgsql -c /tmp/天水街道网格数据/社区.shp community geoserver_postgis | psql -d geoserver_postg

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d47ddYIk-1690625127140)(zh-cn_image_0000001194855579.png)]

创建 SEQUENCE

CREATE SEQUENCE 
test_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START WITH 1
CACHE 1;

select nextval('test_id_seq');
select currval('test_id_seq')
drop SEQUENCE test_id_seq;

查找重复sql

SELECT tbl_poi_shp.geom_data,COUNT(geom_data)
FROM tbl_poi_shp
GROUP BY geom_data
HAVING COUNT(geom_data) > 1
ORDER BY geom_data;
select * from tbl_poi_shp

delete from tbl_poi_shp a 
using tbl_poi_shp b
where a.id>b.id
and a.geom_data=b.geom_data;

DELETE FROM tbl_poi_shp
WHERE id IN
   (SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER( PARTITION BY geom_data ORDER BY id ) AS row_num
        FROM tbl_poi_shp 
    ) t
    WHERE t.row_num > 1 );

select * from tbl_poi_shp;




[外链图片转存中...(img-d47ddYIk-1690625127140)]

#### 创建 SEQUENCE

CREATE SEQUENCE
test_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START WITH 1
CACHE 1;

select nextval(‘test_id_seq’);
select currval(‘test_id_seq’)
drop SEQUENCE test_id_seq;


#### 查找重复sql

```sql
SELECT tbl_poi_shp.geom_data,COUNT(geom_data)
FROM tbl_poi_shp
GROUP BY geom_data
HAVING COUNT(geom_data) > 1
ORDER BY geom_data;
select * from tbl_poi_shp

delete from tbl_poi_shp a 
using tbl_poi_shp b
where a.id>b.id
and a.geom_data=b.geom_data;

DELETE FROM tbl_poi_shp
WHERE id IN
   (SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER( PARTITION BY geom_data ORDER BY id ) AS row_num
        FROM tbl_poi_shp 
    ) t
    WHERE t.row_num > 1 );

select * from tbl_poi_shp;