postgre安装和psql基础命令

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

查看服务是否启动

img

操作连接数据库

./psql -Upostgres -dpostgres

img

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)';
  1. 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 实例维护的一个进程相关的视图,是实时变化的。

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

主要字段

ColumnTypeDescription
datidoid后台连接的数据库id(OID)
datnamename后台连接的数据库名
pidinteger后代进程id(PID)
usesysidoid连接到后台的用户id
usenamename连接到后台的用户名称
application_nametext通过后台连接的应用程序名称
client_addrinet后台连接的IP地址。当该字段为空时,说明客户端是在服务器主机上通过Unix的socket协议直连,或是数据库内部进程(如autovacuum等)
client_hostnametext根据client_addr字段,通过DNS解析获取的对应主机名称. 但仅当log_hostname被开启时才有效,并且只有通过IP协议连接时才能记录到主机名;
client_portinteger后台连接使用的TCP端口号。当使用Unix-Socket协议连接时此值为-1。
backend_starttimestamp with time zone进程开始的时间,即客户端连接到服务器的时刻。
xact_starttimestamp with time zone进程的当前事务开始执行的时间。若当前无活动事务,该值为null。 如果当前query是事务中的首次查询,此项值应与query_start列相同.
query_starttimestamp with time zone当前事务中有在执行中的语句时,此值记录该语句开始执行的时间; 若事务state不是active状态,此值记录的是上一次开始执行语句时间。(即每次开始执行新的语句时此值更新)
state_changetimestamp with time zone记录进程中事务状态发生改变的时刻;
waitingboolean是否处于等待。当进程在等待解锁时此值为t,否则为false
statetext表示后台进程的整体状态。可能的值有: 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.
querytext后台进程最近(当前)执行的语句内容. 当状态是 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');

总结:

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

  2. 什么时候使用timestamptz类型,什么时候使用timestamp?

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

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

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

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

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

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

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;
  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值