list tablespaces named in custom format dump

On 5/23/22 9:45 AM, Rob Sargent wrote:

Using pg 14, is it easy (possible) to list the tablespaces used in a
custom format dump. Are the definitions included?

https://www.postgresql.org/docs/14/app-pgdump.html

"pg_dump only dumps a single database. To back up an entire cluster, or
to back up global objects that are common to all databases in a cluster
(such as roles and tablespaces), use pg_dumpall."

https://www.postgresql.org/docs/14/app-pg-dumpall.html

"-g
--globals-only

Dump only global objects (roles and tablespaces), no databases.
"

Thanks,

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/23/22 10:50, Adrian Klaver wrote:

On 5/23/22 9:45 AM, Rob Sargent wrote:

Using pg 14, is it easy (possible) to list the tablespaces used in a
custom format dump. Are the definitions included?

https://www.postgresql.org/docs/14/app-pgdump.html

"pg_dump only dumps a single database. To back up an entire cluster,
or to back up global objects that are common to all databases in a
cluster (such as roles and tablespaces), use pg_dumpall."

https://www.postgresql.org/docs/14/app-pg-dumpall.html

"-g
--globals-only

    Dump only global objects (roles and tablespaces), no databases.
"

Roger that, but does the dump of a database name the tablespaces from
which the objects were retrieved? And if so, is that accessible?

On 5/23/22 9:54 AM, Rob Sargent wrote:

On 5/23/22 10:50, Adrian Klaver wrote:

Roger that, but does the dump of a database name the tablespaces from
which the objects were retrieved? And if so, is that accessible?

https://www.postgresql.org/docs/14/app-pgrestore.html

"--no-tablespaces

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

Would indicate it does. I don't have cluster set up with tablespaces to
test, but I would try:

pg_restore -l <custom_format_file>

This will produce a Table of Contents for the file.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/23/22 11:00, Adrian Klaver wrote:

On 5/23/22 9:54 AM, Rob Sargent wrote:

On 5/23/22 10:50, Adrian Klaver wrote:

Roger that, but does the dump of a database name the tablespaces from
which the objects were retrieved? And if so, is that accessible?

https://www.postgresql.org/docs/14/app-pgrestore.html

"--no-tablespaces

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

Would indicate it does. I don't have cluster set up with tablespaces
to test, but I would try:

pg_restore -l <custom_format_file>

This will produce a Table of Contents for the file.

