PG Backup Dump

Types of DB backup

·         Logical BACKUP  <pg_dump>

·         Physical Backup 

PG SQL Dump

    Three tasks we are going to do... 

       Restoring a backup

    Performing a backup

    Scheduling a backup job to run daily at 1am

Create acweb Database

createdb acweb

Download TestDB from gitHub

wget https://github.com/linuxacademy/content-postgresql-deepdive/raw/master/acweb/acweb.tar

Restore the downloaded DB

-bash-4.2$ pg_restore --dbname=acweb --verbose acweb.tar

Check Tables…

acweb=# \dt *.*

                        List of relations

       Schema       |          Name           | Type  |  Owner

--------------------+-------------------------+-------+----------

 hr                 | staff                   | table | postgres

Download a script to create our access log table in the dev database:

wget https://raw.githubusercontent.com/linuxacademy/content-postgresql-deepdive/master/acweb/accessLog.sql

Now execute the script on the DB

psql -f accessLog.sql acweb

Create a DIR for backup…

mkdir backups

 pg_dump -F t acweb > backups/acwebdev.tar

 Schedule a Backup

Download this backup script:

wget https://raw.githubusercontent.com/linuxacademy/content-postgresql-deepdive/master/acweb/pg_backup.sh

 

Make the script executable:

chmod +x pg_backup.sh

Test the script by executing it:

./pg_backup.sh

Edit Edit crontab: to run the backup at 1 AM daily.

crontab –e

0 1 * * * /var/lib/pgsql/pg_backup.sh

 crontab -l

0 1 * * * /var/lib/pgsql/pg_backup.sh

For backup PostgreSQL databases using the pg_dump and pg_dumpall tool.

Type of backups:

• Full / partial databases
• Both data and structures, or only structures
• Point-in-time recovery
• Restore performance

 pg_dump -U username -W -F t database_name > \u05\backup\backup_file.tar

-F : specifies the output file format that can be one of the following:

• c: custom-format archive file format
• d: directory-format archive
• t: tar
• p: plain-text SQL script file).

 pg_dumpall program exports all databases, one after another, into a single script file.

pg_dumpall -U postgres > \u05\pgbackup\all.sql

Backup database object definitions

pg_dumpall --schema-only > \u05\pgdump\definitiononly.sql

pg_dumpall --roles-only > \u05\pgdump\allroles.sql

pg_dumpall --tablespaces-only > \u05\pgdump\allroles.sql

Restore Command

Note: We need to create DB manually.  

psql <db_name> <<Fine_Name.sql>>

psql db_name <  backup_dump_file.sql

Physical Backup

 

For Backup

Step 1. Enable WAL (For PITR)

                Created /var/lib/pgsql/11/archive_wal

                Restarted DB instance.

/*

#------------------------------------------------------------------------------

# WRITE-AHEAD LOG

#------------------------------------------------------------------------------

# - Settings -

#wal_level = replica                    # minimal, replica, or logical*/

Step 2. Take backup (Full)

 

 

/var/lib/pgsql/backup/pg_db_bkp

 

pg_basebackup -Ft -D /var/lib/pgsql/backup/pg_db_bkp -P

 

{Don’t use it} pg_basebackup --pgdata=/var/lib/pgsql/11/replicated_data -P

op=> 373575/373575 kB (100%), 1/1 tablespace

-D directory

-F format

t tar

-z gzip

-P progress

  

For Restore

                Step 1. Stop DB Server

                                                systemctl stop postgresql-11

                Step 2. Delete all the data

cd /var/lib/pgsql/11/data/

rm -rf *

                                               

Step 3. Extract tar files

                                tar xvf /var/lib/pgsql/backup/pg_db_bkp/base.tar -C /var/lib/pgsql/11/data/

                                tar xvf /var/lib/pgsql/backup/pg_db_bkp/pg_wal.tar -C /var/lib/pgsql/11/data/pg_wal/

 

Step 4. Add recovery.conf

vi /var/lib/pgsql/11/data/recovery.conf

 

restore_command = 'cp /var/lib/pgsql/11/archive_wal/%f "%p"'

recovery_target_time = '2020-09-14 16:02:00 UTC'

recovery_target_name = 'flag-1'

recovery_target_inclusive = false

 

/*Notes

·         recovery_target -> recovery should end as soon as a consistent state is reached

·         recovery_target_name -> specifies the named restore point (created with pg_create_restore_point()) to which recovery will proceed.

sep7_2020=# select pg_create_restore_point('flag_1');

 pg_create_restore_point

-------------------------

 0/690001A8

(1 row)

·         recovery_target_time -> specifies the time stamp up to which recovery will proceed. The precise stopping point is also influenced by recovery_target_inclusive.

recovery_target_inclusive (boolean)

Specifies whether to stop just after the specified recovery target (true), or just before the recovery target (false).

 */

  

Start DB Instance