Thread: 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