Re: Backup restore for upgrade - Mailing list pgsql-admin

Hi,

On Sun, Nov 8, 2020 at 2:22 AM Walters Che Ndoh <chendohw@gmail.com> wrote:

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.

See if temporarily you could add a sequence column as a Primary key column to the list of tables without a primary key. If not see below. 

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..??

It will be great to see and understand the issue if you could share some error messages during restore.
However, see if you are using the binaries of the latest (target) version for both pg_dump and pg_restore. There can sometimes be challenges when you are using Older version binaries to perform the dump and restore. 

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 

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

--

Regards,
Avinash Vallarapu (Avi)