On Tue, Mar 15, 2011 at 5:50 PM, Stephen Rees <srees@xxxxxxxxxxx> wrote: > 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. That really makes no sense :-) You can use pg_dump on a read-only slave, but I think the issue that people tend to run into is that the pg_dump operations get canceled out by incoming changes before it can finish. You can of course modify the configs to work around this somewhat, but eventually it becomes a problem. > 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? > Well, basically that's in in a nutshell. You have to stop replay while you are doing the dumps like this, so eventually that delay becomes unbearable for most people (especially on the order of hours). There are several ways to work around this... you can use filesystem snapshots to make copies and dump from there; great if you have the option. If you don't you might want to look into omnipitr, it can create filesystem level backups from a slave (not the same as a logical export, but it might do). Robert Treat play: xzilla.net work: omniti.com hiring: l42.org/lg -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
