pg_restore

     pg_restore restores a PostgreSQL database from an archive file created by pg_dump.

     pg_restore [connection-option...] [option...] [filename]

     Example:

     We have dumped a database called mydb into a custom-format dump file:

     pg_dump -Fc mydb > db.dump

     To drop the database and recreate it from the dump:

     $ dropdb mydb

     $ pg_restore -C -d postgres db.dump

 

      The database named in the -d switch can be any database existing in the  cluster; pg_restore only uses it to issue the CREATE DATABASE command  for mydb. With -C, data is always restored into the database name that appears in the dump file.

   To reload the dump into a new database called newdb:

    $ createdb -T template0 newdb

    $ pg_restore -d newdb db.dump

 

    Notice we don't use -C, and instead connect directly to the database to be restored into. Also note that we clone the new database from template0 not template1, to ensure it is initially empty.

pg_switch_wal()

pg_switch_wal() is a system function which forces PostgreSQL to switch to a new WAL file.

    pg_switch_wal() returns the end LSN + 1 of the old WAL file.

    However, if there has been no activity which generates WAL since the last WAL file switch, a switch will not be carried out and the start location of the current WAL file will be returned.

    pg_switch_wal() can only be executed on a primary server (i.e. not a read-only standby).

    postgres=# SELECT pg_switch_wal();

    pg_switch_wal

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

    0/161E050

     If there has been no activity which generated WAL since the last WAL file  switch, a new WAL file will not be created:

 

    postgres=# SELECT pg_walfile_name(pg_switch_wal()), pg_walfile_name(pg_switch_wal());

     pg_walfile_name      |     pg_walfile_name     

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

 000000010000000200000086 | 000000010000000200000086

(1 

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

PostgreSQL Backup

    pg_basebackup is used to take base backups of a running PostgreSQL database cluster. We can take hot-backup and the backup can be used for...

   1.     point-in-time recovery as the starting point for a log shipping or 

   2.     streaming replication standby servers  

    It makes a binary copy of the database cluster files, while making sure the system is put in and out of backup mode automatically. Backups are always taken of the entire database cluster; it is not possible to back up individual databases or database objects.

    For individual database backups, a tool such as pg_dump must be used.

    The backup is made over a regular PostgreSQL connection, and uses the replication protocol. The connection must be made with a superuser or a user having REPLICATION permissions  and pg_hba.conf must explicitly permit the replication connection. The server must also be configured with max_wal_senders set high enough to leave at least one session available  for the backup and one for WAL streaming (if used).

    There can be multiple pg_basebackups running at the same time.

    pg_basebackup can make a base backup from not only the master but also the standby.

    Note that there are some limitations in an online backup from the standby:

    The backup history file is not created in the database cluster backed up.

    pg_basebackup cannot force the standby to switch to a new WAL file at the end of backup. When you are using -X none, if write activity on the primary is low, pg_basebackup may need to wait a long time for the last WAL file required for the backup to be switched and archived. In this case, it may be useful to run pg_switch_wal on the primary in order to trigger an immediate WAL file switch.

     If the standby is promoted to the master during online backup, the backup fails.

     All WAL records required for the backup must contain sufficient full-page writes, which requires you to enable full_page_writes on the master and not to use a tool like pg_compresslog as archive_command to remove full-page writes from WAL files.