Backups / replication

Поиск
Список
Период
Сортировка
От Oliver Kohll - Mailing Lists
Тема Backups / replication
Дата
Msg-id B5EC50DB-D3DB-4306-B340-2AF9D754ABC7@gtwm.co.uk
обсуждение исходный текст
Ответы Re: Backups / replication  (Alan Hodgson <ahodgson@simkin.ca>)
Re: Backups / replication  (Adrian von Bidder <avbidder@fortytwo.ch>)
Список pgsql-general
Hello,

I'm interested in using WAL shipping / replication for backup purposes but have no interest in failover. Currently my
situationis: 

I have two servers, live and backup, which are in different cities. The backup server is also a test/development
machine.

Backups of my most important database are made hourly with pg_dump, excluding some larger tables with non-critical
loggingdata. Even so, as the database grows, backups are taking longer and it looks as though they may start to impact
performance.A full backup is made nightly and transferred to the backup machine, along with all of the day's hourly
backups.

I'm looking into using replication by WAL shipping - after all, there's no use to backing up data which hasn't changed
sincelast time - only a small percentage of records are created/updated. However, I need 

a) to be able to restore to a point in time easily, which I can do to within an hour at the moment by restoring the
correctdump. Sometimes users ask for a restore having accidentally updated/deleted records. 
b) to carry on running a test server database, that means one that's read and writeable.

I obviously can't use a replication slave as a read/write test server at the same time. At the moment I've thought of a
coupleof options, I don't know if either are possible - I have a bit of a hazy idea of WAL replication. 

1) Continuously ship the WAL records to somewhere on the test server unknown to Postgres but run the test machine as a
normaldatabase completely separately. If a backup is needed, delete the test database, restore to the last full backup
(afilesystem backup?) and copy all WAL records into Postgres' directory so it can see them. Start it up configured to
replaythem, up to a certain time. 

2) Run two instances of Postgres on the test/backup server on different ports, one configured as a replication slave,
onenormal. I'm not sure if this is possible with the RPM builds I'm using. 

Are either of those two likely? Any other suggestions? Another question is will the replication coming in v9.0 change
thingsand would it be worth holding off until then? In particular Command Prompt's PITR tools look useful for restoring
toa particular point in time, will these still work or will there be equivalents? 

Regards
Oliver Kohll

oliver@agilebase.co.uk / +44(0)7814 828608 / skype:okohll




В списке pgsql-general по дате отправления:

Предыдущее
От: Vick Khera
Дата:
Сообщение: Re: Does enterprisedb.com down?
Следующее
От: Vick Khera
Дата:
Сообщение: Re: IMMUTABLE columns in tables?