Обсуждение: Turning slave into a master - PostgreSQL 9.2
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:
- touch /tmp/pg_failover_triggerrestore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'recovery_target_timeline = 'latest'standby_mode = offtrigger_file = '/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
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:- touch /tmp/pg_failover_triggerrestore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'recovery_target_timeline = 'latest'standby_mode = offtrigger_file = '/tmp/pg_failover_trigger'- service postgresql startAnd then postgres starts recovering the wal_files. But I don't want that.. as I don't need a up-to-dateIs 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>:
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:- touch /tmp/pg_failover_triggerrestore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'recovery_target_timeline = 'latest'standby_mode = offtrigger_file = '/tmp/pg_failover_trigger'- service postgresql startAnd then postgres starts recovering the wal_files. But I don't want that.. as I don't need a up-to-dateIs 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>:
2016-11-01 10:33 GMT+13:00 David G. Johnston <david.g.johnston@gmail.com>: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:- touch /tmp/pg_failover_triggerrestore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'recovery_target_timeline = 'latest'standby_mode = offtrigger_file = '/tmp/pg_failover_trigger'- service postgresql startAnd then postgres starts recovering the wal_files. But I don't want that.. as I don't need a up-to-dateIs 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
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.
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.
2016-11-02 2:55 GMT+13:00 Scott Marlowe <scott.marlowe@gmail.com>:
You can try using pg_basebackup to get the replica setup. In 9.2 youOn 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
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>:You can try using pg_basebackup to get the replica setup. In 9.2 youOn 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
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:
LOG: archive recovery complete
LOG: database system is ready to accept connections
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.