Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_basebackup restore a single table

pg_basebackup restore a single table

From
camarillo
Date:
Hello how are you?

Currently, I'm doing a basebackup of a postgres instance 9.5 due to the
growth of it, doing a pg_dump took a long time.
Can I do a restore of a single table or single base using the archive
generated for the basebackup without having to delete the filesystem
(/var/lib/pgsql/9.5/*)?.  

Thanks,
Carlos 



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pg_basebackup restore a single table

From
Andreas Kretschmer
Date:

Am 11.04.2018 um 15:53 schrieb camarillo:
> Can I do a restore of a single table or single base using the archive
> generated for the basebackup without having to delete the filesystem
> (/var/lib/pgsql/9.5/*)?.

No, but you can use a spare machine to restore the hole database 
(point-in-time-recovery) and restore the table from that using pg_dump 
or copy.


Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: pg_basebackup restore a single table

From
Ron
Date:

On 04/11/2018 10:21 AM, Andreas Kretschmer wrote:
>
>
> Am 11.04.2018 um 15:53 schrieb camarillo:
>> Can I do a restore of a single table or single base using the archive
>> generated for the basebackup without having to delete the filesystem
>> (/var/lib/pgsql/9.5/*)?.
>
> No, but you can use a spare machine to restore the hole database 
> (point-in-time-recovery) and restore the table from that using pg_dump or 
> copy.

Not everyone has a spare machine and a few TB lying around...

-- 
Angular momentum makes the world go 'round.

Re: pg_basebackup restore a single table

From
Melvin Davidson
Date:


On Wed, Apr 11, 2018 at 11:22 AM, Ron <ronljohnsonjr@gmail.com> wrote:


On 04/11/2018 10:21 AM, Andreas Kretschmer wrote:


Am 11.04.2018 um 15:53 schrieb camarillo:
Can I do a restore of a single table or single base using the archive
generated for the basebackup without having to delete the filesystem
(/var/lib/pgsql/9.5/*)?.

No, but you can use a spare machine to restore the hole database (point-in-time-recovery) and restore the table from that using pg_dump or copy.

 Not everyone has a spare machine and a few TB lying around...

--
Angular momentum makes the world go 'round.


>Can I do a restore of a single table or single base using the archive
>generated for the basebackup ...

As Ron has previously stated, this is not possible, but it begs the question...why not simply do a pg_dump of one table and restore that?

--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

Re: pg_basebackup restore a single table

From
"David G. Johnston"
Date:
On Wed, Apr 11, 2018 at 8:22 AM, Ron <ronljohnsonjr@gmail.com> wrote:
On 04/11/2018 10:21 AM, Andreas Kretschmer wrote:
Am 11.04.2018 um 15:53 schrieb camarillo:
Can I do a restore of a single table or single base using the archive
generated for the basebackup without having to delete the filesystem
(/var/lib/pgsql/9.5/*)?.

No, but you can use a spare machine to restore the hole database (point-in-time-recovery) and restore the table from that using pg_dump or copy.

 Not everyone has a spare machine and a few TB lying around..
​.

​The machine one tests their backup restoration process on should suffice for this purpose...​

David J.

Re: pg_basebackup restore a single table

From
camarillo
Date:
Thank you!



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html