Thread: Facing issues with pgsql upgrade.

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

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

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