Thread: Backup restore for upgrade

Backup restore for upgrade

From
Walters Che Ndoh
Date:
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 
------------------------------------------------------------------------------------------------------------------------- 



Re: Backup restore for upgrade

From
Holger Jakobs
Date:
Hi,

Try to dump one db with pg_dump (using either plain or custom format) and then restore with psql (from plain) or pg_restore (from custom) manually without a script.

Then show us the outputs in case of errors.

Regards,

Holger

Am 8. November 2020 07:22:15 MEZ schrieb Walters Che Ndoh <chendohw@gmail.com>:
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 
------------------------------------------------------------------------------------------------------------------------- 




--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -

Re: Backup restore for upgrade

From
Jeff Janes
Date:


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

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


Fixing these errors is probably going to be easier than what you are currently trying to do.  Can you share them with us?

Jeff

Re: Backup restore for upgrade

From
Avinash Kumar
Date:
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)

Re: Backup restore for upgrade

From
DiasCosta
Date:
Hi,

I suppose your OS is some Unix.
If so, do you consider the possibility of migrating the complete database using pg_dump to export from the source
and redirect by pipe to psql to import into the target database as follows?


#pg_dump --host=localhost --username=postgres --port=12970 --password --inserts --table=sch_conta.facturas --dbname=db_source | psql.exe --host=localhost --port=12970  --username=postgres --dbname=db_aml

Dias Costa

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

On 08-11-2020 06:22, Walters Che Ndoh 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.

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





-- 
J. M. Dias Costa
Telef. 214026948

Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.

Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não respeitem o 
malfadado acordo ortográfico.

Re: Backup restore for upgrade

From
"Anjul Tyagi"
Date:
I suggest you can change the replica identity to Full and replicate the data. you may see more walfiles as compare to now a days. 

 
 
 

Regards,

Anjul TYAGI

 

ü Go Green


------ Original Message ------
From: "Avinash Kumar" <avinash.vallarapu@gmail.com>
To: "Walters Che Ndoh" <chendohw@gmail.com>
Sent: 11/9/2020 12:37:21 AM
Subject: Re: Backup restore for upgrade

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)