Обсуждение: Help with PITR in PostgreSQL 8.4

Hello People,

I need some clarification on Continuous Archiving and Point-In-Time
Recovery (PITR).

I have been able to replicate a postgresql 8.4 database from one machine
to another using rsync and wal files.

I do this as follows (Using debian gnu/linux squeeze):

In *master* i have the following options under postgresql.conf:

archive_mode = on               # allows archiving to be done
archive_command = 'test ! -f
/var/lib/postgresql/8.4/main/backup_in_progress || cp -i %p
/var/lib/postgresql/8.4/main/wal-archive/%f'   # command to use to
archive a logfile segment

I start the backup on *master*:

touch /var/lib/postgresql/8.4/main/backup_in_progress
DATE=$(date +%Y%m%d-%H%M%S)
psql  --cluster 8.4/main -c "SELECT pg_start_backup('backup_${DATE}');"

Then rsync on *standby*.

    rsync -avz --partial --progress --delete --rsh=ssh \
        --exclude 'lost+found' --exclude 'postmaster.pid' --exclude
'postmaster.opts' --exclude 'wal-archive' \
        postgres@$SRC_HOST:/var/lib/postgresql/8.4/main/
/var/lib/postgresql/8.4/main/

Plus all the tablespaces which are located on different directories and
rsynced with same options as above (except the exclude)

Then stop backup on *main*.

psql  --cluster 8.4/main -c "SELECT pg_stop_backup();"

And transfer all the WAL files from *master* to *standby*:

rsync -avz --partial --progress --rsh=ssh \
     --exclude 'lost+found' \
     postgres@$SRC_HOST:/var/lib/postgresql/8.4/main/wal-archive/
/var/lib/postgresql/8.4/main/wal-archive/

In *standby* i have in recovery.conf:

restore_command = '/usr/lib/postgresql/8.4/bin/pg_standby -l -d -s 2 -t
/var/run/postgresql/8.4-main.trigger
/var/lib/postgresql/8.4/main/wal-archive %f %p %r
2>>/var/log/postgresql/postgresql-8.4-main-standby.log'
recovery_end_command = 'rm -f /var/run/postgresql/8.4-main.trigger'

Then start the database on *standby* and everything worked fine, I
replicated the main database from one machine to another and later on
the *standby* machine I process the logical backups which off course
takes a lot of time.

But there is one big issue:

The database is about: 300 Gb of size, so rsyncing the data from one
machine to another takes more than 2 hours, I don't know if I can change
some rsync options in order to improve since this is too much time.

Another thing is to improve the script and not having to rsync the
database and just transferring the wal files and apply them every day,
this is what I am trying to do without luck.

So the question is: Can I have some continues wal archiving and then
transfer all WAL files to *standby* everyday, recover the database and
backup all data without having to rsync the data every time i make a
backup ?

For now i have to stick with 8.4 .

Thank you very much for your time and support.

Slds.

--
Typed on my key64.org keyboard

Nestor A Diaz

On 07/17/2013 03:05 AM, chiru r wrote:

Hi Nestor,

So the question is: Can I have some continues wal archiving and then
transfer all WAL files to *standby* everyday, recover the database and
backup all data without having to rsync the data every time i make a
backup ?

Yes,You can do as you mentioned.

1) set the below parameters in Postgresql.conf file on Primary server.

  archive_mode=on

archive_command='cp %p  /opt/backup/wal_archives/%f'

2)apply these wall files to standby server hourly/daily basis as your requirement.

   configure the recovery.conf file.

Hi Chiru, I have done archiving on primary server, and recovery on standby server, howerver, after the first recovery I am unable to recover more wal files on standby server

Can it be possible in 8.4 ? or just in 9.1 ?

This is the recovery I use:

<< EOF

restore_command = '/usr/lib/postgresql/8.4/bin/pg_standby -l -d -s 2 -t /var/run/postgresql/8.4-main.trigger /var/lib/postgresql/8.4/main/wal-archive %f %p %r 2>>/var/log/postgresql/postgresql-8.4-main-standby.log'
recovery_end_command = 'rm -f /var/run/postgresql/8.4-main.trigger'

