Documentation Outline
  1. About PGLiveBackup
  2. Download And Installation & Repository Initiation
  3. General PGLiveBackup Usage
  4. Backup Operations
    • Manual Database Backup
    • Manual Table(s) Backup
    • Manual Configurations Backup
  5. Restore Operations
    • Restoring Database/Table(s)
    • Restoring Configurations
  6. PGLiveBackup Automation
    • Creating Backup Job (Using Wizard)
    • Creating Backup Job (From Template)
    • Enabling Core Services
    • Enabling Automation
About PGLiveBackup
PGLiveBackup (or PGL) is an implementation of backup and restore automation for PostgreSQL written completely using Python. The main purpose of this solution is to grant DBAs with the tools they need so their backup/restore operations will be done with better information and statistics infront of them. Plus, PGL has reorganized all of the tools/commands the DBA needs into one command line utility. In order to have the information accessible at all times, PGL saves it's metadata in a special schema created on the 'postgres' database (see chapter 2 for more details). If you have any experience with Oracle database administration you might find PGLiveBackup to be a compatible utility to RMAN (Oracle's Recover Manager). PGL is a feature-rich utility that requires you to read the documentation first to understand it but there is a quick-start guide as well (though I do recommend reading the docs first). If you already know PGL from its last version, you will find a vast array of changes. These are the main changes:

PGLiveBackup is released under the MIT license - see https://en.wikipedia.org/wiki/MIT_License for more information. By using PGLiveBackup you fully agree to the license terms.

Download And Installation
As mentioned before - the entire project is built from one python script file called "pgl". The source code can be found on https://github.com/pglivebackup/pglivebackup. Simply download the file to any location in your linux server and execute the following code:

# Python Dependencies (varies between machines) - install using 'root'.
pip install shlex
pip install psycopg2
pip install dateutil
# FYI: Additional core packages used: sys,os,getpass,pwd,grp,random,subprocess,datetime,pycurl,re,string

# Backup Folder (e.g. FRA @ Oracle)
mkdir /pgl
chown -R postgres:postgres /pgl

# Download PGLiveBackup
wget  https://raw.githubusercontent.com/pglivebackup/pglivebackup/master/pgl
mv pgl /pgl/pgl
chmod +x /pgl/pgl
chown postgres:postgres /pgl/pgl

In addition, we will need to pre-determine some environment variables so it would be easier next time we log into the server. In order to do that, we will edit the '~/.bash_profile' of user 'postgres':

# Following values should apear in the '~/.bash_profile' or user 'postgres'
alias pgl='/pgl/pgl'
export PGL_DESTINATION=/pgl
export PGL_PORT=5432
export PGL_RESTART_COMMAND="/usr/pgsql-9.6/bin/pg_ctl restart -D /var/lib/pgsql/" # Your restart command might look different

FYI - The restart command will only be used when and if you choose and only after configurations restore (documented below). As Different linux distros uses different commands (e.g. systemctl on CentOS and service on Debian), your PostgreSQL restart command might look different. Please re-log to the system to allow the bash profile take effect (simply exit back to root and 'su - postgres' again). At this stage, you should be able to simply type 'pgl' anywhere you are and you should see the PGLiveBackup console:


    PGLiveBackup Command Line Utility (http://www.pglivebackup.org).
    Release 2017.04.01 - Licensed under the MIT license.

      PGL>

  If you do not see the above console then something went wrong and you should see an error description - follow the instructions. At the time of this document writing, the full error mapping is under development.

Once you get to the PGL console, the next (and final) step of the installation will be to create the repository. The repository creates several tables in a new schema called "pgl" on database "postgres" itself. To create the repository, simply run the "init" command from the PGL console. Please remember that the "init" command will always drop the schema and recreate it and you will always see a confirmiration message before. Once the repository is created... you are good to go!

General PGLiveBackup Usage
Before delving into the bits and bytes of the backup and restore operations, there are some commands that we need to know before that will help us know how PGL works.

Reviewing Current Configurations
PGL has many configurations and most of them are automatically configured. As the DBA, you may need to see them from time to time and this can be achieved by running the 'status' or '\s' command from the PGL console. You will then see a list of the PGL configurations that looks like this:

    System Status Report
    -----------------------------------------------------------------------------
    PGLiveBackup Version: 2017.04.01 (stable).
    Using PSQL Client Utility: /bin/psql
    Using PG_DUMP Utility: /bin/pg_dump
    Using SCP Utility: /bin/scp
    Using PG_RESTORE Utility: /bin/pg_restore
    Using Auto Response Mode: NO (Can be changed with SET command)
    Error/Info Log File: /var/lib/pgsql/pgl.log
    
    Local Connection & Repository Status
    -----------------------------------------------------------------------------
    Local PostgreSQL Port: 5432
    Using NOREPO mode: NO (Can be changed with SET command)
    Max Backup Size: Not Set (Can be changed with SET command)
    Local PGL Repository: Found Repository
    
    Product Version & Upgrade
    -----------------------------------------------------------------------------
    PGLiveBackup Command Line Utility
    Version: 2017.04.01
    Website: http://www.pglivebackup.org

Reviewing Current Cluster (PostgreSQL Installation) Database Layout
From time to time, you will want to check the existing databases in your PostgreSQL installation. This can be done without leaving PGL by running the 'cluster status' command from the PGL console. The result will be a list of information with the following columns:
Exiting PGL
Quitting PGL is done by running either the 'quit' or '\q' commands.

Backup Operations
It's time to get to the main reason PGL was built for... backup and restore. In this part, we will cover the backup part, the restore part will be covered after that. PGL uses pg_dump to export backups from your PostgreSQL cluster. The exported files are a complete representation of the data, which means that the backup uses the "-Fc" command. There are no incremental backups (at least not yet anyway). The exported backup files (called "parts" in the PGL terminology) are a full and complete binary representation of the database or tables that you exported. Take this fact under important consideration as this will have a huge impact on your data when and if you restore your data. If you are backing up a database, the part will include a complete image of it, including all of its objects (e.g. sequences, functions etc.). If you are backing up a table, the backup will include all of the child objects as well (e.g. triggers, indexes etc.).

Database/Table Backup
Backing up database or tables is actually very simple using PGL. The command starts with 'BACKUP DATABASE' and follows different arguments. Let's review the general syntax for this command:

    BACKUP DATABASE {database_name} WITH
       GRANULARITY = {database/tables},
       INVENTORY = {"table1,table2,table3"}

The 'BACKUP DATABASE' command takes three main details while two are mandatory and the third one (table list) is optional. The database name given must be valid in your PostgreSQL cluster and the granuarity must be either 'database' or 'tables'. Let's review some examples to better understand how to backup databases/tables using PGL.

Manual Database Backup
If we would like to take a complete backup of the entire database, this is the command we will need to run using the PGL console. FYI: the backup can be done online without stopping normal activities.

    PGL>  BACKUP DATABASE {database_name} WITH GRANULARITY = database

Manual Table(s) Backup
If we would like to take a complete backup of several tables only, this can be done very similarilly to the above command, like this:

    PGL>  BACKUP DATABASE {database_name} WITH GRANULARITY = tables, INVENTORY = "table1,table2,table3"

It highly important to remember that the table list must be wrapped with double quotes. Even if you are backing up one table - wrap it with double quotes.

Manual Configurations Backup
The third backup type of PGL is the configurations backup. This kind of backup will backup three main files: postgresql.conf, pg_hba.conf and pg_ident.conf. The syntax for backing up the configuration files is actually extremely simple:

    PGL>  BACKUP CONFIGS

  Automation and repository lookups will be discussed shortly.

Restore Operations
In this part of the documentation, we will be looking on how to restore the database that we want from the parts we took earlier.

Using the LOOKUP command
The LOOKUP command is the main command of "querying" the PGL repository. It will show you the parts related to the object you are looking for (e.g. database/table/config). You can also query the repository table itself (pgl.backuplog) but that does not check for file existance while the lookup does. Let's show how to lookup each object.

Lookup Database Parts
The first object type (highest granularity) is the database object. Let's say I want to see any parts I have related to the database called "iwiz". In order to do that I would need to run the following command:

    PGL>  lookup database iwiz

You will then see a table with possible results and file explanation (and the file existance check which is pretty important):



You can clearly see the physical files (parts) that exist with the database "iwiz". The most important thing that we will need to do now is to decide which part number we would like to restore as we will use the part's number on the restore command (explain below).

Lookup Table Parts
Looking up table parts is pretty similar to the database parts lookup. Let's assume I want to search any part that has a table called "tier1" in it. I would run the following command:

    PGL>  lookup table tier1

The resulting table will show me the parts that have a table called "tier1" in them. Please take in consideration that this search type will only work for backup jobs that their granularity level was set to tables and not for a complete database.



Lookup Configuration Parts
Looking up for configuration parts is done slightly different. As configurations doesn't belong to any database as they belong to the entire instance (cluster) the lookup command for them is simpler:

    PGL>  lookup configs

The resulting table shows the configuration parts available for restore.
IMPORTANT NOTICE:  Restoring a configuration part will overwrite three key files:


Using the RESTORE command
The restore command will always require a part number for its restoration process (the "latest" keyword is under development at this point). Plus, the restore command devides into two main subcommands. The first one is for restoring database/tables and the second one is for restoring configuration parts. In order to restore database or table you should use the following syntax:

    PGL>  restore part ####

This command will result in a confirmiration summary when you will then be asked to approve or reject the restore process. Please see the details provides to you by PGL and make sure that you are restoring the part you intended to. For instance, if I would like to restore a database called "iwiz" and my part number is "1010", this will be my resulting confirmiration message:



Restoring a configuration is slightly different and involves an option to restart PostgreSQL if you choose. Let's examine the basic syntax for the configurations restore:

    RESTORE CONFIGS {part_number} WITH/WITHOUT RESTART

Please remember that restoring the configuration files will overwrite the files. Running the command without restart will cause PGL to run the "pg_reload_conf()" function so the changes that supports runtime changes will take affect as soon as possible. Running the command with restart will use the restart command stored in the environment variable called PGL_RESTART_COMMAND (for user 'postgres'). Should the command stored in the PGL_RESTART_COMMAND is not complete or invalid the restart will fail while the configuration files are already overwritten! - Please make sure the PGL_RESTART_COMMAND is valid (as mentioned above, control the values constantly by editing the "~/.bash_profile" file of the user 'postgres').

PGLiveBackup Automation
The next section will show you how to automate your PGLiveBackup tasks. We will start by creating a backup task tutorial and then we will see how we can automate those tasks using command line arguments and utilizing CRON.

Creating Backup Tasks
In order to create a new backup task, you will need to run the CREATE JOB command and providing a name for the new job. The new job's name must contain only English characters and numbers - any other combination will result in an error. The basic syntax would look like this:

    CREATE JOB {job_name} WITH WIZARD

If there will be a job with the same name in the repository, you will see a relevant error saying that. If there is not such job in the system (according to the name), PGL will start asking you several questions. Let's review the questions asked:
If there are no errors, you will see a green line saying that "The job created succesfully". If you see this message, you are one step closer to your automation. One more way of create a new backup job is to clone an existing one. This can be done by running the following command:

    CREATE JOB {job_name} AS {exiting_job_id}

  The above command is still under final development and testings - I would not recommend using this one on production yet until further changes.

Showing Jobs Status
After we created jobs on our PGL repository, we need a way of seeing their status on screen. This can be achieved by running "JOB STATUS" on the PGL prompt. Let's review the columns and data displayed:
Deleting Jobs
If there are no errors, you will see a green line saying that "The job created succesfully". If you see this message, you are one step closer to your automation. One more way of create a new backup job is to clone an existing one. This can be done by running the following command:

    DROP JOB {job_name}

IMPORTANT: When you delete a job PGL does NOT delete the backup files related to this job.

Automating Your Jobs (Enabling Automation)
When you create tasks, they are not automatically automated. In order to enable automation you have to do two more steps. The first one is automating PGL using CRON and the second one is to enable the core services (explained later). You can enable PGL automation by simply adding the following line to your cron jobs - remember to do this on the crontab of the user 'postgres' and not 'root':

    * * * * * /pgl/2017.04/pgl --automate

Enabling Automation Services
After you completed the first step (crontab editing), you can control if the automation will be active or not from the PGL prompt instead of editing the crontab file again. There are two services - Let's review then now.
You can always enable/disable the automation services by running the following command:

    ENABLE/DISABLE {service_name}