pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPITR compatible backup with exclusion
5+ messages / 4 participants
[nested] [flat]
* PITR compatible backup with exclusion
@ 2015-01-26 12:17 Martin Bruset Solberg <martin.bruset.solberg@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Martin Bruset Solberg @ 2015-01-26 12:17 UTC (permalink / raw)
To: pgsql-admin
I am faced with a scenario of backing up a database with one large
(partitioned) table, and several smaller tables. The large table is so big
that backing it up is proving problematic both in terms of storage and time
spent backing up and restoring. The backup of the table itself is also
reduntant, as the table data can be recreated by importing from the source
files. This data is not mission critical for the application, so a speedy
recovery is not urgent.
The data from the smaller tables, however, is critical. The combined size
of the rest of the database is not a problem neither in terms of storage or
backup/restore speed, so a logical backup excluding the large table/schema
works fine. But logical backup is not sufficiant, as I need to be able to
do PITR in case of disaster.
So my question is, what are my options when I want to make a PITR
compatible backup, and need to exclude a table/schema/tablespace of the
database? Any suggestions welcome...
Martin Bruset Solberg
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PITR compatible backup with exclusion
@ 2015-01-26 20:10 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: Martin Bruset Solberg <martin.bruset.solberg@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Rosser Schwarz @ 2015-01-26 20:10 UTC (permalink / raw)
To: Martin Bruset Solberg <martin.bruset.solberg@gmail.com>; +Cc: pgsql-admin
On Mon, Jan 26, 2015 at 4:17 AM, Martin Bruset Solberg <
martin.bruset.solberg@gmail.com> wrote:
> So my question is, what are my options when I want to make a PITR
> compatible backup, and need to exclude a table/schema/tablespace of the
> database?
>
You might try using one of the trigger-based, asynchronous replication
tools (like Bucardo) to replicate the things you want included in your PITR
archive to another host, and then doing WAL archiving of that.
rls
--
:wq
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PITR compatible backup with exclusion
@ 2015-01-27 07:36 Thomas Kellerer <spam_eater@gmx.net>
parent: Martin Bruset Solberg <martin.bruset.solberg@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Thomas Kellerer @ 2015-01-27 07:36 UTC (permalink / raw)
To: pgsql-admin
Martin Bruset Solberg schrieb am 26.01.2015 um 13:17:
> I am faced with a scenario of backing up a database with one large
> (partitioned) table, and several smaller tables. The large table is
> so big that backing it up is proving problematic both in terms of
> storage and time spent backing up and restoring. The backup of the
> table itself is also reduntant, as the table data can be recreated by
> importing from the source files. This data is not mission critical
> for the application, so a speedy recovery is not urgent. The data
> from the smaller tables, however, is critical. The combined size of
> the rest of the database is not a problem neither in terms of storage
> or backup/restore speed, so a logical backup excluding the large
> table/schema works fine. But logical backup is not sufficiant, as I
> need to be able to do PITR in case of disaster.
>
> So my question is, what are my options when I want to make a PITR
> compatible backup, and need to exclude a table/schema/tablespace of
> the database? Any suggestions welcome...
What if you change that big table to UNLOGGED?
As unlogged tables don't generate WAL they don't contribute to the PITR "restore time"
(and they won't be restored if you have to do a PITR!)
It will be included in the necessary base backup though if I'm not mistaken.
Thomas
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PITR compatible backup with exclusion
@ 2015-03-14 11:30 jaime soler <jaime.soler@gmail.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 5+ messages in thread
From: jaime soler @ 2015-03-14 11:30 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-admin
El mar, 27-01-2015 a las 08:36 +0100, Thomas Kellerer escribió:
> Martin Bruset Solberg schrieb am 26.01.2015 um 13:17:
> > I am faced with a scenario of backing up a database with one large
> > (partitioned) table, and several smaller tables. The large table is
> > so big that backing it up is proving problematic both in terms of
> > storage and time spent backing up and restoring. The backup of the
> > table itself is also reduntant, as the table data can be recreated by
> > importing from the source files. This data is not mission critical
> > for the application, so a speedy recovery is not urgent. The data
> > from the smaller tables, however, is critical. The combined size of
> > the rest of the database is not a problem neither in terms of storage
> > or backup/restore speed, so a logical backup excluding the large
> > table/schema works fine. But logical backup is not sufficiant, as I
> > need to be able to do PITR in case of disaster.
> >
> > So my question is, what are my options when I want to make a PITR
> > compatible backup, and need to exclude a table/schema/tablespace of
> > the database? Any suggestions welcome...
>
>
> What if you change that big table to UNLOGGED?
> As unlogged tables don't generate WAL they don't contribute to the PITR "restore time"
> (and they won't be restored if you have to do a PITR!)
However, they are not crash-safe: an unlogged table is automatically
truncated after a crash or unclean shutdown.
>
> It will be included in the necessary base backup though if I'm not mistaken.
>
> Thomas
>
>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PITR compatible backup with exclusion
@ 2015-03-14 11:59 Thomas Kellerer <spam_eater@gmx.net>
parent: jaime soler <jaime.soler@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas Kellerer @ 2015-03-14 11:59 UTC (permalink / raw)
To: pgsql-admin
jaime soler wrote on 14.03.2015 12:30:
>> What if you change that big table to UNLOGGED?
>> As unlogged tables don't generate WAL they don't contribute to the PITR "restore time"
>> (and they won't be restored if you have to do a PITR!)
>
> However, they are not crash-safe: an unlogged table is automatically
> truncated after a crash or unclean shutdown.
That is correct, but Martin wrote in the initial question:
> The backup of the table itself is also reduntant,
>as the table data can be recreated by importing from the source files
So if the data lost, it apparently doesn't matter.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-03-14 11:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-01-26 12:17 PITR compatible backup with exclusion Martin Bruset Solberg <martin.bruset.solberg@gmail.com>
2015-01-26 20:10 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2015-01-27 07:36 ` Thomas Kellerer <spam_eater@gmx.net>
2015-03-14 11:30 ` jaime soler <jaime.soler@gmail.com>
2015-03-14 11:59 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox