postgres安装pg_cron插件及备份恢复

1、安装pg_cron插件

wget https://github.com/citusdata/pg_cron/archive/v1.6.2.tar.gz
cd /usr/local/pgsql
tar -zxvf /u01/soft/pg_cron_v1.6.2.tar.gz
cd pg_cron-1.6.2/
make   PG_CONFIG=/usr/local/pgsql/bin/pg_config
make install  PG_CONFIG=/usr/local/pgsql/bin/pg_config
vim data/postgresql.conf
  shared_preload_libraries = 'pg_cron'
  cron.database_name = 'dbname'
  cron.use_backgroud_works= on 
  max_worker_processes = 16
  max_connections=16000
systemctl restart postgresql

复制

2、恢复前的准备工作，重建数据库及配置权限

dropdb -Upostgres dbname 

postgres=# drop database dbname ;
ERROR:  database "dbname " is being accessed by other users
DETAIL:  There is 1 other session using the database.
postgres=# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'dbname '; drop database dbname  ;

createdb -Upostgres dbname
psql -Upostgres
CREATE ROLE user;
\c dbname 
create extension pg_cron ;
grant usage on schema cron to user;

ALTER ROLE user WITH NOSUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'xxxx';
复制

3、使用pg_restore 恢复pg_dump备份的数据库

v_own=user
v_db=dbname
v_dt=`date '+%Y%m%d'`
pg_dump -Fc -U ${v_own}  -d  ${v_db}  > bak/${v_db}_${v_dt}.bak


pg_restore -vcC -U ${v_own} -d  ${v_db} < ${v_db}_${v_dt}.bak
复制

4、使用psql 恢复pg_dumpall备份的数据库

pg_dumpall -Upostgres -v > xxx.sql

psql -Upostgres -p 5432 < xxx.sql  
复制

5、编译安装pg，手动安装dblink

进入源码目录下的/contrib/dblink，
运行 make && make install 进行安装，
成功后再执行create extension dblink。

6、pg_dumpall备份命令

pg_dumpall -Upostgres  --roles-only

-a
--data-only
只转储数据，不转储模式（数据定义）。

-c
--clean
包括在重建数据库之前清除（移除）它们的 SQL 命令。角色和表空间的DROP命令也会被加入进来。

-f filename
--file=filename
将输出发送到指定的文件中。如果省略，将使用标准输出。

-g
--globals-only
只转储全局对象（角色和表空间），而不转储数据库。

-o
--oids
将对象标识符（OID）转储为数据的一部分。如果你的应用以某种方式引用OID列（例如在外键约束中），请使用这个选项。否则不应该使用这个选项。

-O
--no-owner
不输出用于设置对象所有权以符合原始数据库的命令。默认情况下，pg_dumpall发出ALTER OWNER或SET SESSION AUTHORIZATION语句来设置被创建的模式元素的所有权。除非脚本是由一个超级用户（或者是拥有脚本中所有对象的同一个用户）所运行，这些语句在脚本运行时会失败。要使得一个脚本能被任意用户恢复，但又不想给予该用户所有对象的所有权，可以指定-O。

-r
--roles-only
只转储角色，不转储数据库和表空间。

-s
--schema-only
只转储对象定义（模式），不转储数据。

-S username
--superuser=username
指定要在禁用触发器时使用的超级用户的用户名。只有使用--disable-triggers时，这个选项才相关（通常，最好省去这个选项，而作为超级用户来启动结果脚本来取而代之）。

-t
--tablespaces-only
只转储表空间，不转储数据库和角色。

-v
--verbose
指定细节模式。这将导致pg_dumpall向标准错误输出详细的对象注释以及转储文件的开始/停止时间，还有进度消息。它也会启用pg_dump中的细节输出。

-V
--version
打印pg_dumpall版本并退出。

-x
--no-privileges
--no-acl
防止转储访问特权（授予/收回命令）。

--binary-upgrade
这个选项用于就地升级功能。我们不推荐也不支持把它用于其他目的。这个选项在未来的发行中可能被改变而不做通知。

--column-inserts
--attribute-inserts
将数据转储为带有显式列名的INSERT命令（INSERT INTO table(column, ...) VALUES ...）。这将使得恢复过程非常慢，这主要用于使转储能够被载入到非PostgreSQL数据库中。

--disable-dollar-quoting
这个选项禁止在函数体中使用美元符号引用，并且强制它们使用 SQL 标准字符串语法被引用。

--disable-triggers
只有在创建一个只转储数据的转储时，这个选项才相关。它指示pg_dumpall包括在数据被重新载入时能够临时禁用目标表上的触发器的命令。如果你在表上有引用完整性检查或其他触发器，并且你在数据重新载入期间不想调用它们，请使用这个选项。

当前，为--disable-triggers发出的命令必须作为超级用户来执行。因此，你还应当使用-S指定一个超级用户名，或者宁可作为一个超级用户启动结果脚本。

--if-exists
时间条件性命令（即增加一个IF EXISTS子句）来清除数据库和其他对象。 只有同时指定了--clean时，这个选项才可用。

--inserts
将数据转储为INSERT命令（而不是COPY）。这将使得恢复非常慢，这主要用于使转储能够被载入到非PostgreSQL数据库中。注意如果你已经重新安排了列序，该恢复可能会一起失败。--column-inserts选项对于列序改变是安全的，但是会更慢。

--lock-wait-timeout=timeout
在转储的开始从不等待共享表锁的获得。而是在指定的timeout内不能锁定一个表时失败。超时时长可以用SET statement_timeout接受的任何格式指定（允许的值根据你从其转出的服务器版本变化，但是从 7.3 以来的所有版本都接受一个整数表示的毫秒数。如果从 7.3 以前的服务器转出，这个选项会被忽略。）。

--no-publications
不转储发布。

--no-role-passwords
不要为角色转储密码。恢复时，角色将具有空密码，并且在设置密码之前密码验证将始终失败。 由于指定此选项时不需要密码值，因此将从目录视图pg_roles 中读取角色信息，而不是从pg_authid中读取角色信息。 因此，如果访问pg_authid受到某些安全策略的限制， 该选项也会有所帮助。

--no-security-labels
不转储安全标签。

--no-subscriptions
不转储订阅。

--no-sync
默认情况下，pg_dumpall将等待所有文件安全写入磁盘。 这个选项会导致pg_dumpall无需等待而返回，这更快， 但意味着后续的操作系统崩溃可能会导致转储损坏。通常，此选项对于测试非常有用， 但在从生产安装中转储数据时不应使用此选项。

--no-tablespaces
不要输出选择表空间的命令。通过这个选项，在恢复期间所有的对象都会被创建在任何作为默认的表空间中。

--no-unlogged-table-data
不转储非日志记录表的内容。这个选项对于表定义（模式）是否被转储没有影响，它只会限制转储表数据。

--quote-all-identifiers
强制引用所有标识符。在从一个与pg_dumpall主版本不同的PostgreSQL服务器转储数据库时或者要将输出载入到一个不同主版本的服务器时，推荐使用这个选项。默认情况下，pg_dumpall只会对为其主版本中保留词的标识符加上引号。在与其他版本的具有不同保留词集合的服务器交互时，这有时会导致兼容性问题。使用--quote-all-identifiers可以阻止这类问题，但是代价是转储脚本会更加难读。

--use-set-session-authorization
输出 SQL-标准的SET SESSION AUTHORIZATION命令取代ALTER OWNER命令来确定对象的所有关系。这让该转储更加兼容标准，但是取决于该转储中对象的历史，该转储可能无法正常恢复。

-?
--help
显示有关pg_dumpall命令行参数的帮助并退出。

下列命令行选项控制数据库连接参数。

-d connstr
--dbname=connstr
以一个连接字符串的形式，指定用来连接到服务器的参数。详见第 33.1.1 节。

这个选项被称为--dbname是为了和其他客户端应用一致，但是因为pg_dumpall需要连接多个数据库，连接字符串中的数据库名将被忽略。使用-l选项指定一个数据库，该数据库被用来转储全局对象并且发现需要转储哪些其他数据库。

-h host
--host=host
指定服务器正在运行的机器的主机名。如果该值开始于一个斜线，它被用作一个 Unix 域套接字的目录。默认是从PGHOST环境变量中取得（如果被设置），否则将尝试一次 Unix 域套接字连接。

-l dbname
--database=dbname
指定要连接到哪个数据库转储全局对象以及发现要转储哪些其他数据库。如果没有指定，将会使用postgres数据库，如果postgres不存在，就使用 template1。

-p port
--port=port
指定服务器正在监听连接的 TCP 端口或本地 Unix 域套接字文件扩展名。默认是放在PGPORT环境变量中（如果被设置），否则使用编译在程序中的默认值。

-U username
--username=username
要作为哪个用户连接。

-w
--no-password
从不发出一个口令提示。如果服务器要求口令认证并且没有其他方式提供口令（例如一个.pgpass文件），那儿连接尝试将失败。这个选项对于批处理任务和脚本有用，因为在其中没有一个用户来输入口令。

-W
--password
强制pg_dumpall在连接到一个数据库之前提示要求一个口令。

这个选项从来不是必须的，因为如果服务器要求口令认证，pg_dumpall将自动提示要求一个口令。但是，pg_dumpall将浪费一次连接尝试来发现服务器想要一个口令。在某些情况下，值得键入-W来避免额外的连接尝试。

注意对每个要被转储的数据库，口令提示都会再次出现。通常，最好设置一个~/.pgpass文件来减少手工口令输入。

--role=rolename
指定一个用来创建该转储的角色名。这个选项导致pg_dump在连接到数据库后发出一个SET ROLE rolename命令。当已认证用户（由-U指定）缺少pg_dump所需的特权但是能够切换到一个具有所需权利的角色时，这个选项很有用。一些安装有针对直接作为超级用户登录的策略，使用这个选项可以让转储在不违反该策略的前提下完成。
复制

7、pg_dump备份命令

下列命令选项控制输出的内容和格式。

dbname
指定要被转储的数据库名。如果没有指定，将使用环境变量PGDATABASE。如果环境变量也没有设置，则使用指定给该连接的用户名。

-a
--data-only
只转储数据，而不转储模式（数据定义）。表数据、大对象和序列值都会被转储。

这个选项类似于指定--section=data，但是由于历史原因又不完全相同。

-b
--blobs
在转储中包括大对象。这是当--schema、--table或 --schema-only被指定时的默认行为，因此-b 开关仅对于将大对象添加到已请求特定模式或表的转储中时有用。 请注意，blob被视为数据，因此仅在使用--data-only时才会包含， 但在--schema-only时不会包含。

-B
--no-blobs
排除转储中的大对象。

当给定-b和-B时，行为是当数据被转储时输出大对象， 请参阅-b文档。

-c
--clean
在输出创建数据库对象的命令之前输出清除（删除）它们的命令 （除非也指定了--if-exists，如果任何对象不存在于 目的数据库中，恢复可能会产生一些伤害性的错误消息）。

这个选项只对纯文本格式有意义。对于归档格式，你可以在调用pg_restore时指定该选项。

-C
--create
使得在输出的开始是一个创建数据库本身并且重新连接到被创建的数据库的命令（通过这种形式的一个脚本，在运行脚本之前你连接的是目标安装中的哪个数据库都没有关系）。如果也指定了--clean，脚本会在重新连接到目标数据库之前先删除它然后再重建。

这个选项只对纯文本格式有意义。对于归档格式，你可以在你调用pg_restore时指定这个选项。

-E encoding
--encoding=encoding
以指定的字符集编码创建转储。在默认情况下，该转储会以该数据库的编码创建（另一种得到相同结果的方式是将PGCLIENTENCODING环境变量设置成想要的转储编码）。

-f file
--file=file
将输出发送到指定文件。对于基于输出格式的文件这个参数可以被忽略，在那种情况下将使用标准输出。不过对于目录输出格式必须给定这个参数，在目录输出格式中指定的是一个目录而不是一个文件。在这种情况中，该目录会由pg_dump创建并且不需要以前就存在。

-F format
--format=format
选择输出的格式。format可以是下列之一：

p
plain
输出一个纯文本形式的SQL脚本文件（默认值）。

c
custom
输出一个适合于作为pg_restore输入的自定义格式归档。和目录输出格式一起，这是最灵活的输出格式，它允许在恢复时手动选择和排序已归档的项。这种格式在默认情况还会被压缩。

d
directory
输出一个适合作为pg_restore输入的目录格式归档。这将创建一个目录，其中每个被转储的表和大对象都有一个文件，外加一个所谓的目录文件，该文件以一种pg_restore能读取的机器可读格式描述被转储的对象。一个目录格式归档能用标准 Unix 工具操纵，例如一个未压缩归档中的文件可以使用gzip工具压缩。这种格式默认情况下是被压缩的并且也支持并行转储。