EOF

Slds.

  
Best Regards,

Chiru

-- 
Typed on my key64.org keyboard

Nestor A Diaz

> Hi Chiru, I have done archiving on primary server, and recovery on
> standby server, howerver, after the first recovery I am unable to
> recover more wal files on standby server
>
> Can it be possible in 8.4 ? or just in 9.1 ?
>
> This is the recovery I use:
>
> << EOF
>
> restore_command = '/usr/lib/postgresql/8.4/bin/pg_standby -l -d -s 2 -t
> /var/run/postgresql/8.4-main.trigger
> /var/lib/postgresql/8.4/main/wal-archive %f %p %r
> 2>>/var/log/postgresql/postgresql-8.4-main-standby.log'
> recovery_end_command = 'rm -f /var/run/postgresql/8.4-main.trigger'
>
> EOF
>
>
Hello Nestor

I use its:

standby_mode = 'on'
primary_conninfo = 'host=X.X.X.X port=5432 user=XXXX password=YYYYYTT'
trigger_file = '/var/pgsql/data/pg_failover_trigger'
restore_command = 'cp /wal/%f /var/lib/postgresql/8.4/main/"%p"'


Saludos,
Gilberto Castillo
La Habana, Cuba
---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>

> Hello Nestor
>
> I use its:
>
> standby_mode = 'on'
> primary_conninfo = 'host=X.X.X.X port=5432 user=XXXX password=YYYYYTT'
> trigger_file = '/var/pgsql/data/pg_failover_trigger'
> restore_command = 'cp /wal/%f /var/lib/postgresql/8.4/main/"%p"'
Ok, but those instructions are for 9.1 not for 8.4, I would like to be
sure if there is any way to perform countinus archiving then recovery
then backup and loop over this every day whithout having to rsync every
time I recover the standby database (8.4)

Slds


--
Typed on my key64.org keyboard

Nestor A. Diaz
T: +57-1-485-3020,211
M: +57-316-227-3593
nestor@tiendalinux.com
www.tiendalinux.com

>
>> Hello Nestor
>>
>> I use its:
>>
>> standby_mode = 'on'
>> primary_conninfo = 'host=X.X.X.X port=5432 user=XXXX password=YYYYYTT'
>> trigger_file = '/var/pgsql/data/pg_failover_trigger'
>> restore_command = 'cp /wal/%f /var/lib/postgresql/8.4/main/"%p"'
> Ok, but those instructions are for 9.1 not for 8.4, I would like to be
> sure if there is any way to perform countinus archiving then recovery
> then backup and loop over this every day whithout having to rsync every
> time I recover the standby database (8.4)
>

See you:
http://www.postgresql.org.es/node/238
http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html


Saludos,
Gilberto Castillo
La Habana, Cuba
---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com>, <http://www.viruslist.com>

On 7/17/13 10:40 AM, Nestor A. Diaz wrote:
>> Hello Nestor
>>
>> I use its:
>>
>> standby_mode = 'on'
>> primary_conninfo = 'host=X.X.X.X port=5432 user=XXXX password=YYYYYTT'
>> trigger_file = '/var/pgsql/data/pg_failover_trigger'
>> restore_command = 'cp /wal/%f /var/lib/postgresql/8.4/main/"%p"'
> Ok, but those instructions are for 9.1 not for 8.4, I would like to be
> sure if there is any way to perform countinus archiving then recovery
> then backup and loop over this every day whithout having to rsync every
> time I recover the standby database (8.4)
>
> Slds
>
>
AFAIK The only way to do this in 8.4 is to make a copy of the standby
and bring the copy online.  We do it to run backups off the standby like
this:

1) take the standby down (pg_ctl -m fast stop)
2) rsync the standby to the standby-copy
3) restart the standby (it will return to recovery mode)
4) bring the standby copy online (remove it's recovery.conf and start it up)
5) run the backups on the copy
6) shutdown the copy (but leave it in place so the next rsync only has
to copy changed data)
7) rinse and repeat