WARNING: canceling wait for synchronous replication due to user request DETAIL: The transaction has already committed locally, but might not have been replicated to the standby

-------Error--------------

postgres=# INSERT INTO said VALUES ('3954','Saiduzzaman');


^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 1
postgres=# exit


Reason

---In Primary--
postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 on
(1 row)

postgres=#
postgres=# select * from pg_stat_replication ;
  pid  | usesysid | usename | application_name |   client_addr   | client_hostname | client_port |         backend_start
        | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_priority |
sync_state
-------+----------+---------+------------------+-----------------+-----------------+-------------+-----------------------
--------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+-
-----------
 27148 |    16393 | repuser | walreceiver      | 192.168.159.134 |                 |       40704 | 2017-12-05 01:03:43.09
7386-05 |              | streaming | 0/23015F48    | 0/23015F48     | 0/23015F48     | 0/23015F48      |             0 |
async
(1 row)

postgres=#

postgres=# select application_name, sync_state from pg_stat_replication;
 application_name | sync_state
------------------+------------
 walreceiver      | async
(1 row)

postgres=#

postgres=# show synchronous_standby_names;
 synchronous_standby_names
---------------------------
 pgslave001
(1 row)


---In Standby--
postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 local
(1 row)

postgres=#


------Solution---------

Changing synchronous_standby_names to 'walreceiver' resolved the error.
[root@pg_primary data]# vi postgresql.conf

#synchronous_standby_names = 'pgslave001'

synchronous_standby_names = 'walreceiver'

[root@pg_primary data]# systemctl stop postgresql-9.5.service
[root@pg_primary data]# systemctl start postgresql-9.5.service
[root@pg_primary data]# su - postgres
Last login: Tue Dec  5 01:04:46 EST 2017 on pts/0
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# INSERT INTO said VALUES ('3690','Nabiul');
INSERT 0 1
postgres=# INSERT INTO said VALUES ('3690','Nabiul');
INSERT 0 1

"/var/lib/pgsql/data" is missing or empty. in postgresql Database

Problem: When trying to check the service status of  postgresql  as root on RHEL 7 server, i got the following error message

[root@localhost RHEL-7.0 Server.x86_64]# systemctl status postgresql.service 
postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
   Active: failed (Result: exit-code) since Mon 2017-12-04 01:37:16 EST; 7s ago
  Process: 14151 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

Dec 04 01:37:16 localhost.localdomain postgresql-check-db-dir[14151]: "/var/lib/pgsql/data" is missing or empty.
Dec 04 01:37:16 localhost.localdomain postgresql-check-db-dir[14151]: Use "postgresql-setup initdb" to initialize the database cluster.
Dec 04 01:37:16 localhost.localdomain postgresql-check-db-dir[14151]: See /usr/share/doc/postgresql-9.2.7/README.rpm-dist for mor...ion.
Dec 04 01:37:16 localhost.localdomain systemd[1]: postgresql.service: control process exited, code=exited status=1
Dec 04 01:37:16 localhost.localdomain systemd[1]: Failed to start PostgreSQL database server.
Dec 04 01:37:16 localhost.localdomain systemd[1]: Unit postgresql.service entered failed state.
Hint: Some lines were ellipsized, use -l to show in full.
[root@localhost RHEL-7.0 Server.x86_64]#

Reason:

initdb creates a new PostgreSQL database cluster. A database cluster is a collection of databases that are managed by a single server instance.

Creating a database cluster consists of creating the directories in which the database data will live, generating the shared catalog tables (tables that belong to the whole cluster rather than to any particular database), and creating the template1 and postgres databases. When you later create a new database, everything in the template1 database is copied. (Therefore, anything installed in template1 is automatically copied into each database created later.) The postgres database is a default database meant for use by users, utilities and third party applications.

Solution:


[root@localhost RHEL-7.0 Server.x86_64]# service postgresql initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... OK

[root@localhost RHEL-7.0 Server.x86_64]#

[root@localhost RHEL-7.0 Server.x86_64]# systemctl stop postgresql.service
[root@localhost RHEL-7.0 Server.x86_64]# systemctl start postgresql.service
[root@localhost RHEL-7.0 Server.x86_64]# systemctl status postgresql.service
postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
   Active: active (running) since Mon 2017-12-04 01:40:51 EST; 4s ago
  Process: 46413 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 46408 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 46418 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─46418 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           ├─46419 postgres: logger process 
           ├─46421 postgres: checkpointer process 
           ├─46422 postgres: writer process 
           ├─46423 postgres: wal writer process 
           ├─46424 postgres: autovacuum launcher process 
           └─46425 postgres: stats collector process 

Dec 04 01:40:50 localhost.localdomain systemd[1]: Starting PostgreSQL database server...
Dec 04 01:40:51 localhost.localdomain systemd[1]: Started PostgreSQL database server.
[root@localhost RHEL-7.0 Server.x86_64]#

Time Based Archivelog Backup in Oracle 11gR2 RMAN

Scenario:

Sometimes we need to take archive log backup for specific time, so we can use the following steps for this purpose

Solution:

bash-4.2$ rman target sys/sys123

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 23 16:22:49 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=214666078)

RMAN> run {
2>      allocate channel ch1 type disk;
3>       backup archivelog from time "TO_DATE('11/20/2017 10:00:00', 'MM/DD/YYYY hh24:mi:ss')"
4>       until time "TO_DATE('11/23/2017 12:00:00', 'MM/DD/YYYY hh24:mi:ss')" format '/bak_archive/ARC0_ORCL_%U_%T';
5>       BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE format '/bak_archive/CONTROL0_ORCL_%U_%T';
6> }

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=5673 instance=ORCL device type=DISK

Starting backup at 23-NOV-17
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=56 RECID=54439 STAMP=960793631
input archived log thread=2 sequence=57 RECID=54438 STAMP=960793630
channel ch1: starting piece 1 at 23-NOV-17
channel ch1: finished piece 1 at 23-NOV-17
piece handle=/bak_archive/ARC0_ORCL_crska3j7_1_1_20171123 tag=TAG20171123T162303 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-NOV-17

Starting backup at 23-NOV-17
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 23-NOV-17
channel ch1: finished piece 1 at 23-NOV-17
piece handle=/bak_archive/CONTROL0_ORCL_csska3j8_1_1_20171123 tag=TAG20171123T162304 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-NOV-17
released channel: ch1

RMAN> exit


Recovery Manager complete.
bash-4.2$

If we want to take archive log backup with sequence and kept in tape then use the following script

RUN
{
  ALLOCATE CHANNEL T1 TYPE 'sbt_tape' PARMS
    'ENV=(TDPO_OPTFILE=)' ;

  SET ARCHIVELOG DESTINATION TO '/bak_archive/archivelog';
  RESTORE ARCHIVELOG FROM SEQUENCE 3389 ;

  RELEASE CHANNEL T1;
}

If we want to report RMAN backup information for specific time the use the following script

LIST BACKUP OF ARCHIVELOG
  FROM TIME "TO_DATE('11/20/2017 10:00:00', 'MM/DD/YYYY hh24:mi:ss')"
  UNTIL TIME "TO_DATE('11/23/2017 12:00:00', 'MM/DD/YYYY hh24:mi:ss')";