Thread: 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