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?
Best Answer
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:
And the data with:
If this works, it would allow you to reload your data into a new cluster.