Re: Backup restore for upgrade

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(at)gmail(dot)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
>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 *
>-------------------------------------------------------------------------------------------------------------------------

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