技术交流QQ群:①185473046   ②190706903   ③203744115   网站地图
登录

下次自动登录
现在的位置: 首页PostgreSQL>正文
Linux下PostgreSQL数据库基本操作
2022年06月28日 PostgreSQL 暂无评论 ⁄ 被围观 6,254次+

说明:

1、PostgreSQL 使用 pg_dumppg_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数据库基本操作完成。

     

  系统运维技术交流QQ群:①185473046 系统运维技术交流□Ⅰ ②190706903 系统运维技术交流™Ⅱ ③203744115 系统运维技术交流™Ⅲ

给我留言

您必须 [ 登录 ] 才能发表留言!



Copyright© 2011-2024 系统运维 All rights reserved
版权声明:本站所有文章均为作者原创内容,如需转载,请注明出处及原文链接
陕ICP备11001040号-3