Postgres Database Backup & Restore Script

Backup

======

[postgres@prod log]$ cat /u01/database_backup/db_backup.sh

PATH=/u01/opt/PostgreSQL/11/bin/pg_dump:.:$PATH; export PATH

PATH=/u01/opt/PostgreSQL/11/bin:$PATH:$HOME/.local/bin:$HOME/bin

export PATH

~

export PGDATA=/u01/opt/PostgreSQL/11/data

export PGUSER=postgres

export PGDATABASE=postgres

MYDB=postgresql://postgres:postgres@127.0.0.1:5432/appdb; export MYDB

pg_dump -v -Fc --dbname=$MYDB --file /u01/database_backup/appdb_$(date +%Y-%m-%d).dump 2> /u01/database_backup/log/appdb_export_log_$(date +%Y-%m-%d).log

Restore

========

[postgres@uat log]$ cat /u01/database_backup/database_restore.sh

PATH=/u01/opt/PostgreSQL/11/bin/pg_dump:.:$PATH; export PATH

PATH=/u01/opt/PostgreSQL/11/bin:$PATH:$HOME/.local/bin:$HOME/bin

export PATH

~

export PGDATA=/u01/opt/PostgreSQL/11/data

export PGUSER=postgres

export PGDATABASE=postgres

PGPASSWORD=postgres; export PGPASSWORD

psql -h db.uat -U postgres -d postgres  -c "SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'appdb';"

psql -h db.uat -U postgres -d postgres  -c "drop  DATABASE appdb;"

psql -h db.uat -U postgres -d postgres  -c "CREATE DATABASE appdb WITH OWNER = appusr ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = appdb_data CONNECTION LIMIT = -1;"

pg_restore -c -C -v -j 4 -Fc -d postgres /u01/database_backup/appdb_$(date +%Y-%m-%d).dump > /u01/database_backup/log/appdbRestore_$(date +%Y-%m-%d).txt 2>&1

pg_restore: [archiver] did not find magic string in file header

Error:

pg_restore: [archiver] did not find magic string in file header

 

Solution:

If the database backup "pg_dump" has been taken with -Fc or -Fd parameter, we must “pg_restore” command to restore the data.

Update patch information in redhat portal

 If the redhat operating system servers are registered in redhat portal with subscription, It Periodically scans  the server and updates the information in the portal. 

Once the patches are applied in the server, to sync the report forcelly, Execute the below command.

service rhsmcertd stop

rhsmcertd --now

service rhsmcertd start

Alternative tool for xming

 xming started charging to use xming tool. Hence, We use the below tool with free of cost. There are so many tools available in market. But, this tool is so easy to configure.

Link :-

How do I set up X11 forwarding? (upenn.edu)

Download [Portable Format ]: 

MobaXterm Xserver with SSH, telnet, RDP, VNC and X11 - Home Edition (mobatek.net)

Unzip the folder and run "MobaXterm_Personal_22.1" tool.

Login to the linux server and execute the below command.

export DISPLAY="<Desktop IP>:0.0"

Example:

export DISPLAY="192.24.40.2:0.0"

run "xclock" command to confirm whether you can see the GUI.

connection timeout with smtp server

Issue: 

 I have faced this issue after I did inplace upgrade from OEL 6 to OEL 7. I am not able to send email and the maillog [/var/log/] updated with below error. 

 node-02 sendmail[27436]: 27E5KQWW098928: to=, ctladdr= (1001/1001), delay=00:20:52, xdelay=00:00:00, mailer=esmtp, pri=480584, relay=test.com., dsn=4.0.0, stat=Deferred: Connection timed out with test.com. 

node-02 sendmail[27544]: 27E5eOXd027538: to=, ctladdr= (1001/1001), delay=00:01:00, xdelay=00:01:00, mailer=esmtp, pri=120584, relay=test.com. [192.23.6.77], dsn=4.0.0, stat=Deferred: Connection timed out with test.com.

Reason For this issue: 

In our infra, our mail server dns name is “mail.test.com” and there is separate server for hosting one website called “test.com”. Due to that, If I use “mail.test.com”, The linux server resolves “test.com” instead of “mail.test.com” and ended with timeout error.

Solution: 

Update the IP instead of dns in the configuration. 

• systemctl stop sendmail 

• vi /etc/mail/sendmail.mc 

• Add the below entry [replace the hostname with IP] 

            o define(`SMART_HOST', `192.168.10.111')dnl 

• m4 /etc/mail/sendmail.mc > /etc/mail/sendmail.cf 

• systemctl restart sendmail 

 If the same issue happens, do the below steps and restart sendmail services.

 • grep DS /etc/mail/sendmail.cf 

Sample output: DS192.168.10.111 If there is no IP, add mail server IP.

Undo Retention and undo tablespace size calculation [To Resolve ora-0555 issues]

SQL Query find the current actual undo size and undo retention ================================================================ SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat 21 ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' / Sample output ============== ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec] ------------------------ ---------------------------------------------------------------------------------------------------- ---------------------------- 180812.922 3600 69354 SQL Query to find the required undo size and undo retention ================================================================ SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]" 6 FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' / Sample output ============== ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte] ------------------------ ---------------------------------------------------------------------------------------------------- ------------------------ 180812.922 3600 9385.5

Sql Server 2019 Cumulative patching [CV] [KB5011644] [15.0.4223.1] is failed with following error.

Error:

     The User Data directory in the registry is not valid. Verify DefaultData key under the instance hive points to a valid directory

 

Reason:-

Check the parameters of “DefaultLog” in the below path in the server registry.

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.EFTS\MSSQLServer

 

Update the correct path in the registry and retry the patching.

Always on database creation failed

 

Always on Database creation failed while adding the database in availability databases using "Full database and log backup" data synchronization preference.

Error:

The following required directories do not exist on replica test11\DRtest : D:\ Data\testdb,L:\ Data\testdb_logs. (Microsoft.SqlServer.Management.HadrModel)

 

------------------------------

Program Location:

 

   at Microsoft.SqlServer.Management.HadrModel.Validator.Validate(IExecutionPolicy policy, ScenarioValidatorHandler validationDelegate)

   at Microsoft.SqlServer.Management.Hadr.TestDatabaseFileLocationCompatibility.DoWork()

   at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

 

 

Reason:

Incorrect database default datafile & logfile location mentioned in the configuration. Change it in database default locations in “server properties”

 

ORA-27370 ORA-27300 ORA-27301 ORA-27302 ORA-27303

 Issue:- 

 Execute windows shell script[bat file] from oracle scheduler.

Error Code:-

ORA-27370: job slave failed to launch a job of type EXECUTABLE

ORA-27300: OS system dependent operation:accessing job scheduler service failed with status: 2

ORA-27301: OS failure message: The system cannot find the file specified.

ORA-27302: failure occurred at: sjsec 6a

ORA-27303: additional information: The system cannot find the file specified.

Reason for the issue:-

The scheduler job [OracleJobScheduler<dbname>] down and it should be up and running to run jobs in the database