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