pgsql学习笔记

阿～宁

已于 2024-10-29 15:48:12 修改

阅读量1.3k

点赞数 25

文章标签：学习笔记

于 2024-03-13 23:24:58 首次发布

本文链接：https://blog.csdn.net/weixin_42683670/article/details/136690721

版权

pgsql

PostgreSQL主要优势

1.PostgresQL完全免费，而且是BSD协议
2.与PostgresQ]配合的开源软件很多，有很多分布式集群软件，如pgpoo1、pgcluster、slonyp1p1oxy等等，很容易做读写分离、负载均衡、数据水平拆分等方案，而这在MySQL下则比较困难。
3.PostgresQL源代码写的很清晰，易读性比MySQL强太多了，怀疑MySQL的源代码被混淆过。所以很多公司都是基本PostqresQL做二次开发的。
4.PostgresQL在很多方面都比MySQL强，如复杂SQL的执行、存储过程、触发器、索引。同时PostgresQl是多进程的，而MySQL是线程的，虽然并发不高时，MSQL处理速度快，但当并发高的时候，对于现在多核的单台机器上，MySQL的总体处理性能不如PostgresQL，原因是MySQL的线程无法充分利用CPU的能力。

psql安装

二进制包安装

apt update
apt install postgresql -y

源码编译安装

apt update
apt install -y gcc make libreadline-dev zlib1g-dev

Short Version
./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

./configure --prefix=/apps/pgsql --with-pgport=5432

root@ubuntu2:~/postgresql-14.2# pg_config --configure
Command 'pg_config' not found, but can be installed with:
apt install libpq-dev          # version 14.9-0ubuntu0.22.04.1, or
apt install postgresql-common  # version 238

make -j 2 world
make install-world

#创建用户
useradd -s /bin/bash -m -d /home/postgres postgres
echo postgres:123456|chpasswd

#创建目录并能授权
mkdir -pv /pgsql/data
chown postgres. /pgsql/data

#设置环境变量
root@ubuntu2:~/postgresql-14.2# cat /etc/profile.d/pgsql.sh
export PGHOME=/apps/pgsql
export PATH=$PGHOME/bin:$PATH
export PGDATA=/pgsql/data
export PGUSER=postgres
export MAPATH=/apps/pgsql/share/man:$MANPATH

postgres@ubuntu2:~$ initdb

postgres@ubuntu2:~$ pg_ctl start
waiting for server to start....2024-03-13 14:18:30.945 UTC [15406] LOG:  starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2024-03-13 14:18:30.952 UTC [15406] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-03-13 14:18:30.954 UTC [15406] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-03-13 14:18:30.958 UTC [15407] LOG:  database system was shut down at 2024-03-13 14:16:22 UTC
2024-03-13 14:18:30.961 UTC [15406] LOG:  database system is ready to accept connections
 done
server started
postgres@ubuntu2:~$
postgres@ubuntu2:~$ psql
psql (14.2)
Type "help" for help.

postgres=#

pg_ctl命令管理

pg_ctl status
pg_ctl start
pg_ctl stop
pg_ctl restart
pg_ctl reload
pg_ctl initdb
pg_ctl promote

psql管理

常用操作

查看psql帮助

\?
\h
\h create database;

设置显示信息的格式

#后续查询将竖着显示，\G
\x

#开启命令执行时长提示
\timing on

postgres=# \x
Expanded display is on.
postgres=# \l
List of databases
-[ RECORD 1 ]-----+----------------------
Name              | postgres
Owner             | postgres
Encoding          | UTF8
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
Access privileges |

postgres=# \timing on
Timing is on.
postgres=# select pg_sleep(3);
 pg_sleep
----------

(1 row)
Time: 3004.296 ms (00:03.004)
postgres=# \timing off
Timing is off.

查看和连接数据库

#列出数据库名  show databases
\l
#显示数据库详细信息
\l+
#查看当前连接信息
\c
#查看当前连接详细信息
\conninfo
#连接数据库 use
\c hellodb

管理表

postgres=# create database hellodb;
CREATE DATABASE
Time: 71.055 ms
postgres=# \c hellodb;
You are now connected to database "hellodb" as user "postgres".
hellodb=# create table tb1(id serial primary key,name text);
CREATE TABLE
Time: 10.248 ms

hellodb=# insert into tb1(name) select (md5(random()::text))  from  generate_series(2,10);
INSERT 0 9
Time: 2.469 ms
hellodb=# select  *  from tb1;
 id |               name
----+----------------------------------
  1 | 4c6bf106455f5d66f005f5500014ed79
  2 | 161ddf6a732b8a2c509119951b9cf167
  3 | cc76366af5d09608b8f4452112c57a74
  4 | 4e970935b9f9baffb4a67109e9148af7
  5 | 5123804ee42de13fad14ca0a4aa0264f
  6 | 8e2dd5fff529c9d755251a6f8e9c5989
  7 | de3bd8c5b7da376f35593b48428aaf6a
  8 | 712388aa9e9a819a38338c204b7edd95
  9 | 17848277ecd7cc5c38383fe5c161df84
(9 rows)

Time: 1.128 ms
hellodb=# insert into tb1(name) select (md5(random()::text))  from  generate_series(1,1000000);
INSERT 0 1000000
Time: 2031.386 ms (00:02.031)

查看表和表信息

#列出所有表，视图，序列
\d
#列出public的schema中所有表明，相当于show tables
\dt
#查看表结构
\d t1

#查看表对应的文件
hellodb=# select oid,datname from pg_database where datname='hellodb';
  oid  | datname
-------+---------
 16384 | hellodb

创建用户和修改密码

#修改密码
postgres=# alter USER postgres with password '123456';

#查看用户
\du
\dg

增删改查与mysql一致

pg实现远程连接

#网络防火墙功能的文件pg_hba.conf---设置哪些ip访问
#任何操作系统用户都可以使用任何数据库用户连接且不需要密码
local all all trust

#需要密码登录验证
local all all md5

#允许用户通过10.0.0.0//24远程密码登陆验证
host all all 10.0.0.0/24  md5

#允许用户 wang通过任意远程主机登录test数据库
host  test  wang  0.0.0.0/0  md5

#监听对外提供服务的ip   postgresql.conf
listen_addresses='*'

实现任意主机远程登陆

 vim /etc/postgresql/16/main/postgresql.conf
#listen_addresses = 'localhost'     # what IP address(es) to listen on; #更改为下面表示任何主机都可以登录
listen_addresses = '0.0.0.0'
[16:55:26root@ubuntu ~]# vim /etc/postgresql/16/main/pg_hba.conf
host    all             all             0.0.0.0/0               md5
#这将允许来自任意 IP 地址的主机使用密码登录
#重启服务生效
#必须设置密码
postgres=# alter USER postgres with password '123456';
psql -h 10.0.0.8 -p 5432 -U username -d database_name

可以 .pgpass文件实现免密登录

用户权限

PostgreSQL使用角色role的概念来管理数据库访问权限。角色是一系列相关权限的集合。为了管理方便，通常会把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权限，就把角色赋给相应的用户。由于用户也拥有一系列的相关权限，为了简化管理，在PostgreSQL中，角色与用户是没有区别的，一个用户也是一个角色，因此可以把一个用户的权限赋给另一个用户。
用户和角色在整个数据库实例中都是全局的,即在同一个实例中的不同数据库中,看到的用户也都是相同的。
在初始化数据库实例时，会创建一个预定义的超级用户，这个用户的名称与初始化该数据库实例的操作系统用户名相同。比如: 如果数据库实例是建立在操作系统用户dba (通常使用 postgres 用户)下的，这个数据库超级用户的名称也会叫dba。可以用这个超级用户连接数据库，注意：dba默认会连接同名的数据库dba，而默认dba不存在，所以需要登录时指定连接数据库postgres进行登录,然后再创建其它的用户

角色用户

postgres-# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# create role role1;
CREATE ROLE
postgres=# create role role2 with password '123456';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     | Cannot login                                               | {}

postgres=# alter role role2 with login;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}

cxn@ubuntu2:~$ psql -U role2  -d postgres
psql (14.2)
Type "help" for help.

postgres=>

postgres=# create role role3;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}
 role3     | Cannot login                                               | {}

postgres=# drop user role3;
DROP ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}

postgres=# grant postgres to role2;
GRANT ROLE
postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {postgres}

postgres=# revoke postgres from role2;
REVOKE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}

postgres=# create user user1 with password '123456';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}
 user1     |                                                            | {}

模式

#创建模式
create schema schema_name;
#删除模式
drop schema schema_name;
#查看模式
\dn

postgres=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

postgres=# CREATE SCHEMA myschema;
CREATE SCHEMA
postgres=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | postgres
 public   | postgres
(2 rows)

postgres=# CREATE SCHEMA AUTHORIZATION user1;
CREATE SCHEMA
postgres=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | postgres
 public   | postgres
 user1    | user1
(3 rows)

postgres=# alter schema user1 rename to user2;
ALTER SCHEMA
postgres=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | postgres
 public   | postgres
 user2    | user1
(3 rows)

postgres=# alter schema myschema owner to user1;
ALTER SCHEMA
postgres=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | user1
 public   | postgres
 user2    | user1
(3 rows)

postgres=# drop schema user2;
DROP SCHEMA
postgres=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | user1
 public   | postgres
(2 rows)

#创建用户
postgres=# CREATE USER mage WITH PASSWORD '123456'; 
CREATE ROLE
#创建模式
postgres=# CREATE SCHEMA magedu;
CREATE SCHEMA 
#创建数据库
postgres=# CREATE DATABASE zabbix; 
CREATE DATABASE
#授权
postgres=# GRANT ALL PRIVILEGES ON DATABASE zabbix TO mage; 
GRANT


postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 mage      |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}
 user1     |                                                            | {}

postgres=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 magedu   | postgres
 myschema | user1
 public   | postgres
(3 rows)

postgres=# SELECT current_schema();
 current_schema
----------------
 public
(1 row)

postgres=# ALTER USER mage SET search_path TO magedu;
ALTER ROLE

psql 是 PostgreSQL 的交互式命令行工具，用于连接和操作 PostgreSQL 数据库
参数分别表示：
-h：指定数据库服务器的主机名（hostname）或 IP 地址。
-p：指定数据库服务器的端口号（port），默认是 5432。
-U：指定连接数据库时使用的用户名（username）。
-c：执行指定的 SQL 命令或查询，然后退出。
-t：仅输出查询结果的内容，不显示列标题和行数（通常用于脚本中）。
-q：安静模式，减少输出信息。

体系架构

在这里插入图片描述
Postmaster 主进程

整个数据库实例的主控制进程，负责启动和关闭该数据库实例。实际上,使用pg ctl来启动数据库时，pg_ctl也是通过运行postgres来启动数据库的，只是它做了一些包装,更容易启动数据库。
它是第一个PostgreSQL进程，此主进程还会fork出其他子进程，并管理它们。

BgWriter 后台写进程

为了提高插入、删除和更新数据的性能，当往数据库中插入或者更新数据时，并不会马上把数据持久化到数据文件中,而是先写入Buffer中
该辅助进程可以周期性的把内存中的脏数据刷新到磁盘中

WalWriter 预写式日志进程

WAL是write ahead log的缩写，WAL log旧版中称为xlog，相当于MySQL中Redo log
预写式日志是在修改数据之前，必须把这些修改操作记录到磁盘中，这样后面更新实际数据时，就不需要实时的把数据持久化到文件中了。即使机器突然宕机或者数据库异常退出，导致一部分内存中的脏数据没有及时的刷新到文件中，在数据库重启后，通过读取WAL日志，并把最后一部分WAL日志重新执行一遍，就能恢复到宕机时的状态了
WAL日志保存在pg_wal目录(早期版本为pg_xlog) 下。每个xlog 文件默认是16MB,为了满足恢复要求，在pg_wal目录下会产生多个WAL日志，这样就可保证在宕机后，未持久化的数据都可以通过WAL日志来恢复，那些不需要的WAL日志将会被自动覆盖

Checkpointer 检查点进程

检查点(Checkpoints)是事务序列中的点,保证在该点之前的所有日志信息都更新到数据文件中。
在检查点时，所有脏数据页都冲刷到磁盘并且向日志文件中写入一条特殊的检查点记录。在发生崩溃的时候，恢复器就知道应该从日志中的哪个点（称做 redo 记录）开始做 REDO 操作，因为在该记录前的对数据文件的任何修改都已经在磁盘上了。在完成检查点处理之后，任何在redo记录之前写的日志段都不再需要，因此可以循环使用或者删除。在进行 WAL 归档的时候，这些日志在循环利用或者删除之前应该必须先归档保存
检查点进程 (CKPT) 在特定时间自动执行一个检查点,通过向数据库写入进程 (BgWriter) 传递消息来启动检查点请求

Session 会话进程