Re: Backup restore for upgrade

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.