Re: Stuck trying to backup large database - best practice?
От | Antony Gelberg |
---|---|
Тема | Re: Stuck trying to backup large database - best practice? |
Дата | |
Msg-id | CADbCqvG-=x65gh_=wcd_9JhVv+4Qur9_rerDyjp5SQOiBNJxuA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Stuck trying to backup large database - best practice? (Adrian Klaver <adrian.klaver@aklaver.com>) |
Ответы |
Re: Stuck trying to backup large database - best practice?
(Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Stuck trying to backup large database - best practice? (Bosco Rama <postgres@boscorama.com>) |
Список | pgsql-general |
On Mon, Jan 12, 2015 at 6:23 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 01/12/2015 08:10 AM, Antony Gelberg wrote: >> >> <some snippage> >> >> On Mon, Jan 12, 2015 at 5:31 PM, Adrian Klaver >> <adrian.klaver@aklaver.com> wrote: >>> >>> >>> On 01/12/2015 07:20 AM, Antony Gelberg wrote: >>>> >>>> >>>> pg_basebackup: could not get transaction log end position from server: >>>> ERROR: requested WAL segment 0000000400002B9F000000B4 has already been >>>> removed >>>> >>>> This attempted backup reached 430GB before failing. >>> >>> >>> >>> It fails because the WAL file it needs has been removed from under it. >>> >> >> Okay. We simply understood that it took too long. Clearly we have a >> lot to learn about WAL and its intricacies. > > > See here: > > http://www.postgresql.org/docs/9.4/interactive/wal.html > Of course we read the docs before asking here, but really learning about a subject comes with time. :) >> >>>> We were advised on IRC to try -Xs, but that only works with a plain >>>> (uncompressed) backup, and as you'll note from above, we don't have >>>> enough disk space for this. >>>> >>>> Is there anything else we can do apart from get a bigger disk (not >>>> trivial at the moment)? Any best practice? >>> >>> >>> What is the purpose of the backup? >>> >>> In other words do really want the data and the WALs together or do you >>> just want the data? >> >> >> No, we just want to be able to restore our data at a later point. (As >> as secondary point, it's not that clear to me why it would be useful >> to have both, I'd be interested for some insight.) > > > Seems you may be better served by pg_dump: > > http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html > > pg_basebackup has additional features which in your case are creating > issues. pg_dump on the other hand is pretty much a straight forward data > dump and if you use -Fc you get compression. So I should clarify - we want to be able to get back to the same point as we would once the WAL was applied. If we were to use pg_dump, would we lose out in any way? Appreciate insight as to how pg_basebackup is scuppering things. > Something I failed to ask in my previous post, how are you determining the > size of the database? It's a managed server - the hosting company told us it was 1.8TB. I just ran the query at http://stackoverflow.com/questions/2596624/how-do-you-find-the-disk-size-of-a-postgres-postgresql-table-and-its-indexes, and I don't have the total, but I'd say the actual table data is less, nearer 1TB at a quick glance. > In addition are you talking about a single database or the Postgres database > cluster? > We only have one database in the cluster, so it's the same thing. Antony
В списке pgsql-general по дате отправления:
Предыдущее
От: Adrian KlaverДата:
Сообщение: Re: Stuck trying to backup large database - best practice?
Следующее
От: Adrian KlaverДата:
Сообщение: Re: Stuck trying to backup large database - best practice?