Load PostgreSQL database from corrupt drive [duplicate]

Question

I suffered corruption of a hard drive but managed to recover files for a PostgreSQL server. We are running on the assumption that the files themselves are not corrupted (yet to be able to tell). My server was running on Windows 10 Pro.

Is it possible for me to load the database(s) onto another PC running the same server version? I have the entire directory recovered from "Program Files/PostgreSQL/11" onward (so I have all of "data" etc).

If so can someone guide me on how to do this, or direct me to existing guidance? I really need to recover this data and sadly with the above corruption I lost the drive containing my dump files (backups).

Can I simply copy the "data" folder into a new build, or is it a bit more complex than that?

Answer 1

Theoretically, your database does not have problem, if it is running.

But to verify it, first Stop your database, make a physical copy of PostgreSQL folders.

After that, start your database make an ordinary backup using pg_dump, after that try to restore this backup in a new environment, if you don't have a problem your database is ok.

If you have some problem I will share a link with the main problems of corruption and how to solve it.

https://stackoverflow.com/questions/11030173/repair-corrupt-database-postgresql

But analyze all of the possibilities before starting this process does not exist the only way to resolve this problem.

Answer 2

If you have all binaries and the complete data directory, then you can copy all of that to a new computer and start Postgres using pg_ctl.

Note that the new target computer should ideally run the same Windows version and "bitness" (i.e. 64bit if the old one was 64bit - who uses 32bit these days?) as the old computer used. You can probably get away with a new Windows version, but probably not with an older version (e.g. from Windows 10 to Windows 7 or something similar)

E.g. if you copy everything to c:\OldPostgres, your data directory will be located in c:\OldPostgres\data and you can start the instance using:

c:\OldPostgres\bin\pg_ctl -D c:\OldPostgres\data

If your target computer already has a running Postgres instance, you should first edit c:\OldPostgres\data\postgresql.conf and change the port Postgres will listen on to avoid conflicts.

Once Postgres is started you can e.g. use pg_dump to get a proper dump of that database to import it to the real target server. Then you can remove the "interims" binaries.