WARM standby with pg

Hi,

I have a few elaborating questions in regard to setting up Warm Standby.

1) The master keeps writing WAL files even though I'm quite sure nothing is=
happening. This seems like a large waste of diskspace?

2) Sometimes my slave does not read and delete WAL files when in recovery m=
ode. This will eventually fill up the disk.

pg_controldata gives me:

Minimum recovery ending location: 0/0

What does that mean?

Is there any good ways of troubleshooting the behaviour, and finding out pr=
ecisely what state the slave is in, etc.?

Thanks,

--
Geysir IT
dth [at] geysirit.dk
http://geysirit.dk
+45 31 51 60 00

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

This is a multi-part message in MIME format.

------=_NextPart_000_000A_01CAD70A.977D0C40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

How to block access to a scheme

I have a database with multiple schemas. I need to block access to a =
particular scheme for doing maintenance on the structure of your tables. =
Does anyone know how to block access to the schema. I also need to =
disconnect users who are accessing the system.

Grateful.

Eduardo Reis
------=_NextPart_000_000A_01CAD70A.977D0C40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18828">
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><SPAN id=3Dresult_box class=3Dlong_text><FONT face=3DArial><FONT =
size=3D2><SPAN
style=3D"BACKGROUND-COLOR: #fff" title=3D"Como bloquear acesso a um =
esquema"
closure_uid_cyyd5c=3D"269">How to block access to a =
scheme<BR><BR></SPAN><SPAN
style=3D"BACKGROUND-COLOR: #fff"
title=3D"Tenho um banco de dados com v=E1rios esquemas." =
closure_uid_cyyd5c=3D"270">I
have a database with multiple schemas. </SPAN><SPAN
style=3D"BACKGROUND-COLOR: #fff"
title=3D"Eu preciso bloquear o acesso a um determinado esquema para =
fazer manuten=E7=E3o na estrutura de suas tabelas."
closure_uid_cyyd5c=3D"271">I need to block access to a particular scheme =
for doing
maintenance on the structure of your tables. </SPAN><SPAN
style=3D"BACKGROUND-COLOR: #fff"
title=3D"Algu=E9m sabe como bloquear acesso ao esquema."
closure_uid_cyyd5c=3D"272">Does anyone know how to block access to the =
schema.
</SPAN></FONT></FONT><SPAN style=3D"BACKGROUND-COLOR: #fff"
title=3D"Tamb=E9m preciso desconectar os usu=E1rios que est=E3o =
acessando o sistema."
closure_uid_cyyd5c=3D"273"><FONT size=3D2 face=3DArial>I also need to =
disconnect users
who are accessing the system.<BR><BR></FONT></SPAN><FONT =
face=3DArial><FONT
size=3D2><SPAN title=3DGrato. =
closure_uid_cyyd5c=3D"274">Grateful.<BR><BR></SPAN><SPAN
title=3D"Eduardo Reis" closure_uid_cyyd5c=3D"275">Eduardo
Reis</SPAN></FONT></FONT></SPAN></DIV></BODY></HTML>

------=_NextPart_000_000A_01CAD70A.977D0C40--

Eduardo S=E1 dos Reis<eduardoreis [at] pjf.mg.gov.br> wrote:

> How to block access to a scheme
>
> I have a database with multiple schemas. I need to block access to
> a particular scheme for doing maintenance on the structure of your
> tables. Does anyone know how to block access to the schema.

I'm not sure I exactly understand, but you can probably find
something on this page that will help:

http://www.postgresql.org/docs/8.4/interactive/sql-revoke.ht ml

Perhaps this?:

REVOKE ALL ON SCHEMA x FROM y;

> I also need to disconnect users who are accessing the system.

http://www.postgresql.org/docs/8.4/interactive/functions-adm in.html#FUNCT=
IONS-ADMIN-SIGNAL-TABLE

Before using these functions, you probably want to modify the
pg_hba.conf file and signal a reload, to block new logins.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Dennis Thrys=F8e<dth [at] geysirit.dk> wrote:

> 1) The master keeps writing WAL files even though I'm quite sure
> nothing is happening. This seems like a large waste of diskspace?

What is your setting for archive_timeout? This limits how long
before a WAL file is sent. You could extend the time, although that
means that in case of a failure, you might not be as up-to-date.
Another option is to use pg_clearxlogtail with gzip or use
pglesslog. I haven't used pglesslog, but piping an empty WAL file
through pg_clearxlogtail and gzip reduces it to about 16 kB (rather
than 16 MB).

