Backup hot-standby database.

From: Stephen Rees <srees(at)pandora(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Backup hot-standby database.
Date: 2011-03-15 21:50:54
Message-ID: 0515F2BB-0522-449C-848B-4E750F4D91BB@pandora.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Using PostgreSQL 9.0.x

I cannot use pg_dump to generate a backup of a database on a hot-
standby server, because it is, by definition, read-only. However, it
seems that I can use COPY TO within a serializable transaction to
create a consistent set of data file(s). For example,

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
COPY t1 TO '/tmp/t1';
COPY t2 TO '/tmp/t2';

... etc ...

COPY t<n> TO '/tmp/t<n>';
COMMIT TRANSACTION;

I can then use pg_dump to export the corresponding database schema
from the master DBMS.

Is this going to scale to a multi-GB database, where it will take
hours to export the data from all of the tables, or are there
scalability issues of which I should be aware?

Thanks in advance,

- SteveR

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2011-03-15 21:59:44 Re: Backup hot-standby database.
Previous Message Bruce Momjian 2011-03-15 20:38:00 Re: log timestamp since daylight saving time