20220722-pg_dump: error: invalid number of parents 0 for table

On 7/22/22 8:20 AM, Techsupport wrote:

*Hi Team,*

We are using PostgreSQL 12.3, It is running under windows

In that server there is nearly 8 databases are running.

I have changed the data directory from one drive to another drive. At
the time of change the data directory,

have stopped the PostgreSQL �instance and Copy the contents under the
data directory and paste it on the new drive,

and changed the data directory in the �registry and start the instance.

After that all the databases are working, but unable to take backup for
one database.

In 8 database, I have faced this issue in one database.

When I see the log file it gives the following error,

*/2022-07-16 12:21:42.460 +04 [12936] WARNING:� could not write block 9
of pg_tblspc/907117/PG_12_201909212/907120/2611/*

*/2022-07-16 12:21:42.460 +04 [12936] DETAIL:� Multiple failures ---
write error might be permanent./*

�

When I try to back up by using command prompt it shows the below error

*//*

*/pg_dump: error: invalid number of parents 0 for table "<partition
Table name>"/*

suggest me how to solve this issue.

Do you have table spaces other then the default set up? In particular
for partitioned tables.

Thanks in advance.

*Thanks,*

*Karthick Ramu*

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks for your reply Adrian Klaver,

Yes, I have the tablespace. It is not located in the default data directory.
It is comes under new directory

Thanks,
Karthick Ramu

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Friday, July 22, 2022 9:23 PM
To: Techsupport; pgsql-general@lists.postgresql.org
Subject: Re: 20220722-pg_dump: error: invalid number of parents 0 for table

On 7/22/22 8:20 AM, Techsupport wrote:

*Hi Team,*

We are using PostgreSQL 12.3, It is running under windows

In that server there is nearly 8 databases are running.

I have changed the data directory from one drive to another drive. At
the time of change the data directory,

have stopped the PostgreSQL  instance and Copy the contents under the
data directory and paste it on the new drive,

and changed the data directory in the  registry and start the instance.

After that all the databases are working, but unable to take backup
for one database.

In 8 database, I have faced this issue in one database.

When I see the log file it gives the following error,

*/2022-07-16 12:21:42.460 +04 [12936] WARNING:  could not write block
9 of pg_tblspc/907117/PG_12_201909212/907120/2611/*

*/2022-07-16 12:21:42.460 +04 [12936] DETAIL:  Multiple failures ---
write error might be permanent./*

…

When I try to back up by using command prompt it shows the below error

*//*

*/pg_dump: error: invalid number of parents 0 for table "<partition
Table name>"/*

suggest me how to solve this issue.

Do you have table spaces other then the default set up? In particular for
partitioned tables.

Thanks in advance.

*Thanks,*

*Karthick Ramu*

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/22/22 21:27, Techsupport wrote:

Thanks for your reply Adrian Klaver,

Yes, I have the tablespace. It is not located in the default data directory.
It is comes under new directory

Please expand on this:

1) Did it change location when you moved the data directory?

2) If so where was it previously and where is it now?

Thanks,
Karthick Ramu

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/23/22 07:54, Adrian Klaver wrote:

On 7/22/22 21:27, Techsupport wrote:

Thanks for your reply Adrian Klaver,

Yes, I have the tablespace. It is not located in the default data
directory.
It is comes under new directory

Please expand on this:

1) Did it change location when you moved the data directory?

2) If so where was it previously and where is it now?

Where I am going with this:

https://www.postgresql.org/docs/current/manage-ag-tablespaces.html

"PostgreSQL makes use of symbolic links to simplify the implementation
of tablespaces. This means that tablespaces can be used only on systems
that support symbolic links.

The directory $PGDATA/pg_tblspc contains symbolic links that point to
each of the non-built-in tablespaces defined in the cluster. Although
not recommended, it is possible to adjust the tablespace layout by hand
by redefining these links. Under no circumstances perform this operation
while the server is running. Note that in PostgreSQL 9.1 and earlier you
will also need to update the pg_tablespace catalog with the new
locations. (If you do not, pg_dump will continue to output the old
tablespace locations.)"

Thanks,
Karthick Ramu

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi to all,

We are using PostgreSQL 12.3 and it is running under Windows environment. In
my server multiple databases are available.

When we try to Back up one of the database, it gives the following error
message, we can able to connect and read & write to the database.
But unable to back up the database , We faced this issue on one database
only, remaining databases are working good and able to backup. Suggest me,
How to resolve this issue.

pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: error: schema with OID 1020797 does not exist

Thanks,
Karthick Ramu

hey Karthick, if you've made triple sure that it's not a permissions issue,
take a look at some of the suggestions in this old thread:
https://dba.stackexchange.com/questions/17546/why-is-pg-dumpall-throwing-an-oid-does-not-exist-error

On Mon, Aug 8, 2022 at 5:30 AM Techsupport <techsupport@sardonyx.in> wrote: