20220722-pg_dump: error: invalid number of parents 0 for table
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:16:58.019 +04 [9332] STATEMENT: select f_rta_log_partition(); 2022-07-16 12:21:41.186 +04 [12936] LOG: request to flush past end of generated WAL; request D9F/E61D8E10, currpos D9F/E61AB138 2022-07-16 12:21:41.186 +04 [12936] CONTEXT: writing block 9 of relation pg_tblspc/907117/PG_12_201909212/907120/2611 2022-07-16 12:21:41.187 +04 [12936] ERROR: xlog flush request D9F/E61D8E10 is not satisfied --- flushed only to D9F/E61AB138 2022-07-16 12:21:41.187 +04 [12936] CONTEXT: writing block 9 of relation pg_tblspc/907117/PG_12_201909212/907120/2611 2022-07-16 12:21:42.458 +04 [12936] LOG: request to flush past end of generated WAL; request D9F/E61D8E10, currpos D9F/E61AB138 2022-07-16 12:21:42.458 +04 [12936] CONTEXT: writing block 9 of relation pg_tblspc/907117/PG_12_201909212/907120/2611 2022-07-16 12:21:42.459 +04 [12936] ERROR: xlog flush request D9F/E61D8E10 is not satisfied --- flushed only to D9F/E61AB138 2022-07-16 12:21:42.459 +04 [12936] CONTEXT: writing block 9 of relation pg_tblspc/907117/PG_12_201909212/907120/2611 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 "" suggest me how to solve this issue. Thanks in advance. Thanks, Karthick Ramu
RE: 20220722-pg_dump: error: invalid number of parents 0 for table
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.kla...@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 " 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.kla...@aklaver.com
RE: 20220722-pg_dump: error: invalid number of parents 0 for table
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
20220221-Clarification regarding PostgeSQL DB backup
Hi Team, We have used PG_DUMP to take backup of particular database from the PostgreSQL Server. It takes too long to restore the databases , which has Half Billion records (almost 8 Hour) My primary need is to make the Differential and Incremental backup on the Windows Server. When I search there is a tool Barman and PG_BackRest is available. But that will be supported only for Linux based servers only Please suggest me a tool to make differential backup Thanks, Karthick Ramu
RE: 20220221-Clarification regarding PostgeSQL DB backup
Hi ~~Na-iem Dollie, Thanks for your reply, In SQL Server there is an option to take Full backup and Differential Backup for a particular database. Like that, we need to take the differential Backup in PostgreSQL. This is my primary need. Thanks, Karthick Ramu From: Daevor The Devoted [mailto:doll...@gmail.com] Sent: Monday, February 21, 2022 11:05 AM To: Techsupport Cc: pgsql-generallists.postgresql.org Subject: Re: 20220221-Clarification regarding PostgeSQL DB backup Hi Karthick I'm a little rusty on PG (it's been about a decade since I last worked intensely with it), but I seem to remember that restoring with indexes can be very slow. The faster approach is to restore without indexes, and then create the indexes once the data restore is complete. My knowledge may be outdated, so best to check with others that have more recent knowledge. However, it's probably worth investigating in the meantime. Okay, I just had a quick look at the documentation, and it seems there is an option to run multiple concurrent jobs for the time-consuming parts of a restore (which includes index creation): -j number-of-jobs see: https://www.postgresql.org/docs/current/app-pgrestore.html (or check the docs for whatever PG version you have) Good luck! ~~Na-iem Dollie On Mon, Feb 21, 2022 at 6:59 AM Techsupport mailto:techsupp...@sardonyx.in> > wrote: Hi Team, We have used PG_DUMP to take backup of particular database from the PostgreSQL Server. It takes too long to restore the databases , which has Half Billion records (almost 8 Hour) My primary need is to make the Differential and Incremental backup on the Windows Server. When I search there is a tool Barman and PG_BackRest is available. But that will be supported only for Linux based servers only Please suggest me a tool to make differential backup Thanks, Karthick Ramu
20220226-Clarification regarding delay time of PostgeSQL starting up
Hi to all, We are using PostgreSQL 12.3, it is running on the Windows Server. Database data directory is configured in the network shared path. Unexpectedly it was disconnected and up again. PgAgent is not connected, when I check the Log, it showing FATAL: the database system is starting up FATAL: the database system is starting up ... Other than that, there is no other errors available. For nearly 7 hours database is not connected, after that it's getting connected. Why it is taking too long time to up. Totally our database has 3.5 TB of data. Suggest me, how to start the server quickly. Thanks, Karthick Ramu
RE: 20220226-Clarification regarding delay time of PostgeSQL starting up
Hi, Thanks for your reply. Is it a good practice to upgrade / update database on production on every year ? In my log > FATAL: the database system is starting up Log showing continuously, other than that there is not PANIC log in the log file, which is under the log folder. Thanks, -Original Message- From: Julien Rouhaud [mailto:rjuju...@gmail.com] Sent: Saturday, February 26, 2022 8:27 PM To: Techsupport Cc: pgsql-general@lists.postgresql.org Subject: Re: 20220226-Clarification regarding delay time of PostgeSQL starting up Hi, On Sat, Feb 26, 2022 at 07:51:45PM +0530, Techsupport wrote: > Hi to all, > > We are using PostgreSQL 12.3, it is running on the Windows Server. So, you're using a version released almost 2 years ago, missing 8 minor releases worth of bug fixes. In general, if you have a problem you should just blindly update to the latest minor version, otherwise it's highly unlikely that anyone will look at your problem. That being said... > > Database data directory is configured in the network shared path. > Unexpectedly it was disconnected and up again. > > PgAgent is not connected, when I check the Log, it showing > > FATAL: the database system is starting up > > FATAL: the database system is starting up > > ... > > Other than that, there is no other errors available. Really? This error is displayed if the server is doing some automatic recovery after an unclean shutdown. Could be an unrecoverable error like IO error on the WALs, or just anything fixed in the last 2 years. You should check for any message with PANIC/FATAL log level too. > For nearly 7 hours database is not connected, after that it's getting > connected. Why it is taking too long time to up. Totally our database > has 3.5 TB of data. Was it 7 uninterrupted hours or did your database keeps being stopped in an unclean way for 7 hours? More details from the logs would help, you could also see https://wiki.postgresql.org/wiki/Guide_to_reporting_problems. But above all you should consider updating to the latest minor version as soon as possible.