> 2) Sometimes my slave does not read and delete WAL files when in
> recovery mode. This will eventually fill up the disk.

Sorry I can't help with that one -- we use our own scripts rather
than pg_standby. Anyone else recognize this issue?

> pg_controldata gives me:
>
> Minimum recovery ending location: 0/0
>
> What does that mean?

I think that only has meaning when the cluster is in archive
recovery status. What does pg_controldata say the "Database cluster
state" is when you see this?

> Is there any good ways of troubleshooting the behaviour, and
> finding out precisely what state the slave is in, etc.?

We use pg_controldata and check "Database cluster state" to ensure
that our warm standbys are still "in archive recovery" and we check
the "Time of latest checkpoint" to ensure that its age is not much
beyond our archive_timeout setting -- to ensure that the data is
indeed still flowing.

I believe that 9.0 will be adding some nicer ways to check such
things.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Hi again,

After copying a new dump of the MASTER cluster data and starting the SLAVE =
with this data, I now get:

Database cluster state: in production
...
Minimum recovery ending location: 0/0

Still not exactly as expected, I guess. The log says things like :

"cp: cannot stat `/psql_archive/00000001.history': No such file or director=
y"

By the way, one of these lines each second!

"2010-04-09 09:09:49 IST FATAL: the database system is starting up"

Any help appreciated.

-dennis

--
Geysir IT
dth [at] geysirit.dk
http://geysirit.dk
+45 31 51 60 00

On 08/04/2010, at 18.36, Kevin Grittner wrote:

> Dennis Thrys=F8e<dth [at] geysirit.dk> wrote:
>
>> 1) The master keeps writing WAL files even though I'm quite sure
>> nothing is happening. This seems like a large waste of diskspace?
>
> What is your setting for archive_timeout? This limits how long
> before a WAL file is sent. You could extend the time, although that
> means that in case of a failure, you might not be as up-to-date.
> Another option is to use pg_clearxlogtail with gzip or use
> pglesslog. I haven't used pglesslog, but piping an empty WAL file
> through pg_clearxlogtail and gzip reduces it to about 16 kB (rather
> than 16 MB).
>
>> 2) Sometimes my slave does not read and delete WAL files when in
>> recovery mode. This will eventually fill up the disk.
>
> Sorry I can't help with that one -- we use our own scripts rather
> than pg_standby. Anyone else recognize this issue?
>
>> pg_controldata gives me:
>>
>> Minimum recovery ending location: 0/0
>>
>> What does that mean?
>
> I think that only has meaning when the cluster is in archive
> recovery status. What does pg_controldata say the "Database cluster
> state" is when you see this?
>
>> Is there any good ways of troubleshooting the behaviour, and
>> finding out precisely what state the slave is in, etc.?
>
> We use pg_controldata and check "Database cluster state" to ensure
> that our warm standbys are still "in archive recovery" and we check
> the "Time of latest checkpoint" to ensure that its age is not much
> beyond our archive_timeout setting -- to ensure that the data is
> indeed still flowing.
>
> I believe that 9.0 will be adding some nicer ways to check such
> things.
>
> -Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Fri, Apr 09, 2010 at 10:19:20AM +0200, Dennis Thrys?e wrote:
> "cp: cannot stat `/psql_archive/00000001.history': No such file or directory"
>
> By the way, one of these lines each second!
>
> "2010-04-09 09:09:49 IST FATAL: the database system is starting up"

I asked about this a few weeks ago and here was the reply. You're good:

* From: Bruce Momjian <bruce [at] momjian.us>
There is also change in 9.0:

Fix longstanding gripe that we check for 0000000001.history at start of
archive recovery, even when we know it is never present.

so you should not see this when using >= PG 9.0.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Dennis Thrys=F8e<dth [at] geysirit.dk> wrote:

> After copying a new dump of the MASTER cluster data and starting
> the SLAVE with this data, I now get:
>
> Database cluster state: in production
> ..
> Minimum recovery ending location: 0/0

Somehow it completed archive recovery and switched over to
production. How are you configured for triggering that? Is there
some triggering file you need to delete before you try again?

> "cp: cannot stat `/psql_archive/00000001.history': No such file
> or directory"

