Questions about how to streaming replication (pg 9.2)

From: Ekaterina Amez <ekaterina(dot)amez(at)zunibal(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Questions about how to streaming replication (pg 9.2)
Date: 2020-01-20 13:59:13
Message-ID: a71c7da9-800b-663e-1949-2b801e031865@zunibal.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

After your comments about how to upgrade postgres from 9.2 to 9.6 in an
overloaded server I've been learning and testing streaming replication.
But the info I've found about this topic is not enough for me (or I'm
not able to completely understand it).

(I'm bad with terminology as I've always had a problem trying to
remember the technical name of things so I apologyze if I make any
mistake with the name of things.)

One of the parameters that have to be changed to get streaming
replication work is /wal_archive./ I've been testing with /hot_standby/
option, as I've found it to be the simplest way to get streaming
replication work. With this option there's not archive of WAL files, so
we only have the files that exist in pg_xlog directory. And the number
of these files is defined by /wal_keep_segments/.

I'm explaining the replication process, so you can tell if is there
something that I'm missing or not understanding. There are two ways of
creating a streaming replica: one is setting /wal_level/ to
/hot_standby/ and  the other one setting it to /archive/. Let's use
/hot_standby/ option and let's suppose we make pg_basebackup that takes
1hr: after this base backup, slave has to synchronize with the master to
get the changes that have been made in this hour. And as there is no
repository for WAL files, we must ensure that pg_xlog contains enough
files to this synchronization is achieved succesfully
(/wal_keep_segments/). Is this right?

I've been looking at the files in this directory, in master's PGDATA,
and ls -lt gives me 62 files created in 1hr so being optimistic this
config of wal files would be enough for my slave to synchronize with the
master, am I right? (when taking this to production scenario I would add
some more files to be sure slave doesn't miss anything)

Would it be better to use wal archiving? The final goal is to create the
replica having master and slave in the same server so I guess archive
command would be something like "cp <source> <destination>" without any
file transfer involved. I suppose archiving folder can be in a different
path than PGDATA.

Thanks for your feedback,

Ekaterina

PS: Just in case anyone wants to know, this is part of the process of
upgrade a server with 9.2 version that has no free space in PGDATA and
that can't be stopped for much time. After asking here, the strategy to
upgrade will be: replicate this DB to a path with space to grow, switch
clusters (slave becoming master and creating a new slave to have
just-in-case), and pg_upgrade slave/new master with --link option.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message stan 2020-01-20 16:05:57 Re: A question about rules
Previous Message Arnaud L. 2020-01-20 07:33:22 Re: minimal wal_level on subscriber