PostgreSQL Backup and Recovery Automation

PostgreSQL Backup and Recovery Automation

Let's explore how to backup and recover PostgreSQL databases.

A critical PostgreSQL client contains valuable data, and PostgreSQL databases should be backed up regularly. Its process is quite simple, and it is important to have a clear understanding of the techniques and assumptions.

SQL Dump

The idea behind this dump method is to generate a text file from DataCenter1 with SQL commands that, when fed back to the DataCenter2 server, will recreate the database in the same state as it was at the time of the dump. In this case, if the Client cannot access the primary server, they can have access to the BCP server. PostgreSQL provides the utility program 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 the standard output. Below, we will see how this can be useful.

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

Dumps created by pg_dump are internally consistent, that is, the dump represents a snapshot of the database as of the time pg_dump begins running. pg_dump does not block other operations on the database while it is working. (Exceptions with an exclusive lock, such as most forms of ALTER TABLE.)

Important: If your database schema relies on OIDs (for instance as foreign keys), you must instruct pg_dump to dump the OIDs as well. To do this, use the -o command-line option.

SQL Dump Automation

  • First, create playbook pgbackup.yml
  • Create role pgbackup, and it 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\s+= ', '') }}"

   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 +%F-%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 this kind of param: pg_password, pg_username & pg_database

Restoring the Dump

The text files created by pg_dump are intended to be read by the psql program. The general command form to restore a dump is psql dbname < infile

Recovering in Data Center2

Infile is what you used as backupoutputfile for the pg_dump command. The database dbname will not be created by this command, so you must create it yourself from template0 before executing psql (e.g., with createdb -T template0 dbname). psql supports options similar to pg_dump's for specifying the database server to connect to and the username to use. See the psql reference page for more information.

Before restoring a SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, then the restore will fail to recreate the objects with the original ownership and/or permissions.

Either way, you will have an only partially restored database. Alternatively, you can specify that the whole dump should be restored as a single transaction, so the restore is either fully completed or fully rolled back. This mode can be specified by passing the -1 or --single-transaction command-line options to psql. When using this mode, be aware that even the smallest of errors can rollback a restore that has already run for many hours. However, that might still be preferable to manually cleaning up a complex database after a partially restored dump.

Recovery of DB

Create pgrecover.yml

Create role for 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

Now pgreceover role

---

  • 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 }}"

Inventory and secret variable file will be the same, which we have in the pgbackup flow.


Using pg_dumpall

pg_dump dumps only a single database at a time, and it does not dump information about roles or tablespaces (because those are cluster-wide rather than per-database). To support convenient dumping of the entire contents of a database cluster, the pg_dumpall program is provided. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as role and tablespace definitions. The basic usage of this command is:

pg_dumpall > outfile

The resulting dump can be restored with psql: psql -f infile Postgres.

(Actually, you can specify any existing database name to start from, but if you are reloading into an empty cluster, then Postgres should usually be used.) It is always necessary to have database superuser access when restoring a pg_dumpall dump, as that is required to restore the role and tablespace information. If you use tablespaces, be careful that the tablespace paths in the dump are appropriate for the new installation.

pg_dumpall works by emitting commands to re-create roles, tablespaces, and empty databases and then invoking pg_dump for each database. This means that while each database will be internally consistent, the snapshots of different databases might not be exactly in-sync.

By implementing minor changes in the automation script, you can change it to pg_dumpall.

Thanks for reading. If you liked this post, share it with all of your programming buddies!

Further reading

☞ How To Manage an SQL Database

☞ The Complete SQL Bootcamp

☞ An Introduction to Queries in PostgreSQL

☞ SQL Database Performance Tuning for Developers

☞ The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

☞ Using PostgreSQL Database with Python

☞ Introduction to SQL Window Functions

☞ MySQL vs PostgreSQL - What to Choose the Right Database for Your Project


Originally published on dzone.com

sql sql-server mysql database web-development

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

Compare ScaleGrid MySQL vs. DigitalOcean Managed Databases - See which offers the best MySQL throughput, latency, and pricing on DigitalOcean across workloads.

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below.

SQL Server System Databases - Basic Concepts

Understanding various SQL Server system databases and their roles is an excellent aid for managing your SQL Server instance.

Exploring MySQL Binlog Server - Ripple

How MySQL Ripple binlog server can help you improve the performance of your master server by introducing a proxy server between the master and slave servers.MySQL does not limit the number of slaves that you can connect to the master server in a replication topology.