pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedStreaming replication: rsync to switchover
8+ messages / 4 participants
[nested] [flat]
* Streaming replication: rsync to switchover
@ 2011-04-18 17:16 rd <rudi.pettazzi@kemen.it>
2011-04-19 08:09 ` Re: Streaming replication: rsync to switchover Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: rd @ 2011-04-18 17:16 UTC (permalink / raw)
To: pgsql-admin
Hi,
I've setup streaming replication + file-based log-shipping, with a
hot-standby.
Everything's good, except I don't know which is the correct way to sync
the once-master db with the newly promoted master after a switchover,
1) I shutdown the master and touch the trigger_file on the slave. Now
the slave becomes the master.
2) On the old-master I create recovery.conf + archive directory for
file-based log-shipping.
3) I hope I can setup the old-master with a simple rsync, because the
operation could be really fast, being M and S almost identical, but I'm
not sure about the correct way to rsync them:
a) should I rsync --delete?
b) should I delete the pg_xlog of the old master, before rsyncing? (I'm
wondering what could happen if some of them never made its way to the
slave, that now has become new-master)
c) am I missing something? For example, I've read somewhere that one
should copy pg_control and pg_stat from the old slave to the new slave
but it doesn't make sense to me.
thanks for any hint,
--
rudi pettazzi
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Streaming replication: rsync to switchover
2011-04-18 17:16 Streaming replication: rsync to switchover rd <rudi.pettazzi@kemen.it>
@ 2011-04-19 08:09 ` Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
2011-04-19 09:16 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Gerhard Hintermayer @ 2011-04-19 08:09 UTC (permalink / raw)
To: rd <rudi.pettazzi@kemen.it>; pgsql-admin
Hi, I do rsync -a --delete new_primary_server::postgresql-data/
/var/lib/postgresql/9.0/data/
Take care to use -a, I had -r and wondered, why the rsync took so long,
despite the data was nearly the same.
assuming you have configured rsyncd on the server as:
[postgresql-data]
uid = postgres
path = /var/lib/postgresql/9.0/data
comment = PostgreSQL 9.0 data dir
exclude = postmaster.log pg_xlog/* postmaster.pid postgresql.conf
The exclude might be tunable a litte (the confs eg. are not stored in the
datadir anymore on my distro)
Always rsync over the existing data dir (of course stop server before)
The former master can also be stopped later. When the trigger file is
created, the connection to it is closed anyhow.
Don't forget to pg_start_backup pg_stop_backup on the new server
before/after you do rsync.
Gerhard
On Mon, Apr 18, 2011 at 7:16 PM, rd <rudi.pettazzi@kemen.it> wrote:
> Hi,
>
> I've setup streaming replication + file-based log-shipping, with a
> hot-standby.
>
> Everything's good, except I don't know which is the correct way to sync the
> once-master db with the newly promoted master after a switchover,
>
> 1) I shutdown the master and touch the trigger_file on the slave. Now the
> slave becomes the master.
> 2) On the old-master I create recovery.conf + archive directory for
> file-based log-shipping.
> 3) I hope I can setup the old-master with a simple rsync, because the
> operation could be really fast, being M and S almost identical, but I'm not
> sure about the correct way to rsync them:
>
> a) should I rsync --delete?
> b) should I delete the pg_xlog of the old master, before rsyncing? (I'm
> wondering what could happen if some of them never made its way to the slave,
> that now has become new-master)
> c) am I missing something? For example, I've read somewhere that one should
> copy pg_control and pg_stat from the old slave to the new slave but it
> doesn't make sense to me.
>
>
> thanks for any hint,
>
>
> --
> rudi pettazzi
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Streaming replication: rsync to switchover
2011-04-18 17:16 Streaming replication: rsync to switchover rd <rudi.pettazzi@kemen.it>
2011-04-19 08:09 ` Re: Streaming replication: rsync to switchover Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
@ 2011-04-19 09:16 ` rudi <rudolone@gmail.com>
2011-04-19 09:28 ` Re: Streaming replication: rsync to switchover Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
2011-04-19 12:11 ` Re: Streaming replication: rsync to switchover Ray Stell <stellr@cns.vt.edu>
0 siblings, 2 replies; 8+ messages in thread
From: rudi @ 2011-04-19 09:16 UTC (permalink / raw)
To: pgsql-admin
On 04/19/2011 10:09 AM, Gerhard Hintermayer wrote:
> assuming you have configured rsyncd on the server as:
>
> [postgresql-data]
> uid = postgres
> path = /var/lib/postgresql/9.0/data
> comment = PostgreSQL 9.0 data dir
> exclude = postmaster.log pg_xlog/* postmaster.pid postgresql.conf
So you keep the old pg_xlog ... But, what about wal that the former
master hasn't yet sent to the slave? I wonder if the former-master could
apply them, becoming out-of-sync with the former-slave (that never
recevied those wal).
thanks
--
rudi
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Streaming replication: rsync to switchover
2011-04-18 17:16 Streaming replication: rsync to switchover rd <rudi.pettazzi@kemen.it>
2011-04-19 08:09 ` Re: Streaming replication: rsync to switchover Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
2011-04-19 09:16 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
@ 2011-04-19 09:28 ` Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Gerhard Hintermayer @ 2011-04-19 09:28 UTC (permalink / raw)
To: rudi <rudolone@gmail.com>; pgsql-admin
I also streaming replication _and_ ship WALs them to the slaves, something
like: restore_command='rsync -pog master::postgresql-wals/%f %p', so
normally the slaves are up to date (via streaming replication) and the WALs
are also on the slaves to be able to recover (maybe to some PIT) from them
in case something goes wrong.
The recommended setup should be handled more in detail in the docs, since
this is a topic where you can make a lot of mistakes :-(
Gerhard
On Tue, Apr 19, 2011 at 11:16 AM, rudi <rudolone@gmail.com> wrote:
> On 04/19/2011 10:09 AM, Gerhard Hintermayer wrote:
>
>> assuming you have configured rsyncd on the server as:
>>
>> [postgresql-data]
>> uid = postgres
>> path = /var/lib/postgresql/9.0/data
>> comment = PostgreSQL 9.0 data dir
>> exclude = postmaster.log pg_xlog/* postmaster.pid postgresql.conf
>>
>
> So you keep the old pg_xlog ... But, what about wal that the former master
> hasn't yet sent to the slave? I wonder if the former-master could apply
> them, becoming out-of-sync with the former-slave (that never recevied those
> wal).
>
> thanks
>
> --
> rudi
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Streaming replication: rsync to switchover
2011-04-18 17:16 Streaming replication: rsync to switchover rd <rudi.pettazzi@kemen.it>
2011-04-19 08:09 ` Re: Streaming replication: rsync to switchover Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
2011-04-19 09:16 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
@ 2011-04-19 12:11 ` Ray Stell <stellr@cns.vt.edu>
2011-04-19 15:29 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Ray Stell @ 2011-04-19 12:11 UTC (permalink / raw)
To: rudi <rudolone@gmail.com>; +Cc: pgsql-admin
On Tue, Apr 19, 2011 at 11:16:08AM +0200, rudi wrote:
> On 04/19/2011 10:09 AM, Gerhard Hintermayer wrote:
> So you keep the old pg_xlog
the admin book says to exclude old pg_xlog
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Streaming replication: rsync to switchover
2011-04-18 17:16 Streaming replication: rsync to switchover rd <rudi.pettazzi@kemen.it>
2011-04-19 08:09 ` Re: Streaming replication: rsync to switchover Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
2011-04-19 09:16 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
2011-04-19 12:11 ` Re: Streaming replication: rsync to switchover Ray Stell <stellr@cns.vt.edu>
@ 2011-04-19 15:29 ` rudi <rudolone@gmail.com>
2011-04-20 08:26 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: rudi @ 2011-04-19 15:29 UTC (permalink / raw)
To: pgsql-admin
On 04/19/2011 02:11 PM, Ray Stell wrote:
> On Tue, Apr 19, 2011 at 11:16:08AM +0200, rudi wrote:
>> On 04/19/2011 10:09 AM, Gerhard Hintermayer wrote:
>> So you keep the old pg_xlog
>
> the admin book says to exclude old pg_xlog
>
Which book is this?
So I guess something like this (from new master to former master) should
be safe:
$ psql -c "SELECT pg_start_backup('switchover base backup')"
$ rsync -av /var/lib/postgresql/9.0/main/ --delete --exclude server.crt
--exclude server.key --exclude recovery.* --exclude postmaster.pid
--exclude archive $FORMER_MASTER_ADDRESS:/var/lib/postgresql/9.0/main/
$ psql -c "SELECT pg_stop_backup()"
What about the archiving directory of the former slave (now master)?
It should be cleaned guess, in case it will be slave again. If I look at
that directory, after a failover it is not empty.
This looks strange to me because pg_cleanup_archive should have already
cleaned it.
BTW, is there any documentation about the logic behind pg_xlog
filenames? It is really obscure and this makes really hard understanding
what's going on. I'm sure that most recent mtime gives me the active
WAL, and I can match somehow this:
Latest checkpoint location: 7/F70021A0
Prior checkpoint location: 7/F7002110
Latest checkpoint's REDO location: 7/F7002168
Latest checkpoint's TimeLineID: 2
with this filename: 0000000200000007000000F7
and that's all.
thanks,
--
rd
Peel your own image from the mirror.
Sit. Feast on your life.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Streaming replication: rsync to switchover
2011-04-18 17:16 Streaming replication: rsync to switchover rd <rudi.pettazzi@kemen.it>
2011-04-19 08:09 ` Re: Streaming replication: rsync to switchover Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
2011-04-19 09:16 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
2011-04-19 12:11 ` Re: Streaming replication: rsync to switchover Ray Stell <stellr@cns.vt.edu>
2011-04-19 15:29 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
@ 2011-04-20 08:26 ` rudi <rudolone@gmail.com>
2011-04-20 08:57 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: rudi @ 2011-04-20 08:26 UTC (permalink / raw)
To: pgsql-admin
On 04/19/2011 05:29 PM, rudi wrote:
> $ psql -c "SELECT pg_start_backup('switchover base backup')"
> $ rsync -av /var/lib/postgresql/9.0/main/ --delete --exclude server.crt
> --exclude server.key --exclude recovery.* --exclude postmaster.pid
> --exclude archive $FORMER_MASTER_ADDRESS:/var/lib/postgresql/9.0/main/
> $ psql -c "SELECT pg_stop_backup()"
No, it doesn't work. The slave is stuck in "archive recovery":
Database cluster state: in archive recovery
and doesn't accept connections:
2011-04-20 10:17:00 CEST:[local]:u@postgres:[13099] FATAL: the database
system is starting up
even if is a hot standby.
I really can't find a way to do a safe switchover.
--
rd
Peel your own image from the mirror.
Sit. Feast on your life.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Streaming replication: rsync to switchover
2011-04-18 17:16 Streaming replication: rsync to switchover rd <rudi.pettazzi@kemen.it>
2011-04-19 08:09 ` Re: Streaming replication: rsync to switchover Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
2011-04-19 09:16 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
2011-04-19 12:11 ` Re: Streaming replication: rsync to switchover Ray Stell <stellr@cns.vt.edu>
2011-04-19 15:29 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
2011-04-20 08:26 ` Re: Streaming replication: rsync to switchover rudi <rudolone@gmail.com>
@ 2011-04-20 08:57 ` rudi <rudolone@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: rudi @ 2011-04-20 08:57 UTC (permalink / raw)
To: pgsql-admin
On 04/20/2011 10:26 AM, rudi wrote:
> Database cluster state: in archive recovery
>
> and doesn't accept connections:
>
> 2011-04-20 10:17:00 CEST:[local]:u@postgres:[13099] FATAL: the database
> system is starting up
>
> even if is a hot standby.
My fault: it seems I forgot to stop the backup in my previous attempt. I
tried this procedure and now the former master acts as a hot standby as
expected:
On the former master:
a) stop
b) cleanup pg_xlog
c) set archive_mode = off and hot_standby = on in postgresql.conf
On the former slave:
a) set archive_mode = on in postgresql.conf
b) psql -c "SELECT pg_start_backup('switchover base backup')"
c) rsync -av $PGDATA --delete --exclude server.crt --exclude server.key
--exclude recovery.* --exclude postmaster.pid --exclude pg_xlog
--exclude archive $SLAVE:$PGDATA
d) psql -c "SELECT pg_stop_backup()"
Finally I started the former master.
--
rd
Peel your own image from the mirror.
Sit. Feast on your life.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-04-20 08:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-18 17:16 Streaming replication: rsync to switchover rd <rudi.pettazzi@kemen.it>
2011-04-19 08:09 ` Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
2011-04-19 09:16 ` rudi <rudolone@gmail.com>
2011-04-19 09:28 ` Gerhard Hintermayer <gerhard.hintermayer@gmail.com>
2011-04-19 12:11 ` Ray Stell <stellr@cns.vt.edu>
2011-04-19 15:29 ` rudi <rudolone@gmail.com>
2011-04-20 08:26 ` rudi <rudolone@gmail.com>
2011-04-20 08:57 ` rudi <rudolone@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox