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;
g
[外链图片转存中...(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;