HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

psql can't connect to existing db

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
existingpsqlcanconnect

Problem

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.

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#325065, answer score: 2

Revisions (0)

No revisions yet.