Re: Practical maximums (was Re: PostgreSQL theoretical

On Tue, 2006-08-08 at 13:48 -0500, Ron Johnson wrote:
> >> I'm imagining something similar to the following:
> >> mydb=# select pg_start_backup('mybackup');
> >>
> >> $ tar zcf - /path/to/pgdata | mux.script /dev/st0 /dev/st1 /dev/st2
> >> $ tar zcf - /path/to/tablespace1 | mux.script /dev/st3 /dev/st4 /dev/st5
> >> $ tar zcf - /path/to/tablespace2 | mux.script /dev/st6 /dev/st7 /dev/st8
> >>
> >
> > Oops, let me clarify. Those tar commands are independent and can be run
> > in parallel, which was the point I was trying to make. So, just run them
> > in the background in whatever way is most convenient.
>
> Open database or closed database?

See http://www.postgresql.org/docs/8.1/static/backup-online.html

In section 23.3.2, it suggests that you _don't_ need the same connection
to do pg_start_backup('mybackup') as to do pg_stop_backup().

So, it appears you really need no database connections at all open
during the process, but clients can freely connect if you want with no
interruption. This backup procedure will not affect your applications or
stop your database.

All you need to do is:
(1) connect, issue pg_start_backup('mybackup'), and disconnect
(2) back up the data dir + all tablespaces. You can do this in parallel
if you want. You don't have to make a filesystem snapshot to do this,
the restoration will fix any internal inconsistencies as long as you
have archived the WAL files according to the above docs. So long as the
backup makes it onto permanent media, and so have the WAL files, it
should be fine.
(3) connect, issue pg_stop_backup(), and disconnect

I recommend reading the above link carefully and doing a few tests to
make sure it works as you expect. But you certainly can do online
backups (hot backups) with PITR, and the restoration as well. With PITR,
the large portion of the data is moved using standard filesystem
interaction, so you can do it in parallel with no problems.

Hope this helps,
Jeff Davis