psql can't connect to existing db

Question

i have problem with connecting to database via psql. I get this error:

postgres@server:/var/lib/pgsql # psql -d aisgdmp2
psql: error: FATAL:  database "aisgdmp2" does not exist
DETAIL:  The database subdirectory "pg_tblspc/16385/PG_13_202007201/16386" is missing.
postgres@server:/var/lib/pgsql #

But when I connect to posgtres and list the db's, the aisgdmp2 db exist...

postgres@server:/var/lib/pgsql # psql
psql (13.5)

postgres=# \l
                                   DB list
   Name    | Owner    | Code     |  Collation  |    CType    |   Privileges
-----------+----------+----------+-------------+-------------+-----------------------
 aisgdmp2  | aisg     | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =Tc/aisg             +
           |          |          |             |             | aisg=CTc/aisg
 postgres  | postgres | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 |
 template0 | postgres | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=#

Any idea where is the problem? The database was running in the past but has been down for the past two months.

Answer 1

The important error is this:

The database subdirectory "pg_tblspc/16385/PG_13_202007201/16386" is missing.

Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

To fix the existing database with the missing tablespace, you may need to restore the database from a dump file. The industry standard term is called a database backup. PostgreSQL calls this a dump file. Maybe because the developer was taking a dump at the time. I'm not sure.

To avoid losing what is in there, you could install another instance of Postgres elsewhere and create a database with the same name, and restore it from the dump file of the bad database as a test, using the first link, until you are successful. Or restore a database with a different name from the dump file, using the second link.

Look into these:

https://helicaltech.com/solve-pg_tblspc-problem-postgres/

https://stackoverflow.com/questions/19223522/how-to-restore-postgres-database-into-another-database-name

Answer 2

Check where the tablespace directory should be located:

postgres=# \db
               List of tablespaces
    Name    | Owner  |        Location              
------------+--------+-----------------------
 pg_default | melkij | 
 pg_global  | melkij | 
 testts     | melkij | /tmp/pgdev_tablespace

or via symlink in pg_tblspc directory:

ls -l /path_to_postgresql_datadir/pg_tblspc/

After that check that this file system is mounted, directory exists and is not empty. It is possible that the partition was just not automatically mounted for some reason (for example, was not added to fstab at all or the name of the block device changed). After fixing the error, the data may become available again - since postgresql started, the data in the tablespace was in a consistent state at the time the database was shut down. But if the tablespace was lost, then all data is also lost.