首页 » share » 正文

编译安装 postgresql 及简单使用

OS : Ubuntu18.04 LTS
我这里下载 PostgreSQL 11 源码

下载,解压

编译安装 postgresql

参考文档:

配置

安装完成,创建数据目录,给 postgresql 创建用户并更改所有者为 postgresql

初始化数据库

tips:因为我这里还没有设置环境变量,所以这里使用相对路径

必须在登录PostgreSQL用戶账户时执行此命令,否则无法启动数据库

也可以通过 程序运行

如果您使用pg_ctl启动和停止服务器可能更直观,因为pg_ctl將是您用于管理数据库服务器实例的唯一命令。

运行

前台运行postgre

从它的提示信息可以看到 监听的socket是 127.0.0.1:5432,默认端口5432.

后台运行postgre

先切换成我创建的postgresql用户

不切换用户的情况下可以这么操作

postgresql基本操作

创建数据库

您也可以使用您的日常用户来创建

删除数据库

您也可以使用您的日常用户来删除

访问数据库

这里使用 同样有效,不再演示.以下使用postgresql用户操作

psql 详细用法 以及 配置文件位置

先看下这个postgresql内置的数据库都有哪些

这个mydb是我刚才创建的测试数据库,现在我们进 postgres 这个库看看,顺便看下这个postgresql的配置文件在哪里

显示配置文件正在我我创建的 postgresql 数据库的数据目录下

控制台命令

\h:查看SQL命令的解释,比如
\?:查看psql命令列表。
\l:列出所有数据库。
\c [database_name]:连接其他数据库。
\d:列出当前数据库的所有表格。
\d [table_name]:列出某一张表格的结构。
\du:列出所有用户。
\e:打开文本编辑器。
\conninfo:列出当前数据库和连接的信息。

数据库操作

基本的数据库操作,使用标准sql。

# 创建新表

# 插入数据

# 选择记录

# 更新数据

# 删除记录

# 添加栏位

# 修改栏位类型

# 更新结构

# 更名栏位

# 删除栏位

# 删除栏位(连约束一起删除)

# 表格更名

# 删除表格

关于postgresql的密码验证

有几种基于密码的身份验证方法。这些方法的操作类似,但用户密码存储在服务器上的方式以及客户端提供的密码如何通过连接发送方式不同。

scram-sha-256

使用scram-sha-256加密密码,可防止密码在不受信任的环境下被嗅探,并支持以加密的哈希形式在服务器上存储密码,这是当前提供的方法中最安全的方法,但较旧的客户端库不支持它。

md5

以md5加密防止密码嗅探,并且避免在服务端以明文存储密码。如今,md5已经不被认为时安全的加密算法。为了从md5过度到SCRAM,如果在pg_hba.conf中指定md5方式,但是数据库中存储的密码是使用SCRAM加密的,那么将自动使用SCRAM方式进行身份认证。也就是说,在目前的版本中,即使设置了md5,在进行认证时,也会根据数据库中密码的实际加密方式动态选择认证方式。

password

该方法password以明文形式发送密码,因此容易受到密码“ 嗅探 ”攻击。如果可能的话,应该始终避免该方式的密码验证。如果连接受SSL加密保护,则 password 可以安全使用。(尽管如果依赖于使用SSL,SSL证书身份验证可能是更好的选择)。

PostgreSQL数据库密码与操作系统用户密码是分开的。每个数据库用户的密码都存储在pg_authid系统表中。可以使用SQL命令CREATE ROLE和ALTER ROLE(例如CREATE ROLE foo WITH LOGIN PASSWORD ‘secret’,或psql命令)来管理密码\password。如果没有为用户设置密码,则存储的密码为空,并且该用户的密码验证将始终失败。

不同的基于密码的身份验证方法的可用性取决于用户的密码在服务器上是如何加密的 (或更准确地说是哈希)。如果使用 scram-sha-256设置对密码进行了加密, 那么它可以用于身份验证方法scram-sha-256和password (但在后一种情况下密码传输将以纯文本形式)。如上所述, 认证方法规范md5会自动切换到使用scram-sha-256方法, 所以它也可以工作。如果密码是使用md5设置加密的,则它只能用于 md5和password身份验证方法规范(同样,在后一种情况下用明文传输密码)。(之前的PostgreSQL版本支持以明文形式在服务器上存储密码。这已不再可行。)要查看当前存储的密码哈希值,请参阅系统表pg_authid。

