Turning slave into a master - PostgreSQL 9.2

On Mon, Oct 31, 2016 at 1:46 PM, Patrick B <patrickbakerbr@gmail.com> 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.
​
​

2016-11-01 10:33 GMT+13:00 David G. Johnston <david.g.johnston@gmail.com>:

On Mon, Oct 31, 2016 at 1:46 PM, Patrick B <patrickbakerbr@gmail.com>
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

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 <patrickbakerbr@gmail.com>:

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

Hi:

On Tue, Nov 1, 2016 at 3:01 AM, Patrick B <patrickbakerbr@gmail.com> 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

On Mon, Oct 31, 2016 at 8:01 PM, Patrick B <patrickbakerbr@gmail.com> 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

2016-11-02 2:55 GMT+13:00 Scott Marlowe <scott.marlowe@gmail.com>:

On Mon, Oct 31, 2016 at 8:01 PM, Patrick B <patrickbakerbr@gmail.com>
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!

2016-11-02 8:43 GMT+13:00 Patrick B <patrickbakerbr@gmail.com>:

2016-11-02 2:55 GMT+13:00 Scott Marlowe <scott.marlowe@gmail.com>:

On Mon, Oct 31, 2016 at 8:01 PM, Patrick B <patrickbakerbr@gmail.com>
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 "00000002000019D5000000EC" has wrong size: 0 instead
of 16777216

To fix it:

rm 00000002000019D5000000EC

Restart postgres and then:

On Tue, Nov 1, 2016 at 1:43 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

2016-11-02 2:55 GMT+13:00 Scott Marlowe <scott.marlowe@gmail.com>:

On Mon, Oct 31, 2016 at 8:01 PM, Patrick B <patrickbakerbr@gmail.com>
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