As already mentioned, that's just noise. Ignore that line, as long
as you just see it once per startup.

> By the way, one of these lines each second!
>
> "2010-04-09 09:09:49 IST FATAL: the database system is starting
> up"

Forever, or some fixed number of times?

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

This is a multi-part message in MIME format.

------=_NextPart_000_000B_01CADA22.D67670B0
Content-Type: text/plain;
charset="ISO-8859-15"
Content-Transfer-Encoding: quoted-printable

I need to disconnect user in my database without creating =
inconsistencies. What command should I use.

Grateful

Eduardo
------=_NextPart_000_000B_01CADA22.D67670B0
Content-Type: text/html;
charset="ISO-8859-15"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-15" =
http-equiv=3DContent-Type>
<META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18828">
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><SPAN id=3Dresult_box class=3Dmedium_text><SPAN
style=3D"BACKGROUND-COLOR: rgb(255,255,255)"
title=3D"Eu preciso desconetctar usu=E1rio no meu banco de dados sem =
gerar inconsistencias."><SPAN
id=3Dresult_box class=3Dmedium_text><SPAN style=3D"BACKGROUND-COLOR: =
rgb(255,255,255)"
title=3D"Eu preciso desconectar usu=E1rio no meu banco de dados sem =
gerar inconsistencias."><FONT
size=3D2 face=3DArial>I need to disconnect user in my database without =
creating
inconsistencies. </FONT></SPAN><FONT face=3DArial><FONT size=3D2><SPAN
style=3D"BACKGROUND-COLOR: rgb(255,255,255)"
title=3D"Qual comando devo utilizar.">What command should I
use.<BR><BR></SPAN><SPAN style=3D"BACKGROUND-COLOR: rgb(255,255,255)"
title=3DGrato>Grateful</SPAN></FONT></FONT></SPAN></SPAN></SPAN></DIV>
<DIV><SPAN class=3Dmedium_text><SPAN style=3D"BACKGROUND-COLOR: =
rgb(255,255,255)"
title=3D"Eu preciso desconetctar usu=E1rio no meu banco de dados sem =
gerar inconsistencias."><SPAN
class=3Dmedium_text><SPAN style=3D"BACKGROUND-COLOR: rgb(255,255,255)"
title=3DGrato><FONT size=3D2
face=3DArial></FONT></SPAN></SPAN></SPAN></SPAN> </DIV>
<DIV><SPAN class=3Dmedium_text><SPAN style=3D"BACKGROUND-COLOR: =
rgb(255,255,255)"
title=3D"Eu preciso desconetctar usu=E1rio no meu banco de dados sem =
gerar inconsistencias."><SPAN
class=3Dmedium_text><SPAN style=3D"BACKGROUND-COLOR: rgb(255,255,255)"
title=3DGrato><FONT size=3D2
face=3DArial>Eduardo</FONT></SPAN></SPAN></SPAN></SPAN></DIV></BODY></HTM=
L>

------=_NextPart_000_000B_01CADA22.D67670B0--

2010/4/12 Eduardo S=E1 dos Reis <eduardoreis [at] pjf.mg.gov.br>:
> I need to disconnect user in my database without creating inconsistencies.
> What command should I use.

If you are using 8.4 then you can do pg_terminate_backend(pid)
function. For more info read
http://www.postgresql.org/docs/8.4/interactive/functions-adm in.html

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru [at] gmail.com / Skype: gray-hemp / ICQ: 29353802

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--Apple-Mail-6-713827209
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=iso-8859-1

On 12-Apr-2010, at 6:00 PM, Eduardo S=E1 dos Reis wrote:

> I need to disconnect user in my database without creating =
inconsistencies. What command should I use.
>
> Grateful
>
> Eduardo

Which Version of PostgreSQL are you using?

In PostgreSQL 8.4, you can use pg_terminate_backend() function.

Thanks & Regards,
Vibhor Kumar (PCP & OCP)
ITIL V3 Cerftified.
Mob: +91-9011042623
Web:www.EnterpriseDB.com

--Apple-Mail-6-713827209
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
charset=iso-8859-1