查看系统表pg_authid

postgresql默认的密码验证是md5,要切换到scram,必须确保了所有正在使用的客户端库支持SCRAM。在 postgresql.conf 中,将配置 password_encryption = scram-sha-256 就可以将md5切换到scram

参考文档:

设置密码

输入密码(由于默认是没有回显的,所以,密码虽然输进去了,但是用户看不到。)。

客户端连接postgresql

我这里使用Navicat Premium演示下

用户名填:postgresql

重置密码

退出\q 帮助\h

创建超级用户

这次演示的是不用sql,用createuser创建

本地创建

-P
--pwprompt 如果给定,createuser将发出新用户密码的提示。如果您不打算使用密码身份验证,则无需执行此操作。

-s
--superuser 新用户将作为超级用户创建。

-e
--echo 回显createuser生成并发送到服务器的命令。

远程创建

-p port
--port=port 指定服务器正在侦听连接的TCP端口或本地Unix域套接字文件扩展名。

-h host
--host=host 指定运行服务器的计算机的主机名。如果值以斜杠开头,则将其用作Unix域套接字的目录。

删除用户

本地删除

远程删除

-i
--interactive 在实际删除用户之前提示确认,如果在命令行中未指定用户名,则提示输入用户名。

-e
--echo 回显dropuser生成并发送到服务器的命令。

设置环境变量

用 postgresql 用户

保存,退出

立即生效

修改认证方式

pg_hba.conf文件

客户端认证是由一个配置文件(通常名为pg_hba.conf并被存放在数据库的数据目录中)控制(HBA表示基于主机的认证)。在initdb初始化数据目录时,它会生成一个默认的pg_hba.conf认证配置文件到数据目录中。不过我们也可以把认证配置文件放在其它地方; 参阅 hba_file配置参数

修改认证

本来想开启更安全的 scram-sha-256 认证,但是开启 scram-sha-256 认证后,我的 Navicat Premium 12 都连不上postgresql了(Navicat Premium 12 目前还不支持 scram-sha-256 认证 )。所以,下面使用md5认证。

本地的 unix socket 使用 md5 认证,远程TCP 也开启 md5 认证。注意,这里的 ADDRESS 127.0.0.1/32 被改成了 0.0.0.0/0 表示允许监听所有 IPv4 地址。::1/128 被改成 :: 表示允许监听所有 IPv6 地址。

参考文档:

关闭postgresql

有几种方法可以关闭数据库服务器。您可以通过向主postgres进程发送不同的信号来控制关闭的方式。

最简单方法:

或者

这种方法需要设置 环境变量,否则,会报

pg_ctl: no database directory specified and environment variable PGDATA unset

如果不想设置 环境变量,可以加 -D 参数

-D datadir
--pgdata datadir 声明该数据库配置文件的文件系统位置。如果忽略则使用PGDATA环境变量。

与oracle相同,在关闭时也可采用不同的模式,简介如下:

SIGTERM
这是智能关闭 模式。收到SIGTERM后,服务器不允许新连接,但允许现有会话正常结束其工作。它仅在所有会话终止后关闭。如果服务器处于联机备份模式,则它还会等待,直到联机备份模式不再处于活动状态。备份模式处于活动状态时,仍然允许新连接,但仅限于超级用户(此例外允许超级用户连接以终止联机备份模式)。如果在请求智能关闭时服务器处于恢复状态,则只有在所有常规会话终止后,才会停止恢复和流复制。

SIGINT
这是快速关闭 模式。服务器不允许新连接,并向所有活跃服务器发送 SIGTERM(让它们立刻退出),,这将导致它们中止当前事务并立即退出。然后它等待所有服务器进程退出并最终关闭。如果服务器处于联机备份模式,则备份模式将终止,从而使备份无效。

SIGQUIT
这是立即关闭 模式。主postgres进程将向所有子进程发送SIGQUIT并立即退出,而不会正确关闭自己。子进程同样在收到SIGQUIT后立即退出。这将导致在下次启动时恢复(通过重放WAL日志)。建议仅在紧急情况下使用。

SIGKILL
此选项尽量不要使用,这样会阻止服务器清理共享内存和信号量,那样的话你只能在启动服务器之前自己手工做这件事。另外,SIGKILL 直接把 postgres 杀掉,而不会等它把信号转发给它的子进程,因此我们还需要手工杀掉每个独立子进程。

要在允许其他会话继续的情况下终止单个会话,请使用 pg_terminate_backend() 或将 信号发送到与会话关联的子进程。

程序提供了一个方便的接口发送这些信号关闭服务器。或者,您可以在非Windows系统上可以直接使用 发送这些信号。可以用 程序或者从数据目录的postmaster.pid文件中找到postgres进程的PID。例如,使用快速关闭的方式关闭postgresql服务器:

参考文档:

重新开启数据库

以上这个命令会把用户的写操作,报错信息等回显到控制台上。我用下面的方法把流写入到日志文件里

设置开机自动启动

保存以后,设置开机自启以及开启服务:

pg_ctl模式和选项的说明

pg_ctl模式

start模式:

启动一个新的服务器。该服务器被启动在后台,并且它的标准输出被附加到/dev/null(或 Windows 上的nul)。在 Unix 类系统上,默认情况下服务器的标准输出和标准错误被发送到 的标准输出(不是标准错误)。 的标准输出应该接着被重定向到一个文件或用管道导向另一个进程(例如日志轮转程序rotatelogs)。否则 将把它的输出写到控制终端(从后台)并且将不会离开 shell 的进程组。在 Windows 上,默认情况下服务器的标准输出和标准错误被发送到终端。这些默认行为可以使用-l追加服务器的输出到一个日志文件来改变。我们推荐使用-l或输出重定向。

stop模式:

关闭运行在指定数据目录中的服务器。对-m选项可以选择三种不同的关闭方法。“Smart”模式等待所有客户端断开连接以及任何在线备份结束。如果该服务器是热备,一旦所有的客户端已经断开连接,恢复和流复制将被终止。“Fast”模式(默认)不会等待客户端断开连接并且将终止进行中的在线备份。所有活动事务都被回滚并且客户端被强制断开连接,然后服务器被关闭。“Immediate”模式将立刻中止所有服务器进程,而不是做一次干净的关闭。 这种选择将导致下一次服务器启动过程中的崩溃恢复循环。

restart模式:

实际上先执行一次停止然后执行一次启动。 这允许改变 的命令行选项, 或者更改在不重新启动服务器的情况下无法更改的配置文件选项。 如果在服务器启动期间在命令行上使用相对路径,则restart可能会失败, 除非在与服务器启动时相同的当前目录中执行

reload模式:

简单地向postgres服务器进程发送一个 SIGHUP信号,导致它重新读取它的配置文件 (postgresql.conf、pg_hba.conf等)。 这允许改变配置文件选项而无需完全重新启动服务器来让改变生效。

status模式:

检查一个服务器是否运行在指定的数据目录中。 如果有一个服务器正在运行,服务器的PID 和用来调用它的命令行选项将被显示。如果服务器没有在运行, 将返回退出状态 3。 如果没有指定一个可以访问的数据目录, 将返回退出状态 4。

pg_ctl选项

-s
--silent 只打印错误,不打印信息性的消息。

-p path 指定postgres可执行程序的位置。默认情况下, 可执行程序可以从 相同的目录得到,或者如果没有在那里找到,则在硬写的安装目录中获得。除非你正在做一些不同寻常的事并且得到错误说没有找到 可执行程序,这个选项不是必需的。

在init模式中,这个选项类似于指定了 可执行程序的位置。

-o initdb-options
--options=initdb-options 指定要直接传递给 命令的选项。 -o可以多次指定,所有给定的选项都被传递。

这些options应该通常被单引号或双引号包围来确保它们被作为一个组传递。

-l filename
--log=filename 追加服务器日志输出到filename。如果该文件不存在,它会被创建。umask被设置成 077,这样默认情况下不允许其他用户访问该日志文件。

-w
--wait 等待操作完成。 支持模式start、stop、 restart、promote和 register,并且是这些模式的默认。

在等待启动时, 会重复尝试连接到服务器。 在等待关闭时, 会等待服务器移除它的PID文件。 基于启动或关闭的成功返回一个退出码。

如果操作在超时时间内未完成(请参阅选项-t), 则 以非零退出状态退出。但请注意, 该操作可能会在后台继续并最终成功。

