Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kbfvM-0001jV-7z for pgsql-admin@arkaria.postgresql.org; Sun, 08 Nov 2020 08:19:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kbfvL-0000VP-6V for pgsql-admin@arkaria.postgresql.org; Sun, 08 Nov 2020 08:19:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kbfvK-0000VH-NC for pgsql-admin@lists.postgresql.org; Sun, 08 Nov 2020 08:19:42 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kbfvH-00082U-ED for pgsql-admin@lists.postgresql.org; Sun, 08 Nov 2020 08:19:41 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id EFD923805E1 for ; Sun, 8 Nov 2020 09:19:36 +0100 (CET) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id nTRUqSp17dY1 for ; Sun, 8 Nov 2020 09:19:36 +0100 (CET) Received: from [10.47.42.27] (x59cc8a1b.dyn.telefonica.de [89.204.138.27]) by rs.plausibolo.de (Postfix) with ESMTPSA id 53A42380357 for ; Sun, 8 Nov 2020 09:19:31 +0100 (CET) Date: Sun, 08 Nov 2020 09:19:20 +0100 User-Agent: K-9 Mail for Android In-Reply-To: References: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----M7PQA3EZBGJIRL55QCCM0RJVHZHLC1" Content-Transfer-Encoding: 7bit Subject: Re: Backup restore for upgrade To: pgsql-admin@lists.postgresql.org From: Holger Jakobs Message-ID: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------M7PQA3EZBGJIRL55QCCM0RJVHZHLC1 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable 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 wi= thout a script=2E Then show us the outputs in case of errors=2E Regards, Holger Am 8=2E November 2020 07:22:15 MEZ schrieb Walters Che Ndoh : >Hi All, > >I am trying to upgrade one of my 1TB databases from pg9=2E5 to 10 and >having >some troubles=2E >First I tried using pglogical and it failed because most of the tables >didn't have a primary key=2E >Second, i tried pg_upgrade and it also failed with some really weird >errors=2E > >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=2E >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=2E >I am trying to speed up the process using some *jobs* based on my cpu=2E >The script doesn't work as intended=2E=2E=2Eespecially the restore part a= s it >just stops at the restore part=2E Maybe the dump format=2E=2E?? > >Please can someone help me review the script below maybe i am making >errors >at defining the variables or at the function level=2E >*OR* if you have a better script to recommend i will appreciate it=2E > >Thanks > >*script* >-------------------------------------------------------------------------= ---------------------------------------------- > >*#!/bin/bash* > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >*jobs=3D6;mydate=3D$(date >+"%Y%m%d%H%M%S");env=3Dprod;dbname=3Dtest;rds_pguser=3Dtest_user;host=3Dl= ocalhost;backupDir=3D/var/lib/data/backup/`date >+%Y-%m-%d`;sqlDir=3D${backupDir}/sql;logDir=3D${backupDir}/logs;backuprol= es=3D${backupDir}/globals-${env}-${mydate}=2Esql;# >Logs source >instanceroles_logs=3D${logDir}/roles_logs-${env}-${mydate}=2Elog;pgdump_l= ogs=3D${logDir}/pgdump_logs-${env}-${mydate}=2Elog;restore_logs=3D${logDir}= /restore_logs-${env}-${mydate}=2Elog;process_log=3D${logDir}/process_log-${= env}-${mydate}=2Elog;# >RDS Target hosttarget_rds_host=3D"IP of destination >server";rds_pguser=3Dtest_user;new_dbname=3Dtest;wait_in_seconds=3D1800;G= REEN=3D"\033[32m"; >BLUE=3D"\033[34m"; RED=3D"\033[31m"; RESET=3D"\033[0m"; YELLOW=3D"\033[33= m";# >Functionslog() { local log_info=3D$1; if [[ "${log_info}" =3D=3D "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") ]:=20 >$2" >>> ${process_log}; elif [[ "${log_info}" =3D=3D "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}" =3D=3D "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}" =3D=3D "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}" =3D=3D "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() {=20 >for >file in ${roles_logs} ${pgdump_logs} ${restore_logs} ${process_log}; do >touch ${file}; ret=3D$?; 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 =20 > if >[[ ! -d "${dir}" ]]; then mkdir -p ${dir}; ret=3D$?; if [ >${ret} >-ne 0 ]; then echo "ERROR: Failed creating directory [${dir}] >failed: ${ret}"; exit 1; fi; fi; done;}dumpRoles() {=20 >log >"INFO" "Start backing up PG-Roles=2E" cmd=3D'/bin/pg_dumpall -v -g'; lo= g >"INFO" "Running: ${cmd}"; # DUMP: ${backuproles} ERRORS: >${roles_logs}"; exec 1>${backuproles}; exec 2> ${roles_logs};=20 >${cmd}; >ret=3D$?; exec 1>/dev/tty 2>&1; if [[ $ret -gt 0 && $ret -lt 127 ]]; >then >log "ERROR" "Backup for PG roles failed=2E 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=3D"pg_dump --dbname ${dbname} --jobs=3D${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}=2E 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=2E" = =20 ># >psql --host=3D{target_rds_host} --dbname=3D${dbname} --port=3D5432 -U >${rds_pguser} < ${backuproles} # rc=3D? # if [ "${rc}" =3D=3D "0" = ]; >then > # log "INFO" "Restore roles finished successfully=2E" # else # > log "ERROR" "Something wrong happned while restoring roles=2E" # >error_exit # fi # if [ $(pg_restore --verbose >--host=3D${target_rds_host} --dbname=3D${new_dbname} --port=3D5432 --no-o= wner >--role=3D${rds_pguser} -U ${rds_pguser} ${backupDir} --jobs=3D${jobs} 2>> >${restore_logs}) $? -eq 0 ]; then # log "INFO" "PG-RESTORE >finished >successfully=2E" # else # log "ERROR" "Something wrong happned >while running pg_restore=2E" # error_exit # fi log "INFO" >"Starting postgres restore"; cmd=3D"pg_restore -v >--host=3D${target_rds_host} --dbname=3D${new_dbname} --port=3D5432 -U >${rds_pguser} ${backupDir} --jobs=3D${jobs}"; log "INFO" "Running: >${cmd}"; exec 2>> ${restore_logs}; $( ${cmd} ); ret=3D$?; =20 >exec >1>/dev/tty 2>&1; if [ $ret -ne '0' ]; then log "INFO" >"PG-RESTORE >finished successfully=2E" else log "ERROR" "Something wrong >happned >while running pg_restore=2E" error_exit fi}# >maincreateDirs;createLogFiles;backup_postgresrestore_postgres * >-------------------------------------------------------------------------= ------------------------------------------------ --=20 Holger Jakobs, Bergisch Gladbach +49 178 9759012 - sent from mobile, therefore short - ------M7PQA3EZBGJIRL55QCCM0RJVHZHLC1 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi,

Try to dump one db with pg_dump (using = either plain or custom format) and then restore with psql (from plain) or p= g_restore (from custom) manually without a script=2E

Then show us th= e outputs in case of errors=2E

Regards,

Holger

Am 8=2E November 2020 07:22:15 MEZ schrieb Walters Che = Ndoh <chendohw@gmail=2Ecom>:
Hi All,

I am trying to upgrade one of my 1TB datab= ases from pg9=2E5 to 10 and having some troubles=2E
First I tried using= pglogical and it failed because most of the tables didn't have a primary k= ey=2E
Second, i tried pg_upgrade and it also failed with some rea= lly weird errors=2E

At this point, I am really convinced = that the upgrade can only be possible by me taking a dump of the old versio= n and restore it in the new version on a new server=2E
So I am wo= rking on this script to help me take a dump from the old version server and= restore it into the pg10 server=2E 
I am trying to speed up the pr= ocess using some jobs based on my cpu=2E
The script doesn'= t work as intended=2E=2E=2Eespecially the restore part as it just stops at = the restore part=2E Maybe the dump format=2E=2E??

Please can someone= help me review the script below maybe i am making errors at defining the v= ariables or at the function level=2E
OR if you have a bett= er script to recommend i will appreciate it=2E

Thanks

script
----------= ---------------------------------------------------------------------------= ----------------------------------
#!/bin/bash
=

jobs=3D6;
mydate=3D$(date +"%Y%m%d%H%M%S");
env= =3Dprod;
dbname=3Dtest;
rds_pguser=3Dtest_user;
host=3Dlocalhost;<= br>
backupDir=3D/var/lib/data/backup/`date +%Y-%m-%d`;
sqlDir=3D${bac= kupDir}/sql;
logDir=3D${backupDir}/logs;
backuproles=3D${backupDir}/g= lobals-${env}-${mydate}=2Esql;

# Logs source instance
roles_logs= =3D${logDir}/roles_logs-${env}-${mydate}=2Elog;
pgdump_logs=3D${logDir}/= pgdump_logs-${env}-${mydate}=2Elog;
restore_logs=3D${logDir}/restore_log= s-${env}-${mydate}=2Elog;
process_log=3D${logDir}/process_log-${env}-${m= ydate}=2Elog;

# RDS Target host
target_rds_host=3D"IP of destinat= ion server";
rds_pguser=3Dtest_user;
new_dbname=3Dtest;
wait_in_se= conds=3D1800;

GREEN=3D"\033[32m"; BLUE=3D"\033[34m"; RED=3D"\033[31m= "; RESET=3D"\033[0m"; YELLOW=3D"\033[33m";

# Functions
log() {  local log_info=3D$1;
  if [[ "${log_info}" =3D=3D "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}"  =3D=3D "WARN" ]]; then
    echo = -e "${YELLOW}[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2 ${RES= ET}";
    echo -e "[${log_info} - $(date +"%Y-%m-%d %H:%M:%S")= ]:  $2" >> ${process_log};
  elif [[ "${log_info}" =3D= =3D  "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" >> ${pr= ocess_log};
  elif [[ "${log_info}" =3D=3D  "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}" =3D=3D  "ERROR" ]]; then
    echo -e "${RED= }[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $2 ${RESET}";
&nb= sp;   echo -e "[ ${log_info} - $(date +"%Y-%m-%d %H:%M:%S") ]:  $= 2" >> ${process_log};
  fi
}

error_exit() {
&nbs= p; log "EXIT" "Exiting due to errors!";
  #send_email "${body_mail_= log}" "${status}"
  exit 1;
}

createLogFiles() {

&= nbsp; for file in ${roles_logs} ${pgdump_logs} ${restore_logs} ${process_lo= g}; do
    touch ${file};
    ret=3D$?;
 =   if [ ${ret} -ne 0 ]; then
      echo "ERROR: Unab= le to create file [${file}]: ${ret}";
      exit 1;
&n= bsp;   fi;
  done;

}

createDirs() {

 = ; for dir in ${backupDir} ${logDir} ${sqlDir}; do
    if [[ ! = -d "${dir}" ]]; then
      mkdir -p ${dir}; ret=3D$?;
=       if [ ${ret} -ne 0 ]; then
      &nbs= p; echo "ERROR: Failed creating directory [${dir}] failed: ${ret}";
&nbs= p;       exit 1;
      fi;
   = ; fi;
  done;

}

dumpRoles() {

  log "INFO= " "Start backing up PG-Roles=2E"

  cmd=3D'/bin/pg_dumpall -v -g= ';
  log "INFO" "Running: ${cmd}"; #    DUMP: ${backuprol= es}  ERRORS: ${roles_logs}";
  exec 1>${backuproles};
&n= bsp; exec 2> ${roles_logs};
  ${cmd};
  ret=3D$?;
&nb= sp; exec 1>/dev/tty 2>&1;

  if [[  $ret -gt 0 &a= mp;& $ret -lt 127 ]]; then
    log "ERROR" "Backup for PG = roles failed=2E See ${roles_logs} for details";
    error_exit= ;
  else
    log "INFO" "Roles were successfully backe= d up to ${backuproles}"
  fi

}

dumpDB() {

&nbs= p; log "INFO" "Starting pg_dump on ${dbname}"
  cmd=3D"pg_dump --db= name ${dbname} --jobs=3D${jobs} -Fd -f ${sqlDir}/${dbname} --verbose";
&= nbsp; log "INFO" "Running: ${cmd}";
  exec 1>/dev/tty
  = exec 2>> ${pgdump_logs};

  ${cmd};

  exec 1&g= t;/dev/tty 2>&1;

  if [[ $ret -gt 0 && $ret -lt = 127 ]]; then
    log "ERROR" "pg_dump failed on ${dbname}=2E S= ee ${pgdump_logs} for details";
    error_exit;
  else=
    log "INFO" "PG-DUMP finished successfully"
  fi
}

backup_postgres() {

  dumpRoles;
  dumpD= B;

}

restore_postgres()
{
    # log "INFO" "S= tart restoring roles=2E"
    # psql --host=3D{target_rds_host}= --dbname=3D${dbname} --port=3D5432 -U ${rds_pguser}  < ${backuprol= es}
    # rc=3D?
    # if [ "${rc}" =3D=3D "0" ];= then
    #     log "INFO" "Restore roles finished s= uccessfully=2E"
    # else
    #     lo= g "ERROR" "Something wrong happned while restoring roles=2E"
  &nbs= p; #     error_exit
    # fi
    # if [= $(pg_restore --verbose --host=3D${target_rds_host} --dbname=3D${new_dbname= } --port=3D5432 --no-owner --role=3D${rds_pguser} -U ${rds_pguser} ${backup= Dir} --jobs=3D${jobs} 2>> ${restore_logs}) $? -eq 0 ]; then
 =   #     log "INFO" "PG-RESTORE finished successfully=2E"    # else
    #     log "ERROR" "Somethi= ng wrong happned while running pg_restore=2E"
    #   &nb= sp; error_exit
    # fi
    log "INFO" "Starting = postgres restore";
    cmd=3D"pg_restore -v --host=3D${target_= rds_host} --dbname=3D${new_dbname} --port=3D5432 -U ${rds_pguser} ${backupD= ir} --jobs=3D${jobs}";
    log "INFO" "Running: ${cmd}";
&n= bsp;   exec 2>> ${restore_logs};
    $( ${cmd} );    ret=3D$?;
    exec 1>/dev/tty 2>&1;<= br>
   if [ $ret -ne '0' ]; then
       = ; log "INFO" "PG-RESTORE finished successfully=2E"
    else        log "ERROR" "Something wrong happned while run= ning pg_restore=2E"
        error_exit
  &nb= sp; fi
}


# main
createDirs;
createLogFiles;
backup_p= ostgres


restore_postgres 
---------------= ---------------------------------------------------------------------------= ------------------------------- 




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