<html><head><base href=3D"x-msg://560/"></head><body style=3D"word-wrap: =
break-word; -webkit-nbsp-mode: space; -webkit-line-break: =
after-white-space; "><div><br><div><div>On 12-Apr-2010, at 6:00 PM, =
Eduardo S=E1 dos Reis wrote:</div><br =
class=3D"Apple-interchange-newline"><blockquote type=3D"cite"><span =
class=3D"Apple-style-span" style=3D"border-collapse: separate; =
font-family: Courier; font-size: medium; font-style: normal; =
font-variant: normal; font-weight: normal; letter-spacing: normal; =
line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; =
white-space: normal; widows: 2; word-spacing: 0px; =
-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: =
0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; "><div><div><span id=3D"result_box" =
class=3D"medium_text"><span title=3D"Eu preciso desconetctar usu=E1rio =
no meu banco de dados sem gerar inconsistencias." =
style=3D"background-color: rgb(255, 255, 255); "><span id=3D"result_box" =
class=3D"medium_text"><span title=3D"Eu preciso desconectar usu=E1rio no =
meu banco de dados sem gerar inconsistencias." style=3D"background-color: =
rgb(255, 255, 255); "><font size=3D"2" face=3D"Arial">I need to =
disconnect user in my database without creating inconsistencies.<span =
class=3D"Apple-converted-space"> </span></font></span><font =
face=3D"Arial"><font size=3D"2"><span title=3D"Qual comando devo =
utilizar." style=3D"background-color: rgb(255, 255, 255); ">What command =
should I use.<br><br></span><span title=3D"Grato" =
style=3D"background-color: rgb(255, 255, 255); =
">Grateful</span></font></font></span></span></span></div><div><span =
class=3D"medium_text"><span title=3D"Eu preciso desconetctar usu=E1rio =
no meu banco de dados sem gerar inconsistencias." =
style=3D"background-color: rgb(255, 255, 255); "><span =
class=3D"medium_text"><span title=3D"Grato" style=3D"background-color: =
rgb(255, 255, 255); "><font size=3D"2" =
face=3D"Arial"></font></span></span></span></span> </div><div><span =
class=3D"medium_text"><span title=3D"Eu preciso desconetctar usu=E1rio =
no meu banco de dados sem gerar inconsistencias." =
style=3D"background-color: rgb(255, 255, 255); "><span =
class=3D"medium_text"><span title=3D"Grato" style=3D"background-color: =
rgb(255, 255, 255); "><font size=3D"2" =
face=3D"Arial">Eduardo</font></span></span></span></span></div></div></spa=
n></blockquote></div><div><br></div>Which Version of PostgreSQL are you =
using?</div><div><br></div><div>In PostgreSQL 8.4, you can use =
pg_terminate_backend() function.</div><div><br><div>
<span class=3D"Apple-style-span" style=3D"border-collapse: separate; =
color: rgb(0, 0, 0); font-family: Courier; font-size: medium; =
font-style: normal; font-variant: normal; font-weight: normal; =
letter-spacing: normal; line-height: normal; orphans: 2; text-align: =
auto; text-indent: 0px; text-transform: none; white-space: normal; =
widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; =
-webkit-border-vertical-spacing: 0px; =
-webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; "><span class=3D"Apple-style-span" =
style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: =
Courier; font-size: medium; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; =
widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; =
-webkit-border-vertical-spacing: 0px; =
-webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; "><div style=3D"word-wrap: =
break-word; -webkit-nbsp-mode: space; -webkit-line-break: =
after-white-space; "><span class=3D"Apple-style-span" =
style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: =
Courier; font-size: medium; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; =
widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; =
-webkit-border-vertical-spacing: 0px; =
-webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; "><span class=3D"Apple-style-span" =
style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: =
Courier; font-size: medium; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; =
widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; =
-webkit-border-vertical-spacing: 0px; =
-webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; "><div><div>Thanks & =
Regards,</div><div>Vibhor Kumar (PCP & OCP)</div><div>ITIL V3 =
Cerftified.</div><div>Mob: =
+91-9011042623</div><div>Web:www.EnterpriseDB.com</div></div></span></span=
></div></span></span>
</div>
<br></div></body></html>=

--Apple-Mail-6-713827209--

WARM standby with pg_standby

How to block access to a scheme

Re: How to block access to a scheme

Re: WARM standby with pg_standby

Re: WARM standby with pg_standby

Re: WARM standby with pg_standby

Re: WARM standby with pg_standby

how to disconnect users

Re: how to disconnect users

Re: how to disconnect users