Thread: WAL and master multi-slave replication
Hello:
I'm currently building a Pg multiserver and have
a question about the possibility of working with
WAL in a multislave environment.
I have few master servers (write only) and
multiple slave servers (read only). I want to
write the WAL files from masters in a central
postgres and that the multiple slaves reads them
from time to time and auto update.
From time to time deleting the old entries. Can
it be done? Can it work as expected? Any problems
working this way? Should wal files be compressed or similar?
I'm thinking in a table schema like this:
CREATE TABLE "WAL_Table"
(
create_date timestamp with time zone NOT NULL,
wal bytea,
CONSTRAINT idx1 PRIMARY KEY (create_date)
)
Thanks in advance
--------------------------------
Eduardo Morrás González
Dept. I+D+i e-Crime Vigilancia Digital
S21sec Labs
Tlf: +34 902 222 521
Móvil: +34 555 555 555
www.s21sec.com, blog.s21sec.com
Salvo que se indique lo contrario, esta información es CONFIDENCIAL y
contiene datos de carácter personal que han de ser tratados conforme a la
legislación vigente en materia de protección de datos. Si usted no es
destinatario original de este mensaje, le comunicamos que no está autorizado
a revisar, reenviar, distribuir, copiar o imprimir la información en él
contenida y le rogamos que proceda a borrarlo de sus sistemas.
Kontrakoa adierazi ezean, posta elektroniko honen barruan doana ISILPEKO
informazioa da eta izaera pertsonaleko datuak dituenez, indarrean dagoen
datu pertsonalak babesteko legediaren arabera tratatu beharrekoa. Posta
honen hartzaile ez zaren kasuan, jakinarazten dizugu baimenik ez duzula
bertan dagoen informazioa aztertu, igorri, banatu, kopiatu edo inprimatzeko.
Hortaz, erregutzen dizugu posta hau zure sistemetatik berehala ezabatzea.
Antes de imprimir este mensaje valora si verdaderamente es necesario. De
esta forma contribuimos a la preservación del Medio Ambiente.
Eduardo Morras wrote: Hi, > I'm currently building a Pg multiserver and have a question about the > possibility of working with WAL in a multislave environment. > > I have few master servers (write only) and multiple slave servers (read > only). I want to write the WAL files from masters in a central postgres > and that the multiple slaves reads them from time to time and auto > update. Never heard of a "multiserver". I assume you mean there's a bunch (zero or more) slaves for each master. there's a suite to handle this kind of thing using pg_standby, including cleanup of old logs; see https://projects.commandprompt.com/public/pitrtools Mind you, the WAL files are not stored in a database but in raw files. I have never seen anyone advocating the use of a database to store them. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
At 19:05 24/06/2009, Alvaro Herrera wrote: >Eduardo Morras wrote: > >Hi, > > > I'm currently building a Pg multiserver and have a question about the > > possibility of working with WAL in a multislave environment. > > > > I have few master servers (write only) and multiple slave servers (read > > only). I want to write the WAL files from masters in a central postgres > > and that the multiple slaves reads them from time to time and auto > > update. > >Never heard of a "multiserver". I assume you mean there's a bunch (zero >or more) slaves for each master. Yes, there will be 3 masters recolleting data (doing updates, inserts and deletes) for now and 5 slaves where we will do the searches. The slaves must have all the data recollected by the 3 masters and the system must be easily upgradable, adding new masters and new slaves. >there's a suite to handle this kind of thing using pg_standby, including >cleanup of old logs; see >https://projects.commandprompt.com/public/pitrtools Didn't know about this solution (PITR yes, but not pitrtools) i'm checking it. >Mind you, the WAL files are not stored in a database but in raw files. >I have never seen anyone advocating the use of a database to store them. Well, i was thinking in slaves servers doing selects to the "central repository" to retrieve the wals archives from timestamp to timestamp and processing them. Thanks -------------------------------- Eduardo Morrás González Dept. I+D+i e-Crime Vigilancia Digital S21sec Labs Tlf: +34 902 222 521 Móvil: +34 555 555 555 www.s21sec.com, blog.s21sec.com Salvo que se indique lo contrario, esta información es CONFIDENCIAL y contiene datos de carácter personal que han de ser tratados conforme a la legislación vigente en materia de protección de datos. Si usted no es destinatario original de este mensaje, le comunicamos que no está autorizado a revisar, reenviar, distribuir, copiar o imprimir la información en él contenida y le rogamos que proceda a borrarlo de sus sistemas. Kontrakoa adierazi ezean, posta elektroniko honen barruan doana ISILPEKO informazioa da eta izaera pertsonaleko datuak dituenez, indarrean dagoen datu pertsonalak babesteko legediaren arabera tratatu beharrekoa. Posta honen hartzaile ez zaren kasuan, jakinarazten dizugu baimenik ez duzula bertan dagoen informazioa aztertu, igorri, banatu, kopiatu edo inprimatzeko. Hortaz, erregutzen dizugu posta hau zure sistemetatik berehala ezabatzea. Antes de imprimir este mensaje valora si verdaderamente es necesario. De esta forma contribuimos a la preservación del Medio Ambiente.
On Wed, Jun 24, 2009 at 11:22 AM, Eduardo Morras<emorras@s21sec.com> wrote: > Yes, there will be 3 masters recolleting data (doing updates, inserts and > deletes) for now and 5 slaves where we will do the searches. The slaves must > have all the data recollected by the 3 masters and the system must be easily > upgradable, adding new masters and new slaves. You know you can't push WAL files from > 1 server into a slave, right?
At 19:25 24/06/2009, you wrote: >On Wed, Jun 24, 2009 at 11:22 AM, Eduardo Morras<emorras@s21sec.com> wrote: > > Yes, there will be 3 masters recolleting data (doing updates, inserts and > > deletes) for now and 5 slaves where we will > do the searches. The slaves must > > have all the data recollected by the 3 > masters and the system must be easily > > upgradable, adding new masters and new slaves. > >You know you can't push WAL files from > 1 server into a slave, right? No, i didn't know that. I read the page http://www.postgresql.org/docs/8.4/static/warm-standby.html and infer/understood that when talking about "primary server" it could be more than one. Thanks for the clarification. -------------------------------- Eduardo Morrás González Dept. I+D+i e-Crime Vigilancia Digital S21sec Labs Tlf: +34 902 222 521 Móvil: +34 555 555 555 www.s21sec.com, blog.s21sec.com Salvo que se indique lo contrario, esta información es CONFIDENCIAL y contiene datos de carácter personal que han de ser tratados conforme a la legislación vigente en materia de protección de datos. Si usted no es destinatario original de este mensaje, le comunicamos que no está autorizado a revisar, reenviar, distribuir, copiar o imprimir la información en él contenida y le rogamos que proceda a borrarlo de sus sistemas. Kontrakoa adierazi ezean, posta elektroniko honen barruan doana ISILPEKO informazioa da eta izaera pertsonaleko datuak dituenez, indarrean dagoen datu pertsonalak babesteko legediaren arabera tratatu beharrekoa. Posta honen hartzaile ez zaren kasuan, jakinarazten dizugu baimenik ez duzula bertan dagoen informazioa aztertu, igorri, banatu, kopiatu edo inprimatzeko. Hortaz, erregutzen dizugu posta hau zure sistemetatik berehala ezabatzea. Antes de imprimir este mensaje valora si verdaderamente es necesario. De esta forma contribuimos a la preservación del Medio Ambiente.
Eduardo Morras escribió: > At 19:25 24/06/2009, you wrote: >> On Wed, Jun 24, 2009 at 11:22 AM, Eduardo Morras<emorras@s21sec.com> wrote: >> > Yes, there will be 3 masters recolleting data (doing updates, inserts and >> > deletes) for now and 5 slaves where we will do the searches. The >> slaves must >> > have all the data recollected by the 3 masters and the system must be >> easily >> > upgradable, adding new masters and new slaves. >> >> You know you can't push WAL files from > 1 server into a slave, right? > > No, i didn't know that. I guess you don't know either that you can't query a slave while it is on recovery (so it's only a "warm" standby, not hot). And if you bring it up you can't afterwards continue applying more segments later. What you can do is grab a filesystem snapshot before bringing it online, and then restoring that snapshot when you want to apply some more segments to bring it up to date (so from Postgres' point of view it seems like it was never brought up in the first place). -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Wed, Jun 24, 2009 at 11:51 AM, Alvaro Herrera<alvherre@commandprompt.com> wrote: > Eduardo Morras escribió: >> At 19:25 24/06/2009, you wrote: >>> On Wed, Jun 24, 2009 at 11:22 AM, Eduardo Morras<emorras@s21sec.com> wrote: >>> > Yes, there will be 3 masters recolleting data (doing updates, inserts and >>> > deletes) for now and 5 slaves where we will do the searches. The >>> slaves must >>> > have all the data recollected by the 3 masters and the system must be >>> easily >>> > upgradable, adding new masters and new slaves. >>> >>> You know you can't push WAL files from > 1 server into a slave, right? >> >> No, i didn't know that. > > I guess you don't know either that you can't query a slave while it is > on recovery (so it's only a "warm" standby, not hot). And if you bring > it up you can't afterwards continue applying more segments later. I think the OP's needs might be better met by slony or londiste and some views over the top of a bunch of tables than using PITR.
Thanks, Scot Kreienkamp La-Z-Boy Inc. skreien@la-z-boy.com 734-242-1444 ext 6379 -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alvaro Herrera Sent: Wednesday, June 24, 2009 1:51 PM To: Eduardo Morras Cc: Scott Marlowe; pgsql-general@postgresql.org Subject: Re: [GENERAL] WAL and master multi-slave replication Eduardo Morras escribió: > At 19:25 24/06/2009, you wrote: >> On Wed, Jun 24, 2009 at 11:22 AM, Eduardo Morras<emorras@s21sec.com> wrote: >> > Yes, there will be 3 masters recolleting data (doing updates, inserts and >> > deletes) for now and 5 slaves where we will do the searches. The >> slaves must >> > have all the data recollected by the 3 masters and the system must be >> easily >> > upgradable, adding new masters and new slaves. >> >> You know you can't push WAL files from > 1 server into a slave, right? > > No, i didn't know that. I guess you don't know either that you can't query a slave while it is on recovery (so it's only a "warm" standby, not hot). And if you bring it up you can't afterwards continue applying more segments later. What you can do is grab a filesystem snapshot before bringing it online, and then restoring that snapshot when you want to apply some more segments to bring it up to date (so from Postgres' point of view it seems like it was never brought up in the first place). That is what I do. I actually have two separate copies of Postgres running at any given time on one of my mirrors. Thefirst is running recovery constantly. The second is an LVM snapshot that is mounted on a different directory that listenson the network IP address. Every hour I have a script that shuts down both copies of Postgres, re-creates and remountsthe new snapshot, alters the Postgresql.conf listen address, brings the LVM snapshot Postgres out of recovery, andthen starts both copies of Postgres again. It takes about 60 seconds for the whole process with a few sleep statementsto smooth things out. It guarantees my PITR mirror is still running and allows the mirror to be queryable. That'sthe best solution I could figure out to fit my requirements. BTW, PITRtools is very nice. I had it scripted in 8.2, when 8.3 came out I switched to PITRtools so it would delete theWAL logs I no longer needed. Very nice, and much easier than my old scripts.