Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

[Scott Marlowe]

On Tue, Nov 1, 2016 at 1:43 PM, Patrick B  wrote:
>
>
> 2016-11-02 2:55 GMT+13:00 Scott Marlowe :
>>
>> On Mon, Oct 31, 2016 at 8:01 PM, Patrick B 
>> wrote:
>> > If I change recovery.conf:
>> >
>> > recovery_target_time = '2016-10-30 02:24:40'
>> >
>> >
>> > I get error:
>> >
>> > FATAL:  requested recovery stop point is before consistent recovery
>> > point
>>
>> You can try using pg_basebackup to get the replica setup. In 9.2 you
>> gotta make your own recovery.conf, but you already know how to do
>> that. Way easier than trying to rsync by hand etc.
>
>
>
> I did a pg_basebackup!

Huh, after a pg_basebackup all you should need is a recovery.conf in
place and a trigger file.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

[Patrick B]

2016-11-02 8:43 GMT+13:00 Patrick B :

>
>
> 2016-11-02 2:55 GMT+13:00 Scott Marlowe :
>
>> On Mon, Oct 31, 2016 at 8:01 PM, Patrick B 
>> wrote:
>> > If I change recovery.conf:
>> >
>> > recovery_target_time = '2016-10-30 02:24:40'
>> >
>> >
>> > I get error:
>> >
>> > FATAL:  requested recovery stop point is before consistent recovery
>> point
>>
>> You can try using pg_basebackup to get the replica setup. In 9.2 you
>> gotta make your own recovery.conf, but you already know how to do
>> that. Way easier than trying to rsync by hand etc.
>>
>
>
> I did a pg_basebackup!
>


Hi guys,

I was able to fix it doing:

recovery.conf:

> restore_command = 'cp /var/lib/pgsql/archive/%f %p'
> recovery_target_timeline = 'latest'
> standby_mode = off
> trigger_file = '/tmp/pg_failover_trigger'


Then, I got the error:

> FATAL:  archive file "000219D500EC" has wrong size: 0 instead
> of 16777216


To fix it:

rm 000219D500EC



Restart postgres and then:

>  LOG:  archive recovery complete
> LOG:  database system is ready to accept connections

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

[Patrick B]

2016-11-02 2:55 GMT+13:00 Scott Marlowe :

> On Mon, Oct 31, 2016 at 8:01 PM, Patrick B 
> wrote:
> > If I change recovery.conf:
> >
> > recovery_target_time = '2016-10-30 02:24:40'
> >
> >
> > I get error:
> >
> > FATAL:  requested recovery stop point is before consistent recovery point
>
> You can try using pg_basebackup to get the replica setup. In 9.2 you
> gotta make your own recovery.conf, but you already know how to do
> that. Way easier than trying to rsync by hand etc.
>


I did a pg_basebackup!

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

[Scott Marlowe]

On Mon, Oct 31, 2016 at 8:01 PM, Patrick B  wrote:
> If I change recovery.conf:
>
> recovery_target_time = '2016-10-30 02:24:40'
>
>
> I get error:
>
> FATAL:  requested recovery stop point is before consistent recovery point

You can try using pg_basebackup to get the replica setup. In 9.2 you
gotta make your own recovery.conf, but you already know how to do
that. Way easier than trying to rsync by hand etc.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

[Francisco Olarte]

Hi:

On Tue, Nov 1, 2016 at 3:01 AM, Patrick B  wrote:
> If I change recovery.conf:
> recovery_target_time = '2016-10-30 02:24:40'
> I get error:
> FATAL:  requested recovery stop point is before consistent recovery point

It looks clear. When you take a base backup from a running system some
recovery is needed to make it consistent. With the target time you can
limit how much is done. But there is a minimum. Think of it, if you
stated '1970-01-01' it would be clearly imposible, your date is
bigger, but still imposible, try raising it a bit.

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

[Patrick B]

If I change recovery.conf:

recovery_target_time = '2016-10-30 02:24:40'


I get error:

FATAL:  requested recovery stop point is before consistent recovery point

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

[Patrick B]

I actually want to restore in a point of time.

Don't want to recovery_target_timeline = 'latest'

How can I stipulate a date?

Thanks

2016-11-01 11:59 GMT+13:00 Patrick B :

>
>
> 2016-11-01 10:33 GMT+13:00 David G. Johnston :
>
>> On Mon, Oct 31, 2016 at 1:46 PM, Patrick B 
>> wrote:
>>
>>> Hi guys,
>>>
>>> I got a test server, let's call it test01.
>>>
>>> The test01 has a basebackup from the master.
>>> I want to turn test01 into a master. It doesn't need to catch up with
>>> the wal_files, because I don't need it to be up-to-date.
>>>
>>> So what I did is:
>>>
>>> - Replaced /var/lib/pgsql/9.2/data/ with the basebackup
>>> - Created recovery.conf:
>>>
>>> restore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'
>>>
>>> recovery_target_timeline = 'latest'
>>>
>>> standby_mode = off
>>>
>>> trigger_file = '/tmp/pg_failover_trigger'
>>>
>>> - touch /tmp/pg_failover_trigger
>>> - service postgresql start
>>>
>>> And then postgres starts recovering the wal_files. But I don't want
>>> that.. as I don't need a up-to-date
>>>
>>> Is the wal_files required anyway?
>>>
>>>
>> ​"...has a basebackup from the master" - the answer to your question
>> depends greatly on the detail behind that sentence.
>>
>> IIRC, unless you know that the data directory is consistent - because the
>> database was offline at the time of the backup - at least some WAL will
>> probably be required to bring the inconsistent backup data directory to a
>> known good state (i.e., post-checkpoint).
>>
>> David J.
>> ​
>> ​
>>
>>
>
>
> I see...
>
>
> as I'm recovering a slave and then turning it into a master, that's why
> the wal_files are required.
>
> Thanks!
> Patrick
>

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

[Patrick B]

2016-11-01 10:33 GMT+13:00 David G. Johnston :

> On Mon, Oct 31, 2016 at 1:46 PM, Patrick B 
> wrote:
>
>> Hi guys,
>>
>> I got a test server, let's call it test01.
>>
>> The test01 has a basebackup from the master.
>> I want to turn test01 into a master. It doesn't need to catch up with the
>> wal_files, because I don't need it to be up-to-date.
>>
>> So what I did is:
>>
>> - Replaced /var/lib/pgsql/9.2/data/ with the basebackup
>> - Created recovery.conf:
>>
>> restore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'
>>
>> recovery_target_timeline = 'latest'
>>
>> standby_mode = off
>>
>> trigger_file = '/tmp/pg_failover_trigger'
>>
>> - touch /tmp/pg_failover_trigger
>> - service postgresql start
>>
>> And then postgres starts recovering the wal_files. But I don't want
>> that.. as I don't need a up-to-date
>>
>> Is the wal_files required anyway?
>>
>>
> ​"...has a basebackup from the master" - the answer to your question
> depends greatly on the detail behind that sentence.
>
> IIRC, unless you know that the data directory is consistent - because the
> database was offline at the time of the backup - at least some WAL will
> probably be required to bring the inconsistent backup data directory to a
> known good state (i.e., post-checkpoint).
>
> David J.
> ​
> ​
>
>


I see...


as I'm recovering a slave and then turning it into a master, that's why the
wal_files are required.

Thanks!
Patrick

Re: [GENERAL] Turning slave into a master - PostgreSQL 9.2

[David G. Johnston]

On Mon, Oct 31, 2016 at 1:46 PM, Patrick B  wrote:

> Hi guys,
>
> I got a test server, let's call it test01.
>
> The test01 has a basebackup from the master.
> I want to turn test01 into a master. It doesn't need to catch up with the
> wal_files, because I don't need it to be up-to-date.
>
> So what I did is:
>
> - Replaced /var/lib/pgsql/9.2/data/ with the basebackup
> - Created recovery.conf:
>
> restore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'
>
> recovery_target_timeline = 'latest'
>
> standby_mode = off
>
> trigger_file = '/tmp/pg_failover_trigger'
>
> - touch /tmp/pg_failover_trigger
> - service postgresql start
>
> And then postgres starts recovering the wal_files. But I don't want that..
> as I don't need a up-to-date
>
> Is the wal_files required anyway?
>
>
​"...has a basebackup from the master" - the answer to your question
depends greatly on the detail behind that sentence.

IIRC, unless you know that the data directory is consistent - because the
database was offline at the time of the backup - at least some WAL will
probably be required to bring the inconsistent backup data directory to a
known good state (i.e., post-checkpoint).

David J.
​
​

[GENERAL] Turning slave into a master - PostgreSQL 9.2

[Patrick B]

Hi guys,

I got a test server, let's call it test01.

The test01 has a basebackup from the master.
I want to turn test01 into a master. It doesn't need to catch up with the
wal_files, because I don't need it to be up-to-date.

So what I did is:

- Replaced /var/lib/pgsql/9.2/data/ with the basebackup
- Created recovery.conf:

restore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'

recovery_target_timeline = 'latest'

standby_mode = off

trigger_file = '/tmp/pg_failover_trigger'

- touch /tmp/pg_failover_trigger
- service postgresql start

And then postgres starts recovering the wal_files. But I don't want that..
as I don't need a up-to-date

Is the wal_files required anyway?
Patrick