pg_dumpall and tablespaces

On 2/2/21 8:43 AM, Joao Miguel Ferreira wrote:

Hello all,

I have a dump file obtained from pg_dumpall on a MAC computer. I need to
load in onto my Linux laptop running postgres.

My scenario is software development. I'm trying to load the dump onto my
Pg installation running on Linux (and later possibly on Linux over
Docker) in order to perform my sw development against a fresh copy of
the data.

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/....'

Obviously I'm a bit confused on how to do this.

pg_dumpall is very powerfull and simple, specially when using the
"--create" option that does all the work for me.

I would appreciate any directions on how to restore that dump

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

Do not output commands to create tablespaces nor select tablespaces
for objects. With this option, all objects will be created in whichever
tablespace is the default during restore.

This would have to be done when the pg_dumpall is run.

thank you
Joao

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Feb 2, 2021 at 4:52 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 2/2/21 8:43 AM, Joao Miguel Ferreira wrote:

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

Do not output commands to create tablespaces nor select tablespaces
for objects. With this option, all objects will be created in whichever
tablespace is the default during restore.

This would have to be done when the pg_dumpall is run.

That's great. I really do not need the tablespaces for dev. I will try that

thank you

Adrian Klaver <adrian.klaver@aklaver.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

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

Adrian Klaver <adrian.klaver@aklaver.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