pg_dump备份和恢复

pg_dump -h 10.0.0.7 -U postgres -f /backup/hellodb.bak hellodb

备份远程数据库服务器到本地。

-h 指定远程主机

-f 指定本地目录和文件名

-U 指定用户 

-C 可以把create database的命令备份出来，恢复的时候不用手动建数据库

本地目标目录的权限

最后面hellodb是数据库名   

#注意远程数据pg_hba.conf 配置文件中的授权    postgressql.conf配置文件中 监听网段和端口 

listen_addresses = '*'          # what IP address(es) to listen on;

port = 5432                             # (change requires restart)

恢复数据库到本地

[postgres@localhost ~]$psql
psql (14.6)
Type "help" for help.

postgres=# create database hellodbbak;
CREATE DATABASE
postgres=# 
[postgres@localhost ~]$psql -d hellodbbak -U postgres -f /backup/hellodb.bak 

SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 8
COPY 14
COPY 7
COPY 15
COPY 25
COPY 4
COPY 0
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
[postgres@localhost ~]$

验证

postgres=# \c hellodbbak 
You are now connected to database "hellodbbak" as user "postgres".
hellodbbak=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

hellodbbak=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | classes  | table | postgres
 public | coc      | table | postgres
 public | courses  | table | postgres
 public | scores   | table | postgres
 public | students | table | postgres
 public | teachers | table | postgres
 public | toc      | table | postgres
(7 rows)

hellodbbak=#