Thread: Facing issues with pgsql upgrade.

Facing issues with pgsql upgrade.

From
Ajay Kajla
Date:

Hello All,


I'm facing the following issue while upgrading pgsql 9.6 to pgsql 13. 

It's saying database "template0" "template1" does not exist on source 9.6, please advise.


image.png


[postgres@htf:/var/lib/pgsql]$ psql

psql (9.2.24, server 9.6.24)

WARNING: psql version 9.2, server version 9.6.

         Some psql features might not work.

Type "help" for help.

 

postgres=# \c template0

FATAL:  database "template0" does not exist

DETAIL:  The database subdirectory "base/13268" is missing.

Previous connection kept

postgres=# \c template1

FATAL:  database "template1" does not exist

DETAIL:  The database subdirectory "base/1" is missing.

Previous connection kept

postgres=#



With Regards,

Ajay Kajla

Attachment

Re: Facing issues with pgsql upgrade.

From
Adrian Klaver
Date:
On 4/16/22 22:30, Ajay Kajla wrote:
> Hello All,
> 
> 
> I'm facing the following issue while upgrading pgsql 9.6 to pgsql 13.
> 
> It's saying database "template0" "template1" does not exist on source 
> 9.6, please advise.
> 
> 
> image.png
> 

 From the below it looks like something/someone deleted the template0 
and template1 directories in the data directory. Is there a file backup 
of the data directory?

> 
> [postgres@htf:/var/lib/pgsql]$ psql
> 
> psql (9.2.24, server 9.6.24)
> 
> WARNING: psql version 9.2, server version 9.6.
> 
>           Some psql features might not work.
> 
> Type "help" for help.
> 
> postgres=# \c template0
> 
> FATAL:  database "template0" does not exist
> 
> DETAIL:  The database subdirectory "base/13268" is missing.
> 
> Previous connection kept
> 
> postgres=# \c template1
> 
> FATAL:  database "template1" does not exist
> 
> DETAIL:  The database subdirectory "base/1" is missing.
> 
> Previous connection kept
> 
> postgres=#
> 
> 
> 
> With Regards,
> 
> Ajay Kajla
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Facing issues with pgsql upgrade.

From
"David G. Johnston"
Date:
On Sun, Apr 17, 2022 at 8:53 AM Ajay Kajla <ajaykajla@gmail.com> wrote:


I'm facing the following issue while upgrading pgsql 9.6 to pgsql 13. 

It's saying database "template0" "template1" does not exist on source 9.6, please advise.



You might want to just forget doing a pg_upgrade migration and do a pg_dumpall one instead.  The missing template databases on the existing cluster shouldn't be noticed.

You can decide how important missing the directories are to you, since that shouldn't happen outside someone going in and "rm'ing" them.

David J.

Re: Facing issues with pgsql upgrade.

From
Ajay Kajla
Date:
Thanks Adrian,

1. What if we re-create template0 and template1?

2. how to restore them if we have a folder backup of the data directory?

Regards,
Ajay


On Sun, Apr 17, 2022 at 9:29 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/16/22 22:30, Ajay Kajla wrote:
> Hello All,
>
>
> I'm facing the following issue while upgrading pgsql 9.6 to pgsql 13.
>
> It's saying database "template0" "template1" does not exist on source
> 9.6, please advise.
>
>
> image.png
>

 From the below it looks like something/someone deleted the template0
and template1 directories in the data directory. Is there a file backup
of the data directory?

>
> [postgres@htf:/var/lib/pgsql]$ psql
>
> psql (9.2.24, server 9.6.24)
>
> WARNING: psql version 9.2, server version 9.6.
>
>           Some psql features might not work.
>
> Type "help" for help.
>
> postgres=# \c template0
>
> FATAL:  database "template0" does not exist
>
> DETAIL:  The database subdirectory "base/13268" is missing.
>
> Previous connection kept
>
> postgres=# \c template1
>
> FATAL:  database "template1" does not exist
>
> DETAIL:  The database subdirectory "base/1" is missing.
>
> Previous connection kept
>
> postgres=#
>
>
>
> With Regards,
>
> Ajay Kajla
>


--
Adrian Klaver
adrian.klaver@aklaver.com


--

Re: Facing issues with pgsql upgrade.

From
Adrian Klaver
Date:
On 4/17/22 19:11, Ajay Kajla wrote:
> Thanks Adrian,
> 
> 1. What if we re-create template0 and template1?

First I would determine what else might be missing?

In psql what happens if you do:

\l

to get a list of databases?

And can you connect to the databases other then postgres?

> 
> 2. how to restore them if we have a folder backup of the data directory?

When was the backup done and how?

Are you sure it is a complete backup?

Do you have tablespaces,other then the default, in use?


> 
> Regards,
> Ajay
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Facing issues with pgsql upgrade.

From
Ajay Kajla
Date:
Hi Adrian,

Command \l gives the list of db available DBs in the cluster including template0 and template1, I can connect to other DBs and run queries as well. 

My motive is just to upgrade pgsql from 9.6 to 13 using the pg_upgrade command.

Regards,
Ajay

On Mon, Apr 18, 2022 at 9:48 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/17/22 19:11, Ajay Kajla wrote:
> Thanks Adrian,
>
> 1. What if we re-create template0 and template1?

First I would determine what else might be missing?

In psql what happens if you do:

\l

to get a list of databases?

And can you connect to the databases other then postgres?

>
> 2. how to restore them if we have a folder backup of the data directory?

When was the backup done and how?

Are you sure it is a complete backup?

Do you have tablespaces,other then the default, in use?


>
> Regards,
> Ajay
>


--
Adrian Klaver
adrian.klaver@aklaver.com


--

Re: Facing issues with pgsql upgrade.

From
Adrian Klaver
Date:
On 4/17/22 22:54, Ajay Kajla wrote:
> Hi Adrian,
> 
> Command \l gives the list of db available DBs in the cluster including 
> template0 and template1, I can connect to other DBs and run queries as 
> well.
> 
> My motive is just to upgrade pgsql from 9.6 to 13 using the pg_upgrade 
> command.

Do you have file level backup of the Postgres data directory from before 
the deletion of template0 and template1 directories?

Why is important that you use pg_upgrade instead of using pg_dumpall as 
David Johnston suggested?

In any case I would suggest doing a pg_dumpall against the 9.6 instance 
just to have a backup.


> 
> Regards,
> Ajay
> 
> On Mon, Apr 18, 2022 at 9:48 AM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 4/17/22 19:11, Ajay Kajla wrote:
>      > Thanks Adrian,
>      >
>      > 1. What if we re-create template0 and template1?
> 
>     First I would determine what else might be missing?
> 
>     In psql what happens if you do:
> 
>     \l
> 
>     to get a list of databases?
> 
>     And can you connect to the databases other then postgres?
> 
>      >
>      > 2. how to restore them if we have a folder backup of the data
>     directory?
> 
>     When was the backup done and how?
> 
>     Are you sure it is a complete backup?
> 
>     Do you have tablespaces,other then the default, in use?
> 
> 
>      >
>      > Regards,
>      > Ajay
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 
> 
> -- 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com