Ah, missed that. Thanks.
(Now I'll have to find the version used to dump the db...)

On 5/23/22 10:01 AM, Rob Sargent wrote:

On 5/23/22 11:00, Adrian Klaver wrote:

On 5/23/22 9:54 AM, Rob Sargent wrote:

On 5/23/22 10:50, Adrian Klaver wrote:

Roger that, but does the dump of a database name the tablespaces from
which the objects were retrieved? And if so, is that accessible?

https://www.postgresql.org/docs/14/app-pgrestore.html

"--no-tablespaces

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

Would indicate it does. I don't have cluster set up with tablespaces
to test, but I would try:

pg_restore -l <custom_format_file>

This will produce a Table of Contents for the file.

Ah, missed that. Thanks.
(Now I'll have to find the version used to dump the db...)

If you have it just use the Postgres 14 version of pg_restore it will be
able to read older files from older versions of pg_dump.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/23/22 11:07, Adrian Klaver wrote:

On 5/23/22 10:01 AM, Rob Sargent wrote:

On 5/23/22 11:00, Adrian Klaver wrote:

On 5/23/22 9:54 AM, Rob Sargent wrote:

On 5/23/22 10:50, Adrian Klaver wrote:

Roger that, but does the dump of a database name the tablespaces
from which the objects were retrieved? And if so, is that accessible?

https://www.postgresql.org/docs/14/app-pgrestore.html

"--no-tablespaces

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

Would indicate it does. I don't have cluster set up with tablespaces
to test, but I would try:

pg_restore -l <custom_format_file>

This will produce a Table of Contents for the file.

Ah, missed that. Thanks.
(Now I'll have to find the version used to dump the db...)

If you have it just use the Postgres 14 version of pg_restore it will
be able to read older files from older versions of pg_dump.

This is great news.  I do have pg14.  I thought you had to dump with the
save version as restore.

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 5/23/22 9:54 AM, Rob Sargent wrote:

Roger that, but does the dump of a database name the tablespaces from
which the objects were retrieved? And if so, is that accessible?

Would indicate it does. I don't have cluster set up with tablespaces to
test, but I would try:
pg_restore -l <custom_format_file>

AFAICS, the output produced by -l doesn't mention objects' tablespaces.
You could see them by extracting the plain text archive and grepping
for object comments that include a tablespace name:

pg_restore -s -f - archive_file | grep Tablespace:

It looks to me like that will only appear for tables with non-default
tablespaces.

regards, tom lane

On 5/23/22 11:29, Tom Lane wrote:

AFAICS, the output produced by -l doesn't mention objects' tablespaces.
You could see them by extracting the plain text archive and grepping
for object comments that include a tablespace name:

pg_restore -s -f - archive_file | grep Tablespace:

It looks to me like that will only appear for tables with non-default
tablespaces.

regards, tom lane

That worked, thank you.  I see it has only the "name"  so I presume
location can be redefined in the receiving cluster.

On 5/23/22 10:19 AM, Rob Sargent wrote:

On 5/23/22 11:07, Adrian Klaver wrote:

On 5/23/22 10:01 AM, Rob Sargent wrote:

On 5/23/22 11:00, Adrian Klaver wrote:

On 5/23/22 9:54 AM, Rob Sargent wrote:

On 5/23/22 10:50, Adrian Klaver wrote:

This is great news.  I do have pg14.  I thought you had to dump with the
save version as restore

pg_dump and pg_restore are backwards compatible. The recommendation is
to use the same version of pg_dump as the server you are dumping if the
plan is use it as backup to restore to same version of server. If you
are planning on restoring to newer version then use the newer version of
pg_dump to dump the older server version e.g. version 14 pg_dump dumps
version 12 Postgres server. Then use the newer version of pg_restore(14
in this case) to restore to the Postgres 14 server.

For the purposes of this question using the 14 version of pg_restore is
acceptable to get the information. Actually doing a restore to a 14
version of the server with 12 version of a dump file may create problems
as the 12 version of pg_dump may not include information needed by the
newer server.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/23/22 11:51, Adrian Klaver wrote:

On 5/23/22 10:19 AM, Rob Sargent wrote:

On 5/23/22 11:07, Adrian Klaver wrote:

On 5/23/22 10:01 AM, Rob Sargent wrote:

On 5/23/22 11:00, Adrian Klaver wrote:

On 5/23/22 9:54 AM, Rob Sargent wrote:

On 5/23/22 10:50, Adrian Klaver wrote:

This is great news.  I do have pg14.  I thought you had to dump with
the save version as restore

pg_dump and pg_restore are backwards compatible. The recommendation is
to use the same version of pg_dump as the server you are dumping if
the plan is use it as backup to restore to same version of server. If
you are planning on restoring to newer version then use the newer
version of pg_dump to dump the older server version e.g. version 14
pg_dump dumps version 12 Postgres server. Then use the newer version
of pg_restore(14 in this case) to restore to the Postgres 14 server.

For the purposes of this question using the 14 version of pg_restore
is acceptable to get the information. Actually doing a restore to a 14
version of the server with 12 version of a dump file may create
problems as the 12 version of pg_dump may not include information
needed by the newer server.

Thanks Adrian.  There's enough wiggle room in there for me to at least
try the restore. Linus file command tells me

db.dump: PostgreSQL custom database dump - v1.13-0

That dump was from a version 10 server.  Any guess at how that "v1.13-0"
relates to the server version?  Dumps from the current pg14 server  say
"v1.14-0" so it looks to me like the dumps were done with v13
pg-restore.  This surprises me, but I do not manage the server.  Maybe
my dump file doesn't have anything pg14 restore can't handle.. we'll see

Thanks again, all.

On 5/23/22 11:04 AM, Rob Sargent wrote:

Thanks Adrian.  There's enough wiggle room in there for me to at least
try the restore. Linus file command tells me

db.dump: PostgreSQL custom database dump - v1.13-0

The

pg_restore -l <custom_format_file>

command will show the relevant information at top of file so:

pg_restore -l pg14_test.out > toc.txt

head -n 15 toc.txt
;
; Archive created at 2022-05-23 10:24:23 PDT
; dbname: test
; TOC Entries: 745
; Compression: -1
; Dump Version: 1.14-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 14.2
; Dumped by pg_dump version: 14.2
;
;
; Selected TOC Entries:

That dump was from a version 10 server.  Any guess at how that "v1.13-0"
relates to the server version?  Dumps from the current pg14 server  say
"v1.14-0" so it looks to me like the dumps were done with v13
pg-restore.  This surprises me, but I do not manage the server.  Maybe
my dump file doesn't have anything pg14 restore can't handle.. we'll see

I have abused this on occasion and succeeded, though sometimes it
involves some tweaks.

Thanks again, all.

--
Adrian Klaver
adrian.klaver@aklaver.com

Rob Sargent <robjsargent@gmail.com> writes:

Thanks Adrian.  There's enough wiggle room in there for me to at least
try the restore. Linus file command tells me

db.dump: PostgreSQL custom database dump - v1.13-0

That dump was from a version 10 server.  Any guess at how that "v1.13-0"
relates to the server version?

It doesn't, it's just a more-or-less-magic identifier for the dump
file's format version, with no connection to any user-visible
release number. What you should try is

pg_restore -f - db.dump | head

which should provide some comments telling you the originating
server version and pg_dump version.

regards, tom lane

On 5/23/22 12:15, Tom Lane wrote:

Rob Sargent<robjsargent@gmail.com> writes:

Thanks Adrian.  There's enough wiggle room in there for me to at least
try the restore. Linus file command tells me
db.dump: PostgreSQL custom database dump - v1.13-0
That dump was from a version 10 server.  Any guess at how that "v1.13-0"
relates to the server version?

It doesn't, it's just a more-or-less-magic identifier for the dump
file's format version, with no connection to any user-visible
release number. What you should try is

pg_restore -f - db.dump | head

which should provide some comments telling you the originating
server version and pg_dump version.

regards, tom lane

OK, back to reality: version 10 dump.

The output of from head command is very different that Adrian shows but
I'm still left giving pg_restore(14) a shot.  Getting older server will
be challenging.

Much appreciated,
rjs

On 5/23/22 11:31, Rob Sargent wrote:

On 5/23/22 12:15, Tom Lane wrote:

pg_restore -f - db.dump | head

which should provide some comments telling you the originating
server version and pg_dump version.

regards, tom lane

OK, back to reality: version 10 dump.

The output of from head command is very different that Adrian shows but
I'm still left giving pg_restore(14) a shot.  Getting older server will
be challenging.

Different view of the information.

pg_restore -l has more detail, but all you are interested in is this:

-- Dumped from database version 14.2
-- Dumped by pg_dump version 14.2

anyway.

As to older version, how are you installing Postgres?

Much appreciated,
rjs

--
Adrian Klaver
adrian.klaver@aklaver.com

On May 23, 2022, at 12:59 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 5/23/22 11:31, Rob Sargent wrote:

On 5/23/22 12:15, Tom Lane wrote:

pg_restore -f - db.dump | head

which should provide some comments telling you the originating
server version and pg_dump version.

regards, tom lane

OK, back to reality: version 10 dump.
The output of from head command is very different that Adrian shows but I'm still left giving pg_restore(14) a shot. Getting older server will be challenging.

Different view of the information.

pg_restore -l has more detail, but all you are interested in is this:

-- Dumped from database version 14.2
-- Dumped by pg_dump version 14.2

anyway.

As to older version, how are you installing Postgres?

Much appreciated,
rjs

Yeah, that’s part of the problem. I’m not installing postgres, another group has that control. I can get the answer for version 14 but maybe not for version 10. Currently these are “Rocky Linux” but the pg14 install could have been done on CentOS8. CentOS<something> for version 10. I’ll see if I can nail that down.

On 5/23/22 12:26, Rob Sargent wrote:

As to older version, how are you installing Postgres?

Much appreciated,
rjs

Yeah, that’s part of the problem. /I’m/ not installing postgres, another
group has that control.  I can get the answer for version 14 but maybe
not for version 10.  Currently these are “Rocky Linux” but the pg14
install could have been done on CentOS8.  CentOS<something> for version
10.  I’ll see if I can nail that down.

So the options are:

1) Try the version 14 pg_restore to version 14 instance of version 10
dump file.

2) Wait to see if the admins can/will install a Postgres 10 instance to
restore to from version 10 dump file then dump from using version 14
pg_dump to restore Postgres 14 instance.

3) Spin up a VM either on cloud service or locally and install Postgres
10 and do the version 10 restore/version 14 pg_dump there.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/23/22 13:47, Adrian Klaver wrote:

On 5/23/22 12:26, Rob Sargent wrote:

As to older version, how are you installing Postgres?

Much appreciated,
rjs

Yeah, that’s part of the problem. /I’m/ not installing postgres,
another group has that control.  I can get the answer for version 14
but maybe not for version 10.  Currently these are “Rocky Linux” but
the pg14 install could have been done on CentOS8.  CentOS<something>
for version 10.  I’ll see if I can nail that down.

So the options are:

1) Try the version 14 pg_restore to version 14 instance of version 10
dump file.

2) Wait to see if the admins can/will install a Postgres 10 instance
to restore to from version 10 dump file then dump from using version
14 pg_dump to restore Postgres 14 instance.

3) Spin up a VM either on cloud service or locally and install
Postgres 10 and do the version 10 restore/version 14 pg_dump there.

Agreed. Unfortunately, in this case option 3 is illegal.