Postgres 常用命令/脚本 （运维版）

数据库

查看版本信息

show server_version;
psql --version
1
2

登录

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

psql -U postgres -d dbname
1

启动服务:

net start postgresql-9.5
1

停止服务:

net stop postgresql-9.5
1

列举数据库：

\l
1

退出psgl：

\q
1

字符集
在windows7中安装的postgresql默认使用GBK字符集,经常不能使用显示中文的数据表,解决办法:
注意:在windows 7下的postgresql中写操作时要使用GBK,读操作时要用UTF8;
查看字符集

\encoding
1

设置字符集为 utf-8 就可以了.

\encoding utf-8       // 设置客户端的字元集
show client_encoding;      // 显示客户端的字元集
show server_encoding;      // 显示服务器的字元集
1
2
3

创建用户

创建数据库新用户

CREATE USER test WITH PASSWORD '1q2w3e';
1

注意：语句要以分号结尾，密码要用单引号括起来。

显示所有数据库(相当于show databases;)
\l

创建用户数据库

CREATE DATABASE testdb01 OWNER test;
1

将数据库的所有权限赋予用户

GRANT ALL PRIVILEGES ON DATABASE testdb01 TO test;
1

将数据库 testDB 权限授权于 test
但此时用户还是没有读写权限，需要继续授权表

GRANT ALL PRIVILEGES ON all tables in schema public TO test;
1

修改数据库的owner为test用户(如果创建时未指定owner、也可以通过alter命令指定）

alter database testdb01 owner to test;
1

将schema中all权限赋予给指定用户（管理员账号在对应DB中执行grant命令授权，授权给test用户为例）

\c testdb01
\c - postgres
grant all on schema public to test;
1
2
3

CREATE USER和CREATE ROLE的区别在于，CREATE USER指令创建的用户默认是有登录权限的，而CREATE
ROLE没有。

\du 指令显示用户和用户的用户属性
创建用户时设定用户属性
基本语法格式

CREATE ROLE role_name WITH optional_permissions;
1

示例:在创建用户时设定登录权限。

CREATE ROLE username WITH LOGIN;
1

例如:可通过以下方式禁止用户登录

ALTER ROLE username WITH NOLOGIN;
1

删除用户和组

DROP ROLE role_name;
1

导入导出

命令行（cmd或powershell）进入PostgreSQL安装目录

cd D:\postgres\bin
1

手动导出

pg_dump -Fc -U postgres postgres > D:\postgres\buckup\sql\postgres_2022-11-11.dump
1

还原：

createdb newDBname
psql -d newDBname -U postgres -f file.sql
1
2

设置一个备份脚本

@ECHO OFF
@setlocal enableextensions
@cd /d "%~dp0"

SET PGPASSWORD=123456
SET PGPATH=D:\postgres\bin\pg_dump.exe
SET SVPATH=D:\postgres\backup\sql
SET PRJDB=postgres
SET DBUSR=test
FOR /F "TOKENS=1,2,3 DELIMS=/ " %%i IN ('DATE /T') DO SET d=%%i-%%j-%%k
FOR /F "TOKENS=1,2,3 DELIMS=: " %%i IN ('TIME /T') DO SET t=%%i%%j%%k

SET DBDUMP=%PRJDB%_%d%_%t%
@ECHO OFF
%PGPATH% -h localhost -p 5432 -U %DBUSE% %PRJDB% > %SVPATH%\%DBDUMP%

echo Backup Taken Complete %SVPATH%%DBDUMP%

pause
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

PGPATH 表示数据库备份程序路径
SVPATH 表示文件存放路径
PRJDB 表示要备份的数据库名称
DBUSR 表示用户
DBDUMP 文件名
PGPASSWORD 密码
导入

d 数据库名
-U 数据库用户名
-f sql文件路径
回车之后提示输入用户密码，然后静待完成就可以了
psql无法使用需要配环境变量，将postgres的bin目录放到Path。

psql -d buckup_test -U postgres -f D:\postgres\buckup\sql\buckup_test_2022-11-11.sql
1

WAL实现增量备份

Write-Ahead Logging，预写日志。
准备

net user postgres password /add    说明：设置postgres的密码，最好与pg的超级用户postgres相同。
net localgroup Users  postgres /add
md  C:\backup #新建备份文件夹 
1
2
3

修改backup的权限，赋予Users组完全控制权，并保持权限继承
新建子目录 D:\backup\base_files 、D:\backup\wal_files （后面直接执行命令也可以生成文件夹）
关闭数据库服务：
任务管理器关闭postgres服务或输入以下命令

pg_ctl stop
1

修改data文件夹下的 postgresql.conf文件，开启WAL日志

wal_level = replica	
archive_mode = on
archive_command = 'copy /V  "%p"  D:\\backup\\wal_files\\%f ' #注意写法，这里有大坑
restore_command = 'cp "d:\\backup\\wal_files\\%f %p"'
recovery_target_time = '2022-11-11 17:26:33.258335+08' //根据恢复的时间点改写
recovery_target_inclusive = false
1
2
3
4
5
6
7

服务启动

pg_ctl start
1

利用pg 提供的命令完成基础备份

pg_basebackup.exe -D D:\backup\base_files2 -Fp  -P -v  -U postgres
1

通过navicat客户端/psql，执行数据库的操作，验证wal存档过程

执行pg的控制台程序：

Psql -U postgres
create table test(c1 int, c2 timestamp default current_timestamp);
insert into test select generate_series(1, 100000), clock_timestamp(); //插入10万条记录
1
2
3

记录当时的时间戳

select current_timestamp;
1

查看wal_files文件夹是否有内容，没有内容检查日志。我之前是文件夹加到postgres文件夹里了，没有提示没有权限。

数据恢复

假如某个时刻，数据库出现故障，恢复pg的过程如下

停止pg服务
把D:\backup\base_files文件夹复制到 D:\PostgreSQL(原data文件夹处)，更名为data_test,并设置权限为 Users组完全控制。
在data_test中建一空文件，文件的全名为 recovery.signal(千万注意，要注意文件的扩展名，别创建一个recovery.signal.txt的文件)。

在bin文件夹地址栏输入cmd/powershell，然后输入

pg_ctl -D D:\postgres\data_test start
1

系统在启动的过程中，自动通过回放WAL恢复数据库。

通过navicat/psql，确认恢复工作是否完成。
确认恢复后将原data文件夹重命名，data_test修改为data。
执行完恢复工作后，系统会自动删除恢复标识信号文件(recovery.signal),如未删除，手动删除。
我修改后数据库进入了只读状态。
以后pg系统再重新启动时，会自动工作在正常运行状态。

原文链接：2021-09-05 Windows环境下 postgresql12 增量备份及恢复实践

远程访问

想在其他主机上访问PostgreSQL数据库服务器，需要修改data目录下的pg_hba.conf和postgresql.conf

监听端口注释打开.并修改为*（一般默认是*）

listen_addresses = '*' 
1

在ipv4链接设置中添加一行.

host    all             all             0.0.0.0/0               md5
1

修改后重启数据库后生效