Hi everyone,

I have some queries regarding the PITR backup procedure on Postgres
8.3. Here are the steps I follow for backup

1. I set up WAL archiving and checked that this is working.
2. Execute SELECT pg_start_backup('label')
3. Zip the entire data directory excluding the pg_xlog directory.
4. At this point the WAL archive directory contains a .backup file
which looks something like this
00000001000000000000000B.00000020.backup. This recognizes that the WAL
file 00000001000000000000000B (and all subsequent WAL files) must be
present when we restore the database.
5. The contents of the .backup file looks something like this
START WAL LOCATION: 0/B000020 (file 00000001000000000000000B)
STOP WAL LOCATION: 0/C000000 (file 00000001000000000000000C)
CHECKPOINT LOCATION: 0/B000020
START TIME: 2009-07-22 04:02:25 UTC
LABEL: Something
STOP TIME: 2009-07-22 04:02:39 UTC
6. Execute the SELECT pg_stop_backup() command to stop the backup.

I have a few questions about this.

1. I was not able to find the file 00000001000000000000000C in the WAL
archive location after taking the base backup. Is that normal ? The
file 00000001000000000000000B exists and is the last WAL file. The
server was stopped after taking the base backup

2. When I do a restore, postgres will have a look at the
restore_command from my recover.conf to look for all WAL files from
00000001000000000000000B right ? Is it ok if it does not find
00000001000000000000000C ?

3. Lets assume that after taking the base backup the WAL files with
the suffix 0C 0D 0E etc were generated. What happens if the entire
hard disk crashes but I still have the data directory archived along
with the WAL file 00000001000000000000000B ? It means that all the
data that was in the DB till the base backup can be recovered but any
subsequent data that was updated / inserted will be lost. Am I right
when I say that ?

Thanks for reading through the message and for your time.

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

Anyone ?

On Wed, Jul 22, 2009 at 10:55 AM, Deepak Bala<deepak.bala.x [at] gmail.com> wrote:
> Hi everyone,
>
> I have some queries regarding the PITR backup procedure on Postgres
> 8.3. Here are the steps I follow for backup
>
> 1. I set up WAL archiving and checked that this is working.
> 2. Execute SELECT pg_start_backup('label')
> 3. Zip the entire data directory excluding the pg_xlog directory.
> 4. At this point the WAL archive directory contains a .backup file
> which looks something like this
> 00000001000000000000000B.00000020.backup. This recognizes that the WAL
> file 00000001000000000000000B (and all subsequent WAL files) must be
> present when we restore the database.
> 5. The contents of the .backup file looks something like this
> START WAL LOCATION: 0/B000020 (file 00000001000000000000000B)
> STOP WAL LOCATION: 0/C000000 (file 00000001000000000000000C)
> CHECKPOINT LOCATION: 0/B000020
> START TIME: 2009-07-22 04:02:25 UTC
> LABEL: Something
> STOP TIME: 2009-07-22 04:02:39 UTC
> 6. Execute the SELECT pg_stop_backup() command to stop the backup.
>
> I have a few questions about this.
>
> 1. I was not able to find the file 00000001000000000000000C in the WAL
> archive location after taking the base backup. Is that normal ? The
> file 00000001000000000000000B exists and is the last WAL file. The
> server was stopped after taking the base backup
>
> 2. When I do a restore, postgres will have a look at the
> restore_command from my recover.conf to look for all WAL files from
> 00000001000000000000000B right ? Is it ok if it does not find
> 00000001000000000000000C ?
>
> 3. Lets assume that after taking the base backup the WAL files with
> the suffix 0C 0D 0E etc were generated. What happens if the entire
> hard disk crashes but I still have the data directory archived along
> with the WAL file 00000001000000000000000B ? It means that all the
> data that was in the DB till the base backup can be recovered but any
> subsequent data that was updated / inserted will be lost. Am I right
> when I say that ?
>
> Thanks for reading through the message and for your time.
>

--
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 Wednesday 22 July 2009 08:25:36 Deepak Bala wrote:
> Hi everyone,
>
> I have some queries regarding the PITR backup procedure on Postgres
> 8.3. Here are the steps I follow for backup
>
> 1. I set up WAL archiving and checked that this is working.
> 2. Execute SELECT pg_start_backup('label')
> 3. Zip the entire data directory excluding the pg_xlog directory.
> 4. At this point the WAL archive directory contains a .backup file
> which looks something like this
> 00000001000000000000000B.00000020.backup. This recognizes that the WAL
> file 00000001000000000000000B (and all subsequent WAL files) must be
> present when we restore the database.
> 5. The contents of the .backup file looks something like this
> START WAL LOCATION: 0/B000020 (file 00000001000000000000000B)
> STOP WAL LOCATION: 0/C000000 (file 00000001000000000000000C)
> CHECKPOINT LOCATION: 0/B000020
> START TIME: 2009-07-22 04:02:25 UTC
> LABEL: Something
> STOP TIME: 2009-07-22 04:02:39 UTC
> 6. Execute the SELECT pg_stop_backup() command to stop the backup.
>
> I have a few questions about this.
>
> 1. I was not able to find the file 00000001000000000000000C in the WAL
> archive location after taking the base backup. Is that normal ? The
> file 00000001000000000000000B exists and is the last WAL file. The
> server was stopped after taking the base backup

This is normal, although arguably not desirable. In PostgreSQL 8.4, this was
changed so that pg_stop_backup() waits until the ...000C file in your case is
in the archive. So that is what you want.

> 2. When I do a restore, postgres will have a look at the
> restore_command from my recover.conf to look for all WAL files from
> 00000001000000000000000B right ? Is it ok if it does not find
> 00000001000000000000000C ?

Yes. Recovery will stop when it runs out of files to restore.

> 3. Lets assume that after taking the base backup the WAL files with
> the suffix 0C 0D 0E etc were generated. What happens if the entire
> hard disk crashes but I still have the data directory archived along
> with the WAL file 00000001000000000000000B ? It means that all the
> data that was in the DB till the base backup can be recovered but any
> subsequent data that was updated / inserted will be lost. Am I right
> when I say that ?

In this scenario you would have to restore your *previous* base backup,
because the current base backup wouldn't be usuable, as it requires that the
....000C file be present.

It's always a good idea to have two base backups around, if you can afford the
space, in case something goes wrong during or around the time you take the
next base backup.


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

Thanks for your replies Peter.

On Wed, Jul 29, 2009 at 12:45 PM, Peter Eisentraut<peter_e [at] gmx.net> wrote:
> On Wednesday 22 July 2009 08:25:36 Deepak Bala wrote:
>> Hi everyone,
>>
>> I have some queries regarding the PITR backup procedure on Postgres
>> 8.3. Here are the steps I follow for backup
>>
>> 1. I set up WAL archiving and checked that this is working.
>> 2. Execute SELECT pg_start_backup('label')
>> 3. Zip the entire data directory excluding the pg_xlog directory.
>> 4. At this point the WAL archive directory contains a .backup file
>> which looks something like this
>> 00000001000000000000000B.00000020.backup. This recognizes that the WAL
>> file 00000001000000000000000B (and all subsequent WAL files) must be
>> present when we restore the database.
>> 5. The contents of the .backup file looks something like this
>> START WAL LOCATION: 0/B000020 (file 00000001000000000000000B)
>> STOP WAL LOCATION: 0/C000000 (file 00000001000000000000000C)
>> CHECKPOINT LOCATION: 0/B000020
>> START TIME: 2009-07-22 04:02:25 UTC
>> LABEL: Something
>> STOP TIME: 2009-07-22 04:02:39 UTC
>> 6. Execute the SELECT pg_stop_backup() command to stop the backup.
>>
>> I have a few questions about this.
>>
>> 1. I was not able to find the file 00000001000000000000000C in the WAL
>> archive location after taking the base backup. Is that normal ? The
>> file 00000001000000000000000B exists and is the last WAL file. The
>> server was stopped after taking the base backup
>
> This is normal, although arguably not desirable. =A0In PostgreSQL 8.4, th=
is was
> changed so that pg_stop_backup() waits until the ...000C file in your cas=
e is
> in the archive. =A0So that is what you want.
>
>> 2. When I do a restore, postgres will have a look at the
>> restore_command from my recover.conf to look for all WAL files from
>> 00000001000000000000000B right ? Is it ok if it does not find
>> 00000001000000000000000C ?
>
> Yes. =A0Recovery will stop when it runs out of files to restore.
>
>> 3. Lets assume that after taking the base backup the WAL files with
>> the suffix 0C 0D 0E etc were generated. What happens if the entire
>> hard disk crashes but I still have the data directory archived along
>> with the WAL file 00000001000000000000000B ? It means that all the
>> data that was in the DB till the base backup can be recovered but any
>> subsequent data that was updated / inserted will be lost. Am I right
>> when I say that ?
>
> In this scenario you would have to restore your *previous* base backup,
> because the current base backup wouldn't be usuable, as it requires that =
the
> ...000C file be present.
>
> It's always a good idea to have two base backups around, if you can affor=
d the
> space, in case something goes wrong during or around the time you take the
> next base backup.
>
>

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

What version of Slony-I is "ok" to use with version 8.4.0, I am getting thi=
s error when trying to make against 8.4.0 on RHEL4:

[postgres [at] linux1558 slony1-1.2.15]$ make
make[1]: Entering directory `/home/postgres/slony1-1.2.15/src'
make[2]: Entering directory `/home/postgres/slony1-1.2.15/src/xxid'
gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -I../.. =
-I/usr/local/pgsql/include/ -I/usr/local/pgsql/include/server/ -c -o xxid.=
o xxid.c
xxid.c: In function `_Slony_I_getMinXid':
xxid.c:236: error: `SerializableSnapshot' undeclared (first use in this fun=
ction)
xxid.c:236: error: (Each undeclared identifier is reported only once
xxid.c:236: error: for each function it appears in.)
xxid.c: In function `_Slony_I_getMaxXid':
xxid.c:249: error: `SerializableSnapshot' undeclared (first use in this fun=
ction)
make[2]: *** [xxid.o] Error 1
make[2]: Leaving directory `/home/postgres/slony1-1.2.15/src/xxid'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/home/postgres/slony1-1.2.15/src'
make: *** [all] Error 2


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

You need to latest release candidate that was announced last week.
The expected actual release is to be this week. I am waiting for
that.

Regards,
Ken

On Mon, Aug 03, 2009 at 04:02:16PM -0500, Plugge, Joe R. wrote:
> What version of Slony-I is "ok" to use with version 8.4.0, I am getting this error when trying to make against 8.4.0 on RHEL4:
>
> [postgres [at] linux1558 slony1-1.2.15]$ make
> make[1]: Entering directory `/home/postgres/slony1-1.2.15/src'
> make[2]: Entering directory `/home/postgres/slony1-1.2.15/src/xxid'
> gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -I../.. -I/usr/local/pgsql/include/ -I/usr/local/pgsql/include/server/ -c -o xxid.o xxid.c
> xxid.c: In function `_Slony_I_getMinXid':
> xxid.c:236: error: `SerializableSnapshot' undeclared (first use in this function)
> xxid.c:236: error: (Each undeclared identifier is reported only once
> xxid.c:236: error: for each function it appears in.)
> xxid.c: In function `_Slony_I_getMaxXid':
> xxid.c:249: error: `SerializableSnapshot' undeclared (first use in this function)
> make[2]: *** [xxid.o] Error 1
> make[2]: Leaving directory `/home/postgres/slony1-1.2.15/src/xxid'
> make[1]: *** [all] Error 2
> make[1]: Leaving directory `/home/postgres/slony1-1.2.15/src'
> make: *** [all] Error 2
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

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

slony1-1.2.17-rc ...

Thank you ....

-----Original Message-----
From: Kenneth Marshall [mailto:ktm [at] rice.edu]
Sent: Monday, August 03, 2009 4:27 PM
To: Plugge, Joe R.
Cc: pgsql-admin [at] postgresql.org
Subject: Re: [ADMIN] Slony-I Version with Postgres 8.4.0

You need to latest release candidate that was announced last week.
The expected actual release is to be this week. I am waiting for
that.

Regards,
Ken

On Mon, Aug 03, 2009 at 04:02:16PM -0500, Plugge, Joe R. wrote:
> What version of Slony-I is "ok" to use with version 8.4.0, I am getting t=
his error when trying to make against 8.4.0 on RHEL4:
>
> [postgres [at] linux1558 slony1-1.2.15]$ make
> make[1]: Entering directory `/home/postgres/slony1-1.2.15/src'
> make[2]: Entering directory `/home/postgres/slony1-1.2.15/src/xxid'
> gcc -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -I../.=
.. -I/usr/local/pgsql/include/ -I/usr/local/pgsql/include/server/ -c -o xxi=
d.o xxid.c
> xxid.c: In function `_Slony_I_getMinXid':
> xxid.c:236: error: `SerializableSnapshot' undeclared (first use in this f=
unction)
> xxid.c:236: error: (Each undeclared identifier is reported only once
> xxid.c:236: error: for each function it appears in.)
> xxid.c: In function `_Slony_I_getMaxXid':
> xxid.c:249: error: `SerializableSnapshot' undeclared (first use in this f=
unction)
> make[2]: *** [xxid.o] Error 1
> make[2]: Leaving directory `/home/postgres/slony1-1.2.15/src/xxid'
> make[1]: *** [all] Error 2
> make[1]: Leaving directory `/home/postgres/slony1-1.2.15/src'
> make: *** [all] Error 2
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

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

--=-LhqWDuweiZ+bSd02fVF9
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, 2009-08-03 at 16:02 -0500, Plugge, Joe R. wrote:
> What version of Slony-I is "ok" to use with version 8.4.0, I am
> getting this error when trying to make against 8.4.0 on RHEL4:

I just tested building 1.2.17rc on RHEL4 -- it worked fine.
--
Devrim G=C3=9CND=C3=9CZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

--=-LhqWDuweiZ+bSd02fVF9
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkp3XkAACgkQtl86P3SPfQ643wCfZ1Vqp/TkPGLCkXMwonQo 9rxW
33oAn1mH+6hUyqp2aTItdvqNCfGpBYrb
=gMqy
-----END PGP SIGNATURE-----

--=-LhqWDuweiZ+bSd02fVF9--

VGhhbmtzLA0KDQpJIGVuZGVkIHVwIGdvaW5nIHdpdGggU2xvbnktSSBWZXJz
aW9uIDIuMC4zLXJjLCBhcyBmb3IgbWUsIDEuMi4xN3JjIGJ1aWx0IGFuZCBp
bnN0YWxsZWQgZmluZSwgYnV0IHdhcyBoYXZpbmcgcHJvYmxlbXMgd2l0aCB0
aGUgaW5pdGlhbCBzeW5jaCBvbiB0aGUgZmlyc3QgdGFibGUgb2YgdGhlIHNl
dCAoc2ltcGxlIHRhYmxlLCBwcmltYXJ5IGtleSBhbmQgY29tbW9uIGRhdGEg
dHlwZXMpLg0KDQpIb3BlZnVsbHkgMi4wLjMgR0Egd2lsbCBiZSBvdXQgc29v
bi4NCg0KDQpKb2UNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZy
b206IERldnJpbSBHw5xORMOcWiBbbWFpbHRvOmRldnJpbUBndW5kdXoub3Jn
XSANClNlbnQ6IE1vbmRheSwgQXVndXN0IDAzLCAyMDA5IDU6MDIgUE0NClRv
OiBQbHVnZ2UsIEpvZSBSLg0KQ2M6IHBnc3FsLWFkbWluQHBvc3RncmVzcWwu
b3JnDQpTdWJqZWN0OiBSZTogW0FETUlOXSBTbG9ueS1JIFZlcnNpb24gd2l0
aCBQb3N0Z3JlcyA4LjQuMA0KDQpPbiBNb24sIDIwMDktMDgtMDMgYXQgMTY6
MDIgLTA1MDAsIFBsdWdnZSwgSm9lIFIuIHdyb3RlOg0KPiBXaGF0IHZlcnNp
b24gb2YgU2xvbnktSSBpcyAib2siIHRvIHVzZSB3aXRoIHZlcnNpb24gOC40
LjAsIEkgYW0gDQo+IGdldHRpbmcgdGhpcyBlcnJvciB3aGVuIHRyeWluZyB0
byBtYWtlIGFnYWluc3QgOC40LjAgb24gUkhFTDQ6DQoNCkkganVzdCB0ZXN0
ZWQgYnVpbGRpbmcgMS4yLjE3cmMgb24gUkhFTDQgLS0gaXQgd29ya2VkIGZp
bmUuDQotLQ0KRGV2cmltIEfDnE5Ew5xaLCBSSENFDQpDb21tYW5kIFByb21w
dCAtIGh0dHA6Ly93d3cuQ29tbWFuZFByb21wdC5jb20gZGV2cmltfmd1bmR1
ei5vcmcsIGRldnJpbX5Qb3N0Z3JlU1FMLm9yZywgZGV2cmltLmd1bmR1en5s
aW51eC5vcmcudHINCiAgICAgICAgICAgICAgICAgICBodHRwOi8vd3d3Lmd1
bmR1ei5vcmcNCgotLSAKU2VudCB2aWEgcGdzcWwtYWRtaW4gbWFpbGluZyBs
aXN0IChwZ3NxbC1hZG1pbkBwb3N0Z3Jlc3FsLm9yZykKVG8gbWFrZSBjaGFu
Z2VzIHRvIHlvdXIgc3Vic2NyaXB0aW9uOgpodHRwOi8vd3d3LnBvc3RncmVz
cWwub3JnL21haWxwcmVmL3Bnc3FsLWFkbWluCg==

Slony-I version 2.0.3RC Error when running the subscription script:

Contents of subscribe.sh

#!/bin/ksh

/usr/local/pgsql/bin/slonik <<_EOF_
cluster name=3Dmolly;
node 1 admin conninfo=3D'dbname=3Dmolly host=3Dlinux2139 port=3D5432 user=
=3Dslony';
node 2 admin conninfo=3D'dbname=3Dmolly host=3Dlinux1558 port=3D5432 user=
=3Dslony';

subscribe set (id=3D1, provider=3D1, receiver=3D2, forward=3Dno);

_EOF_

Anything to worry about .. it appears to be replicating?

[slony [at] linux1558 ~]$ ./subscribe.sh
<stdin>:5: NOTICE: subscribe set: omit_copy=3Df
<stdin>:5: NOTICE: subscribe set: omit_copy=3Df
CONTEXT: SQL statement "SELECT "_holly".subscribeSet_int( $1 , $2 , $3 =
, $4 , $5 )"
PL/pgSQL function "subscribeset" line 68 at PERFORM


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