Обсуждение: General queries regarding backup

Поиск
Список
Период
Сортировка

General queries regarding backup

От
Deepak Bala
Дата:
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.

Re: General queries regarding backup

От
Deepak Bala
Дата:
Anyone ?

On Wed, Jul 22, 2009 at 10:55 AM, Deepak Bala<deepak.bala.x@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.
>

Re: General queries regarding backup

От
Peter Eisentraut
Дата:
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.

Re: General queries regarding backup

От
Deepak Bala
Дата:
Thanks for your replies Peter.

On Wed, Jul 29, 2009 at 12:45 PM, Peter Eisentraut<peter_e@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.  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.
>
>

Slony-I Version with Postgres 8.4.0

От
"Plugge, Joe R."
Дата:
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@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

Re: Slony-I Version with Postgres 8.4.0

От
Kenneth Marshall
Дата:
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
onRHEL4: 
>
> [postgres@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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

Re: Slony-I Version with Postgres 8.4.0

От
"Plugge, Joe R."
Дата:
slony1-1.2.17-rc ...

Thank you ....

-----Original Message-----
From: Kenneth Marshall [mailto:ktm@rice.edu]
Sent: Monday, August 03, 2009 4:27 PM
To: Plugge, Joe R.
Cc: pgsql-admin@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 this error when trying to make against 8.4.0
onRHEL4: 
>
> [postgres@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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

Re: Slony-I Version with Postgres 8.4.0

От
Devrim GÜNDÜZ
Дата:
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ÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org
Вложения

Re: Slony-I Version with Postgres 8.4.0

От
"Plugge, Joe R."
Дата:
Thanks,

I ended up going with Slony-I Version 2.0.3-rc, as for me, 1.2.17rc built and installed fine, but was having problems
withthe initial synch on the first table of the set (simple table, primary key and common data types).
 

Hopefully 2.0.3 GA will be out soon.


Joe

-----Original Message-----
From: Devrim GÜNDÜZ [mailto:devrim@gunduz.org] 
Sent: Monday, August 03, 2009 5:02 PM
To: Plugge, Joe R.
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Slony-I Version with Postgres 8.4.0

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ÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org

Slony-I Version 2.0.3RC with Postgres 8.4.0

От
"Plugge, Joe R."
Дата:
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=molly;
node 1 admin conninfo='dbname=molly host=linux2139 port=5432 user=slony';
node 2 admin conninfo='dbname=molly host=linux1558 port=5432 user=slony';

subscribe set (id=1, provider=1, receiver=2, forward=no);

_EOF_

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

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