1 like 0 dislike
1.2k views
asked by (2.4k points)  
Hi anyone having idea about how to take backup on slave server ?,slave is read only mode so we can't access the postgresql utility so how to take backup ?

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (2.4k points)  
edited by

 

In this tutorial I will explained how to take logical backup from PostgreSQL Slave server here i used PostgreSQL 10.3 and redhat 7.3 plateform.

       POSTGRESQL 9.6                        POSTGRESQL 10       
select pg_xlog_replay_pause();       select pg_wal_replay_pause(); #Pauses recovery immediately
select pg_is_xlog_replay_paused():   select pg_is_wal_replay_paused(); #True if recovery is paused.
select pg_xlog_replay_resume();      select pg_wal_replay_resume();  # Restarts recovery if it was paused

 

Step 1: issue the pg_wal_replay_pause() or pg_xlog_replay_pause()  function on slave 
select pg_wal_replay_pause();

Step 2:insert some data into master or restore the data then check the slave whether replicated or not

On master:
create table t3(id int,name varchar);
CREATE TABLE

On slave:
postgres=# \dt t3
Did not find any relation named "t3".

Step 3:pg_start_backup("label") is not possible on slave_2 but you can use pg_dump,pg_dumpall

On slave:
select pg_start_backup('label');
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.

TAKING BACKUP FROM SLAVE:
cd /usr/bin/pg_dump -d postgres -f /BACKUP/nijam.dump

AFTER BACKUP RESUME THE STREAMING REPLICATION THEN CHECK THE TABLE T3 WHETHER REPLICATED OR NOT

postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t    | table | postgres
 public | t1   | table | postgres
 public | t3   | table | postgres

 

More Info:https://www.tutorialdba.com/2017/11/oracle-and-postgresql-dba-related.html

Related questions

0 like 0 dislike
0 answers
0 like 0 dislike
2 answers
2 like 0 dislike
1 answer
0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
Welcome to Discussion Forum where you can ask questions and receive answers from other members of the community.
...