Questions about how to streaming replication (pg 9.2)
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.
On Mon, 2020-01-20 at 14:59 +0100, Ekaterina Amez wrote:
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.
Do not upgrade to 9.6, upgrade to at least v11.
Set "wal_keep_segments" high enough on the primary for safety.
Run "pg_basebackup" on the standby.
Create a "recovery.conf" on the standby that has "primary_conninfo" set
and "standby_mode = on".
Start the standby and wait until it has caught up, then switch over.
Yours,
Laurenz Albe
El 21/1/20 a las 10:14, Laurenz Albe escribió:
On Mon, 2020-01-20 at 14:59 +0100, Ekaterina Amez wrote:
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.Do not upgrade to 9.6, upgrade to at least v11.
v9.6 is target version because this is the last server that remains with
unsupported PG version, and the others have been upgraded to 9.6. After
having all of them in 9.6, I have to discuss what to do next as at least
one of the servers is installed over CentOS 32 bits (facepalm...)
Set "wal_keep_segments" high enough on the primary for safety.
I'm not sure how to figure this value . Of course I could use a big
value and forget, but as the server is running out of space I'd like to
adjust it so there is (almost) no waste of space. This is why I began to
think about wal archiving, though it seems a bit more complicated.
Run "pg_basebackup" on the standby.
Create a "recovery.conf" on the standby that has "primary_conninfo" set
and "standby_mode = on".Start the standby and wait until it has caught up, then switch over.
Yeah, I've been testing this part and it's the only part where I feel I
know what I'm doing.
Yours,
Laurenz Albe
Regards,
Ekaterina
On Tue, 2020-01-21 at 11:18 +0100, Ekaterina Amez wrote:
[about wal_keep_segments]
I'm not sure how to figure this value . Of course I could use a big
value and forget, but as the server is running out of space I'd like to
adjust it so there is (almost) no waste of space. This is why I began to
think about wal archiving, though it seems a bit more complicated.
See how many WAL files are generated in a day.
If you set wal_keep_segments to that value, the standby will be
able to survive a down time of a day.
If you don't need to cater for that much down time, use a smaller value.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com