patternsqlMinor
psql can't connect to existing db
Viewed 0 times
existingpsqlcanconnect
Problem
i have problem with connecting to database via psql. I get this error:
But when I connect to posgtres and list the db's, the aisgdmp2 db exist...
Any idea where is the problem?
The database was running in the past but has been down for the past two months.
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:
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
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.