Re: Backup restore for upgrade

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(dot)vallarapu(at)gmail(dot)com>
To: "Walters Che Ndoh" <chendohw(at)gmail(dot)com>
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org
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(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 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)