Обсуждение: list tablespaces named in custom format dump
Using pg 14, is it easy (possible) to list the tablespaces used in a custom format dump. Are the definitions included?
Thanks,
Thanks,
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:Ah, missed that. Thanks.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.
(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:This is great news. I do have pg14. I thought you had to dump with the save version as restore.On 5/23/22 11:00, Adrian Klaver wrote:On 5/23/22 9:54 AM, Rob Sargent wrote:Ah, missed that. Thanks.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.
(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> 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:Thanks Adrian. There's enough wiggle room in there for me to at least try the restore. Linus file command tells meOn 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.
db.dump: PostgreSQL custom database dump - v1.13-0That 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 medb.dump: PostgreSQL custom database dump - v1.13-0That 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:OK, back to reality: version 10 dump.
pg_restore -f - db.dump | head
which should provide some comments telling you the originating
server version and pg_dump version.
regards, tom lane
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.