说明：
1、PostgreSQL 使用 pg_dump 和 pg_dumpall 进行数据库的逻辑备份，使用 psql 和 pg_restore 导入数据；
2、pg_dump 可以选择一个数据库或者部分表进行备份，pg_dumpall是对整个数据库集群进行备份；
3、psql恢复SQL文本格式的数据备份，pg_restore恢复自定义格式的数据备份。
数据库用户：dbuser
数据库：testdb
数据表：new_test，tb_test，testdb
数据库安装路径：/usr/local/pgsql/bin/
mkdir -p /data/backup #创建备份文件存放目录
chown postgres.postgres -R /data/backup
su - postgres
一、PostgreSQL数据库导出
#pg_dump命令
1、导出数据库中的单个表数据
#导出数据库testdb中的new_test表
pg_dump -h 127.0.0.1 -U dbuser -p 5432 -W testdb -t new_test --column-inserts -f /data/backup/new_test.sql
#导出后压缩为.gz格式
pg_dump -h 127.0.0.1 -U dbuser -p 5432 -W testdb -t new_test --column-inserts | gzip > /data/backup/new_test.sql.gz
#导出数据库testdb中的tb_test表
pg_dump -h 127.0.0.1 -U dbuser -p 5432 -W testdb -t tb_test --column-inserts -f /data/backup/tb_test.sql
2、导出数据库中的多个表数据
#导出数据库testdb中的new_test、tb_test两张表
pg_dump -h 127.0.0.1 -U dbuser -p 5432 -W testdb -t new_test -t tb_test --column-inserts -f /data/backup/new_test_tb_test.sql
3、导出整个数据库
pg_dump -h 127.0.0.1 -U dbuser -p 5432 -W testdb --column-inserts -f /data/backup/testdb.sql
#导出后压缩为.gz格式
pg_dump -h 127.0.0.1 -U dbuser -p 5432 -W testdb --column-inserts | gzip > /data/backup/testdb.sql.gz
#导出自定义格式的备份，如果不加-Fc 则文件格式是sql语句
pg_dump -Fc -h 127.0.0.1 -U dbuser -p 5432 -W testdb --column-inserts -f /data/backup/testdb.dump
4、只导出数据库表结构，不导出数据
pg_dump -h 127.0.0.1 -U dbuser -p 5432 -W testdb -s --column-inserts -f /data/backup/testdb.sql
5、只导出数据，不导出表结构
pg_dump -h 127.0.0.1 -U dbuser -p 5432 testdb --column-inserts -a > testdb.sql
#pg_dumpall命令
pg_dumpall -h 127.0.0.1 -U postgres -p 5432 --column-inserts -f /data/backup/bak.sql
6、将pgsql执行的内容输出到文本文件中
#进入到pgsql控制台，执行 \o 绝对路径的文件位置，然后再执行操作
postgres=> \o /tmp/test.txt
postgres=> select * from access;
二、PostgreSQL数据库导入
#创建新数据库、数据库用户、设置权限
#进入控制台
psql -U postgres -d postgres -h 127.0.0.1 -p 5432
#创建数据库用户newdbuser并设置密码为123456
CREATE USER newdbuser WITH PASSWORD '123456';
#创建数据库newdb，指定所有者为newdbuser
CREATE DATABASE newdb OWNER newdbuser;
#设置用户权限，把newdb数据库的所有权限都赋予newdbuser，否则newdb数据库只能登录控制台，没有任何数据库操作权限
GRANT ALL PRIVILEGES ON DATABASE newdb to newdbuser; #授予用户数据库权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO newdbuser; #授予用户表所有权限
#导入数据库，不需要进入控制台
psql -d databaename(数据库名) -U username(用户名) -f < 路径/文件名.sql
#导入.sql格式
/usr/local/pgsql/bin/psql -d newdb -U newdbuser -f /data/backup/testdb.sql
#导入压缩后.gz格式
gunzip -c /data/backup/testdb.sql.gz | /usr/local/pgsql/bin/psql newdb
或者
cat /data/backup/testdb.sql.gz | gunzip | /usr/local/pgsql/bin/psql newdb
#导入自定义格式的数据备份
/usr/local/pgsql/bin/pg_restore -d newdb /data/backup/newdb.dump
三、PostgreSQL数据库删除
1、使用 DROP DATABASE SQL 语句来删除
#进入数据库控制台
psql -U postgres -d postgres -h 127.0.0.1 -p 5432
#删除数据库daorudb
postgres=# DROP DATABASE daorudb;
2、使用 dropdb 命令来删除
#使用超级用户postgres登录到主机地址为127.0.0.1，端口号为5432的PostgreSQL数据库中并删除testdb数据库
/usr/local/pgsql/bin/dropdb -h 127.0.0.1 -p 5432 -U postgres testdb
4、删除用户
DROP USER newdbuser;
四、添加数据库备份脚本，定时备份数据库
vi /data/backup/pgsql_bak.sh
#!/bin/sh
#备份文件存放目录
bakdir=/data/backup
#要备份的数据库名称
bakdata=testdb
#PostgreSQL数据库安装目录
pgsqldir=/usr/local/pgsql
#数据库主机地址
host=127.0.0.1
#执行备份的数据库用户
bakuser=dbuser
#PostgreSQL运行用户
pgsqlroot=postgres
#PostgreSQL数据库端口
port=5432
#时间格式
date=`date +%Y-%m-%d-%H-%M-%S`
#设置删除30天之前的备份文件
days=30
su - $pgsqlroot -c "$pgsqldir/bin/pg_dump -h $host -U $bakuser -p $port $bakdata --column-inserts | gzip > $bakdir/$bakdata-$date.sql.gz"
sleep 5
find $bakdir/*.sql.gz -mtime +$days -exec rm {} \;
:wq! #保存退出
#添加执行权限
chown postgres.postgres /data/backup/pgsql_bak.sh
chmod +x /data/backup/pgsql_bak.sh
#配置脚本执行的时候不交互输入密码
#根据自己实际IP，端口，数据库名称，数据库用户名，数据库用户的密码，写入配置文件
echo "127.0.0.1:5432:testdb:dbuser:123456" > /home/postgres/.pgpass
chmod 0600 /home/postgres/.pgpass #修改权限
#查看文件
cat /home/postgres/.pgpass
ls -al /home/postgres/.pgpass
#添加计划任务，每天零点执行一次备份
su - root #切换到root用户
echo -e "0 0 * * * /bin/sh /data/backup/pgsql_bak.sh & >/dev/null" >> /var/spool/cron/root
systemctl restart crond
#也可以直接在postgres用户下添加计划任务，去掉脚本里面用户切换su - postgres -c
五、设置postgresql数据库客户端psql版本和服务端server版本一致
#查看客户端版本命令：psql --version
psql (PostgreSQL) 10.15
#客户端版本为10.15，我们安装的服务端版本是11.18
#查找客户端psql的路径命令：which -a psql
/usr/bin/psql
/data/server/pgsql/bin/psql
#备份默认的版本
mv /usr/bin/psql /usr/bin/psql.bak
#软连接新版本到默认路径
ln -s /data/server/pgsql/bin/psql /usr/bin/psql
#再次查看客户端版本：psql --version
psql (PostgreSQL) 11.18
#现在postgresql数据库客户端psql版本和服务端server版本已经一致了。
扩展阅读：
1、pg_dump的常用参数
-h host，指定数据库主机名，或者IP
-p port，指定端口号
-U user，指定连接使用的用户名
-W，按提示输入密码
dbname，指定连接的数据库名称，实际上也是要备份的数据库名称。
-a，–data-only，只导出数据，不导出表结构
-c，–clean，是否生成清理该数据库对象的语句，比如drop table
-C，–create，是否输出一条创建数据库语句
-f file，–file=file，输出到指定文件中
-n schema，–schema=schema，只转存匹配schema的模式内容
-N schema，–exclude-schema=schema，不转存匹配schema的模式内容
-O，–no-owner，不设置导出对象的所有权
-s，–schema-only，只导致对象定义模式，不导出数据
-t table，–table=table，只转存匹配到的表，视图，序列，可以使用多个-t匹配多个表
-T table，–exclude-table=table，不转存匹配到的表。
–inserts，使用insert命令形式导出数据，这种方式比默认的copy方式慢很多，但是可用于将数据导入到非PostgreSQL数据库。
–column-inserts，导出的数据，有显式列名
2、pg_restore常用参数
pg_restore --help
-d, --dbname=名字 连接数据库名字
-f, --file=文件名 输出文件名
-F, --format=c|d|t backup file format (should be automatic)
-l, --list 打印归档文件的 TOC 概述
-v, --verbose 详细模式
--help 显示此帮助信息, 然后退出
--version 输出版本信息, 然后退出恢复控制选项:
-a, --data-only 只恢复数据, 不包括模式
-c, --clean 在重新创建数据库对象之前需要清除（删除）数据库对象
-C, --create 创建目标数据库
-e, --exit-on-error 发生错误退出, 默认为继续
-I, --index=名称 恢复指定名称的索引
-j, --jobs=NUM 可以执行多个任务并行进行恢复工作
-L, --use-list=文件名 从这个文件中使用指定的内容表排序输出
-n, --schema=NAME 在这个模式中只恢复对象
-O, --no-owner 忽略恢复对象所属者
-P, --function=名字(参数) 恢复指定名字的函数
-s, --schema-only 只恢复模式, 不包括数据
-S, --superuser=NAME 使用指定的超级用户来禁用触发器
-t, --table=NAME 恢复指定命字的表
-T, --trigger=NAME 恢复指定命字的触发器
-x, --no-privileges 跳过处理权限的恢复 (grant/revoke)
-1, --single-transaction 作为单个事务恢复
--disable-triggers 在只恢复数据的过程中禁用触发器
--no-data-for-failed-tables 没有恢复无法创建表的数据
--no-security-labels do not restore security labels
--no-tablespaces 不恢复表空间的分配信息
--use-set-session-authorization 使用 SESSION AUTHORIZATION 命令代替ALTER OWNER命令来设置对象所有权
联接选项:
-h, --host=主机名 数据库服务器的主机名或套接字目录
-p, --port=端口号 数据库服务器的端口号
-U, --username=名字 以指定的数据库用户联接
-w, --no-password 永远不提示输入口令
-W, --password 强制口令提示 (自动)
--role=ROLENAME 在恢复前执行SET ROLE操作
至此，Linux下PostgreSQL数据库基本操作完成。
②190706903 ③203744115
