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

PostgreSQL Index

VMWARE WORKSTATION 3,4,5,6,7,8,9,10,11,12,14,15...etc LICENSE KEYS COLLECTION

How to CreateYour Own AWS Account Alias?

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

PostgreSQL ALTER TABLE ... SET LOGGED / UNLOGGED