On 01/12/2015 08:40 AM, Antony Gelberg wrote:
On Mon, Jan 12, 2015 at 6:23 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx> 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?
pg_dump does not save WALs, so it would not work for that purpose.
Appreciate insight as to how
pg_basebackup is scuppering things.
From original post it is not entirely clear whether you are using the
-X or -x options. The command you show does not have them, but you
mention -Xs. In any case it seems wal_keep_segments will need to be
bumped up to keep WAL segments around that are being recycled during the
backup process. How much will depend on a determination of fast Postgres
is using/recycling log segments? Looking at the turnover in the pg_xlog
directory would be a start.
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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
