Re: Streaming Replication Without Downtime

On 21/02/2017 01:49, Venkata B Nagothi wrote:
>
> On Tue, Feb 21, 2017 at 6:53 AM, Gabriel Ortiz Lour <ortiz(dot)admin(at)gmail(dot)com <mailto:ortiz(dot)admin(at)gmail(dot)com>> wrote:
>
> Hi!
>
> Thanks for pointing out pg_basebackup
>
> The issue I'm facing now is about missing WAL files.
>
> What i'm doing:
> # su postgres -c 'pg_basebackup -D /var/lib/postgresql/9.1/main/ -x -h master -U sa_rep' ; service postgresql start
>
Ok but between pg_basebackup and service postgresql start you must configure the db as a stand by. You need to copy a pre-configured recovery file into the data dir (or whatever debian demands) and
then start. In 9.3 there is --write-recovery-conf which does this for you.
>
> The idea is to call "postgresql start" as soon as pg_basebackup ends.
> But I'm getting the following error:
>
> FATAL:could notreceive data fromWAL stream:FATAL:requested WAL segment XXXXXXX has already been removed
>
> Shoud I just increase 'wal_keep_segments' ?
>
>
> Yes, that is the way to go. But, you need to know what number you need to increase the wal_keep_segments to ?
> Which purely depends on the number of WALs being generated. Which version of PostgreSQL are you using by the way ?
Apparently 9.1. In 9.2 you can always use -X stream , thus making increasing of the wal_keep_segments redundant.
>
> If I do so, do I need do restart the master or just a reload will do it?
>
>
> No need to restart, "reload" will do.
>
> Venkata B N
> Database consultant
>
> ---------- Forwarded message ----------
> From: *Achilleas Mantzios* <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)com>>
> Date: 2017-02-17 11:20 GMT-02:00
> Subject: Re: [GENERAL] Streaming Replication Without Downtime
> To: pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org>
>
>
> Gabriel you are thinking this in the correct way, but its really :
>
> pg_basebackup -D <your data dir> --write-recovery-conf --progress --xlog-method=stream -h <your primary db server>
> then you just edit recovery.conf (if needed), tweak postgersql.conf (if needed) and start the standby .
>
> On 17/02/2017 15:09, Gunnar "Nick" Bluth wrote:
>> (sorry for the toppost, mobile device)
>> What you're looking for is pg_basebackup with - - xlog=stream, I guess.
>>
>> Regards,
>>
>> Nick
>>
>>
>> Am 17. Februar 2017 14:06:36 MEZ schrieb Gabriel Ortiz Lour <ortiz(dot)admin(at)gmail(dot)com> <mailto:ortiz(dot)admin(at)gmail(dot)com>:
>>
>> Hi all,
>> I've been searching for a way to initialize a new Hot Standby node with Streaming Replication withou the need for stop or even restarting the master.
>> Of course the master is already with the needed SR configs.
>>
>> I know I have to use pg_start_backup/pg_stop_backup, but i'd like some tips, or a link to some tutorial, with the order of the steps.
>>
>> I assume will be something like:
>>
>> - configure Slave for SR
>> - pg_start_backup()
>> - rsync PGDATA to slave
>> - start PG on the slave
>> - pg_stop_backup()
>>
>> Anything i'm thinking wrong?
>>
>> Thanks in advance,
>> Gabriel
>>
>>
>> --
>> Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt