pg_dump: aborting because of server version mismatch

Initiating backup using pg_dump keeps on failing with error as below :

pg_dump: [archiver (db)] query was: LOCK TABLE

While performing backup using pg_dump with database user fails with error as below :

pg_restore with -C option fails to create database. FATAL: database "nexaprod" does not exist

When you use pg_restore you have various options available, for example : 

-c to drop database objects before recreating them,

-C to create a database before restoring into it,

initialise multiple postgres instance on same RHEL server

It is pretty easy to install multiple instances of PostgreSQL servers on same server and have its benefits.

Who uses Postgresql

A good read Who uses Postgresql 

Postgres | Query to identify the lag among the Primary and Replica

How to stop starting of clusters while starting up services

Infra activities such as upgrade, OS patching or Sevrer maintaince activity may require disabling the auto startup of any specific cluster or if you want  NOT auto start on a call such as sudo service postgresql start

pgtune | installation and usage

pgtune helps expands the database server to be as powerful as the hardware it's being deployed on , by taking an existing postgresql.conf file as an input, making changes to it based on the amount of RAM in your server and suggested workload, and output a new file.

pg_basebackup: could not connect to server: FATAL: number of requested standby connections exceeds

-bash-4.1$ pg_basebackup -h oracleasm1.localdomain -D /u01/PG_Adventure
pg_basebackup: could not connect to server: FATAL:  number of requested standby connections exceeds … 

pg_basebackup : could not connect to server: FATAL: no pg_hba.conf entry for replication connection

pg_basebackup: could not connect to server: FATAL:  no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL off

Postgres Drop table : ERROR: must be owner of relation table_name

In Postgres, The right to drop an object is not treated with any grantable privilege. It is only achived by the ownership. How .. ?

Creating a database on Postgresql server

1.    Login to PostgreSQL server

[oracle@oracleasm1 ~]$ su - postgres
Password:
-bash-4.1$
 
-bash-4.1$ psql
psql (8.4.18, server 9.6.15)
WARNING: psql version 8.4, server version 9.6.
         Some psql features might not work.
Type "help" for help.

Installating PostgreSQL Server on CentOS, RHEL 6 / 7 Systems

This article will help you for installing PostgreSQL on CentOS, RHEL 6 / 7 Systems

RMAN-08591: WARNING: invalid archived log deletion policy

For one of database we were getting backup failure alerts where the backup was configured on standby database.

We saw below Error messsages in backup-log :

RMAN-08591: WARNING: invalid archived log deletion policy

-

  

Cause: If archive log location is set to FRA, then there might be a chance of the deleting the archives automatically when the space pressure in FRA.

In that case at least one of the destination standby must be set as a "MANDATORY" destination.

Solution: To eliminate the RMAN warning message, at least one archive destination must be set as a mandatory destination.

Steps : 

1. Log in to the broker command line utility

[oracle@host1 log]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration – primdb1
  Protection Mode: MaxPerformance
  Members:

  primdb1 - Primary database
    bcpdb1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 7 seconds ago)

2. For the standby site check the "Binding" property

DGMGRL> show database verbose bcpdb1

Database - bcpdb1

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      11 minutes (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 625.00 KByte/s
  Active Apply Rate:  523.00 KByte/s
  Maximum Apply Rate: 18.62 MByte/s
  Real Time Query:    ON
  Instance(s):
    bcpdb1

Properties:

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


Binding        = 'optional'

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

3. Set the "binding" Property as - MANDATORY
        
//This parameter controls whether the destination is mandatory or not

DGMGRL> edit database bcpdb1 set property Binding='mandatory';

Property "binding" updated

DGMGRL> exit

Once the broker configuration has set a standby site as a mandatory destination, the RMAN configuration can be altered to set the archivelog deletion policy to applied on standby.

RMAN> configure archivelog deletion policy to applied on standby;

Once the broker configuration has set a standby site as a mandatory destination, then RMAN will not report this error again.

Reference: Data Guard Physical Standby - RMAN configure archivelog deletion policy reports RMAN-08591 (Doc ID 1984064.1)

-

Found this post interesting? Subscribe us  😊😉

Read more 

Configure Streaming Replication in PostgreSQL

Installation and configure Postgres 13 on RHEL

All about Physical Replication and Log shipping in Postgres 

Possible ways to recover space from deleted rows with insufficient disk space

Streaming-Replication Sync and Async, benefits of streaming replication over Log-shipping

“Be like a tree. Stay grounded. Connect with your roots. Turn over a new leaf. Bend before you break. Enjoy your unique beauty. Keep growing.” -- Joanne Rapits.

Query to find the session creating more redo

How to find the session generating more number of Redo or we can say Archivelogs as well.
Using the view v$sess_io, we can find out the sessions which are performing the block_changes in database. From this we can determine the sessions.

Below is the query we can use to find the Session IDs

set pages 1000 lines 1000
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc;