Shulou(Shulou.com)06/01 Report--

Most people do not understand the knowledge points of this "PostgreSQL backup and recovery Automation method" article, so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "PostgreSQL backup and recovery automation method" article.

SQL dump

The idea behind this dump method is to use the SQL command to generate a text file from DataCenter1, and when fed back to the DataCenter2 server, the database in the same state as at the time of the dump will be recreated. In this case, if the client cannot access the primary server, they can access the BCP server. PostgreSQL provides a utility pg_dump for this purpose. The basic usage of this command is: pg_dump dbname > backupoutputfile.db.

As you can see, pg_dump writes its results to standard output. Next, we'll see how this works.

Pg_dump is a regular PostgreSQL client application. This means that you can perform this backup process from any remote host that has access to the database. Pg_dump does not run with special privileges. In particular, it must have read access to all tables you want to back up, so in practice, you almost always have to run it as a database superuser.

The dumps created by pg_dump are consistent internally, that is, they represent a snapshot of the database when pg_dump starts running. Pg_dump does not block other operations on the database while it is working. (exceptions with exclusive locks, such as most forms of ALTER TABLE. )

Important: if your database schema depends on OID (for example, as a foreign key), you must instruct pg_dump to dump OID as well. To do this, use the-o command line option.

SQL dump automation

First, create the script pgbackup.yml

Create a role pgbackup, which will be called from pgbackup.yml

Pgbackup.yml

-hosts: database_prim:database_replica gather_facts: true vars_files:-mysecret_vars/ {{environ}} .yml # This is to Identify if DB is Primary and replicating data to secondary tasks:-name: select pg status command: psql-c "SELECT pg_is_in_recovery () "register: IsPromoted changed_when: False environment: PGDATABASE:" {{pg_database}} "PGUSER:" {{pg_username}} "PGPASSWORD:" {{pg_password}} "# Get the DB parameter from run time on Client application Not required if you have parameters-block:-name: Get client database settings shell: "awx-manage print_settings | grep'^ DATABASES'" register: results changed_when: False delegate_to: "{{groups ['client'] [0]}}"-name: Ingest client database settings set_fact: client_db_settings: "{{results.stdout | regex_replace (' DATABASES\\ slots =' '')} "delegate_to:" {{groups ['client'] [0]}} "- include_role: name: pgbackup when:"' f'in IsPromoted.stdout "tags: pgbackup

Pgbackup role

-name: Determine the timestamp for the backup. Set_fact: now:'{lookup ("pipe", "date +% FMI% T")}}'- name: Create a directory for a backup to live. File: path:'{backup_dir.rstrip ("/")}} / {{now}} / 'mode: 0775 owner: root state: directory- name: Create a directory for non-instance specific backups file: path:' {{backup_dir.rstrip ("/")}} / common/' mode: 0775 owner: root state: directory# create dump, Here adding runtime param. You can add param whatever ways- name: Perform a PostgreSQL dump. Shell: "pg_dump-- clean-- create-- host=' {{client_db_settings.default.HOST}}'--port= {{client_db_settings.default.PORT}}'- username=' {{tower_db_settings.default.USER}}'--dbname=' {{tower_db_settings.default.NAME}}'> pgbackup.db" args: chdir:'{backup_dir.rstrip ("/")}} / common/' Environment: PGPASSWORD: "{{client_db_settings.default.PASSWORD}}"-name: Copy file with owner and permissions copy: src:'{{backup_dir.rstrip ("/")}} / common/pgbackup.db' dest:'{{backup_dir.rstrip ("/")}} / {{now}} / 'remote_src: yes

Inventory file

[all:vars] # database settings.linux.us.ams1907.com[client] linuxclient.us.com[database _ prim] linuxmas.us.com[database _ replica] linuxreplica.us.com

Secret variable storage: create this file if you want to store any encrypted data

Mysecret_vars/ {{environ}}. Yml ansible-vault encrypt mysecretvar.yml

Store such parameters: pg_password, pg_username, and pg_database

Restore dump

Text files created by pg_dump are intended to be read by psql programs. The general form of command to recover a dump is psql dbname

