Обсуждение: trigger run of archive_command?
Hi, I want to do online backup saving the WAL files. So I set the database into backup mode, make a backup, and then set the datbase to normal mode with pg_stop_backup(). To make the backup complete, I need to backup the actual WAL file too, but this one is still open when I run pg_stop_backup(), so I want to trigger the archive_command to be sure that I don't miss anything. In postgresql.conf I have defined an archive_command in postgresql.conf to tell postgres what to do when the WAL file reaches a maximum filesize. I use postgresql 8.1.4, so I only can make the archive_command depend on the size of the WAL files, right now the maximum size of a WAL file is defined to 16mb. As a workaround I could create a dummy table, insert 16MB to make sure the WAL files get rotated, and then I can backup the last WAL file. But my hope is that there is an easier way, e.g. do I can use a trigger to force the archive_command or any other mechanism? kind regards Sebastian
Hi, > this one is still open when I run pg_stop_backup(), so I want to trigger the > archive_command to be sure that I don't miss anything. I guess what you want is pg_switch_xlog() (see http://www.postgresql.org/docs/8.2/static/functions-admin.html) which became available with PG 8.2 ... Thomas
Вложения
Hi, Thomas Karcher <thkarcher@gmx.de> wrote: > Hi, > > > this one is still open when I run pg_stop_backup(), so I want to trigger the > > archive_command to be sure that I don't miss anything. > > I guess what you want is pg_switch_xlog() (see > http://www.postgresql.org/docs/8.2/static/functions-admin.html) this is exactly what I am looking for > which became available with PG 8.2 ... unfortunately right now I am stuck to PG 8.1.4. I hope I can manage to upgrade without too much hassle, it seems meanwhile I have to use my workaround. thanks a lot Sebastian
Hello, On Mon, 14 Jan 2008 17:39:34 +0100 Sebastian Reitenbach wrote: > Thomas Karcher <thkarcher@gmx.de> wrote: > > > > > this one is still open when I run pg_stop_backup(), so I want to trigger > > > the archive_command to be sure that I don't miss anything. > > > > I guess what you want is pg_switch_xlog() (see > > http://www.postgresql.org/docs/8.2/static/functions-admin.html) > this is exactly what I am looking for > > which became available with PG 8.2 ... > unfortunately right now I am stuck to PG 8.1.4. > I hope I can manage to upgrade without too much hassle, it seems meanwhile I > have to use my workaround. a very rude hack is to produce enough changes so that PG will rotate the logfile anyway: CHECKPOINT; CREATE TABLE xlog_switch AS SELECT '0123456789ABCDE'::VARCHAR FROM generate_series(1,300000); DROP TABLE xlog_switch; This is a lot of overhead, but it does the job. Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group
Hi, Andreas 'ads' Scherbaum <adsmail@wars-nicht.de> wrote: > > Hello, > > On Mon, 14 Jan 2008 17:39:34 +0100 Sebastian Reitenbach wrote: > > > Thomas Karcher <thkarcher@gmx.de> wrote: > > > > > > > this one is still open when I run pg_stop_backup(), so I want to trigger > > > > the archive_command to be sure that I don't miss anything. > > > > > > I guess what you want is pg_switch_xlog() (see > > > http://www.postgresql.org/docs/8.2/static/functions-admin.html) > > this is exactly what I am looking for > > > which became available with PG 8.2 ... > > unfortunately right now I am stuck to PG 8.1.4. > > I hope I can manage to upgrade without too much hassle, it seems meanwhile I > > have to use my workaround. > > a very rude hack is to produce enough changes so that PG will rotate > the logfile anyway: > > CHECKPOINT; > CREATE TABLE xlog_switch AS SELECT > '0123456789ABCDE'::VARCHAR FROM generate_series(1,300000); > DROP TABLE xlog_switch; > > This is a lot of overhead, but it does the job. until we upgrade, we do it in a very similar way. thanks Sebastian