Re: Backup restore for upgrade

Hi,

On Sun, Nov 8, 2020 at 2:22 AM Walters Che Ndoh <chendohw(at)gmail(dot)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
> instanceroles_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 hosttarget_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";#
> Functionslog() { 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}#
> maincreateDirs;createLogFiles;backup_postgresrestore_postgres *
>
> -------------------------------------------------------------------------------------------------------------------------
>
>
>
>

--
Regards,
Avinash Vallarapu (Avi)