< infile 在数据中心 2 中恢复 Infile 是您用作 pg_dump 命令的备份输出文件的文件。该命令不会创建数据库 dbname，因此您必须在执行 psql 之前从 template0 自己创建它（例如，使用 createdb -T template0 dbname）。psql 支持类似于 pg_dump 的选项，用于指定要连接的数据库服务器和要使用的用户名。有关更多信息，请参阅 psql 参考页。 在还原 SQL 转储之前，拥有对象或被授予转储数据库中对象权限的所有用户必须已经存在。如果不这样做，则还原将无法重新创建具有原始所有权和/或权限的对象。 无论哪种方式，您都将拥有一个仅部分恢复的数据库。或者，您可以指定整个转储应作为单个事务恢复，以便完全完成或完全回滚恢复。这种模式可以通过将 -1 或 --single-transaction 命令行选项传递给 psql 来指定。使用此模式时，请注意，即使是最小的错误也可能回滚已运行数小时的还原。但是，这可能仍然比在部分还原转储后手动清理复杂数据库更可取。 数据库恢复 创建 pgrecover.yml 为 pgrecover 创建角色 pgrecover.yml - hosts: database_prim[0] tasks: - name: Get client database settings shell: "awx-manage print_settings | grep '^DATABASES'" register: results changed_when: False delegate_to: "{{ groups['client'][0] }}" - name: Ingest client database settings set_fact: tower_db_settings: "{{ results.stdout | regex_replace('DATABASES\\s+= ', '') }}" delegate_to: "{{ groups['client'][0] }}"# Create User - name: PostgreSQL | Create test user if its not there postgresql_user: name: "test" password: "{{ client_db_settings.default.PASSWORD }}" port: "5432" state: present login_user: "postgres" no_password_changes: no become: yes become_user: "postgres" become_method: su # Create Database - name: PostgreSQL | Create test Database if its not there postgresql_db: name: "test" owner: "test" encoding: "UTF-8" lc_collate: "en_US.UTF-8" lc_ctype: "en_US.UTF-8" port: "5432" template: "template0" state: present login_user: "postgres" become: yes become_user: "postgres" become_method: su - include_role: name: pgrecover 现在 pgreceover 角色 ---- name: Create a directory for non-instance specific backups file: path: '{{ backup_dir.rstrip("/") }}/restore/' mode: 0775 owner: root state: directory- name: Copy file for restore copy: src: '{{ backup_dir.rstrip("/") }}/common/client.db' dest: '{{ backup_dir.rstrip("/") }}/restore/' remote_src: yes- name: Perform a PostgreSQL restore shell: "psql --host='{{ client_db_settings.default.HOST }}' --port={{ client_db_settings.default.PORT }} --username='{{ client_db_settings.default.USER }}' --dbname='test' < ./client.db" args: chdir: '{{ backup_dir.rstrip("/") }}/restore/' environment: PGPASSWORD: "{{ client_db_settings.default.PASSWORD }}" 库存和秘密变量文件将与我们在 pgbackup 流程中的相同。 使用 pg_dumpall pg_dump 一次只转储一个数据库，它不会转储有关角色或表空间的信息（因为它们是集群范围的而不是每个数据库的）。为了支持方便地转储数据库集群的全部内容，提供了 pg_dumpall 程序。pg_dumpall 备份给定集群中的每个数据库，还保留集群范围的数据，例如角色和表空间定义。该命令的基本用法是： pg_dumpall >

Output file

You can use psql to recover the generated dump: psql-f infile Postgres.

In fact, you can specify any existing database name as a starting point, but if you want to reload into an empty cluster, you should usually use Postgres. Database superuser access is always required when restoring pg_dumpall dumps, because that is the need to restore role and tablespace information If you use tablespaces, note that the tablespace path in the dump is suitable for the new installation.

Pg_dumpall re-creates roles, tablespaces, and empty databases by issuing commands, and then calls pg_dump for each database. This means that while each database will remain consistent internally, snapshots of different databases may not be fully synchronized.

You can change it to pg_dumpall by making minor changes in the automation script.

The above is the content of this article on "methods of PostgreSQL backup and recovery automation". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.