Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

HOW TO TAKE THE BACKUP FROM SLAVE SERVER - POSTGRESQL 10.3?

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

Comments

Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

Ora2PG - Oracle/MySQL to Postgres DB migration Version 20.0

PostgreSQL Introduction