pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with PITR in PostgreSQL 8.4
6+ messages / 3 participants
[nested] [flat]
* Help with PITR in PostgreSQL 8.4
@ 2013-07-15 22:06 Nestor A. Diaz <nestor@tiendalinux.com>
0 siblings, 1 reply; 6+ messages in thread
From: Nestor A. Diaz @ 2013-07-15 22:06 UTC (permalink / raw)
To: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with PITR in PostgreSQL 8.4
@ 2013-07-17 15:44 Nestor A. Diaz <nestor@tiendalinux.com>
parent: Nestor A. Diaz <nestor@tiendalinux.com>
0 siblings, 1 reply; 6+ messages in thread
From: Nestor A. Diaz @ 2013-07-17 15:44 UTC (permalink / raw)
To: chiru r <chirupg@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with PITR in PostgreSQL 8.4
@ 2013-07-17 15:54 Gilberto Castillo <gilberto.castillo@etecsa.cu>
parent: Nestor A. Diaz <nestor@tiendalinux.com>
0 siblings, 1 reply; 6+ messages in thread
From: Gilberto Castillo @ 2013-07-17 15:54 UTC (permalink / raw)
To: Nestor A. Diaz <nestor@tiendalinux.com>; +Cc: chiru r <chirupg@gmail.com>; pgsql-admin
> 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;
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with PITR in PostgreSQL 8.4
@ 2013-07-17 16:40 Nestor A. Diaz <nestor@tiendalinux.com>
parent: Gilberto Castillo <gilberto.castillo@etecsa.cu>
0 siblings, 2 replies; 6+ messages in thread
From: Nestor A. Diaz @ 2013-07-17 16:40 UTC (permalink / raw)
To: gilberto.castillo@etecsa.cu; +Cc: pgsql-admin
> 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with PITR in PostgreSQL 8.4
@ 2013-07-17 17:26 Gilberto Castillo <gilberto.castillo@etecsa.cu>
parent: Nestor A. Diaz <nestor@tiendalinux.com>
1 sibling, 0 replies; 6+ messages in thread
From: Gilberto Castillo @ 2013-07-17 17:26 UTC (permalink / raw)
To: Nestor A. Diaz <nestor@tiendalinux.com>; +Cc: gilberto.castillo@etecsa.cu; pgsql-admin
>
>> 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;
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with PITR in PostgreSQL 8.4
@ 2013-07-18 13:20 CS DBA <cs_dba@consistentstate.com>
parent: Nestor A. Diaz <nestor@tiendalinux.com>
1 sibling, 0 replies; 6+ messages in thread
From: CS DBA @ 2013-07-18 13:20 UTC (permalink / raw)
To: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-07-18 13:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-15 22:06 Help with PITR in PostgreSQL 8.4 Nestor A. Diaz <nestor@tiendalinux.com>
2013-07-17 15:44 ` Nestor A. Diaz <nestor@tiendalinux.com>
2013-07-17 15:54 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2013-07-17 16:40 ` Nestor A. Diaz <nestor@tiendalinux.com>
2013-07-17 17:26 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2013-07-18 13:20 ` CS DBA <cs_dba@consistentstate.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox