1

Right, my PostgreSQL database 11.1 is on top of a GlusterFS.

My GlusterFS break, my backup was being done on another table and I don't have backups anymore.

I started the database on a docker container, and i recived some erros:

2020-10-07 22:03:43.677 UTC [359] FATAL:  database "template1" does not exist
2020-10-07 22:03:43.677 UTC [359] DETAIL:  The database subdirectory "base/73729" is missing.

Basically, I only have the postgres data directory. The files on directory are:

base                  opa           pg_ident.conf  pg_replslot   pg_stat_tmp  PG_VERSION            postgresql.conf
core.48               pg_commit_ts  pg_logical     pg_serial     pg_subtrans  pg_wal                postmaster.opts
docker-entrypoint.sh  pg_dynshmem   pg_multixact   pg_snapshots  pg_tblspc    pg_xact               postmaster.pid
global                pg_hba.conf   pg_notify      pg_stat       pg_twophase  postgresql.auto.conf

When i try to pg_dumpall:

pg_dumpall -U user1 -g 
pg_dumpall: could not connect to database "template1": FATAL:  database "template1" does not exist
DETAIL:  The database subdirectory "base/73729" is missing.

When i try to pg_dump:

pg_dump -Uuser1 desired_database
pg_dump: [archiver (db)] query failed: ERROR:  could not open file "base/16385/2839": No such file or directory
LINE 1: ....tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog...
                                                             ^
pg_dump: [archiver (db)] query was: SELECT n.tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) as nspacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) as rnspacl, NULL as initnspacl, NULL as initrnspacl FROM pg_namespace n LEFT JOIN pg_init_privs pip ON (n.oid = pip.objoid AND pip.classoid = 'pg_namespace'::regclass AND pip.objsubid = 0)

Theres any way to recovery, make a partial dump or something to extract at least some part of the tables?

Improve this question
2
  • What, if any, files are in the data directory?
    – Ian Barwick
    Oct 8, 2020 at 15:25
  • Added the list of files on the question
    – Psycho Mantys
    Oct 8, 2020 at 16:27

1 Answer 1

Reset to default
0

It is hard to know what the limit of the corruption is. If this is the only problem, you should be able to get the globals with:

pg_dumpall -g

And the data with:

pg_dump desired_database

If this works, it would allow you to reload your data into a new cluster.

Improve this answer
2
  • Nothing change, the pg_dumpall is the same. But i updated the post to reflect more data about the error.
    – Psycho Mantys
    Oct 8, 2020 at 19:25
  • pg_dumpall -g should not try to connect to database template1, unless database postgres already fails to exist. It seems like your corruption is more extensive than just one database.
    – jjanes
    Oct 10, 2020 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.