t
tar
输出一个适合于输入到pg_restore中的tar-格式归档。tar 格式可以兼容目录格式，抽取一个 tar 格式的归档会产生一个合法的目录格式归档。不过，tar 格式不支持压缩。还有，在使用 tar 格式时，表数据项的相对顺序不能在恢复过程中被更改。

-j njobs
--jobs=njobs
通过同时归档njobs个表来运行并行转储。这个选项缩减了转储的时间，但是它也增加了数据库服务器上的负载。你只能和目录输出格式一起使用这个选项，因为这是唯一一种让多个进程能在同一时间写其数据的输出格式。

pg_dump将打开njobs + 1 个到该数据库的连接，因此确保你的max_connections设置足够高以容纳所有的连接。

在运行一次并行转储时请求数据库对象上的排他锁可能导致转储失败。其原因是，pg_dump主控进程会在工作者进程将要稍后转储的对象上请求共享锁，以便确保在转储运行时不会有人删除它们并让它们出错。如果另一个客户端接着请求一个表上的排他锁，那个锁将不会被授予但是会被排入队列等待主控进程的共享锁被释放。因此，任何其他对该表的访问将不会被授予或者将排在排他锁请求之后。这包括尝试转储该表的工作者进程。如果没有任何防范措施，这可能会是一种经典的死锁情况。要检测这种冲突，pg_dump工作者进程使用NOWAIT选项请求另一个共享锁。 如果该工作者进程没有被授予这个共享锁，其他某人必定已经在同时请求了一个排他锁并且没有办法继续转储，因此pg_dump除了中止转储之外别无选择。

对于一个一致的备份，数据库服务器需要支持同步的快照，在PostgreSQL 9.2中引入了一种针对主服务器特性和10个针对备用服务器的特性。有了这种特性，即便数据库客户端使用不同的连接，也可以保证他们看到相同的数据集。pg_dump -j使用多个数据库连接，它用主控进程连接到数据一次，并且为每一个工作者任务再一次连接数据库。如果没有同步快照特征，在每一个连接中不同的工作者任务将不能被保证看到相同的数据，这可能导致一个不一致的备份。

如果你希望运行一个 9.2 之前服务器的并行转储，你需要确保数据库内容从主控进程连接到数据库一直到最后一个工作者任务连接到数据库之间不会改变。做这些最简单的方法是在开始备份之前停止任何访问数据库的数据修改进程（DDL 以及 DML）。当对一个 9.2 之前的PostgreSQL服务器运行pg_dump -j时，你还需要指定--no-synchronized-snapshots参数。

-n schema
--schema=schema
只转储匹配schema的模式，这会选择模式本身以及它所包含的所有对象。当没有指定这个选项时，目标数据库中所有非系统模式都将被转储。多个模式可以通过书写多个-n开关来选择。另外，schema参数可以被解释为一种根据psql's \d命令所用的相同规则（见模式（Pattern））编写的模式，这样多个模式也可以通过在该模式中书写通配字符来选择。
复制

8、注意访问控制，pg_hba.conf,postgres.conf配置

遇到的小问题

psql: error: connection to server at "192.168.XX.CCC", port 5432 failed: 拒绝连接
        Is the server running on that host and accepting TCP/IP connections?
复制

网络上解决方案都差不多:
（1）修改data目录下postgresql.conf，设置listen_address=’*’
（2）修改data目录下pg_hba.conf，添加host all all 0.0.0.0/0 trust
（3）设置或者关闭windows防火墙

vim postgresql.conf
listen_address='*'
vim pg_hba.conf
local   postgres             all                         trust
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0              scram-sha-256
host    all             all             ::1/128                 trust
host    all             all             ::/128               md5
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
复制

9、修改postgres密码

#PostgreSQL数据库默认创建管理员账号：postgres；修改其密码，仅需一下三步：
#1、首先，登录PostgreSQL
sudo -u postgres
 psql -Upostgres -p 5432
#2、然后，修改账号postgres的密码
ALTER USER postgres WITH PASSWORD 'passwd';
#3、最后，退出pgsql客户端
exit
复制