-t seconds
--timeout=seconds 指定等待操作(参阅选项-w)完成的最大秒数。默认值是环境变量 的值,如果该环境变量未设置,则默认值为 60。

-m mode
--mode=mode 指定关闭模式。mode可以是smart(智能关闭)、 fast(快速关闭)或immediate(立即关闭),或者这三者之一的第一个字母。 如果忽略该选项,默认是fast。

systemd配置的解释

[Unit] 区块:启动顺序与依赖关系。

After字段:表示如果network.target需要启动,那么postgresql.service应该在它们之后启动。

[Service] 区块:启动行为

Type字段:定义启动类型。它可以设置的值如下:

  • simple(默认值):ExecStart字段启动的进程为主进程。并不包含任何通知机制(例如通知”服务启动成功”)。要想使用通知机制,应该将 Type= 设为其他非默认值
  • forking:ExecStart字段将以fork()方式启动,此时父进程将会退出,子进程将成为主进程。可用于能将自身切换到后台的服务。
  • oneshot:类似于simple,但只执行一次,Systemd 会等它执行完,才启动其他服务。用于那些只需要执行一次性动作而不需要持久运行的单元, 例如文件系统检查或者清理临时文件。 此类单元, 将会在启动后一直等待指定的动作完成, 然后再回到停止状态。
  • dbus:类似于simple,但会等待 D-Bus 信号后启动。可用于能够在完成初始化之后获得一个 D-Bus 名称的单元。
  • notify:类似于simple,启动结束后会发出通知信号,然后 Systemd 再启动其他服务。可用于能够理解 systemd 通知协议的服务
  • idle:类似于simple,但是要等到其他任务都执行完,才会启动该服务。一种使用场合是为让该服务的输出,不与其他服务的输出相混合
  • ExecStart字段:定义启动进程时执行的命令。
  • ExecReload字段:重启服务时执行的命令
  • ExecStop字段:停止服务时执行的命令
  • ExecStartPre字段:启动服务之前执行的命令
  • ExecStartPost字段:启动服务之后执行的命令
  • ExecStopPost字段:停止服务之后执行的命令

Environment字段: 用来设置环境变量,可以使用多次。

EnvironmentFile字段: 通过文件的方式设置环境变量,可以把下面的内容保存到文件中。

OOMScoreAdjust字段:设置进程因内存不足而被杀死的优先级。 可设为 -1000(禁止被杀死) 到 1000(最先被杀死)之间的整数值。 详见 proc.txt 文档。

[Install] 区块

WantedBy字段:表示该服务所在的 Target。

Target的含义是服务组,表示一组服务。 指的是, 所在的 Target 是multi-user.target。

这个设置非常重要,因为执行 命令时,postgresql.service 的一个符号链接,就会放在/etc/systemd/system目录下面的multi-user.target.wants子目录之中。

Systemd 有默认的启动 Target。

上面的结果表示,默认的启动 Target 是graphical.target。由于我的Ubuntu开启了图形界面,System V init运行级别为5,所以 为graphical.target,在System V init运行级别为2,3,4时 get-default为 multi-user.target。 multi-user.target 被 graphical.target 所依赖(下面会讲到)。 所以在这个组里的所有服务,都将开机启动。这就是为什么 命令能设置开机启动的原因。

System V init运行级别systemd目标名称作用
0 runlevel0.target -> poweroff.target关机
1 runlevel1.target -> rescue.target单用户模式
2runlevel2.target -> multi-user.target等同于级别3
3runlevel3.target -> multi-user.target多用户的文本界面
4runlevel4.target -> multi-user.target等同于级别3
5runlevel5.target -> graphical.target多用户的图形界面
6 runlevel6.target -> reboot.target重启
emergencyemergency.target紧急shell

使用 Target 的时候, 命令和 命令也很有用。

一般来说,常用的 Target 有两个:一个是multi-user.target,表示多用户命令行状态;另一个是graphical.target,表示图形用户状态,它依赖于multi-user.target。官方文档有一张非常清晰的 Target 依赖关系图

修改配置文件后重启

修改配置文件以后,需要重新加载配置文件,然后重新启动相关服务。

参考文档:

参考文档:

发表评论

正在加载验证码......

请先完成验证