Re: pg_dumpall and tablespaces

On Tue, Feb 2, 2021 at 5:08 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> > On 2/2/21 8:43 AM, Joao Miguel Ferreira wrote:
> >> I have a dump file obtained from pg_dumpall on a MAC computer. I need
> to
> >> load in onto my Linux laptop running postgres.
> >> I got 2 problems concerning tablespaces:
> >> a) during the restore step I get lots of errors about the necessity to
> >> have root permissions to re-create the tablespaces and
> >> b) the tablespaces paths on the dump file are bound to the MAC
> >> filesystem (/Users/..../pg/....). I would need to re-write that path to
> >> my home folder or '/var/lib/....'
>
> > Do you want to maintain tablespaces on the dev machine?
>
> > If not from here:
> > https://www.postgresql.org/docs/12/app-pg-dumpall.html
> > --no-tablespaces
>
> Also, if you're not in a position to re-make the dump file, you
> can just restore it and ignore all the tablespace-related errors.
> You'll end up with the same situation either way, i.e. all the
> tables exist in the default tablespace.
>
> If you do need to preserve the separation into distinct tablespaces,
> you could try this:
>
> * Starting with an empty installation, create the tablespaces you need,
> matching the original installation's tablespace names but putting
> the directories wherever is handy.
>
> * Restore the dump, ignoring the errors about tablespaces already
> existing.
>
> Either way, the key is that a dump file is just a SQL script and
> isn't especially magic; you don't have to be in fear of ignoring
> a few errors. pg_dump builds the script to be resistant to certain
> types of issues, and missing tablespaces is one of those.
>
> I do recommend capturing the stderr output and checking through it
> to ensure you didn't have any unexpected errors.
>
> regards, tom lane
>

Hi Tom,

thanks for the additional details. I did not know about that kind of
tolerance during restore.

Cheers
Thank you