Re: Backup restore for upgrade

Hi All,

I am trying to upgrade one of my 1TB databases from pg9.5 to 10 and having some troubles.

First I tried using pglogical and it failed because most of the tables didn't have a primary key.

Second, i tried pg_upgrade and it also failed with some really weird errors.

At this point, I am really convinced that the upgrade can only be possible by me taking a dump of the old version and restore it in the new version on a new server.

So I am working on this script to help me take a dump from the old version server and restore it into the pg10 server. 
I am trying to speed up the process using some jobs based on my cpu.

The script doesn't work as intended...especially the restore part as it just stops at the restore part. Maybe the dump format..??

Please can someone help me review the script below maybe i am making errors at defining the variables or at the function level.

OR if you have a better script to recommend i will appreciate it.

Thanks

script

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

#!/bin/bash

jobs=6;
mydate=$(date +"%Y%m%d%H%M%S");
env=prod;
dbname=test;
rds_pguser=test_user;
host=localhost;

backupDir=/var/lib/data/backup/`date +%Y-%m-%d`;
sqlDir=${backupDir}/sql;
logDir=${backupDir}/logs;
backuproles=${backupDir}/globals-${env}-${mydate}.sql;

# Logs source instance
roles_logs=${logDir}/roles_logs-${env}-${mydate}.log;
pgdump_logs=${logDir}/pgdump_logs-${env}-${mydate}.log;
restore_logs=${logDir}/restore_logs-${env}-${mydate}.log;
process_log=${logDir}/process_log-${env}-${mydate}.log;

# RDS Target host
target_rds_host="IP of destination server";
rds_pguser=test_user;
new_dbname=test;
wait_in_seconds=1800;

GREEN="\033[32m"; BLUE="\033[34m"; RED="\033[31m"; RESET="\033[0m"; YELLOW="\033[33m";

# Functions
log() {
  local log_info=$1;
  if [[ "${log_info}" == "INFO" ]]; then
    echo -e "${BLUE}[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2 ${RESET}";
    echo -e "[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2" >> ${process_log};
  elif [[ "${log_info}"  == "WARN" ]]; then
    echo -e "${YELLOW}[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2 ${RESET}";
    echo -e "[${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2" >> ${process_log};
  elif [[ "${log_info}" ==  "SUCC" ]]; then
    echo -e "${GREEN}[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2 ${RESET}";
    echo -e "[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2" >> ${process_log};
  elif [[ "${log_info}" ==  "EXIT" ]]; then
    echo -e "${RED}[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2 ${RESET}";
    echo -e "[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2" >> ${process_log};
  elif [[ "${log_info}" ==  "ERROR" ]]; then
    echo -e "${RED}[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2 ${RESET}";
    echo -e "[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2" >> ${process_log};
  fi
}

error_exit() {
  log "EXIT" "Exiting due to errors!";
  #send_email "${body_mail_log}" "${status}"
  exit 1;
}

createLogFiles() {

  for file in ${roles_logs} ${pgdump_logs} ${restore_logs} ${process_log}; do
    touch ${file};
    ret=$?;
    if [ ${ret} -ne 0 ]; then
      echo "ERROR: Unable to create file [${file}]: ${ret}";
      exit 1;
    fi;
  done;

}

createDirs() {

  for dir in ${backupDir} ${logDir} ${sqlDir}; do
    if [[ ! -d "${dir}" ]]; then
      mkdir -p ${dir}; ret=$?;
      if [ ${ret} -ne 0 ]; then
        echo "ERROR: Failed creating directory [${dir}] failed: ${ret}";
        exit 1;
      fi;
    fi;
  done;

}

dumpRoles() {

  log "INFO" "Start backing up PG-Roles."

  cmd='/bin/pg_dumpall -v -g';
  log "INFO" "Running: ${cmd}"; #    DUMP: ${backuproles}  ERRORS: ${roles_logs}";
  exec 1>${backuproles};
  exec 2> ${roles_logs};
  ${cmd};
  ret=$?;
  exec 1>/dev/tty 2>&1;

  if [[  $ret -gt 0 && $ret -lt 127 ]]; then
    log "ERROR" "Backup for PG roles failed. See ${roles_logs} for details";
    error_exit;
  else
    log "INFO" "Roles were successfully backed up to ${backuproles}"
  fi

}

dumpDB() {

  log "INFO" "Starting pg_dump on ${dbname}"
  cmd="pg_dump --dbname ${dbname} --jobs=${jobs} -Fd -f ${sqlDir}/${dbname} --verbose";
  log "INFO" "Running: ${cmd}";
  exec 1>/dev/tty
  exec 2>> ${pgdump_logs};

  ${cmd};

  exec 1>/dev/tty 2>&1;

  if [[ $ret -gt 0 && $ret -lt 127 ]]; then
    log "ERROR" "pg_dump failed on ${dbname}. See ${pgdump_logs} for details";
    error_exit;
  else
    log "INFO" "PG-DUMP finished successfully"
  fi

}

backup_postgres() {

  dumpRoles;
  dumpDB;

}

restore_postgres()
{
    # log "INFO" "Start restoring roles."
    # psql --host={target_rds_host} --dbname=${dbname} --port=5432 -U ${rds_pguser}  < ${backuproles}
    # rc=?
    # if [ "${rc}" == "0" ]; then
    #     log "INFO" "Restore roles finished successfully."
    # else
    #     log "ERROR" "Something wrong happned while restoring roles."
    #     error_exit
    # fi
    # if [ $(pg_restore --verbose --host=${target_rds_host} --dbname=${new_dbname} --port=5432 --no-owner --role=${rds_pguser} -U ${rds_pguser} ${backupDir} --jobs=${jobs} 2>> ${restore_logs}) $? -eq 0 ]; then
    #     log "INFO" "PG-RESTORE finished successfully."
    # else
    #     log "ERROR" "Something wrong happned while running pg_restore."
    #     error_exit
    # fi
    log "INFO" "Starting postgres restore";
    cmd="pg_restore -v --host=${target_rds_host} --dbname=${new_dbname} --port=5432 -U ${rds_pguser} ${backupDir} --jobs=${jobs}";
    log "INFO" "Running: ${cmd}";
    exec 2>> ${restore_logs};
    $( ${cmd} );
    ret=$?;
    exec 1>/dev/tty 2>&1;

   if [ $ret -ne '0' ]; then
        log "INFO" "PG-RESTORE finished successfully."
    else
        log "ERROR" "Something wrong happned while running pg_restore."
        error_exit
    fi
}


# main
createDirs;
createLogFiles;
backup_postgres


restore_postgres 

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